SQL优化总结-CSDN博客

本文链接：https://blog.csdn.net/2301_81405396/article/details/147993304

前言

总结了一些常见的SQL优化并通过逻辑分析和借助MySQL中的explain来分析优化的效果。

SQL优化主要是借助索引来达到快速读取的目的，建议先学习了索引再来食用。

插入优化

首先我们知道在一个正常的数据库系统中插入的操作频次是远低于查询的。

并且插入操作并不涉及条件查询，自然而然地与索引地关系也就不大了。

因此对于插入的优化主要是一些操作上的优化——使用合适的策略来避免产生过多重复操作。

`insert`优化

主要策略有：

批量插入

当我们需要插入多条数据时，这些数据可能是分开的，每次使用insert都要涉及到数据库连接等操作，这非常浪费时间，所以我们在使用insert时尽可能的集中使用insert，避免重复的连接操作浪费时间。
手动提交事务

和上方同理，每条insert语句单独执行时，每次都需要进行开启事务，提交事务等操作，非常浪费时间，所以在批量插入时手动开启事务，手动提交事务。
主键顺序插入

主键顺序插入的效率会高于乱序插入，这个在后面的主键优化中会讲到。

大批量插入优化

当要插入的数据过多时，使用insert优化后可能依然不满足我们的要求，此时我们就可以使用MySQL数据库提供的load指令进行插入。

load从文件中进行插入，与insert不同。

具体操作为：

在连接时加上 --loacl-infile指明从文件中进行插入。
```
mysql --local-infile -u root -p
```
设置参数local_infile = 1，开启从本地文件导入数据的开关
```
set session local_infile = 1;
```

执行load命令，将指定的文件数据加载到数据库中，指定列分隔符和行分隔符。

load data local infile '/root/sql1.log' into table `table_name` fields terminated by ',' lines terminated by '\n';

主键优化

主键的重要性不言而喻，不仅涉及到聚集索引的效率，并且还影响到诸多的二级索引。

首先来解释一下为什么前面所说的为什么主键顺序插入的效率要比乱序插入高。

数据组织方式

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

就像我们平常使用的xlsx表一样，按照主键的顺序从小到大 | 从大到小存放。

如上图，聚集索引呈B+树结构。每一个结点在InnoDB引擎中都是一页。

而我们知道B+树之所以能够维持所有的叶子节点都在同一层，主要归功于其优秀的分裂操作和合并操作。而这种操作在InnoDB引擎中对应了两种概念——页分裂和页合并。

页分裂

InnoDB引擎中，页可以为空，也可以填充一半，也可以填充100%。每个页包含了2 - N行数据。

但是如果页中的节点过多，就会涉及到页分裂。

主键顺序插入对页分裂的影响：

如果末尾的页没有满，数据会直接插入到页尾。不涉及分裂操作。

如果末尾的页已经满了，对于叶子节点来说会直接加入新的页，叶子节点不涉及分裂操作。但要注意这里仅仅是叶子节点不分裂，根节点还是有可能会发生分裂的，但是数据库系统中的B+树一般非常密集，叶子节点的数量要远远大于根节点，所以对于根节点的分裂这里不考虑。

主键乱序插入对页分裂的影响：

如果要插入的页没有满，不会发生页分裂操作，但是可能会涉及到页内顺序结构的插入操作，众所周知顺序结构的插入是非常耗时的。

如果插入的页满了，根据B+树的特性，当前数据会找到要插入的页，随后会向上逐层递归进行页分裂操作，显然这种操作是非常耗时的。

综上所述，主键顺序插入的效率要大于乱序插入。

页合并

页合并并不涉及sql优化，所以只是简单地提一嘴。

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

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

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定

主键设计原则

接下来就是主键地设计原则了，主要从顺序插入的影响以及InnoDB中索引本身的性质来考虑。

插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键。
在业务满足地情况下，尽量降低主键的长度。

主要从两方面来分析：

一方面在聚簇索引中，主键的长度直接影响的是查找效率。越长的主键在比较时消耗的时间就越长。

另一方面在二级索引中，主键的长度直接影响的是叶子节点能够存储数据的个数。

众所周知，在InnoDB引擎中二级索引叶子节点存储的是聚簇索引id（这里是主键），当主键过大时，叶子节点中每条数据的长度相应的也会增大。

而在InnoDB引擎中的每个叶子节点的大小都是固定的，每条数据的长度增大相应的页数目就会增多，一方面在存储上会占用更多的空间；另一方面在范围查询时因为页数目增多，查询时的io次数也会增多。

所以在业务满足的情况下，应该尽量降低主键的长度。

这里为什么不提聚簇索引中叶子节点呢？因为聚簇索引中存储的是行数据，而在行数据中主键的占比一般都比较低，其带来的影响相对较低。
尽量不要使用UUID做主键或者是其他自然主键，比如身份证号。

