最详细Sql语句优化大汇总面试必问我就不信你还不会Sql优化

本文链接：https://blog.csdn.net/m0_62314761/article/details/129336444

欢迎补充和纠正！！！

基础知识

一条sql语句的执行过程

客户端发送一条查询语句到服务器，然后将SQL发送到SQL接口；
SQL接口会在先根据哈希值查询缓存，如果命中缓存返回存储在缓存中的数据；
若未命中缓存，MySQL解析器会先进行词法分析将SQL语句转化为二进制结构根据关键字将SQL语句进行解析，并生成一颗对应的解析树
MySQL解析器再进行语法分析将使用MySQL语法进行验证和解析。
然后再交给优化器按照最优的方案进行
执行器根据执行计划查询执行引擎，调用API接口调用存储引擎来查询数据；
将结果返回客户端，并进行缓存；

注意：在MySQL8.0后缓存的功能是被移除了的。因为查询在业务中是非常频繁的，而缓存也经常失效，在实际业务非常麻烦。特别对于需要经常更新的数据来说，MySQL查询不建议使用缓存。对于不经常更新的数据来说缓存还是可以的。

sql语句关键字的执行顺序

from
join
on
where
group by
avg, sum等聚合函数
having
select
distinct
order by
limit

SQL优化

使用explain来分析Sql语句

使用：

explain <sql语句>，返回结果如下

其中 type和 extra 是需要我们重点关注的

type

性能排名：排名越靠后性能越高；实际优化中达到ref或者range级别即可

1.All
全表扫描，需要优化

2.index
全索引扫描，效率比All好。两种情况：1.使用了覆盖索引。2.使用了索引排序

3.range
表示利用索引查询限制了范围，在指定范围内查询。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range。

4.index_subquery
利用索引来关联子查询，不再扫描全表

5.unique_subquery

和index_subquery类似，使用的是唯一索引

6.index_merge
查询中使用多个索引组合使用

7.ref_or_null
对某个索引需要关联查询，也需要null值的条件

8.ref
使用了非唯一性索引进行数据查找

9.eq_ref
使用了唯一性索引进行数据查找

10.const

表里最多一行数据配合，主键查询时触发较多

11.system

表里只有一行记录，等于系统表

extra关键字
- Using filesort：说明mysql没有按照表内的索引顺序进行读取而是会对数据使用一个外部的索引排序
  - mysql无法利用索引进行排序，只能利用排序算法进行排序消耗额外的位置
- Using temporary：MySQL在对查询结果排序时使用临时表。
  - 建立临时表来保存中间结果，查询完成之后把临时表删除
- Using index：查询时不需要回表查询直接通过索引就可以获取查询的结果数据。（使用到了覆盖索引）
  - 使用到了覆盖索引，直接从索引读取数据而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
- using where：Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；
  - 使用where进行条件过滤
- Using join buffer：使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的joinbuffer调大一些。
- impossible where：where子句的值总是false ，不能用来获取任何元组
possible_keys

显示可能应用在这张表中的索引，但不一定被查询实际使用

实际使用的索引。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。一般来说，索引长度越长表示精度越高，效率偏低；长度越短，效率高，但精度就偏低。

表示哪一列被使用了，常数表示这一列等于某个常数。

rows

找到所需记录需要读取的行数。

filter

表示选取的行和读取的行的百分比

尽量用varchar代替char

varcahr是可变长度字段，根据实际内容进行空间存储可以节省存储空间
char是按照声明大小存储，不够的会补空格
对于查询来说在应该小字段里查询效率更高而且节省空间

使用数值代替字符串

性别（sex）：0-代表女，1-代表男；
商品状态（state）：1-上架，2-下架
支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态（state）：1-开启，2-暂停，3-停止

不建议对大量重复数据建索引

字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

重要信息要进行伪删除

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

这里的删除只是是否显示给用户看，并非真正从数据库中删除了。因为在大系统中表之间的关系十分复杂，随意进行删除或者更改操作都会使其他表出现问题
在数据量大的情况下这种速度快，通过where 条件即可过滤

查询尽量不要Select * 而是返回真正需要的字段

无用字段增加网络消耗，消耗更多的 CPU 和 IO 以网络带宽资源
如果*中的数据范围过大，可能会不走索引，降低性能
增加了回表的可能性（会通过辅助索引去过滤不必要的信息，增加了一次IO操作）

在Select 后面使用函数可以通过索引，但where后面接函数(内置函数)就不行

1.可以走索引

Select sum(a) from table where b = 1;

