为什么索引不用 Hash 类型 mysql索引为什么不用hash

MYSQL调优（四）之索引优化

• 1、MYSQL的索引
• 2、索引的基本知识
• 3、哈希索引
• 4、组合索引
• 5、聚簇索引与非聚簇索引
• 6、页分裂与页合并
• 7、覆盖索引
• 8、Join算法
• 9、索引监控

1、MYSQL的索引

mysql的innoDB存储引擎使用的是B+树的结构。为什么不用hash、二叉树或者B树呢？
hash：
hash的缺点：利用hash存储的话需要将所有的数据文件添加到内存，比较浪费内存空间。如果所有的都是等值查询，那么hash确实很快，但是在一般情况下，用的范围查找更多，所以hash就不适合了。

二叉树与红黑树：

二叉树红黑树的缺点：都会因为树的过深而造成IO次数变多。AVL树会进行1-N次旋转，而这种旋转比较浪费时间。插入效率极低，但是查询比较高。

所以有了红黑树。最短子树高度不超过最高子树的2倍。旋转 + 变色。变色为了减少旋转。损失了部分查询效率，提高了插入效率。

B树：

虽然解决了二叉树红黑树的问题，所以节点更多了一些。
B树的特点：

1. 所有键值分布在整棵树中
2. 搜索有可能在非叶子节点结束
3. 每个节点最多拥有m个子树。

1. 
   
   假设data数据为1k，那么由已知mysql一次读16k，那么读16条。三层B树那么读16 x 16 x 16 = 4096条数据。但是公司随便一个mysql都是几万条，b树存在了问题。大部分的磁盘空间被data占用了，所以要进行优化，让非叶子节点磁盘块不存数据，那么用B+树。

B+树：

假设16kb，那么每个节点占用10个字节。那么一层为1600个。那么三层能支持千万级别的数据。

注意：

1. 1.MyISAM是数据和索引分开来存放，INODB是一起存放。所以在innodb中，遍历到最后叶子节点的data是取出完整的数据。但是myisam的话，因为位于两个不同的文件，所以在查找数据的时候，最后放的并不是实际的数据，而是放的实际数据所在的文件地址，多了次IO。 2. innodb是通过B+树对主键创建索引，如果没有创建主键，那么选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键（对用户不可见，在oracle中可以显示，如下）。

2、索引的基本知识

索引的优点：

1. 大大减少了服务器需要扫描的数据量与IO量
2. 帮助服务器避免排序和临时表。提升排序效率
3. 将随机io变成顺序io(磁盘位置连续与磁盘位置不连续，读的效率问题)

索引的用处：

1. 快速查找匹配WHERE子句的行
2. 从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引
3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
4. 当有表连接的时候，从其他表检索行数据
5. 查找特定索引列的min或max值
6. 如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组
7. 在某些情况下，可以优化查询以检索值而无需查询数据行

索引的分类：

• 主键索引 不能为空
• 唯一索引 可以为空
• 普通索引 普通的列作为索引
• 全文索引 text，varchar，char建全文索引。是由倒排索引构建而成。
• 组合索引 比如把name与age组合起来。

面试技术名词：

• 回 表 ：innodb对普通列建立索引时，最后一行存储的并不是整行数据，放的是主键，那么第一次查询会根据name列查找，但是信息并不在该B+树中，所以查找到主键之后，查找主键的B+树。这就是回表。比如select * from emp where name = 1;先找name列的B+树，找到后再找主键B+树。
• 覆盖索引：每次查数据时，发现都会先查到主键值，再从主键B+树找数据，有两次B+树，但是如果查询时，包含了所有的字段，不需要回表操作，那么就是覆盖索引。比如 select id from emp where name = 1;发现主键ID已经有了，不需要回表。
• 最左匹配：建索引时，有可能遇到多个列创建索引，比如包含name与age建立的索引，比如写select * from emp where name = ? and age = ?;此时会先匹配name，再匹配age，但是如果select * from emp where age = ?；相当于把那name跨过去了，那么此时不能用这个索引了。必须先有最左边，再有最右边。否则查不了。这就是最左匹配。可以用age + name建立索引，此时可以匹配到age；或者对age再建立一个索引都可以解决。只有组合索引才有最左匹配。
• 谓词下推：比如select t1.name,t2.name from t1 join t2 on t1.id = t2.id;假设t1表有10条，t2有20条。有两种组织形式，一种为将t1与t2按照id进行关联，整体20列再取name。另外一种，将t1.name t2.name与t1.id与t2.id取出来，再按照id关联。那么第二种方式比较好，这是谓词下推。
• 索引下堆：比如有个组合索引（name，age）。Where name = ‘’ and age = ‘’。第一种方式，在存储引擎先取出所有name等于的值先去出来，再到server层时，再把age过滤。高版本时，第二种方式，在存储引擎对age过滤掉。这就意味着server层读数据时的IO量少了，那么效率高了。

