Sql优化总结！详细！（2021最新面试必问）

阳光穿透心脏的1/2处 2022-11-17 13:40 256阅读 0赞

### Sql优化 ###

*  Sql执行顺序
 *  基础Sql优化
 *   *  查询SQL尽量不要使用select \*，而是具体字段
     *  避免在where子句中使用or来连接条件
     *  使用varchar代替char
     *  尽量使用数值替代字符串类型
     *  查询尽量避免返回大量数据
     *  使用explain分析你SQL执行计划
     *  是否使用了索引及其扫描类型
     *  创建name字段的索引
     *  优化like语句：
     *  字符串怪现象
     *  索引不宜太多，一般5个以内
     *  索引不适合建在有大量重复数据的字段上
     *  where限定查询的数据
     *  避免在索引列上使用内置函数
     *  避免在where中对字段进行表达式操作
     *  避免在where子句中使用!=或<>操作符
     *  去重distinct过滤字段要少
     *  where中使用默认值代替null
 *  高级SQL优化
 *   *  批量插入性能提升
     *  批量删除优化
     *  伪删除设计
     *  提高group by语句的效率
     *  复合索引最左特性
     *  排序字段创建索引
     *  删除冗余和重复的索引
     *  不要有超过5个以上的表连接
     *  inner join 、left join、right join，优先使用inner join
     *  in子查询的优化
     *  尽量使用union all替代union

# Sql执行顺序 #

*  **(8)** `SELECT`**(9)** `DISTINCT` column,…  
    选择字段 、去重
 *  **(6)** `AGG_FUNC(column or expression)`,…  
    聚合函数
 *  **(1)** `FROM [left_table]`  
    选择表
 *  **(3)** <join\_type> `JOIN` <right\_table>  
    链接
 *  **(2)** `ON` <join\_condition>  
    链接条件
 *  **(4)** `WHERE` <where\_condition>  
    条件过滤
 *  **(5)** `GROUP BY` <group\_by\_list>  
    分组
 *  **(7)** `HAVING` <having\_condition>  
    分组过滤
 *  **(10)** `ORDER BY` <order\_by\_list>  
    排序
 *  **(11)** `LIMIT` count OFFSET count;  
    分页

# 基础Sql优化 #

## 查询SQL尽量不要使用select \*，而是具体字段 ##

**反例：**

SELECT * FROM student

**正例：**

SELECT id,NAME FROM student

**理由：**

*  字段多时，大表能达到100多个字段甚至达200多个字段
 *  只取需要的字段，节省资源、减少网络开销
 *  select \* 进行查询时，很可能不会用到索引，就会造成全表扫描

## 避免在where子句中使用or来连接条件 ##

查询id为1或者薪水为3000的用户：

**反例：**

SELECT * FROM student WHERE id=1 OR salary=30000

**正例：**  
使用union all

SELECT * FROM student WHERE id=1
    UNION ALL
    SELECT * FROM student WHERE salary=30000

分开两条sql写

SELECT * FROM student WHERE id=1
    SELECT * FROM student WHERE salary=30000

**理由：**

*  使用or可能会使索引失效，从而全表扫描
 *  对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的

## 使用varchar代替char ##

**反例：**

`deptname` char(100) DEFAULT NULL COMMENT '部门名称'

**正例：**

