20种sql语句优化方案

概要

提示：SQL语句中常见的 20 种优化方式。优雅，真优雅！

SQL语句20种常见优化方式汇总

提示：一次性可能记不住，建议收藏！

大家写sql语句中，是不是常有死锁、超时等问题出现，那就说明你的索引、查询语句规范并不完美。以下是小主整列20种sql语句优化方案，可以让你的sql语句，查询删除更加快速。当然看起来更加容易理解！

汇总：

• *一、查询SQL尽量不要使用select ，而是具体字段查询

  反例：
  SELECT * FROM user
  正例：
  SELECT id, userName,UserPawword,salary,createdatetime FROM user
  

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

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

  反例：
  SELECT id, userName,UserPawword,createdatetime FROM user WHERE id=1 or salary=100
  正例：
  使用union all
  SELECT id, userName,UserPawword,salary,createdatetime FROM user WHERE id=1 
  UNION ALL
  SELECT id, userName,UserPawword,salary,createdatetime FROM user WHERE salary=5000
  

  使用理由：
  使用or可能会使索引失效，从而全表扫描。对于or没有索引的salary这种情况，假设它走了id的索引，但是走到 salary查询条件时，它还得全表扫描；
  如果第一遍就走全表扫描，直接一遍扫描搞定；
  虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的；

• 三、尽量使用数值替代字符串类型。这个视情况而定。
  1、正例
  主键（id）：primary key优先使用数值类型int，tinyint。
  性别（sex）：0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint2。
  使用理由：
  因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；而对于数字型而言只需要比较一次就够了；
  字符会降低查询和连接的性能，并会增加存储开销；

• 四、使用varchar代替char

  反例：
  `username` char(100) DEFAULT NULL COMMENT '用户'
  正例：
  `username` varchar(100) DEFAULT NULL COMMENT '用户'
  

  使用理由：
  varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；
  char按声明大小存储，不足会自动补空格；
  其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

• 五、char与varchar的区别？
  1、char的长度是固定的，而varchar的长度是可以变化的。
  比如，存储字符串“110”，对于char(10)，表示你存储的字符将占10个字节（包括7个空字符），在数据库中它是以空格占位的。
  再看varchar(10)，实际则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。
  2、char的效率比varchar2的效率稍高。
  char和varchar是一对矛盾的统一体，两者是互补的关系。
  varchar比char节省空间，在效率上比char会稍微差一点，既想获取效率，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率”。
  varchar虽然比char节省空间，但是假如一个varchar列经常被修改，而且每次被修改的数据的长度不同，这会引起“行迁移”现象，而这造成多余的I/O，是数据库设计中要尽力避免的，这种情况下用char代替varchar会更好一些。char中还会自动补齐空格，因为你insert到一个char字段自动补充了空格的,但是select后空格没有删除，因此char类型查询的时候一定要记得使用trim。

• 六、where中使用默认值代替null

  反例：
  SELECT id, userName,UserPawword,salary,createdatetime FROM user WHERE salaryIS NOT NULL
  正例：
  SELECT id, userName,UserPawword,salary,createdatetime FROM user WHERE age>0
  

  使用理由：
  使用了is null或者 is not null，也不是说就不会不走索引，因为这个跟mysql版本以及查询成本都有关；
  如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引；
  其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；
  如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；

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

  SELECT id, userName,UserPawword,salary,createdatetime FROM user WHERE age<>10
  SELECT id, userName,UserPawword,salary,createdatetime FROM user WHERE age!=10
  

  使用理由：
  使用!=和<>很可能会让索引失效应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
  根据实际情况业务而定，不是一定不要实现。

• 八、提高group by语句的效率

  反例：先分组在过滤
  select username, avg（salary） from user
  group by username
  having username='develop' or username= 'test';
  正例：先过滤再分组
  select username，avg（salary） from user
  where username='develop' or username= 'test' 
  group by username;
  

  使用理由：
  可以在执行到该语句前，把不需要的记录过滤掉，减少数据。

• 九、清空表时优先使用truncate
  truncate table在功能上与不带 where子句的 delete语句相同：
  二者均删除表中的全部行。但 truncate table比 delete速度快，且使用的系统和事务日志资源少（本人一直经常使用）。
  delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。
  truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
  truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变。
  新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。
  如果要删除表定义及其数据，请使用 drop table语句。
  对于由 foreign key约束引用的表，不能使用 truncate table，而应使用不带 where子句的 DELETE 语句。
  由于 truncate table不记录在日志中，所以它不能激活触发器。
  truncate table不能用于参与了索引视图的表。

