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

- 日理万妓 2022-11-27 11:29 520阅读 0赞

###  一：问题 ###

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

优化方向：

①给md5\_code、nuclear\_time字段加索引。

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

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

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

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 1][]

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

### 二：验证优化效果-查询 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 2][]

效果还行，数据出来了。

### 三：其他优化点 ###

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

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

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

### 四：查询缓存问题  [参考地址][Link 1] ###

SHOW VARIABLES LIKE '%query_cache%';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 3][]

SHOW STATUS LIKE 'qcache%';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 4][]

生产库建议配置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。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70]: /images/20221124/25559170391e4e56acc2f1bd46ff6cb2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 1]: /images/20221124/328aaedfae1a4b73ba3fd1a265360156.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 2]: /images/20221124/66c315cc3c4a4eac8a386e28d51183f3.png
[Link 1]: https://www.cnblogs.com/ccs-mxs/p/6844921.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 3]: /images/20221124/b67b7a12c11d432d91d805907ab97e79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 4]: /images/20221124/91d228848ccc4d21b39eb5cdd1c660ad.png