sql优化总结

FearlessVoyager

已于 2023-12-13 16:15:25 修改

阅读量230

点赞数 3

分类专栏：数据库文章标签：数据库

于 2020-10-24 15:25:03 首次发布

本文链接：https://blog.csdn.net/qq_33807380/article/details/109258107

版权

数据库专栏收录该内容

14 篇文章 2 订阅

订阅专栏

一、MySQL查询过程

在这里插入图片描述
（1）连接器：主要负责跟客户端建立连接、获取权限、维持和管理连接。
（2）查询缓存：优先在缓存中进行查询，如果查到了则直接返回，如果缓存中查询不到，在去数据库中查询。MySQL缓存是默认关闭的，并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。因为如果缓存中的数据没有得到及时更新的话，容易查询出错误的数据。
（3）解析器/分析器：主要是对要执行的SQL语句进行词法解析、语法解析，最终得到抽象语法树，然后再使用预处理器对抽象语法树进行语义校验，判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影列字段是否在表中存在等。
（4）优化器：主要将SQL经过词法解析、语法解析后得到的语法树，通过数据字典和统计信息的内容，再经过一系列运算，最终得出一个执行计划，包括选择使用哪个索引等。
（5）执行器：根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。

二、sql语句优化

1、使用EXPLAIN分析sql执行计划

在explain展示的执行计划结果中，主要关注框起来的那几列。
在这里插入图片描述

2、合理的使用索引

从explain的执行计划中的key列的值可以看出使用的索引，从type列的值可以看出索引的效率由高到低依次为：system > const > eq_ref > ref > range > index > all。一般来说，至少保证type列的值达到range级别，最好能达到ref级以上。
（1）一般在查询条件中的字段可以使用索引：比如where、order by等语句后的字段。
在这里插入图片描述
（2）IS NULL在字段允许为null的情况下会走索引（type级别为ref），在字段不允许为null的情况下不走索引。

（3）IS NOT NULL在字段允许为null的情况下会走索引（type级别为range），在字段不允许为null的情况下不走索引。
在这里插入图片描述

（4）在使用’<>’、’ != '进行条件判断时，MySQL查询优化器会根据符合条件的记录的数量多少来决定用不用索引，比如符合不等于条件的结果远超过整个表的一半时，这时使用索引也没什么意义，MySQL查询优化器就会放弃使用索引，当符合条件的记录相对整个表较小时，则会使用索引查询。
在这里插入图片描述

（5）在使用like以通配符开头(%xxx)作为过滤条件时，MySQL索引失效会变成全表扫描。所以使用like时最好是保证开头不使用通配符，可以在结尾使用（xxx%）。

（6）不要在索引字段上做任何操作，如：算数运算，函数，类型转换等，否则会导致索引字段失效。

运算
函数
类型转换

（7）在使用or连接条件判断时，要保证or两边的字段都有索引，不然整个sql将会进行全表扫描。
在这里插入图片描述

（8）在使用and连接条件判断时，只要有一个条件字段有索引，这个sql执行就会使用索引。

（9）在查询字段比较少的情况下，可以将要查询的字段创建一个联合索引，这样MySQL在执行的时候会直接从索引里面获取到想要的数据，不需要再回表查询。
在这里插入图片描述
（10）在使用联合索引的时候，主义保证最左侧原则，即创建联合索引的最左边的字段一定要出现在where条件中，至于是在where条件的哪个位置无所谓。

（11）尽量不要在数据没有区分度的字段上创建索引，例如性别，因为性别无非只有男和女，就相当于把整个表的数据分成两部分，即便使用索引，也要扫描半个表才能找到想要的数据，这样索引就没有啥意义了，反而浪费磁盘空间。

（12）使用短索引。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3、只返回必要的列

在明确知道想要查询的字段时，尽量避免使用SELECT * 查所有字段，因为查询的字段越多，对cpu、io、内存、网络带宽的消耗也会越大，所以在查询时尽可能只返回确切的字段。
SELECT user_name, department_id FROM user