• 十、操作delete或者update语句，加个limit或者循环分批次删除
  1、降低写错SQL的代价，清空表数据可不是小事情，一个手抖全没了，难道要删库跑路？如果加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的。
  2、SQL效率很可能更高SQL中加了limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。
  3、避免长事务delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
  4、数据量大的话，容易把CPU打满如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢。
  5、锁表一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

• 十一、批量插入性能提升

  多条提交
  INSERT INTO user (id,username) VALUES(1,'张三写代码');
  
  INSERT INTO user (id,username) VALUES(2,'李四找BUg');
  
  批量提交
  INSERT INTO user (id,username) VALUES(1,'哪吒编程'),(2,'妲己');
  

  使用理由：
  默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。

• 十二、表连接不宜太多，索引不宜太多，一般推荐4个左右
  1、表连接不宜太多，一般关联的表个数越多，编译的时间和开销也就越大。每次关联内存中都生成一个临时表，应该把连接表拆开成较小的几个执行，可读性更高如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了阿里规范中，建议多表联查三张表以下
  2、索引不宜太多，一般索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；索引表的数据是排序的，排序也是要花时间的；insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

• 十三、避免在索引列上使用内置函数

  反例：
  SELECT id, userName,UserPawword,salary,createdatetime FROM user 
  WHERE DATE_ADD(createdatetime ,INTERVAL 7 DAY) >=NOW();
  正例：
  SELECT id, userName,UserPawword,salary,createdatetim FROM user 
  WHERE  createdatetim >= DATE_ADD(NOW(),INTERVAL 7 DAY);
  

  使用理由：
  使用索引列上内置函数，索引失效。

• 十四、组合索引
  排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。

  create index IDX_USERNAME_TEL on user(username,UserPawword,userid,createdatetime);
  select userid,username,UserPawword from user where age= 18 and username= '老李' 
  order by createdatetime desc; 
  

  实际上只是查询出符合 age= 18 and username = '老李’条件的记录，并按降序排序，但写成order by createdatetime desc性能较差。

• 十五、优化like语句
  模糊查询like，程序员最爱使用的，但是like很可能让你的索引失效。

  反例：
  select id, userName,UserPawword,salary,createdatetime from user 
  where username like '%老张' (不使用索引)
  select id, userName,UserPawword,salary,createdatetime from user 
  where username like '%老李%' (不使用索引)
  正例：
  select * from user where username like '老张%' (使用索引) 
  

  使用理由：
  首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；
  左模糊like ‘%…’无法直接使用索引，但可以利用reverse + function index的形式，变化成 like ‘…%’；
  全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。

• 十六、很多时候用 exists 代替 in 是一个好的选择
  确实啊，用EXISTS代替IN有时候确实能让查询跑得更快，特别是当子查询返回的结果集很大的时候。
  因为EXISTS只要找到第一个符合条件的记录就会返回结果，而IN则需要把子查询的结果都加载进来，再一个个比对。所以，如果只是想判断是否存在符合条件的记录，用EXISTS确实是个好选择！

• 十七、count统计

  select count(*) from user
  

  这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

• 十八、关于临时表
  1、避免频繁创建和删除临时表，以减少系统表资源的消耗；
  2、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log；
  3、如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；
  4、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定；
  *** 十九、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引**
  索引确实不适合建在有大量重复数据的字段上，比如性别字段，因为这样的字段上建立索引并不会带来太大的查询性能提升，反而可能会因为索引的维护成本而降低写操作的性能。
  而对于排序字段，创建索引确实是一个好主意。因为索引能够大大提高排序操作的效率，尤其是在处理大量数据时。
  数据库系统可以利用索引的有序性，直接进行排序操作，而不需要对数据进行额外的排序算法处理。

• 二十、尽量避免使用游标
  虽然游标能够让我们逐行处理查询结果，但是在处理大量数据时，游标会变得非常慢，因为它需要频繁地进行磁盘I/O操作。
  所以啊，如果可能的话，尽量避免使用游标，而是尽量使用集合操作来处理数据。比如，用JOIN、GROUP BY、HAVING等SQL语句来代替游标操作，这样通常能获得更好的性能。
  当然啦，有些情况下游标还是必不可少的，比如需要逐行处理复杂逻辑的时候。但是，在使用游标之前，最好先考虑一下有没有更好的替代方案哦！

小结

以上的20种SQL语句优化，也是小主在开发过程中常用的。当然还有更多的语句优化，希望大家在评论区种发出来，一起进步。谢谢大家！比心！！！