优化总结

做索引
使用 EXPLAIN 分析，type至少要达到range级别，杜绝出现all级别
最左前缀原则
不要使用 select *
尽量用union all代替union
使用合理的分页方式以提高分页的效率, 如加索引字段作为查询条件
不建议使用%前缀模糊查询
避免在 where 子句中对字段进行 null 值判断(会导致索引失效)
当只需要一条数据的时候，使用limit 1, EXPLAIN中type列达到const类型
如果排序字段没有用到索引，就尽量少排序
如果限制条件中其他字段没有索引，尽量少用or
分段查询，使用between缩小查询访问
必要时可以使用force index来强制查询走某个索引
避免隐式类型转换
join优化，利用小表驱动大表
分库分表
做主从，读写分离

EXPLAIN

列名	描述
type	针对单表的访问方法，至少要达到range级别，杜绝出现all级别
key	实际上使用的索引，如果没有选择索引，值是NULL
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
Extra	额外的信息

最左前缀原则

比如设计的联合索引是 name_birthday_phone，那么我们在sql中where后面需要按照这个顺序

// 索引起效
SELECT * FROM person_info WHERE name = 'Owater' AND birthday = '2020-04-07';

// 索引失效
SELECT * FROM person_info WHERE birthday = '1990-09-27';

那最左前缀原则有什么作用呢？

充分利用索引，节省索引空间
建立了name_birthday_phone，也就意味着不需要单独再建立索引name和索引name_birthday

那为什么不按照这个顺序，索引就失效了呢？

因为B+索引是有序排序，先按照name排序再按照birthday、phone依次排序，所以突然从birthday开始查找，是无法查找的到

匹配列前缀

通常情况下一般都不太建议使用LIKE，那么该如何正确使用LIKE呢？

首先，我们需先理清楚一个字符串索引在B+树种是怎么存储？

Aaple
Apple
Azple
..
Baple
Bbple

从以上排序，得知一个字符串索引是按照字母的大小进行排序
那么程序查找自然也是需要按照这个顺序

// 索引起效
SELECT * FROM user WHERE name LIKE 'As%';

// 索引失效，%开始，程序不知从哪处开始查找
SELECT * FROM user WHERE name LIKE '%As%';

为什么不能使用 select *

SELECT * FROM user WHERE name > 'Owater' AND name < 'Water';

如上sql，即使是使用了索引，但是效率还是低，因为这过程发生了回表

从索引 name_birthday_phone 对应的B+树中取出name值在Owater～Water之间的用户记录。此时是访问二级索引使用顺序I/O
发现查询列表是 ==*==，则需到 聚集索引 中查找所有数据，这是所谓的回表。此时访问聚集索引使用随机I/O，顺序I/O比随机I/O的性能高很多。

尽量用union all代替union

union 将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算。
union all 则是将两个结果集进行合并，会存在重复的情况。

使用合理的分页方式以提高分页的效率

假设一张表100W条数据，如果只是单独使用limit，随着偏移量越大效率越低，因为检索需从某一个偏移量到记录集的结束所有的记录行

// 效率低
select id, name, phone from user limit 100000, 20

// 优化如下，可以采用id来限制下一页的起点
select id, name, phone from user where id > 100000 limit 100000, 20

谨慎使用范围查询

比如between,>,<等条件时，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引。

SELECT * FROM user WHERE name > 'Owater' AND name < 'Water' AND birthday > '2020-04-09';

查询name的时候，会用到索引，而birthday列用不到索引，因为只有name值相同的情况下才能用，而这个查询中通过name进行范围查找的记录中可能并不是按照birthday列进行排序的。

分段查询

当扫描的行数成百万级以上的时候，可以通过程序循环遍历，分段去数据库查找数据，再将结果集进行合并，这样可以减负数据库单次执行的性能消耗。

区分in和exists

in 是先执行子查询，适合于外表大而内表小的情况。
exists 是以外层表为驱动表，先被访问，适合于外表小而内表大的情况。