4、尽量避免使用子查询

子查询的内部执行计划是先执行子查询再执行外层查询，由于每次执行子查询都需要创建并删除临时表，会消耗大量资源，从而影响数据库性能。如：
SELECT t1.user_name, t1.department_id FROM user WHERE department_id = (SELECT id FROM roles WHERE rolename = '超级管理员')
可以改写成Inner Join，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表。如：
SELECT t1.user_name, t1.department_id FROM user t1 JOIN roles t2 ON t1.department_id = t2.id AND t2.rolename = '超级管理员'

5、用IN来替换OR

使用or的查询效率要比in低，例如：SELECT user_name, department_id FROM user WHERE id = 1 or id = 2 or id = 3，
可以使用in来进行查询。如：SELECT user_name, department_id FROM user WHERE id in(1,2,3)。
对于连续的数值，使用 between 的效率比in更高，如：SELECT user_name, department_id FROM user WHERE id BETWEEN 1 AND 3

6、合理使用LIMIT

SELECT user_name, department_id FROM user LIMIT offset , n offset：偏移量； n：每页的数据量
使用LIMIT可以有效的控制每次返回的数据量，从而避免一次性查询所有数据导致的效率慢的问题。不过当查询的页码越大时，使用limit分页查询会越来越慢。这是因为MySQL并不是跳过 (offset * n)行的数据，而是取 (offset+1)*N 行，然后丢弃前 (offset * n)行，返回 N 行，所以当 offset 特别大的时候，需要查询的数据量也就很大，当然效率就非常的低下。
优化的方法：SELECT user_name, department_id FROM user where id> 100000 LIMIT 20
100000为前一页最大的id，然后根据 id> 100000 来限制下一页的起点，这样就能直接跳过前面页码的数据，直接获取想要的该页的数据。

7、禁止不必要的Order By排序

如果我们对查询结果没有排序的要求，就尽量少用排序；一定要用排序的话，尽量在排序的字段上加上索引。
SELECT department_id, username FROM user WHERE department_id > 1 ORDER BY department_id DESC

8、Group By语句优化

group by默认会对分组字段进行排序，结果没有排序要求，可以在语句后面加 order by null紧张对分组字段进行排序；
SELECT department_id, COUNT(1) FROM user GROUP BY department_id ORDER BY NULL;
尽量给group by的字段加上索引，尽量避免使用having子句，可以使用where子句替换Having子句。having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤出符合条件的数据，就可以减少对全部数据的排序分组的操作，只针对符合条件的数据进行排序分组。
低效：SELECT department_id, COUNT(1) num FROM user GROUP BY department_id HAVING department_id > 3
高效：SELECT department_id, COUNT(1) num FROM user WHERE department_id > 3 GROUP BY department_id ORDER BY NULL;

9、尽量使用union all替换union

union all和union的差异主要是union会对查询出来的结果集中的数据都是进行去重操作，会增加大量的CPU运算，加大资源消耗及延迟。当我们明确知道查询结果中不会出现重复数据时，使用 union all 可以有效的提高查询速度。
SELECT user_name, department_id FROM user WHERE id = 1 UNION SELECT user_name, department_id FROM user WHERE id = 2
SELECT user_name, department_id FROM user WHERE id = 1 UNION ALL SELECT user_name, department_id FROM user WHERE id = 2

10、合理使用in和exists

in主要是判断条件字段的值是否在in后面的（）中，只要在（）中，就会出现在最终的查询结果集中。而EXISTS用于检查子查询是否至少会返回一行数据，如果是则为true，否则为false。只有为true时，才会把子查询中符合条件的数据查询出来。in和exists的主要区别在于查询过程中驱动顺序的不同，in是以子查询为驱动表，先执行子查询，再执行外层查询，而exists则相反。在执行过程中驱动表的数据小于被驱动表时查询效率是最优的，因此，当子查询的数据小于外层查询时，子查询就应当作驱动表，这时就应该使用in；而当外层查询的数据小于子查询时，外层查询就应当作驱动表，那这时就应使用exists。
SELECT username,department_id FROM user WHERE EXISTS (SELECT id FROM roles WHERE id = user.department_id)
SELECT username,department_id FROM user WHERE department_id IN (SELECT id FROM roles)

