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

首页 > 数据库 > Mysql 更新日期: 2015-10-03
这篇文章主要介绍了MySQL索引优化的一些实际案例,主要是用到Order by desc/asc limit M的方法,需要的朋友可以参考下

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

案例一:

一条sql执行非常的慢,执行时间为:

[email protected] 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)

执行计划如下:

[email protected]_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索引为:

[email protected]_db01:52:45:SHOW INDEX FROM test_order_desc;
 
Ind_hot_endtime(end_time,count_num)

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

添加索引:

[email protected] 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

再次执行sql,观察其执行时间:

[email protected] 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)

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

[email protected] 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 |

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

[email protected] : 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;

案例二:

[email protected] : 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索引优化的实际案例分析

B表的idx_uid_stat_inid的索引列包括了(user_id,status,instance_no):

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

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

[email protected] : 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)

调整索引后查看执行计划:

[email protected] : test_db 16:09:42
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索引优化的实际案例分析

我们加上force index强制走我们新加的索引:

[email protected] : test_db 16:10:24
EXPLAIN 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;

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

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

[email protected] : test_db 16:10:30
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;
(0.49 sec)

原始的执行时间:

[email protected] : 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)

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


> 本站内容系网友提交或本网编辑转载,其目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及作品内容、版权和其它问题,请及时与本网联系,我们将在第一时间删除内容!

相关文章
  • 分析MySQL中优化distinct的技巧
    分析MySQL中优化distinct的技巧
    这篇文章主要介绍了分析MySQL中优化distinct的技巧,主要是通过减少本地扫描的次数来进行优化的方法,需要的朋友可以参考下有这样的一个需求:select count(distinct nick) from user_access_xx_xx; 这条sql用于统计用户访问的uv,由于单表的数据量在10G以上,即使在user_access_xx_xx上加上 ...
  • MySQL通过索引优化含ORDERBY的语句
    合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度.关于建立索引的几个准则: 1.合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度. 2.索引越多,更新数据的速度越慢. 3.尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以BTree存储索引),而不是InnoDB.但MyISAM不支持Tr ...
  • MySQL性能优化之路---修改配置文件my.cnf
    mysql数据库的优化,算是一个老生常谈的问题了,网上也有很多关于各方面性能优化的例子,今天我们要谈的是MySQL 系统参数的优化即优化my.cnf文件在Apache, PHP, MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,My ...
  • 浅谈MySQL中优化sql语句查询常用的30种方法
    本篇文章是对MySQL中优化sql语句查询常用的30种方法进行了详细的分析介绍,需要的朋友参考下1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描. 3.应尽量避免在 where 子句中对字段进 ...
  • MySQLDBA教程:Mysql性能优化之缓存参数优化
    在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助数据库属于 IO 密集型的应用程序,其主要职责就是数据的管理及存储工作.而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读 ...
  • 关于MySQL索引的几点值得注意的事项
    索引对查询的速度有着至关重要的影响,MySQL索引的建立对于MySQL的高效运行是很重要的在数据库中,对性能影响最大的包括数据库的锁策略.缓存策略.索引策略.存储策略.执行计划优化策略. 索引策略决定数据库快速定位数据的效率,存储策略决定数据持久化的效率. 1.索引不存储null值. 更准确的说,单列索引不存储null值,复合索引不存储全为null的值.索引 ...
猜你喜欢