MySQL index optimization case analysis and mysql index Case Analysis

MySQL index optimization case analysis and mysql index Case Analysis

Order by desc/asc limit M is a scenario that I often encounter in mysql SQL optimization. Its optimization principle is also very simple, that is, using the Order of indexes, the optimizer scans data in the order of indexes and stops scanning data in M rows that meet the criteria. It looks very simple, however, I often see that many SQL statements with poor performance do not use this optimization rule. The following uses some actual cases to analyze and explain:

Case 1:

The execution of an SQL statement is very slow. The execution time is:

root@test 02:00:44 SELECT * FROM test_order_desc WHERE END_TIME>now() ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12; +---------+-----------+------------+------+---------------------+---------------------+-------------------Data1..................................................................................................... Data2..................................................................................................... +---------+-----------+------------+------+---------------------+---------------------+-------------------12 ROWS IN SET (0.49 sec)

The execution plan is as follows:

root@test_db01:53:23 EXPLAIN SELECT * FROM test_order_desc WHERE END_TIME > now() ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12; +----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+----- | id | select_type | TABLE  | TYPE | possible_keys  | KEY  | key_len | REF | ROWS  | Extra   | +----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+----- | 1 | SIMPLE   | test_order_desc | range | ind_hot_endtime | ind_hot_endtime | 9    | NULL | 113549 | USING WHERE; USING filesort | +----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+-----

Ind_hot_endtime index:

root@test_db01:52:45:SHOW INDEX FROM test_order_desc; Ind_hot_endtime(end_time,count_num)

Note that there are 113549 rows in SQL that meet the filtering condition end_time> now (), and the remaining condition contains order by, which leads to a very large sorting result set, the execution is very resource-consuming. Therefore, after analyzing the SQL statements and including sorting conditions such as order by desc limit, the appropriate indexes are added to meet the sorting conditions, at the same time, due to the limited result set of limit, when the number of rows meeting the condition is scanned and the query is exited, let's look at the optimization results:

Add an index:

root@test 02:01:06:ALTER TABLE test_order_desc ADD INDEX ind_gmt_create(gmt_create,count_num); Query OK, 211945 ROWS affected (6.71 sec) Records: 211945 Duplicates: 0 Warnings: 0

Execute the SQL statement again and observe the execution time:

root@test 02:01:35: SELECT * FROM test_order_desc WHERE END_TIME > now()  ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12; +---------+-----------+------------+------+---------------------+---------------------+col2................................................................................... +---------+-----------+------------+------+---------------------+---------------------+ Data1.................................................................................. Data2.................................................................................. +---------+-----------+------------+------+---------------------+---------------------+ 12 ROWS IN SET (0.00 sec)

We can see that the execution time has been reduced to below milliseconds and its execution plan is viewed:

root@test 02:01:42: EXPLAIN SELECT * FROM test_order_desc WHERE END_TIME > now() ORDER BY GMT_CREATE DESC,count_num DESC LIMIT 12, 12; +----+-------------+----------+-------+-----------------+----------------+---------+------+------+-------------+ | id | select_type | TABLE  | TYPE | possible_keys  | KEY | key_len | REF | ROWS | Extra | +----+-------------+----------+-------+-----------------+----------------+---------+------+------+-------- | 1 | SIMPLE   | test_order_desc | INDEX | ind_hot_endtime | ind_gmt_create | 14   | NULL | 48 | USING WHERE |

We can see that the optimizer has selected ind_gmt_create index scan, which avoids sorting the result set, at the same time, the optimizer predicts that 14 rows of data will be scanned to obtain the data that meets the query conditions (END_TIME> now (), and the Execution Plan is ideal.

 

root@127.0.0.1 : test_db 16:05:15:EXPLAIN SELECT b.*,a.*,k.*  FROM instance b LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND  b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;

Case 2:

root@127.0.0.1 : test_db 16:05:15:EXPLAIN SELECT b.*,a.*,k.*  FROM instance b LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND  b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;

The index columns of idx_uid_stat_inid in Table B include (user_id, status, instance_no ):

From the execution plan analysis, the table connection sequence is: B-> r_a-> a-> k, we can see that the data of row 49212 needs to be scanned in the first row of the execution plan. Because the status adopts the in method, instance_no cannot be used even in the index, in this way, temporary tables are used for sorting, which is also the cause of slow SQL Execution. We can see that the last sorting in SQL is order by B. instance_no asc limit 37300,50. Here we can see the dawn of optimization and adjust the database index to meet the sorting requirements of Table B:

root@127.0.0.1 : test_db 16:05:04 ALTER TABLE instance ADD INDEX ind_user_id(user_id,instance_no);Query OK, 0 ROWS affected (0.56 sec)

Adjust the index and view the execution plan:

root@127.0.0.1 : test_db 16:09:42EXPLAIN SELECT b.*,a.*,k.*  FROM instance b LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND  b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;

We add force index to force the Newly Added index:

root@127.0.0.1 : test_db 16:10:24EXPLAIN SELECT b.*,a.*,k.*  FROM instance b force INDEX (ind_user_id) LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND  b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;

We can see that after the prompt is added, the newly added index is used. The number of scanned rows is 54580 rows, and the execution time is as follows:

root@127.0.0.1 : test_db 16:10:30SELECT b.*,a.*,k.*  FROM instance b force INDEX (ind_user_id) LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND  b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;(0.49 sec)

Original execution time:

root@127.0.0.1 : test_db 16:10:51:SELECT b.*,a.*,k.*  FROM instance b  LEFT OUTER JOIN image a ON b.image_id=a.image_id LEFT OUTER JOIN key_pair k ON b.key_pair_id=k.key_pair_id LEFT OUTER JOIN region_alias r_a ON r_a.region_no=b.region_no WHERE b.STATUS IN (1,8) AND  b.user_id = 21 AND r_a.big_region_no='regeion_xx' ORDER BY b.instance_no ASC LIMIT 37300,50;(1.28 sec)

Summary:
Order by desc/asc limit optimization technology sometimes gets unexpected optimization results when you cannot build a good index, but sometimes it has some limitations, the Optimizer may not scan according to your specified index path. The optimizer must take into account the filtering of the query column and the limit length. When the query column is highly selective, the cost of using sort is not high. When the selection of query columns is very low, the use of order by + limit technology is very effective.