主要是从上面讲的两方面来考虑，首先UUID的占用很大，在效率上不如auto_increment。而身份证号不仅长度很大，并且还是乱序的，在插入时难免会乱序插入，而前面我们讲了顺序插入的效率要高于乱序插入，所以尽量避免使用这类标识做主键。
业务操作时，避免对主键的修改。

主键作为聚簇索引的关键和众多二级索引的媒介，修改主键时不仅要对聚簇索引进行修改，还要对众多的二级索引进行修改，这显然效率是非常低的。

而主键在大多数情况下只是作为唯一标识使用，并没有什么实际的意义，对于主键的修改意义显然不大。

特殊的当主键存在特殊含义时，如身份证号，本身就不允许修改，所以于情于理主键都不应该修改。

`order by`优化

在InnoDB中有两种排序方式：

Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FillSort, 主要用于没有专门设置索引的字段。对应all
Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为Using index，不需要额外排序，操作效率高。对应index

我们粗略的分析一下显然Using index的效率是要高于Using filesort的，因为Using index使用的是在B+树中以及排序好的数据。

在讲解Order by之前先加入一些数据

create table user_info
(
    id         int auto_increment
        primary key,
    name       varchar(255) null,
    phone      varchar(20)  null,
    email      varchar(255) null,
    profession varchar(255) null,
    age        int          null,
    gender     int          null,
    status     int          null,
    createTime datetime     null
);

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

INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (1, '吕布', '17799990000', 'lvbu@163.com', '软件工程', 23, 1, 6, '2000-02-02 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (2, '曹操', '17799990001', 'caocao@qq.com', '通讯工程', 33, 1, 0, '2004-05-05 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (3, '赵云', '17799990002', 'zhaoyun@163.com', '工程造价', 31, 1, 2, '2008-03-03 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (4, '貂蝉', '17799990003', 'diaochan@163.com', '舞蹈', 22, 0, 3, '2006-04-04 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (5, '花木兰', '17799990004', 'huamulan@163.com', '软件工程', 25, 0, 6, '2002-02-07 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (6, '大乔', '17799990005', 'daqiao@163.com', '舞蹈', 27, 0, 1, '2003-03-03 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (7, '露娜', '17799990006', 'luna@163.com', '应用数学', 24, 0, 1, '2002-02-08 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (8, '程咬金', '17799990007', 'chengyaojin@163.com', '厨师', 32, 1, 2, '2005-05-05 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (9, '李信', '17799990008', 'lixin@163.com', '舞蹈', 21, 0, 3, '2004-04-04 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (10, '白起', '17799990009', 'baiqi@163.com', '机械工程及自动化', 27, 1, 1, '2006-06-06 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (11, '韩信', '17799990010', 'hanxin@163.com', '机械非金属材料', 21, 1, 2, '2004-04-12 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (12, '荆轲', '17799990011', 'jingke@163.com', '会计', 29, 1, 1, '2008-08-08 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (13, '兰陵王', '17799990012', 'lanlingwang@163.com', '工程造价', 44, 1, 1, '2004-04-09 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (14, '孙悟空', '17799990013', 'sunwukong@163.com', '软件工程', 30, 1, 0, '2007-07-07 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (15, '甄姬', '17799990014', 'zhenji@163.com', '软件工程', 31, 0, 0, '2001-03-03 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (16, '妲己', '17799990015', 'daji@163.com', '软件工程', 32, 0, 3, '2001-03-30 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (17, '芈月', '17799990016', 'miyue@163.com', '工业经济', 35, 0, 2, '2008-08-08 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (18, '嬴政', '17799990017', 'yingzheng@163.com', '舞蹈', 38, 1, 3, '2006-06-06 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (19, '安琪拉', '17799990018', 'anqila@163.com', '舞蹈', 20, 0, 2, '2005-05-05 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (20, '周瑜', '17799990019', 'zhouyu@163.com', '城市规划', 51, 1, 2, '2004-04-12 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (21, '典韦', '17799990020', 'dianwei@163.com', '城市规划', 52, 1, 2, '2006-04-12 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (22, '高渐离', '17799990021', 'gaojianli@163.com', '土木工程', 19, 1, 0, '2007-07-18 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (23, '后羿', '17799990022', 'houyi@163.com', '市场营销', 20, 1, 1, '2008-08-10 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (24, '公孙离', '11111111111', 'gongsunli@163.com', '软件工程', 23, 1, 2, '2000-02-02 00:00:00');
INSERT INTO baidu.user_info (id, name, phone, email, profession, age, gender, status, createTime) VALUES (25, '李白', '11111111111', 'libai@163.com', '软件工程', 30, 1, 2, '2000-01-01 00:00:00');

在这里可以悄悄试一试insert优化了OvO。

紧接着我们对没有索引的name字段进行Order by操作

explain select id, name from user_info order by name;

得到以下结果

我们主要观察type和Extra栏的数据，可以发现没有使用任何索引，排序方式为Using filesort这显然很慢了。

接下来我们为name字段建立索引来分析效率。

create index idx_user_name on user_info(name);

再次进行order by。

可以发现type变为了type而Extra变为了Using index，增加了效率。

接下来我们来测试联合索引对效率的影响，在我们的表中是存在一个联合索引的。

运行下面的语句

explain select id from user_info order by profession, age, status;

可以发现MySQL使用了联合索引进行排序。

接下来我们要验证在这里最左前缀法则是否成立

explain select id from user_info order by age, status;

可以发现使用了索引但是没有完全使用索引，包括下面的情况

explain select id from user_info order by profession, status, age;

同样的说明在order by操作时仍然要遵循最左前缀法则，想要优化这种情况只能再建立一个符合顺序的联合索引。

综上所述，order by的优化主要有以下几点：

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

尽量使用覆盖索引（避免回表还降低占用，何乐而不为？）

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

如果不可避免地出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认为256K)

-- 查看默认的排序缓冲区的大小
select @@global.sort_buffer_size;
-- 如果缓冲区占满了，mysql会选择直接在磁盘中进行排序，显然在缓存区中进行排序速度是大于在磁盘中进行排序的。

`group by` 优化

group by优化和order by优化策略类似，主要是分析索引对于order by的效果。分析策略与上方相同，不进行赘述，但这里贴上分析的代码

-- 只留下主键
# drop index idx_user_age_phone on user_info;
# drop index idx_user_age_phone_ad on user_info;
# drop index idx_user_info_pro_age_status on user_info;

-- 验证索引的作用
explain select profession, count(*) from user_info group by profession;
explain select age, count(*) from user_info group by age;
explain select profession, age, count(*) from user_info group by profession, age;
explain select profession, age, count(*) from user_info where profession = '软件工程' group by age;
-- 创建联合索引
create index idx_user_pro_age_sta on user_info(profession, age, status);

分别在创建联合索引和不创建索引的时候查看explain，分析其效率。

`limit`优化

实际上，MySQL是很难确定每一条数据到底是第几条数据的，其策略只能是在表中逐行查找，直至找到目标的起点。

所以在大批量数据的分页操作时，分页操作其实是很慢很慢的。

一个常见又头疼的问题就是limit 2000000, 10，此时需要MySQL排序前2000010记录，仅仅返回2000010 - 2000010的记录，其他记录丢弃，查询的代价非常大。

但是我们可以发现相较于起点，每页的数据量一般是很小的，所以我们尝试能不能转变思路，直接查询到目标的数据。

主要策略为通过主键分页和子查询的形式，尽可能地优化

select tb_sku.* from tb_sku, (select id from tb_sku order by id limit 9000000, 10) s1 where tb_sku.id in s1;

但是实际上就算这样操作也是非常耗时地，所以在实际操作时应该避免一张表过大。

`count`优化

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

InnoDB引擎在执行count(*)的时候，需要把数据一行一行地从引擎中读出来，然后累积计数。

优化思路：自己计数。设置一个变量同步insert和delete操作，实时的记录数据。

所以在InnoDB中我们对于count其实是很无奈的。同样的在大批量数据操作时应该避免count

但是对于小数据时使用count我们仍然可以做出优化。

`count`的几种方式

count()是一个聚合函数，对于返回的结果集，一行行的判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。

用法：count(*), count(主键), count(字段), count(1)

count(主键) InnoDB引擎会遍历整张表，把每一行的主键id取出来，返回给服务层，服务层拿到主键后，直接按行进行累加(主键不可能为null)
count(字段)如果没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null才计数，如果有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count(1) InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字1进去，直接按行进行累加。
count(*) InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

通过主观分析，最终可得：count(字段) < count(主键id) < count(1) ~~ count(*)，所以尽量使用count(*)。

`update`优化

update会涉及查找，但是查找部分以及讲解过了，这里不过多赘述。

首先引入新的数据

-- auto-generated definition
create table course
(
    id   int auto_increment comment '主键ID'
        primary key,
    name varchar(10) null comment '课程名称'
)
    comment '课程表';

create index idx_course_name
    on course (name);

INSERT INTO baidu.course (id, name) VALUES (1, 'JavaSE');
INSERT INTO baidu.course (id, name) VALUES (4, 'Kafka2');
INSERT INTO baidu.course (id, name) VALUES (3, 'MySQL');
INSERT INTO baidu.course (id, name) VALUES (2, 'Spring');

对于update的优化主要是应用在多事务并发的时候。

在InnoDB引擎中，update操作默认触发的是行级锁。这也就在保证了数据完整性的同时有不会影响并发性能。