MySql 索引（聚集索引，辅助索引，联合索引，覆盖索引..）

引入一个面试问题:

看完以下以后再回顾，会发现迎刃而解

Mysql 可以为每一张表设置 存储引擎 这里我们只说 InnoDB 存储引擎.

由于实际情况，数据页只能按照一棵 B+树 进行排序， 因此每张表只能拥有一个 聚集索引（即 主键）。

栗子：

每个叶子节点的索引行中包含了一个书签（bookmark）. 该书签是用来告诉 InnoDB存储引擎哪里可以找到该索引对应的数据行或者说 行数据！ 由于InnoDB存储引擎表， 是按照主键来构建的， 所以 ，该书签内其实包含或者说指向了 数据行所对应的聚集索引键

也就是说 辅助索引的 叶结点保存了 指向对应数据的 聚集索引， 可以通过该聚集索引 找到对应的数据行

辅助索引的存在并不影响数据在聚集索引中的组织，因为每张表上可以有多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引(聚集索引)的主键，然后再通过聚集索引找到一个完整的数据行。

例如：

聚集索引辅助索引关系:

： 又叫做组合索引 ， 辅助索引的一种 ， 和普通创建索引的方式一样，不同的是 可以同时添加多列来作为索引项

从本质上来说，联合索引也是一课B+树

个人理解： 所谓最左原则， 是因为 存储引擎构建组合索引时 是根据最左边的那一列索引项进行排序的 ，所以使用组合索引，必须满足 条件中必须存在 最左边那一列的索引项，这样 才可以找到对应的索引，继而 去寻找对应的数据

： 又叫做 索引覆盖，InnoDB中支持覆盖索引，即 从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

比如 这里没有根据最左原则使用组合索引，但是 优化器依然进行选择

共勉，欢迎指导谢谢~

mysql的索引类型及使用索引时的注意事项有：

一、普通索引。这是最基本的索引，它没有任何限制。它有以下几种创建方式：

1、创建索引

代码如下:

CREATE INDEX indexName ON mytable(username(length))

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

2、修改表结构

代码如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   ) 

-- 删除索引的语法：

DROP INDEX [indexName] ON mytable

二、唯一索引。它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

代码如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

-- 修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

-- 创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   UNIQUE [indexName] (username(length))   )

三、主键索引。它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

代码如下:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   )

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

四、组合索引。为了形象地对比单列索引和组合索引，为表添加多个字段：

代码如下:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  )

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

代码如下:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age)[code]

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：usernname,city,age   usernname,city   usernname  为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

[code]

SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin"

了解mysql的索引类型的时候，我觉得按照以下4中方式划分逻辑是比较清晰的。

1.存储结构 2.物理存储 3.作用字段 4.功能

按照数据存储的结构可以分B树索引和hash索引。

又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

查询必须从索引的最左边的列开始。

查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。

存储引擎不能使用索引中范围条件右边的列。

也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。

其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。

不能使用 HASH 索引排序。

HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。

HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

聚集索引是按照所以把数据排好序了，所以一个表只能存在一个聚集索引，其它的都是非聚集索引。

因这个特性，聚集索引是查询数据范围的时候有很大的性能优势。

但是也需要注意的是如果频繁更新的列不适合设置为聚集索引，

原因很简单，每次更新都需要从新排序，频繁的更新给的压力也大。

如果不指定的话，默认主键为聚集索引。

一个表里除了一个聚集索引外其他的都是非聚集索引，虽然不能把数据按照索引排序，但是索引数据是可以排序的。

所以非聚集索引查询范围的时候是先找索引列的范围，再通过这个索引查询行的值。

单列索引即一个索引只包含单个列。

组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

Primary Key（聚集索引）：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。

主键索引是一种特殊的唯一索引，不允许有空值。

既不是主键索引也不是唯一索引的一般索引。

FULLTEXT（全文索引）：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

空间索引主要用于地理空间数据类型 GEOMETRY。

下面是 mysql官网给出的几个存储引擎和索引之间的关系 。

欢迎大家的意见和交流

email: li_mingxie@163.com

---