Detailed explanation of MySQL index principles and optimization

Preface

This article was written by a big shot from Meituan. It's pretty good and I'd like to share it with you. The SQL statements embedded in HTML in the code are written in the Java framework. You only need to understand the SQL statements to be executed.

background

With its excellent performance, low cost and abundant resources, MySQL has become the preferred relational database for most Internet companies. Although it has excellent performance, as the saying goes, "a good horse deserves a good saddle", how to use it better has become a compulsory course for development engineers. We often see requirements such as "proficient in MySQL", "SQL statement optimization", and "understanding database principles" in job descriptions. We know that in general application systems, the read-write ratio is about 10:1, and insertion operations and general update operations rarely have performance issues. The most common and most problematic operations are some complex query operations, so the optimization of query statements is obviously a top priority.

Since July 2013, I have been working on slow query optimization in Meituan’s core business system department, covering more than ten systems, and have solved and accumulated hundreds of slow query cases. As the business becomes more complex, the problems encountered are varied and bizarre. This article aims to explain the principles of database indexing and how to optimize slow queries from the perspective of a development engineer.

<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> task <span class="hljs-keyword">where</span> <span class="hljs-keyword">status</span>=<span class="hljs-number">2</span> <span class="hljs-keyword">and</span> operator_id=<span class="hljs-number">20839</span> <span class="hljs-keyword">and</span> operate_time><span class="hljs-number">1371169729</span> <span class="hljs-keyword">and</span> operate_time<<span class="hljs-number">1371174603</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">type</span>=<span class="hljs-number">2</span>;

System users reported that a function was becoming increasingly slow, so engineers found the SQL above.

And he came to me excitedly and said, "This SQL needs to be optimized. Add indexes to every field for me."

I was surprised and asked, "Why do I need to index every field?"

“It will be faster if we add indexes to all query fields,” the engineer said confidently.

"In this case, you can create a joint index. Because it is a leftmost prefix match, the operation_time needs to be placed at the end, and other related queries need to be taken, and a comprehensive evaluation needs to be done."

"Joint index? Leftmost prefix match? Comprehensive evaluation?" The engineer couldn't help but fall into deep thought.

In most cases, we know that indexes can improve query efficiency, but how should we create indexes? What is the order of the indexes? Many people only know roughly. In fact, it is not difficult to understand these concepts, and the principles of indexing are far less complicated than imagined.

Index Purpose

The purpose of the index is to improve query efficiency. It can be compared to a dictionary. If we want to look up the word "mysql", we must locate the letter m, then find the letter y from the bottom to the bottom, and then find the rest of sql. If there is no index, you may need to look through all the words to find what you want. What if I want to find a word that starts with m? Or words starting with ze? Do you feel that this task cannot be completed without an index?

Indexing principle

In addition to dictionaries, examples of indexes can be found everywhere in life, such as train schedules at train stations, book catalogs, etc. Their principles are the same, which is to filter out the final desired results by continuously narrowing the scope of the data you want to obtain, and at the same time turn random events into sequential events. That is, we always lock the data through the same search method.

The same is true for databases, but it is obviously much more complicated because it not only faces equality queries, but also range queries (>, <, between, in), fuzzy queries (like), union queries (or), and so on. How should the database choose to deal with all the problems? Let’s think back to the dictionary example. Can we divide the data into segments and then query them in segments? The simplest way is to divide 1,000 pieces of data into the first section with numbers 1 to 100, the second section with numbers 101 to 200, and the third section with numbers 201 to 300. Then, to check the 250th piece of data, you only need to find the third section, thus eliminating 90% of the invalid data at once. But what if there are 10 million records, how many segments should they be divided into? Students who have a basic knowledge of algorithms will think of the search tree, which has an average complexity of lgN and has good query performance. But we have overlooked a key issue here. The complexity model is based on the same operation cost each time. The database implementation is relatively complex, and the data is stored on the disk. In order to improve performance, part of the data can be read into the memory for calculation each time. Because we know that the cost of accessing the disk is about 100,000 times that of accessing the memory, a simple search tree is difficult to meet complex application scenarios.

Disk IO and read-ahead

