一、插入优化
- 批量插入,但也仅限于几千条记录,如果过大量级的数据插入,还是性能较低
- 手动提交事务,因为MySQL默认自动提交事务,当你执行完一条insert语句后,事务就提交了,频繁的事务的开启和提交会影响性能
- 主键顺序插入
- 大量级数据,用load指令
二、主键优化
InnoDB的逻辑结构中,page结构中存放的行数据
如果乱序插入主键的话,会导致页分裂这种现象
主键优化策略
- 满足业务需求的情况下,尽量降低主键的长度。因为一个表中,聚集索引/主键索引只有一个,但是二级索引有很多个,而二级索引叶子节点挂的正是主键,如果主键比较长,二级索引比较多,那么占用的磁盘空间就比较大
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID做主键或者其他自然主键,如身份证号
- 业务操作时,避免对主键的修改
三、order by优化
根据排序字段建立合适的索引,根据索引排序,多字段排序时,也遵循最左前缀法则
尽量使用覆盖索引
多字段排序,一个升序,一个降序,此时需要注意联合索引在创建时的规则
如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size
四、group by优化
- 在分组操作时,用索引来提升效率
- 分组操作时候,注意遵循最左前缀法则
例如查询软件工程专业的学生并对年龄进行统计
我们建立profession和age的联合索引
select age,count(*) from tb_user where profession='软件工程' group by age;
五、分页优化
一个非常常见且难顶的问题就是limit 2000000,10,此时MySQL查询2000010条记录,然后仅仅返回2000000~2000010的记录,其他记录丢弃,查询代价非常大
尽量通过覆盖索引+子查询的形式进行优化
六、update优化
由于是InnoDB数据库存储引擎,我们在更新数据的时候,要根据索引字段进行更新,在事务未提交之前加的是行锁,不影响其他行更新操作。但如果不是根据索引字段进行更新,在事务未提交之前加的是表锁,会导致其他行无法进行更新操作
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,如果索引字段失效,那么行锁就会升级为表锁,并发性能就会降低,所以我们在操作的时候尽量根据索引字段进行更新,规避掉使用无索引字段进行的update操作