MySQL索引深入剖析

1. MySQL索引深入剖析

1.1. 索引是什么

索引图解
数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从 500 万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。
但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

就像我们从一本 500 页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。那么这本书有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，只要确定内容对应的页码，就能很快地找到我们想要的内容。

1.2. 索引类型

大多数的 MySQL 的索引（主键索引，唯一索引，普通索引，全文索引）都是 B-trees 结构。例外的情况有：在空间数据类型使用 R-trees 结构。存储引擎为 MEMORY 的数据库，也可以支持哈希索引。InnoDB 存储引擎的全文索引使用反向列表结构。

在 InnoDB 里面，索引类型有三种，普通索引、唯一索引（主键索引是特殊的唯一索引）、全文索引。

普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。
唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。
全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text。

创建全文索引：

create table m3 (
 name varchar(50), fulltext index(name)
);

全文索引的使用：

select * from fulltext_test where match(content) against('全文索引' IN NATURAL LANGUAGE MODE);

MyISAM 和 InnoDB 支持全文索引。
这个是索引的三种类型：普通、唯一、全文。

我们说索引是一种数据结构，那么它到底应该选择一种什么数据结构，才能实现数据的高效检索呢？

1.3. 索引存储模型推演

1.3.1. 二分查找

其实这个就是二分查找的一种思想，也叫折半查找，每一次，我们都把候选数据缩小了一半。如果数据已经排过序的话，这种方式效率比较高。
所以第一个，我们可以考虑用有序数组作为索引的数据结构。
有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，可能要挪动大量的数据（改变 index），所以只适合存储静态的数据。
为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高。
所以，有没有可以使用二分查找的链表呢？
为了解决这个问题，BST（Binary Search Tree）也就是我们所说的二叉查找树诞生了。

1.3.2. 二叉查找树（BST Binary Search Tree）

二叉查找树的特点是什么？
左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

二叉查找树既能够实现快速查找，又能够实现快速插入。
但是二叉查找树有一个问题：就是它的查找耗时是和这棵树的深度相关的，在最坏的情况下时间复杂度会退化成O(n)。

1.3.3. 平衡二叉树（AVL Tree）（左旋、右旋）

AVL Trees (Balanced binary search trees)
平衡二叉树的定义：左右子树深度差绝对值不能超过 1。
是什么意思呢？比如左子树的深度是 2，右子树的深度只能是 1 或者 3。
这个时候我们再按顺序插入 1、2、3、4、5、6，一定是这样，不会变成一棵“斜树”。

我们注意看：当我们插入了 1、2 之后，如果按照二叉查找树的定义，3 肯定是要在2 的右边的，这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。
那应该怎么办呢？因为它是右节点下面接一个右节点，右-右型，所以这个时候我们要把 2 提上去，这个操作叫做左旋

同样的，如果我们插入 7、6、5，这个时候会变成左左型，就会发生右旋操作，把 6提上去。

所以为了保持平衡，AVL 树在插入和更新数据的时候执行了一系列的计算和调整的操作。

在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容？
它应该存储三块的内容：

第一个是索引的键值。比如我们在 id 上面创建了一个索引，我在用 where id =1 的条件查询的时候就会找到索引里面的 id 的这个键值。
第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。
第三个，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于 26 的时候，走右边，到下一个树的节点，继续判断。

1.4. 索引使用原则

我们容易有以一个误区，就是在经常使用的查询条件上都建立索引，索引越多越好，那到底是不是这样呢？

1.4.1. 列的离散（sàn）度

第一个叫做列的离散度，我们先来看一下列的离散度的公式： count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。

查看表上的索引，Cardinality [kɑ:dɪ'nælɪtɪ] 代表基数，代表预估的不重复的值的数量。索引的基数与表总行数越接近，列的离散度就越高。

show indexes from test;

如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。
这个给我们的启发是什么？建立索引，要使用离散度（选择度）更高的字段.

1.4.2. 联合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引。单列索引可以看成是特殊的联合索引。比如我们在 user 表上面，给 name 和 phone 建立了一个联合索引。

ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）。

从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候， phone 才是有序的。

这个时候我们使用 where name= '青山' and phone = '136xx '去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较 phone。但是如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

1.4.3. 覆盖索引

回表：
非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
例如：select * from user_innodb where name = '青山';

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。
我们先来创建一个联合索引：

-- 创建联合索引
ALTER TABLE user_innodb DROP INDEX comixd_name_phone;
ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);

这三个查询语句都用到了覆盖索引：

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
EXPLAIN SELECT name FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
EXPLAIN SELECT phone FROM user_innodb WHERE name= '青山' AND phone

Extra 里面值为“Using index”代表使用了覆盖索引。

select * ，用不到覆盖索引。
很明显，因为覆盖索引减少了 IO 次数，减少了数据的访问量，可以大大地提升查询效率。

1.4.4. 前缀索引

当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可
以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引：

create table shop(address varchar(120) not null);
alter table shop add key (address(12));

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的，截取得少了，重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？
先看一下字段在全部数据中的选择度：

