【MySQL】常见的SQL优化方式（一）

目录

1、插入数据

（1）批量插入

（2）手动提交事务

（3）主键顺序插入

2、主键优化

（1）页分裂

（2）页合并

3、order by 优化

（1）排序方式

（2）order by优化

1、插入数据

数据插入优化其实可以通过几个简单的操作来大幅提高效率。

以下是几种常见的优化方法：

（1）批量插入

        如果我们要一次性插入很多数据，而每条数据都用单独的 INSERT 语句，那会很慢。最好使用批量插入，把多条数据写在一条 INSERT 语句里。这样数据库只需要一次性处理多个数据，而不是每次都处理一条，速度会快很多。

INSERT INTO tb_test VALUES (1, 'Tom'), (2, 'Cat'), (3, 'XiaoTao');

（2）手动提交事务

        默认情况下，每次插入一条数据，数据库都会自动提交一个事务。如果我们有很多条数据要插入，手动控制事务可以大幅减少数据库的事务开销。也就是说，先插入一批数据，然后手动提交，而不是每插入一条就提交一次。

START TRANSACTION;

INSERT INTO tb_test VALUES (1, 'Tom'), (2, 'Cat'), (3, 'XiaoTao');
INSERT INTO tb_test VALUES (4, 'Tom'), (5, 'Cat'), (6, 'XiaoTao');
INSERT INTO tb_test VALUES (7, 'Tom'), (8, 'Cat'), (9, 'XiaoTao');

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

（4）大批量插入数据 - 使用 LOAD DATA 

        当需要一次插入非常大量的数据时，INSERT 语句的效率就很低了。这时可以使用 MySQL 提供的 LOAD DATA 指令。它能直接从文件中批量加载数据，速度比普通的 INSERT 快得多。举个例子，插入 100 万条数据，INSERT 可能需要十几分钟，而 LOAD DATA 只需要十几秒。使用 LOAD DATA 指令时也是主键顺序插入性能高于乱序插入

具体操作步骤：

• 首先，用 mysql --local-infile 命令连接数据库，开启从本地加载文件的功能。
• 然后，设置全局参数 local_infile=1，允许加载本地文件。
• 最后，用类似下面的 LOAD DATA 命令导入数据：

LOAD DATA LOCAL INFILE '/root/sql1.log' 
INTO TABLE `tb_user` 
FIELDS TERMINATED BY ', ' 
LINES TERMINATED BY '\n';

        这个SQL命令的意思就是：从 /root/sql1.log 文件中读取数据，按照逗号分隔每个字段，按照换行符分隔每条记录，批量插入到 tb_user 表里。fields terminated by ', ' 的意思是每一个字段之间使用 ', ' 分隔，lines terminated by '\n' 的意思是每一行数据用 '\n' 分隔

2、主键优化

        主键的设计对数据库性能影响非常大，尤其是在InnoDB存储引擎中，表是按照主键顺序存储的，合理的主键设计能有效避免性能问题

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

        InnoDB的逻辑存储结构：最外层是表空间（Tablespace），表空间中存储的是一个一个段（Segment），段当中存放的是一个一个区（Extent），一个区的大小是固定的1M，在区当中存放的是一个一个的页（Page），页当中存放的是一个一个的行（Row），行当中就是存放着具体的字段值。页是InnoDB磁盘管理的最小单元，一个页的大小默认是16K，也就是一个区当中可以包含64个页

（1）页分裂

        页可以为空，也可以填充一半，也可以填充100%。每个页包含了2至N行数据，具体包含多少行数据取决于每行的大小(如果一行数据过大，会行溢出)，每行数据根据主键排列。

主键顺序插入：当我们按照顺序插入数据时，页的填充不会导致分裂。这意味着在插入新数据时，InnoDB 会自动将数据放入适当的页，不会造成额外的结构调整。

主键乱序插入：如果插入的是乱序数据，B+ 树必须在合适的位置插入新数据，这可能导致现有页被填满或溢出，从而触发页分裂。发生页分裂时，InnoDB 会将当前页中的部分数据移动到一个新的页，以保持主键的顺序。下面的过程就是页分裂

（2）页合并

        当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

        当页中删除的记录达到  MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

比如：下面图一第二页中的删除记录达到了50%，然后就页合并变成了图二，再插入id为20的数据时就插入到新的页中

