mysql 分组查询慢_记一次MySQL分组查询慢优化过程

一：问题

时间范围查询所有数据的同数据存在超过4次的数据，检索查询时已经没有速度了，直接不响应。

优化方向：

①给md5_code、nuclear_time字段加索引。

②给sql语句后面加order by null。

③调整where条件里字段的查询顺序，有索引的放前面。

④给所有where条件的字段加组合索引。

⑤用子查询的方式，先查where条件里的内容，再去重。

SQL分析后的结果是：

可能用到索引：nuclear_time,md5_code

实际用到索引：md5_code

第一步：优化逻辑where查询没有使用到我的索引：强制必须使用nuclear_time索引FORCE INDEX(nuclear_time)

EXPLAIN

SELECT md5_code

FROM nuclear_price FORCE INDEX(nuclear_time)

WHERE nuclear_time BETWEEN '2020-03-18' AND '2020-06-17'

GROUP BY md5_code

HAVING COUNT(md5_code) >= 4

ORDER BY NULL

优化效果出来了，索引直接使用了 nuclear_time，数据量缩小了一半了，开始查询。

二：验证优化效果-查询

效果还行，数据出来了。

三：其他优化点

1：指定ORDER BY NULL禁止排序:

2：Mysql 查询缓存配置 地址，如果没有配置查询缓存，不建议配置。

查询缓存的作用就是当查询接收到一个和之前同样的查询，服务器将会从查询缓存种检索结果，而不是再次分析和执行上次的查询。这样就大大提高了性能，节省时间。

1.配置查询缓存

修改配置文件，修改[mysqld]下的query_cache_size和query_cache_type(如果没有则添加)。

query_cache_size：表示缓存的大小

query_cache_type：有3个值，表示缓存那种类  型的select结果集，query_cache_type各个值如下：

0或off：关闭缓存

1或on：开启缓存，但是不保存使用sql_no_cache的select语句,如不缓存select  sql_no_cache name from wei where id=2

2或demand：开启有条件缓存，只缓存带sql_cache的select语句，缓存select  sql_cache name from wei where id=4

例子的配置为下，配置完成重启Mysql服务器即可。

query_cache_size=256M

query_cache_type=1

四：查询缓存问题  参考地址

SHOW VARIABLES LIKE '%query_cache%';

SHOW STATUS LIKE 'qcache%';

生产库建议配置MySQL，配置后重启。不建议开启查询缓存

SHOW VARIABLES LIKE '%query_cache%';

SHOW STATUS LIKE 'Qcache%';

#如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况;

如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲;

Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。

● “Qcache_free_blocks”：Query Cache中目前还有多少剩余的blocks。如果该值显示较大，则说明Query Cache中的内存碎片较多了，可能需要寻找合适的机会进行整理。

● “Qcache_free_memory”：Query Cache中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache内存大小是否足够，是需要增加还是过多了；

● “Qcache_hits”：多少次命中。通过这个参数我们可以查看到Query Cache的基本效果；

● “Qcache_inserts”：多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两个参数我们就可以算出Query Cache的命中率了：

Query Cache命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；

● “Qcache_lowmem_prunes”：多少条Query因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出

● “Qcache_not_cached”：因为query_cache_type的设置或者不能被cache的Query的数量；

● “Qcache_queries_in_cache”：当前Query Cache中cache的Query数量；

● “Qcache_total_blocks”：当前Query Cache中的block数量；

Query Cache的限制

Query Cache由于存放的都是逻辑结构的Result Set，而不是物理的数据页，所以在性能提升的同时，也会受到一些特定的限制。

a) 5.1.17之前的版本不能Cache帮定变量的Query，但是从5.1.17版本开始，Query Cache已经开始支持帮定变量的Query了；

b)所有子查询中的外部查询SQL不能被Cache；

c)在Procedure，Function以及Trigger中的Query不能被Cache；

d)包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。

鉴于上面的这些限制，在使用Query Cache的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入Query Cache，仅仅让某些Query的查询结果被Cache。