Detailed description of the data structure and algorithm principles behind MySQL indexes

Php Chinese network (www.php.cn) provides the most comprehensive basic tutorial on programming technology, introducing HTML, CSS, Javascript, Python, Java, Ruby, C, PHP, basic knowledge of MySQL and other programming languages. At the same time, this site also provides a large number of online instances, through which you can better learn programming... Summary

This article takes MySQL database as the research object and discusses some topics related to database indexes. In particular, MySQL supports many storage engines, and various storage engines also support different indexes. Therefore, MySQL database supports multiple index types, such as BTree indexes and hash indexes, full-text index. To avoid confusion, this article will only focus on the B-tree index, because this is an index that is commonly used in MySQL. This article will not discuss hash indexes and full-text indexes for the moment.

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

The first part mainly discusses the mathematical basis of MySQL database indexes in terms of data structure and algorithm theory.

The second part discusses topics such as clustered index, non-clustered index, and covered index based on the index architecture in MySQL database MyISAM and InnoDB data storage engine.

The third part discusses the high-performance index usage policy in MySQL based on the above theoretical basis.

Data structure and Algorithm basics

The nature of indexes

MySQL officially defines indexes as: indexes are the data structures that help MySQL efficiently obtain data. Extract the sentence trunk to get the essence of the index: index is the data structure.

We know that database query is one of the most important functions of the database. We all want to query data as quickly as possible, so the database system designers will optimize the query algorithm. Of course, the most basic query algorithm is linear search. this algorithm with the complexity of O (n) is obviously bad when the data volume is large, fortunately, the development of computer science provides many better search algorithms, such as binary search and binary tree search. If you perform a slight analysis, you will find that each search algorithm can only be applied to a specific data structure. for example, binary search requires that the retrieved data be ordered, while binary search can only be applied to binary search trees, however, the organizational structure of the data itself cannot fully satisfy all kinds of data structures (for example, theoretically it is impossible to organize both columns in order at the same time, the database system also maintains data structures that meet specific search algorithms. these data structures reference (point to) data in some way, so that advanced search algorithms can be implemented on these data structures. This data structure is an index.

Let's look at an example:

Due to the characteristics of B-Tree, the algorithm for retrieving data by key in B-Tree is very intuitive: first, perform a binary search from the root node. If yes, the data of the corresponding node is returned, otherwise, recursive search is performed on the node pointed to by the pointer in the corresponding interval until the node is found or the null pointer is found. The former is found successfully, and the latter fails to be searched. The pseudo code for searching algorithms on B-Tree is as follows:

BTree_Search(node, key){  if(node == null) return null;    foreach(node.key)  {    if(node.key[i] == key) return node.data[i];    if(node.key[i] > key) return BTree_Search(point[i]->node);  }    return BTree_Search(point[i+1]->node);}  data = BTree_Search(root, my_key);

B-Tree has a series of interesting properties. for example, if a B-Tree with a degree of d has N keys indexed, the upper limit of its H is logd (N + 1) /2), retrieves a key, and the progressive complexity of finding the number of nodes is O (logdN ). From this point, we can see that B-Tree is a very efficient index data structure.

In addition, insertion and deletion of new data records will damage the nature of B-Tree. Therefore, when inserting and deleting data records, we need to split, merge, transfer, and other operations on the Tree to maintain the B-Tree nature. This article does not intend to fully discuss the B-Tree content, because many documents have already elaborated on the mathematical nature of B-Tree and the insertion and deletion algorithm, interested friends can find relevant materials in the reference column at the end of this article for reading.

B + Tree

B-Tree has many variants, the most common of which is B + Tree. for example, MySQL generally uses B + Tree to implement its index structure.

Compared with B-Tree, B + Tree has the following differences:

1. The maximum pointer value for each node is 2d instead of 2d + 1.

2. internal nodes do not store data, but only store keys. leaf nodes do not store pointers.

