Mysql索引

mysql 索引分类

1. 从物理存储角度分为：

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

1. 从逻辑存储角度分为：

普通索引：仅加速查询,无限制

唯一索引：加速查询 ，列值唯一（可以有null）,如果是组合索引，则列值的组合必须唯一

主键索引：加速查询 ，列值唯一（不可以有null），表中只允许有一个主键索引, 是一种特殊的唯一索引

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（索引合并：使用多个单列索引组合搜索）
只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

全文索引：目前只有 CHAR、VARCHAR 、TEXT 列上可以创建全文索引

1. 索引的优势

1. 通过创建索引,可以在查询的过程中,提高系统的性能
2. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
3. 在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间，比如查询order by age desc，因为B+索引树本身就是排好序的，所以再查询如果触发索引，就不用再重新查询了。
4. 降低CPU使用率
5. 提高查询效率（降低IO使用率）

2. 何时创建索引

1. 经常需要搜索的列上
2. 作为主键的列上
3. 经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
4. 经常需要根据范围进行搜索的列上
5. 经常需要排序的列上
6. 经常使用在where子句上面的列上

3. 创建索引的规范

(1)单张表中索引数量不超过5个。

(2)单个索引中的字段数不超过5个。

(3)索引名必须全部使用小写。

(4)非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。

(5)唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。

(6)组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add。

(7)表必须有主键,推荐使用UNSIGNED自增列作为主键。

(8)唯一键由3个以下字段组成,并且字段都是(整)(形)(时),可使用唯一键作为主键。其他情况下,建议使用自增列或发号器作主键。

(9)禁止冗余索引。

(10)禁止重复索引。

(11)联表查询时,JOIN列的数据类型必须相同,并且要建立索引。

(12)选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。

(13)对字符串使用前缀索引,前缀索引长度不超过8个字符。

(14)不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引,或添加CRC32或MD5伪列并建立索引。

(15)合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

(16)合理使用覆盖索引减少IO,避免排序。

4. 索引失效的情况

• 条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
• 在索引字段上使用计算、函数等，会导致索引失效。例如left(name,4),导致name字段索引失效。
• 对于组合索引，如果不使用的组合索引的第一列字段，则不会使用索引（即不符合最左前缀原则）
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
• 范围查找，右边的索引全失效。eg:一个组合索引 index（name,age.sex）
  select name,age,sex from where name= 'aa' and age >25 and sex=1
  sex 字段上的索引，无法被使用
• 使用