MySql实战45讲【索引】

04 | 深入浅出索引（上）

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样.

实现索引的方式分别有 哈希表、有序数组和搜索树

哈希表
哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。

User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链
表。假设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将
ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。

如果你现在要找身份证号在 [ID_card_X, ID_card_Y] 这个区间的所有用
户，就必须全部扫描一遍了。所以，哈希表这种结构适用于只有等值查询的场景

有序数组

这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候，如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是O(log(N))

这个索引结构支持范围查询。你要查身份证号在 [ID_card_X, ID_card_Y] 区
间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于
ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环。

但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高，所以，有序数组索引只适用于静态存储引擎

二叉搜索树

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF ->User2 这个路径得到。这个时间复杂度是 O(log(N))。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块
的大小

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。


基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量
使用主键查询

身份证号做主键，还是用自增字段做主键呢?
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树,

05 | 深入浅出索引（下）

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT ''
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff')

如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

由于查询结果所需要的数据只在主键索引上有，所以不得不回表

覆盖索引
如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的
值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是
说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引.

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用
的性能优化手段。

需要注意的是，在引擎内部使用覆盖索引在索引 k 上其实读了三个记录，R3~R5（对应的
索引 k 上的记录项），但是对于 MySQL 的 Server 层来说，它就是找引擎拿到了两条记
录，因此 MySQL 认为扫描行数是 2

CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义
了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行
时间

最左前缀原则
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录.

索引下推
MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过
程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数.

索引区分度
在说上述问题之前,我们先来看看另一个概念,就是区分度。
区分度: 指字段在数据库中的不重复比
区分度在新建索引时有着非常重要的参考价值,在MySQL中,区分度的计算规则如下:
字段去重后的总数与全表总记录数的商。
例如:

select count(distinct(name))/count(*) from t_base_user;

结果如下:

其中区分度最大值为1.000,最小为0.0000,区分度的值越大,也就是数据不重复率越大，新建索引效果也越好,在主键以及唯一键上面的区分度是最高的,为1.0000。在状态,性别等字段上面的区分度值是最小的。 (这个就要看数据量了,如果只有几条数据,这时区分度还挺高的,如果数据量多,区分度基本为0.0000。也就是在这些字段上添加索引后,效果也不佳的原因。)

值得注意的是: 如果表中没有任何记录时,计算区分度的结果是为空值，其他情况下,区分度值均分布在0.0000-1.0000之间。

如何建索引
(一) : 区分度
个人强烈建议, 建索引时,一定要先计算该字段的区分度,原因如下:

单列索引
可以查看该字段的区分度,根据区分度的大小,也能大概知道在该字段上的新建索引是否有效，以及效果如何。区分度越大,索引效果越明显。

多列索引(联合索引)
多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效,例如:

select * from t_base_user where name="" and status=1;

像上述语句,如果建联合索引的话,就应该是:

alter table t_base_user add index idx_name_status(name,status);

而不是:

alter table t_base_user add index idx_status_name(status,name)；

(二) 最左前缀匹配原则
MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如

select * from t_base_user where type="10" and created_at<"2017-11-03" and status=1, (该语句仅作为演示)

在上述语句中,status就不会走索引,因为遇到<时,MySQL已经停止匹配,此时走的索引为:(type,created_at),其先后顺序是可以调整的,而走不到status索引,此时需要修改语句为:

select * from t_base_user where type=10 and status=1 and created_at<"2017-11-03"

即可走status索引。

(三) 函数运算
不要在索引列上,进行函数运算,否则索引会失效。因为b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

(四) 扩展优先
扩展优先,不要新建索引,尽量在已有索引中修改。如下:

select * from t_base_user where name="andyqian" and email="andytohome"

在表t_base_user表中已经存在idx_name索引,如果需要加入idx_name_email的索引,应该是修改idx_name索引,而不是新建一个索引。

参考

mysql索引