00 Preface Let’s Understand MySQL Together #

Hello, I’m Lin Xiaobin, also known as “Ding Qi”. Welcome to my column, and let’s begin the journey of learning MySQL together. I have worked at both Baidu and Alibaba, specializing in MySQL database development. From being a novice in databases to becoming a MySQL kernel developer, it has been a decade since I first flipped through the MySQL source code in search of answers. Along the way, I have taken many detours but also gained a lot of knowledge and insights, which I hope to share with you in this column.

I remember the first time I came into contact with MySQL was when I was working on an authorization system at Baidu Tieba. We encountered a strange issue where a normal SQL query that usually took 10 milliseconds would occasionally take over 100 milliseconds to complete. When my supervisor asked me for the reason, I couldn’t figure it out either. So, I searched online for answers, but I couldn’t find anything. I felt shy and didn’t want to admit that I didn’t know, so I decided to dive into the source code. As time went on, I encountered more and more problems with similar situations, which prompted me to develop the habit of analyzing the source code to understand the principles behind them.

At that time, my personal feeling was that even as a software engineer and only a user of MySQL, understanding the principles of each system module before using it made a huge difference. When I wrote a line of database command in the code, I could imagine how it would be executed on the database server and what its performance would be like. I also became more aware of which data processing tasks were better suited for the database system and which ones were better off in the cache. When creating tables and indexes, I would also consciously consider future query optimization, such as deciding whether to use an incrementing primary key and how to choose the columns for the primary key, and so on.

However, I soon encountered a new problem. I felt that my knowledge of MySQL was scattered and lacked a coherent structure. After solving one problem, I would easily forget it. When similar problems arose again, I had to dive back into the code once more.

Fortunately, when I joined Alibaba, I participated in the development of the kernel for AliSQL, an open-source branch of Alibaba Cloud’s relational database service. This allowed me to study and understand the MySQL kernel and source code at a deeper level. Through interactions with both internal and public cloud clients, I had the opportunity to face and solve numerous problems. By studying the documentation systematically, I gradually built up a network of knowledge about MySQL, although the path was not without obstacles.

Therefore, when I look back on this journey, my first feeling is that it would have been much better if I had some systematic guidance from theory to practice right from the beginning. Perhaps I could have learned faster.

When the team at Geek Time contacted me to plan this column, I was initially skeptical. Why? It’s different now compared to years ago. I still remember that over a decade ago, if you encountered a problem while using MySQL, you could only search for answers within the code because there was so little information available online at that time.

However, in the past decade, MySQL has become widespread in China, and there is now an abundance of technical articles being shared. So, if we were to systematically introduce MySQL again, most of the knowledge points mentioned could probably be found in community articles. So, where does the significance of this column lie, and why should it be a paid service? Not until I received a response from the team at Geek Time did I begin to have a clear understanding of what this column “What You Can Do and What You Want to Do” is about. The database is a comprehensive system that has developed over several decades of database theory. At the same time, a database system is also an application system. Even after using MySQL for two or three years, a business developer may not be clear why the “best practices” they have been using are the best.

Therefore, I hope this column can help developers like them: those who are currently using MySQL and know how to write logically correct SQL statements to achieve business goals, but are unsure if these statements are optimal; those who have heard of some best practices for using databases but want to understand the reasons behind them; those whose databases occasionally encounter problems and urgently need to know how to locate and even solve the problems more quickly and accurately…

In the past seven years, I have mentored over a dozen fresh graduates and watched them grow. I have emphasized the importance of understanding principles before putting them into practice. Over the years, their growth has been rapid, and several of them have become key members of the team within two years of graduation. I have also interviewed many candidates in social recruitment who have good operational practice and abilities, but unfortunately, they did not pass the interview because they only had a superficial understanding of database principles.

Therefore, I hope this column can inspire developers to explore the principles of databases, thereby better understanding the problems they encounter at work and gaining deeper insights. So, I will select the knowledge that frequently appears when using databases, such as transactions, indexes, and locks, as the main content of this column. These main topics consist of individual knowledge points. Each point is an explanation of a concept, a mechanism, or a principle. After each explanation, I will discuss a practical problem with you.

I hope that in this way, you can have an overall understanding of the few main topics of MySQL and grasp the basic concepts. In the subsequent practical sections, I will refer to the background knowledge of these main topics and focus on explaining how they guide practice. In this way, you can connect the dots, form a network of MySQL knowledge, and move from individual topics to a comprehensive understanding.

Here is a table of contents where you can get an overview of the knowledge structure of the entire column.

As mentioned earlier, almost every knowledge point on these main topics is not the latest, and some have remained unchanged for ten years. However, I hope that the explanations for these points can give you more confidence when using MySQL, help you make choices, and understand the reasons behind them. Only by understanding the principles can you continuously innovate and improve your personal value and work output in practice.