11、优化Join语句

（1）用小结果集驱动大结果集，将筛选结果小的表作为驱动表去连接结果集比较大的表，尽量减少join语句中的Nested Loop的循环总次数。
（2）优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能。
（3）在被驱动表的join字段上建立索引，如果被驱动表的join字段无法建立索引的时候，设置足够的Join Buffer Size。
（4）尽量用inner join，会自动选择小表去驱动大表。
（5）当左表数据小于右表时，可以使用left join，因为left join是以左表作为驱动表的，然后再在右表的关联字段建立索引。right join则与其相反。

三、数据结构优化

尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快。
尽量避免设计成可为null的列，可以把为null的字段变为一个空的字符串。
在明确只含有数字的字段，尽量使用满足需求的最小的整数类型。
字符类型查询速度：char>varchar>text，按照实际需求选择合适的字符类型。
日期时间数据最好不要使用字符串类型来存储，可以使用日期时间类型datetime和timestamp，日期时间类型通常比字符串占用的存储空间更小，在进行查找过滤时可以直接利用数据来进行比对，不需要做函数的转换，而且日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算。

四、架构优化

对于数据量非常大时，可以采用分库分表的方式，或者采用数据库集群来处理。

五、软件配置优化

可以对MySQL的参数设置和调整，如缓存大小、连接数、并发处理数等，以适应不同的负载和访问模式。
设置参数的三种方式：
（1）设置当前会话参数：set 参数名 = 参数值；
（2）设置全局会话参数：set global 参数名 = 参数值；
（3）设置应用配置文件，每次启动MySQL实例时加载配置。Window 存放到将my.ini，Linux保存在/etc/my.cnf。

max_connections：MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
back_log：堆栈(缓冲区)中数据库连接保存数量，如果MySQL的链接数量到达了Max_connections时，新来的请求会被存放在堆栈当中去，以等待某一连接释放资源，该堆栈的数量就是back_log。如果等待的数量超过back_log的值，那么数据库就会抛出unauthenticated user ｜ xxx.xxx.xxx.xxx | NULL | login | NULL 的异常信息。可以根据实际情况去改变这个配置值，如果程序处理较快，MySQL服务配置高，可相应的增大该值；相反如果值很大，可能造成连接积压，也是无形中增加数据库的压力，程序响应速度也比较慢。

六、服务器硬件配置优化

增加内存容量
内存容量是影响MySQL性能的重要因素之一。在MySQL中，有一个名为“缓冲池”的内存区域，用于缓存数据和索引。如果缓冲池太小，MySQL将频繁地从磁盘中读取数据，从而导致性能下降。因此，增加内存容量可以提高缓冲池的大小，从而提高MySQL的性能。
使用SSD硬盘
传统的机械硬盘的读写速度较慢，在高并发的情况下容易成为MySQL性能的瓶颈。使用SSD硬盘可以显著提高MySQL的性能。SSD硬盘的读写速度较快，可以减少磁盘I/O操作的时间，从而提高MySQL的响应速度。
增加CPU数量
CPU是MySQL性能的另一个重要因素。如果CPU数量不足，可能会导致MySQL的性能下降。因此，增加CPU数量可以提高MySQL的性能。当然，CPU的性能也很重要，因此选择高性能的CPU也可以提高MySQL的性能。
配置RAID
RAID是一种数据存储技术，可以将多个硬盘组合成一个逻辑卷。RAID可以提高数据的可靠性和读写速度。在MySQL中，使用RAID可以将数据和索引分布在多个硬盘上，从而提高MySQL的性能。