SQL优化

SQL优化：插入数据、主键优化、order by优化、group by优化、limit优化、count优化、update优化等7个方面。

一、插入数据

a、选择批量插入数据的方式，设置手动提交实物，避免频繁的开启和关闭事物。

b、插入数据时避免主键乱序插入，尽量顺序插入。

c、涉及大批量插入数据，用mysql提供的load指令插入。

实践：

1、开启从本地加载文件导入数据的开关：

 2、开始加载本地大批量数据： 

二、主键优化

a、尽量避免乱序插入，乱序插入会产生页分裂的现象，影响效率和空间，顺序主键的话插入数据直接会插在最末端。

b、主键顺序插入时，mysql会监测是否符合页合并的条件，满足的话会合并起来节省空间。

c、满足业务的条件下，尽量降低主键的长度，因为二级索引的叶子节点挂的就是主键，如果主键较长，二级索引比较多，那么会占用大量的磁盘空间，搜索的时候会耗用大量的磁盘io。

d、使用auto_increment自增主键，不要用uuid做主键或者其他自然主键如身份证号，这些生成的乱序的主键且长度较长，会影响插入效率。

e、业务操作时，尽量避免对主键的修改，因为会动到主键的索引结构，代价比较大。

页分裂：

页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-n行数据（如果一行数据多大，会行溢出），根据主键排列。

主键顺序插入：写满一页就开启一页继续写即可。

主键乱序插入：

插入50时，发现是插在第一页(mysql根据主键顺序插入)，已经满了，就会开辟新的第三页

找到第一页的50%的位置，移动到新开的第三页，再把50插入在47后

重新设置链表指针：

页合并

当删除一行记录时，实际上记录并没有被真正的物理删除，只是被标记(flaged) ，这样它的空间就变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%，可以自己修改)，innoDB会开始寻找靠近页的(前或后) 看看是否可以将两个页合并以优化空间使用。

1、13--16删除时只是被标识，没有被真正删除，标识后可以被其他记录声明使用。

2、删除13--16达到了页的50%，innoDB寻找靠近页的(前或后) 看看是否可以将两个页合并以优化空间使用，发现第三页的内容可以移动进去，然后17--19会被插到13--15的位置。

 三、order by 优化

a、根据排序的字段建立合适的索引(多字段排序创立联合索引)，多字段排序时，也遵循最左前缀法则。

b、尽量使用覆盖索引，不要用select * ，用select id,name...， 因为用了*的话可能回表查询不走索引，如果id和name刚好有索引，那么select id,name时会直接走二级索引返回值，不需要回表查询，效率提高。

c、多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则，创建时要指定字段排序规则。

d、如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区的大小sort_buffer_size(默认是256k)

超过256k就会在磁盘文件中进行排序，性能就会降低，你可以修改这个值大一点：

explain时Extra中的值解析：

实践：

1、 传统方式创建的联合索引，创建时没指定字段排序规则，查找数据时，发现Extra中存在Using filesort, 操作效率不是很高。

2、创建联合索引时指定字段的排序方式：（age升序，phone降序）：

 3、这时phone已经变为降序了：

 4、这时再去查找数据，Extra中发现已经没有Using filesort，查找效率提高了。

 四、group by优化

a、根据分组的字段创建联合索引。

b、创建的联合索引也遵循最左前缀法则。

五、limit优化 

a、用覆盖查询方式优化，避免select *

b、用子查询方式优化

实践：

1、传统select耗时较长

 2、先select id得到数据的id，然后再根据id获取数据；id是主键索引，select id效率也相对较高。

3、根据id来获取数据 。直接用in的话语法报错，可以把id当成一个表，通过多表联查来获取数据：

 五、count优化

六、update优化

 update的条件要是索引字段，比如where id =1 中的id是主键索引，update时只会锁住当前行数据，这时操作效率会高；where name =‘韦一笑’，这name不是索引的话在操作时会把整个表给锁住！其他事务进行操作时就会被锁住等待耗时！

总结：