MySQL系列三（定位慢SQL、索引优化、SQL优化）Using filesort

1. 慢SQL

1.1 定位慢SQL（慢查询日志）

在mysql 配置文件中 （my.conf)，进行下面配置，修改配置后重启mysql生效。

# 开启或关闭慢查询日志
slow_query_log = ON  
# 慢查询记录时间阈值，SQL执行超过此时间则会被记录到日志（单位：秒，默认10秒）。
long_query_time = 5
# 指定生成的慢查询日志路径（未设置则和默认和数据文件放一起） 
slow_query_log_file = /opt/soft/mysql/log/slow.log
# 是否记录未使用索引的SQL。
log_queries_not_using_indexes=on

设置全局变量（MySQL重启后失效，不建议）

set global slow_query_log = on;
set global log_output = 'FILE,TABLE';
set global long_query_time = 0.001;

1.2 慢SQL优化整体思路

1. 找出对业务影响大的（调用次数多的）、扫描行数多的SQL进行优化，查看执行计划explain，SQL使用到的表的结构、查看其是否使用索引、是否有冗余索引；
2. 对响应时间长但调用次数少的SQL，评估其对业务的影响，是否存在短时间批量执行的可能，因为一旦批量执行可能会在短时间内让数据库挂掉；

慢SQL优化办法：

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引，查询条件多时可加联合索引；

创建联合索引时列的选择原则

• 经常用的列优先（最左匹配原则）
• 离散度高的列优先（离散度高原则）
• 宽度小的列优先（最少空间原则）

• select的查询结果中尽量避免使用*，只查询用到的字段；
• 尽量避免使用in和not in，能用between就不用in，in和not in会导致数据库进行全表扫描，增加运行时间。例如查询学号为8，9的人的学号和成绩

select 学号,成绩 from 成绩表 where 学号 in(8,9);

优化后：

select 学号,成绩 from 成绩表 where 学号 between 8 and 9;

• 尽量避免使用or，or会导致数据库进行全表扫描。可以使用union代替or*；例如从成绩表中选出成绩是88分或89分学生的学号：

select 学号 from 成绩表 where 成绩=88 or 成绩=89；

优化后：

select 学号 from 成绩表 where 成绩=88
union
select 学号 from 成绩表 where 成绩=89

• where子句比较符号左侧避免函数，尽量避免在比较符号的左侧出现表达式、函数等操作，因为会导致全表扫描，增加运行时间。所以，为了提高效率，可以把where子句中遇到函数或加减乘除的运算移到比较符号的右侧；
• 用limit子句限制返回的数据行数，如果前台只需要显示15行数据而查询结果返回了1万行，那么最好使用limit子句来限制查询返回的数据行数。

2. 索引失效的场景

like查询以%开头时索引失效：

select * from doc where title like ‘%XX’； --不能使用索引
select * from doc where title like ‘XX%’； --非前导模糊查询，可以使用索引

反向条件不走索引：负向条件有：!=、<>、not in、not exists、not like 等

select * from doc where status != 1 and status != 2; --不能使用索引
select * from doc where status in (0,3,4); --优化为 in 查询，可以使用索引

IS NULL、IS NOT NULL 在无法使用索引，不过在mysql的高版本已经做了优化，允许使用索引

在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描

select * from doc where YEAR(create_time) <= ‘2016’; – 不能使用索引
select * from doc where create_time<= ‘2016-01-01’; – 可以使用索引
select * from order where date < = CURDATE()； – 不能使用索引
select * from order where date < = ‘2018-01-2412:00:00’; – 可以使用索引
select id from t where substring(name,1,3)=’abc’ – 不能使用索引
select id from t where name like ‘abc%’ – 可以使用索引
select id from t where num/2=100 – 不能使用索引
select id from t where num=100*2 – 可以使用索引

强制类型转换会导致全表扫描 ：字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

select * from user where phone=13800001234; – 不能使用索引
select * from user where phone=‘13800001234’; – 可以使用索引

使用组合索引时，要符合最左前缀原则： ：组合索引的字段数不允许超过5个。如果在a,b,c三个字段上建立联合索引 index(a,b,c)，那么他会自动建立 a、(a,b)、(a,b,c) 三组索引。

• 建立联合索引的时候，区分度最高的字段在最左边：
• 存在等号和非等号混合判断条件时，在建立索引时，把等号条件的列前置，如 where a > ? and b= ?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
• 最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致，但还是建议 where 条件的顺序和联合索引一致。
• 假如index(a,b,c), where a=3 and b like ‘abc%’ and c=4，a能用，b能用，c不能用。

索引失效情况总结:
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用。

3. 索引优化

一些常见的索引优化技巧包括：

1. 选择合适的索引列，避免创建过多的索引。定期进行索引维护和优化，包括删除不必要的索引、重建索引等。单表索引建议控制在5个以内：索引不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，同时也会暂用空间。一个表的索引数较好不要超过5个。更新十分频繁、数据区分度不高的列不宜建立索引： 数据更新会变更 B+ 树，在更新频繁的字段建立索引会大大降低数据库性能。类似于“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。
2. 尽可能使用联合索引，以满足复合查询的需求。
3. 尽可能使用覆盖索引来进行查询操作，避免回表，减少select * 的使用。覆盖索引：覆盖索引是select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。
4. 避免在索引列上进行函数操作，以充分利用索引。

SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
ref：使用普通的索引
range：对索引进行范围检索。
当 type=index 时，索引物理文件全扫，速度非常慢。

进行join联表查询的字段需要建立索引，join最好不要超过三个表，需要 join 的字段，数据类型必须一致： 多表关联查询时，保证被关联的字段需要有索引。left join是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用right join。

4. SQL语句优化

减少请求的数据量：

• 只返回必要的列，用具体的字段列表代替 select * 语句
• 只返回必要的行，使用 Limit 语句来限制返回的数据。如果不使用 Limit 的话，MySQL将会一行一行的将全部结果按照顺序查找，最后返回结果，借助 Limit 可以实现当找到指定行数时，直接返回查询结果，提高效率

优化深度分页的场景：利用延迟关联或者子查询：对于 limit m, n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。延迟关联示例如下，先快速定位需要获取的 id 段，然后再关联：

延迟关联：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据
覆盖索引：select的数据列只用从索引中就能够得到，不用回表查询
select a.* from 表1 a,(select id from 表1 where 条件 limit 100000,20) b where a.id=b.id；

深度分页原理：limit m，n查询过程是先回表查询m+n条记录，然后丢掉前m条，取后面n条结果返回。内存占用和IO读取开销太大

避免在使用or来连接查询条件：如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

小表驱动大表，即小的数据集驱动大的数据集：

Using filesort

Using filesort 的含义很简单，就是使用了排序操作，出现这个选项的常见情况就是 Where 条件和 order by 子句作用在了不同的列上

解决办法：建立联合索引，对where条件列和order by列建立联合索引。