SQL优化
Author: Lijb
Email: lijb1121@163.com
SQL优化概述
基本的SQL优化的思路
1. 截取SQL语句
2. 识别有问题的SQL语句
3. 确认语句执行
4. 语句分析
5. 语句优化
6. 优化验证
截取SQL语句
- 全面查询日志-启用方式
1. 在mysql的配置文件中,设置如下:
general_log=1
general_log_file=/path/logfile log_output=FILE
2. 也可以通过SQL语句来动态启用和禁用,还可以指定输出到表或者文件中:
set global general_log=1;
set global log_output='TABLE';
select * from mysql.general_log;
注意 全面日志提供所有连续的sql查询语句顺序,但不提供执行时间,在开发环境开启这个功能,可以很好的来审查SQL语句。但永远不要在生产环境开启这个功能!!!
- 慢查询日志-启用方式
1. 在mysql的配置文件中,设置如下:
slow_query_log=1
slow_query_log_file=/usr/local/mysql/logs/slowlog
long_query_time=0.2
log_output=FILE
- 进程列表
show full processlist;
- 引擎状态
show engine innodbstatus ,可以提供SQL语句的详细信息,比如:导致外键验证失败或者造成死锁的SQL语句。
识别有问题的SQL语句
- 哪些SQL语句应该是我们通常去关注的?
1. 运行最慢的sql语句
2. 运行较快,但是执行频率非常高的sql语句
- 识别方法
1. MySql自带的mysqldumpslow
如: mysqldumpslow/usr/local/mysql/logs/slowlog
2. 开源的pt-query-digest
下载地址:https://www.percona.com/software/mysql-tools/percona-toolkit
3. 这个工具的输出结果有3个不同部分
第一个部分提供了总体时间、查询频率、硬件故障细节等信息
第二部分提供最差的不同查询的详细列表,按照总执行时间排序
第三部分是执行时间的分布,以及具体的SQL语句
确认SQL语句执行
1. 环境
应该尽可能跟出问题的环境一样或者相似,也就是要让问题得以重现。
这样也利于优化改进后进行验证,看看优化是否有效果。
2. 时间统计
采用同一个监控执行时间的标准,以利于发现问题,以及对比优化前后的效果
SQL语句分析
常用的SQL语句分析命令
1.explain
分析语句执行基于开销的优化器,以及被优化器考虑的访问策略等.
1. select_type:表示table列引用的使用方式的类型:
a:simple:不包含子查询和其它复杂语法的简单查询
b:primary:复杂查询中的最外层的表
c:derived:表不是物理表
d:dependent:子查询
2. table:表名、表的别名或者一个为查询产生的临时表的标识符
3. type:表使用的连接方式,常用的有:
a:const:这个表最多只有一行匹配
b:system:表只有一行
c:eq_ref:有一行是为了每个之前确定的表而读取的
d:ref:所有具有匹配值的行都被用到
e:range:所有符合给定范围值的索引行都被用到
f:all:全表扫描
4. possible_keys:优化器为查询选定的索引
5. key:优化器选择使用的索引
6. key_len:用于Sql语句的连接条件的键的长度,对于确认索引的有效性,以及 多列索引中用到的列的数据很重要,常见的有:
key_len:4,表示intnot null
key_len:5,表示intnull
key_len:30,表示char(30) not null
key_len:32,表示varchar(30) not null
key_len:92,表示varchar(30) null
7. ref:用来进行索引比较的列或者常量
8. rows:优化器估计的所有存在于累计结果集中的行数目
9. extra:优化器使用的额外信息
2.show create table 表名
展示表中列和索引定义的细节信息
3.show indexes from 表名
察看表的索引信息,其中的Cardinality表示索引中每一列唯一值的数量的 估计值
4.show table status like 表名
察看表的底层大小以及表结构。对于innodb引擎,都是估计值。
5.show status命令
察看服务器当前内部状态信息,比如:show global status like ‘handler_read%’
用来查看是否使用了索引,以及使用索引读取了多少值
6.show variables命令
查看MySQL系统变量的当前值,比如:show variables like ‘%slow%’,用来 查跟慢日志相关的变量的值
SQL语句优化
- 常用的SQL语句优化手段
1. 尽量去除表连接操作
2. 尽量减少操作到的列的数目
3. 精简数据类型和约束条件,以改进表结构
4. 合理的构建索引
5. 在SQL中有意、合理的利用索引
6. 去除重复索引
7. 删除不用的索引
8. 尽量减少SQL语句要扫描的语句数量
9. 确保on或者using子句上的列上有索引
10. 确保group by和order by中的表达式只涉及表中的一个列
11. 尽量明确写出要查询的列,少用select *
12. 尽量不要在where里面使用不等于符号,或者是进行null值判断,这会导致全表扫描
13. 尽量不要在where里面对字段进行函数式操作
14. 用exist代替in
太多太多的细节,这里就不去罗列了,并且网上优化的SQL语句案例很多,只要掌握了从哪些方面优化剩下的都好办.
SQL优化实际是个综合性的工作 硬件服务器、MySQL配置、表结构、索引、临时表、SQL语句等一起综合考虑。