慢sql

定义：

mysql慢查询日志是mysql提供的一种日志记录，它是用来记录在mysql中相应时间超过阈值的语句，就是指运行时间超过long_query_time值的sql，

会被记录在慢查询日志中。long_query_time的默认值是10，意思是运行10S之上的语句

查询慢sql时间设设置的值

show variables like 'long%';

修改慢查询时间阈值

set global long_query_time=4

# 展示慢sql的开启状态和日志文件的存放位置
show variables like 'slow%';

slow_launch_time 　　　　2
slow_query_log　　　　 ON
slow_query_log_file 　　/root/mysql/datas/mysql/slow_query.log

# 未使用索引的查询被记录到慢查询日志中。如果调优的话，建议开启这个选项。如果开启了这个参数，full index scan的sql也会被记录到慢查询日志中。

show variables like 'log_queries_not_using_indexes';

# 开启开关

set global log_queries_not_using_indexes=1

##  查询有多少条慢查询记录

show global status like '%Slow_queries%';

 

过expalin可以得到：

1. 表的读取顺序
2.表的读取操作的操作类型
3.哪些索引可以使用
4. 哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询

 

 

 

 

 

从解释结果看，第一条的sql索引利用率高于第二条的。（后文会讲到：索引type从优到差：System-->const-->eq_ref-->ref-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all.）

possible_keys :  哪些索引可能有助于查询。如果为空,说明没有可用的索引。

key:  实际从 possible_key 选择使用的索引，如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT语句中使用 USE INDEX (indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制 MYSQL 忽略索引

key_len:  使用的索引的长度。在不损失精确性的情况 下,长度越短越好。

ref:  显示索引的哪一列被使用了 

rows:   请求数据返回的大概行数

extra:  其他信息，出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

优化手段：
① SQL优化

避免 SELECT *，只查询需要的字段。
小表驱动大表，即小的数据集驱动大的数据集：
当B表的数据集比A表小时，用in优化 exist两表执行顺序是先查B表再查A表查询语句：SELECT * FROM tb_dept WHERE id in (SELECT id FROM tb_dept) ;
当A表的数据集比B表小时，用exist优化in ，两表执行顺序是先查A表，再查B表，查询语句：SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID) ;
尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表。
② 优化索引的使用

尽量使用主键查询，而非其他索引，因为主键查询不会触发回表查询。
不做列运算，把计算都放入各个业务系统实现
查询语句尽可能简单，大语句拆小语句，减少锁时间
or 查询改写成 union 查询
不用函数和触发器
避免 %xx 查询，可以使用：select * from t where reverse(f) like reverse('%abc');
少用 join 查询
使用同类型比较，比如 '123' 和 '123'、123 和 123
尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描
列表数据使用分页查询，每页数据量不要太大
避免在索引列上使用 is null 和 is not null
③ 表结构设计优化

使用可以存下数据最小的数据类型。
尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int。
尽可能使用 not null 定义字段，因为 null 占用 4 字节空间。数字可以默认 0 ，字符串默认 “”
尽量少用 text 类型，非用不可时最好独立出一张表。
尽量使用 timestamp，而非 datetime。
单表不要有太多字段，建议在 20 个字段以内。
Mysql常用数据类型存储大小及范围：https://blog.csdn.net/HXNLYW/article/details/100104768

3.如果以上优化还是有问题，可以使用show profiles 分析sql 性能

show profiles  

show profile for query  [queryId]