mysql优化之索引及索引失效

索引：本质上是一种排好序的数据结构，利用索引可以提高mysql的检索效率，降低排序成本；但是会增加磁盘空间，降低新增、修改效率。

1、分类：

主键索引：

就是我们的主键id，因为我们现在默认使用的都是innodb存储引擎，而innodb又使用的是b+数这个数据结构，所以我们创建表的时候必须要设置一个int类型自增的主键。如果设置了这个主键，innodb就是使用这个主键作为聚族索引，而不用再为我们再额外创建一个字段作为聚族索引了，为什么是int类型，因为容易比较大小；为什么需要自增，索引为了维护一个有序性，使得新增的数据都在末尾添加，减小了数分叉的概率

唯一索引

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

联合索引

一个索引中包含多个列

单值索引

一个索引中只包含单个列

2、操作

# 添加单值索引,
# tableName表示表的名称；
# indexName表示这个索引的名称，可以自定义，但建议以idx_加上字段名称；
alter TABLE tableName add INDEX indexName(column)

# 添加联合索引
alter TABLE tableName add INDEX indexName(column1,column2,column3...)
# 有可能添加索引时会出现：column column1 used in key specification without a key length
# 这是因为添加的索引字段长度太大了，可以使用前缀索引来解决，意思就是这个字段我只取前面多少个字节作为索引
alter TABLE tableName add INDEX indexName(column1(50),column2,column3...)
# 这里的column1(50)表示column1这个字段的前50个字节作为索引

# 添加唯一索引（唯一索引也可以多个字段）
# indexName表示这个索引的名称，可以自定义，但建议以idx_加上字段名称；
alter TABLE tableName add UNIQUE indexName(column1...)

# 删除索引
alter TABLE tableName DROP INDEX indexName

3、注意事项

哪些情况下适合创建索引，可以参考一下三星索引的创建原则

• 主键自动创建主键索引
• 频繁作为查询条件的字段应该创建索引
• 查询与他表关联的字段，外键关系应该建立索引
• order by的排序字段应该建立索引
• group by的分组字段应该建立索引
• 优先创建联合索引

哪些情况下字段不合适创建索引

• 表记录太少的可以不用创建索引，如果一个表数据最多不过几千行可以不用创建索引
• 经常需要修改的字段可以不用创建索引
• 重复率太高的字段不用创建索引

4、索引失效

关于索引失效的几种情况，网上有一句口诀我觉得特别好,“模型数空运最快”

• 模：模糊查询%在前面的时候（“%val”）会导致索引失效
• 型：查询时涉及到数据类型转换的会导致索引失效，例如字段column为varchar类型，查询时条件为column=123
• 数：查询时套用函数的会导致索引失效，例如 select * from test1 where concat(id,‘’)=‘1000’;
• 空：查询时使用了 IS NULL 或者IS NOT NULL的会导致索引失效
• 运：查询时使用了运算的会导致索引失效，例如 select * from test1 where id+0=‘1000’;
• 最：查询时where条件不满足最左匹配原则的会导致索引失效
• 快：当MySQL优化器判断全表扫描比使用索引更快时，会导致索引失效
• 使用范围查询时，范围条件之后的条件使用不上索引，例如 select * from table where a = 1 and b > 100 and c = 10;此处的c条件使用不上索引
• 使用 != 或者 <> 时索引会失效