We mentioned disk access earlier, so here is a brief introduction to disk IO and pre-reading. Disk reading relies on mechanical movement. The time spent on each data read can be divided into three parts: seek time, rotational delay, and transmission time. Seek time refers to the time required for the magnetic arm to move to the specified track, and mainstream disks are generally below 5ms; rotational delay is the disk rotation speed we often hear about. For example, a disk with 7200 rpm means it can rotate 7200 times per minute, which means it can rotate 120 times per second. The rotational delay is 1/120/2 = 4.17ms; transmission time refers to the time it takes to read or write data from the disk, which is generally a few tenths of a millisecond and can be ignored compared to the first two times. So the time to access a disk, that is, the time for a disk IO, is about 5+4.17 = 9ms, which sounds good, but you have to know that a 500-MIPS machine can execute 500 million instructions per second, because instructions rely on the nature of electricity. In other words, the time to execute an IO can execute 400,000 instructions. Databases often contain hundreds of thousands, millions, or even tens of millions of data. 9 milliseconds each time is obviously a disaster. The following figure is a comparison of computer hardware delays for your reference:

various-system-software-hardware-latencies

Considering that disk IO is a very expensive operation, the computer operating system has made some optimizations. During an IO, not only the data at the current disk address, but also the adjacent data are read into the memory buffer. This is because the principle of local pre-reading tells us that when the computer accesses data at an address, the data adjacent to it will also be accessed quickly. The data read each time by IO is called a page. The specific size of a page depends on the operating system, which is usually 4k or 8k. That is, when we read data in a page, only one IO actually occurs. This theory is very helpful for the design of index data structure.

Index data structure

Earlier we talked about examples of indexes in life, the basic principles of indexes, the complexity of databases, and the relevant knowledge of operating systems. The purpose is to let everyone understand that any data structure does not come out of thin air, and it must have its background and usage scenarios. Let's summarize now what we need this data structure to do. In fact, it is very simple, that is: each time you look up data, control the number of disk IO times to a very small order of magnitude, preferably a constant order of magnitude. So we wonder if a highly controllable multi-way search tree can meet the needs? In this way, b+ tree came into being.

Detailed explanation of b+ tree

b+tree

As shown in the figure above, it is a b+ tree. For the definition of b+ tree, please refer to B+ tree. Here we will only talk about some key points. The light blue block is called a disk block. You can see that each disk block contains several data items (shown in dark blue) and pointers (shown in yellow). For example, disk block 1 contains data items 17 and 35, and contains pointers P1, P2, and P3. P1 represents a disk block less than 17, P2 represents a disk block between 17 and 35, and P3 represents a disk block greater than 35. The real data exists in the leaf nodes 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, and 99. Non-leaf nodes do not store real data, but only store data items that guide the search direction. For example, 17 and 35 do not actually exist in the data table.

The search process of b+ tree

As shown in the figure, if you want to find data item 29, disk block 1 will be loaded from the disk to the memory first. At this time, an IO occurs. A binary search is used in the memory to determine that 29 is between 17 and 35. The P2 pointer of disk block 1 is locked. The memory time is very short (compared to the disk IO) and can be ignored. Disk block 3 is loaded from the disk to the memory through the disk address of the P2 pointer of disk block 1. The second IO occurs. 29 is between 26 and 30. The P2 pointer of disk block 3 is locked. Disk block 8 is loaded into the memory through the pointer. The third IO occurs. At the same time, a binary search is performed in the memory to find 29, and the query ends. A total of three IOs are performed. The reality is that a 3-layer b+ tree can represent millions of data. If searching millions of data only requires three IOs, the performance improvement will be huge. If there is no index, each data item will require an IO, then a total of millions of IOs will be required, which is obviously very costly.

B+ tree properties

Through the above analysis, we know that the number of IO times depends on the height h of b+number. Assuming that the data in the current data table is N, and the number of data items in each disk block is m, then h=㏒(m+1)N. When the data volume N is constant, the larger the m, the smaller the h; and m = disk block size/data item size. The disk block size is the size of a data page, which is fixed. If the space occupied by the data item is smaller and the number of data items is greater, the height of the tree is lower. This is why each data item, i.e. the index field, should be as small as possible. For example, int occupies 4 bytes, which is half of bigint 8 bytes. This is why the b+ tree requires that the real data be placed in the leaf nodes rather than the inner nodes. Once placed in the inner nodes, the data items of the disk blocks will drop significantly, causing the tree to increase in height. When the data item is equal to 1, it will degenerate into a linear list.

