10 SQL进阶-SQL优化（8.15）

一、插入数据

1、批量插入

建议500-1000条，超过1000建议分割处理。

2、手动提交事务

3、主键顺序插入

4、大批量插入数据-load

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行操作。

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

二、主键优化

1、数据组织方式

看最后一行id大小，由小到大。

叶子结点和索引都存在page中。

2、页分裂

（1）顺序插入

（2）乱序插入

图中为已经插入好的数据，现在要插入id=50的数据。叶子结点是有序的，但一号page写不下，找到第一个数据页50%的地方，先将23和47移动到新数据页，再后面放入50。

对链表指针重新设置。

3、页合并

删除13 14 15 16:，达到50%，将2和3自行合并。

最后效果：

默认为50%，可以修改。

4、主键设计原则

三、order by 优化

1、排序方式

（1）using filesort

通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序。

因为age和phone没有索引，所以全表扫描。

（2）using index

通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

前提：覆盖索引

创建索引后，再次排序，即为using index。

（3）backward index scan

backward index scan：反向扫描索引

Btree里是升序排序，要反向扫描一下。

（4）其他

索引顺序为phone、age，这里反了过来，违背最左前缀法则。

创建索引时，默认升序，age为用到索引，phone全表扫描。

这里的collation中的A为asc。

优化一下。

先按照年龄升序排序，年龄相同再按照phone升序排序。

先按照年龄升序排序，年龄相同再按照phone降序排序。

2、总结

（最后一条）

show variables like 'sort_buffer_size';

四、group by优化

1、案例

全表扫描，没有用到索引。using temporary 用到临时表，性能较低。

建立索引：

用age分组，有using temporary，性能较差。因为没有满足最左前缀法则。

此时满足最左前缀法则。使用了索引。

利用where条件筛选profession，再用age分组，此时也是用了索引，想Btree结构。

2、总结

（1）在分组操作时，可以通过索引来提高效率。

（2）分组操作时，索引的使用也是满足最左前缀法则的。

五、limit优化

两者时间不同，越往后越大。

优化前：

优化后：

把查询到的id看做新表

六、count优化

1、优化思路

2、count的几种用法

3、性能分析

七、update优化

客户端一：

此时会把id=1给锁住。

客户端二：

此时会把id=4锁住。

相互不影响，可以提交。

若是update course set name = 'SpringBoot' where name = 'PHP';

此时为表锁，另一事务将不能执行update操作。当事务中update被执行，表锁被释放可以成功。

当对name字段建立索引，此时再执行前面的操作，为行锁。