【MySQL】七种SQL优化方式你知道几条

我就是我 2024-03-09 01:59 101阅读 0赞

## 1.插入数据 ##

### 1.1insert ###

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化。

insert into tb_test values(1,'tom');
    insert into tb_test values(2,'cat');
    insert into tb_test values(3,'jerry');

1).  优化方案一

批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

2).  优化方案二

手动控制事务

start transaction;
    insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
    insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
    insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
    commit;

3).  优化方案三

主键顺序插入，性能要高于乱序插入。

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
    主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

## 1.2大批量插入数据 ##

如果一次性需要插入大批量数据 ( 比如 :  几百万的记录 ) ，使用 insert 语句插入性能较低，此时可以使

用 MySQL 数据库提供的 load 指令进行插入。操作如下：

![bd0db78117474251846da27f507068ce.png][]

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile
    mysql –-local-infile -u root -p
    -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
    set global local_infile = 1;
    -- 执行load指令将准备好的数据，加载到表结构中
    load data local infile '/root/sql1.log' into table tb_user fields
    terminated by ',' lines terminated by '\n' ;

主键顺序插入性能高于乱序插入

示例演示 :

A.  创建表结构

CREATE TABLE `tb_user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `birthday` DATE DEFAULT NULL,
    `sex` CHAR(1) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_user_username` (`username`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

B.  设置参数

-- 客户端连接服务端时，加上参数 -–local-infile
    mysql –-local-infile -u root -p
    -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
    set global local_infile = 1;

C. load 加载数据

load data local infile '/root/load_user_100w_sort.sql' into table tb_user
    fields terminated by ',' lines terminated by '\n' ;

![c7f1ebc234ee445db4ba14706a9cc8e8.png][]

我们看到，插入 100w 的记录， 17s 就完成了，性能很好。

> 在 load 时，主键顺序插入性能高于乱序插入

## 2.主键优化 ##

在上一小节，我们提到，主键顺序插入的性能是要高于乱序插入的。 这一小节，就来介绍一下具

体的原因，然后再分析一下主键又该如何设计。

1).  数据组织方式

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

(index organized table IOT) 。

![b9647aa839c64a7e9a6f21e2468c1405.png][]

行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过 InnoDB 的逻辑结构图：

![4d9a35a447774487b8d8eb9074f63a30.png][]

在 InnoDB 引擎中，数据行是记录在逻辑结构  page  页中的，而每一个页的大小是固定的，默认

16K 。

那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不小，将会存

储到下一个页中，页与页之间会通过指针连接。

2).  页分裂

页可以为空，也可以填充一半，也可以填充 100% 。每个页包含了 2-N 行数据 ( 如果一行数据过大，

会行溢出) ，根据主键排列。

A.  主键顺序插入效果

① .  从磁盘中申请页， 主键顺序插入

![25dd4059fd05493a92c9e3315c83af39.png][]

② .  第一个页没有满，继续往第一页插入

![b6631e212b104a5598f535ffa97ff2a0.png][]

③ .  当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

![9807994f7a13452a94f57638a19d0847.png][]

④ .  当第二页写满了，再往第三页写入

![fe338a0e7fcb4f629a357dcfe8f34928.png][]

B.  主键乱序插入效果

① .  加入 1\#,2\# 页都已经写满了，存放了如图所示的数据

![8ad992b01e544f369c96a221c59af133.png][]

② .  此时再插入 id 为 50 的记录，我们来看看会发生什么现象

会再次开启一个页，写入新的页中吗？

![530e110dbf92435e81acbd21fa92a1dd.png][]

不会。因为，索引结构的叶子节点是有顺序的。按照顺序，应该存储在 47 之后。

![b8c46d1c2c7b49588642854e8519662a.png][]

但是 47 所在的 1\# 页，已经写满了，存储不了 50 对应的数据了。 那么此时会开辟一个新的页  3\# 。

![a0f016eea1c947d0ab10ec972e605281.png][]

但是并不会直接将 50 存入 3\# 页，而是会将 1\# 页后一半的数据，移动到 3\# 页，然后在 3\# 页，插入

50 。

![da4b2845e16e4684b5f35285756842cf.png][]

移动数据，并插入 id 为 50 的数据之后，那么此时，这三个页之间的数据顺序是有问题的。  1\# 的下

一个页，应该是3\# ，  3\# 的下一个页是 2\# 。 所以，此时，需要重新设置链表指针。