When the data items of the b+ tree are composite data structures, such as (name, age, sex), the b+ tree builds the search tree in order from left to right. For example, when data such as (Zhang San, 20, F) is retrieved, the b+ tree will first compare the name to determine the next search direction. If the names are the same, then the age and sex are compared in turn to finally get the retrieved data. However, when data without a name such as (20, F) comes, the b+ tree does not know which node to check next, because name is the first comparison factor when building the search tree, and it is necessary to search based on the name first to know where to query next. For example, when retrieving data like (Zhang San, F), the b+ tree can use name to specify the search direction, but the next field age is missing, so it can only find the data with the name equal to Zhang San, and then match the data with the gender of F. This is a very important property, namely the leftmost matching feature of the index.

The principle of MySQL index is relatively boring. You only need to have a perceptual understanding of it and do not need to understand it very thoroughly and deeply. Let’s look back at the slow query we talked about at the beginning. After understanding the index principle, do you have any ideas? Let's first summarize the basic principles of indexing:

Several major principles for building indexes: the leftmost prefix matching principle, a very important principle. MySQL will keep matching to the right until it encounters a range query (>, <, between, like), and then stop matching. For example, if a = 1 and b = 2 and c > 3 and d = 4, if you build an index in the order of (a, b, c, d), d will not be used in the index. If you build an index in the order of (a, b, d, c), all of them can be used, and the order of a, b, d can be adjusted arbitrarily. = and in can be in any order, for example, a = 1 and b = 2 and c = 3. You can create an (a,b,c) index in any order, and the MySQL query optimizer will help you optimize it into a form that the index can recognize. Try to choose columns with high discrimination as indexes. The formula for discrimination is count(distinct col)/count(*), which indicates the ratio of non-duplicate fields. The larger the ratio, the fewer records we need to scan. The discrimination of a unique key is 1, while some status and gender fields may have a discrimination of 0 in the face of big data. Someone may ask, is there any empirical value for this ratio? This value is difficult to determine due to different usage scenarios. Generally, we require the value of the field to be joined to be above 0.1, which means that an average of 10 records are scanned for each field. Index columns cannot participate in calculations. Keep the columns "clean". For example, if from_unixtime(create_time) = '2014-05-29', the index cannot be used. The reason is simple. The b+ tree stores the field values ​​in the data table. However, when searching, all elements need to be compared with the function, which is obviously too costly. So the statement should be written as create_time = unix_timestamp('2014-05-29'). Try to expand the index as much as possible and do not create a new index. For example, if there is already an index of a in the table, and you want to add an index of (a,b), you only need to modify the original index. Return to the slow query at the beginning

According to the leftmost matching principle, the index of the first SQL statement should be the joint index of status, operator_id, type, and operate_time; the order of status, operator_id, and type can be reversed, so I said that all related queries of this table should be found and analyzed comprehensively; for example, there are also the following queries:

<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> task <span class="hljs-keyword">where</span> <span class="hljs-keyword">status</span> = <span class="hljs-number">0</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">type</span> = <span class="hljs-number">12</span> <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;

<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*) <span class="hljs-keyword">from</span> task <span class="hljs-keyword">where</span> <span class="hljs-keyword">status</span> = <span class="hljs-number">0</span>;

Then it is very correct to create an index of (status, type, operator_id, operate_time) because it can cover all situations. This is the principle of the leftmost match of the index.

Query optimization tool – explain command

I believe everyone is familiar with the explain command. For specific usage and field meanings, please refer to the official website explain-output. It should be emphasized here that rows is the core indicator. Most statements with small rows must be executed very quickly (there are exceptions, which will be discussed below). Therefore, optimization statements are basically optimizing rows.

Basic steps for slow query optimization: Run the query first to see if it is really slow. Note that you should set the SQL_NO_CACHEwhere condition for single table query and lock the minimum return record table. This sentence means to apply the where clause of the query statement to the table with the smallest number of records returned. Start by querying each field of a single table to see which field has the highest discrimination. Explain to see if the execution plan is consistent with the expectations in step 1 (start querying from the table with fewer records). The SQL statement in the form of order by limit allows the sorted table to be searched first. Understand the business side's usage scenarios. Refer to the major principles of indexing when adding indexes. Observe the results. If they do not meet expectations, continue to analyze several slow query cases from step 0.

The following examples explain in detail how to analyze and optimize slow queries.

How to write complex sentences

