mysql系列05---sql优化

1、主键优化

主键存储规则：

在innoDB引擎中，表数据都是根据主键顺序存放的，主键索引的叶子节点存的是这一行的行数据，非叶子节点只存索引

主键设计的原则：

a、主键最好顺序生成，否则在插入数据时会产生页分分裂。

b、主键不要过长

c、不要使用身份证作主键

d、业务操作时尽量避免对主键的修改

 

2、insert优化

 a、大量数据插入时打开 --local-infile 参数开关

b、多条insert语句要放在一个事务中。不要一条insert语句一个事务。

3、order by优化

 执行计划的规则

using filesort:将通过索引或全表扫描的数据在缓冲区再次排序，而不是返回排序的结果

using index:通过有序索引直接返回有序的数据，而不需要再次排序，效率更高。

优化的原则：

a、根据排序字段建索引、遵守最左前缀法则

b、尽量使用覆盖索引，不要使用select * 

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

 

4、group by 优化

优化思路： group by 后面的字段也可以通过建索引来提高效率，也遵守最左前缀法则

5、update 优化

 innoDB行锁是针对索引加的锁，不是针对记录加的锁，如果此列的索引失效，则会从行锁升级到表锁。所以更新的条件必段要有索引。

6、limit优化

 优化思路：

a、创建覆盖索引

b、加子查询

 

7、count优化

如果字段的值不是null内里加1，否则不加

效率从高到低：count(*)>conut(1)>counut(id)>count(其它字段)