select count(distinct address) / count(*) from shop;

1.4.5. 索引条件下推（ICP）

“索引条件下推”，称为 Index Condition Pushdown (ICP)，这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”，注意我们这里特意强调了“一个”，这是因为这样的索引优化不是用于多表连接而是用于单表扫描，确切地说，是单表利用索引进行扫描以获取数据的一种方式。

1.4.5.1. 索引条件下推”的目的

用ySQL官方手册描述：

The goal of ICP is to reduce the number of full-record reads and thereby reduce IO operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce IO.

这句官方描述，一是说明减少完整记录（一条完整元组）读取的个数；二是说明对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚集索引有效。

使用场景：

1 ICP只能用于辅助索引，不能用于聚集索引。
2 ICP只用于单表，不是多表连接是的连接条件部分（如开篇强调）
如果表访问的类型为：
3 EQ_REF/REF_OR_NULL/REF/SYSTEM/CONST: 可以使用ICP
4 range：如果不是“index tree only（只读索引）”，则有机会使用ICP
5 ALL/FT/INDEX_MERGE/INDEX_SCAN:  不可以使用ICP

再来看这么一张表，在 last_name 和 first_name 上面创建联合索引。

drop table employees;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL, `birth_date` date NULL, `first_name` varchar(14)NOT NULL,
`last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NULL, PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
alter table employees add index idx_lastname_firstname(last_name,first_name);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (1, NULL, '698', 'liu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (2, NULL, 'd99', 'zheng', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (3, NULL, 'e08', 'huang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (4, NULL, '59d', 'lu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (5, NULL, '0dc', 'yu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (6, NULL, '989', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (7, NULL, 'e38', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (8, NULL, '0zi', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (9, NULL, 'dc9', 'xie', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10, NULL, '5ba', 'zhou', 'F', NULL);

关闭 ICP:

set optimizer_switch='index_condition_pushdown=off';

查看参数：

show variables like 'optimizer_switch';

现在我们要查询所有姓 wang，并且名字最后一个字是 zi 的员工，比如王胖子，王瘦子。查询的 SQL：

select * from employees where last_name='wang' and first_name LIKE '%zi' ;

这条 SQL 有两种执行方式：

1、根据联合索引查出所有姓 wang 的二级索引数据，然后回表，到主键索引上查询全部符合条件的数据（3 条数据）。然后返回给 Server 层，在 Server 层过滤出名字以 zi 结尾的员工。

2、根据联合索引查出所有姓 wang 的二级索引数据（3 个索引），然后从二级索引中筛选出 first_name 以 zi 结尾的索引（1 个索引），然后再回表，到主键索引上查询全部符合条件的数据（1 条数据），返回给 Server 层。

很明显，第二种方式到主键索引上查询的数据更少。
注意，索引的比较是在存储引擎进行的，数据记录的比较，是在 Server 层进行的。而当 first_name 的条件不能用于索引过滤时，Server 层不会把 first_name 的条件传递给存储引擎，所以读取了两条没有必要的记录。
这时候，如果满足 last_name='wang'的记录有 100000 条，就会有 99999 条没有必要读取的记录。
执行以下 SQL，Using where:

explain select * from employees where last_name='wang' and first_name LIKE '%zi' ;

Using Where 代表从存储引擎取回的数据不全部满足条件，需要在 Server 层过滤。先用 last_name 条件进行索引范围扫描，读取数据表记录，然后进行比较，检查是否符合 first_name LIKE '%zi' 的条件。此时 3条中只有 1 条符合条件。

开启 ICP:

set optimizer_switch='index_condition_pushdown=on';

此时的执行计划，Using index condition：

把 first_name LIKE '%zi'下推给存储引擎后，只会从数据表读取所需的 1 条记录。索引条件下推（Index Condition Pushdown），5.6 以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

1.5. 聚簇索引和非聚簇索引

MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类，理解聚集索引和非聚集索引可通过对比汉语字典的索引。（拼音索引和笔画索引）

1.5.1. 聚簇索引

eg:主键索引

1.5.1.1. 基本概念

簇索引并不是一种单独的索引类型，而是一种数据存储方式。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。
因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

InnoDB通过主键聚集数据，
如果没有定义主键，InnoDB会选择一个唯一的的非空索引代替。
如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚集的数据有一些重要的优点：
数据访问更快，聚簇索引将索引和数据保存在同一个B-Tree中，因此从簇聚索引中获取数据通常比在非聚簇索引中查找要快。

同时，簇聚索引也有一些缺点：
更新簇聚索引列的代价很高，因为会强制InnoDB将每个被更新的列移动到新的位置；（可能有页分裂问题，也就是树的变动）

特点：
索引中键值的逻辑顺序决定了表中相应行的物理顺序。（数据就存储在索引的叶子节点上）
聚集索引确定表中数据的物理顺序。

1.5.1.2. 使用场景：

聚集索引对于那些经常要搜索范围值的列特别有效。
用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。

如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

以上是innodb的b+tree索引结构

我们知道b+tree是从b-tree演变而来，一棵m阶的B-Tree有如下特性：

1、每个结点最多m个子结点。
2、除了根结点和叶子结点外，每个结点最少有m/2（向上取整）个子结点。
3、如果根结点不是叶子结点，那根结点至少包含两个子结点。
4、所有的叶子结点都位于同一层。
5、每个结点都包含k个元素（关键字），这里m/2≤k<m，这里m/2向下取整。
6、每个节点中的元素（关键字）从小到大排列。
7、每个元素（关键字）字左结点的值，都小于或等于该元素（关键字）。右结点的值都大于或等于该元素（关键字）。

b+tree的特点是：

1、所有的非叶子节点只存储关键字信息。
2、所有卫星数据（具体数据）都存在叶子结点中。
3、所有的叶子结点中包含了全部元素的信息。
4、所有叶子节点之间都有一个链指针。

我们发现，b+trre有以下特性：

对一个范围内的查询特别有效快速（通过叶子的链指针）；
对具体的key值查询仅仅比b-tree低效一点（因为要到叶子一级），但也可以忽略；

1.5.2. 非聚集索引

eg：大部分非主键索引

1.5.2.1. 基本概念

索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。（数据没有直接存储在索引的叶子节点上，索引查询数据需要回表）
其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

非聚集索引的存储结构与前面是一样的，不同的是在叶子结点的数据部分存的不再是具体的数据，而数据的聚集索引的key。所以通过非聚集索引查找的过程是先找到该索引key对应的聚集索引的key，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！

1.5.2.2. 如何解决非聚集索引的二次查询问题

1.复合索引（覆盖索引）
建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句

select col1, col2 from t1 where col1 = '213';

要注意使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

1.5.3. 聚集索引或非聚集索引使用场景

目前mysql中就是将自增Id强制设定为主键索引，这是为了b+tree和分页。

mysql中每次新增数据，都是将一个页写满，然后新创建一个页继续写，这里其实是有个隐含条件的，那就是主键自增！主键自增写入时新插入的数据不会影响到原有页，插入效率高！且页的利用率高！但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率！降低页的利用率！

这也是为什么在innodb中建议设置主键自增的原因！
这棵树的非叶子结点上存的都是主键，那如果一个表没有主键会怎么样？在innodb中，如果一个表没有主键，那默认会找建了唯一索引的列，如果也没有，则会生成一个隐形的字段作为主键！

1.5.4. InnoDB和MyISAM的数据分布对比

1.6. 索引的创建与使用

1.6.1. 索引的创建

1、在用于 where 判断 order 排序和 join 的（on）字段上创建索引
2、索引的个数不要过多。——浪费空间，更新变慢。
3、区分度低的字段，例如性别，不要建索引。 ——离散度太低，导致扫描行数过多。
4、频繁更新的值，不要作为主键或者索引。 ——页分裂
5、组合索引把散列性高（区分度高）的值放在前面。
6、创建复合索引，而不是修改单列索引。
7、过长的字段，怎么建立索引？
8、为什么不建议用无序的值（例如身份证、UUID ）作为索引？

1.6.2. 什么时候用不到索引？

1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）：

explain SELECT * FROM `t2` where id+1 = 4;

2、字符串不加引号，出现隐式转换

explain SELECT * FROM `user_innodb` where name = 136;
explain SELECT * FROM `user_innodb` where name = '136';

3、like 条件中前面带%,where 条件中 like abc%，like %2673%，like %888 都用不到索引吗？为什么？
但是%在后面却可以使用到索引。（like abc%）

explain select *from user_innodb where name like 'wang%';
explain select *from user_innodb where name like '%wang';

过滤的开销太大，所以无法使用索引。这个时候可以用全文索引。
4、负向查询
NOT LIKE 不能：

explain select *from employees where last_name not like 'wang';

!= （<>）和 NOT IN 在某些情况下可以：

explain select *from employees where emp_no not in (1)
explain select *from employees where emp_no <> 1

注意一个 SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。
其实，用不用索引，最终都是优化器说了算。

优化器是基于什么的优化器？基于 cost 开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer），也不是基于语义。

怎么样开销小就怎么来。

1.7. 索引创建原则：

1、对查询频次较高，且数据量比较大的表建立索引
2、索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的组合。
3、使用唯一索引，区分度越高，使用索引的效率越高
4、索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然就很高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，mysql也会有一种选择的困难，尽管最终会找到一个可用的索引，但无疑提高了选择的代价。
5、使用短索引，索引创建之后也是使用硬盘来存储的，因此，提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效提升mysql访问索引的I/O效率。
6、利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询sql可以利用组合索引来提升查询效率。

作者：javacoo
链接：https://www.jianshu.com/p/ff195927b8d5
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

posted @ 2023-06-21 15:03 数据库小白（专注）阅读(24) 评论(0) 收藏举报

刷新页面返回顶部

数据库小白（专注）