With excellent performance, low cost and abundant resources, MySQL has become the preferred relational database of most Internet companies.
Although the performance is excellent, but the so-called “good horse with good saddle”, how to better use it, has become a required course for development engineers, we often see from the job description such as “proficient in MySQL”, “SQL statement optimization”, “understand the principle of database” and other requirements.
We know that the general application system, read and write ratio of about 10:1, and insert operation and general update operation rarely appear performance problems, encountered the most, is also the most prone to problems, or some complex query operations, so the optimization of query statement is obviously a top priority.
Since July 2013, I have been working in the Core Business System Department of Meituan to optimize the slow query, totaling more than ten systems and solving and accumulating hundreds of slow query cases. As the complexity of the business increases, the problem is all kinds of strange, all kinds of strange. This article aims to explain the principles of database indexing and how to optimize slow queries from the perspective of a development engineer.
A slow query triggered by thinking
select
count(*)
from
task
where
status=2
and operator_id=20839
and operate_time>1371169729
and operate_time<1371174603
and type=2;
Copy the codeOne feature was getting slower and slower, so the engineers found the SQL above. And excitedly came to me, “this SQL needs to be optimized, give me every field index”. Surprised, I asked, “Why do I need every field indexed?” “It will be faster to index all the fields in the query,” the engineer was confident. “This is a case where you can create a federated index, because the left-most prefix matches, so operate_time needs to be last, and you need to take all the other queries that are relevant, and you need to do a comprehensive evaluation.”
“Joint index? Left-most prefix match? Comprehensive assessment?” The engineer was lost in thought. In most cases, we know that indexes can improve query efficiency, but how do you build an index? What is the order of the indexes? Many people have only a rough idea. It is not difficult to understand these concepts, and the principles of indexing are far less complicated than you might think.
MySQL > create index
▌ The purpose of indexing is to improve query efficiency. If you want to look up the word “mysql”, you will need to locate the letter M, then search down to the letter Y, and then find the rest of the SQL.
If there’s no index, then you might have to look through all the words to find what you want, but what if I want to find the m word? Or what about words that start with ze? Don’t you think you could have done it without an index?
▌ Apart from dictionaries, examples of indexes can be found everywhere in life, such as train schedules at railway stations, catalogues of books, etc. They all work the same way, by narrowing down the range of data we want to get to filter out the results we want, and at the same time turning random events into sequential events, which means we always lock the data in the same way.
The same is true for databases, but it is obviously much more complex, because there are not only equivalent 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 these problems?
If we think back to the dictionary example, can we break up the data into segments and query it in segments? The simplest is if 1000 pieces of data, 1 to 100 into the first paragraph, 101 to 200 into the second paragraph, 201 to 300 into the third paragraph… If you look up the 250 data, just look for the third paragraph, and you eliminate 90% of the invalid data in one fell swoop.
But what if it’s 10 million records? What’s a good number? Those of you with a little algorithmic foundation will think of search trees, which have an average complexity of lgN and good query performance. But here we ignore one of the key problems, every time the complexity of the model is based on the same operating costs to consider, database implementation is more complex, the data stored in the disk, and in order to improve performance, every time can put some data into memory to calculate again, because we know that the cost of access disk access memory is probably about one hundred thousand times, Therefore, simple search trees cannot meet complex application scenarios.
☞ disk IO and pre-reading mentioned before the disk access, then here first a brief introduction of disk IO and pre-reading, disk read data by mechanical movement, every time the time spent reading data can be divided into seek time, rotational delay, transmission time three sections, seek time refers to the magnetic arm moves to specify the time required to track, Mainstream disks are generally less than 5ms; Rotation delay is the disk speed we often hear, for example, a disk 7200 revolution, means that it can rotate 7200 times per minute, that is, one second can rotate 120 times, the rotation delay is 1/120/2 = 4.17ms; Transfer time refers to the time it takes to read or write data from or to the disk, usually in the order of a few tenths of a millisecond and negligible compared to the first two.
So the time to access a disk, namely the time to access a disk IO, is about 5+4.17 = 9ms, which sounds good, but remember that a 500-MIps machine can execute 500 million instructions per second, because instructions rely on electrical properties, in other words, the time to execute an IO can execute 400,000 instructions. A database with hundreds of millions or even tens of millions of data, nine milliseconds at a time, is a disaster. The following is a comparison of computer hardware delay for your reference:
Considering the disk IO is a very expensive operation, computer operating system made some optimization, when an I/o, not only the current disk address data, but the adjacent data are read into memory buffer, because local pre-reading sex principle tells us that when the computer access to an address data, the adjacent will soon be access to data. Each IO read is called a page.
The specific data size of a page depends on the operating system, and it is usually 4K or 8K. In other words, when we read the data in a page, we actually only have one IO. This theory is very helpful for the data structure design of the index.
☞ index data structure Index about the life in front of the example, the basic principle of the index, the complexity of the database, and the relevant knowledge of the operating system, the purpose is to let everybody know, any kind of data structure is not without foundation, there must be its background and usage scenarios, we summarize, now we need to what could be done this kind of data structure, It’s actually very simple, which is:
Keep the disk I/O count to a small order of magnitude, preferably a constant order of magnitude, for each data lookup. So we wondered if a highly controllable multi-path search tree could meet the requirements? And so the B + tree was born.
☞ explanation b + tree As shown above, it is a b + tree, about the definition of b + tree can see b + tree, said only a few key here, light blue piece of what we call a disk block, you can see every disk block contains several data items (shown in blue) and pointer (shown in yellow), such as disk blocks 1 contains 17 and 35 items, Pointers P1, P2, and P3. P1 indicates a disk block smaller than 17. P2 indicates a disk block between 17 and 35.
Real data exists in leaf nodes 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. Non-leaf nodes only store the real data and only the data items that guide the search direction, such as 17 and 35, which do not really exist in the data table.
☞ b + tree search process As shown, if you want to search a data item 29, so you can take 1 by the disk loaded into memory, disk blocks at this point one IO, using binary search in memory between 29 in the 17th and 35, locking disk 1 piece of P2 Pointers, memory for a very short time (compared to a disk I/o) can be neglected, By disk 1 piece of P2 pointer disk address the disk block 3 by disk loaded into memory, in the second IO, 29 between 26 and 30, locking disk blocks of the 3 P2 pointer, pointer loading disk blocks through eight into memory, in the third IO, binary search to find memory do 29 at the same time, the end of the query, a total of three IO.
The reality is that a 3-tier B + tree can represent millions of data. If millions of data lookups take only three IO, the performance improvement is huge. If there is no index and each data item takes one IO, the total number of IO will be millions, which is obviously very, very expensive.
▌ From the above analysis, we know that the number of I/OS depends on the height of b+ h. 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 amount of data N is constant, the larger m is, the smaller H is; The size of a disk block is the size of a data page. The size of a disk block is fixed. If the space occupied by data items is smaller, the number of data items is larger and the height of the tree is lower.
This is why each data item, the index field, should be as small as possible. For example, int is 4 bytes, which is less than half of bigInt8 bytes. This is why b+ trees require real data to be placed in leaf nodes rather than inner nodes, where the number of data items in a disk block drops dramatically, causing the tree to grow taller. When the data item is equal to 1 it will degenerate into a linear table.
2. When the data items of the b+ tree are compound data structures, such as (name,age,sex), the search tree is built on the order of b+ numbers from left to right. For example, when the data of (zhang 3,20,F) is searched, the b+ tree will compare the name preferentially to determine the direction of the next search. If the name is the same, then age and sex are compared in turn to obtain the retrieved data. However, when data such as (20,F) without name comes, b+ tree does not know which node to search next, because name is the first comparison factor when the search tree is established, and search must be conducted according to name first to know where to search next.
For example, when (three,F) data to search, b+ tree can use name to specify the search direction, but the next field age is missing, so we can only find the data whose name is equal to three, and then match the data whose gender is F, this is a very important property, that is, the left-most matching feature of the index.
☞ Slow query optimization About the MySQL index principle is rather boring stuff, you only need to have a perceptual understanding, not a very thorough and in-depth understanding. Let’s go back to the beginning of the slow query we said, after understanding the principle of indexing, do you have any ideas? Let me summarize some of the basic principles of indexing
▌ select * from the mysql database where the default value is >, <, between, or like. Select * from the mysql database where the default value is >, <, between, or like. A = 1 and b = 2 and c > 3 and d = 4 a = 1 and b = 2 and c > 3 and d = 4 a = 1 and b = 2 and C > 3 and d = 4
2, = and in can be in random order, such as a = 1 and b = 2 and c = 3 to create (a,b,c) index can be in any order, mysql query optimizer will help you optimize the form to be recognized by the index
3. Try to select columns with high distinction as indexes. The formula of distinction is count(DISTINCT Col)/count(*), which indicates the proportion of fields that are not duplicated. So one might ask, is there any empirical value to this ratio? This value is also difficult to determine in different application scenarios. Generally, we require join fields to be above 0.1, that is, 10 records are scanned per item on average
4, index columns can not participate in the calculation, keep the column “clean”, e.g
From_unixtime (create_time) = ‘2014-05-29’
The reason is very simple, b+ tree stores all the field values in the data table, but when retrieving, all the elements need to be compared by using functions, which obviously costs too much. So the statement should be written
Create_time = unix_timestamp (‘ 2014-05-29 ‘);
5, as far as possible to expand the index, do not create a new index. For example, if you want to add (a,b) to a table that already has an index of A, you only need to modify the original index
▌ According to the left-most matching principle, the initial SQL statement index should be the combined index of status, operator_ID, type, and operate_time. Select * from status, operator_id, and type; select * from status, operator_id and type;
☞ For example, there is the following query
select * from task where status = 0 and type = 12 limit 10;
select count(*) from task where status = 0 ;
Copy the codeThe index (status,type,operator_id,operate_time) is correct because it covers all cases. This takes advantage of the left-most matching principle of the index
☞ Explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain command explain output So optimizing statements is basically optimizing rows.
☞ Slow query optimization Basic step 0. Run first to check whether it is really slow. Set SQL_NO_CACHE
1, where condition single table lookup, lock minimum return record table. Select * from table where the smallest number of entries is returned. Select * from table where the smallest number of entries is returned. Select * from table where the smallest number of entries is returned
2, explain see if execution plan, and 1 expected (starting from the lock record less table query) 3, order by limit form of SQL statements for sorting table priority check 4, to understand the business parties use scenario 5, plus index reference index of a few big principle 6, observation, Nonconformance to expectations continues from 0 analysis
☞ several slow query case The following example explains in detail how to analyze and optimize the slow query ☞ complex statements written in many cases, we write SQL just in order to realize the function, it is only a first step, different way of written statements about the nature of the efficiency often have difference, this requires us to mysql implementation plan and the principle of the index is very clear, Look at the following statement
select distinct cert.emp_id from cm_log cl inner join ( select emp.id as emp_id, emp_cert.id as cert_id from employee emp left join emp_certificate emp_cert on emp.id = emp_cert.emp_id where emp.is_deleted=0 ) cert on ( cl.ref_table='Employee' and cl.ref_oid= cert.emp_id ) or ( cl.ref_table='EmpCertificate' and cl.ref_oid= cert.cert_id ) where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00 'Copy the codeRows in set (1.87 SEC) > 53 rows in set (1.87 SEC)
Mysql > select idx_last_upd_date from ‘cm_log’; select idx_last_upd_date from ‘cm_log’; Then table lookup scanned 63,727 records, divided into two parts. Derived, meaning a table that doesn’t exist, is simply the result set of a statement, followed by a number indicating the ID of the statement.
Derived2 indicates that the query with ID = 2 constructed a virtual table and returned 63727 records. SQL > select * from EMPLOYEE where emp_CERTIFicate_EMPID from EMPLOYEE where emp_certificate_empID from EMPLOYEE where emp_certificate_empID from EMPLOYEE where emp_certificate_empID from EMPLOYEE where emp_certificate_empID from EMPLOYEE where emp_certificate_EMPID from EMPLOYEE where emp_certificate_EMPID from EMPLOYEE where emp_certificate_EMPID from EMPLOYEE where emp_certificate_EMPID from EMPLOYEE where emp_certificate from EMPLOYEE Each association locks only one record, which is efficient. Then, 379 records of cm_log are associated according to rules. As you can see from the execution, 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? As you can see, we still want to join cm_log after running. Can we join cm_log before running? If the ref_table of cm_log is EmpCertificate, then the emp_certificate table will be associated. If the ref_table of cm_log is Employee, then the Employee table will be associated. Join them with a union. Notice that you use union instead of union all because the original statement has “distinct” to get a unique record, and unions do just that.
If there is no distinct in the original statement, we can directly use union all, because the action of union removal will affect SQL performance.
☞ The optimized statements are as follows
select emp.id from cm_log cl inner join employee emp on cl.ref_table = 'Employee' and cl.ref_oid = emp.id where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00' and emp.is_deleted = 0 union select emp.id from cm_log cl inner join emp_certificate ec on cl.ref_table = 'EmpCertificate' and cl.ref_oid = ec.id inner join employee emp on emp.id = ec.emp_id where cl.last_upd_date >='2013-11-07 15:03:00' and cl.last_upd_date<='2013-11-08 16:00:00' and emp.is_deleted = 0Copy the code4, do not need to understand the business scenario, only need to modify the statement and the previous statement to keep the same result 5, the existing index can meet, do not need to build index 6, with the modified statement experiment, only need 10ms reduced nearly 200 times!
▌ The purpose of this example is to subvert our perception of the degree of distinction between columns. In general, we assume that a more distinguished column will lock fewer records, but in some special cases, this theory is limited
select
*
from
stage_poi sp
where
sp.accurate_result=1
and (
sp.sync_status=0
or sp.sync_status=2
or sp.sync_status=4
);
Copy the code951 rows in set (6.22 SEC) 1, explain 1, rows in set (6.22 SEC) 1, explain 1, rows in set (6.22 SEC) 2, ALL rows in set (6.22 SEC) 2, ALL rows in set (6.22 SEC) 2, ALL rows in set (6.22 SEC) Let the EXPLAIN rows be as close to 951 as possible because it is a single table query 0
▌ If we look at the number of records accurate_result = 1, we see that the distinction of accurate_result is very low. The whole table has only -1,0,1 values, and a very small amount of data cannot be locked with the index. Look again at the sync_STATUS field. The same distinction is low and, according to theory, not suitable for indexing.
If sync_status 0 and 3 are evenly distributed, the locking records will be in millions. If sync_status 0 and 3 are evenly distributed, the locking records will be in millions
4. Find the business side to communicate and see the usage scenarios. The business side uses the SQL statement like this. Every five minutes, the business side scans the qualified data and changes the sync_STATUS field to 1. The number of qualified records in five minutes is not too many, about 1000. Once you understand the usage scenarios of the business side, it becomes easy to tune this SQL because the business side ensures that the data is unbalanced, and indexes can filter out most of the data that is not needed
5. Use the following statement to create an index according to the index creation rule
alter table stage_poi add index idx_acc_status(accurate_result,sync_status);
Copy the code6. Observing the expected results, I found that only 200ms was needed, which was more than 30 times faster. 952 Rows in Set (0.20 SEC)
We look back at some problem analysis, the process of the single table query is relatively good optimization, most of the time just need to put field in accordance with the rules of the where condition inside index is good, if only this “mindless” optimization, obviously some very low degree of differentiation, should not be indexed columns will be combined with index, This can have a serious impact on insert and update performance, and may affect other queries.
Therefore, the use scenario of SQL in step 4 is very critical. Only when we know this business scenario, can we better assist us to better analyze and optimize the query statement.
☞ Statements that cannot be optimized
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_id from contact c inner join contact_branch cb on c.id = cb.contact_id inner join branch_user bu on cb.branch_id = bu.branch_id and bu.status in ( 1, 2) inner join org_emp_info oei on oei.data_id = bu.user_id and oei.node_left >= 2875 and oei.node_right <= 10802 and oei.org_category = - 1 order by c.created_time desc limit 0 , 10;Copy the code10 rows in set (13.06 SEC) 10 rows in set (13.06 SEC)
SQL > alter table branch_user (idx_userid_STATUS); SQL > alter table contact_branch (idx_branch_id); SQL > alter table branch_user (idx_branch_id); Finally, the primary key associates the Contact table. Rows returns very little and you can’t see anything unusual. Order by + limit = order by + limit So let’s simplify the SQL and remove the order by and limit to see how many records are used to sort
select
count(*)
from
contact c
inner join
contact_branch cb
on c.id = cb.contact_id
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
Copy the code1,
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = – 1
+———-+
| count(*) |
+———-+
778878 | |
+———-+
1 row in set (5.19 sec)
We found that 778,878 records were locked before sorting, and it would be disastrous if we sorted for 700,000 result sets. No doubt it is so slow. Can we change our thinking and sort first by created_time of contact and then join? This was transformed into the following statement, which could also be optimized with 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_i
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,
10;
Copy the codeVerify the effect is expected to improve more than 13,000 times in 1ms! 10 Rows in set (0.00 SEC)
Sort before join is the same as join before sort. The cost of joining before sort is the same. The general implementation process is as follows: Mysql selects the first 10 entries by index, and then performs join filtering. When it finds that there are less than 10 entries, it performs 10 entries again and joins again. This will be a disaster if there are too many entries in the inner join filtering. Almost traversed the table!
☞ Under SQL tests with different parameters
select
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) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 2875
and oei.org_category = - 1
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,
10;
Empty set (2 min 18.99 sec)
Copy the code2 min 18.99 SEC! It’s much worse than it was before. Due to the nested loop mechanism of mysql, this situation cannot be optimized. This statement is ultimately left to the application system to optimize its logic.
As we can see from this example, not all statements can be optimized, and often when we optimize, the consequences can be worse than the original because the SQL use case regression leaves out some extreme cases. So, one: don’t expect all statements to be SQL optimized, and two: don’t be too confident and optimize for a specific case, ignoring more complex cases.
Slow query case analysis to here, the above is just some more typical cases. In the process of optimization, we encountered “garbage SQL” with more than 1000 rows and 16 table joins. We also encountered the difference between online and offline databases, which led to the application being directly dragged to death by slow query. We also encountered vARCHAR equivalence comparison without single quotation marks, and cartesian product query directly killed the slave library. No matter how many cases there are, it’s just a matter of experience, and if we’re familiar with the inner workings of the query optimizer and indexes, it’s easy to analyze them.
☞ In the following part, this paper introduces the principle of MySQL indexing and some methods of optimizing slow queries with a slow query case. The typical cases encountered are analyzed in detail. In fact, after such a long time of statement optimization, I found that any database-level optimization is not as good as the optimization of application system. The same MySQL can be used to support Google/FaceBook/Taobao application, but it may not even support your personal website. To use the more popular words recently: “easy to query, not easy to optimize, and write and cherish!”