In many cases, we write SQL just to implement functions. This is only the first step. Different statement writing methods often have essential differences in efficiency. This requires us to have a very clear understanding of MySQL's execution plan and index principles. Please see the following statement:

<span class="hljs-keyword">select</span> <span class="hljs-keyword">distinct</span> cert.emp_id <span class="hljs-keyword">from</span> cm_log cl <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> ( <span class="hljs-keyword">select</span> emp.id <span class="hljs-keyword">as</span> emp_id, emp_cert.id <span class="hljs-keyword">as</span> cert_id <span class="hljs-keyword">from</span> employee emp <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> emp_certificate emp_cert <span class="hljs-keyword">on</span> emp.id = emp_cert.emp_id <span class="hljs-keyword">where</span> emp.is_deleted=<span class="hljs-number">0</span> ) cert <span class="hljs-keyword">on</span> ( cl.ref_table=<span class="hljs-string">'Employee'</span> <span class="hljs-keyword">and</span> cl.ref_oid= cert.emp_id ) <span class="hljs-keyword">or</span> ( cl.ref_table=<span class="hljs-string">'EmpCertificate'</span> <span class="hljs-keyword">and</span> cl.ref_oid= cert.cert_id ) <span class="hljs-keyword">where</span> cl.last_upd_date >=<span class="hljs-string">'2013-11-07 15:03:00'</span> <span class="hljs-keyword">and</span> cl.last_upd_date<=<span class="hljs-string">'2013-11-08 16:00:00'</span>;

Run it first, 53 records in 1.87 seconds, and no aggregation statements are used, which is slow

53 rows in <span class="hljs-keyword">set</span> (<span class="hljs-number">1.87</span> sec)

explain

+<span class="hljs-comment">----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+<span class="hljs-comment">----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span>| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary || 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer || 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where || 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |+<span class="hljs-comment">----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span>

To briefly describe the execution plan, first, MySQL scans the cm_log table based on the idx_last_upd_date index to obtain 379 records; then it looks up the table and scans 63,727 records, which are divided into two parts. Derived means a constructed table, that is, a non-existent table, which can be simply understood as a result set formed by a statement, and the number behind it represents the statement ID. Derived2 indicates that the query with ID = 2 constructed a virtual table and returned 63727 records. Let's take a look at what the statement with ID = 2 does to return such a large amount of data. First, the employee table is fully scanned for 13,317 records. Then, the emp_certificate table is associated with the index emp_certificate_empid. Rows = 1 means that each association only locks one record, which is more efficient. After obtaining, it is associated with the 379 records of cm_log according to the rules. From the execution process, it can be seen that too much data is returned, and most of the returned data is not used by cm_log because cm_log only locks 379 records.

How to optimize it? You can see that we still have to join with cm_log after running, so can we join with cm_log before? A careful analysis of the statement reveals that the basic idea is that if the ref_table of cm_log is EmpCertificate, it is associated with the emp_certificate table, and if the ref_table is Employee, it is associated with the employee table. We can completely split it into two parts and connect them with union. Note that union is used here instead of union all because the original statement has "distinct" to get the unique record, and union happens to have this function. If there is no distinct in the original statement and deduplication is not required, we can directly use union all, because using union requires deduplication, which will affect SQL performance.

The optimized statements are as follows:

<span class="hljs-keyword">select</span> emp.id <span class="hljs-keyword">from</span> cm_log cl <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> employee emp <span class="hljs-keyword">on</span> cl.ref_table = <span class="hljs-string">'Employee'</span> <span class="hljs-keyword">and</span> cl.ref_oid = emp.id <span class="hljs-keyword">where</span> cl.last_upd_date >=<span class="hljs-string">'2013-11-07 15:03:00'</span> <span class="hljs-keyword">and</span> cl.last_upd_date<=<span class="hljs-string">'2013-11-08 16:00:00'</span> <span class="hljs-keyword">and</span> emp.is_deleted = <span class="hljs-number">0</span> <span class="hljs-keyword">union</span><span class="hljs-keyword">select</span> emp.id <span class="hljs-keyword">from</span> cm_log cl <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> emp_certificate ec <span class="hljs-keyword">on</span> cl.ref_table = <span class="hljs-string">'EmpCertificate'</span> <span class="hljs-keyword">and</span> cl.ref_oid = ec.id <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> employee emp <span class="hljs-keyword">on</span> emp.id = ec.emp_id <span class="hljs-keyword">where</span> cl.last_upd_date >=<span class="hljs-string">'2013-11-07 15:03:00'</span> <span class="hljs-keyword">and</span> cl.last_upd_date<=<span class="hljs-string">'2013-11-08 16:00:00'</span> <span class="hljs-keyword">and</span> emp.is_deleted = <span class="hljs-number">0</span>

