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的表必须要有聚集索引:
- 如果表定义了PK,则PK就是聚集索引;
- 如果表没有定义PK,则第一个非空unique列是聚集索引;
- 否则,InnoDB会创建一个隐藏的row-id作为聚集索引;
聚集索引,也只能够有一个,因为数据行在物理磁盘上只能有一份聚集存储。
聚集索引,也只能够有一个,因为数据行在物理磁盘上只能有一份聚集存储。
InnoDB的普通索引可以有多个,它与聚集索引是不同的:普通索引的叶子节点,存储主键(也不是指针)
对于InnoDB表,这里的启示是:
- 不建议使用较长的列做主键,例如char(64),因为所有的普通索引都会存储主键,会导致普通索引过于庞大;
- 建议使用趋势递增的key做主键,由于数据行与索引一体,这样不至于插入记录时,有大量索引分裂,行记录移动;
仍是上面的例子,只是存储引擎换成InnoDB:
其B+树索引构造如上图:id为PK,行记录和id索引树存储在一起,name为KEY,有一棵name的索引树,叶子存储id
当查询 select * from user where name=‘ls’;
会先通过name辅助索引定位到B+树的叶子节点得到id=5,再通过聚集索引定位到行记录。
innodb回表问题
如粉红色路径,需要扫码两遍索引树:
- 先通过普通索引定位到主键值id=5;
- 在通过聚集索引定位到行记录;
这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。
什么是覆盖索引?
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索引的限制
- Hash索引必须进行二次查找 使用哈市索引两次查找,第一次找到相应的行,第二次读取数据,但是被频繁访问到的行一般会缓存在内存中,这点对数据库性能的影响不大。
- hash索引不能用于外排序hash索引存储的是hash码而不是键值,所以无法用于外排序。
- hash索引不支持部分索引查找也不支持范围查找只能用到等值查询,不能范围和模糊查询。
- 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索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些:
- Hash索引仅仅能满足"=",“IN"和”<=>"查询,不能使用范围查询。
哈希索引只支持等值比较查询,包括=、IN 、<=> (注意<>和<=>是不同的操作)。 也不支持任何范围查询,例如WHERE price > 100。由于Hash索引比较的是进行Hash运算之后的Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的Hash算法处理之后的Hash值的大小关系,并不能保证和Hash运算前完全一样。- Hash索引无法被用来避免数据的排序操作。 由于Hash索引中存放的是经过Hash计算之后的Hash值,而且Hash值的大小关系并不一定和Hash运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;
- Hash索引不能利用部分索引键查询。
对于组合索引,Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值,而不是单独计算Hash值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash索引也无法被利用。- Hash索引在任何时候都不能避免表扫描。 前面已经知道,Hash索引是将索引键通过Hash运算之后,将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中,由于不同索引键存在相同Hash值,所以即使取满足某个Hash键值的数据的记录条数,也无法从Hash索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
- 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