2.不走索引（但MySQL8.0之后就增加了函数索引，可以走索引）

因为索引保存的是索引字段的原始值而不是经过函数计算后的值

Select a from table where length(b)=6;

禁止使用不含字段列表的 INSERT 语句

如：

insert into t values ('a','b','c');

应使用：

insert into t(c1,c2,c3) values ('a','b','c');

使用预编译语句提高性能

预编译就是将一些灵活的参数值以占位符?的形式给代替掉，我们把参数值给抽取出来，把SQL语句进行模板化。让MySQL执行相同的语句但只是参数不同的时候就不用再解析SQL语句浪费性能
还可以解决动态 SQL 所带来的 SQL 注入的问题。
相同语句可以一次解析，多次使用，提高处理效率。

避免数据类型的隐式转换

当操作符左右两边的数据类型不一致时就会发生隐式转换
当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

隐式转换会导致索引失效如:

select name,phone from customer where id = '111';

这种id的数据类型应该为数字的时候却用了字符

避免使用子查询，可以把子查询优化为 join 操作

通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。
子查询性能差的原因：
- 子查询会产生大量临时表，而频繁的产生和删除临时表会消耗过多的 CPU 和 IO 资源，降低性能，产生慢查询
- 子查询中的结果集会被存储到临时表，而内存临时表还是磁盘临时表都不会存在索引，因此会导致无法使用索引，降低查询性能

避免使用 JOIN 关联太多的表

对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由 join_buffer_size 参数进行设置。
在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。
如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。
同时对于关联操作来说，会产生临时表操作，影响查询效率，MySQL 最多允许关联 61 个表，建议不超过 5 个。

减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

避免使用ENUM类型

修改ENUM值需要使用ALTER语句；
ENUM类型的ORDER BY操作效率低，需要额外操作；
禁止使用数值作为ENUM的枚举值；

where后面尽量不使用字符运算操作

不走索引

where b-1 = 6;

走索引

where b = 6-1;

因为索引保存的是索引字段的原始值，而不是表达式计算后的值，因此会走全表扫描然后依次计算得到对应的值

使用like查询的时候要小心和%的搭配

如果使用like的时候后面没有跟通配符“%”则是和普通的 “=”是一样的

SELECT * FROM products WHERE products.prod_name like '1000';

如果是全模糊查询或者左模糊查询如 like ‘%a’ 这种情况就不会走索引，因为 %在前造成的范围过大
如果是右模糊查询如 like ‘....%’ ，是有可能使用索引的

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

在 WHERE 子句中，如果在 or 前的条件列是索引列，而在 or 后的条件列不是索引列，那么索引会失效举个例子，比如下面的查询语句，b 是主键，e 是普通列，从执行计划的结果看，是走了全表扫描。

对应同一列进行 or 判断时，使用 in 代替 or

in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

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

可能会因为返回的值过大而走全表扫描不走索引

in查询中如果结果集过大，索引会失效

使用 in肯定会走索引，但是当in的取值范围超过结果集的30%时会导致索引失效，走全表扫描

对于经常更新的字段不建议用索引

insert或update时有可能会重建索引。如果数据量大的话需要操作就会很频繁，影响性能

索引不是越多越好

索引并非越多越好，一般是五个以内，虽然提高了查询效率但如果频繁更新字段反而会降低性能
数据量小的表不需要建立索引，建立会增加额外的索引开销
不经常引用的列不要建立索引，因为不常用，即使建立了索引也没有多大意义。对经常用于查询的字段应该创建索引。
经常频繁更新的列不要建立索引，因为肯定会影响插入或更新的效率
数据重复且分布平均的字段，因此他建立索引就没有太大的效果（例如性别字段，是否上架等等，不适合建立索引）
数据变更需要维护索引，意味着索引越多维护成本越高。当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
更多的索引也需要更多的存储空间，一个表中很够创建多个索引，这些索引度会被存放到一个索引文件中(专门存放索引的地方)

对于频繁更新的字段优先考虑使用覆盖索引

覆盖索引：就是包含了所有(覆盖)查询字段的索引。
覆盖索引的好处：
- 避免Innodb表进行索引的二次查询
  - Innodb是以聚集索引的顺序来存储的，对于Innodb来说，二级索引（辅助索引）在叶子节点中所保存的是行的主键(PRIMARY KEY)信息的键，如果是用普通索引(INDEX)查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们所需要的数据，即回表查询，查询了两颗B+ tree，增加了IO操作。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。
- 可以把随机IO变成顺序IO加快查询效率
  - 由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

