MySQL优化

一、插入数据优化

1、insert优化

1.1、插入多条数据时，不要一条数据一个SQL语句，使用批量插入，一条一条插入每次都要与数据库建立连接。每次批量插入数据量也不建议太大（500-1000）。

1.2、手动提交事务

MySQL中事务是自动提交的，每次插入操作都需要自动提交事务，这样就会导致频繁的事物开启和提交。建议手动控制在执行insert语句之前开启事物，在执行多条insert之后，统一提交事务。

1.3、主键顺序插入

在InnoDB中，表数据都是根据主键顺序组织存放

乱序插入会导致叶分裂现象

什么是叶分裂？

        当插入一条数据主键的值所对应的页（page1）已经放不下，这时就会发生也分裂现象。会先创建一个新的页（page3），然后将超过50%的数据移动到新的页（page3）。然后该条数据插入到新数据页（page3），再对链表指针进行重新设置。将（page1）的下一个指向page3，page3的上一个指向page1，将page3的下一个指向page1之前的下一页。

什么是页合并？

        在InnoDB中要删除一行数据，实际上记录的这条数据并没有被删除，只是被标记被删除了，这块空间就可以被其他记录声明使用。当一个页中数据删除的记录达到MERGE_THRESHOLD
（默认为是页的百分之50），InnoDB会去查看上一页或者下一页是否有合并的可能性。

之前页

2、大数据插入优化

如果一次性插入大批量的数据，使用insert语句效率较低，可以使用load指令进行插入，可以将本地磁盘文件的数据直接加载到数据库中

 二、order by 优化

1、Using filesort :通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
2、Using index :通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
创建联合索引时不指定排序，查询时要么全部使用降序，要么全部使用升序，否则会出现Using filesort，如果order by 排序方式不同，可以重新创建一个联合索引并指定他们的排序方式。多字段排序时，也遵循最左匹配法则。

三、group by 优化

在分组操作是可以创建联合索引提高效率。

在分组操作时，索引的使用也是满足最左匹配法则的

四、limit优化

limit在查询大数据量分页性能下降，原因在于MySQL需要根据偏移量扫描出所有符合的数据，然后再从中取分页记录数返回，其余数据全部丢弃。如 select * from t limit 100000, 10; MySQL需要扫描 100010 行数据并返回最后的10条。

可以用过覆盖索引加子查询的方式来优化：

select s.* from student s ,(select id from student order by id limit 1000000,10) a
where s.id = a.id;

五、count优化

在MyISAM中记录了这个表的总行数，执行count（*）就会返回这个值

在InnoDB中没有这个设计，执行计数时会将数据一行一行读出来累计计数。优化思路就是自己计数（可以在redis中计数，添加一条数据redis计数加一，删除一条数据则减一）

 六、update优化

在InnoDB中支持行锁，我们在执行update语句的时候一定要使用索引更新，并且索引不能失效，否则行锁就会升级为表锁，锁表并发性能就会降低