SQL 语句优化

🔥 主要思路：
🚀 增加数据时，尽量顺序插入
🚀 删除、修改、查询数据时，尽量走索引操作

🐈‍⬛ 主键优化

⭐ 满足业务需求的情况下，尽量降低主键的长度，减少磁盘 IO
⭐ 插入数据尽量选择顺序插入，选择使用 auto_increment 自增主键
⭐ 尽量不要使用 UUID 或其他自然主键（如身份证号）作为主键
⭐ 业务操作避免对主键的修改

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

数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认 16K。也就意味着，一个页中所存储的行也是有限的，如果插入的数据行在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

🦮 insert 优化

一次性往数据库中插入多条数据，可从以下三方面优化：

批量插入数据

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

手动控制事务

1
2
3
4
5
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;

主键顺序插入

数据在 B+ 树的叶子节点有序存放，顺序插入保障每次插入都是在后面添加一条记录（顺序磁盘 IO），不需要调整叶子节点中的内容。

1
2
🙅‍♂️ 主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
🙋‍ 主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

🦛 update 优化

🫧 防止索引失效
🫧 防止升级为表锁
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。
开启多个事务，在执行 SQL 时，防止行锁升级为表锁。

🦒 order by 优化

🍀 根据排序字段建立合适的索引
🍀 多字段排序时，尽量遵循最左前缀法则
🍀 尽量使用索引 / 覆盖索引

🐎 limit 优化

在数据量比较大时，如果进行 limit 分页查询，在查询时，越往后，分页查询效率越低。

当在进行分页查询时，如果执行 limit 2000000,10 ，此时 MySQL 会排序前 2000010 条的记录，仅仅返回 2000000 - 2000010 的记录，其他记录会被丢弃，查询排序的代价非常大。

优化思路:

🎈 在业余允许的情况下，可以限制页数。通常用户也不会向后面翻很多页。
例如在淘宝双十一时不能查询历史购物记录。

🎈 可以通过先定位数据，先偏移，再 limit

1
2
3
select * 
from user
where id > 2000000 limit 10;

🎈 通过覆盖索引 + 子查询形式进行优化

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
select *
from user u
where id >= (select id from user order by id limit 2000000 1)
limit 10;

-- or

select * 
from user u, (select id from tb_sku order by id limit 2000000, 10) a
where u.id=a.id;

🦬 count 优化

count(*) == count(数字) > count(主键) > count(字段)

⭐ count(主键)
- InnoDB 引擎会遍历整张表，把每一行的主键id 值都取出来，返回给服务层。服务层拿到主键后，按行进行累加（主键不可能为null）。
⭐ count(字段)（注意 null 约束）
- 没有 not null 约束: InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null，就计数累加。
- 有 not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
⭐ count(数字)
- InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字 “1” 进去，直接按行进行累加。
⭐ count(*)
- InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。