MySQL索引-使用原则

一、索引分类

索引是数据库管理系统中一个排序的数据结构，用来快速的查询、更新数据库表中的数据

• 普通索引(Normal)

也叫非唯一索引，没有任何限制，是最普通的索引

• 唯一索引（Unique）

唯一索引值不能重复，但值可以为null，且可以多个null值；

• 主键索引（Primary key）

主键索引是特殊的唯一索引，区别在于索引值不能为null

• 全文索引（Fulltext）

使用Like语句，查询数据量比较大（几KB）的字段数据（如：消息内容、一片文章）时，全文匹配效率低，适合使用全文索引；
只用文本类型可以使用全文索引（char、varchar、text）

二、索引使用

索引原则

• 列的离散度

1. 列的离散度：count(distinct(column_name)):count(*)，列的全部不同值比数据行数。
2. 列值的重复度越高，离散度也就越低，重复值越低，离散度越高；
3. 要在离散度高的列上建立索引；

• 最左匹配原则：联合索引

1. 联合索引就是多个字段组成一个索引；
2. 联合索引遵循最左匹配原则；
3. 因为最左匹配原则，所以建立联合索引时，把最常用的列放在最左边；
   index(a、b、c)相当于创建了三个索引：index(a)、index(a、b)、index(a、b、c)，因此不用在创建index(a)、index(a、b)；

• 覆盖索引

1. 除主键索引外，其它索引都是二级索引；
2. 主键索引中存索引值和数据，二级索引中村索引值和主键值；
3. 使用二级索引查询数据时，会查询出二级索引中的主键值，在通过主键索引查询出数据，这就是回表；
4. 当查询的列都在二级索引列中，就不会回表，提升了效率；

• 索引条件下推

1. 只适用于二级索引
2. 为了降低读取表完整行的数量，从而减少磁盘IO来提高性能；
3. 就是将数据过滤的动作在存储层完成，而不是server层；
4. ICP默认是开启的

索引创建

• 适合创建所以的字段

where条件
order排序字段
join的on字段
group by字段

• 索引的数量不要过多

数据变动，所以也学要跟着变，所以过多的索引不仅使更新操作变慢且浪费空间；

• 过长的字段建立前缀索引

• 离散度低的字段不要建立索引

区分度低的字段，因为和全表扫描效率差不多，优化器很可能会放弃使用所以，这样不仅没有提高查询效率，反而还要另外维护索引；

• 频繁更新的值，不要建立主键或索引

• 随机无序的值，不要建立索引

• 组合所以，把散列值（区分度）高的值放在前面

• 创建符合索引，而不是修改索引

索引失效情况

1. 索引列上使用函数、表达式计算

函数：replace、SUBSTR、CONCAT、sum、count、avg
计算：+、-、*、/

2. 字符串不加引号

搜索是应该：where name = ‘abc’；
实际使用了：where name = 123；索引失效

3. Like条件中前面带%

Like %xxx或者LIke %xxx%，索引失效

4. 负向查询

不等于：!=或者不在NOT IN，索引失效

注意事项

1. 索引的使用根数据库版本、数据量、数据类型都有关系，索引索引优化和使用这些因素都要考虑进去；
2. 使用索引与否是由优化器决定的，优化器选择选择索引是基于Cost开销决定的，怎么开销小就怎么来；