![11f2dbaa82364455bd95ae58c945b22c.png][]

上述的这种现象，称之为  " 页分裂 " ，是比较耗费性能的操作。

3).  页合并

目前表中已有数据的索引结构 ( 叶子节点 ) 如下：

![7675ac3dabc74e86b29bcf50065ae829.png][]

当我们对已有数据进行删除时，具体的效果如下 :

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（ flaged ）为删除并且它的空

间变得允许被其他记录声明使用。

![462cbd20bfc347d3932031b557d21720.png][]

当我们继续删除2\#的数据记录

![a23081a2e4524a98b751ad21ed078f0d.png][]

当页中删除的记录达到  MERGE\_THRESHOLD （默认为页的 50% ）， InnoDB 会开始寻找最靠近的

页（前或后）看看是否可以将两个页合并以优化空间使用。

![0a045a25b5e04b41a914eff95ea57da5.png][]

删除数据，并将页合并之后，再次插入新的数据21，则直接插入3\#页

这个里面所发生的合并页的这个现象，就称之为 "页合并"。

> 知识小贴士： 
> 
> MERGE\_THRESHOLD ：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

4).  索引设计原则

满足业务需求的情况下，尽量降低主键的长度。

插入数据时，尽量选择顺序插入，选择使用 AUTO\_INCREMENT 自增主键。 尽量不要使用 UUID 做

主键或者是其他自然主键，如身份证号。

业务操作时，避免对主键的修改。

![483dbcafbea4424e9cd2e100886f0e5e.png][]

## 3.order by优化 ##

MySQL 的排序，有两种方式：

Using filesort :  通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort

buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫  FileSort  排序。

Using index :  通过有序索引顺序扫描直接返回有序数据，这种情况即为  using index ，不需要

额外排序，操作效率高。

对于以上的两种排序方式， Using index 的性能高，而 Using filesort 的性能低，我们在优化排序

操作时，尽量要优化为  Using index 。

接下来，我们来做一个测试：

A.  数据准备

把之前测试时，为 tb\_user 表所建立的部分索引直接删除掉

drop index idx_user_phone on tb_user;
    drop index idx_user_phone_name on tb_user;
    drop index idx_user_name on tb_user;

![38121091073f4940972140ecca8a53ef.png][] B. 执行排序SQL

explain select id,age,phone from tb_user order by age ;

![9b3341cd447349db953a8fb28d25f78b.png][]

explain select id,age,phone from tb_user order by age, phone ;

![c45b4ef2cf2a4dec9e8bbf3cbb78e2bf.png][]

由于  age, phone  都没有索引，所以此时再排序时，出现 Using filesort ， 排序性能较低。

C.  创建索引

-- 创建索引
    create index idx_user_age_phone_aa on tb_user(age,phone);

D.  创建索引后，根据 age, phone 进行升序排序

explain select id,age,phone from tb_user order by age; 1

![6eb6abcdf0f04a50857bd3ffc49ef168.png][]

explain select id,age,phone from tb_user order by age , phone; 1

![40c6d0556b7e40b49e41d93590386714.png][]

建立索引之后，再次进行排序查询，就由原来的 Using filesort ， 变为了  Using index ，性能

就是比较高的了。

E.  创建索引后，根据 age, phone 进行降序排序

explain select id,age,phone from tb_user order by age desc , phone desc ;

![8124ec9df65d4ae69a76a1c4e3a2faa0.png][]

也出现  Using index ， 但是此时 Extra 中出现了  Backward index scan ，这个代表反向扫描索

引，因为在 MySQL 中我们创建的索引，默认索引的叶子节点是从小到大排序的，而此时我们查询

排序时，是从大到小，所以，在扫描时，就是反向扫描，就会出现 Backward index scan 。 在

MySQL8 版本中，支持降序索引，我们也可以创建降序索引。

F.  根据 phone ， age 进行升序排序， phone 在前， age 在后。

explain select id,age,phone from tb_user order by phone , age;

排序时 , 也需要满足最左前缀法则 , 否则也会出现  filesort 。因为在创建索引的时候，  age 是第一个

字段， phone 是第二个字段，所以排序时，也就该按照这个顺序来，否则就会出现  Using filesort。

F.  根据 age, phone 进行降序一个升序，一个降序

explain select id,age,phone from tb_user order by age asc , phone desc ;

![d006393802e2427a84d3c728e2e76647.png][]

因为创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，

