浅谈MySQL查询优化

 

本文从以下几方面对MySQL优化进行分析

• 获取有性能问题的SQL
• 通过慢查询日志获取有性能问题的SQL
• 慢查询日志内容
• 实时获取有性能问题的SQL
• SQL预处理解析
• 如何确定查询消耗时间
• 优化特定的SQL

一丶获取有性能问题的SQL

1. 通过用户反馈获取存在问题的SQL，此用户一般为测试人员，例执行某一个查询非常慢（不推荐）
2. 通过慢查询日志获取存在性能问题的SQL
3. 实时获取存在性能问题的SQL

以下着重介绍上述2,3点

二丶通过慢查询日志获取有性能问题的SQL

  相关参数：

 ① 启动/停止记录慢查询日志

set global slow_query_log = on/off # 还可以通过其他方式定时开关闭

  ② 指定慢查询的存储路径及文件名

slow_query_log_file #默认保存在mysql的数据目录中

  ③  指定记录慢查询日志SQL执行时间的阈值，以秒为单位

long_query_time

其中MySQL慢查询日志记录的包括：查询语句，数据修改语句，事务回滚的SQL。慢查询默认时间为10s，通常修改为1毫秒

  ④  是否记录未使用索引的SQL

log_queries_not_using_innndexes

三丶两种方式分析慢查询日志内容

#user@Host:sbtest[sbtest]@localhost[] Id:7
#Query_time:0.000233
#lock_time:0.000120
#Rows_sent:1             #扫描的数据行数
#Rows_examined:1         #返回的数据行数
SET timestamp = 1458612917 #SQL执行时间
select id from sbtest where id = 1; #执行的SQL

⑴  常用的慢查询分析工具1

 mysqldumpslow : 汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出

命令：

mysqldumpslow -s r -t 10 slow -mysql.log

#-t 表示 按总时间。还有以下几个参数，c:总次数。t:总时间。l:锁的时间。r:总数据行。at,al,ar:表示t,l,r的平均数

⑵  常用的慢查询分析工具2

pt-query-digest : 使用慢查询日志获取有性能问题的SQL并生成查询报告

命令：

pt-query-digest \
--explain h=127.0.0.1,u=username,p=password \
slow -mysql.log

四丶实时获取有性能问题的SQL

利用MySQL下 information_schema 库下的 processlist 表，实时获取有问题的SQL

五丶SQL解析预处理

MySQL执行查询语句的流程为：客服端发送SQL请求，SQL服务器判断是否命中缓存，MySQL进行SQL解析预处理，优化器生成对应的执行计划，根据执行计划调用API查询数据，最后将结果返回给客户端。

⑴ 打开查询缓存（对于一个读写频繁的系统使用查询缓存很可能会降低查询处理效率）

query_cache_type     #设置缓存是否开启或关闭
query_cache_size     #设置查询缓存的内存大小
query_cache_limit    #设置查询缓存可用的存储最大值
query_cache_wlock_invalidate #设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit #设置查询缓存分配的内存最小单位

⑵ SQL预处理阶段

检查SQL语法是否正确并生成查询计划

MySQL生成错误的查询计划可能的原因：

① 统计信息不准确

② 查询计划中的成本估算不等同于实际计划成本

③ MySQL优化器认为的最后与实际存在一定偏差

造成以上的原因可能为：

① MySQL不考虑其他并发查询

② MySQL的既定规则

③ 调用了MySQL存储过程及用户自定的函数

比如执行了一条错误的SQL，select id from a where id = -1;

⑶ 查询优化器支持的SQL

① 重新定义表的关联顺序

② 将外连接转化成内连接

③ 使用等价变化规则 如 where id.>5 and id =5 转换为 where id>=5

③ 优化 count(id) min(id) max(id) 统计行的最大最小值等

④ 将一个表达式转换为常数，比如在求某一个时间字段时，不用系统函数而采用给定的固定值

⑤ 子查询优化，可以将子查询转化为关联查询

⑥ 对 not in ， <>等的优化

六丶两种方式确定查询消耗时间

⑴ profile (mysql5.5以前版本)

set profile = 1;        #启动profile
###执行查询语句
show profiles;          #查看每一个查询语句消耗的总的时间信息
show profile for query query_id #查询某个query_id在每个阶段消耗的时间
show profile cpu query query_id #查询某个query_id在每个阶段消耗的CPU信息

⑵ performance_schema(mysql5.5以后版本)

① 启动proformance_schema

② 查看其它线程所消耗的时间

七丶举例优化特定的SQL

⑴ 在千万级的表中删除或修改百万行数据（分批次修改，并单次修改完间隔几秒再执行操作）

⑵ 对大表的结构进行修改

⑶ 优化 not in , <>查询 ，例：

原SQL：

select aid from a where aid not in (select aid from b)

优化后SQL:

select aid from a aa left join b bb on aa.aid = bb.bid where bb.bid IS NULL

⑷ 优化主键最左匹配

例：

原：select aid from a where addtime > '2018-07-22' and aid >10;
优：select aid from a where aid > 10 and addtime > '2018-07-22';

(5) 随机获取数据

原:
SELECT * FROM `tabName` ORDER BY rand() LIMIT 5  #大表此语句会存在性能问题

优化:
SELECT * FROM `tabName` WHERE id>= (SELECT floor(RAND() * ((SELECT MAX(id) FROM `tabName`) - (SELECT MIN(id) FROM `tabName`)) + (SELECT MIN(id) FROM `tabName`))) ORDER BY idLIMIT 5 #优化后能毫秒级响应