04 索引
索引：为了提高数据查询的效率，就像书的目录一样。
索引的常见模型
哈希表
图中，User2 和 User4 根据身份证号算出来的值都是 N，后面还跟了一个链表。假设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2。
需要注意的是，图中四个 ID_card_n 的值并不是递增的，好处是增加新的 User 时速度会很快，只需要往后追加。缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。****
哈希表结构适用于只有等值查询的场景。
有序数组
有序数组在等值查询和范围查询场景中的性能就都非常优秀。
要查 ID_card_n2 对应的名字，用二分法就可以快速得到，时间复杂度是 O(log(N))。同时很显然，这个索引结构也支持范围查询。
仅仅看查询效率，有序数组就是最好的数据结构。但是，在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
所以，有序数组索引只适用于静态存储引擎。
N 叉树
二叉搜索树的特点是：父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。
如果你要查 ID_card_n2 的话，图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。时间复杂度是 O(log(N))。
为了维持 O(log(N)) 的查询复杂度，需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。
二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。
InnoDB 的索引模型
在 MySQL 中，索引是在存储引擎层实现的。
InnoDB 使用了 B+ 树索引模型，每一个索引在 InnoDB 里面对应一棵 B+ 树。
一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。
表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。
根据叶子节点的内容，索引类型分为主键索引和非主键索引。
- 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
- 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
基于主键索引和普通索引的查询区别？：
- select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
- select * from T where k=5，即普通索引查询方式，需要先搜索 k 索引树，得到 ID 值为 500，再到 ID 索引树搜索一次。这个过程称为回表。
基于非主键索引的查询需要多扫描一棵索引树，因此在应用中应该尽量使用主键查询。
索引维护
- 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
- 有业务逻辑的字段做主键，则往往不容易保证有序插入，写数据成本相对较高。
从性能和存储空间方面考量，自增主键往往是更合理的选择。
同时适合用业务字段直接做主键的场景是：
- 只有一个索引；
- 该索引必须是唯一索引。
由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
这时候优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。
问：对于 InnoDB 表 T，如果你要重建索引 k，你的两个 SQL 语句可以这么写：
alter table T drop index k;
alter table T add index(k);
如果你要重建主键索引，也可以这么写：
alter table T drop primary key;
alter table T add primary key(id);
对于上面这两个重建索引的作法，说出你的理解。
答：为什么要重建索引。索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，重建索引使得页面的利用率最高，也就是索引更紧凑、更省空间。
重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。
不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。
在下面这个表 T 中，如果执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
mysql> 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'),(700,7,'gg');
SQL 查询语句的执行流程：
- 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
- 再到 ID 索引树查到 ID=300 对应的 R3；
- 在 k 索引树取下一个值 k=5，取得 ID=500；
- 再回到 ID 索引树查到 ID=500 对应的 R4；
- 在 k 索引树取下一个值 k=6，不满足条件，循环结束。
回到主键索引树搜索的过程，称为回表。查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。
由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？
覆盖索引
如果执行的语句是 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 PRIMARY KEY,
id_card VARCHAR(32),
name VARCHAR(32),
age INT,
INDEX idx_id_card_name (id_card, name)
);
上述语句创建了一个名为 idx_id_card_name 的联合索引，包含了 id_card 和 name 两个字段。
现在，假设有一个高频请求，要根据市民的身份证号查询他的姓名：
-- 使用覆盖索引，不需要回表查整行记录
EXPLAIN SELECT name FROM tuser WHERE id_card = '123456789';
由于我们在 idx_id_card_name 中包含了 name 字段，这个查询可以直接使用覆盖索引，不需要回表查整行记录。这样可以减少磁盘IO和提高查询效率，尤其在高频请求的场景下。
最左前缀原则
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
（name，age）这个联合索引来分析
只要满足最左前缀，就能利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
在建立联合索引的时候，如何安排字段顺序：
评估标准：索引的复用能力。
因为支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。
如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，不得不维护另外一个索引，也就是说需要同时维护 (a,b)、(b) 这两个索引。
这时候，考虑的原则就是空间了。比如name 字段是比 age 字段大的 ，则建议创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。
索引下推
在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
无索引下推执行流程
索引下推执行流程
虚线代表回表一次，第1张图回表4次，第2张图InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过，回表了2次。
问：
实际上主键索引也是可以使用多个字段的。一个表结构定义类似这样的：
CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;
公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。
既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？
同事告诉他，是因为他们的业务里面有这样的两种语句：
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;
这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？
答：
???
