MySQL如何创建索引优化索引

索引是帮助数据库高效获取数据的一种数据结构

• 优势

提高数据检索的效率，降低数据库的IO成本

• 劣势

降低了更新表的速度

索引分类

1. 单值索引

一个索引包含单个列，一张表可以有多个单列索引

2. 唯一索引

索引列的值必须唯一，但允许有空值

3. 复合索引

一个索引包含多个列

创建索引

indexName ：索引名称
tableName ：表名
columnName : 列名（可包含多个）

方式一

create index indexName on tableName(columnName,...);

方式二

alter table tableName add [UNIQUE] index indexName(columnName,...);

删除索引

drop index indexName on tableName;

查看索引

show index from tableName;

哪些情况适合创建索引

• 主键自动建立唯一索引
• 频繁查询字段
• 查询中与其它表关联的字段，外键关系建立索引
• 频繁更新的字段不适合创建索引
• where条件里用到的建议建索引
• 单键、组合键索引的选择问题，在高并发下倾向于组合索引
• 查询中排序的字段适合做索引，将大大提高排序速度
• 查询中统计或者分组的字段

哪些情况不建议创索引

• 数据少
• 经常增删改的表
• 数据重复且分布平均的表字段

关于索引优化

• 两表连接
  • 左连接 索引加右表外键
  • 右连接 索引加左表外键
• 组合索引(where)
  • 全值匹配 查询或筛选的多列或一列与创建索引相同
  • 最佳左前缀法则（位置顺序不一致也会导致索引失效，但MySQL会根据实际情况自动优化成位置顺序一致，所以有时候也不会失效,例如where a = 1 and c = 2 and b = 3 MySQL可以自动调整位置）
  • 中间不能断 组合索引的使用中间列不能失去
  • 范围之后全失效 遇到查询范围的条件 之后的索引会失效
• 不在索引字段上做任何操作(类型转换)，否则索引失效
• 尽量使用索引覆盖(select)

关于索引失效

• 使用!=或者<>的时候索引失效
• is null或者is not null也会失效
• like后配合%或是_最好加字段右边，否则失效，如果非要用，可以用覆盖索引解决。
• 字符串不加单引号索引失效，因为使用了隐式类型转换。
• 少用or，索引会失效
• 类型转换，或者在索引字段上进行操作

order by与索引

排序字段跟索引一致可以避免filesort
对于组合索引使用排序，顺序、升降要一致，否则产生filesort

示例：

索引情况：KEY a_b_c(a,b,c)

• order by可以使用索引情况
  • order by a
  • order by a,b
  • order by a,b,c
  • order by a DESC,b DESC,c DESC
  • where a = const order by b,c(带头大哥是常量，所以排序不是从a开始也可以)
  • where a = const and b = const order by c
  • where a = const and b > const order by b,c(同上)
• order by索引失效情况
  • order by a ASC,b DESC,c DESC （升降不一致）
  • order by b,c (索引头丢失)
  • where a = const order by c （中间断了）
  • where a = const order by c,d （d不是索引一部分）
  • where a in (…) order by b,c (范围之后全失效)