SQL索引学习笔记

SQL索引本质

索引（index）是帮助MySQL高效获取数据的数据结构。

在RDBMS中，索引存储在硬盘中。

Hash - 哈希索引

AHI-adaptive hash index - 自适应哈希索引

对字段计算hash值，将hash值和行指针对应存在hash表中。

为什么innodb引擎不支持手动使用hash索引：

1. 存在内存中，数据量大，内存容易爆
2. hash查询行记录，不适合指定查某几列
3. 按hash值排序，对范围查找没有帮助，如果还有order，需要二次排序
4. 哈希碰撞，计算出的hash值相同。一个hash值指向多行指针，需要逐一查找

memory引擎可以手动使用hash索引，因为memory引擎使用内存。

一般中间表使用memory引擎。

B+Tree 索引

二叉搜索树

一个节点最多分出两个节点，子节点满足左小右大。

缺点：极限情况，假设1为根节点，往下节点依次为2、3、4、5等等，要查询5需要5次。

平衡二叉树

一个节点最多分出两个节点，两个子节点的深度差绝对值不能大于1。

1. IO次数过多，随着数据变多，树的深度变大。
2. 目标数据过少，造成很大的IO资源浪费。

B-Tree 多路搜索树、多叉平衡查找树

1. 多关键数
2. 多路数
3. 每条路深度一样、绝对平衡

每个节点，最多关键数的个数 = 路数 - 1。

假设节点为：1，5，10

则分路为[负无穷,1),[1, 5),[5,10),[10,正无穷)

每个节点都有数据区。

B+Tree

节点关键数和路数1：1。

假设节点为：1，5，10

则分路为[1, 5),[5, 10),[10, 正无穷)

数据区存放在叶子节点，所以所有查询都要查询到叶节点，虽然部分查询比B-Tree更耗时，但是稳定。

优点：

1. 基于索引扫表能力强
2. 基于索引排序能力强
3. 查询所有数据都要到叶子节点，时间稳定。
4. 取消数据区内容，读写能力更强

聚集索引

myisam 引擎

myi 文件，存索引，叶节点存地址

myd 文件，存地址对应的数据

innodb引擎

ibd文件：

• 主键索引：叶节点挂载行记录

• 非主键索引：叶节点保存主键值

innodb引擎中，只有主键是聚集索引，其他索引都是非聚集索引。

主键索引

如果不手动建立主键索引，会自动用隐藏列_rowid作为主键索引，占用6byte，而int只占用4byte。

并且行锁会升级为表锁

优秀的索引

列的离散性

count distinct col : count col

非重复列数：所有列数

列重复越少越好。

联合索引

最左匹配原则：联合索引的任何前缀都可启用联合索引

create index idx_name_ph_age(name,phone,age)

name

name, phone

name, phone, age

以上三个均可启用联合索引，而

phone

phone, age

phone, name ,age

等不可启用联合索引

下述两个查询是否启用联合查询，使用了几个：

1. where name=‘peter’ and phone > 1333333 and age = 18

   使用了联合索引，1个。“范围之后全失效”

   因为phone>1333333范围过大，而age=18重复率高。

2. where phone = 1333333 and name = ‘peter’

   使用联合索引2个，mysql优化器，会把选择性最后的列放在where的最左边。

连接器：管理连接，权限验证

分析器：词法分析，语法分析

优化器：执行计划生成，索引选择

执行器：操作引擎，返回结果

覆盖索引

不触发回表操作。

通过索引项的信息可以直接返回锁查询的列，则该索引成为查询SQL的覆盖索引。

尽量使用覆盖索引，因为能加快查询速度。

回表操作：

根据前面提到的innodb引擎

• 主键索引：叶节点挂载行记录
• 非主键索引：叶节点保存主键值

根据非主键索引查出主键后，再根据主键索引查询行记录的操作为回表操作。

假设表有：

1. 主键索引
2. 非主键索引 name
3. 联合索引 name, phone

则：

select * from user where name = 'mervyn'触发回表操作，通过非主键索引name查询出id后， 又根据主键索引查出行数据。

select id, name from user where name = 'mervyn'没有触发回表操作，通过非主键索引name可以直接查询出id和name。

select id, phone, name from user where name = 'mervyn'没有触发回表操作，联合索引

三星索引

1. where后匹配的索引关键字列越多越好，扫描的数据越精确，越少越好。
2. 避免再次排序。
3. 尽可能的应用覆盖索引。减少回表操作。