SQL优化

一、插入优化

1. 批量插入，但也仅限于几千条记录，如果过大量级的数据插入，还是性能较低
2. 手动提交事务，因为MySQL默认自动提交事务，当你执行完一条insert语句后，事务就提交了，频繁的事务的开启和提交会影响性能
3. 主键顺序插入
4. 大量级数据，用load指令

二、主键优化

InnoDB的逻辑结构中，page结构中存放的行数据

如果乱序插入主键的话，会导致页分裂这种现象

主键优化策略

1. 满足业务需求的情况下，尽量降低主键的长度。因为一个表中，聚集索引/主键索引只有一个，但是二级索引有很多个，而二级索引叶子节点挂的正是主键，如果主键比较长，二级索引比较多，那么占用的磁盘空间就比较大
2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
3. 尽量不要使用UUID做主键或者其他自然主键，如身份证号
4. 业务操作时，避免对主键的修改

三、order by优化

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

尽量使用覆盖索引

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

如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size

四、group by优化

1. 在分组操作时，用索引来提升效率
2. 分组操作时候，注意遵循最左前缀法则

例如查询软件工程专业的学生并对年龄进行统计

我们建立profession和age的联合索引

select age,count(*) from tb_user where profession='软件工程' group by age;

五、分页优化

一个非常常见且难顶的问题就是limit 2000000,10，此时MySQL查询2000010条记录，然后仅仅返回2000000~2000010的记录，其他记录丢弃，查询代价非常大

尽量通过覆盖索引+子查询的形式进行优化

六、update优化

由于是InnoDB数据库存储引擎，我们在更新数据的时候，要根据索引字段进行更新，在事务未提交之前加的是行锁，不影响其他行更新操作。但如果不是根据索引字段进行更新，在事务未提交之前加的是表锁，会导致其他行无法进行更新操作

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，如果索引字段失效，那么行锁就会升级为表锁，并发性能就会降低，所以我们在操作的时候尽量根据索引字段进行更新，规避掉使用无索引字段进行的update操作