SQL 优化

插入数据

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
 
-- 查询全局参数 local_infile 默认是0
select @@local_infile;
 
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 
set global local_infile = 1;
 
-- 执行load指令将准备好的数据，加载到表结构中 文件中字段以,分割，行以换行分割
load data local infile '/root/load_user_100w_sort.sql' into table tb_user fields terminated by ',' lines terminated by '\n' ;

 可以看出使用load用时16秒多，如果使用insert可以需要数分钟。注意 “在load时，主键顺序插入性能高于乱序插入 ”

主键优化

主键按顺序插入

 主键乱序插入 

页分裂

主键乱序插入时，可能出现页分裂的情况

1.当插入一个id=50的数据，这时第一个page剩余的容量不够存储

 2.则申请一个新的page

 3，将1#page从50%处分割，分割后后半部分，移动到新的page中

 4.把新插入的id=50的数据追加到新的page中

 

5.需用重新设置每个page的，双向链表

 以上的5步，就是在乱序插入可能发生的页分裂情况，所以批量插入时，推荐按照主键顺序插入。

页合并

1.当行数据被删除时，没有物理删除，只是被标记，例如图中的13,14,15,16

2.当page被标记删除的数据已经超过容量的50%，则会查看前后的page是否有容量少于50%，可以合并的page，如果有则，移动数据到2#page中

 3.如果在插入新的数据则会在#page中插入，这种动态移动行数据的被称为页合并

MERGE_THRESHOLD 表示 合并的上限的阀值

 主键设计的原则

Order by 优化

 Mysql的排序有两种方式

Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序 操作时，尽量要优化为 Using index。

实例：

 升序、降序的索引结构图

 

Group by 优化

Limit 优化

 

Count 优化

 

 

update优化

 行锁升级成表锁的原因是，where的条件，没有设置索引，所以需要全表扫描，所以会升级锁，如果name存在索引，则数据库可以定位到一行，只会使用行锁进行更新操作。

 总结