MySQL高级性能优化8---SQL优化

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

知是行之始，行是知之成