Mysql 查询优化（一）

前言：

工作上遇到需要优化查询速度的任务，在网上找了比较多的资料，主要还是索引的优化，最后解决问题。

 

环境：

mysql版本：8

数据量：60万

sqlyog可视化工具

 

问题描述：

1、单表查询（未优化查询速度大概是5~7s）

SELECT* FROM actionlogs ORDER BY create_time, code  LIMIT 0, 30 

2、分组查询（未优化查询速度大概是5~7s）

SELECT  en_name, cn_name  FROM actionlogs WHERE code IN('6001', '6003', '6002', '6004', '2001', '3013', '3012', '3011', '3010', '3009', '3008', '3007', '3006')

AND cn_name = '123'

AND create_time> '2015-12-09 17:42:54' 

AND create_time< '2020-12-09 17:43:01'

GROUP BY  en_name, cn_name   

3、分组统计查询（未优化查询速度大概是12s）

SELECT  en_name, cn_name,

SUM(CASE code WHEN '6001' THEN 1 ELSE 0 END) AS '数据1' , 

SUM(CASE code WHEN '6003' THEN 1 ELSE 0 END) AS '数据2' , 

SUM(CASE code WHEN '6002' THEN 1 ELSE 0 END) AS '数据3' , 

SUM(CASE code WHEN '6004' THEN 1 ELSE 0 END) AS '数据4' , 

SUM(CASE code WHEN '2001' THEN 1 ELSE 0 END) AS '数据5',

.......

 FROM actionlogs

WHERE code IN('6001', '6003', '6002', '6004', '2001', '3013', '3012', '3011', '3010', '3009', '3008', '3007', '3006')

AND cn_name = '123'

AND create_time> '2015-12-09 17:42:54' 

AND create_time< '2020-12-09 17:43:01'

GROUP BY  en_name, cn_name LIMIT 0, 30

4、SELECT COUNT(*) FROM actionlogs （未优化查询速度大概是6s）

 

解决：

1、order by 优化：

一般来说，数据量60万其实并不大，除了分页处理，还需要从sql语句本身和数据表结构入手修改。

关键在order by 后面的字段。order by后面的字段需要按顺序建立索引或联合索引。

a. 建立create_time, code这两个字段的联合索引（优化明显，瞬间出来）；

b. select * 改成 select 字段（一般有点用，作用不大）。

2、group by 优化：

a. 建立分组字段联合索引：en_name,cn_name,code,create_time联合索引，其中en_name,cn_name是分组条件，code,create_time是筛选条件（优化明显，可以在0.3s出来）；

b. select的顺序和分组字段顺序一致。

3、group by sum 优化：

类似2；

4、select count(*) 优化

a. select count(*) 改为 select count(1)（优化不明显，查询速度为4s）

b.select count(*) 改为 select count(code)（优化明显，瞬间出来 code是索引）。

 

总结：

1、使用explain 分析sql语句执行过程，看具体用到的索引。

2、索引不能瞎建，需要根据自己的实际情况建立不同的索引或联合索引。

3、可以使用force index(index_name)强制指定使用某个索引。