You do not need to understand the business scenario, you only need to keep the results of the transformed statements consistent with those before the transformation.

Existing indexes can satisfy this requirement, and no index creation is required

Experimenting with the modified statement only takes 10ms, which is nearly 200 times faster!

+<span class="hljs-comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+<span class="hljs-comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where || 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where || 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where || 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | || 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where || NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |+<span class="hljs-comment">----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>53 rows in <span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec)

Identify application scenarios

The purpose of giving this example is to overturn our understanding of the distinctiveness of columns. Generally speaking, we believe that the more distinctive a column is, the easier it is to lock fewer records. However, in some special cases, this theory has limitations.

<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> stage_poi sp <span class="hljs-keyword">where</span> sp.accurate_result=<span class="hljs-number">1</span> <span class="hljs-keyword">and</span> ( sp.sync_status=<span class="hljs-number">0</span> <span class="hljs-keyword">or</span> sp.sync_status=<span class="hljs-number">2</span> <span class="hljs-keyword">or</span> sp.sync_status=<span class="hljs-number">4</span> );

Let's see how long it takes to run. 6.22 seconds for 951 pieces of data, which is really slow.

951 rows in <span class="hljs-keyword">set</span> (<span class="hljs-number">6.22</span> sec)

First explain, the number of rows reaches 3.61 million, and type = ALL indicates a full table scan.

+<span class="hljs-comment">----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+</span>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+<span class="hljs-comment">----+-------------+-------+------+--------------+-----+---------+------+---------+---------+</span>| 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | NULL | 3613155 | Using where |+<span class="hljs-comment">----+-------------+---+-------+-------------+------+---------+------+---------+---------+------+---------+</span>

All fields are queried to return the number of records. Since it is a single table query, 951 records have been made.

Make the number of explained rows as close to 951 as possible.

Take a look at the number of records where accurate_result = 1:

<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*),accurate_result <span class="hljs-keyword">from</span> stage_poi <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> accurate_result;+<span class="hljs-comment">----------+----------------+</span>| count(*) | accurate_result |+<span class="hljs-comment">----------+----------------+</span>| 1023 | -1 || 2114655 | 0 || 972815 | 1 |+<span class="hljs-comment">----------+----------------+</span>

We can see that the accuracy_result field has very low discrimination. The entire table has only three values: -1, 0, and 1. Even with the index, it is not possible to lock a particularly small amount of data.

Let's take a look at the sync_status field:

<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*),sync_status <span class="hljs-keyword">from</span> stage_poi <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> sync_status;+<span class="hljs-comment">----------+-------------+</span>| count(*) | sync_status |+<span class="hljs-comment">----------+-------------+</span>| 3080 | 0 || 3085413 | 3 |+<span class="hljs-comment">----------+-------------+</span>

The same discrimination is also very low, and theoretically, it is not suitable for indexing.

After analyzing the problem to this point, it seems that we have come to the conclusion that this table cannot be optimized. The distinction between the two columns is very low. Even with the addition of an index, it can only adapt to this situation and it is difficult to make a general optimization. For example, when sync_status 0 and 3 are evenly distributed, the number of locked records is also in the millions.

Communicate with the business side and look at the usage scenarios. This is how the business side uses this SQL statement. It scans the qualified data every five minutes, and changes the sync_status field to 1 after processing. The number of qualified records in five minutes is not too large, about 1,000. After understanding the business side's usage scenarios, optimizing this SQL becomes simple, because the business side ensures the imbalance of the data. If an index is added, most of the unnecessary data can be filtered out.

According to the index creation rules, use the following statement to create an index

<span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> stage_poi <span class="hljs-keyword">add</span> <span class="hljs-keyword">index</span> idx_acc_status(accurate_result,sync_status);

Observing the expected results, we found that it only takes 200ms, which is more than 30 times faster.

952 rows in <span class="hljs-keyword">set</span> (<span class="hljs-number">0.20</span> sec)

