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. 操作
客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
设置全局参数 local_infile 为 1
set global local_infile = 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...
Using index 是根据索引排序
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 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁...
并发性能因此降低...
Last updated