05 - SQL Optimization

05 - SQL 优化

插入数据、主键优化、order by 优化、group by 优化、limit 优化、count 优化、update 优化...

1. 插入数据

1.1. insert 优化

• 批量插入...

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

• 手动提交事务

start transition...

...

commit...

• 主键顺序插入

性能大于乱序插入

1.2. 大批量插入

推荐使用 load 而不是 insert...

用逗号分割字段...

load 里面写的不是 SQL 语句，而是一个文件...

1.3. 操作

1. 客户端连接服务端时，加上参数 --local-infile

mysql --local-infile -u root -p

1. 设置全局参数 local_infile 为 1

set global local_infile = 1;

1. 执行 load 指令将准备好的数据，加载到表结构中

2. 主键优化

2.1. 数据组织方式

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

2.2. 页分裂

页是存储行数据的...

页可以为空、可以填充一半、也可以填充 100%...

按照主键顺序排列...

页与页之间

如果页写满，会申请新的页...

如果乱序插入

页分裂...而且设计大量数据移动 + 链表移动操作，性能差

2.3. 页合并

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

2.4. 主键设计原则

• 满足业务需求时，尽量降低主键长度

• 插入数据，选择顺序插入，选择 auto increase...

• 尽量不要使用 UUID 做主键，身份证号

• 业务操作时，尽量避免主键修改

3. order by优化

排序 order...

Using index、Using filesort...

1. Using index 是根据索引排序

2. Using filesort 根据文件排序

3.1. 尽量优化为 Using index...

根据索引返回有序数据，性能较高...

这里的序号还是相关的，性能根据 asc 或者 desc 都有关系

3.2. 默认使用升序排序...

所以在创建索引需要指定了后续的排序情况，这样存储在 B+ Tree 里面的叶子节点的数据就是有序的了！

这样正常扫描就行，如果不正常，需要反向扫描 backward scan...

3.3. 总结

• 尽量使用覆盖索引

• 根据排序字段，选择合适的索引...

4. group by 分组优化

和 order by 的优化方向一致

4.1. 分组依据

尽量根据主键 或者 索引 来进行分组

5. limit 优化

分页查询

limit 可以查询指定页的记录

在大数据情况下，limit 效率低...

5.1. 常见问题

limit 200w，10

代表需要对 200w+10 的数据进行排序，最后仅仅返回 10 条数据...

5.2. 优化思路

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

6. count 优化

count 返回个数...MyISAM 引擎会把一个表的总行数存在了磁盘，可以直接返回

但是 InnoDB 引擎不会...会一个一个计数，最后返回...

6.1. 优化

自己计数！

没什么方式

6.2. count 用法

• count() 是一个聚合函数

• 用法 count(主键)

6.3. count 主键

InnoDB 会遍历整张表，每一行的 主键id 都取出来，直接累加（不用判断是否为 null（主键约束））

6.4. count 字段

遍历表

• 没有 not null 约束 || 在服务层判断是否为 null，按值累加

• 有 not null 约束 || 直接累加

6.5. count *

不会取全部字段，不取值，直接累加

6.6. count 1

不取值，每一行放入 1 进行累加

6.7. 性能排序

count * ~ count 1 > count 主键 > count 字段

7. update 优化

要根据索引进行 where 搜索，如果不是，如果执行没有行锁，会直接执行表锁...

关于事务

7.1. 事务

begin 开启一个事务...

commit 结束一个事务？

事务的隔离级别...比较重要的一课...

7.2. 优化

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

并发性能因此降低...