SQL优化技巧

时间：2022-11-02 07:54:26

一、查询尽量不要用*，尽量写明要查询的列名。

反例
select * from pay_history
正例
select name,pay_time,order_no,money from pay_history
理由

节省资源、网络开销
可能会用到覆盖索引，减少回表，提高查询效率

二、避免在where中使用or来连接条件

反例
select name,pay_time,order_no,money from pay_history where money > 100 or name like '李%'
正例
select name,pay_time,order_no,money from pay_history where money >100
union all 
select name,pay_time,order_no,money from pay_history where name like '李%'
理由

使用or会导致索引失效，从而导致全表扫描。

三、尽量使用数值替代字符串类型

正例

主键（id）：优先使用int、tinyint
性别（sex）：女（0）男（1），mysql推荐使用tinyint

理由

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符
数值类型比较一次就可以了
字符会降低查询和连接的性能，并会增加存储开销

四、where中使用默认值代替null

反例
select name,pay_time,order_no,money from pay_history where money is not null
正例
select name,pay_time,order_no,money from pay_history where money > 0
理由

如果mysql优化器发现，走索引比不走索引成本高的时候，就会放弃索引，这些条件!=，<>，is null，is not null经常会让索引失效；
一般情况下，查询成本高时优化器就会放弃索引；
如果把null值，换成默认值，很多时候让走索引成为可能，同时思路也相对清晰一些；

五、避免在where子句中使用!=或<>操作符

反例
select name,pay_time,order_no,money from pay_history where money != 100
select name,pay_time,order_no,money from pay_history where money <> 100
理由

使用!=和 <>很可能让索引失效；
应避免在where子句中使用!=、<>操作符，否则会进行全表扫描；
如果实在没办法，可以适当使用。

六、inner join 、left join、right join

三种连接如果结果集相同，优先使用inner join，如果使用left join，左表尽量使用小表；

  • inner join 内连接，保留两个表中完全匹配的结果集；
  • left join 会返回左表中所有数据，即使在右表中没有匹配的记录；
  • right join 会返回右表中所有数据，即使在左表中没有匹配的记录；
理由
  • inner join，等值连接，返回的行数比较少，性能相对好一些
  • left join，使用左连接时，左表数据结果尽量少一些，条件尽量放到左表处理，返回的结果行数可能比较少；
  • 这是 mysql优化原则，就是小表驱动大表，小的数据集驱动大数据集，使性能更好；

七、提高group by语句的效率

反例

先分组，再过滤

select name,pay_time,order_no,sum(money) from pay_history group by name having name like '李%' or name like '张%';
正例

先过滤，再分组

select name,pay_time,order_no,sum(money) from pay_history where name like '李%' or name like '张%' group by name;
理由

在分组前，将结果集过滤完毕。

八、操作delete、update语句，加个limit或者循环分批次删除

  • 降低写错sql的代价
    清空数据可不是小事情，添加limit，即便删除错了也是丢失了部分数据，可以通过binlog快速恢复；
  • SQL效率可能更高
    sql中加了limit 1 ，如果命中第一条记录就return，没有limit的话，还会继续执行扫描表；
  • 避免长事务
    delete执行时，如果 money加了索引，mysql会将所有相关的行加写锁和间隙锁，所有执行相关行都会被锁住，如果删除的数据量很大，会直接影响部分功能无法使用；
  • 数据量大，CPU超负荷
    如果要删除的数据量很大时，不加limit限制，会把造成cpu爆表，导致越删除越慢；
  • 锁表
    如果删除太多数据，可能造成锁表，会有lock wait timeout exceed 的错误，所以分批次操作；

九、避免在索引列上使用内置函数

反例
select name,pay_time,order_no,sum(money) from pay_history WHERE DATE_ADD(pay_time,INTERVAL 7 DAY) >=NOW();
正例
select name,pay_time,order_no,sum(money) from pay_history WHERE pay_time >= DATE_ADD(=NOW(),INTERVAL 7 DAY);

十、优化like语句

反例
select name,pay_time,order_no,sum(money) from pay_history WHERE name like '%李'; 

select name,pay_time,order_no,sum(money) from pay_history WHERE name like '%李%';
正例
select name,pay_time,order_no,sum(money) from pay_history WHERE name like '李%';
理由

如果必须使用模糊查询，避免使用全模糊查询，即like '李%'，是会使用索引的；
左模糊‘%李’无法直接使用索引；

十一、使用explain分析你的SQL执行计划

  1. type
  • system：表仅有一行
  • const：表最多一行数据配合，主键查询时触发较多；
  • eq_ref：对于每个来自前面的表的行组合，从该表中读取一行。这可能最好的连接来行，除了const类型；
  • range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>= 、<、 <=、IS NULL、<=>、BEWTEEN或者IN操作符，用常量比较关键字列时，可以使用range；
  • index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件一般比数据文件小；
  • all：全表扫描；
  • 性能排名： system > const > eq_ref > ref > range > index > all
  • 实际sql优化中，最好达到ref或range级别。
  1. Extra常用关键字
  • Using index：只从索引树中获取信息，而不需要回表查询；
  • Using where：where子句用于限制哪一行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra不为Using where并且表联接类型为ALL或index，查询可能有一些错误，需回表查询；
  • Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；
标签：

相关文章