This article is for learning and communication purposes only, not for commercial purposes. For non-commercial reprints, please indicate the translator and source, and keep the original link of this article: http://www.ituring.com.cn/art…
Interviewee: Mick,Working in a system development company in Japan, is a performance engineer. The professional field is the design and performance optimization of large-scale data analysis systems such as Bi / DWH. If performance problems occur, they will also deal with various aspects such as OS resources or Java Memory resolution. In addition, I have recently been engaged in training for young engineers in the company.
Mick has been a member of the choir since he was a student, but his recent interest has been playing with children.
This book is aimed at those readers who are first exposed to SQL and relational databases. It helps them learn from the basic SQL knowledge, including the basic syntax of select / update / delete, and the knowledge points of case expression, table combination, associated subquery, window function and other important functions. In addition, since this book is based on the syntax of standard SQL, it can be used as a portable and useful technology to accompany no matter what the difference is in database products. ——MICK
Technical people give people the impression of “house”. What kind of person are you in your life?
Last autumn, my baby was born, so now my personal time is basically with the child. Let’s go to the zoo (the news reported the birth of the baby panda) and play with toys. Although it is a very happy and touching thing to be able to see their children grow up with their own eyes, it is a headache for the parents who accompany them to grow up (laughter). Now, basically have no time to learn technology, is also very distressed.
What forms will Japan’s technology circles exchange and learn from each other?
Learning conferences and seminars are particularly frequent and active in Japan. Although most of the time is held after work on weekdays, there are still many people who squeeze out time to participate in the busy work. This kind of learning is sometimes held in different industries, and sometimes it is held on the theme of specific technical fields (DB, Java, ruby, machine learning, etc.). I will also attend DB’s study meeting and have been entrusted to give a speech. Because this is very important not only for myself, but also for the development of the whole IT industry, so when I receive the entrustment, I will accept it.
Japan is an efficient society with relatively fast pace of life. What are the secrets of your work, life and writing?
The secret… It’s hard. (laughter). Frankly speaking, in my 20s, it was physical strength. At that time, the company’s work was very busy, and I could go home late at night, and I would often continue to study or write books when I got home. But it certainly can’t last too long. It’s just a privilege when you’re young. When it comes to the trick, it’s to make good use of your spare time. I go to and from work by tram (about 45 minutes to the company). Since the popularity of smart phones, I have been recording articles that suddenly appear in the tram, or translating technical reports in English, returning an email, sorting out trivial tasks, and so on. This allows a lot of time for really important activities like learning and writing books.
Topic of Book Writing
What was the opportunity for you to start writing a technical book?
Initially, I published technology related information on my personal website.
I didn’t intend to write a book. I just wanted to share the memo with you. Later, the editor of the publishing house who had read the website sent me an invitation to write technical reports for the online magazine. I accepted the invitation and gradually began to become a writer, and then began to write books.
Since the publication of SQL basic course, it has been well received by readers. What do you think is the reason why this book is loved by readers?
I’m really happy to be praised in Japan and China, two countries with different languages. When I wrote this book, I paid more attention to “write a book I wanted to read when I was a beginner”. Not only SQL, but also those introductory books of programming languages, basically do not explain the detailed principles, “just remember to write this way” is also a lot of content. These are called “cookbooks” or “cookbooks.”. For me, even if it’s an introductory book, I don’t use this way of writing. Because if you don’t understand the principle at the beginning, you can’t leave the stage of beginners.
To write such a simple, easy to understand and meticulous introduction book, we need to have a careful observation and deep understanding of things. How do you do it?
Indeed, I am very careful not to neglect the point of “meticulous observation of things”. In other words, “we can’t ignore the small discordance.”. When I began to study a certain field, I often took the question of “why is this so?” and I never ignored such a question and investigated it carefully until I really solved the problem.
For example, when learning SQL at the beginning, if we want to retrieve null, we can’t use “< column name > = null”, we must write it as “< column name > is null”. This syntax rule is very inconceivable. Why can’t “=” be used? If you don’t go deep into such questions, you can recite them according to “this is how they were written.”. However, this will not be able to master the actual application ability, and will soon encounter a bottleneck period of growth. I think the best way to understand the essence is not to ignore the trivial questions you see. “God is in the details” is one of my mottos.
Do you have any writing plans for the third edition in the future? If so, in what ways will the second edition be supplemented or updated?
Although there is no writing plan for the 3rd Edition, I will consider the following two ways as a supplement. One is the application technology of adding SQL. Now, as a tool for big data processing and analysis, SQL has many more advanced uses than before. By using window function and case expression, we can create a very convenient program. At present, the two versions that have been published are still on the explanation of the basic usage methods, so we are considering to expand some contents that conform to the times. The other is to add a description about the position of SQL / RDB in the overall system development. For example, add some similar SQL injection principles and countermeasures, aiming at how to apply SQL / RDB in actual system development.
Technical book translation
We know that you have translated Joe Celko’s books. Can you tell me your opinion about Joe Celko and his works?
Celko’sJoe Celko’s SQL for SmartiesIt’s my textbook for learning SQL. It can be said that almost all the knowledge about SQL was learned from him. This is a very large and difficult book to understand, so it is very hard to read. In fact, one of my motivations for writing a book was “to make Celko’s book easier to understand.”. My book is actually a commentary on Celko’s book.
As a translator, how do you decide whether to translate a book or not?
Now, I’m translating three other Celko books. Since I started reading his books in his early 20s, I have always respected him so much that I always want to have a chance to translate his books. I even have the passion to propose to the press. In addition to Celko, there are many excellent English books on database, and I hope to translate such books when.
How does the translation of technical books affect the translator’s existing knowledge? In the process of translation, will the translator add some understanding or suggestions?
As mentioned above, Celko’s book is very difficult to understand, and can’t be understood by reading it once. Therefore, when translating, we will add a lot of annotations that can be easily understood by readers. In addition, when they do not agree with the author’s opinions, they will try their best to express their own views and provide readers with the opportunity to think for themselves.
In China, there are a large number of explanatory books on the profound ancient books headed by the Analects of Confucius. For example, Zhu Xi’s “the Analects of Confucius” and he Yan’s “the Analects of Confucius” also have a wide range of readers in Japan. This kind of annotation text not only explains the meaning of the language, but also adds a lot of independent thinking content, which is very creative. I also hope that, like this, I will not do simple language transformation, but translate for the purpose of bringing more added value to the original book.
Relational database and non relational database
Can you briefly introduce the characteristics of relational database and non relational database (NoSQL) and their suitable application scenarios?
When NoSQL first appeared, there were some opinions about whether it would replace relational databases. But after so many years, it has been stabilized in the form of “suitable talents and suitable places”. There are so many types of NoSQL that it is impossible to generalize them in one word. It is more representative to distinguish them according to their types.
(1)Key Value Store (KVS)Memcached, redis, dynamodb (※), etc
(2)Text DBMongodb, dynamodb, etc
It also has the function of text dB.
I think (1) has two advantages. One is to make high-speed query possible by simply processing the data structure with key value. Give up the freedom of SQL query, instead of the pursuit of speed. Make the scalability of data increase more abundant. Through kVs, especially after data memory, the performance can be improved.
(2) The advantage is that through the form of JSON and XML, you can make the data in the past relational database in the form of “table”, which is difficult to process, and become very free. In this way, it can be used to deal with all kinds of data that are not well-designed and standardized.
However, no matter which type it is, it will sacrifice the high transaction processing function and data security of relational database. From the literal meaning of “result consistency”, it can only ensure that the data is finally kept in the correct state after the change, and temporary inconsistency is allowed. Therefore, (1) it can be applied to the data processing of “requiring high-speed response, allowing some degree of data inconsistency and disappearance”. Specifically, it refers to streaming data like SNS contributions or session information of users in EC websites, etc. For session information, even the worst loss can be reset by logging in again. However, the disappearance of records and payment data of purchased goods must not be allowed. Therefore, such a situation requires the use of kVs as the foreground and relational database as the background.
In addition, there are some to deal withDatabase of unstructured data(although there are still divergent opinions on whether it can be called NoSQL). In a word, it is the non text data such as images and sounds, as well as the data that the relational database of the past, such as graphic structure, is more difficult to deal with. This kind of database is specially used to deal with the data that the relational database can’t handle (or is difficult to process). In the specific application field, it may replace the relational database.
However, because the stability and universality of relational database are very outstanding, so we use relational database to build core system, and use NoSQL in more lightweight front-end and other subdivision fields, which will continue in the future.
Will non relational database (NoSQL) become the future trend of data management?
I think NoSQL has two directions. First, NoSQL still has the possibility of independent development in the future. The other is the possibility that it can be absorbed as a function of relational database. In fact, in the past, XML database was originally developed as an independent database product, but now it has been absorbed as a function of most DBMS. Even now, relational databases have the possibility to expand the functions of processing JSON and spatial dB, and eventually become a “big family” with NoSQL functions. Anyway, I think as an engineer, learning how to deal with unstructured data will become more and more important in the future.
What habits do you think novices need to develop when learning SQL? In terms of efficiency and standardization, can we give some suggestions?
I think it should be, at the beginning, not to memorize those clever skills and “secrets”, but to be able to understand the principle of action with your heart. Compared with Java and other programming languages, SQL is very simple at first sight because it has fewer functions and functions and is close to the natural language of English. But in fact, those “simple functions” (associations, case expressions, window functions, having statements, and so on) combine to become extremely complex.SQL may be very similar to building blocks. If we ignore the understanding of each building block, it is absolutely impossible to build a high building。
If you want to be engaged in software development in the future, what aspects do beginners need to work hard after learning SQL basic course? Can you give me some professional advice?
This book is ultimately focused on helping you understand SQL syntax. In fact, for the necessary knowledge of technical personnel, the knowledge of SQL is far from enough, so I hope you can focus on a broader field. For example, even in the database field, there are also technologies like ER modeling, Oracle, MySQL and other product design technologies, SQL performance optimization, etc., which need to be studied for many years. If you are software developers, I think you must learn Java, PHP, Python and other development languages.
What is your goal? It is determined by your own career intention and market demand, so I can’t give a general suggestion. But no matter which field it is, it is very important to study with interest. Therefore, it is very important to find the areas where you can keep learning motivation as soon as possible and work with interest continuously.
In the early stage of software outline design, different projects need to choose different databases. Can you tell us a little bit about how to choose the right database?
At present, there are five representative relational databases in Japan: Oracle, SQL server, DB2, PostgreSQL and mysql. How to choose is a very important problem for technical engineers, and it is a difficult problem that must consider many factors comprehensively.
But in reality, sometimes we don’t have a choice. In a word, it is often restricted by “money”. For example, when the company starts and needs to establish new services, in order to minimize the license fee, there is no choice except for the OSS based databases such as PostgreSQL and mysql. If we want to build a system for financial institutions and public institutions that pursue very high availability and performance, we must choose Oracle, which has high functions and can provide meticulous and thoughtful support services. Or when the information flow of EC website changes frequently with seasons, it may be the most cost-effective to choose Amazon RDS and other cloud based databases as SaaS databases.
In recent years, any kind of database product can hardly be distinguished in terms of functional enrichment. Therefore, compared with the past selection criteria, the above mentioned aspects such as budget constraints, non functionality, and support enrichment have become more important.
Can you share your experience and suggestions on improving SQL performance? For example, in complex SQL (multiple tables, nested queries, associated queries), or stored procedures, where are performance issues likely to occur?
SQL performance optimization is one of my majors. Although SQL is very easy to cause performance problems, in fact, it is mainly due to the large amount of data processed by the database. The web server and AP server can only process the maximum amount of data (GB level) that can be saved in memory. However, processing TB level data on a database server is a matter of course. In recent years, with the popularity of big data, the amount of data has gradually increased.
This will inevitably make the speed of reading data from the memory become a bottleneck that is hard to surpass, which also causes many performance problems of SQL. Especially in large table Association, this problem is more obvious.
If you want to talk about solutions in this interview, it may not be enough space.I plan to write a book on SQL performance improvement。 However, if you want to give an example of an improvement method that can be implemented immediately, it is to load as much physical memory as possible on the DB server and allocate more memory to the database so that the database can use more memory. In the past, the price of memory was relatively high, but recently it is becoming cheaper. 32GB or 64GB of memory can be installed on general DB servers without pressure. Memory is much faster than general HDD. If you can save data in memory, you can solve many performance problems. Although this is only a solution to replace the mind with money, it is recommended because the investment effect is significant. (laughter)
If you want to give an example that needs attention in design, it is to minimize the amount of data processed by SQL as a basic policy. Old data that is rarely needed can be transferred to historical tables. For the data query conditions that can be set on the screen, let the user set them as far as possible, and so on.
SQL performance optimization is a last resort for problems that cannot be solved by design efforts.Don’t rely on SQL performance optimization from the beginning. It is very important to design without SQL performance optimization. Please keep this in mind。
Sun Miao,He has been engaged in software design and R & D in Japan for more than 10 years. He studied and worked in Japan from 2007 to 2009, and has been working in Japan for a long time since 2015. Proficient in the application of Java, PHP web framework design and development, and Oracle, Teradata, mysql, NoSQL and other database design and development experience. Happy to taste the subtle details of life, keen to taste and make delicious food. Is the translator of SQL basic course.
More wonderful, join Turing interview wechat!