MySQL SQL语句调优

Love The Way You Lie 2022-11-05 13:50 144阅读 0赞

# SQL语句调优 #

### 文章目录 ###

*  SQL语句调优
 *   *  一、大批量插入数据
     *   *  1. 主键顺序插入
         *  2. 关闭唯一性校验
         *  3. 手动提交事务
     *  二、优化insert语句
     *  三、优化order by语句
     *   *  1. 环境准备
         *  2. 两种排序方式
         *  3. 多字段排序的优化
         *  4. filesort的优化
     *  四、优化group by语句
     *  五、优化嵌套语句
     *  六、优化or条件
     *  七、优化分页查询
     *   *  1. 优化思路一
         *  2. 优化思路二
     *  八、使用SQL提示
     *   *  1. USE INDEX
         *  2. IGNORE INDEX
         *  3. FORCE INDEX

## 一、大批量插入数据 ##

大批量数据存在本地文件中，使用load命令加载

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

### 1. 主键顺序插入 ###

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率

提前创建好的脚本文件介绍 :
    	sql1.log  ----> 主键有序
    	sql2.log  ----> 主键无序

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2gJzaSDB-1615031649954)(图片/4. SQL语句调优/image-20210306181026993.png)\]][img-2gJzaSDB-1615031649954_4. SQL_image-20210306181026993.png]

*  插入主键有序文件
    
    ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-plz9UWaj-1615031649959)(图片/4. SQL语句调优/image-20210306181216903.png)\]][img-plz9UWaj-1615031649959_4. SQL_image-20210306181216903.png]
 *  插入主键无序文件
    
    ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6OH5KqTo-1615031649962)(图片/4. SQL语句调优/image-20210306181243659.png)\]][img-6OH5KqTo-1615031649962_4. SQL_image-20210306181243659.png]

### 2. 关闭唯一性校验 ###

如果在创建表的时候创建了唯一索引，添加数据的时候会检查是否唯一

在导入数据前执行 SET UNIQUE\_CHECKS=0，关闭唯一性校验，可以提高导入的效率，在导入结束后执行SET UNIQUE\_CHECKS=1，恢复唯一性校验

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OlsXeEhZ-1615031649966)(图片/4. SQL语句调优/image-20210306181522803.png)\]][img-OlsXeEhZ-1615031649966_4. SQL_image-20210306181522803.png]

### 3. 手动提交事务 ###

在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，可以提高导入的效率，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yTTaI94e-1615031649968)(图片/4. SQL语句调优/image-20210306181709562.png)\]][img-yTTaI94e-1615031649968_4. SQL_image-20210306181709562.png]

## 二、优化insert语句 ##

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案

*  如果需要同时对一张表插入很多行数据时，应该尽量使用一条insert语句，这种方式可以缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
    
    示例， 原始方式为：
    
        insert into tb_test values(1,'Tom');
        insert into tb_test values(2,'Cat');
        insert into tb_test values(3,'Jerry');
    
    优化后的方案为 ：
    
        insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry'); --只需要连接数据库一次
 *  在事务中进行数据插入
    
        start transaction;
        insert into tb_test values(1,'Tom');
        insert into tb_test values(2,'Cat');
        insert into tb_test values(3,'Jerry');
        commit;
 *  按照主键的顺序插入
    
        insert into tb_test values(4,'Tim');
        insert into tb_test values(1,'Tom');
        insert into tb_test values(3,'Jerry');
        insert into tb_test values(5,'Rose');
        insert into tb_test values(2,'Cat');
    
    优化后
    
        insert into tb_test values(1,'Tom');
        insert into tb_test values(2,'Cat');
        insert into tb_test values(3,'Jerry');
        insert into tb_test values(4,'Tim');
        insert into tb_test values(5,'Rose');

## 三、优化order by语句 ##

### 1. 环境准备 ###

