MySQL的SQL语句优化和索引设计

MySQL的SQL语句优化和索引设计

前言

先稍微讲述下面会出现的名词：

• 联合索引：多个字段组合创建的索引
• 覆盖索引：使查询的字段和条件字段都出现在同一个索引中，就可以避免要还去另一个索引中寻找
• 最左前缀法则：这个要自己先去看索引的详细内容，这边讲不完。

1.插入优化：

• 多条数据时建议批量插入

• 设置手动提交事务
  以上都是为了避免频繁地开启关闭事务降低效率

• 按主键顺序插入 避免页分裂，涉及到InnoDB的存储结构

2.主键优化

• 主键长度尽可能短
• 主键尽量自增，避免使用身份证号等无序字段成为主键
  同样是为了避免页分裂

3.order by 优化

• 使用覆盖索引
  一般是where后的条件和order by后的条件建立联合索引，遵循最左前缀法则。条件允许则连同查询的字段也一同建立联合索引，避免回表查询

• 建立索引时根据排序条件的升序降序创建
  创建索引时可指定索引的排序顺序

create index 索引名 on 表名(字段名 asc或desc) #默认是asc升序

4.group by优化

• 类似order by 优化，同样是建立联合索引，尽量覆盖索引

5.limit 优化

• 使用覆盖索引加子查询的方式
  先通过子查询查出主键，再通过联合查询的方式找出主键对应的信息。就是将子查询的结果当作一张新表再联合查询，然后根据联合查询建立联合索引，依旧是尽量覆盖索引，避免回表查询

6.count优化

效率排序：

• count() = count(1) > count(主键) > count(字段)
  **innoDB引擎对count()做了优化，所以尽量使用它**

7.update优化

• 更新的条件要有索引
  innoDB事务中使用的行锁是针对索引的，如果没有索引或者索引失效会导致行锁升级为表锁，当该事务未提交前，其它事务都无法对该表进行操作，会极大降低并发能力

索引的设计原则