一文讲清楚SQL优化所有使用场景

01-插入数据时的SQL优化

1.1 Insert 语句优化

批量插入数据

一次性批量插入的数据不建议超过1000条，一般 500 – 1000 条比较合适

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

手动控制事务

        MySQL中事务提交方式默认自动提交，每执行一条 insert 语句都会涉及开启事务和提交事务，导致频繁的的事务开启和提交

start transaction;– 执行多条 insert 之前手动开启事务
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 语句提交完之后统一提交事务

主键顺序插入

主键顺序插入的性能高于乱序插入

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

如果一次性要插入上百万的大数据量，使用 insert 性能就不高了

1.2 大批量插入数据 — load 指令

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

操作如下：

可以执行如下指令，将数据脚本文件中的数据加载到表结构中：

注意：使用 load 指令批量插入数据时建议使用顺序插入，主键顺序插入性能高于乱序插入

02-主键优化

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

• 一张表中聚集索引只有一个，而二级索引有很多个，二级索引的叶子结点挂的就是数据的主键；
• 如果主键长度比较长，二级索引比较多，会占用大量的磁盘空间；而且在搜索时会耗费大量的磁盘IO

2. 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键；

• 主键乱序插入可能会出现 页分裂 现象

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

• 每次生成的 UUID 是无序的，乱序插入可能会导致频繁的页分裂
• UUID 和 身份证号 这种长度比较长，检索时会耗费更多的磁盘IO

4. 业务操作时，避免对主键的修改；

• 修改主键会修改对应的索引结构，损耗性能

03-order by 优化

04-group by 优化

在没有用到索引情况下，Extra 显示 Using temporary，即用到了临时表，这个性能是比较低的

05-limit优化

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

上面是把子查询看作一张表，通过多表联查实现

————————————————————————————————————

下面则是 覆盖索引 + 子查询 形式实现，两种都行

06-count 优化

MyISAM 补充：如果是带条件的 count，MyISAM 也慢

思路：自己计数（可以借助于redis这样的数据库进行，但是如果是带条件的count又比较麻烦了）

count 的几种用法：

07-update优化

一句话：执行 update 语句时一定要根据索引字段来更新

update 中 where 后面跟有索引的字段，这样子就是行锁，否则就是表锁

08-总结