MySql--sql语句优化

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 本文字数： 760 阅读时长 ≈ 3 分钟

摘要

一般优化原则

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

  • 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 by 与 group by 优化

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

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

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

in和exsits优化，小表驱动大表

  • 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 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。