mysql_SQL优化

插入数据优化

1.批量插入数据

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

2.手动控制事务

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');
commit;

3.主键顺序插入，性能要高于乱序插入。

1	主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
2	主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

主键优化

1.满足业务需求的情况下，尽量降低主键的长度。
2.插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
3.尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
4.业务操作时，避免对主键的修改。
5.从分裂页 和合并页这个维度 去考虑问题
MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。 默认50%

order by优化原则:

A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

B. 尽量使用覆盖索引。

C. 多字段排序, 一个升序一个降序，此时需要注意联合索引 在创建时的规则（ASC/DESC）。

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

group by优化

A. 在分组操作时，可以通过索引来提高效率。

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

limit优化

原因：在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
优化思路: 一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain	
select * from tb_sku t , 
(select id from tb_sku order by id limit 2000000,10) a
 where t.id = a.id;

count优化

count用法	含义
count(主键)	InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
count(数字)	InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1” 进去，直接按行进行累加。
count(*)	InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接 按行进行累加。
count(字段)	没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

按照效率排序的话 
count(字段) < count(主键 id) < count(1) ≈ count(*)，
所以尽量使用 count(*)。

update优化

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时。

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

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁   ,并且该索引不能失效，否则会从行锁升级为表锁 。