MySQL索引

索引介绍

• 索引是什么

索引是帮助数据库高效获取数据的数据结构，通俗讲：数据库索引好比是一本书的目录，能够加快数据库的查询速度。
索引往往是存储在磁盘上的文件中，可以存储在单独的索引文件中，也可以和数据一起存储在数据文件中。
我们通常所说的索引包括：唯一索引、组合索引、聚集索引、覆盖索引、前缀索引等，默认是B+树组织结构。

• 索引的优势和劣势

优势：
1.在检索上，可以提高数据库检索效率，降低数据库的IO成本
2.在排序上，可以通过索引降低数据库的排序成本，降低CPU消耗

劣势：
1.索引会占据磁盘空间
2.索引会降低更新表的效率，每次增删改不仅要保存数据，还要保存对应的索引文件

• 哪些情况需要创建索引：

1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.多表关联查询中关联字段应该创建索引，on两边都要创建索引
4.查询中排序的字段应该创建索引
5.频繁查找的字段应该创建覆盖索引
6.查询中统计或分组的字段应该创建索引

• 哪些情况不需要创建索引：

1.表记录很少
2.经常要进行增删改操作的表
3.频繁更新的字段‘
4.where条件里使用不高的字段

• 索引的分类

1.单列索引

单列索引包括：普通索引、唯一索引、主键索引
a.普通索引：MySQL中基本的索引类型，没有什么限制，可以在定义的索引列中插入null值和重复值。
b.唯一索引：索引中的列的值必须是唯一的，但允许是null值。
c.主键索引：是一种特殊的唯一索引，不允许有null值。

2.组合索引
由表中多个字段组合创建的索引，一般情况下建议使用组合索引替代单列索引（除了主键索引）

3.全文索引
只有在MyISAM引擎和InnoDB引擎上使用，且只能在char、varchar、text类型字段上使用全文索引。

索引的使用

1.创建索引

单列索引之创建普通索引：

create index index_name on table_name(column);
alter table table_name add index index_name (column);

单列索引之创建唯一索引：

create unique index index_name on table_name(column);
alter table table_name add unique index index_name(column);

单列索引之创建全文索引

create fulltext index index_name on table_name(column);
alter table table_name add fulltext index index_name(column);

创建组合索引：

alter table table_name add index index_name(col_1,col_2);

2.删除索引

drop index index_name on table_name;

3.查看索引

show index from table_name;

索引原理分析

索引的存储结构：
索引是在存储引擎中实现的，不同的存储引擎会使用不同的索引
MyISAM和InnoDB存储引擎只支持B+tree索引。
Memory和heap存储引擎支持hash索引和Btree索引。

B树和B+树最大的区别在于是非叶子节点是否存储数据：
B树叶子节点和非叶子节点都会存储数据
B+树只有在叶子节点存储数据，而且存储的都在一行上，这些数据都是有指针指向的(有序的）

• MyISAM非聚集索引

B+树的叶子节点只会存储数据行的指针，也就是数据和索引不在一起
非聚集索引包含主键索引和辅助索引都会存储指针的值

主键索引（Primary Key）：
其中Col1为主键，图为一个MyISAM主索引的示意图，可以看出MyISAM的索引文件仅仅保存数据记录的地址。

通过索引树找到对应的索引，然后通过索引中存储的记录指针找到数据文件对应的记录。

辅助索引（Secondary Key）：
辅助索引在结构上与主键索引没有任何区别，只是主键索引要求Key是唯一的，而辅助索引中Key可以重复。

• InnoDB聚集索引

主键索引的叶子节点会存储数据行，数据和索引是在一起的。
辅助索引只会存储主键的值。
如果没有主键则使用唯一索引创建聚集索引，如果没有唯一索引，MySQL会按照一定的规则创建聚集索引。

主键索引（Primary Key）：
InnoDB要求表必须有主键，如果没有显式指定，则MySQL会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL会自动会InnoDB表生成一个隐含字段作为主键，类型为长整数型。

下图为InnoDB的主键索引图，叶子节点包含了完整的数据记录，

辅助索引（Secondary Key）：
InnoDB辅助索引数据域存储的是相应记录主键的值，即InnoDB所有的辅助索引都引用主键作为data域。

a.多用组合索引
聚集索引的实现方式使得按照主键索引的方式十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后从主索引中检索获得记录。

例如：
select * from user where name = ‘Alice’ 需要进行回表查询
selcet id,name from user where name= ‘Alice’ 则不需要进行回表查询，在辅助索引树上即可查询到。

b.不建议使用过长的字段作为主键
MySQL创建组合索引的规则是首先对组合索引的第一个字段进行排序，在第一个字段排序的基础上对第二个字段进行排序，类如组合索引（name,cid),相当于实现了order by name，cid。

为了节省MySQL

c.尽量在InnoDB表上使用自增字段作为表的主键