提示：MERGE THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

（3）主键设计原则

根据上面对主键的了解，主键设计原则如下

（一）满足业务需求的情况下，尽量降低主键的长度。因为对于一张表来说，主键（聚集）索引只有一个，但是二级索引可以有多个，在二级索引的叶子节点中存放的就是数据的主键，所以说如果主键比较长，二级索引比较多，那么会占用大量的磁盘空间，而且在搜索时也会耗费大量的磁盘IO，所以要尽量降低主键的长度。

（二）插入数据时，尽量选择顺序插入，选择使用 AUTOINCREMENT 自增主键。因为如果是顺序插入就会使第一个页数据插入满了就插入下一个页，不会发生页分裂的现象。

（三）尽量不要使用UUID做主键或者是其他自然主键，如身份证号。因为UUID生成的主键是无序的，在插入数据时就是乱序插入的，就可能会存在页分裂的现象。还有一点就是主键使用UUID或身份证号，主键的长度就会长，在检索的时候就会耗费大量的磁盘IO。

（四）主键的唯一性决定了它不应该经常被修改。修改主键不仅要调整数据，还要重构相关的索引结构，这会造成较大的性能开销。所以，尽量避免在业务操作中对主键进行修改。这里的修改主键是指重新指定主键字段。

3、order by 优化

（1）排序方式

在MySQL中，排序主要有两种方式：Using filesort 和 Using index。

Using filesort：当排序无法通过索引直接返回结果时，MySQL会先通过索引或全表扫描获取满足条件的数据行，然后在排序缓冲区（sort buffer）中完成排序。这种排序就是Using filesort。

Using index：当数据可以通过有序索引直接返回时，不需要额外的排序操作，这种情况就是Using index，效率更高。

        如果根据某个字段进行排序，并且该字段有相应的索引，MySQL会采用Using index的方式；反之，没有索引时，就会用到Using filesort。创建索引时，索引的默认排序为升序，

        举个例子：如果有一个包含 age 和 phone 字段的联合索引，并且按这两个字段进行升序或降序排序，通常会使用Using index。

# 没有创建索引时，根据age,phone进行排序
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age, phone;

# 创建索引
CREATE INDEX idx_user_age_phone_aa ON tb_user(age, phone);

# 创建索引后，根据age,phone进行升序排序
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age, phone;

# 创建索引后，根据age,phone进行降序排序
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age DESC, phone DESC;

        但是，如果想让 age 按升序排序，phone 按降序排序，那么MySQL会使用Using index加Using filesort。要避免filesort，可以创建一个 age 升序、phone 降序的联合索引。

# 根据age升序、phone降序排序
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age ASC, phone DESC;

# 创建age升序、phone降序的联合索引
CREATE INDEX idx_user_age_phone_ad ON tb_user(age ASC, phone DESC);

# 创建索引后，再次根据age升序、phone降序排序
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age ASC, phone DESC;

        创建age、phone的联合索引，age和phone都为升序的索引结构如下：先根据age进行升序排序，当age相同时再根据phone进行升序排序

        创建age、phone的联合索引，age为升序、phone为降序的索引结构如下：先根据age进行升序排序，当age相同时再根据phone进行降序排序

（2）order by优化

（一）合理建立索引：根据排序字段创建合适的索引。如果是多个字段的排序，遵循最左前缀法则，确保索引能最大程度利用。

（二）尽量使用覆盖索引：避免SELECT *，因为如果查询的字段不在索引里，MySQL需要回表查询，排序依然会使用 filesort 。

（三）注意联合索引的排序规则：如果多字段排序时一个字段升序、另一个降序，需要在创建联合索引时明确 ASC/DESC 顺序。

（四）增大sort buffer size：如果无法避免filesort，并且数据量很大，可以适当增大排序缓冲区（sort buffer size，默认是256k）。否则，当数据超出缓冲区时会进行磁盘排序，影响性能。

推荐：

【数据结构】二叉查找树和平衡二叉树，以及二者的区别_平衡树和二叉搜索树-CSDN博客https://blog.csdn.net/m0_65277261/article/details/136137098?spm=1001.2014.3001.5501【数据结构】前缀树的模拟实现_前缀树实现-CSDN博客https://blog.csdn.net/m0_65277261/article/details/136086068?spm=1001.2014.3001.5501