SQL优化
索引失效
- 全值匹配(最好情况)
- 表中建立的索引刚好全部用到
- 最佳左前缀法则
- 当有多个索引的时候,如果缺少某个索引,则该索引右边的字段建立的索引都会失效
- 条件查询顺序可以乱,但必须得有
- 不在索引上做任何操作
- 计算,函数,类型转换
- 会导致索引失效而转向全表扫描
- 返回条件右边的索引失效
- mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
- is not null索引失效
- 少用or,用or连接时,会导致索引失效
- like以通配符开头(%test)索引失效,导致全表扫描
- 字符串不加引号索引失效
- 尽量使用覆盖索引
- 查询的字段和建立的字段刚好吻合
排序与分组优化
- 在使用order by时,经常出现Using filesort
- 索引作为排序时
- 没有按照索引顺序排序
- 当使用*时,order by即使使用了全部索引,也会出现
- 当索引字段等于某个常量时,可以当做是使用索引的
- 使用排序一升一降
- 使用group by时,使用不当,会出现Using tempory
- 按照索引顺序分组
大批量数据时分页操作如何优化
-
传统分页查询
- 使用limit,随着offset,查询速度会越来越慢
- 会把前面的数据都取出,找到对应位置
-
优化后分页查询
-
把limit耗时的操作通过索引查出id,然后在关联该虚拟表,取出 id相同的
-
使用子查询优化
-
select * from test inner join (select id from test limit 5000000,10) t on test.id = t.id
-
-
使用id限定优化(前提id自增)
-
select * from test where id > 5000000 limit 10
-
select * from test where id between 5000000 and 5000010 limit 10
-
-
-
max最大值优化
- 给最大值列添加索引
count使用点
- count(*) 包含了null
- count(name) 不包含null,要包含可写count(name or name is null)
小表驱动大表
-
小的数据集,驱动大的数据集
-
类似嵌套循环
-
in与exist的选择
-
select * from A where id in (select id from B)
-
select * from A where exists(select 1 from B where A.id = B.id)
-
当A表数据大于B表数据,选择in,否则exists
-
知是行之始,行是知之成