Let's review the process of analyzing the problem. Single-table queries are relatively easy to optimize. Most of the time, you only need to add indexes to the fields in the where condition according to the rules. If this is just a "brainless" optimization, some columns with very low differentiation and columns that should not be indexed will also be indexed, which will have a serious impact on the insertion and update performance, and may also affect other query statements. Therefore, the usage scenario of our fourth step of debugging SQL is very critical. Only when we know this business scenario can we better assist us in better analyzing and optimizing query statements.

Statements that cannot be optimized

<span class="hljs-keyword">select</span> c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) <span class="hljs-keyword">as</span> created_time, from_unixtime(c.last_modified) <span class="hljs-keyword">as</span> last_modified, c.last_modified_user_id <span class="hljs-keyword">from</span> contact c <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> contact_branch cb <span class="hljs-keyword">on</span> c.id = cb.contact_id <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> branch_user bu <span class="hljs-keyword">on</span> cb.branch_id = bu.branch_id <span class="hljs-keyword">and</span> bu.status <span class="hljs-keyword">in</span> ( <span class="hljs-number">1</span>, <span class="hljs-number">2</span>) <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> org_emp_info oei <span class="hljs-keyword">on</span> oei.data_id = bu.user_id <span class="hljs-keyword">and</span> oei.node_left >= <span class="hljs-number">2875</span> <span class="hljs-keyword">and&llt;/span> oei.node_right <= <span class="hljs-number">10802</span> <span class="hljs-keyword">and</span> oei.org_category = - <span class="hljs-number">1</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> c.created_time <span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> <span class="hljs-number">0</span> , <span class="hljs-number">10</span>;

Still a few steps.

First, let's see how long the statement takes to run. It took 13 seconds for 10 records, which is unbearable.

10 rows in <span class="hljs-keyword">set</span> (<span class="hljs-number">13.06</span> sec)

explain

+<span class="hljs-comment">----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span>| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+<span class="hljs-comment">----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span>| 1 | SIMPLE | oei | ref | idx_category_left_right,idx_data_id | idx_category_left_right | 5 | const | 8849 | Using where; Using temporary; Using filesort || 1 | SIMPLE | bu | ref | PRIMARY,idx_userid_status | idx_userid_status | 4 | meituancrm.oei.data_id | 76 | Using where; Using index || 1 | SIMPLE | cb | ref | idx_branch_id,idx_contact_branch_id | idx_branch_id | 4 | meituancrm.bu.branch_id | 1 | || 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 108 | meituancrm.cb.contact_id | 1 | |+<span class="hljs-comment">----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span>

From the execution plan, MySQL first checks the org_emp_info table to scan 8849 records, then uses the index idx_userid_status to associate with the branch_user table, then uses the index idx_branch_id to associate with the contact_branch table, and finally associates with the contact table using the primary key.

The number of rows returned is very small, and no abnormality is seen. When we look at the statement, we find that there is an order by + limit combination at the end. Could it be that the amount of sorting is too large? So we simplify the SQL, remove the order by and limit at the end, and see how many records are actually used for sorting.

<span class="hljs-keyword">select</span> <span class="hljs-keyword">count</span>(*)<span class="hljs-keyword">from</span> contact c <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> contact_branch cb <span class="hljs-keyword">on</span> c.id = cb.contact_id <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> branch_user bu <span class="hljs-keyword">on</span> cb.branch_id = bu.branch_id <span class="hljs-keyword">and</span> bu.status <span class="hljs-keyword">in</span> ( <span class="hljs-number">1</span>, <span class="hljs-number">2</span>) <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> org_emp_info oei <span class="hljs-keyword">on</span> oei.data_id = bu.user_id <span class="hljs-keyword">and</span> oei.node_left >= <span class="hljs-number">2875</span> <span class="hljs-keyword">and</span> oei.node_right <= <span class="hljs-number">10802</span> <span class="hljs-keyword">and</span> oei.org_category = - <span class="hljs-number">1</span> +<span class="hljs-comment">----------+</span>| <span class="hljs-keyword">count</span>(*) |+<span class="hljs-comment">----------+</span>| <span class="hljs-number">778878</span> |+<span class="hljs-comment">----------+</span><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">5.19</span> sec)

It is found that 778,878 records are locked before sorting. If we sort the result set of 700,000, it will be disastrous. No wonder it is so slow. Can we change our thinking and sort by the created_time of the contact first, and then join it? Will it be faster?

