随着业务量的增长,运营同事反馈有个报表页面越来越慢,从对应的报表语句中逐个子查询筛查,找出如下最慢的语句:
可以看到,其中有个子集全表扫了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管理中的重要环节,它能够帮助我们识别并优化那些执行时间过长,影响整体数据库性能的SQL语句。PMM(Percona Monitoring and Management)是一款强大的开源工具,专为MySQL、MongoDB和Percona ...
总结来说,MySQL慢查询日志是排查性能问题的有效工具,通过对日志的分析,可以定位到拖慢系统的关键查询,并采取相应的优化措施。在日常运维中,定期分析慢查询日志并持续优化是提升数据库性能的重要环节。通过熟练...
本文将详细介绍如何开启MySQL的慢查询日志,并通过实例来展示操作过程。 一、慢查询日志简介 慢查询日志是MySQL提供的一种特性,用于追踪执行时间过长的查询,以便进行性能分析和调优。通过对这些慢查询的分析,...
### MySQL优化、问题案例分析 #### 案例一:索引优化 ##### 问题描述 用户系统响应缓慢,数据库CPU占用率高达100%。经排查发现,数据库中有大量执行时间超过2秒的慢SQL语句。具体示例如下: ```sql SELECT uid ...
### MySQL慢查询日志的作用与使用详解 #### 一、慢查询日志概述 在MySQL数据库管理中,慢查询日志是一项重要的诊断工具,它能够帮助数据库管理员和开发者识别并解决那些执行时间过长的SQL语句所带来的性能问题。...
性能分析案例中,可以针对特定场景,如CPU使用率升高时,通过分析`events_statements_summary_by_digest`找出导致CPU消耗高的SQL语句,然后优化这些语句的执行计划,减少索引扫描、避免全表扫描等。对于并发线程数...
为了诊断MySQL故障,我们通常会观察系统日志文件,包括慢查询日志(slowlog)、警告日志(alertlog)、通用查询日志(generalquerylog)和二进制日志(binlog)。通过检查状态变量如com_select、com_insert等,我们...
1. **日志读取**:读取MySQL慢查询日志文件,通常包含查询时间、用户、执行的SQL语句等信息。 2. **日志解析**:解析日志格式,提取关键字段,如查询时间戳、查询语句、执行计划等。 3. **错误处理**:处理日志文件...
通过一个实例给大家分享了MySQL Sending data表查询慢问题解决办法。 最近在代码优化中,发现了一条sql语句非常的慢,于是就用各种方法进行排查,最后终于找到了原因。 一、事故现场 SELECT og.goods_barcode, og....
开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 一、配置慢查询 1、参数说明 slow_query_log : 慢查询开启状态(默认关闭) slow_...
MySQL慢查询是数据库管理中一个重要的概念,它指的是在数据库中执行时间超过预设阈值(默认为10秒)的SQL查询。这种查询可能导致系统性能下降,甚至引发服务器资源耗尽,例如CPU使用率过高、内存占用过大,以及网络...
#### 十一、慢查询案例 以一个常见的慢查询为例,假设有一个用户表(user)和订单表(order),需要查询某个用户的最近一笔订单详情。如果未正确建立索引或查询语句编写不当,可能会导致全表扫描,从而大幅降低查询速度...
49.MySQL高级_慢查询日志.avi 48.MySQL高级_为排序使用索引OrderBy优化.avi 47.MySQL高级_in和exists.avi 46.MySQL高级_小表驱动大表.avi 45.MySQL高级_索引优化答疑补充和总结口诀.avi 44.MySQL高级_...
例如,在本实例中,首先了解了查询的业务目标,然后进行测试和分析,最后根据结果将 log_time 字段设置为索引。 知识点6:表结构的重要性 表结构的设计对查询速度有很大的影响。在本实例中,表 packet_buy_report ...
### 阿里云MySQL分析 #### 一、概述 阿里云MySQL服务是阿里云针对企业级用户设计的一款高性能数据库解决方案。它不仅提供了强大的数据库管理功能,还特别针对MySQL进行了优化,以解决传统MySQL在高可用性和维护...