携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第21天，点击查看活动详情
⭐️前面的话⭐️
✉️坚持和努力一定能换来诗与远方！
💭推荐书籍：📚《王道408》，📚《深入理解 Java 虚拟机-周志明》，📚《Java 核心技术卷》
💬算法刷题：✅力扣🌐牛客网
🎈Github
🎈码云Gitee
9. SQL优化（对索引进行优化）
9.1 插入数据
- 批量插入
- 手动提交事务
- 主键顺序插入
- 大批量插入数据
使用load指令将100w数据加载进表结构当中，16s。使用insert的话，需要十多分钟。
主键顺序插入性能高于乱序插入。
9.2 主键优化（主键的设计原则）
所有的数据都在叶子结点，非叶子结点仅仅起到索引数据的作用，
非叶子结点的索引和叶子节点的数据最终都是存放在一个逻辑结构——页page当中的。
页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
主键乱序插入可能出现页分裂的情况。
页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页
合并以优化空间使用。
知识小贴士：
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
主键设计原则
➢ 满足业务需求的情况下，尽量降低主键的长度。（主键索引只有一个，二级索引可以有很多）
➢ 插入数据时，尽量选择顺序插入（否则可能会出现页分裂），选择使用AUTO_INCREMENT自增主键。
➢ 尽量不要使用UUID做主键（无序的，可能会乱序插入。而且长度也较长。）或者是其他自然主键，如身份证号。
➢ 业务操作时，避免对主键的修改。（修改主键还会去动对应的索引结构，代价是比较大的）
9.3 order by 优化
① Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer中完成排序操作，所有不是通过
索引直接返回排序结果的排序都叫 Filesort排序。
② Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为Using index ，不需要额外排序，操作效率高。（相比上
面，性能更高。）（优化order by语句的时候尽量优化为这种。）
-- 没有创建索引时，根据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进行升序排序(优化为Using index)
explain select id, age, phone from tb_user order by age, phone;
-- 创建索引后，根据age,phone进行降序排序(优化为Using index)(反向扫描索引)
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;(Using filesort, Using index)
-- 创建索引
create index idx_user_age_phone_ad on tb_user(age acs, phone desc);
-- 再次测试，发现已经优化为Using index
➢ 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
➢ 尽量使用覆盖索引。
➢ 多字段排序 一个升序一个降序，此时需要注意联合索引在创建时的规则（asc / desc ）。
➢ 如果不可避免的出现 ，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size （默认256k ）
9.4 group by （分组）优化
-- 删除目前的联合索引
drop index idx_user_pro_age_sta on tb_user;
-- 执行分组操作，根据profession字段分组
explain select profession, count(*) from tb_user group by profession;(用到了临时表，性能较低)
-- 创建联合索引
create index idx_user_pro_age_sta on tb_user(profession, age, status);
-- 执行分组操作，根据profession字段分组
explain select profession, count(*) from tb_user group by profession;（优化为了Using index）
-- 执行分组操作，根据profession字段分组
explain select profession, count(*) from tb_user group by profession, age;
➢ 在分组操作时，可以通过索引来提高效率。
➢ 分组操作时，索引的使用也是满足最左前缀法则的。
9.5 limit（分页查询）优化
一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010
的记录，其他记录丢弃，查询排序的代价非常大 。
-- 第1页（速度很快）
select * from tb_sku limit 0,10;
-- 第2页（速度很快）
select * from tb_sku limit 10,10;
-- 1000000的前10条（慢）
select * from tb_sku limit 1000000,10;
-- 官方：覆盖索引+子查询实现
select s.* from tb_sku s, (select id from tb_sku order by id limit 9000000, 10) a where s.id = a.id;
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
9.6 count（聚合函数）优化
explain select count(*) from tb_sku;
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count( ) 的时候会直接返回这个数，效率很高；
- InnoDB 引擎就麻烦了，它执行 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( )（数据库专门做了优化）。
9.7 update（更新数据时）优化
执行update语句的时候，一定要根据索引字段进行更新，否则行锁升级为表锁。
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。
\
