MySQL索引详解

什么是索引

索引类似于书本的目录，是存储引擎用于提高数据库表的访问速度的一种数据结构。在mysql内部，将热点数据，以B+树的形式将所有page页，进行组织成一定的数据结构，再用其配套的查找算法进行查找，叫做索引查询。

为什么要有索引

数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有数据到内存，依次进行检查，读取磁盘的次数较多。

有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，大大提升了查询速度。

索引的类型

按照数据结构分类：

BTree索引

MySQL数据库使用最多的索引类型就是BTree索引，底层基于B+树的数据结构来实现的。B+树是基于B树和叶子节点顺序访问指针进行实现，它具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出来的哈希码值是不同的，将哈希码的值作为哈希表的key值，将数据行的指针作为哈希表的value值。

全文索引（fulltext）

目前只有MyISAM引擎支持（InnoDB在1.2.x之后也支持）。只有char、varchar、text列上可以创建全文索引。

RTree索引

RTree在MySQL很少使用，仅支持geometry数据类型。相对于BTree，RTree的优势在于范围查找。

为什么不是其他数据结构

B+ vs 链表

链表需要线性遍历，搜索效率低。

B+ vs 二叉搜索树

二叉搜索树的搜索效率不稳定，在极端情况下会退化成线性结构，同样存在搜索效率低的问题。

B+ vs AVL树/红黑树

虽然AVL树和红黑树是平衡或者近似平衡，但毕竟是二叉结构，相比较多阶B+，意味着树整体过高，同样是自顶向下查找，层高越低，意味着系统与磁盘有着更少的IO交互。

B+ vs 哈希

B+索引与哈希索引的区别：

1. 哈希索引不支持排序，因为哈希表是无序的。

2. 哈希表不支持范围查找。

3. 哈希表不支持模糊查询及多列索引的最左前缀匹配。

4. 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

官方的索引实现方式中，MySQL是支持哈希索引的，不过InnoDB和MyISAM并不支持。虽然哈希的查找速度很快，但对于范围查找就明显不行。

B+ vs B

B+树与B树的区别：

1. B树节点，既有数据，又有Page指针，而B+，只有叶子节点有数据，其它目录页，只有键值和Page指针。

2. B+叶子节点，全部相连，而B没有。

为何选择B+：

1. 非叶子节点不存储data，这样一个节点就可以存储更多的key，可以使得树更矮，所以IO操作次数更少。

2. 叶子节点相连，更便于范围查找。

聚簇索引和非聚簇索引

聚簇索引

 

像InnoDB这种，将用户数据与索引数据放在一起的索引方案，叫做聚簇索引。

非聚簇索引

向MyISAM这种，将用户数据与索引数据分离的方案，叫做非聚簇索引。

区别

1.叶节点存放数据不同

MyISAM引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。MyISAM最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。相较于InnoDB索引，InnoDB是将索引和数据放在一起的。

2.MyISAM不需要回表查询

MySQL除了默认会建立主键索引外，用户也有可能建立按照其他列信息建立的索引，一般这种索引叫做辅助（普通）索引。

对于MyISAM，建立辅助（普通）索引和建立主键索引没有差别，无非就是主键不饿能重复，而非主键可以重复。叶子节点放的同样是指向改行数据的指针。

而对于InnoDB，InnoDB的非主键索引中的叶子节点并没有数据，而只有对应记录的key值，所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检验辅助索引获得主键，然后用主键到主键索引中检索获得记录。这种过程，就叫做回表查询。

总结

这样看来MyISAM引擎是优于InnoDB引擎的，因为MyISAM做到了将用户数据与索引数据进行解耦，一方面降低了数据之间的耦合度，另一方面，提升了查找效率。而且MyISAM还支持全文索引，更方便用于进行查找，但为什么InnoDB任然是主流呢？是因为InnoDB支持事务，而MyISAM并不支持。

索引设计原则

1. 使用区分度高的字段作为索引列，索引效果更好。

2. 比较频繁作为查询条件的字段应该创建索引。

3. 不会出现在where子句中的字段不该创建索引。

4. 更新非常频繁的字段不适合创建索引。

5. 尽量使用短索引，对于较长的字符串进行索引时，应该指定一个较短的前缀长度，因为较小的索引使得目录页中能存放更多的目录，能有效的减少高度，使系统与磁盘的IO次数减少，查询速度加快。

6. 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。

索引失效

1.对于组合索引，不使用组合索引最左边的字段，则不会使用索引。

2.以%开头的like查询如%abc，无法使用索引；非%开头的like查询，如abc%，相当于范围查询，会使用索引。

3.查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效。

4.判断索引列是否不等于某个值时。

5.对索引列进行运算。

6.查询条件使用 or 连接。

索引操作

创建主键索引

方式一：

-- 在创建表的时候，直接在字段名后指定 primary key create table user1(id int primary key, name varchar(30));

方式二：

-- 在创建表的最后，指定某列或某几列为主键索引 create table user2(id int, name varchar(30), primary key(id));

方式三：

create table user3(id int, name varchar(30)); -- 创建表以后再添加主键 alter table user3 add primary key(id);

创建唯一索引

方式一：

-- 在表定义时，在某列后直接指定unique唯一属性。 create table user4(id int primary key, name varchar(30) unique);

方式二：

`-- 创建表时，在表的后面指定某列或某几列为unique create table user5(id int primary key, name varchar(30), unique(name));

方式三：

create table user6(id int primary key, name varchar(30)）； alter table user6 add unique(name);

创建普通索引

方式一：

create table user8(id int primary key, name varchar(20), email varchar(30), index(name) --在表的定义最后，指定某列为索引 );

方式二：

create table user9(id int primary key, name varchar(20), email varchar(30)); alter table user9 add index(name); --创建完表以后指定某列为普通索引

方式三：

create table user10(id int primary key, name varchar(20), email varchar(30)); -- 创建一个索引名为 idx_name 的索引 create index idx_name on user10(name);

创建全文索引

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) )engine=MyISAM;

查询索引

方式一：show keys from 表名;

方式二：show index from 表名;

方式三（信息比较简略）：desc 表名;

删除索引

方式一：

-删除主键索引：alter table 表名 drop primary key;

方式二：

-其他索引的删除： alter table 表名 drop index 索引名; 索引名就是show keys from 表名中的Key_name 字段。

方式三：

drop index 索引名 on 表名;