[大数据]【MySQL】SQL优化

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;

• 主键顺序插入

???????如果一次性需要插入大批量数据（比如: 几百万的记录），使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

# 客户端连接服务端
mysql –-local-infile -u root -p

# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

# 执行load指令将准备好的数据加载到表结构中
load data local infile './file.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

2. 主键优化

（1）数据的组织方式
在InnoDB存储引擎中，表数据根据主键顺序组织存放，这种存储方式的表称为索引组织表（index organized table, IOT）

在InnoDB存储引擎中，数据行存储在逻辑结构“页”中，页的大小默认为16KB。InnoDB存储引擎规定，每个页包含2~N行数据。

（2）页分裂

• 主键顺序插入
  ①从磁盘中申请页，主键按顺序插入。
  
  ②第一页没有满，继续往第一页插入。
  
  ③第一页写满，再写入第二页，页与页之间会通过指针连接。
  
  ④第二页写满，再写入第三页。
  

• 主键乱序插入
  ①初始状态第一页、第二页都已经写满。
  ②此时插入ID为50的记录。由于索引结构的叶子节点是有顺序的，不会再申请一个页，应该按照顺序存储。
  
  ③第一页已经写满，存储不下ID为50对应的数据，此时申请一个新的页
  
  ④但是并不会直接将ID为50对应的数据存入第三页，而是把第一页后一半的数据，移动到第三页，然后在第三页，插入ID为50对应的数据。
  
  ⑤重新调整链表指针。
  
  （3）页合并
  当删除一行记录时，实际上记录并没有被物理删除，只被标记为删除并且它的空间允许被其他记录使用。
  
  
  当页中删除的记录达到合并页的阈值（默认为50%），InnoDB存储引擎会合并相邻的页以优化空间使用。
  
  （4）主键的设计原则

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

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

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

• 业务操作时，避免对主键的修改。

3. order by优化

MySQL的排序有两种方式。我们在优化排序操作时，尽量要优化为Using index。

# 索引
create index idx_user_age_phone_aa on tb_user(age, phone);

# 升序排序1
explain select id, age, phone from tb_user order by age;
explain select id, age, phone from tb_user order by age, phone;

没有创建索引，出现Using filesort，排序性能较低。创建索引后，出现Using index，排序性能较高。

# 降序排序
explain select id,age,phone from tb_user order by age desc , phone desc ;

创建索引后，出现 Using index、Backward index scan（反向扫描索引）。在MySQL中，索引的叶子节点默认从小到大排序，而此时的查询语句是降序排序的，MySQL8版本中，支持创建降序索引。

# 升序排序2
explain select id, age, phone from tb_user order by phone, age;

排序时，联合索引也需要满足最左前缀法则，所以此时出现filesort。

# 一升一降
explain select id,age,phone from tb_user order by age asc , phone desc ;

创建索引时，如果未指定顺序，默认都是按照升序排序的，而查询时，一个升序，一个降序，此时就会出现Using filesort。为了解决上述的问题，我们可以创建这样的联合索引：

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

order by优化法则：

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• 尽量使用覆盖索引。
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则。
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小，默认256KB。

4. group by优化

# 联合索引
create index idx_user_pro_age_sta on tb_user(profession , age , status);

# 分组查询
explain select profession, count(*) from tb_user group by profession;
explain select age, count(*) from tb_user group by age;

我们发现，如果仅根据age字段进行分组，就会出现Using temporary。而如果是根据profession、age两个字段同时分组，则不会出现Using temporary。

• 可以通过索引来提高效率。
• 索引的使用也遵循最左前缀法则。

5. limit优化

在数据量比较大时，如果进行分页查询，越往后，分页查询效率越低。解决方法：覆盖索引加子查询的形式进行优化。

explain select * from tb_sku as t , (select id from tb_sku order by id limit 2000000,10) as a where t.id = a.id;

6. count优化

• MyISAM存储引擎把表的总行数存在磁盘上，执行count(*) 会直接返回，效率很高，但是如果是带条件的count，MyISAM也慢。
• InnoDB存储引擎执行 count(*) ，需要把数据一行一行地从引擎里面读出来，然后累积计数。

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

1. count(主键)
   InnoDB存储引擎遍历整张表，把每一行的主键值取出来，返回给服务层。由于主键不可能为NULL，服务层拿到主键后，直接按行进行累加。
2. count(字段)

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

3. count(数字)
   InnoDB存储引擎遍历整张表，但不取值，返回给服务层。服务层对于返回的每一行，放一个数字进去，直接按行进行累加。
4. count(*)
   InnoDB存储引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键) < count(1) ≈ count(*)，所以尽量使用 count(*)。

7. update优化

• 执行update语句时，一定要根据索引字段进行更新，否则行锁升级为表锁，降低事务并发度。
• InnoDB存储引擎的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁 。

# 客户端1
update course set name = 'JavaEE' where id = 1;

# 客户端2
update course set name = 'SpringBoot' where name = 'PHP';

参考：https://www.bilibili.com/video/BV1Kr4y1i7ru

开发者涨薪指南

48位大咖的思考法则、工作方式、逻辑体系