The data structure and algorithm principle behind MySQL index

This paper takes MySQL database as the research object and discusses some topics related to database indexing. In particular, MySQL supports many storage engines, and the various storage engines support the indexes differently, so the MySQL database supports multiple index types such as btree indexes, hash indexes, full-text indexes, and so on. To avoid confusion, this article will focus only on the Btree index, as this is the primary index for dealing with MySQL, and the hash index and the full-text index are not discussed in this article.

The main content of the article is divided into three parts.

The first part mainly discusses the mathematical basis of MySQL database index from the data structure and algorithm theory level.

The second part discusses the topics such as clustered indexes, nonclustered indexes, and overlay indexes, in conjunction with the schema of indexes in the MyISAM and InnoDB data storage engines in MySQL database.

The third part discusses the strategy of high performance using indexes in MySQL based on the theoretical basis above.

The nature of data structure and algorithm base index

The official MySQL definition of an index is: index is the data structure that helps MySQL to get data efficiently. By extracting the skeleton of a sentence, you can get the essence of the index: The index is the data structure.

We know that database query is one of the most important functions of database. We all want to query the data as fast as possible, so the designers of the database system are optimized from the point of view of the query algorithm. The most basic query algorithm, of course, is sequential lookup (linear search), the complexity of the O (n) algorithm is obviously bad when the volume of data is large, fortunately, the development of computer science provides a lot of better search algorithms, such as binary search, Binary tree search (binary trees search), and so on. If you look at it a little bit, you will find that each lookup algorithm can only be applied to a particular data structure, such as a binary lookup requires an orderly retrieval of data, while a binary tree lookup can only be applied to a binary lookup tree, but the data itself cannot be fully organized to meet a variety of data structures (for example, It is theoretically impossible to organize both columns sequentially, so in addition to the data, the database system maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index.

http://gyjkyy.51sole.com/

http://gyjk.b2b168.com/

Http://www.wenbing.cn/gyjk

The data structure and algorithm principle behind MySQL index