此时就会出现Using filesort 。

![dac20e9053dd4c0eaa0dca029f48d1ce.png][]

为了解决上述的问题，我们可以创建一个索引，这个联合索引中  age  升序排序， phone  倒序排

序。

G.  创建联合索引 (age  升序排序， phone  倒序排序 )

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

![545d452ef5f14339a6aa83f7a716a6be.png][]

H. 然后再次执行如下SQL

explain select id,age,phone from tb_user order by age asc , phone desc ;

升序/降序联合索引结构图示:

![57773a406db84906a9993a1fc68c2e0e.png][]

由上述的测试 , 我们得出 order by 优化原则 :

A.  根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

B.  尽量使用覆盖索引。

C.  多字段排序 ,  一个升序一个降序，此时需要注意联合索引在创建时的规则（ ASC/DESC ）。

D.  如果不可避免的出现 filesort ，大数据量排序时，可以适当增大排序缓冲区大小

sort\_buffer\_size( 默认 256k) 。

## 4.group by优化 ##

分组操作，我们主要来看看索引对于分组操作的影响。

首先我们先将  tb\_user  表的索引全部删除掉 。

drop index idx_user_pro_age_sta on tb_user;
    drop index idx_email_5 on tb_user;
    drop index idx_user_age_phone_aa on tb_user;
    drop index idx_user_age_phone_ad on tb_user;

![1a00826d16b04168877f30f85952afbd.png][]

接下来，在没有索引的情况下，执行如下 SQL ，查询执行计划：

explain select profession , count(*) from tb_user group by profession ;

![fa79c0b10c67479685f6dfcecf3027a3.png][]

然后，我们在针对于  profession  ，  age ，  status  创建一个联合索引。

create index idx_user_pro_age_sta on tb_user(profession , age , status);

紧接着，再执行前面相同的 SQL 查看执行计划。

explain select profession , count(*) from tb_user group by profession ;

![f2a3c797ab474234a5b68ea8ba1cae8e.png][]

再执行如下的分组查询 SQL ，查看执行计划：

![5e3f9f0471364137a05ee8b52376f27b.png][]

我们发现，如果仅仅根据 age 分组，就会出现  Using temporary  ；而如果是 根据

profession,age 两个字段同时分组，则不会出现  Using temporary 。原因是因为对于分组操作，

在联合索引中，也是符合最左前缀法则的。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

A.  在分组操作时，可以通过索引来提高效率。

B.  分组操作时，索引的使用也是满足最左前缀法则的。

## 5.limit优化 ##

在数据量比较大时，如果进行 limit 分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit 分页查询耗时对比：

![b197d499a96141c58732fb49120cc292.png][]

通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。

因为，当在进行分页查询时，如果执行  limit 2000000,10  ，此时需要 MySQL 排序前 2000010  记

录，仅仅返回  2000000 - 2000010  的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路 :  一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查

询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id
    limit 2000000,10) a where t.id = a.id;

## 6.count优化 ##

### 6.1概述 ###

select count(*) from tb_user ;

在之前的测试中，我们发现，如果数据量很大，在执行 count 操作时，是非常耗时的。

MyISAM  引擎把一个表的总行数存在了磁盘上，因此执行  count(\*)  的时候会直接返回这个

数，效率很高； 但是如果是带条件的 count ， MyISAM 也慢。

InnoDB  引擎就麻烦了，它执行  count(\*)  的时候，需要把数据一行一行地从引擎里面读出

来，然后累积计数。

如果说要大幅度提升 InnoDB 表的 count 效率，主要的优化思路：自己计数 ( 可以借助于 redis 这样的

数据库进行, 但是如果是带条件的 count 又比较麻烦了 ) 。

### 6.2count用法 ###

count()  是一个聚合函数，对于返回的结果集，一行行地判断，如果  count  函数的参数不是

NULL ，累计值就加  1 ，否则不加，最后返回累计值。

用法： count （ \* ）、 count （主键）、 count （字段）、 count （数字）

![2764627cc8ab453b84ca39cf337a5c3c.png][]

> 按照效率排序的话， count( 字段 ) < count( 主键  id) < count(1) ≈ count(\*) ，所以尽 
> 
> 量使用  count(\*) 。

## 7.update优化 ##

我们主要需要注意一下 update 语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ; 1

当我们在执行删除的 SQL 语句时，会锁定 id 为 1 这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下 SQL 时。

