优化总结
- 做索引
- 使用 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，即使是使用了索引，但是效率还是低，因为这过程发生了回表
1、 从索引 name_birthday_phone 对应的B+树中取出name值在Owater～Water之间的用户记录。此时是访问二级索引使用顺序I/O
2、 发现查询列表是 ==*==，则需到 聚集索引 中查找所有数据，这是所谓的回表。此时访问聚集索引使用随机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 是以外层表为驱动表，先被访问，适合于外表小而内表大的情况。