08 SQL优化
上一篇文章记录了索引的创建、使用、设计，除了索引方面还需要注意平日对于SQL的使用，对SQL进行优化；SQL的优化是建立在索引使用的基础上
这篇笔记将从以下7个方面对SQL进行优化。
1. 插入数据
使用批量插入,避免循环单条插入
注意批量插入不要超过1000条；若超出则可以将记录进行拆分后分批插入
手动进行事务的提交
开启和提交事务比较耗时，可以执行完多个命令后，手动提交事务
主键顺序插入
插入的时候按照主键从小到大的顺序进行插入，降低维护索引耗费的时长
大批量插入数据时使用load指令
如果有大批量数据需要导入，我们可以使用load指令将表的磁盘文件导入到数据库中
load指令
# 在客户端连接服务端的时候，加上参数--local-infile
mysql --local-file -u root -p;
# 查看参数local_infile，默认值为0
select @@local_infile;
# 设置全局允许加载MySQL数据文件
set global local_infile = 1;
# 加载数据， load data local infile为固定格式
load date local infile '/root/sql1.log' into table 'table_name' fields terminated by ',' lines terminated '\n';
2. 主键优化
在Innodb引擎中，表数据是根据索引组织存放的，这种存储方式称为索引组织表
主键优化方式：
降低主键长度
节省空间，使页中存放更多数据，减少树的层数
尽量顺序插入，降低页分裂
降低页分裂
不要使用uuid，最好要有顺序例如自增
降低页分裂
业务操作不要修改主键
降低页分裂和页合并
页分裂：
Innodb中数据存储的最小单元为页，在主键顺序插入时，会按顺序填充页，一个页满了之后继续填充下一个页形成双向链表
但是当主键乱序插入的时候，假设有A、B两个页，两个页都是满的，由于乱序插入需要在A、B两个页中间插入一条记录，这时需要A分裂出一半数据放入新页C中同时把该记录放入C中；由于页的分裂比较浪费性能，因此插入的时候最好按照主键从小到大的顺序插入
插入和更新操作都可以触发页分裂
参考：https://blog.csdn.net/weixin_44228698/article/details/119057511
页合并（拓展）：
innodb中删除一条记录时并不是立即删除，而是在页中标记为已删除，使得其占用空间可以被其他记录引用，在页合并的时候彻底删除掉
当一个页中被删除的数据达到
MERGE_THRESHOLD（默认为页体积的50%）时，Innodb开始寻找该页前或后的页判断能否合并这两个页，当另一个页正好使用的空间不足50%，就可以合并这两个页为一个，空白的那个页可以继续记录数据删除和更新可以触发页合并
页分裂图解
页合并图解
3. order by优化
使用explain查看order by语句执行计划，在extra中有两种：Using index和Using filesort两种
Using index：通过索引可以直接完成排序，效率很高Using filesort：通过索引或者全表扫描找到数据后；需要将数据加载到sort buffer排序缓冲区中进行排序；所有不是通过索引直接返回数据的都是File sort排序；效率较低因此我们如果优化order by语句也是通过索引来进行入手
- order by也需要遵守最左前缀法则，如果order by字段没有最左侧的索引，那么索引将失效
- 尽量使用覆盖索引
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC、DESC）
- 如果不可避免要使用filesort，在大数据量的时候可以增加sort_buffer_size设置排序缓冲区的大小（默认256k）
4. group by优化
与order by优化类似，使用explain查看group by执行计划，发现extra中也有两种：Using temporal 和Using index两种
Using temporal：使用临时表，效率较低Using index：直接通过索引返回记录因此我们如果优化group by语句也是通过索引来进行入手
- 遵守最左前缀法则，避免索引失效
5. limit优化
一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化思路： 一般分页查询时，通过创建 覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化
explain select * from tb_sku a, (select id from tb_sku order by id limit 2000000, 10) b where a.id = b.id;
6. count优化
通常我们使用count的时候有四种方式：count(*)，count(主键)， count(1)， count(某一列)；对于Myisam引擎，磁盘直接将一个表的总行数记录在了引擎上，使用count(*)的时候直接返回了这个总行数，而Innodb引擎需要遍历每一行将数据从引擎中读出来再累计计数
四种方式的区别：
count(*)：遍历每一行，但是不取数据，服务层直接进行累加count(1)：遍历每一行，但是不取数据，服务层在每一行中加入一个1，直接按行进行累加count(主键)：遍历每一行，取出每一行中的主键，服务层拿到主键后按行进行累加count(某一列)：遍历每一行，取出每一行中的该列，服务层判断该列是否为null，不为null的话进行累加因此执行效率从快到慢为：count(*) ≈ count(1) > count(主键) > count(某一列)
7. update优化避免行锁升级为表锁
我们都知道Myisam引擎为表级锁，Innodb为行级锁，那么我们在update的时候需要避免行锁升级为表锁
在A事务中执行下面sql（name上没有索引），则会导致行锁升级为表级锁，使另一个事务阻塞
update student set sex = '男' where name = '赵四';
因为Innodb的行级锁针对索引加的锁而不是针对记录的，如果没有使用到索引或者索引失效，就会升级为成表级锁