SQL优化

一、Insert优化：

1、批量插入：（单次批量500-1000条）

2、使用手动提交事务：

start transaction;
insert into tb_user values(xxx,xxx),(xxx,xxx),(xxx,xxx);
insert into tb_user values(xxx,xxx),(xxx,xxx),(xxx,xxx);
insert into tb_user values(xxx,xxx),(xxx,xxx),(xxx,xxx);
commit;

3、主键顺序插入：

二、大批量插入数据：

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的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';

此时注意：主键顺序插入性能高于乱序插入

三、主键优化：

数据组织方式：

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ETEbv6Sv-1687508886797)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619112246636.png)]

页分裂：

页可以为空，也可以填充一半，也可以填充100%。

每个页包含了2-N行数据（如果一行数据多大，会行一处），根据主键排列。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pqiZcWnt-1687508886798)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619170029246.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OtUmf0dN-1687508886798)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619170037711.png)]

页合并：

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ngaOl6Kg-1687508886799)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619170407013.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lFaCuZ4u-1687508886799)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619170435787.png)]

注意：MERGE_THRESHOLD：可以自己设置，在创建表或者创建索引时指定。

主键设计原则：

（1）满足业务需求的情况下，尽量降低主键长度。

（2）插入数据时候，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

（3）尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

（4）业务操作时，避免对主键的修改。

四、order by 优化

Extra：额外信息

（1）Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。

（2）Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

-- show index from tb_user;
#创建索引，默认升序排列
-- create index idx_age_phone on tb_user(age,phone);
#查看执行计划可以发现使用idx_age_phone索引
-- EXPLAIN select id,age,phone from tb_user order by age,phone;
#创建索引后进行降序排列
-- EXPLAIN select id,age,phone from tb_user order by age desc,phone desc;
#一个升序一个降序会导致效率降低
-- EXPLAIN select id,age,phone from tb_user order by age asc,phone desc;
#创建对应升序/降序字段对应索引
-- create index idx_user_age_pho_ad on tb_user(age asc,phone desc);

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

2、尽量使用覆盖索引

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

4、如果不可避免的出现filesort，大数据量排序时候，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

五、group by优化

-- show index from 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);
#执行分组操作，根据age分组（效率低）
-- EXPLAIN select age,count(*) from tb_user group by age;
#根据profession、age分组
-- EXPLAIN select profession,age,count(*) from tb_user group by profession,age;
#添加where条件，指定profession之后再根据age分组
-- EXPLAIN select age,count(*) from tb_user where profession='软件工程'  group by age;

在分组操作时候，可以通过索引来提高效率。

分组操作时，索引的使用也需要满足最左前缀法则。

六、limit优化

常见问题：limit 2000000，10 => 此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢失，查询排序代价非常大。

优化思路：一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询的形式进行优化

-- select * from tb_sku LIMIT 9000000,10;

-- select id from tb_sku ORDER BY id LIMIT 9000000,10;

-- select s.* from tb_sku s,(select id from tb_sku ORDER BY id LIMIT 9000000,10) a where s.id=a.id;

七、update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

begin;
#存在主键索引，所以使用行级锁
update course set name = 'xxxx' where id = 1;
--commit;

#事务a
begin;
#name字段不存在索引，引擎使用表锁
update course set name = 'xxxx' where name = 'x';
--commit;

#事务b
begin;
#此时因为事务a已经给course表施加表锁，下面语句失效
update course set name = 'xxx' where id = 2;
--commit;