update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务，在执行上述的 SQL 时，我们发现行锁升级为了表锁。 导致该 update 语句的

性能大大降低

> InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁  , 并且该索引不能失效，否则会从行锁升级为表锁 。

[bd0db78117474251846da27f507068ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/e8f31677885b415fa1bfab9a79643475.png
[c7f1ebc234ee445db4ba14706a9cc8e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/c5ebfc797c834056894af9aff83a54ea.png
[b9647aa839c64a7e9a6f21e2468c1405.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/db51c3340f4c4828b3ce5eed3032fe73.png
[4d9a35a447774487b8d8eb9074f63a30.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/8748135b2e7b47b0b81e036e4558b52b.png
[25dd4059fd05493a92c9e3315c83af39.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/60e5259a82dd4f16a3734f4bedb91df1.png
[b6631e212b104a5598f535ffa97ff2a0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/8fadca0b43ae40e2a1d7d873820fd729.png
[9807994f7a13452a94f57638a19d0847.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/92909acf87e04e7ca3dbe3f320a9ce11.png
[fe338a0e7fcb4f629a357dcfe8f34928.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/aa11fb8733984573ab09193165a6886e.png
[8ad992b01e544f369c96a221c59af133.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/ae88f286dd574b9fa9cfb688d531af3d.png
[530e110dbf92435e81acbd21fa92a1dd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/b0c69747f93d443483b30c2f2c2db202.png
[b8c46d1c2c7b49588642854e8519662a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/ebb9068f12604b31a59bbc39c4ac1aea.png
[a0f016eea1c947d0ab10ec972e605281.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/32101047e67e4d7c92304aa69518f6f7.png
[da4b2845e16e4684b5f35285756842cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/4a30bb8bcc614d7ba8ef60050fbb969d.png
[11f2dbaa82364455bd95ae58c945b22c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/4f9ed8e06fd84a10942351c08fbf5e32.png
[7675ac3dabc74e86b29bcf50065ae829.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/ece928da4b5c47f7a22f7f707f69d0c3.png
[462cbd20bfc347d3932031b557d21720.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/7241add67b164e6a991ae69e8c204816.png
[a23081a2e4524a98b751ad21ed078f0d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/dbfaf55804d54ff48b1f2975894979a2.png
[0a045a25b5e04b41a914eff95ea57da5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/e041e0ca5a744d93ba79dd96a47be89b.png
[483dbcafbea4424e9cd2e100886f0e5e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/5718c5d78d9c4352a9932fe6ad049aaf.png
[38121091073f4940972140ecca8a53ef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/3baed7ce3f9249d2ab7bfd75d198748b.png
[9b3341cd447349db953a8fb28d25f78b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/142f0b6ac42b40f290ba1d1825cbdedd.png
[c45b4ef2cf2a4dec9e8bbf3cbb78e2bf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/cd5047d7f07f4142a1d2c24263e7f732.png
[6eb6abcdf0f04a50857bd3ffc49ef168.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/524f353f63a84b968180bb94d3a246ee.png
[40c6d0556b7e40b49e41d93590386714.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/5a4c56b42b524b6ca8ef5cc3dc6477a1.png
[8124ec9df65d4ae69a76a1c4e3a2faa0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/02c3203f3398490a9e1a509e06363a0a.png
[d006393802e2427a84d3c728e2e76647.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/6f047afa32dc443fbaca26ea826a7d77.png
[dac20e9053dd4c0eaa0dca029f48d1ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/08a36f66e0e14c56a72b750250e6bb66.png
[545d452ef5f14339a6aa83f7a716a6be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/5f1b22ce21fa4796878e2e252dd18501.png
[57773a406db84906a9993a1fc68c2e0e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/1eba6c35a61a4e64ba5c2a33bea81374.png
[1a00826d16b04168877f30f85952afbd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/bf08ea29189a441290a45b7143f99168.png
[fa79c0b10c67479685f6dfcecf3027a3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/7b67321e48d4418584b362032b0a43e8.png
[f2a3c797ab474234a5b68ea8ba1cae8e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/7ab2d7a3d30d469fb952d242d5ea927d.png
[5e3f9f0471364137a05ee8b52376f27b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/b449c81f071b4f3db3f7b51337f8b022.png
[b197d499a96141c58732fb49120cc292.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/064761ac9db24785bbd39923da8db096.png
[2764627cc8ab453b84ca39cf337a5c3c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/09/b7c4f0a3f77243f3a2075b2342ad3ad0.png