[Content Overview]
This book is designed and implemented by a MySQL expert from Sun's Chinese Engineering Institute. The author divides the complex MySQL kernel into multiple functional subsystems to resolve various difficulties in understanding the subsystems.
This book is divided into 12 chapters. Chapter 2 describes the historical origins of MySQL. Chapter 2 describes the architecture of the MySQL database. Chapter 2 provides an in-depth understanding of MySQL source code and Chapter 3rd explains key classes and algorithms. From Chapter 1 to the end, we will explain each subsystem, the content includes connection and network system, server thread and resource management, query parsing and optimizer, security management system, storage engine Interface Details and practices, classic storage engine, log function and practice analysis and other subsystems.
Related MySQL source code is provided in the book distribution CD.
This book is suitable for technical staff and developers interested in kernel programming and design, as well as various technical staff who use MySQL Databases during learning and work. Because the main content involves the MySQL kernel source code, learners should have C/C ++ preparation knowledge. [Basic Information]
- Author:Zhu dingzeZhang HaiHuang Jianchang
- Press: Tsinghua University Press
[Translator]
Author: Zhang Hai
Zhang Hai, SUN System Engineer (SCSA, SCNA), MySQL DBA (CMDBA), now works for a software company in Pakistan, responsible for the overall O & M of the system network and database of the Beijing Office, it also provides project support for LAHORE headquarters ..
Author: Huang Jianchang
Huang Jianchang: over 10 years of software R & D and project management experience. He works at Sun and serves as technical consultant for ISV Engineering. He is mainly responsible for software technical consulting for telecom industry partners and has technical expertise in Java, Solaris, MySQL application development and performance optimization. Now works for IBM.
Author: Zhu dingze
Zhu dingze: The English name "Hironics. Sun MySQL architect pays close attention to the growth and Application of MySQL in China. Several authors jointly operate and maintain the MySQL Technology website http://www.mysqlsystems.com, Hironics often contributed to InfoQ. Hironics has all MySQL certifications: CMA, CMDEV, CMDBA, and CMCDBA.
[Directory]Chapter 1 past and present of MySQL
1.1 MySQL History 2
1.2 Storage engine 4
1.3 MySQL market 6
1.4 Things 8
Chapter 2 three-dimensional view of database system 9
2.1 database system architecture 10
2.2 Classification of database systems 10
2.2.1 Object-Oriented Database 10
2.2.2 relational database 13
2.2.3 Object Relational Database 14
2.3 relational database system architecture 15
2.3.1 client application 16
2.3.2 query interface 16
2.3.3 query statement processing 17
2.3.4 query optimization 18
2.3.5 statement execution 19
2.3.6 read and write files 19
2.3.7 module collaboration 19
2.3.8 RDBMS hierarchy 20
. 2.4 MySQL Database System 21
2.4.1 MySQL Database System Architecture 22
2.4.2 connection between subsystems 23
2.4.3 MySQL subsystem and Code 25
2.5 Summary 29
Chapter 3 Changyou source code 31
3.1 download MySQL source code 32
3.1.1 Bazaar installation 32
3.1.2 access MySQL source code 33
3.2 source code directory structure 35
3.2.1 key directories 36
3.2.2 code contributed by the open-source community 42
3.2.3 interpretation of SQL/SQL _delete.cc 43
3.3 MySQL Kernel Analysis Tool 46
3.3.1 compile and install MySQL 48
3.3.2 debug MySQL 50
3.3.3 source code retrieval tool 56
3.3.4 Doxyen source code 57
3.4 summary 58
Chapter 59 core classes, library functions and Algorithms
4.1 core class 61
4.1.1 Thread class (THD) 61
4.1.2 Item type 65
4.1.3 TABLE description-TABLE 68
4.1.4 FIELD Class 69
4.2 function library 72
4.2.1 memory operation function 73
4.2.2 file system operating functions 74
4.2.3 hash table operation 76
4.2.4 string operation 77
4.2.5 macro definition 78
4.3 Core algorithms 78
4.3.1 Bitmaps-bitmap 79
4.3.2 Working Principle of table connection buffering 80
4.3.3 MySQL sorting implementation 83
4.3.4 Character Set and proofreading Rules 86
4.4 summary 88
Chapter 3 connection and network system 89
5.1 MySQL NET protocol 90
5.1.1 protocol and operating system protocol stack 90
5.2 network package format 92
5.2.1 Null end string and
String with length 93
5.2.2 network Header Format 94
5.3 client-sent packet 95
5.3.1 client authentication package 95
5.3.2 Command package (Command) 96
5.4 server-side packet 99
5.4.1 handshake initialization package 99
5.4.2 result package category 102
5.4.3 OK package 102
5.4.4 ERROR package 105
5.4.5 result set package 107
5.5 Conclusion 114
Chapter 2 server thread and Resource Management 6th
6.1 thread or process 118
6.1.1 comparison between threads and processes 119
6.2 MySQL thread Problems and Solutions 120
6.2.1 Standard C function call 121
6.2.2 mutex locks 122
6.2.3 thread synchronization 124
6.3 client request processing 126
6.3.1 MySQL startup 127
6.3.2 execution stream 128
6.4 class, API, variable, and struct 130
6.5 MySQL memory allocation 132
6.5.1 memory shared Block 132
6.5.2 thread memory Area (TMA) 133
6.5.3 How can MySQL allocate 134 of memory?
6.6 conclusion 137
Chapter 2 query parser and optimizer 7th
7.1 MySQL parser 140
7.1.1 lexical analysis program. 143
7.1.2 syntax analyzer 146
7.1.3 key categories 146
7.2 query optimizer 154
7.2.1 traditional optimization algorithm 154
7.2.2 MySQL query algorithm 155
7.3 Conclusion 170
Chapter 2 Security Management System 8th
8.1 account authentication 172
8.1.1 account definition 172
8.1.2 identity audit 173
8.1.3 specific priority 182
8.2 permission control 183
8.2.1 system permission 184
8.2.2 permission review 186
8.2.3 permission 189
8.3 secure deployment 193
8.3.1 server system security 194
8.3.2 database system security 194
8.4 conclusion 198
Chapter 2 storage engine interface explanation and practices 9th
9.1 architecture of MySQL plug-in storage engine 200
9.1.1 basic process 200
9.1.2 starting from the source code file 201
9.1.3 unexpected help 201
9.1.4 Handlerton 202
9.1.5 Handler class 204
9.1.6 Archive storage engine
Simple analysis 208
9.2 develop my storage engine --
Lillian (template profiling) 209
9.2.1 underlying I/O 209
9.2.2 Lillian_data class 209
9.2.3 Lillian_index class 217
9.3 develop my storage engine --
Lillian (code writing) 232
9.3.1 Stage 1: Initialize storage engine 232
9.3.2 Stage 2: Table operation 238
9.3.3 Stage 3: Read and Write Data 243
9.3.4 Phase 4: Modify and delete data 245
9.3.5 Stage 5: indexing 248
9.4 conclusion 261
Chapter 2 storage engine 10th
10.1 MySQL metadata file -- frm 264
10.2 MyISAM storage engine 270
10.2.1 MyISAM architecture 270
10.2.2 data file (. MySQL) 271
10.2.3 index file (. MYI) 277
10.3 InnoDB Storage engine 288
10.3.1 InnoDB's purpose and
Main functions and features 288
10.3.2 InnoDB architecture and
Code layout 288
10.3.3 InnoDB file 291
10.3.4 InnoDB record structure 292
10.3.5 structure of the InnoDB page 296
10.4 conclusion 300
Chapter 2 MySQL Log functions and implementation analysis 11th
11.1 Error Log 302
11.1.1 Error Log 302
11.1.2 Error Log initialization 303
11.1.3 error log record implementation 305
11.2 common log 307
11.2.1 General log function introduction 307
11.2.2 General log initialization 310
11.2.3 normal logging 311
11.3 slow query log 312
11.3.1 overview of slow query log 312
11.3.2 initialize the slow query log 313
11.3.3 Implementation of slow query logs: 314
11.4 binary log 317
11.4.1 binary log feature 317
11.4.2 binary log initialization 320
11.4.3 binary log implementation 321
11.5 conclusion 322
Chapter 2 other subsystems 12th
12.1 Replication subsystem 324
12.1.1 Replication infrastructure
Description 324
12.1.2 implementation principle of Replication
Summary 325
12.1.3 Replication thread 326
12.1.4 functional file of Replication 326
12.1.5 composition of the Replication subsystem 327
12.1.6 principles of Replication 327
12.1.7 summary 342
12.2 error message subsystem 342
12.2.1 implementation principle of error message 342
12.2.2 error message consistency 344
12.2.3 how to add error messages in different versions: 345
12.2.4 summary 345
Appendix 1 getting started with Bazaar 347
Appendix 2 MySQL source code distribution overview 354
References... 371
[Preface]Background.
I have been involved in MySQL for several years without knowing it. Like other friends who are interested in it, I have also gone through the process of learning-Application-focus-and re-learning. That is to say, I have gone from nothing to nothing, and then to a glimpse of the path, and I feel that my heart has gone down this road. Fortunately, the company provided the opportunity to learn and practice MySQL and the environment.
When I first came up with the idea of accumulating and writing my MySQL experience into a book, I had a lot of thoughts and had no idea where to start. I really don't want to fall into the embarrassment of MySQL's traditional books. I write such a book in a box. Yes, my predecessors wrote this article, but since I want to write something special, how should I position my book? To tell the truth, this is indeed a great deal of thinking.
Throughout the book sea, MySQL-related books mostly stop at database management and database applications, so far there is no in-depth discussion of its principles and kernel materials. Finally, I realized that I could look at MySQL from a new perspective. Developing the kernel is not my job, but studying the kernel is my hobby. Over the years, I have been collecting kernel-related anecdotes, including Linux, Solaris, and MySQL. Relying on these accumulated materials, I can write a book on kernel learning. Essentially, this book describes how the MySQL kernel is designed and implemented, and the information provided tends to be practical. Through this book, you can do some database kernel development work, and even customize the database you need. Generally, IT practitioners focus on effectiveness. Therefore, this is a practical book that should be interesting, easy to read, and helpful.
I hope readers can learn more about the highlights of the MySQL kernel from this book, including the written and unwritten features, it is also hoped that the readers will try to improve the rapidly developing MySQL kernel code from reading this book and reading the kernel code. If you are just interested in reading and entertaining, you may also want to have fun. Of course, if you need a book with "core" or "core" on the bookcase to show your superior technology and senior position, this is also a good choice.
No matter what you want to learn about MySQL, I sincerely hope that you will like my book.
Book Structure
This book is divided into 12 chapters.
In chapter 1, we will start from the historical origins of MySQL and talk about its advantages and disadvantages. In the second chapter, although the database theory seems empty and a little tasteless, its practice is eventually converted to the theory. Do not build a high platform in the sand.
In chapter 3, we dive into the original MySQL source code and explain the key classes and Algorithms in chapter 4, so that readers can strategize the vast sea of code without fear.
From Chapter 5 to the end, we will arrange the troops according to each subsystem. From the network subsystem in Chapter 5 to the replication subsystem in Chapter 12th, the reader can chapter as the camp. Among these chapters, the ninth chapter is the most interesting. In chapter 9, we work with readers to compile our MySQL storage engine Lillian...
Chapter 8 and Chapter 12 of the book and Chapter 1 and Chapter 2 of the appendix are edited by Zhang Hai. Huang Jianchang wrote chapter 11th in his busy schedule and completed other chapters by me.
Intended audience
We believe this book will provide a useful reference for all kinds of technical staff who are studying and using MySQL databases at work.
In this book, application developers can find out how MySQL Databases following JDBC and ODBC can store and read information. This information helps developers understand the performance, impact, and implementation details of each data call when developing foreground applications. Resource Management, network and communication, and query and resolution optimization are the most useful chapters for such readers.
Project management personnel and system analysts can learn the behavioral characteristics of major MySQL subsystems and understand the strengths and weaknesses of MySQL. The system design can provide a reference for better selection of suitable databases.
Technical support staff, consultants, and DBAs will find a lot of detailed information about MySQL implementation. The source code parsing and data structure provided in each chapter can help debug, optimize, and operate MySQL databases.
If you are a MySQL user just getting started, you can find the overall framework Overview In the first few chapters, which will have a feeling of a small view. We can also deepen our understanding of MySQL through the learning practices in the following chapters.
In addition to users in the technical community, those who study the database system in the school will find that this book is a good reference to combine theory with practice. MySQL database is a highly available, fast, and widely used database system. With the introduction of MySQL 5.4, it is suitable for different workloads, from a single-processor desktop to an SMP system with a large amount of memory and CPU threads. MySQL databases provide Cluster 7.0 high availability and scalability for commercial data processing, Web Services, and telecommunications systems, which is second to none in the industry. A lot of knowledge can be learned to study this database system.
Related materials and CD
As a supplement to this book, readers can find the latest content on our website (http://www.mysqlsystems.com), including updated materials, tools we use, and related training documents. We will regularly update the website to timely feedback MySQL Kernel updates and information. In addition, the website has a forum (http://forum.mysqlsystems.com) on the book, readers can interact with the author of the book on the topic of the book.
In addition, you can find the software and code used in the book on the CD-Rom presented in this book.
Tools and platforms
Operating System
It should be pointed out that the operating systems used by several authors in this book are different, including OpenSolaris, Linux, and Windows XP. Therefore, in different chapters of this book, you will see that we use various operating system tools to view or edit the source code. Fortunately, in most cases, the differences in operating systems do not affect the reader's reading and understanding of this book. Unless otherwise specified, we use Linux for demonstration, because we speculate that most of the readers of this book use Linux extensively in their work and study.
MySQL source code
People who write IT books are always faced with a common problem: books are not published yet, and the content is outdated. When we compare all the MySQL version codes, we believe that the code of MySQL 5.1 is the most complete and authentic response to the actual situation of MySQL. No significant changes were made to the source code architecture and layout in 5.4 and 6.0. Although Falcon and other storage engines are added to 6.0, the main line is consistent with 5.1. The 5.4 optimization is mainly implemented for the InnoDB Storage engine and does not change the overall architecture of the existing system. Therefore, we will not pay too much attention here.
In this book, although we use MySQL 5.1 as an example, it is not limited to this version. Significant updates to version 6.0 or 5.4 will also be reflected in this book and will be particularly noted when referenced.
Author's words
MySQL is praised for its free spirit. All source code is at your fingertips. Mr. Hou Jie's cloud: "the source code is in front, and there is no secret ". Yes, but when we face it, why is the steep learning curve created by this scale and level always stuck? Many of my friends have fallen, even though the database World is flourishing, even though the MySQL kernel is boundless and vast. But how can the haze in the eyes be dispelled by the sun? If you are ambitious, you may lose your mind, but where is your path?
It is not easy to learn the MySQL kernel. The reason why it is difficult to learn lies in the huge scale and complexity. If the scale is large, it is difficult to show the original face and integrate the whole system. Naturally, it is not easy to find the starting point. If there are many layers, it will be dazzled. How can we make people outline it?
Starting from the whole, this book gives readers an overall grasp of the core subsystems of the MySQL kernel, including what services they provide, and how they implement such services. This book also contains a lot of information that MySQL kernel developers need to use during development, including debugging technology and precautions. Based on this book, if you focus on understanding the kernel, You can further study the source code itself.
Be prepared to start your kernel journey!
Thank you
I once could not understand why every book had to say "grateful" or "thank you" in the preface. I have been deeply touched by the sincere help of many people since I wrote this book for a few months. It turned out to be the most appropriate part.
I would like to thank Mike and Gary, bosses who have made painstaking efforts to complete this book, and their colleagues Sarah. In addition, I have reviewed this book technically including Liu Wensheng, Zhang Wenlong, Gu Yongchun, Wang Qiang, and Hunter.
I would also like to thank all my colleagues and friends who have spent time reading this book, met me, and never met me. They are Sun ERI's president Kevin Song, Sun Asia Pacific's chief MySQL consultant Kajiyama in Japan, and Han Lei, general manager of CSDN. They slaughtered 100 typos of Li Kai. Zhou Linzhi, han Xiaomei, the old student who modified the content, told me about the Monty of MySQL history late at night.
I would like to express my special thanks to Chen Dacheng, the editor of Tsinghua press, and other staff.
Most importantly, thanks to Lillian for its support, which is why I name my storage engine Lillian ....
[Preface]Sequence (1)
I was surprised to hear that two people in the ISVE group are writing books on the MySQL kernel. Indeed, Sun's Chinese Engineering Research Institute wrote a book to reveal the software kernel, which is a very noteworthy thing.
A few years ago, we have been concerned about the development of MySQL. Since Sun's acquisition of MySQL, MySQL is still the most popular open source database software. The open-source and free MySQL allows you to build a database with a huge capacity or online transaction system at almost zero cost. Coupled with the technical support of Sun and countless Community Elites, it is not so popular!
However, over the years, MySQL Chinese data has been scarce, basically at the initial and intermediate level, and lacks sufficient depth. People often know the software, but do not know why. This leads to a relatively small number of senior MySQL database talents in China.
MySQL core insider this book is an excellent guide for the study of MySQL kernel, truly "teach people to fish ".
Open the source code of MySQL for a moment, you can find that its scale is large, such as afangu-"over three hundred miles, isolated Days"; its relationship is complex, it can be said that "the corridor waist is back, the teeth are high, each holding the terrain, intrigue ". This is undoubtedly a problem for those who want to have a deep understanding of MySQL.
In MySQL core insider, the author splits MySQL into multiple functional subsystems by dividing MySQL into complex MySQL kernels. In each chapter, the author analyzes the difficulties of the subsystem. Therefore, you are not only faced with naked code, but with a truly elegant design art.
At the same time, you must practice it yourself: the shortcut to understanding a part of the kernel is to modify it so that you can see the deep mechanism of the kernel beyond the Code itself. With patience and perseverance, the readers will go through the difficult times until they have read the last chapter of this book, and will feel the joy of "Looking back, but that person is in the dark!
I believe that, under the guidance of this book, the level of MySQL users and supporters in the Chinese region can be further improved.
Kevin Song
Sun, president of the Chinese engineering institute
Order (2)
MySQL is known as the world's most popular open source database in the industry. It provides a new feasible solution for cost-saving database management systems.
MySQL has many advantages, not only competitive in price, but also as a "Open Source" leader, it achieves full code openness and provides professional "enterprise-level adaptability" capabilities. The open-source availability of MySQL allows all users and engineers to understand the architecture and behavior at the code level.
Sun acquired MySQL's open-source business and provided experienced technical support engineers to ensure high-quality services for customers.
To understand all the details and advantages of the source code level, engineers should clarify the basic principles of the MySQL kernel. I believe this book will be able to implement this task if I wish dingze, a well-trained MySQL expert, to help engineers and readers understand MySQL's philosophy and design. Let us enjoy ourselves in the open-source mountain world.
I wish you success!
Asia Pacific head MySQL Consultant
Lushan longfu
[Media comment]I was surprised to hear that two people in the ISVE group are writing books on the MySQL kernel. People who never thought about SUN's Chinese Engineering Research Institute are also interested in writing core books. Now when this book is fully presented to me, I'm sure they're not joking. However, over the years, MySQL Chinese data has been scarce, basically at the initial and intermediate level, and lacks sufficient depth. People often know it, but do not know why. This leads to a relatively small number of senior MySQL database talents in China. MySQL core insider this book is an excellent guide for the study of MySQL kernel, truly "teach people to fish "..
Kevin Song-Sun, president of the Chinese engineering institute
MySQL is known as the world's most popular open source database in the industry. It provides a new feasible solution for cost accounting database management systems. Sun acquired MySQL's Open Source Business by providing customers with high-quality services to provide experienced technical support engineers. To understand all the details and benefits of the source code level, engineers should understand the basic principles of the MySQL kernel. I believe this book has completed this task, and I wish dingze, an accomplished MySQL expert, helps engineers and readers understand MySQL's philosophy and design. Enjoy the free world of open source code. Let's celebrate this possibility!
Asia Pacific head MySQL consultant ..
Longshan Region
As a developer, you should not only use tools, but understand their principles and essence. The best way to understand the principle of a tool is to start with the source code. MySQL core insider opens a door for domestic developers who want to understand the core mechanism of MySQL. The author combines theory and practice, the system architecture and core code are elaborated in detail. This book is a must-have book for technicians who love MySQL open-source software and who like to answer questions.
-- Wang Zhigang, financial architect
MySQL core story is the first book in China to parse MySQL source code. It deeply reveals the internal mechanism of MySQL, describes the association of MySQL subsystems, and provides examples to compile a custom storage engine, I believe that the technical staff engaged in MySQL-related work can benefit from this.
--- Baidu senior DBA Wu shizhan