So it is transformed into the following statement, which can also be optimized using straight_join:

select c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) as created_time, from_unixtime(c.last_modified) as last_modified, c.last_modified_user_idfrom contact cwhere exists ( select 1 from contact_branch cbinner join branch_user buon cb.branch_id = bu.branch_idand bu.status in ( 1, 2)inner join org_emp_info oeion oei.data_id = bu.user_idand oei.node_left >= 2875and oei.node_right <= 10802and oei.org_category = – 1where c.id = cb.contact_id)order by c.created_time desc limit 0 , 10;

Verify the expected effect

Within <span class="hljs-number">1</span>ms, it increased by more than <span class="hljs-number">13000</span> times! sql<span class="hljs-number">10</span> rows <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)

We thought we were done with this, but we missed a detail in the previous analysis. In theory, the cost of sorting first and then joining is the same as that of joining first and then sorting. The reason why it is improved so much is because there is a limit! The general execution process is: MySQL first sorts by index to get the first 10 records, and then joins and filters. When it finds that there are less than 10 records, it goes to 10 records again and joins again. This will obviously be a disaster when there is a lot of data to be filtered in the inner layer. In extreme cases, no data can be found in the inner layer, and MySQL still stupidly takes 10 records each time, almost traversing the entire data table!

Test SQL with different parameters:

<span class="hljs-keyword">select</span> sql_no_cache c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) <span class="hljs-keyword">as</span> created_time, from_unixtime(c.last_modified) <span class="hljs-keyword">as</span> last_modified, c.last_modified_user_id <span class="hljs-keyword">from</span> contact c <span class="hljs-keyword">where</span> <span class="hljs-keyword">exists</span> ( <span class="hljs-keyword">select</span> <span class="hljs-number">1</span> <span class="hljs-keyword">from</span> contact_branch cb <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> branch_user bu <span class="hljs-keyword">on</span> cb.branch_id = bu.branch_id <span class="hljs-keyword">and</span> bu.status <span class="hljs-keyword">in</span> ( <span class="hljs-number">1</span>, <span class="hljs-number">2</span>) <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> org_emp_info oei <span class="hljs-keyword">on</span> oei.data_id = bu.user_id <span class="hljs-keyword">and</span> oei.node_left >= <span class="hljs-number">2875</span> <span class="hljs-keyword">and</span> oei.node_right <= <span class="hljs-number">2875</span> <span class="hljs-keyword">and</span> oei.org_category = - <span class="hljs-number">1</span> <span class="hljs-keyword">where</span> c.id = cb.contact_id ) <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> c.created_time <span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> <span class="hljs-number">0</span> , <span class="hljs-number">10</span>;Empty <span class="hljs-keyword">set</span> (<span class="hljs-number">2</span> <span class="hljs-keyword">min</span> <span class="hljs-number">18.99</span> sec)

2 min 18.99 sec! It's much worse than before. Due to the nested loop mechanism of MySQL, it is basically impossible to optimize in this situation. Ultimately, this statement can only be handed over to the application system to optimize its own logic. From this example, we can see that not all statements can be optimized. Often when we optimize, some extreme cases are missed when the SQL use case regresses, which can cause more serious consequences than the original ones. Therefore, first, do not expect that all statements can be optimized through SQL. Second, do not be overconfident and optimize only for specific cases while ignoring more complex situations.

This is the end of the analysis of slow query cases. The above are just some typical cases. During the optimization process, we encountered more than 1,000 rows of "junk SQL" involving 16 table joins. We also encountered differences between online and offline databases that caused the application to be directly dragged down by slow queries. We also encountered varchar equal value comparisons without single quotes, and Cartesian product queries that directly killed the slave database. No matter how many cases there are, they are just the accumulation of experience. If we are familiar with the internal principles of query optimizer and index, then analyzing these cases becomes very simple.

This article uses a slow query case to introduce the MySQL index principles and some methodologies for optimizing slow queries; and makes a detailed analysis of typical cases encountered. In fact, after working on statement optimization for such a long time, I found that any optimization at the database level is not as good as the optimization of the application system. The same MySQL can be used to support Google/FaceBook/Taobao applications, but it may not even support your personal website. To paraphrase a popular saying recently: "Querying is easy, optimizing is not, so write and cherish it!"