MySQL 索引

1、为什么是B+ tree

1.1、为啥不选择二叉树(Binary Search Tree)

二叉树的特点，小的数在左，大的在右，存在如下一种情况，会导致每次查找只能依次遍历整棵树；

1.2、为啥不选择红黑树(Red Black Tree)

如上图所示，采用红黑树的两大弊端：

1. 数据多了之后，树的高度依旧会很高
2. 每个节点只存储一个数据，节点不连续，无法使用局部性原理（磁盘往内存 Tree

1.3、为啥不选择B tree

如上图所示，可以说是解决了上诉红黑树提及的两大缺陷，树的高度有所降低，树的节点可以存 储多个数据，能够满足局部性原理；但是会面临一个新的缺陷，例如范围查询，select ... from ... where ... between A and B，由上图可以发现，B tree存储的数据只是部分连续，大多数还是散列再各个节点种且互相独立，这样查询必然需要多次检索树，例如5->6->7，因为再实际磁盘中，极可能数据散列分布，这种查询方式势必会带来多次随机IO的开销，造成不必要的性能开销。

1.4、为啥选择B+ Tree

如上图所示，B+ tree的叶子节点之间均有单指针相连接，这就解决上诉范围查询 B tree的问题，只要找到最小的边界值，后面就可以随着叶子节点之间的指针，一路向前。

2、索引具体是个啥

2.1、索引的分类

• 数据结构层次
  • HASH 索引
  • B+ tree 索引
  • R tree 索引
• 功能逻辑层次
  • 主键索引
  • 唯一索引
  • 普通索引
  • 全文索引
  • 空间索引
• 存储方式层次
  • 聚簇索引
  • 非聚簇索引

2.2、索引的存储示意图

create table student(
    id int not null,
    name varchar(10) not null,
    country varchar(10) not null,
    primary key(id),
    key idx_name(name)
);

id	name	country
1	b白起	秦
2	d邓艾	魏
3	f法正	蜀
4	g关羽	蜀
5	h黄巢	唐

索引示意图如上，MySQL innodb存储引擎中，具体的数据存储在聚簇索引的叶子节点中（如果表字段中没有主键字段或者唯一索引，那么MySQL会自动生成一个row_id来作为“隐藏主键”使用）；普通索引的叶子节点中除了存放索引字段，还会存放对应聚簇索引的主键ID。

索引中数据节点均按从小到大依次排序

3、增删改查如何使用索引

3.1、select

3.1.1、按聚簇索引查询

聚簇索引查询比较简单，从索引树依次检索，直至找到符合条件的记录；如果时范围查询，先定位到最小的记录，然后沿着叶子节点之间的指针，依次往后找，直至找齐符合条件的所有记录。因为聚簇索引同时存储了完整的记录字段，所以此时也就获得了所有的字段数据。

3.1.2、按非聚簇索引查询

索引的检索过程与聚簇索引类似，但是因为非聚簇索引只存储索引字段，并没有其他字段，所以如果要想获取其他字段内容，需要依据索引中锁包含的主键，去聚簇索引中在检索一次，以期获得其他的字段内容，这个过程也称之为“回表”；

3.2、update

update student set name = 'c陈庆之' where id = 5;

1. 根据主键查询到聚簇索引中的记录
2. 依据记录中name及id到idx_name中找到对应索引记录
3. 将聚簇索引中的name修改为'c陈庆之'
4. 删除idx_name中的原索引记录
5. 新增idx_name （'c陈庆之'， 5）索引记录

聚簇索引update操作具体有三类场景

1. 主键值未被修改，且更新列内容存储空间大小与修改前一致，再原位置直接修改
2. 主键值未被修改，但是更新列内容存储空间大小与修改前不一致，则先直接删除记录（非delete mark）再新增一条记录
3. 主键值被修改，先delete原纪录，再insert一条新记录

3.3、insert

新数据插入，对于MySQL而言，需要做两件事情

1. 将新记录加入聚簇索引
2. 将新记录涉及的索引字段及主键ID分别插入各自对应的非聚簇索引中

3.4、delete

delete from student where id = 3;
删除数据时，与新增操作类似

1. 先将记录中涉及的非聚簇索引删除（做delete mark）
2. 再将聚簇索引中记录删除（同样delete mark）

4、还需要知道的索引知识

4.1、索引下推

create table t(
    id int not null auto_increment,
    name varchar(10) not null,
    age int not null,
    tel varchar(11) not null,
    addr varchar(10) not null,
    primary key(id),
    key idx_name_age(name, age)
);
insert into t(a, b) values
('张三', 10, '123', '北京'),
('李四', 12, '456', '天津'),
('王五', 10, '789', '深圳'),
('张猛', 16, '321', '上海');

id	name	age	tel	addr
1	张三	10	123	北京
2	李四	12	456	天津
3	王五	10	789	深圳
4	张猛	16	321	上海

select * from t where name like '张%' and age = 10; 通过执行计划可以发现该查询走的是idx_name_age, 且extra= use index condition;说明MySQL使用索引下推（index condition pushdown ICP）优化。
索引下推就是将部分查询条件的判断下推到引擎层处理，具体而言如下图所示 索引下推是MySQL5.6引入的新特性，如果是之前无索引下推，则如下图处理 通过上诉两种方式，可以发现所谓的索引下推，也比较好理解，索引中既然都包含了查询条件的字段了，那么在存储引擎这层直接判断完了就行了，没必要在交给server层再去判断，最主要的是，减少了不必要的回表次数，降低了IO提升性能。

4.2、索引覆盖

索引覆盖，也比较好理解，如果索引列中已经包含了返回字段，那么就没有必要再回表，到聚簇索引中取出完整的记录。例如select name, age from t where name = '张三';(借用4.1 t表)，idx_name_age, 索引列中已经包含了name、age、id，完全可以满足select语句的返回字段要求，无需在做回表操作。

