Mysql | sql优化实战与常用经验总结

前言

      最近工作上遇到一个sql查询缓慢的问题，觉得这个sql对于sql优化的知识点运用得比较全面。遂记录总结一下，同时整理一下sql优化相关的知识点

SQL优化知识

一个sql的查询流程

首先我们看一看一个sql在MySQL里面是如何运行的：

1. 客户端client向mysql服务器发起sql比如查询操作的连接
2. 连接成功后，在server的服务层，先对sql进行优化（比如程序员写的sql优化策略，通常是索引优化，程序员自定义），之后server底层会再次对优化后的sql进行优化（这一点也就是有时候我们明明按照索引优化的策略优化了sql却效果没有按照想象中的来的原因）
3. 在引擎层，根据哪种引擎（InnoDB，MyISAM）来进行查询
4. crud存储层的存储的数据

知道了上述一个sql的执行流程，我们对于sql优化会有几个原则：

1. 减少数据访问： 设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
2. 返回更少的数据： 只返回需要的字段和数据分页处理 减少磁盘io及网络io
3. 减少交互次数： 批量DML操作，函数存储等减少数据连接次数
4. 减少服务器CPU开销： 尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
5. 利用更多资源： 使用表分区，可以增加并行操作，更大限度利用cpu资源

总结到SQL优化中，就三点:

1. 最大化利用索引；
2. 尽可能避免全表扫描；
3. 减少无效数据的查询；

select执行顺序

1. SELECT 
2. DISTINCT
3. FROM 
4. JOIN
5. ON
6. WHERE
7. GROUP BY
8. HAVING 
9. ORDER BY
10.LIMIT

sql优化常用策略

1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. null
   2.1 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
   2.2 最好不要给数据库字段留NULL，尽可能的使用 NOT NULL或者空字符串或者0等数字填充数据库.
3. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
4. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

explain select * from teacher where tname ='' or tcid >1 ; --将or左侧的tname 失效。

5. exist和in
   select …from table where exist (子查询) ;
   select …from table where 字段 in (子查询) ;

   如果主查询的数据集大，则使用In ,效率高。
   如果子查询的数据集大，则使用exist,效率高。

   exist语法： 将主查询的结果，放到子查需结果中进行条件校验（看子查询是否有数据，如果有数据 则校验成功） ，
   如果 复合校验，则保留数据；

   select tname from teacher where exists (select * from teacher) ;
   –等价于select tname from teacher

   select tname from teacher where exists (select * from teacher where tid =9999) ;

   in: select …from table where tid in (1,3,5) ;

6. order by 优化
   order by通常会explain分析出来是走的using filesort。
   using filesort 有两种算法：双路排序、单路排序 （根据IO的次数）
   MySQL4.1之前 默认使用 双路排序；双路：扫描2次磁盘（1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序） 2：扫描其他字段 ）
   –IO较消耗性能
   MySQL4.1之后 默认使用 单路排序 ： 只读取一次（全部字段），在buffer中进行排序。但种单路排序 会有一定的隐患 （不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行“分片读取、多次读取”。
   注意：单路排序 比双路排序 会占用更多的buffer。
   单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小： set max_length_for_sort_data = 1024 单位byte

   如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路 （太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

   提高order by查询的策略：
   a.选择使用单路、双路 ；调整buffer的容量大小；
   b.避免select * …
   c.复合索引 不要跨列使用 ，避免using filesort
   d.保证全部的排序字段 排序的一致性（都是升序 或 降序）

7. 模糊查询 like 尽量以“常量”开头，不要以’%'开头，否则索引失效

	explain select * from teacher  where tname like '%x%'; --tname索引失效

	explain select * from teacher  where tname like 'x%';  --正确做法
 
	explain select tname from teacher  where tname like '%x%'; 
	--如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。

8. 尽量不要使用类型转换（显示、隐式），否则索引失效
   tname类型为string，如果第二种写法会把 123 转换为 ‘123’，导致索引失效

	explain select * from teacher where tname = 'abc' ;
	explain select * from teacher where tname = 123 ;//程序底层将 123 -> '123'，即进行了类型转换，因此索引失效

9. 复合索引
   9.1 复合索引，不要跨列或无序使用（最佳左前缀）
   9.2 复合索引，尽量使用全索引匹配
10. 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

	select ..where A.x = .. ;  --假设A.x是索引
	不要：select ..where A.x*3 = .. ;
	索引为（authorid，typeid）
	explain select * from book where authorid = 1 and typeid = 2 ;--用到了 authorid， typeid 2个索引
	explain select * from book where authorid = 1 and typeid*2 = 2 ;--用到了authorid 1个索引
	explain select * from book where authorid*2 = 1 and typeid*2 = 2 ;----用到了0个索引
	explain select * from book where authorid*2 = 1 and typeid = 2 ;----用到了0个索引,原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。

	drop index idx_atb on book ; 
	alter table book add index idx_authroid (authorid) ;
	alter table book add index idx_typeid (typeid) ;
	explain select * from book where authorid*2 = 1 and typeid = 2 ;（authorid*2 无效，typeid 有效）

