MySQL数据库进阶知识（三）《优化》

学习目标：

• 一周掌握SQL优化知识

---

学习内容：

一、插入数据

1.insert优化

• 批量插入

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

• 手动提交事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

• 主键顺序插入
  主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
  主键顺序插入：1 2 3 4 5 6 7 8 9 15 21 88 89
• 大批量插入数据
  如果一次性需要插入大批量数据，使用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/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n';

二、主键优化

• 数据组织方式
  在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）。
  
• 页分裂
  页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。
  主键顺序插入：
  
  主键乱序插入（页分裂）：
  1.先开启一个新的页
  
  2.将第一页数据50%之后的数据分裂出来放入新页，并将新数据放入新页
  
  3.重新调整页之间的连接顺序
  
• 页合并
  当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到merge_threshold（默认为页的50%），InnoDB会开始寻找最靠近的页（前或者后）看看是否可以将两个页合并以优化空间使用。
  

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

• 主键设计原则
  1.满足业务需求的情况下，尽量降低主键的长度。
  2.插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  3.尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
  4.业务操作时，避免对主键的修改。
  

三、order by优化

（1）Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都是FileSort排序。
（2）Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
例：
1.未建立针对age、phone建立索引，按照age（phone）进行排序，排序执行情况如下：

explain select id,age,phone from tb_user order by age;

explain select id,age,phone from tb_user order by age,phone;

2.针对age、phone建立联合索引后，进行排序操作，查询排序执行情况如下：

create index idx_age_phone on tb_user(age,phone);

explain select id,age,phone from tb_user order by age;

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;

先根据phone进行排序，再根据age进行排序（两种执行方式都出现，原因是不符合最左前缀法则）：

explain select id,age,phone from tb_user order by phone,age;

按照age升序排列，再按照phone倒序排列（两种执行方式都出现）：

explain select id,age,phone from tb_user order by age asc,phone desc;

查看index，collation列表示字段排序方式（A表示升序，D表示倒叙）：

可以在创建索引时指定排序方式：

create index idx_age_pho_ad on tb_user(age asc,phone desc);

在建立age升序、phone倒叙联合索引后，查询执行过程显示如下：

两种索引存储结构如下：

• order by优化原则：
  1.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  2.尽量使用覆盖索引（不使用会导致索引失效）。
  3.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
  4.如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k）。

show variables like 'sort_buffer_size';

四、group by优化

#删除目前的索引idx_user_pro_age_sta
drop index idx_prof_age_sta on tb_user;

#执行分组操作，根据profession字段分组
explainselect 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;

#执行分组操作，根据profession，age分组
explain select profession,count(*) from tb_user group by profession,age;

分组时不满足最左前缀法则（效率较低）：

explain select age,count(*) from tb_user group by age;

对profession进行条件筛选，并根据age进行分组，执行过程如下：表明该情况满足最左前缀法则

explain select age,count(*) from tb_user where profession='软件工程' group by age;

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

---

五、limit优化

比如查询limit 2000000，10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化思路：
一般分页查询时，通过创建覆盖索引，能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化。
分页查询耗时：
1.查询起始位置为1：

select * from tb_sku limit 1,10;

2.查询起始位置为5000000：

select * from tb_sku limit 5000000,10;

查询起始位置越往后，效率越低。
查询起始位置为900w之后的10条数据：用时16s

select * from tb_sku limit 9000000,10;

根据id列进行排序，并分页查询900w之后的10条数据，用时11s

select id from tb_sku order by id limit 9000000,10;

利用查询出来的id列表与tb_sku表进行连接查询，查询出结果用时10s，比原表分页查询节省了6s，效率提升较多。

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

六、count优化

explain select count(*) from tb_user;

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。
• InnoDB引擎更麻烦，它执行count(*)的时候，需要把数据一行一行从引擎读出来，然后累计计数
  优化思路：自己计数（例如增加一个字段，在新增一列时字段数值+1，删除一列时字段数值-1，但该方法较复杂）
• count的几种用法
  count()是一个聚合函数，对于返回的结果集，一行一行进行判断，如果count函数的参数不是null，累计值就+1，否则不加，最后返回累计值。
  用法：count(*)、count(主键)、count(字段)、count(1)
  count(1)表示查询数据时，查询到一次数据，就加1

select count(1) from tb_user;

1.count(主键)
InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）
2.count(字段)
没有not null约束：InnoDB引擎会遍历整张表把每一行字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
有not null约束：InnoDB引擎会遍历整张表把每一行字段值都取出来，返回给服务层，直接按行进行累加。
3.count(1)
InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
4.count()
InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
按照效率排序，count(字段)<count(主键 id)<count(1)≈count()，所以尽量使用count()*

七、update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁
优化：尽量根据主键/索引字段进行数据更新
1.在InnoDB存储结构下，开启2个事务，分别执行如下命令：
事务1：

update course set name='JavaEE' where id=1;

事务2：

update course set name='Spring' where id=4;

在事务1执行时，由于使用了id字段进行条件判断，锁住了id为1的行数据，因此不会影响更改id为4的数据的事务2。事务1、2提交后，查询数据两个事务都生效：

2.在事务1执行的语句更改条件不为索引列，同时在事务2执行更改操作
事务1执行操作：

update course set name='Java' where name='JavaEE';

事务2执行操作：

update course set name='Kafka2' where id=4;

事务2无法操作更改，尽管两个事务未操作同一条数据。原因是事务1在执行更改操作时会对指定条件的字段（未设置索引）进行列锁定
3.对表中name列建立索引后，两个事务的执行情况
– 对name字段建立索引

create index idx_course_name on course(name);

事务1对name列进行条件指定：

update course set name='SpringBoot' where name='Spring';

事务2同时对指定条件的数据进行更改操作：(建立索引后2个事务1操作数据不会影响事务2操作另一条数据）

学习时间：

• 周一至周五晚上 7 点—晚上9点
• 周六上午 9 点-上午 11 点
• 周日下午 3 点-下午 6 点

---

学习产出：

• 技术笔记 2 遍
• CSDN 技术博客 1 篇