Mysql入门MySQL索引优化的实际案例分析

概述介绍《Mysql入门MySQL索引优化的实际案例分析》开发教程，希望对您有用。

《MysqL入门MysqL索引优化的实际案例分析》要点：
本文介绍了MysqL入门MysqL索引优化的实际案例分析，希望对您有用。如果有疑问，可以联系我们。

MysqL实例Order by desc/asc limit M是我在MysqL sql优化中经常遇到的一种场景,其优化原理也非常的简单,就是利用索引的有序性,优化器沿着索引的顺序扫描,在扫描到符合条件的M行数据后,停止扫描；看起来非常的简单,但是我经常看到很多性能较差的sql没有利用这个优化规律,下面将结合一些实际的案例来分析说明：

MysqL实例案例一：

MysqL实例一条sql执行非常的慢,执行时间为：

MysqL实例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)

MysqL实例执行计划如下：

MysqL实例root@test_db01:53:23 EXPLAIN SELECT * FROM test_order_desc WHERE END_TIME > Now() ORDER BY GMT_CREATE DESC,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 | +----+-------------+----------+-------+-----------------+-----------------+---------+------+--------+-----

MysqL实例Ind_hot_endtime索引为：

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

MysqL实例在注意到sql中满足过滤条件end_time>Now()的有113549行,在加上剩余的条件中含有order by,这样会造成排序的结果集非常的大,执行非常的耗费资源；于是分析sql,在sql中包括了order by desc limit这样的排序条件后,新增适当的索引满足排序的条件,同时由于有limit的限制结果集,当扫描到满足条件的行数后退出查询,那么我们来看看优化效果：

MysqL实例添加索引：

MysqL实例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

MysqL实例再次执行sql,观察其执行时间：

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

MysqL实例可以看到执行时间已经降到了毫秒以下,查看其执行计划：

MysqL实例root@test 02:01:42: EXPLAIN SELECT * FROM test_order_desc WHERE END_TIME > Now() ORDER BY GMT_CREATE DESC,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 |

MysqL实例可以看到优化器已经选择了ind_gmt_create索引扫描,这样的话就避免了对结果集进行排序的过程,同时优化器预估扫描14行数据就会得到满足查询条件的数据(END_TIME > Now()),执行计划非常的理想.

MysqL实例 

MysqL实例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;

MysqL实例案例二：

MysqL实例root@127.0.0.1 : test_db 16:05:15:EXPLAIN SELECT b.*,50;

MysqL实例

MysqL实例B表的IDx_uID_stat_inID的索引列包括了(user_ID,status,instance_no)：

MysqL实例

MysqL实例我们从执行计划上分析来看,表的连接顺序为：b―>r_a―>a―>k,可以看到执行计划的第一行中需要扫描49212行的数据,同时由于status采用的是in的方式,instance_no即使在索引中也用不上,这样就导致了排序使用到了临时表,这也是导致sql执行慢的原因.我们看到sql中的最后一个排序为order by b.instance_no asc limit 37300,50,这里我们好像可以看到优化的曙光,调整数据库的索引以满足B表的排序需求：

MysqL实例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)

MysqL实例调整索引后查看执行计划：

MysqL实例root@127.0.0.1 : test_db 16:09:42EXPLAIN SELECT b.*,50;

MysqL实例

MysqL实例我们加上force index强制走我们新加的索引：

MysqL实例root@127.0.0.1 : test_db 16:10:24EXPLAIN SELECT b.*,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,50;

MysqL实例

MysqL实例可以看到在加上提示符后,使用到了我们新加的索引,扫描的行数为54580行,执行时间：

MysqL实例root@127.0.0.1 : test_db 16:10:30SELECT b.*,50;(0.49 sec)

MysqL实例原始的执行时间：

MysqL实例root@127.0.0.1 : test_db 16:10:51:SELECT b.*,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,50;(1.28 sec)

MysqL实例总结：
Order by desc/asc limit的优化技术有时候在你无法建立很好索引的时候,往往会得到意想不到的优化效果,但有时候有一定的局限性,优化器可能不会按照你既定的索引路径扫描,优化器需要考虑到查询列的过滤性以及limit的长度,当查询列的选择性非常高的时候,使用sort的成本是不高的,当查询列的选择性很低的时候,那么使用order by +limit的技术是很有效的.

总结

以上是内存溢出为你收集整理的Mysql入门MySQL索引优化的实际案例分析全部内容，希望文章能够帮你解决Mysql入门MySQL索引优化的实际案例分析所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。