CREATE TABLE `emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) NOT NULL,
      `age` int(3) NOT NULL,
      `salary` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
    
    --插入数据
    
    --创建索引
    create index idx_emp_age_salary on emp(age,salary);

### 2. 两种排序方式 ###

*  filesort 排序（在explain中的extra中可以看到），所有不是通过索引直接返回排序结果的排序都叫 fileSort 排序，效率低
    
    ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SFygG6XS-1615031649970)(图片/4. SQL语句调优/image-20210306182350566.png)\]][img-SFygG6XS-1615031649970_4. SQL_image-20210306182350566.png]
 *  index排序，通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高
    
    ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lQBEnKvA-1615031649973)(图片/4. SQL语句调优/image-20210306182818933.png)\]][img-lQBEnKvA-1615031649973_4. SQL_image-20210306182818933.png]

了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据

### 3. 多字段排序的优化 ###

*  对于多字段的排序，尽量排序方向一致
 *  即使多字段排序方向一致，也要保证排序字段和索引字段顺序位置相对应

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WFw9cM5z-1615031649974)(图片/4. SQL语句调优/image-20210306183058292.png)\]][img-WFw9cM5z-1615031649974_4. SQL_image-20210306183058292.png]

### 4. filesort的优化 ###

对于filesort ， MySQL 有两种排序算法：

*  两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果 sort\_buffer\_size 不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作
 *  一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集，排序时内存开销较大，但是排序效率比两次扫描算法要高

MySQL 通过比较系统变量 max\_length\_for\_sort\_data 的大小和Query语句取出的字段总大小， 来判定使用哪种排序算法，如果max\_length\_for\_sort\_data 更大，那么使用一次扫描算法；否则使用两次扫描算法

可以适当提高 sort\_buffer\_size 和 max\_length\_for\_sort\_data 系统变量，来增大排序区的大小，提高排序的效率

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IPtnrSei-1615031649975)(图片/4. SQL语句调优/image-20210306190031804.png)\]][img-IPtnrSei-1615031649975_4. SQL_image-20210306190031804.png]

## 四、优化group by语句 ##

*  GROUP BY 实际上也会进行排序操作，在排序的基础上，增加了分组操作
 *  可以执行 order by null 禁止排序，仅分组，如下（此时没有使用索引）：
    
    没有禁止的效果：
    
    ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hrrwEk7J-1615031649975)(图片/4. SQL语句调优/image-20210306191341577.png)\]][img-hrrwEk7J-1615031649975_4. SQL_image-20210306191341577.png]
    
    禁止排序后：
    
    ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H1d52GPN-1615031649975)(图片/4. SQL语句调优/image-20210306191747369.png)\]][img-H1d52GPN-1615031649975_4. SQL_image-20210306191747369.png]
    
    从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行 “filesort”， 而上文提过filesort往往非常耗费时间
 *  使用 group by 时，也可以使用索引
    
    ![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eRwxjNCv-1615031649976)(图片/4. SQL语句调优/image-20210306192204404.png)\]][img-eRwxjNCv-1615031649976_4. SQL_image-20210306192204404.png]
    
    此例的 group by 使用索引 + 禁止排序，效率较高

## 五、优化嵌套语句 ##

*  有些情况下，子查询可以被更高效的连接（JOIN）替代
 *  使用多表连接替换子查询

示例：

优化前：

select * from t_user where id in (select user_id from user_role );

优化后：

select * from t_user u , user_role ur where u.id = ur.user_id;

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

## 六、优化or条件 ##

*  对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 
 *  建议使用 union 替换 or

示例：

优化前：

select * from emp where id = 1 or id = 10;

优化后：

select * from emp where id = 1 union select * from emp where id = 10;

## 七、优化分页查询 ##

*  分页操作时会对主键排序
 *  对于 limit 2000000,10  ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大

### 1. 优化思路一 ###

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bPIFLTRq-1615031649977)(图片/4. SQL语句调优/image-20210306194014136.png)\]][img-bPIFLTRq-1615031649977_4. SQL_image-20210306194014136.png]

### 2. 优化思路二 ###

该方案仅适用于主键自增且无断层的表，可以把 limit 查询转换成某个位置的查询

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Hcjnl0nX-1615031649977)(图片/4. SQL语句调优/image-20210306194140968.png)\]][img-Hcjnl0nX-1615031649977_4. SQL_image-20210306194140968.png]

## 八、使用SQL提示 ##

SQL提示就是在SQL语句中加入一些人为的指示来达到优化操作的目的

### 1. USE INDEX ###

在查询语句中表名的后面，添加 use index(索引名) 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FV0YjD65-1615031649978)(图片/4. SQL语句调优/image-20210306194424879.png)\]][img-FV0YjD65-1615031649978_4. SQL_image-20210306194424879.png]

### 2. IGNORE INDEX ###

如果想让MySQL忽略一个或者多个索引，则可以在表名后使用 ignore index(索引名)

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hvhFaf1r-1615031649979)(图片/4. SQL语句调优/image-20210306194556040.png)\]][img-hvhFaf1r-1615031649979_4. SQL_image-20210306194556040.png]

### 3. FORCE INDEX ###

强制MySQL使用一个特定的索引，可在表名后使用 force index(索引名)

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qIxvw1id-1615031649979)(图片/4. SQL语句调优/image-20210306194720602.png)\]][img-qIxvw1id-1615031649979_4. SQL_image-20210306194720602.png]

与 use index 区别：use 仅作为参考，MySQL认为全表扫描更快则不使用此索引，而 force 强制使用此索引

[img-2gJzaSDB-1615031649954_4. SQL_image-20210306181026993.png]: /images/20221023/214d1596f122450b941f65f32efc74cd.png
[img-plz9UWaj-1615031649959_4. SQL_image-20210306181216903.png]: /images/20221023/09817a203d9f47f4a77a355cd39998e6.png
[img-6OH5KqTo-1615031649962_4. SQL_image-20210306181243659.png]: /images/20221023/87640f0aed28424fbdf2d24802d20d9f.png
[img-OlsXeEhZ-1615031649966_4. SQL_image-20210306181522803.png]: /images/20221023/28bcfa4c68314ef2a89a719f138c85a6.png
[img-yTTaI94e-1615031649968_4. SQL_image-20210306181709562.png]: /images/20221023/1a81ac4e6f9b41d6b5bc3b86e177a480.png
[img-SFygG6XS-1615031649970_4. SQL_image-20210306182350566.png]: /images/20221023/72c2011620074cbfada65a8f051ed64c.png
[img-lQBEnKvA-1615031649973_4. SQL_image-20210306182818933.png]: /images/20221023/cbefea4fa6e34eaa99a78f55be672c48.png
[img-WFw9cM5z-1615031649974_4. SQL_image-20210306183058292.png]: /images/20221023/11de4736e60b4c27a65656e8015781cb.png
[img-IPtnrSei-1615031649975_4. SQL_image-20210306190031804.png]: /images/20221023/728407ce188e4fefb98e9f342cb7525d.png
[img-hrrwEk7J-1615031649975_4. SQL_image-20210306191341577.png]: /images/20221023/eb6cfb6fe20843599609b6a0c307c210.png
[img-H1d52GPN-1615031649975_4. SQL_image-20210306191747369.png]: /images/20221023/123adf9d8c24496aba89bea0738b6f5a.png
[img-eRwxjNCv-1615031649976_4. SQL_image-20210306192204404.png]: /images/20221023/1118cf95e2ab4358ae57067b28b8d703.png
[img-bPIFLTRq-1615031649977_4. SQL_image-20210306194014136.png]: /images/20221023/250da7fecd8d4ec5b860bf97e98b4bf0.png
[img-Hcjnl0nX-1615031649977_4. SQL_image-20210306194140968.png]: /images/20221023/36db07fa668a4871a30801d6ac0bfa8b.png
[img-FV0YjD65-1615031649978_4. SQL_image-20210306194424879.png]: /images/20221023/f6a6002e77ff4719bf4e9ccb9be067c6.png
[img-hvhFaf1r-1615031649979_4. SQL_image-20210306194556040.png]: /images/20221023/2e5eb24f04784f599bfcb0a0b961ae7a.png
[img-qIxvw1id-1615031649979_4. SQL_image-20210306194720602.png]: /images/20221023/94c1b47bb25c4137b7ffefc88bbbfc3d.png