一次mysql查询优化过程-evanxia.docVIP

一次Mysql 查询优化过程 By evanxia 2016.06.27 背景：应项目需求，目前有一个业务需要每分钟向数据库中插入一批数据，然后另一个程序会从数据库中读取某一时间段内，某些特征的数据。随着时间的积累，数据量提升，查询时间爆发式增加，不得不优化数据库。本次优化只考虑了特定的查询优化，没有考虑优化后插入效率，为了减轻影响批量插入时采用的是一条insert...values(),()...形式语句。以下记录查询优化过程。 准备 先介绍一下，可能会使用到的mysql相关的工具。 清除查询缓存： reset query cache; 清理查询缓存碎片以提高内存使用性能： FLUSH QUERY CACHE; MySQL查询分析器： EXPLAIN或DESC 二、简单测试 2.1、环境 数据库： mysql Ver 14.14 Distrib 5.5.49, for debian-linux-gnu (x86_64) using readline 6.3 数据： 2801178条记录 腾讯云服务器： Linux VM-122-135-ubuntu 3.13.0-36-generic #63-Ubuntu SMP Wed Sep 3 21:30:07 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux 资源[dmidecode |more]： CPU：单核，最大 2000 MHz 内存： 1 GB 2.2、测试 1、未加索引 A、EXPLAIN SELECT id, connect_count, total_count, monitor_rate,`time`,CAST((connect_count/ total_count)*100 AS UNSIGNED) as rate FROM statistics_data where statistics_id in (20,21,22, 23, 24, 25) and `time` between 1469319304 and 1469405704 order by `time`; 2、建立time索引 ALTER TABLE `statistics_data` ADD INDEX ( `time` ) ; 清空缓存：reset query cache; 检索： A、EXPLAIN SELECT id, connect_count, total_count, monitor_rate,`time`,CAST((connect_count/ total_count)*100 AS UNSIGNED) as rate FROM statistics_data where statistics_id in (20,21,22, 23, 24, 25) and `time` between 1469319304 and 1469405704 order by `time`; B、分析发现没有使用time索引，所以强制使用 EXPLAIN SELECT id, connect_count, total_count, monitor_rate,`time`,CAST((connect_count/ total_count)*100 AS UNSIGNED) as rate FROM statistics_data force index (time) where statistics_id in (20,21,22, 23, 24, 25) and `time` between 1469319304 and 1469405704 order by `time`; 3、between换为 清空缓存：reset query cache; 检索： EXPLAIN SELECT id, connect_count, total_count, monitor_rate,`time`,CAST((connect_count/ total_count)*100 AS UNSIGNED) as rate FROM statistics_data where statistics_id in (20,21,22, 23, 24, 25) and `time` 1469319304 and `time` 1469405704 order by `time`; 4、建立statistics_id索引 ALTER TABLE `statistics_data` ADD INDEX ( `statistics_id` ) ; 清空缓存：reset query cache; 检索 EXPLAIN SELECT id, connect_count, total_c