【MySQL-26】万字总结＜SQL优化＞——【插入优化 主键优化 order by优化-group by优化-limit优化-count优化-update优化】

前言 大家好吖，欢迎来到 YY 滴MySQL系列 ，热烈欢迎！ 本章主要内容面向接触过C++的老铁 主要内容含：

※全文大致内容总结

一.插入数据优化

关于插入数据优化，主要有以下三个方面

1. 批量插入
2. 手动提交事务
3. 主键顺序插入

1.批量插入

• 采取以下形式，在一个insert语句中完成数据插入，而不是分成几个insert语句

Insert into tb test values(1,'Tom'),(2,'cat'),(3, jerry');

【1】需要大批量插入数据——load指令

• 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MVSQL数据库提供的load指令进行插入。
• 本地磁盘文件中的数据，通过load直接加载到数据库表结构中
• 操作如下:

#客户端连接服务端时，加上参数--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' ;

2.手动提交事务

• 而不是自动模式
• 减少锁竞争：通过主动控制事务，你可以更精确地控制事务的开始和结束，从而减少不必要的锁竞争和锁等待时间。
• 批量处理：对于大量的插入操作，将它们封装在一个事务中可以显著提高性能，因为数据库只需在事务结束时进行一次磁盘写入操作，而不是每次插入都写入。

传送门3.主键顺序插入

• 在大多数数据库系统中，如表数据是使用B树（或其变种如B+树）这样的数据结构进行索引的。
• 顺序插入可以减少 页分裂 （下文主键优化有详解）相应博客传送门

二.主键优化

1.主键设计原则

1. 满足业务需求的情况下， 尽量降低主键的长度。 尽量不要使用UUID做主键或者是其他自然主键，如身份证号

• 对于一个表。聚集索引有一个，但二级索引有很多，二级索引到叶子节点中挂的就是主键。主键比较长，二级索引比较多，会占用许多空间，搜索时耗费更多磁盘io

1. 业务操作时，避免对主键的修改。
2. 插入数据时，尽量选择 顺序插入 ，选择使用AUTOINCREMENT自增主

• 顺序插入可以减少 页分裂 （可以了解下按下面的数据组织方式）

2.数据组织方式

【1】主键顺序插入

• 在大多数数据库系统中，如表数据是使用B树（或其变种如B+树）这样的数据结构进行索引的。

• 主键顺序插入
• 第一个页满了，插入第二个页，一页16k，以此类推

【2】页分裂（主键乱序插入）

下面演示页分裂：

• 此时两页都满了， 我们要插入id为50的数据 ，他会开辟一个新的数据页，但并不是直接插入到第三个数据页当中

• 找到第一个 数据页百分之50的位置 ，大于百分之50的部分移动的新开辟的数据页当中
• 之后插入id为50的数据

• 插入要进入的50数据时，此时就应该改动链表指针

【3】页合并（主键乱序删除）

• 当删除一行记录时，实际上记录并 没有被物理删除 ，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
• 当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后) 看看是否可以将两个页合并以优化空间使用。
• MERGE THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定

演示：

• 有三个页，我们删除中间页的数据，依次删除主键为16，15，14，13的数据，此时达到了MERGE_THRESHOLD，触发页合并

-

• 寻找最靠近的页(前或后)，将两个页合并以优化空间使用

三.order by优化

1. 尽量使用 覆盖索引INDEX .
2. 多字段排序， 一个升序一个降序 ， 此时需要注意联合索引在创建时的规则(ASC/DESC)。
3. 如果不可避免的出现filesort（详情看下方介绍），大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

1.覆盖索引index与filesort

1. Using filesort： 通过表的索引或全表扫描，读取满足条件的数据行， 非直接返回 ，然后在排序缓冲区sort buffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
2. Using index：通过有序索引顺序扫描， 直接返回有序数据 ，这种情况即为 using index， 不需要额外排序，操作效率高

演示：

• 根据年龄升序排序，无索引——>看执行结果为filesort，效率低

2.联合索引在创建时的规则(ASC/DESC)

【1】没有索引时的升序降序——创建索引（有无）指定顺序

• 创建索引没有指定顺序——> 默认升序
• 没有索引时，看执行结果为filesort，效率低

• 创建索引（age 和 phone的联合索引）后，看执行结果为Using Index， 根据age,phone进行升序排序 ， 用到了覆盖索引

• 如果是根据age，phone进行降序升序混合排序 ，执行结果是filesort和index混合

• 此时我们就要 指定顺序 创建索引

• 此时我们就发现，根据age，phone进行降序升序混合排序，执行结果从原来的filesort和index混合，变为只有index

【2】演示代码

#没有创建索引时，根据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进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc , phone desc;

#创建索引
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;

四.group by优化

• 在分组操作时，可以 通过索引 来提高效率
• 分组操作时，索引的使用也是满足最左前缀法则的

演示：

• 没有对profession设置索引，查询时用的是 临时表

在这里插入图片描述

• 设置 联合索引（profession，age，status） 后

• 我们发现根据profession分类，用的是 索引Index

• 我们发现根据age分类，此时 违背了最左前缀法则 ，用到了 临时表

• 同理，只要profession出现，且分组 不跳过中间联合索引，就能用 索引Index

五.limit优化（覆盖索引加子查询形式＆多表联查）

优化思路：

• 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过 覆盖索引加子查询形式 或者 多表联查 进行优化。
• 优化场景：一个常见又非常头疼的问题就是 limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010 的记录，其他记录丢弃，查询排序的代价非常大。

演示：

• 执行下面语句，因为 需要回表查询 ，所以执行耗时长

• 我们拿一个查询案例作为前提2

• 覆盖索引加子查询形式 进行优化
• 可以看到我们MySQL此版本无法用这种方式

• 我们也可以把上面那段查询当作一张表，进行 多表联查

六.count优化

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*)的时候会 直接返回这个数 ，效率很高;
• InnoDB 引擎就麻烦了，它执行 count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后 累积计数
• 优化思路:自己计数

count的几种用法：

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

1. count(主键)

• InnoDB 引擎会遍历整张表，把每一行的 主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加 (主键不可能为null) 。

1. count(字段)

• 字段没有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
• 字段有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

1. count(1)

• InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

1. count(*) InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

七.update优化(避免行锁升级为表锁）

• 我们针对对象进行update更改
• InnoDB的行锁是 针对索引 加的锁，不是 针对记录 加的锁,并且该索引不能失效，否则会从 行锁升级为表锁
• 如果对象带索引，此时加的是行锁， 不会影响其他终端对其他行的操作权限
• 如果对象没带索引，此时加的是表锁， 会影响其他终端对其他行的操作权限

update sludent set no='2000100100' where id =l;
update student setno='2000100105' where namne='韦一笑';