Is a simple B + Tree diagram.

2

In the official MySQL documentation, the page for accessing this database is http://dev.mysql.com/doc/employee/en/employee.html. This section describes the database in detail and provides and import methods. if you are interested in importing the database to your own MySQL, refer to the content in this article.

Principle and optimization of leftmost prefixes

The primary condition for using indexes efficiently is to know what kind of queries will use indexes. this problem is related to the "leftmost prefix principle" in B + Tree. the following example illustrates the leftmost prefix principle.

Here we will talk about the concept of Federated indexes. In the above article, we assume that the index only references a single column. In fact, the index in MySQL can reference multiple columns in a certain order. this index is called a joint index. generally, A federated index is an ordered tuples. each element is a column in a data table. In fact, relational algebra is required to strictly define an index. However, I do not want to discuss too many topics about relational algebra, this will be boring, so we will not strictly define it here. In addition, a single column index can be seen as a special case where the number of union index elements is 1.

Take the employees. titles table as an example. The following describes the indexes on the table:

SHOW INDEX FROM employees.titles;+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+| titles |     0 | PRIMARY |      1 | emp_no   | A     |    NULL |   | BTREE   || titles |     0 | PRIMARY |      2 | title    | A     |    NULL |   | BTREE   || titles |     0 | PRIMARY |      3 | from_date  | A     |   443308 |   | BTREE   || titles |     1 | emp_no  |      1 | emp_no   | A     |   443308 |   | BTREE   |+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

From the result, the primary index of the titles table is And a secondary index . To avoid the complexity of multiple indexes (MySQL SQL Optimizer is more complicated when multiple indexes are involved), we will drop the secondary index here:

ALTER TABLE employees.titles DROP INDEX emp_no;

In this way, you can focus on the index PRIMARY behavior.

Case 1: full column match.

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| id | select_type | table | type | possible_keys | key   | key_len | ref        | rows | Extra |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| 1 | SIMPLE   | titles | const | PRIMARY    | PRIMARY | 59   | const,const,const |  1 |    |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