4.3、索引合并

create table t1(
    id int not null auto_increment,
    a int not null,
    b int not null,
    c int not null,
    primary key(id),
    key idx_a(a),
    key idx_b(b)
);

select * from t1 where a = 1 and b = 1;

graph TD
A(idx_a查询) --> B(id1, id2, id3)
C(idx_b查询) --> D(id3, id4, id5)
D-->|合并|E(id3)
B-->|合并|E
E-->F(根据id3回表取出最终记录)

如上图示意图所示，索引合并的目的也是为了较少回表的次数;

4.4、explain

explain select\update\delete …… from table where …… 通过查看执行计划来查看SQL是否走索引；择出两个需要重要关注的字段

4.4.1、type字段

type	含义
all	全表扫描
index	全索引扫描，遍历了整个索引树
range	基于索引字段进行范围查询
index_subquery	与unique_subquery类似，区别在于子查询使用普通索引而非主键或唯一索引
unique_subquery	一般出现在in子查询语句种，优化器研判可以将in查询转换为exist查询，且该子查询可以使用到主键或者唯一索引时
index_merge	使用了索引合并
ref_or_null	进行普通二级索引等值匹配，同时索引字段也可以是null值
fulltext	全文索引
ref	通过普通索引做等值匹配
eq_ref	连接查询时，被驱动表时通过主键或者唯一索引等值匹配的方式访问
const	根据主键或者唯一索引做等值匹配
system	表中只有一行数据,const的特例
null	表中没有数据，无需索引任何数据检索，直接返回结果

常见的type性能排序：const -> eq_ref -> ref -> index_merge -> range -> index -> all，$\color{red}日常编写sql，最好下限是ref，最差不能超过range, 千万不能出现index,all$

4.4.2、extra字段

extra	含义
Using index	使用了索引覆盖，不需要额外回表
Using where	1、全表扫描查询某张表且查询条件种有针对于该表的检索条件 2、使用了索引，但是仅凭借索引不够，还需要回表判断其他检索字段
Using temporary	顾名思义，光凭索引不能解决问题或者无法使用到索引，MySQL使用了临时表，例如select distinct 非索引列 from xxx
Using filesort	order by 无法使用索引排序，从而在内存或者磁盘排序
Using where;Using index
Using index condition	使用了ICP索引下推
Using join buffer	连接查询种，被驱动表不能有效地利用索引，MySQL会使用join buffer来提高效率
Using intersect\union\sort_union(……)	一般使用index merge时会出现

常见的extra性能排序：Using index -> Using index condition -> Using where -> Using where;Using index -> Using join buffer -> Using temporary

5、表连接的本质

create table t1(
    id int not null auto_increment,
    a int not null,
    b int not null,
    primary key(id)
);
insert into t1(a, b) values(1, 1), (2, 2);
create table t2(
    id int not null auto_increment,
    a int not null,
    c int not null,
    primary key(id)
);
insert into t2(a, c) values(1, 1), (1, 2), (2, 1);

t1

id	a	b
1	1	1	1
2	2	2	2

t2

id	a	c
1	1	1
2	1	2
3	2	1

select * from t1, t2 where t1.a = t2.a and t1.a < 3 and t2.c = 1; 假设t1是驱动表，连接过程如上图所示

1. 先检索驱动表，查询出符合的记录
2. 拿着上一步驱动表过滤出的结果再分别去被驱动表中查找符合的记录
3. 最后就得出符合查询条件的所有结果（如果还有第二张驱动表，将结果作为新的驱动表，重复上诉步骤） 上诉过程也叫做“嵌套循环连接”

什么是基于块的嵌套循环连接（Block Nested-Loop Join）
上诉嵌套循环连接的过程中，可以发现被驱动表会被访问多次，MySQL设计了一个join buffer的机制，即将多条驱动表中的记录结果事先缓存起来，等到扫描被驱动表时，从被驱动表中取出一条记录后，与join buffer中的驱动表记录一起比较，过滤出符合条件的记录。这样可以大大减少扫描被驱动表的次数，提高性能。

6、count(*)、count(1)、count(id)、count(列名)哪个快

count(expr) expr指定表达式不为null的记录有多少条

按照上诉语法，count(*)（$\color{red}{count函数，MySQL将*当作0处理}$）、count(1)，可以直观地看出来，他两其实是一码事；

create table t11(
    id int not null auto_increment,
    name varchar(10) not null,
    country varchar(10) not null,
    primary key(id),
    key idx_name(name)
);

select count(*) from t11 通过上诉的执行计划，可以发现count(*), 走的是idx_name索引。具体MySQL执行步骤如下：

1. server层创建一个变量count=0；
2. server层向引擎层innodb要第一条记录；
3. innodb沿着索引idx_name找到第一条记录，直接返回给server层（因为只是统计记录数，不需要回表）；
4. select count(*) 等同于 select count(0) "0!=null", 符合条件，故此count+1;
5. 继续向innodb要下一条记录，如此往复直至到idx_name的最后一条记录，告知server层结束了；
6. server最后将count结果告知客户端。 根据innodb索引的特点，二级索引与主键索引的记录数是对等的，所以count(id)，不一定非要选择聚簇索引进行遍历统计，选择任何一个普通二级索引统计的值均相同，所以count(id) ≈ count()，MySQL会自主选择代价最小最为经济的一个索引进行遍历统计数量; 然而count(列名)会指定使用含该列的索引进行检索遍历，如果该列名无索引包含，则只能扫描全表。
   结论： select count()、select count(1)、select count(id)，基本是一回事，然后select count(列名)取决于具体的索引，与前面几种写法相比，可能相近，可能更慢。