mysql索引类型与数据存储 06

myisam与innodb索引与数据存储

一般来说， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node(叶子节点) ，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引。当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息（增加了顺序访问指针），这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。

1. myisam索引实现方式

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
MyISAM的索引与行记录是分开存储的，叫做非聚集索引（UnClustered Index）。
其主键索引与普通索引没有本质差异：
有连续聚集的区域单独存储行记录,主键索引的叶子节点，存储主键，与对应行记录的指针,普通索引的叶子结点，存储索引列，与对应行记录的指针；

举个例子：
有用户表如下：id为主键，name字段为普通索引

id	name	desc	text
1	sj	m	A
5	ls	m	A
9	ww	f	B

主键索引与普通索引是两棵独立的索引B+树，通过索引列查找时，先定位到B+树的叶子节点，再通过指针定位到行记录。

行记录单独存储,id为PK，有一棵id的索引树，叶子指向行记录,name为KEY，有一棵name的索引树，叶子也指向行记录。

2. innodb索引实现方式

InnoDB的主键索引与行记录是存储在一起的，故叫做聚集索引（Clustered Index）：
没有单独区域存储行记录，主键索引的叶子节点，存储主键，与对应行记录（而不是指针）因为这个特性，InnoDB的表必须要有聚集索引：

1. 如果表定义了PK，则PK就是聚集索引；
2. 如果表没有定义PK，则第一个非空unique列是聚集索引；
3. 否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

聚集索引，也只能够有一个，因为数据行在物理磁盘上只能有一份聚集存储。
聚集索引，也只能够有一个，因为数据行在物理磁盘上只能有一份聚集存储。
InnoDB的普通索引可以有多个，它与聚集索引是不同的：普通索引的叶子节点，存储主键（也不是指针）

对于InnoDB表，这里的启示是：

1. 不建议使用较长的列做主键，例如char(64)，因为所有的普通索引都会存储主键，会导致普通索引过于庞大；
2. 建议使用趋势递增的key做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动；

仍是上面的例子，只是存储引擎换成InnoDB:

其B+树索引构造如上图：id为PK，行记录和id索引树存储在一起,name为KEY，有一棵name的索引树，叶子存储id

当查询 select * from user where name=‘ls’;

会先通过name辅助索引定位到B+树的叶子节点得到id=5，再通过聚集索引定位到行记录。

innodb回表问题

如粉红色路径，需要扫码两遍索引树：

1. 先通过普通索引定位到主键值id=5；
2. 在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

什么是覆盖索引？

mysql官网表达意思：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
概念：索引覆盖:通过普通索引查询的时候,不需要回表查询,直接可以获取到对应的数据

如下示例:

创建联合索引(name,sex) 可以避免回表查询提高效率

索引的使用跟查询的and的前后顺序没有关系:

创建(name,sex,flag)索引,以下这两个查询都走索引
select name,id,sex from t where name = “lisi” and sex = “m”;
select name,id,sex from t where sex = “m” and name = “lisi” ;

以下查询不走索引: select name,id,sex from t where sex = “m”;
建了一个索引idx(A, B, C),他说的是要使用A,AB, ABC这样的顺序查询,而使用B, BC, 这样是使用不到索引的.而我理解成了and的前后顺序要跟这一样 查询条件类型不一致,需要强转也不会走索引.

hash 索引

在MySQL的存储引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的，后面会对其做具体说明。

hash索引的特点

1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。
2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。
3、hash索引包括键值、hash码和指针 。

因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：

hash索引的限制

1. Hash索引必须进行二次查找 使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。
2. hash索引不能用于外排序hash索引存储的是hash码而不是键值，所以无法用于外排序。
3. hash索引不支持部分索引查找也不支持范围查找只能用到等值查询，不能范围和模糊查询。
4. hash索引中的hash码的计算可能存在hash冲突。

当出现hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上（重复值很多）。姓名、性别、身份证（合适）
上面说到InnoDB的“自适应hash索引”。就是当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上在创建一个hash索引，这样就让B-tree索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过，如果有需要，完全可以关闭该功能。

BTree索引和哈希索引的区别

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。
可能很多人又有疑问了，既然Hash索引的效率要比B-Tree高很多，为什么大家不都用Hash索引而还要使用B-Tree索引呢?任何事物都是有两面性的，Hash索引也一样，虽然Hash索引效率高，但是Hash索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

1. Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
   哈希索引只支持等值比较查询，包括＝、IN 、<=> (注意<>和＜＝＞是不同的操作）。 也不支持任何范围查询，例如WHERE price > 100。由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。
2. Hash索引无法被用来避免数据的排序操作。 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算;
3. Hash索引不能利用部分索引键查询。
   对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。
4. Hash索引在任何时候都不能避免表扫描。 前面已经知道，Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
5. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。

对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

全文搜索索引

根据名称创建全文索引：alter table customers1 add fulltext index testfulltext(name) with parser ngram;

select * from customers1 where match(name) against(’-化实’,in boolean mode) limit 0,5;

下一篇：mysql 索引选择原则 07

上一篇：mysql索引与innodb结构 05