`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'

**理由：**

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

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

*  **主键（id）**：primary key优先使用数值类型int，tinyint
 *  **性别（sex）**：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
 *  **支付方式（payment）**：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
 *  **服务状态（state）**：1-开启、2-暂停、3-停止
 *  **商品状态（state）**：1-上架、2-下架、3-删除

## 查询尽量避免返回大量数据 ##

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。  
通常采用分页，一页习惯10/20/50/100条。

## 使用explain分析你SQL执行计划 ##

SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

EXPLAIN
    SELECT * FROM student WHERE id=1

返回结果：  
![在这里插入图片描述][20210409162148132.png]

## 是否使用了索引及其扫描类型 ##

[SQL索引概念（详解B+树）][SQL_B]

**`type：`**

*  ALL 全表扫描，没有优化，最慢的方式
 *  index 索引全扫描
 *  range 索引范围扫描，常用语<，<=，>=，between等操作
 *  ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
 *  eq\_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
 *  const 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理
 *  null MySQL不访问任何表或索引，直接返回结果
 *  System 表只有一条记录(实际中基本不存在这个情况)

> **性能排行：**  
> System > const > eq\_ref > ref > range > index > ALL

**`possible_keys：`**

*  显示可能应用在这张表中的索引

**`key：`**

*  真正使用的索引方式

## 创建name字段的索引 ##

提高查询速度的最简单最佳的方式

ALTER TABLE student ADD INDEX index_name (NAME)

## 优化like语句： ##

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效

**反例：**

EXPLAIN
    SELECT id,NAME FROM student WHERE NAME LIKE '%1'
    EXPLAIN
    SELECT id,NAME FROM student WHERE NAME LIKE '%1%'

**正例：**

EXPLAIN
    SELECT id,NAME FROM student WHERE NAME LIKE '1%'

**理由：**  
未使用索引：故意使用sex非索引字段

EXPLAIN
    SELECT id,NAME FROM student WHERE NAME=1 OR sex=1

![在这里插入图片描述][20210409162257456.png]

主键索引生效

EXPLAIN
    SELECT id,NAME FROM student WHERE id=1

![在这里插入图片描述][20210409162254883.png]

索引失效，type=ALL，全表扫描

EXPLAIN
    SELECT id,NAME FROM student WHERE id LIKE '%1'

![在这里插入图片描述][20210409162252199.png]

## 字符串怪现象 ##

**反例：**

#未使用索引
    EXPLAIN
    SELECT * FROM student WHERE NAME=123

**正例：**

#使用索引
    EXPLAIN
    SELECT * FROM student WHERE NAME='123'

**理由：**

*  为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较

## 索引不宜太多，一般5个以内 ##

*  索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
 *  索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
 *  再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要
 *  insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
 *  一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要

## 索引不适合建在有大量重复数据的字段上 ##

如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引  
列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

## where限定查询的数据 ##

数据中假定就一个男的记录

**反例：**

SELECT id,NAME FROM student WHERE sex='男'

**正例：**

SELECT id,NAME FROM student WHERE id=1 AND sex='男'

**理由：**

*  需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销

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

业务需求：查询最近七天内新生儿（用学生表替代下）

**给birthday字段创建索引：**

ALTER TABLE student ADD INDEX idx_birthday (birthday)

**当前时间加7天：**

SELECT NOW()	
    SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)

**反例：**

EXPLAIN
    SELECT * FROM student
    WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

**正例：**

EXPLAIN
    SELECT * FROM student
    WHERE  birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);

**理由：**

*  使用索引列上内置函数
 *  索引失效：  
    ![在这里插入图片描述][20210409162359706.png]
 *  索引有效：  
    ![在这里插入图片描述][20210409162405558.png]

## 避免在where中对字段进行表达式操作 ##

**反例：**

EXPLAIN
    SELECT * FROM student WHERE id+1-1=+1

**正例：**

EXPLAIN
    SELECT * FROM student WHERE id=+1-1+1

EXPLAIN
    SELECT * FROM student WHERE id=1

**理由：**

*  SQL解析时，如果字段相关的是表达式就进行全表扫描

![在这里插入图片描述][20210409162410691.png]

*  字段干净无表达式，索引生效  
    ![在这里插入图片描述][20210409162421235.png]

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

应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

**反例：**

EXPLAIN
    SELECT * FROM student WHERE salary!=3000

EXPLAIN
    SELECT * FROM student WHERE salary<>3000

**理由：**

*  使用!=和<>很可能会让索引失效  
    ![在这里插入图片描述][20210409162430222.png]

## 去重distinct过滤字段要少 ##

#索引失效
    EXPLAIN
    SELECT DISTINCT * FROM student

#索引生效
    EXPLAIN
    SELECT DISTINCT id,NAME FROM student

EXPLAIN
    SELECT DISTINCT NAME FROM student

**理由：**

*  带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间

## where中使用默认值代替null ##

**环境准备：**

#修改表，增加age字段，类型int，非空，默认值0
    ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

#修改表，增加age字段的索引，名称为idx_age
    ALTER TABLE student ADD INDEX idx_age (age);

**反例：**

EXPLAIN
    SELECT * FROM student WHERE age IS NOT NULL

**正例：**

EXPLAIN
    SELECT * FROM student WHERE age>0

**理由：**

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

# 高级SQL优化 #

## 批量插入性能提升 ##

大量数据提交，上千，上万，批量性能非常快，mysql独有

**多条提交：**

INSERT INTO student (id,NAME) VALUES(4,'name1');
    INSERT INTO student (id,NAME) VALUES(5,'name2');

**批量提交：**

INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');

**理由：**

*  默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
 *  数据量小体现不出来

## 批量删除优化 ##

避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

**反例：**

#一次删除10万或者100万+？
    delete from student where id <100000;

#采用单一循环操作，效率低，时间漫长
    for（User user:list）{
      delete from student;
    }

**正例：**

#分批进行删除，如每次500
    for(){
    delete student where id<500;
    }

delete student where id>=500 and id<1000;

**理由：**

*  一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

## 伪删除设计 ##

商品状态（state）：1-上架、2-下架、3-删除

**理由：**

*  这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
 *  同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
 *  通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
 *  操作速度快，特别数据量很大情况下

## 提高group by语句的效率 ##

可以在执行到该语句前，把不需要的记录过滤掉

**反例：先分组，再过滤**

select job，avg（salary） from employee  
    group by job 
    having job ='president' or job = 'managent';

**正例：先过滤，后分组**

select job，avg（salary） from employee 
    where job ='president' or job = 'managent' 
    group by job;

## 复合索引最左特性 ##

创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAIN
    SELECT * FROM student WHERE NAME='name1'

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN
    SELECT * FROM student WHERE salary=3000

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN
    SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN
    SELECT * FROM student WHERE salary=3000 AND NAME='name1'

**理由：**

*  复合索引也称为联合索引
 *  当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
 *  联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

## 排序字段创建索引 ##

什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

#使用*，包含了未索引的字段，导致索引失效
    EXPLAIN
    SELECT * FROM student ORDER BY NAME;
    
    EXPLAIN
    SELECT * FROM student ORDER BY NAME,salary
    
    #name字段有索引
    EXPLAIN
    SELECT id,NAME FROM student ORDER BY NAME
    
    #name和salary复合索引
    EXPLAIN
    SELECT id,NAME FROM student ORDER BY NAME,salary
    
    EXPLAIN
    SELECT id,NAME FROM student ORDER BY salary,NAME
    
    #排序字段未创建索引，性能就慢
    EXPLAIN
    SELECT id,NAME FROM student ORDER BY sex

## 删除冗余和重复的索引 ##

SHOW INDEX FROM student 
    
    #创建索引index_name
    ALTER TABLE student ADD INDEX index_name (NAME)
    
    #删除student表的index_name索引
    DROP INDEX index_name ON student ;
    
    #修改表结果，删除student表的index_name索引
    ALTER TABLE student DROP INDEX index_name ;
    
    #主键会自动创建索引，删除主键索引
    ALTER TABLE student DROP PRIMARY KEY ;

![在这里插入图片描述][20210409164210552.png]

## 不要有超过5个以上的表连接 ##

*  关联的表个数越多，编译的时间和开销也就越大
 *  每次关联内存中都生成一个临时表
 *  应该把连接表拆开成较小的几个执行，可读性更高
 *  如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
 *  阿里规范中，建议多表联查三张表以下

## inner join 、left join、right join，优先使用inner join ##

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

*  inner join 内连接，只保留两张表中完全匹配的结果集
 *  left join会返回左表所有的行，即使在右表中没有匹配的记录
 *  right join会返回右表所有的行，即使在左表中没有匹配的记录

**理由：**

*  如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点
 *  同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

## in子查询的优化 ##

日常开发实现业务需求可以有两种方式实现：

*  一种使用数据库SQL脚本实现
 *  一种使用程序实现  
    如需求：查询所有部门的所有员工：

#in子查询
    SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
    #这样写等价于：
    
    #先查询部门表
    SELECT id FROM tb_dept
    
    #再由部门dept_id，查询tb_user的员工
    SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：

List<> resultSet;
    for(int i=0;i<B.length;i++) {
      for(int j=0;j<A.length;j++) {
        if(A[i].id==B[j].id) {
          resultSet.add(A[i]);
          break;
        }
      }
    }

上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。

**理由：**

*  数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿

## 尽量使用union all替代union ##

**反例：**

SELECT * FROM student
    UNION
    SELECT * FROM student

**正例：**

SELECT * FROM student
    UNION ALL
    SELECT * FROM student

**理由：**

*  union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
 *  union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
 *  union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION

[20210409162148132.png]: /images/20221022/d03e20e9161745dba1af10619762ebee.png
[SQL_B]: https://blog.csdn.net/weixin_53601359/article/details/115558427
[20210409162257456.png]: /images/20221022/b2cbd9e1dcf7424ba334affbb77d363b.png
[20210409162254883.png]: /images/20221022/69c64f251f1146eba09790bfc7d7c5b8.png
[20210409162252199.png]: /images/20221022/e99fad1a071843e3a181613e92a04889.png
[20210409162359706.png]: /images/20221022/fa64ccc14d7f489db06787dd6257b1a6.png
[20210409162405558.png]: /images/20221022/d7f51cbc7f404bafa341edaa3953f8ee.png
[20210409162410691.png]: /images/20221022/e0a8352530944460890090b2ba63c680.png
[20210409162421235.png]: /images/20221022/23acea7b443a430e835a9e41c0c5d28f.png
[20210409162430222.png]: /images/20221022/0c5e39d8b3794c8cabd64e9d59d7736e.png
[20210409164210552.png]: /images/20221022/929aa547774d47e19eec8f060d87681a.png