Mysql中InnoDB存储引擎索引

1.索引是什么：

索引用于快速找出在某个列中有某一特定值的行。

2.索引的优缺点：

优点：索引能够有效地提高查询的效率；

缺点：索引过多也会影响应用程序的性能。

3.索引的分类：

按索引包含列的个数可以分为

（1）单列索引（一个索引只包含一个列，但一个表可以有多个单列索引）

（2）组合索引（一个组合索引包含两个或者两个以上的列）

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

我们创建一张menu表

CREATE TABLE `menu` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '菜单id',

  `sid` bigint(20) DEFAULT NULL COMMENT '系统id',

  `name` varchar(255) DEFAULT NULL COMMENT '菜单名',

  `pid` bigint(20) DEFAULT NULL COMMENT '父菜单',

  `type` tinyint(4) DEFAULT NULL COMMENT '类型（1：一级菜单 2：二级菜单）',

  `url` varchar(255) DEFAULT NULL COMMENT '菜单url',

  `sort` int(11) DEFAULT NULL COMMENT '排序',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

我们创建了一个索引

show index 展现每列的结果含义：

Table:索引所在的表名

Non_unique:非唯一的索引，如果是唯一的索引就是0，非唯一索引就是1

Key_name:索引的名称

Seq_in_index:该列在索引中的位置

Column_name:列的名称

Collation:列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。 如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash 根据Hash桶存放索引数据，而不是对数据进行排序。

Cardinality:非常关键的值，表示索引中唯一值的数目的估计值。Cardi-nality表的行数应 尽可能接近1，如果非常小，那么需要考虑是否可以删除此索引。

Sub_part:是否是列的部分被索引。如果索引整个列，则该字段为NULL。

Packed:关键字如何被压缩。如果没有被压缩，则为NULL。

Null:是否索引的列含有NULL值。如果索引的列允许为空的话则为YES

Index_type:索引的类型。

Comment：注释。

 

接下来就是执行查询语句

explain select * from menu where sid = 1

explain是解析SQL语句执行计划，展现每列的结果含义：

Select_type：select的类型，有SIMPLE，就是简单的select

Table：查询的那张表

Type：system >const > eq_ref > ref > fulltext > ref_or_null > index_merge >

unique_subquery > index_subquery > range > index > ALL

Possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。

Key：key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。

Key_len：key_len列显示MySQL决定使用的键（索引）长度。如果键（索引）是NULL， 则长度为NULL。

Ref：显示索引的那一列被使用了，如果可能，是一个常量const。

Rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行 数。

Filtered：指返回结果的行占需要读到的行(rows列的值)的百分比。

Extra：该列包含MySQL解决查询的详细信息。

我们可以看到select * from menu where sid = 1使用了索引index1，

 

 

select * from menu where name="用户";

select * from menu where name="用户" and url="/user";

select * from menu where url="/user";

这三个都没有使用到索引

select * from menu where sid=1 and url="/user";

使用到了索引。

说明查询条件中不包含索引的最左列的不走索引查询。

我们再创建一个name和pid的组合索引

然后执行select * from menu where name like "%用户%";

发现没有通过索引来查找；

执行select * from menu where name like "用户%";

发现通过索引index2来查找数据

说明查询条件中如何有模糊查询的，如果是”XX%”，则可以走索引，如果是”%XX%”，则 不走索引。

按索引的类型可以分为

1. 普通索引（这个是最基本的索引）
2. 唯一索引（与普通索引类似，但是唯一索引要求所有的列的值是唯一的，也允许有空值）
3. 主键索引（要求所在的列的值是唯一的，不允许有空值）

按照索引的存储结构类型可以分为

1. B+树索引
2. Hash索引

B+树索引分为聚集索引和非聚集索引（引用网上文章

https://www.cnblogs.com/s-b-b/p/8334593.html ）

聚集索引

数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

网上举的一个例子来说明什么是聚集索引：一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面，如下图所示：

第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。

结合上面的表格我们可以知道：数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是数据页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

                                                         聚集索引实际存放的示意图

从上图可以看出聚集索引的好处了，索引的叶子节点就是对应的数据节点（MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别），可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询，后面会详细讲。因此在查询方面，聚集索引的速度往往会更占优势。

如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引。Mysql里主键就是聚集索引。

 

非聚集索引

非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

                                                          非聚集索引实际存放的示意图

非聚集索引的二次查询问题

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

例如有下表t

和聚集索引clustered index(id),非聚集索引index（username）

使用如下语句进行查询，不需要进行二次查询，直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

select id,username from t where username=‘小明’

select username from t where username=‘小明’

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score。

select username,score from t where username = ‘小明’

在MySQL里面就算表里数据量少且查询了非键列，也不会使用聚集索引去全索引扫描，但如果强制使用聚集索引去查询，性能反而比非聚集索引查询要差。

 

如何解决非聚集索引的二次查询问题

复合索引（覆盖索引）

建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句

select col1, col2 from t1 where col1 = '213';

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

 

Hash索引

InnoDB存储引擎使用Hash算法来对字典进行查找，其冲突机制采用链表方式，Hash函数采用除法散列方式。

自适应Hash索引采用Hash表的方式实现。这是数据库自身创建并使用的，DBA本身并不能对其进行干预。自适应Hash索引经哈希函数映射到一个Hash表中，因此对于字典类型的查找非常快速，如SELECT * FROM TABLE WHERE index_col=’XXX’。但是对于范围查找就不能使用了。

 

全文索引

全文检索通常使用倒排索引来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：

inverted file index，其表现形式为{单词，单词所在文档的ID}

full inverted index，其表现形式为{单词，（单词所在文档的ID，在具体文档中的位置）}

例如，对于下面这个例子，表t存储的内容如表5-6所示。

DocumentId表示进行全文检索文档的Id,Text表示存储的内容，用户需要对存储的这些文档内容进行全文检索。例如，查找出现过Some单词的文档Id，又或者查找单个文档中出现两个Some单词的文档Id,等等。

对于inverted file index的关联数组，其存储的内容如表5-7所示。

可以看到单词code存在于文档1和4中，单词days存在于文档3和6中。之后再要进行全文查询就简单了，可以直接根据Documents得到包含查询关键字的文档。对于inverted file index，其仅存取文档Id，而full inverted index存储的是对（pair），即（DocunmentId

,Position）,因此其存储的倒排索引如表5-8所示。

full inverted index还存储了单词所在的位置信息，如code这个单词出现在（1:6），即文档1的第6个单词为code。相比之下，full inverted index占用更多的空间，但是能更好地定位数据，并扩充一些其他的搜索特性。

InnoDB存储引擎从1.2.x版本开始支持全文检索的技术，其采用full inverted index的方式。

当前InnoDB存储引擎的全文检索还存在以下的限制：

每张表只能有一个全文检索的索引。

由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。

不支持没有单词界定符的语言，如中文、日语、韩语等。