注意：如果使用主键查找，那么使用的是聚簇索引，能直接获取到数据，而如果我们使用非聚簇索引查找，如果该索引不包含我们需要查找的的全部字段，那么就需要根据叶子节点存储的主键值在进行一次查找来找到我们需要的数据，这就是回表查询

避免建立冗余索引和重复索引

增加了查询优化器生成执行计划的时间

重复索引示例：primary key(id)、index(id)、unique index(id)
冗余索引示例：index(a,b,c)、index(a,b)、index(a);（这里如果不明白为什么冗余的可以看看联合索引部分）

用distinct过滤去重的时候字段要少

索引失效

Select DiSTINCT * from table;

索引生效

Select DiSTINCT id from table;

当含有DISTINC关键字的时候占用cpu的时间会高于不带的语句。当查询的字段过多的话，占用的系统资源就会很多，性能下降

where中使用默认值代替null

索引失效

Select * from table where a IS NOT NULL;

索引生效

Select * from table where a>0;

如果是用null或者is not null的话可能有时候让索引失效，成本比走索引更高的话就会全表扫描。而换成默认值就更可能走索引

避免产生大事务操作或者频繁产生事务

如插入的时候使用批量插入

多条提交：

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语句是需要事务控制因此导致每条都需要事务开启和事务提交导致大量阻塞；而批量处理是一次事务开启和提交。但数据量少是体现不出来的。长时间的阻塞会沾满数据库的可用连接，使其他应用无法连接数据库，因此要注意大量的插入或者修改操作要分批。

超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,加个limit或者分批多次进行操作

大批量操作可能会造成严重的主从延迟
- 主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况
binlog 日志为 row 格式时会产生大量的日志
- 大批量写操作会产生大量日志，特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因
降低写错Sql的代价
- 如果不小心把表数据全部清空那就是很大的事情，所以加个limit也只是丢失部分数据，可以通过binlog日志恢复
避免长事务
- 如果delete操作的字段加了索引，那么MySQL会将所有相关的行加写锁和间隙锁，删除量大的时候还会造成锁表，会影响业务
数据量大的话容易是的CPU占满

尽量控制单表数据量的大小，建议控制在500万以内。

500万并不是MySQL数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。
可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

读写分离与分库分表

当数据量达到一定的数量之后，限制数据库存储性能的就不再是数据库层面的优化就能够解决的；这个时候往往采用的是读写分离与分库分表同时也会结合缓存一起使用，而这个时候数据库层面的优化只是基础。
读写分离适用于较小一些的数据量；分表适用于中等数据量；而分库与分表一般是结合着用，这就适用于大数据量的存储。

group by语句应先过滤再分组

错误做法：先分组，再过滤

select job, avg（salary） from employee 
group by job
having job ='IT' or job = 'iKUN';

正确做法：先过滤，后分组

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

可以过滤掉不必要的字段，提高性能

使用联合索引要注意最左匹配原则

创建一个联合索引(`sname`, `s_code`, `address`)

INDEX `联合索引`(`name`, `s_code`, `address`) USING BTREE

符合最左匹配，索引生效
- where name = 'kk'
- where name = 'kk' and s_code = 11
- where s_code = 11 and name = 'kk' （虽然不是name在前，但底层会优化到最前）
- where s_code = 11 and address = '广东'
- where name = 'kk' and s_code = 11 and address = '广东'
不符合最左匹配，索引失效
- where address = '广东'
- where s_code = 11 and address = '广东'
总结：创建一个联合索引的时候，如(a1,a2,a3)，相当于创建了（a1）、(a1,a2)和(a1,a2,a3)三个索引，这就是最左匹配原则。有点类似于闯关游戏，得先把前面的关卡闯过后才可闯后面的
但其实联合索引中有个跳跃扫描机制，可以打破最左匹配原则。

不要有超过5个以上的表连接和索引建立

表连接：
- 关联的表个数越多，编译的时间和开销也就越大
- 每次关联内存中都生成一个临时表，频繁创建删除临时表会降低性能
- 应该把连接表拆开成较小的几个执行，可读性更高
- 如果一定需要连接很多表才能得到数据，那就是设计有问题
- 阿里规范中，建议多表联查三张表以下
索引建立：
- 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
- 索引可以理解为一个就是一张表，其可以存储数据，而数据就要占空间；
- 索引表的数据是排序的，排序也是要花时间的，因此建议是主键的话用自增；
- insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；
- 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

关于临时表

避免频繁创建和删除临时表，以减少系统表资源的消耗；
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log；
如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。