随着业务量的增长,运营同事反馈有个报表页面越来越慢,从对应的报表语句中逐个子查询筛查,找出如下最慢的语句:
可以看到,其中有个子集全表扫了300多万行数据。抽出来单独explain,定位到问题语句:
SELECT t1.statDate, t1.tips FROM passport_4366_test t1 WHERE t1.statDate>='2016-05-01' AND t1.statDate<='2016-05-05' GROUP BY t1.statDate, t1.tips;
检查一下表结构发现,在表的statDate上明明建立了索引,照理说type应该是range才对,怎么会是all呢?
去掉group by语句试试:
仍然是全表扫描。去掉一个字段再试试:
要扫描的数据量变成1/5,type也变成range了。那么到底是什么原因导致MySQL查询执行器放弃使用索引直接进行全表扫描呢?去问问谷歌,搜索到如下可能有帮助的文章:
回顾一下,这个表字段是date类型,难道是因为这个表同一天产生太多数据降低了索引区分度致使查询执行器放弃治疗?那么,把这个字段改成datetime类型会不会好点?好,我们来动手试试看。
create table passport_4366_test like passport_4366; insert into passport_4366_test select * from passport_4366 where statDate>='2016-04-01' AND statDate<='2016-05-06'; alter table passport_4366_test add index dt_idx(updateTime); alter table passport_4366_test drop index statDate;
然后explain看看:
真是顽固不化的MySQL,只好force index了:
终于乖乖听话了。想起来还有一个解决思路,那就是建立联合索引:
alter table passport_4366_test add index statDate_tips_idx(statDate, tips);
再来看看explain的结果:
效果很不错,rows列比force index的结果还要好(可能是因为Index Condition Pushdown Optimization)。拉大时间范围,直接执行语句对比一下执行时间:
结果是联合索引完胜。
最后,总结一下:
1. 建立索引时候尽量选择区分度大的列;
2. 适当条件下可以考虑建立联合索引,譬如本例中联合索引的优势明显;
3. 写SQL的时候要多点explain,主要看type和rows,一般来说,执行效率const > eq_ref > ref > range > all,rows值越少越好。
参考文章:
http://blog.csdn.net/mchdba/article/details/9190771
https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/
http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
相关推荐
本文实例讲述了mysql慢查询操作。分享给大家供大家参考,具体如下: mysql有些sql会执行很慢,有可能造成服务器负载飙升 首先查询 确定影响负载的是mysql ,使用top命令,ps命令等 其次,进入MySQL,使用show full ...
主要介绍了MySQL慢查询日志分析的实例教程,通过设置参数从慢查询日志开始分析性能问题的原因,需要的朋友可以参考下
开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 二、参数说明 slow_query_log 慢查询开启状态 slow_query_log_file 慢查询日志存放的位置...
开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 一、配置慢查询 1、参数说明 slow_query_log : 慢查询开启状态(默认关闭) slow_...
通过一个实例给大家分享了MySQL Sending data表查询慢问题解决办法。 最近在代码优化中,发现了一条sql语句非常的慢,于是就用各种方法进行排查,最后终于找到了原因。 一、事故现场 SELECT og.goods_barcode, og....
和mysql的慢SQL日志分析一样,redis也有类似的功能,来帮助定位一些慢查询操作。 Redis slowlog是Redis用来记录查询执行时间的日志系统。 查询执行时间指的是不包括像客户端响应(talking)、发送回复等IO操作,而单单...
49.MySQL高级_慢查询日志.avi 48.MySQL高级_为排序使用索引OrderBy优化.avi 47.MySQL高级_in和exists.avi 46.MySQL高级_小表驱动大表.avi 45.MySQL高级_索引优化答疑补充和总结口诀.avi 44.MySQL高级_...
尚硅谷_MySQL高级_慢查询日志 · 50.尚硅谷_MySQL高级_批量插入数据脚本 · 51.尚硅谷_MySQL高级_用Show Profile进行sql分析 · 52.尚硅谷_MySQL高级_全局查询日志 · 53.尚硅谷_MySQL高级_数据库锁理论概述 ·...
6.2慢查询基础:优化数据访问196 6.2.1是否向服务器请求了不需要的数据196 6.2.2MySQL是否在扫描额外的记录198 6.3重构查询的方式201 6.3.1一个复杂查询还是多个简单查询201 6.3.2切分查询202 6.3.3分解关联...
本文将结合一个线上案例,分析如何正确设置 MySQL 慢日志参数和使用慢日志功能,并介绍下网易云 RDS 对 MySQL 慢日志功能的增强。 MySQL 参数组功能 网易云 RDS 实例提供了参数组管理功能,可通过参数管理界面查看...
04-MySQL数据恢复的必要条件及恢复案例场景图解分析 05-实战模拟企业数据丢失应用的案例场景 06-MySQL数据库增量恢复详细实战 07-更多MySQL数据库增量恢复大讨论及大总结 08-MySQL增量恢复小结及核心恢复思想 09-...
看起来非常的简单,但是我经常看到很多性能较差的sql没有利用这个优化规律,下面将结合一些实际的案例来分析说明: 案例一: 一条sql执行非常的慢,执行时间为: root@test 02:00:44 SELECT * FROM test_order_...
在我们的实验中,考虑的参数是同时请求包含相同SQL查询的同一Web页的客户端实例数,SQL查询的表数,要在请求的Web页上显示的记录数。 ,以及使用的数据库服务器的类型。 这项工作演示了这些参数对Web服务器性能分析...
INSERT语句是最常见的SQL语句之一,最近有台MySQL服务器不定时的会出现并发线程的告警,从记录信息来看,有大量insert的慢查询,执行几十秒,等待flushing log,状态query end 【初步分析】 从等待资源来看,大部分...
12-某聚簇索引排序慢案例分析.wmv 13-索引长度与区分度.wmv 14-伪哈希函数降低索引长度.wmv 15-大数据量分页优化.wmv 16-索引与排序.wmv 17-冗余索引与重复索引.wmv 18-索引碎片修复.wmv 19-Explain分析SQL效果.wmv ...
mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢? mysql> show variables like 'log_slow_queries'; +------------------+-------+ | ...
本文实例讲述了mysql建立高效的索引。分享给大家供大家参考,具体如下: 如何建立理想的索引? 查询频繁度 区分度 索引长度 覆盖字段 区分度 假设100万用户,性别基本上男/女各为50W, 区分度就低。 长度小 索引...