Mysql索引类型

索引分类

1.按功能划分

• 普通索引

就是最最基础的索引，这种索引没有任何的约束作用，它存在的主要意义就是提高查询效率。

• 唯一性索引

在普通索引的基础上增加了数据唯一性的约束，一张表中可以同时存在多个唯一性索引。

• 主键索引

在唯一性索引的基础上又增加了不为空的约束（换言之，添加了唯一性索引的字段，是可以包含 NULL 值的），即 NOT NULL+UNIQUE，一张表里最多只有一个主键索引，当然一个主键索引中可以包含多个字段（组合主建）。

• 全文索引

其实我们很少在 MySQL 中用，如果项目中有做全文索引的需求，一般可以通过 Elasticsearch 或者 Solr 来做，目前比较流行的就是 Elasticsearch。

2.按存储方式

• 聚集索引

在存储的时候，可以按照主键（不是必须，看情况）来排序存储数据，B+Tree 的叶子结点就是完整的数据行，查找的时候，找到了主键也就找到了完整的数据行。

当我们基于 InnoDB 引擎创建一张表的时候，都会创建一个聚集索引，每张表都有且只有一个的聚集索引：

1. 如果这张表定义了主键索引，那么这个主键索引就作为聚集索引。
2. 如果这张表没有定义主键索引，那么该表的第一个唯一非空索引作为聚集索引。
3. 如果这张表也没有唯一非空索引，那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个 6个字节的列，该列的值会随着数据的插入自增。

每个表建议设置一个id主键，类型为unsigned bigint，单表时自增、步长为1。自增主键插入的时候比较快，直接插入即可，不会涉及到叶子节点分裂等问题（不需要挪动其他记录）。

• 非聚集索引

非聚集索引我们一般也称为二级索引或者辅助索引，对于非聚集索引，数据库会有单独的存储空间来存放。非聚集索引在查找的时候要经过两个步骤，第一步搜索非聚集索引的 B+Tree 拿到主键值，第二步根据主键值再去搜索聚集索引（主键索引）的 B+Tree，这个过程就是所谓的回表。

3.优化

• 覆盖索引

阿里巴巴Java开发手册中推荐：利用覆盖索引来进行查询操作，避免回表。

核心思想就是直接从非聚集索引里获取数据，比如建立一个普通索引a_b_c，那么查询语句：select a,b,c from table 就可以避免回表。用explain的结果，extra列出现：using index 那么就说明走索引了。

• 延迟关联

在做大分页时如limit 100000,10 取第100001-100010条记录，mysql会取出100010条记录然后将前100000条记录丢弃，这无疑是一种巨大的性能浪费。我们可以采用延迟关联来进行优化。
比如：

-- 索引
key `idx_a_b_c` (`a`,`b`,`c`)

-- sql语句
select * from t where a=1 and b=2 order by c desc limit 10000,10;

-- 使用延迟关联优化后的sql
select t1.* from t1 , (select id from t where a=1 and b=2 order by c desc limit 10000,10) t2 where t1.id=t2.id 

核心思想：查询t2时利用了索引覆盖查询id 可以避免回表，最后在查询t1时利用了主键索引。