MySql--sql语句优化

摘要

一般优化原则

  • 查询类型要与字段类型匹配,否则不会使用索引,这里注意日期类型可以使用字符串比较

  • like '%xx%'不会使用索引,like 'xx%'会使用索引

  • in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描

  • where条件左侧避免使用函数,否则不会使用索引

分页查询优化

1
select * from employees limit 90000,25;
  • 1.根据自增且连续的主键排序的分页查询,要求主键自增且连续

1
select * from employees where id > 90000 limit 25;
  • 2.根据非主键字段排序的分页查询(这种方式更加灵活)

1
select * from employees e inner join (select id from employees order by name limit 90000,25) ed on e.id = ed.id;

order bygroup by 优化

  • group by 与``order by` 很类似,其实质是先排序后分组,遵照索引创建顺序的最左前缀法则

  • order bygroup by 字段尽量与 selectwhere 中的字段组合为联合索引,即覆盖索引,避免文件排序和回表

  • where 高于 having,能写在 where 中的限定条件就不要去 having 限定了

inexsits优化,小表驱动大表

  • 1.当B表的数据集小于A表的数据集时,in优于exists

1
select * from A where id in (select id from B);
  • 2.当A表的数据集小于B表的数据集时,exists优于in

1
select * from A where exists (select 1 from B where B.id = A.id)

count(*)查询优化

  • count(*)≈count(1)>count(索引字段)>count(主键id)

多表关联语句优化

  • 不要超过3张表,关联表越多查询效率越低

  • mysql优化器一般会优先选择小表做驱动表,所以使用 inner join 时,排在前面的表并不一定就是驱动表。

  • 当使用 left join 时,左表是驱动表,右表是被驱动表,当使用 right join 时,右表时驱动表,左表是被驱动表, 当使用 join 时,mysql会选择数据量比较小的表作为驱动表,大表作为被驱动表。

  • 多表关联字段一定要创建索引

  • 在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。