索引匹配方式：

1.全值匹配：全值匹配指的是和索引中的所有列进行匹配

explain select * from staffs where name = ‘July’ and age = ‘23’ and pos = ‘dev’;

2.匹配最左前缀：匹配前面的几列，顺序无所谓但是必须包含。

explain select * from staffs where name = ‘July’ and age = ‘23’;

explain select * from staffs where name = ‘July’;

3.匹配列前缀：可以匹配某一列的值的开头部分。

explain select * from staffs where name like ‘J%’;

explain select * from staffs where name like ‘%y’;

但是如果再加个百分号，就不会用到索引了。

4.***匹配范围值：***可以查找某一个范围的数据

explain select * from staffs where name > ‘Mary’;

5.精确匹配某一列并范围匹配另外一列：可以查询第一列的全部和第二列的部分。

explain select * from staffs where name = ‘July’ and age > 25;

6.只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引。

explain select name,age,pos from staffs where name = ‘July’ and age = 25 and pos = ‘dev’;

这里的using index意思是使用到了索引覆盖。

3、哈希索引

基于哈希表的实现，只有精确匹配索引所有列的查询才有效。
在mysql中，只有memory的存储引擎显式支持哈希索引。
哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快。

哈希索引的限制:

1. 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行.
2. 哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
3. 哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
4. 哈希索引支持等值比较查询，也不支持任何范围查询
5. 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
6. 哈希冲突比较多的话，维护的代价也会很高

4、组合索引

当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要.

案例，建立组合索引a,b,c.不同SQL语句使用索引情况

最后那个，只是用了a。

倒数第二个，如果b使用了范围查询，那么之后的所有索引列都不会进行匹配。

5、聚簇索引与非聚簇索引

InnoDB是聚簇索引，myisam是非聚簇索引。
存储的是地址，再从地址找到数据行。这是myisam的非聚簇索引。

聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起。
优点：

1. 数据访问更快，因为索引和数据保存在同一个树中
2. 可以把相关数据保存在一起
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

1. 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式。如果中间插入数据的话，有可能发生页分裂的情况。
3. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

非聚簇索引：数据文件跟索引文件分开存放

6、页分裂与页合并

存放在磁盘块中的数据，如果该索引的数据大于页的剩余空间大小，而后面一页也满了，那么会发生页分裂的情况，页分裂将那一页拆分为两页。
页合并会将两个页空间满足一页的大小的数据合并起来。这里会涉及拆分的问题，会浪费性能（IO）。

排序的话，order by不太好，比如用should by，先拆分为n多个分区，先保证小分区有序，再把所有的数据合并。

7、覆盖索引

基本介绍
1、如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
2、不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
3、不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引
优势
1、索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量
2、因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
3、一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
4、由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

8、Join算法

Simple Nested-loop算法，嵌套循环。

Index nested-loop 算法

一般用小表当作驱动表，这样匹配的次数会少一些。Block nested-loop 算法

缓存块嵌套循环连接通过一次性缓存多条数据，把参与查询的列缓存到Join Buffer 里，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少了内层循环的次数（遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据）。

当不使用Index Nested-Loop Join的时候，默认使用Block Nested-Loop Join。

9、索引监控

show status like ‘Handler_read%’;

参数解释

Handler_read_first：读取索引第一个条目的次数

Handler_read_key：通过index获取数据的次数

Handler_read_last：读取索引最后一个条目的次数

Handler_read_next：通过索引读取下一条数据的次数

Handler_read_prev：通过索引读取上一条数据的次数

Handler_read_rnd：从固定位置读取数据的次数

Handler_read_rnd_next：从数据节点读取下一条数据的次数