MySql优化

sql优化

insert 优化
批量插入
insert  into tb_test values(1,'TOM'),(2,'CAT'),(3,'JERRY');

手动提交事务
start transaction;
insert into tb_test values(1,'TOM'),(2,'CAT'),(3,'JERRY');
insert into tb_test values(4,'TOM'),(5,'CAT'),(6,'JERRY');
insert into tb_test values(7,'TOM'),(8,'CAT'),(9,'JERRY');

主键顺序插入
主键乱序插入:2 6 8 1 67 59 1 6 2
主键顺序插入：1 1 2 2 6 6 8 67 29

大批量数据插入
insert 性能较低  此时使用load 指令进行插入
load data local infile

主键优化 
插入删除数据的两个现象
页分裂：页可以为空 也可以填充一半 也可以填充100% 。每个页包含了2-N行数据（如一行数据多大 才会溢出），根据主键排列。
页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到 MERGE THRESHOLD(默认为页的50%)，nnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

主键设计原则
1.满足业务需求情况下 尽量降低主键的长度。（尽可能的短）
2.插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
3.尽量不要使用UUID（无序）做主键或者是其他自然主键 像身份证号。
4.业务操作尽量别对主键进行修改

order by 优化
1.Using filesort;通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，
所有不是通过索引直接返回排序结果的排序都叫 FileSort排序
2.Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

根据排序字段建立合适的索引， 多字段排列时，也需要遵循左前缀法则。
尽量使用覆盖索引
多字段排序 一个升序一个降序 此时需要注意联合索引在创建时的规则（ASC/DESC)
如果不可避免的出现filesort,在大数据排列时，可以适当增大排列缓冲区大小sort_buffer_size(默认256k)

group by 优化
在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的。

limit 优化

一个常见又非常头疼的问题就是 limit 2000000,10，此时需要MvOL排序前2000010 记录，仅仅返回2000000 - 2000010的记录，
其他记录丢弃，查询排序的代价非常大。
优化思路:一般分页查询时，通过创建 覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

count 语句的优化
没有太好的方式

update优化
尽量根据主键/索引字段进行数据更新。
InnouDB的行锁是针对索引加的锁 不是针对记录加的锁，并且该索引不能失效 ，否则会从行锁升级为表锁