11. 避免出现select *。使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。
12. 多表关联查询时，小表在前，大表在后。
    12.1.小表驱动大表
    12.2.索引建立在经常查询的字段上

案例

场景：一个订单表600w+数据，一个sql查询耗时30多秒
sql：
字段类型：

字段名	类型
ORG_ID	decimal
APPLY_TYPE	varchar
ORDER_STATUS	varchar
CERT_SN	varchar
END_TS	decimal
CREATE_TS	decimal

优化前：

SELECT 
     cmsorderen0_.ORDER_ID , 
     cmsorderen0_.APPLY_TYPE ,
     cmsorderen0_.ORDER_STATUS , 
     cmsorderen0_.CERT_SN , 
     cmsorderen0_.END_TS ，
     cmsorderen0_.CREATE_TS , 
     cmsorderen0_.APP_TYPEID , 
     cmsorderen0_.IS_DOWN 
FROM CMS_ORDER cmsorderen0_
WHERE 
  (cmsorderen0_.CERT_TYPE <> 'c'
  OR cmsorderen0_.CERT_TYPE IS NULL)
  AND (cmsorderen0_.CERT_DN LIKE '%12321412421%')
  AND cmsorderen0_.APPLY_TYPE = '1'
  AND cmsorderen0_.ORDER_STATUS = '45'
  AND cmsorderen0_.END_TS > 20210413151522986
  AND cmsorderen0_.CREATE_TS >= 20200910000000000
  AND cmsorderen0_.CREATE_TS <= 20200930235959000
  #AND cmsorderen0_.ORG_ID IN (1240121658350505984,1277514727949598720,1277514878332174336,1277514978160803840,1282508717988052992,1282509174139584512,1283606268992585728,1286574382143295488,1288744629134745600,1318829207002091520,1331870753645494272,1333680811224170496,1347449709463441408,1357156853049724928,1374269914449453056,1277515199653609472,1277515307996676096,1278525914136981504,1278526439221899264,1278606135326425088,1282508968450916352)
  AND cmsorderen0_.ORG_ID <> 1
  AND cmsorderen0_.STATUS = 1
  and order_id='T130794788051393331226661137'
ORDER BY cmsorderen0_.ORDER_STATUS ASC, cmsorderen0_.CREATE_TS DESC
LIMIT 10

优化后：

  SELECT 
    od.ORG_ID,
    od.ORDER_ID,
    od.STATUS,
    od.APPLY_TYPE,
    od.ORDER_STATUS,
    od.CREATE_TS,
    od.CERT_SN,
    od.END_TS,
    od.APP_TYPEID,
    od.CERT_TYPE,
    od.CERT_DN,
    od.IS_DOWN 
    FROM cms_order_temp od
    WHERE
      od.STATUS = '1' 
      AND od.APPLY_TYPE = '1' 
      AND od.ORDER_STATUS = '45' 
      AND od.CREATE_TS <= 20200930235959000 
      AND od.CREATE_TS >= 20200910000000000 
      AND od.END_TS > 20210413151522986 
      AND od.ORG_ID <> 1 
      AND od.CERT_TYPE <> 'c' -- 将null值 都填为缺省值空''
      AND ( od.CERT_DN LIKE '%12321412421%' ) 
      -- in() 条件件放最后
  ORDER BY
    od.ORDER_STATUS ASC,
    od.CREATE_TS DESC 
  LIMIT 10

看一看如何优化的：（这里俩张表不一致，是因为笔者把原表copy了一份作试验，还因为表数据大了创建索引会很慢）

1. 数据库的数据很多字段都是null，用空字符串代替。然后条件就可以去掉null的判断（这里未展示数据库数据）
2. 创建索引STATUS, APPLY_TYPE, ORDER_STATUS, CREATE_TS, END_TS
3. sql中使用了<>，如果实在无法避免，放在最后。前面走复合索引STATUS, APPLY_TYPE, ORDER_STATUS, CREATE_TS，最左前缀。
4. ORDER BY的顺序跟跟where和索引的字段和顺序尽量一致
5. 模糊查询这里也可以改一下（这里未改动）

还需优化点：
问题：过多的字段创建复合索引也有额外的问题，比如增大数据库的存储等，有时候也会导致走索引还会慢的情况。可以考虑做一下调整

表数据过大创建更新索引慢解决方法：
1.基于原表创建新表
2.创建索引
3.复制数据：insert into B from （select * from A）

总结

1. 通常情况下按照sql策略做一些sql的优化是没有问题的。但是考虑到mysql底层还会做一次优化，所以我们的优化会是概率问题，有时候不生效。
2. 用explain实际分析每条sql语句，避免using filesort文件排序（order by常出现），和临时表（group by常出现）
3. 除了sql索引优化，还可以从硬件，系统配置，数据库表结构方面多维度思考解决性能问题。但是主要还是基于sql索引优化来处理，因为改动成本最低

相关学习博客：
1.SQL优化最干货总结 - MySQL（2020最新版）（这篇文章的博主力推，写了很多关于MySQL的干货）
2.数据库SQL优化大总结之 百万级数据库优化方案
3.MYSQL INNODB B+树索引