MYSQL索引理解

MYSQL索引

• 本质

1. 帮助高效查询的一种数据结构
2. 数据存储在磁盘中，每次默认查询一页大小16K

• 二叉搜索树，平衡二叉搜索树（缺点）（父节点至多只有2个子节点）

1. 最坏情况：单边树/高度多，层数多，查询效率很低。（数据量大情况下）
2. 查询很多次，IO次数很多，开销大。

三、B-Tree (多叉，多路平衡树)

   特性：

1. 每个节点包括：关键字索引，数据区，指针。
2. 高度降低，原来只有俩个路，现在多路，可以放更多节点。
3. 每个节点放更多数据指针，水平扩展。

• B+Tree

特性：只有叶子节点放的数据，非叶子节点放的是关键字+指针

优化：

1. 基于索引扫表速度快（数据全部都在叶子节点）
2. 基于索引排序更快（叶子节点数据天然有序）
3. IO吞吐能力更强 （每页放更多）

• 存储引擎

1. Innodb、（支持事务）

磁盘存放结构：frm(表结构)，ibd（索引结构和表数据）

特点：

1. 主键索引，叶子节点数据区：存放主键和行记录。

比如:select age from user where id;查一次就够了

1. 非主键索引，叶子节点数据区：存放非主键索引和主键索引

比如：select age from user where name;(name 为普通索引)

此时就需要查询俩次，第一次通过非主键查询到主键索引，第二次再通过主键索引，查询到行记录。

1. Myisam（不支持事务，适合查询）

磁盘存放结构：Frm(表结构), myi（索引结构），myd（表数据）

特点：主键索引和非主键索引，叶子节点存放的数据，只会存储磁盘地址值。

• 常见面试题：

1. Like ‘dayh%’ 一定会用到索引么？
2. 建立索引的原则

数据离散性尽量高，常见：名字建索引，年龄和性别不适合建索引

1. 为什么MySQL默认使用B+Tree,而不是B-Tree，AVL（二叉树）
2. 为什么不建议写select * from 进行查询？

会触发回表操作，再次查询主键索引树。

以下：情况下不会进行回表操作：

表结构：user

主键索引：id

联合索引：name,age

唯一索引: username

Select name,age from user where name=?

Select id,age from user where name=?

1. 最左匹配原则如何理解，哪些情况下导致索引失效？

B+tree按照从左到右建立索引搜索树，比如（a,b,）先通过a再找到b。范围之后会导致索引失效，><，or

1. 为什么主键ID是递增，和B+Tree的关联？（UUID弊端）

1. 叶子节点数据天然有序，插入只需要追加。
2. UUID 不能保证有序，又是插入操作，并且占字符串空间。

1. 如何理解聚集和非聚集索引、稀疏索引、覆盖索引？

聚集索引（主键索引，稠密索引）：叶子节点包含：主键和行记录

非聚集索引（稀疏索引，辅助索引）：叶子节点包含：索引和主键

覆盖索引：当Sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中 （联合索引）,而不需要进行回表操作。

1. 如何从100条数据从找到其中少了数据（比如100条）？、

考察点：用覆盖索引，减少回表次数，一次直接获取查询的列。

方式：将查询的列建立联合索引，比如查询name,age (name+age)联合索引；利用联合索引最左匹配原则where name like ‘杨%’；

1. 为什么Innodb要求一定建立主键索引？

Innodb不建主键索引，就会建立隐藏索引默认是int型；并且在事务隔离性上，比做update操作，默认就会将行锁由变为表锁。

1. 如何理解回表操作？

第一次索引查询需要获取到主键，第二次通过主键再获取行记录