Obviously, an index can be used when exact match is performed based on all columns IN the index (here, exact match refers to "=" or "IN" match. Note that indexes are theoretically sensitive to order, but the MySQL Query Optimizer automatically adjusts the conditional order of where clauses to use suitable indexes, for example, we will reverse the conditional order in where:

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| id | select_type | table | type | possible_keys | key   | key_len | ref        | rows | Extra |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+| 1 | SIMPLE   | titles | const | PRIMARY    | PRIMARY | 59   | const,const,const |  1 |    |+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

The results are the same.

Case 2: match the leftmost prefix.

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+| 1 | SIMPLE   | titles | ref | PRIMARY    | PRIMARY | 4    | const |  1 |    |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

When the query condition exactly matches one or more columns on the left of the index, for example Or , So it can be used, but only part of it can be used, that is, the leftmost prefix of the condition. The above query shows that the PRIMARY index is used from the analysis results, but the key_len is 4, indicating that only the first column prefix of the index is used.

Case 3: The exact match of the column in the index is used for the query condition, but a condition in the middle is not provided.

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra    |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| 1 | SIMPLE   | titles | ref | PRIMARY    | PRIMARY | 4    | const |  1 | Using where |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

In this case, the index usage is the same as that in case 2. because the title is not provided, only the first column of the index is used for the query, and the from_date is also in the index, however, the title does not exist and cannot be connected to the left prefix. Therefore, you need to scan and filter the result from from_date (because emp_no is unique, so scanning does not exist ). If you want from_date to use indexes instead of where filter, you can add a secondary index. In this case, the above query will use this index. In addition, you can use an optimization method called "isolate columns" to fill in the "pitfall" between emp_no and from_date.

First, let's take a look at the title with several different values:

SELECT DISTINCT(title) FROM employees.titles;+--------------------+| title       |+--------------------+| Senior Engineer  || Staff       || Engineer      || Senior Staff    || Assistant Engineer || Technique Leader  || Manager      |+--------------------+

There are only 7 types. When the number of columns that become "pitfall" is relatively small, you can consider using "IN" to fill this "pitfall" to form the leftmost prefix:

EXPLAIN SELECT * FROM employees.titlesWHERE emp_no='10001'AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')AND from_date='1986-06-26';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref | rows | Extra    |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE   | titles | range | PRIMARY    | PRIMARY | 59   | NULL |  7 | Using where |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

The key_len value is 59, which indicates that the index is fully used. but from the type and rows, we can see that IN actually executes a range query. here we check 7 keys. Let's take a look at the performance comparison of the two types of queries:

SHOW PROFILES;+----------+------------+-------------------------------------------------------------------------------+| Query_ID | Duration  | Query                                     |+----------+------------+-------------------------------------------------------------------------------+|    10 | 0.00058000 | SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'||    11 | 0.00052500 | SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ...     |+----------+------------+-------------------------------------------------------------------------------+

The performance is improved a little after "filling in holes. If a large amount of data is left after filtering by emp_no, the latter has more obvious performance advantages. Of course, if there are many title values, it is not appropriate to fill in the pitfalls. secondary indexes must be created.

Case 4: The first column of the index is not specified in the query condition.

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows  | Extra    |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| 1 | SIMPLE   | titles | ALL | NULL     | NULL | NULL  | NULL | 443308 | Using where |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

Because it is not the leftmost prefix, indexes cannot be used for such queries.

Case 5: match the prefix string of a column.

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref | rows | Extra    |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE   | titles | range | PRIMARY    | PRIMARY | 56   | NULL |  1 | Using where |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

Indexes can be used at this time, but indexes cannot be used if the wildcard does not appear only at the end. (The original expression is incorrect. if the wildcard % does not appear at the beginning, the index can be used, but only one prefix may be used according to the specific situation)

Case 6: Query by range.

EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref | rows | Extra    |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE   | titles | range | PRIMARY    | PRIMARY | 4    | NULL |  16 | Using where |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

The index can be used for a range column (the leftmost prefix must be used), but the index cannot be used for the column after the range column. At the same time, the index can be used for a maximum of one range column. Therefore, if there are two range columns in the query condition, the index cannot be fully used.

EXPLAIN SELECT * FROM employees.titlesWHERE emp_no < 10010'AND title='Senior Engineer'AND from_date BETWEEN '1986-01-01' AND '1986-12-31';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref | rows | Extra    |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE   | titles | range | PRIMARY    | PRIMARY | 4    | NULL |  16 | Using where |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

We can see that the index is powerless to the second range index. It is particularly important to note that MySQL is an interesting place, that is, the range index and multi-value matching cannot be distinguished only by using explain, because both of them are displayed as range in type. At the same time, the use of "between" does not mean that it is a range query, for example, the following query:

EXPLAIN SELECT * FROM employees.titlesWHERE emp_no BETWEEN '10001' AND '10010'AND title='Senior Engineer'AND from_date BETWEEN '1986-01-01' AND '1986-12-31';+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref | rows | Extra    |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE   | titles | range | PRIMARY    | PRIMARY | 59   | NULL |  16 | Using where |+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

It seems that two range queries are used, but "BETWEEN" acting on emp_no is actually equivalent to "IN", that is, emp_no is actually a multi-value exact match. We can see that this query uses all three columns of the index. Therefore, you must be cautious about multi-value matching and range matching in MySQL. otherwise, MySQL may be confused.

Case 7: The query condition contains a function or expression.

Unfortunately, if a query condition contains a function or expression, MySQL does not use an index for this column (although some can be used in a mathematical sense ). For example:

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key   | key_len | ref  | rows | Extra    |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+| 1 | SIMPLE   | titles | ref | PRIMARY    | PRIMARY | 4    | const |  1 | Using where |+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

Although this query has the same function as in case 5, the left function cannot be used to apply an index to the title column, and the LIKE function can be used in case 5. Another example is:

EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows  | Extra    |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+| 1 | SIMPLE   | titles | ALL | NULL     | NULL | NULL  | NULL | 443308 | Using where |+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

Obviously, this query is equivalent to a function where emp_no is 10001. However, because the query condition is an expression, MySQL cannot use indexes for it. It seems that MySQL has not been smart enough to automatically optimize constant expressions. Therefore, when writing a query statement, try to avoid the expression appearing in the query. Instead, you should first perform manual algebra, converts a query statement to a non-expression query statement.

Index selection and prefix index

Since the index can speed up the query, is it necessary to create an index as long as it is required by a query statement? The answer is No. Although the index accelerates the query speed, the index also has a cost: the index file itself consumes storage space, and the index will increase the burden of inserting, deleting, and modifying records. In addition, mySQL also consumes resources to maintain indexes during runtime. Therefore, the more indexes, the better. We do not recommend creating indexes in two cases.

The first case is that the number of table records is relatively small. for example, if there are 1000 or 2000 or even hundreds of records in a table, you do not need to create an index, so that you can perform a full table scan for the query. As for how many records are counted, this individual has his own opinion. my personal experience is to use 2000 as the demarcation line. if the number of records does not exceed 2000, you can consider not to create an index, more than 2000 indexes can be considered as appropriate.

Another case where indexing is not recommended is that indexing is less selective. The so-called Index Selectivity refers to the ratio of non-repeated index values (also called Cardinality) to the number of table Records (# T:

Index Selectivity = Cardinality/# T

Obviously, the value range of selectivity is (0, 1]. the higher the selectivity, the greater the value of the index, which is determined by the nature of B + Tree. For example, in the employees. titles table used above, if the title field is frequently queried separately, do you need to create an index? let's take a look at its selectivity:

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;+-------------+| Selectivity |+-------------+|   0.0000 |+-------------+

The selection of the title is less than 0.0001 (the exact value is 0.00001579), so there is no need to create a separate index for it.

There is an index optimization policy related to index selectivity called Prefix index, that is, replacing the entire column with the column prefix as the index key. when the prefix length is appropriate, it can make the prefix index selectively close to the full-column index, and reduce the size and maintenance overhead of the index file because the index key becomes short. The following uses the table employees. employees as an example to describe how to select and use a prefix index.

From 2, we can see that the employees table has only one index. If we want to search for a person by name, we can only scan the entire table:

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+| id | select_type | table   | type | possible_keys | key | key_len | ref | rows  | Extra    |+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+| 1 | SIMPLE   | employees | ALL | NULL     | NULL | NULL  | NULL | 300024 | Using where |+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

If you frequently search for employees by name, the efficiency is obviously low, so we can consider creating indexes. There are two options: Or To check the selectivity of the two indexes:

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;+-------------+| Selectivity |+-------------+|   0.0042 |+-------------+ SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;+-------------+| Selectivity |+-------------+|   0.9313 |+-------------+

Apparently, the selectivity is too low, The selection is good, but the length of first_name and last_name is 30. Is there a way to balance the length and selectivity? You can use the first few characters of first_name and last_name to create an index. for example To see its selectivity:

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;+-------------+| Selectivity |+-------------+|   0.7879 |+-------------+

The selectivity is good, but the distance from 0.9313 is still a bit, so add the last_name prefix to 4:

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;+-------------+| Selectivity |+-------------+|   0.9007 |+-------------+

At this time, the selection is ideal, and the index length is only 18 The index is nearly half short. we will create the index with this prefix:

ALTER TABLE employees.employeesADD INDEX `first_name_last_name4` (first_name, last_name(4));

Execute the query by name again to compare and analyze the results before the index creation:

SHOW PROFILES;+----------+------------+---------------------------------------------------------------------------------+| Query_ID | Duration  | Query                                      |+----------+------------+---------------------------------------------------------------------------------+|    87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' ||    90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |+----------+------------+---------------------------------------------------------------------------------+

The performance improvement is significant, and the query speed is improved by more than 120 times.

Prefix indexes take into account both the index size and query speed. However, they cannot be used for order by and group by operations or Covering indexes (that is, when the index itself contains all the data required for the query, no longer accessing the data file itself ).

Primary key selection and insertion optimization for InnoDB

When using the InnoDB storage engine, if you do not have a special need, always use an auto-increment field that is not related to the business as the primary key.

I often see posts or blogs discussing primary key selection. some people suggest using auto-incrementing primary keys unrelated to the business. some people think it is unnecessary to use a unique field such as student ID or ID card number as the primary key. Whatever arguments are supported, most arguments are at the business layer. From the perspective of database index optimization, using the InnoDB engine instead of using the auto-incrementing primary key is definitely a bad idea.

The index implementation of InnoDB has been discussed above. InnoDB uses clustered indexes, and data records are stored on the leaf nodes of the primary index (a B + Tree. This requires that each data record in the same leaf node (the size is a memory page or disk page) be stored in the primary key order. Therefore, when a new record is inserted, mySQL inserts an appropriate node and location based on its primary key. if the page reaches the load factor (InnoDB defaults to 15/16), a new page (node) is created ).

If the table uses an auto-incrementing primary key, the records are added to the subsequent positions of the current index node each time a new record is inserted. when a page is full, A new page is automatically created. As shown in:

4

At this time, MySQL had to move the data in order to insert the new record to the appropriate location, and even the target page may have been written back to the disk and cleared from the cache. at this time, it had to read back from the disk, this adds a lot of overhead, and frequent movement and paging operations cause a lot of fragmentation, resulting in a compact index structure. later, we had to use optimize table to recreate the TABLE and OPTIMIZE the page filling.

Therefore, if you can, use the auto-incrementing field on InnoDB as the primary key.

Postscript

This article has been written on and off for half a month. The main content is the above. It is undeniable that this article has been mentioned on paper to some extent, because my use of MySQL belongs to the Cainiao level, and I do not have much experience in database optimization, it's a bit difficult to talk about database index optimization here. This is my personal study note.

In fact, database index optimization is a technical activity and cannot rely solely on theory. because the actual situation is ever-changing, and MySQL itself has a complicated mechanism, such as query optimization policies and implementation differences between various engines make the situation more complex. At the same time, these theories are the basis of index optimization. only by understanding the theory can we reasonably infer the optimization strategy and understand the mechanisms behind it, then, we will continue to experiment and explore in practice to achieve the goal of using MySQL indexes efficiently.

In addition, MySQL indexes and their optimization cover a wide range. This article only involves part of them. For example, this article does not cover the topic of index optimization and Covering index (Covering index) related to order, in addition to B-Tree indexes, MySQL does not include hash indexes and full-text indexes supported by different engines. If you have the opportunity, I hope to add some parts not covered in this article.

References

[1] Baron Scbwartz, translated by Wang Xiaodong; High Performance MySQL; electronics industry Press, 2010

[2] Michael Kofler, translated by Yang Xiaoyun; The Definitive Guide to MySQL5; People's Post and Telecommunications Press, 2006

[3] Jiang Chengyao; MySQL Technology Insider-InnoDB storage engine; Mechanical Industry Press, 2011

[4] D Comer, Ubiquitous B-tree; ACM Computing Surveys (CSUR), 1979

[5] Codd, E. f. (1970 ). "A relational model of data for large shared data banks ". communications of the ACM, Vol. 13, No. 6. pp. 377-387

[6] MySQL5.1 Reference Manual-http://dev.mysql.com/doc/refman/5.1/zh/index.html

For more details about the data structure and algorithm principles behind MySQL indexes, please follow the PHP Chinese website!