数据库的索引数据结构

索引结构

二叉树

image.png

缺点：如果顺序插入，树的高度会很大
image.png
会变成一条链

红黑树（自动平衡二叉树）

image.png
缺点：树的高度还是有点大

B树

image.png
特点：
•叶节点具有相同的深度，叶节点的指针为空
•所有索引元素不重复
•节点中的数据索引从左到右递增排列

B+树（MySQL所用）

image.png
只有叶子节点存数据，上面的索引为冗余索引

Hash结构

image.png

存储引擎

MyISAM

image.png
叶子节点存储的是数据的地址

InnoDB

主键索引（聚集索引）
image.png

非主键索引（非聚集索引）
image.png

联合索引
image.png
最左前缀原理，搜索条件最左边的都要包括，才能使用索引。
叶子节点存储的是数据。表的所有数据

索引的分类及使用

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引_primary key

  • 唯一的标识，主键可以重复，只能有一个列作为主键 ```sql 主键索引: 1 | — 创建的时候添加: 

    1.  Create table t1( Id int(3) primary key,)
    3.  Create table t1(
    4.  Id int(3),
    5.  Primary key(id)
    6.   )

2 | —表创建完了之后添加 Alter table 表名 add primary key(id)

3 | —删除主键索引: Alter table 表名 drop primary key;

  3. <a name="TUZDr"></a>
  4. #### 唯一索引_unique key
  6. - 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
  7. ```sql
  8. 唯一索引:
  9. 1 | -- 创建的时候添加:  
  10.        Create table t1(Id int(3) unique,)
  12.        Create table t1(
  13.        Id int(3),
  14.        Unique key uni_name (id)
  15.         )
  17. 2 | 表创建好之后添加唯一索引:
  18.     alter table s1 add unique key  u_name(id);
  20. 3 | 删除唯一索引:
  21.     Alter table s1 drop index u_name;

常规索引_key/index

  • 默认的，**index**，**key**关键词来设置 ```sql 普通索引: 1 | — 创建的时候添加: 
    1.  Create table t1(
    2.  Id int,
    3.  Index index_name(id)
    4.  )

2 | —表创建好之后添加普通索引:
Alter table s1 add index index_name(id); Create index index_name on s1(id);

3 | —删除普通索引: Alter table s1 drop index u_name; DROP INDEX 索引名 ON 表名字;

  3. <a name="TtSNe"></a>
  4. #### 全文索引_fulltext
  6. - 在特定的数据库引擎下才有，MYISAM
  7. - 快速定位数据
  9. <a name="MwsFi"></a>
  10. ## 索引的创建
  11. 索引的创建时机一般有两处：
  13. - **起初**，`**建表时顺便建立索引**`
  14. - **后期**，`**修改表结构创建索引**`（一般都是这样，因为很难未卜先知，提前优化等于瞎优化）
  15. <a name="VW8Qr"></a>
  16. #### 在创建表的时候给字段增加索引
  17. ```sql
  18. create table `student`(
  19.  `studentno` int(4) not null comment"学号",
  20.     `loginpwd` varchar(20) default null,
  21.     `studentname` varchar(20) default null comment"学生姓名",
  22.     `sex` tinyint(1) default null comment"性别，0或1",
  23.     `gradeid` int(11) default null comment "年级编号",
  24.     `phone` varchar(50) not null comment "联系电话，允许为空",
  25.     `address` varchar(255) not null comment "地址，允许为空",
  26.     `borndate` datetime default null comment "出生时间",
  27.     `email` varchar (50) not null comment "邮箱账号允许为空",
  28.     `identitycard` varchar(18) default null comment "身份证号",
  29.     primary key (`studentno`),
  30.     unique key `identitycard`(`identitycard`),
  31.     key `email` (`email`)
  32. )engine=myisam default charset=utf8;

后期如果需要添加索引，可以通过两种方式：

  • **SQL语句**
  • **Navicat图形界面**

1、创建完毕后，增加索引

  1. -- --增加一个全文索引 `studentname`(`studentname`)索引名（列名）
  2. alter table t_table.student add fulltext index `studentname`(`studentname`)

2、创建完毕后，增加索引（第三种方法）

  1.  --id_表名_字段名
  2.  --CREATE INDEX 索引名 on 表(字段)
  3.  CREATE INDEX id_app_user_name on app_user(`name`)//增加索引

显示所有的索引信息

  1. show index from student

explain 分析sql执行的状况

  1. EXPLAIN SELECT * FROM student;--非全文索引
  3. EXPLAIN SELECT * FROM student WHERE MATCH(studentname) against('张');

利用Navicat图形界面创建单列索引：

image.png
利用Navicat图形界面创建联合索引：
image.png
数据量太大的表，不要自己随便加索引，搞不好会锁表哦…后面有机会再说。总之，你可以“懂索引”，但要“动索引”前，最好三思。

使用索引原因

目的

  • **提高查询效率**

索引的影响

  • 在表中有大量数据的前提下，创建索引速度会很慢
  • 在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

为什么要学习SQL优化

索引的好与坏

提到索引，很多人就会说：哦，索引能提高查询速度。一般这么说的人，可能学得还不错，但绝对还没有完全掌握索引的底层原理。

如果你认为索引的优势只是加快查询，那就太小看索引了。

索引的优势是：

  • 加快查询速度（包括关联查询）
  • 加快排序速度（ORDER BY）
  • 加快分组速度（GROUP BY）

虽然加快排序、加快分组最终还是体现在加快查询速度上，但能主动意识到这一点算是一种突破。只有当你意识到索引能加快排序和分组，你才会在写ORDER BY和GROUP BY时有意识地利用索引分组和排序（最左匹配原则），从而写出更优的SQL。

索引的劣势：

  • 改变数据的时候需要维护索引

    创建索引是需要付出代价的，主要体现在维护成本、空间成本和回表成本。也就是说索引能提高查询效率，但往往会降低增删改的速度，例如插入了一条新的数据，这个数据要加入当前索引里，就有可能需要移动索引的位置，比如原来索引数据是1，3，5，这时候添加了2的数据，那索引就会变成1，2，3，5，也就移动了原来3，5的位置，这就消耗了性能

  • 如果使用了联合索引，还需要考虑索引失效问题

    联合索引是需要符合最左前缀原则的， 比如建立连合索引（a,b,c），你的查询条件如果是 （1）where a= and b= and c= 或where a= and b=或where a= 这三种情况都会走索引； （2）如果是where a= and c= 或where b= and c=或where b= 或 where c=这些情况就会索引失效； 最左前缀原则其实就是要先满足最左边的条件，比如要b的索引列生效，就得先满足a的条件，依此类推，要c 的索引列生效，就得先满足a和b的条件.所以整个连合索引(a,b,c)可以索引生效的情况是a；ab；abc，而像ac；这种情况就相当于中间断开了b的条件，c列的索引也就不会生效了，a列的索引还是生效的，只是abc联合索引不生效

  • 索引会占用物理资源

    索引会占用物理空间，因为索引的数据是存储在索引文件上的，而这个文件的数据存储需要占用物理空间

  • 太多的索引会增加查询优化器的选择时间（选择太多也麻烦）

    索引会让字段查询很快，但是索引也不是盲目用的，盲目用就会引起“太多的索引会增加查询优化器的选择时间”

建索引的原则

很多人觉得SQL优化才是重中之重，创建索引只需要一行代码即可，没什么大不了的。但现在你已经知道了索引的优势与劣势，你会明白“在合适的时候、合适的字段建立索引”是多么空泛的口号。创建索引的判断依据究竟是什么呢？

创建索引有4个大原则：

  • 索引并不是越多越好，联合索引应该优于多个单列索引
  • 索引应该建立在区分度高的字段上
  • 尽量给查询频繁的字段创建索引，避免为修改频繁的字段创建索引
  • 避免重复索引

第一个原则背后的原因是，实际上数据库一次查询只会选择一棵索引树（不包括回表），更专业的说法是每次查询只会选择一个执行计划。即使你给a,b,c,d,e,f,g…所有列都加了索引，SELECT xx, xxx FROM table WHERE …时，数据库也只会择优选择一个执行计划进行查询。

需要注意的是，每建一个索引，就需要维护一棵索引树，所以索引绝对不是越多越好，不合适的索引会增加数据库的负担。比如，你已经搞了一个根据拼音查找汉字的目录，又想根据偏旁部首来，那没辙了，只能劳烦您自己再搞一个目录了。

看到这，你可能会反问：我靠，那MySQL也太笨了吧，为什么这么死心眼一次只利用一个索引？

比较粗浅的理由是：你根据拼音查完汉字以后，还会根据偏旁部首再查一遍吗？

比较正经的理由是：按我个人的理解，索引本身的出发点是“走完一遍索引后，数据库应该返回精确的结果或很小的结果集”，从成本上考虑，此时再走一遍索引还不如直接遍历结果集来得快。当然，要想一次索引就得到精确的结果集，着实需要下一番苦功夫。给哪个字段加索引好呢？我建议，应该尽可能给区分度高的字段添加索引。

什么是区分度很高？这就是建索引的第二个原则啦。比如，表中有100w学生数据，你如果在sex列加索引，那么根据sex大概只能过滤掉50w数据，剩下的结果集仍然很大，说明这个索引建得不太合适，区分度太低了。

第三个原则就是字面意思。比如一本字典根据内容编好目录以后，隔三差五地就有新词汇要往里面加，或者经常要修改汉字读音，一顿操作后必然要连累目录，只能重新编排啦。也就是说，为了保证目录能正确指向对应的汉字，每次增删改后都要额外多一个操作：重新修订目录。

总之要意识到索引在加快查询的同时几乎必然会对修改产生负担，所以创建索引并没有那么简单，它绝对是一门“平衡的艺术”。

第四个原则是，比如已经建立a索引，又建立index(a,b,c)联合索引，此时单列索引a就是冗余的，因为联合索引已经可以保证符合条件时会利用a索引。在物理存储上，a单列索引和index(a, b, c)是两个独立的B+树，重复的索引会增加维护成本。

以上四个原则，后面的内容还会重新提到。

适合建索引情况：

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 与其它表关联的字段，外键关系建立索引
  4. 单键/组合索引的选择问题，(在高并发下，倾向创建组合索引)
  5. 排序的字段
    排序字段若通过索引去访问将大大提高排序速度

  6. 统计、分组的字段

    不适合建索引情况：

  7. Where条件里用不到的字段不创建索引

  8. 频繁更新的 字段 不适合建索引 （每次更新记录，还要更新索引）

  9. 经常增删改的表

    索引可以提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
    因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，加重了IO负担）

  10. 数据重复且分布平均的表字段

    因此应该只为最经常查询和最经常排序的数据列建立索引。
    如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
    比如一个字段的值只 有0、1 表示男女，完全不需要建索引。

  11. 表记录太少

原则

① 全值匹配：使用了联合索引的话，如果用到的联合索引中的字段越多，效率越高
② 最左前缀法则
③ 不在索引列上做任何操作。比如说函数、计算等

  1. EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

④ 使用联合索引的话，不能使用范围查询右边的列

  1. EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

⑤ 尽量使用覆盖索引，减少回表。
⑥ is null is not null 一般不走索引
⑦ 不等于 not in not exists 不走索引， > < 不一定会走索引
⑧ like 

  1. EXPLAIN SELECT * FROM employees WHERE name like '%Lei'  //不走索引
  2. EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'  //走索引

⑨ 如果使用字符串，要加上’’ ‘字符串’
⑩ 少用or或in

范围查找优化：拆分，拆成小范围查找。
① 代码先行，索引后上
② 联合索引尽量覆盖条件
③ 不要在小基数上建索引
④长字符串可以采用前缀索引（但是只能满足=，并且不能排序）
⑤ where和order by 冲突时，优先选where
⑥ 基于慢sql查询优化

索引优化

（这里索引是联合索引）(name,age,position)
① 如果查询的第一个条件字段为范围查找，那mysql会觉得第一个条件就用范围查找，结果集应该很大，回表效率不高，所以走全表扫描。

  1. EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

强制走索引

  1. EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

这个可以使用覆盖索引优化。
② in或or会在数据量大的情况下走索引。
③ like ‘kk%’ 一般情况下会走索引

  1. EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

这里有索引下推的概念
对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =’manager’ 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

对于innodb引擎的表索引下推只能用于二级索引

为什么范围查找不走索引下推？应该是范围查找一般来说结果集比较大，走索引下推效率不高。

③ Order by 和 Group by 优化
Case1：
image.png
分析：
利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort
Case 2：
image.png
分析：
从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。
Case 3：
image.png
分析：
查找只用到索引name，age和position用于排序，无Using filesort。
Case 4：
image.png
分析：
和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。
Case 5：
image.png
分析：
与Case 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。
Case 6：
image.png
分析：
虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
Case 7：
image.png
分析：
对于排序来说，多个相等条件也是范围查询
Case 8：
image.png
可以用覆盖索引优化
image.png
文件排序：

  • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示或者
  • 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示

判断使用哪个排序？
max_length_for_sort_data>1M 双路
我们先看单路排序的详细过程：

  1. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
  2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
  3. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
  4. 重复步骤 2、3 直到不满足 name = ‘zhuge’
  5. 对 sort_buffer 中的数据按照字段 position 进行排序
  6. 返回结果给客户端

我们再看下双路排序的详细过程：

  1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
  2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
  3. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
  4. 重复 3、4 直到不满足 name = ‘zhuge’
  5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
  6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

④ limit优化
a: 有连续自增主键并且不断层 

  1. EXPLAIN select * from employees limit 90000,5;
  2. EXPLAIN select * from employees where id > 90000 limit 5;

b：根据非主键字段分页

  1. EXPLAIN select * from employees ORDER BY name limit 90000,5;
  2. select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

首先走二级索引树，查询到符合条件的id，然后通过表连接查出结果，过程都走索引。

⑤ Join关联优化
a：关联字段加索引
b：小表驱动大表
表关联算法：
a：嵌套循环连接算法（NLJ）走索引

  1. EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

首先，t2是小表100行，t1是大表10000行，首先将t2表中满足条件的一条记录，然后通过字段a在t1表中找，找到后与t2这条记录连接起来，返回给客户端。
b：基于块嵌套循环连接算法（BLJ）

  1. EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

首先，t2是小表100行，t1是大表10000行，将小表t2满足条件的全部数据放到join_buffer缓存中，然后再从t1表中拿出一条一条记录进行比对，返回满足条件的join结果。

⑥ in和exsits优化
原则，小表驱动大表
in：B表为小表

  1. select * from A where id in (select id from B)2 #等价于：
  2. 2 for(select id from B){
  3. 3 select * from A where A.id = B.id4
  4. }

exsits：A表为小表

  1.  select * from A where exists (select 1 from B where B.id = A.id)2
  2.  等价于:
  3.  for(select * from A){
  4.  4 select * from B where B.id = A.id5
  5.  }
  6.  #A表与B表的ID字段应建立索引

A表与B表的ID字段应建立索引

⑦ count()查询优化 count()效率最高
字段有索引：count()≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)字段无索引：count()≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

trance工具

开启 关闭：

  1. mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;  --开启trace
  2. mysql> select * from employees where name > 'a' order by position;
  3. mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
  4. mysql> set session optimizer_trace="enabled=off";    --关闭trace

内容，主要看cost，cost高，查询花费资源大：

MySQL常用引擎

MySQL的引擎有很多种，但最常听到的就MyISAM和InnoDB，而实际开发几乎99%选择使用InnoDB，而且MySQL5.6还是哪个版本以后默认引擎就从MyISAM变成了InnoDB，所以这里着重介绍InnoDB，简略介绍MyISAM。
对于两种引擎的介绍，可以看：存储引擎简介
image.png
这里主要想和大家讨论MyISAM和InnoDB在索引组织上的区别。大家应该都已经知道，MyISAM和InnoDB存储数据的方式是不同的。
MyISAM的每张表在存储时会分为3个文件：

  • 表结构
  • 表数据
  • 索引

也就是说，表数据和索引是分别独立存储的。

而InnoDB的表数据在存储时只分为2个文件：

  • 表结构
  • 表数据+索引

需要注意的是，InnoDB所有表的数据和索引都在同一个文件里（见下一个小节）。

聚簇索引与非聚簇索引

对于BTREE索引而言，从数据的组织形式来看，索引又可以分为两大类：

  • 聚簇索引
  • 非聚簇索引

所谓聚簇索引，可以简单理解为索引和数据是“聚合”在一起的，而非聚簇索引的数据和索引是分开的。
image.png
根据InnoDB引擎的主键索引查询时无需回表，每一行完整的数据都直接挂在叶子节点下，可以直接返回。也就是说，对于InnoDB的主键索引而言，数据即索引，索引即数据。image.png
MyISAM不是很重要，不提了。

InnoDB的索引也并不是都不需要回表，根据是否需要回表其实可以分为两类：主键索引、辅助索引（或者叫二级索引、普通索引）。

会什么要做这种区分呢？

假设一个场景：

新建一张表后，自然会产生主键索引。但后期发现name字段查询很频繁，于是加了name索引。

如果name索引也和主键索引一样挂着数据，那么两个索引数据就会重复。想象一下，现在磁盘中有一颗叫name的树和一棵叫id的数据，一个以name为节点，一个以id为节点，相同的是最底层叶子节点都挂着完整的表数据。也就是说，磁盘中存了两份一模一样的student数据。且不说数据冗余，更新时还可能产生数据不一致（要同步数据，确保多张表的数据一致性）。

所以InnoDB的做法是，辅助索引只存储索引列+主键，必要时进行“回表”操作：
image.png
由于SELECT FROM stu WHERE name=’bravo’中，查询的数据是，也就是整行数据。而上面的辅助索引只存了主键+name，所以必须回表：拿着主键再去跑一遍主键索引，最终返回整行数据。

现在，我们可以给MyISAM和InnoDB的索引分类做个简单的总结：

  • MyISAM：非聚簇索引，需要回表
  • InnoDB：
    • 聚簇索引：主键索引，叶子节点是表数据，不需要回表
    • 非聚簇索引：辅助索引（唯一索引、普通索引），叶子节点是主键，必要时需要根据主键回表查询

图片.png
InnoDB每张表只能有一个主键索引，辅助索引则可以有多个。表数据只有一份，挂在主键索引下面。

需要注意的是，如有可能，应该尽量避免回表。SQL优化的本质其实就是减少/减小磁盘IO，而回表必然会增加磁盘IO次数。

举个例子，假设某张表总共就两棵索引树：主键索引+name辅助索引，两棵树高度都是3。由于只有主键索引下才挂着表数据，所以对于SELECT * FROM table WHERE name=’xxx’来说，需要先走辅助索引取得id，再根据id走一遍主键索引。假设两棵树需要的数据都在第三层，那么这条SQL需要进行6次逻辑IO访问。而如果直接根据id查询，就可以直接走主键索引，IO次数为3。

所以，通常情况下辅助索引查询都是需要回表的，比主键索引查询多扫描一棵索引树（自身+主键索引），实际编写SQL时，应该尽量走主键索引。

那么，什么情况下辅助索引可以避免回表吗？

索引覆盖

索引覆盖这个名字，咋一听不知所云，所以很多初学者一直搞不明白什么意思，其实它做大的作用就是：避免回表。

下面通过一个案例来说明。

假设有个需求：前端需要支持根据用户名模糊搜索订单，而页面需要的字段如下。

id productName price userName userAge
1 iphone12 5999 bravo1988 18

一个可行的方案是：

  1. 在t_user表中根据name搜索用户，得到user_id、user_name、user_age
  2. 在t_order表中根据user_id查询订单
  3. 在内存中根据user_id匹配order和user数据后返回

由于t_user表此时的查询条件是user_name，为了加快t_user表的查询速度，可以给user_name加普通索引。但，这样真的好吗？我劝！不要犯这样的聪明，小聪明啊。

你要知道，此时我们从t_user表查询的可不止user_name，还有user_age和id。如果只是给user_name加了索引，那么此时磁盘中产生的索引树是这样的：
图片.png
这棵树的非叶子节点是user_name，叶子节点是id，也就是说从这棵树上我们只能得到user_name和user_id，至于user_age，MySQL底层只能跳出name索引树，然后跑到隔壁主键索引获取。整个过程被称为回表，而回表意味着多跑一趟。

此时我们可以给user_name和user_age加一个联合索引，这样就能产生所谓的“索引覆盖”：
图片.png
当辅助索引上的字段完全满足本次查询的列时，就是所谓的索引覆盖，这是一个好消息，意味着不需要回表，查询效率将会大大提高。这也是为什么SQL优化原则中经常会强调：尽量只取必要的字段，避免SELECT *（提高索引覆盖的记录，查询的字段越多，几率越低）。

即使目前表中只有两个字段且已经索引覆盖，也不要写SELECT 。因为后期随着业务扩展，这张表会新增其他字段，此时SELECT 将不再覆盖索引！

为了方便记忆，大家可以把索引覆盖理解为 索引的字段 >= 查询需要的字段。比如联合索引的字段是index(a,b,c)，那么此时SELCT a, b就会发生索引覆盖，索引覆盖最大的好处是避免回表。

需要强调的是，覆盖索引和联合索引没有必然关系。比如我只给user_name加单索引，而我查询语句是

SELECT id, user_name FROM t_user WHERE name=’bravo’;

此时也是索引覆盖。所以，能否索引覆盖不取决于索引单方面，需要查询配合。

关于联合索引，我们放在下一篇介绍。

重点总结

今天我们学习了索引的几种分类、索引的好与坏、创建的索引的几个原则，还介绍了聚簇索引和非聚簇索引，由此引出了回表的概念。而回表会产生额外的IO，为了提高效率我们又学习了“索引覆盖”。希望通过这篇文章，能让大家对索引有个大致印象。

但索引最难的部分还未到来，下一篇我们一起来学习联合索引，这是非常重要的一块知识点，由它引出的问题多不胜数，比如索引失效、最左匹配原则、ORDER BY排序失效等等。

测试增加100万条数据

新增一个表

  1. CREATE TABLE `app_user` (
  2. `id` BIGINT(20) unsigned NOT NULL auto_increment,
  3. `name` VARCHAR(50) DEFAULT  '' COMMENT '用户昵称',
  4. `email` VARCHAR(50) NOT NULL COMMENT "用户邮箱",
  5. `phone` VARCHAR(20) DEFAULT '' COMMENT "手机号",
  6. `gender` TINYINT(4) UNSIGNED DEFAULT 0 COMMENT "性别（0：男;1:女)",
  7. `password` VARCHAR(100) NOT NULL COMMENT "密码",
  8. `age` TINYINT(4) DEFAULT '0'  COMMENT '年龄',
  9. `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  10. `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  11. PRIMARY KEY (`id`)
  12. ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';

新增100万数据

  1. 1 | -- 百万数据插入
  2. DROP FUNCTION IF EXISTS mock_data; -- 写函数之前必须要写，标志:$$
  4. DELIMITER $$
  5. CREATE FUNCTION mock_data()
  6. RETURNS INT -- 注意returns，否则报错。
  7. DETERMINISTIC -- 8.0版本需要多这么一行
  8. BEGIN
  9.  DECLARE num INT DEFAULT 1000000; -- num 作为截止数字，定义为百万，
  10.  DECLARE i INT DEFAULT 0;
  11.  WHILE i< num DO
  12.    INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
  13.      VALUES(CONCAT('用户',i),'965499224@qq.com', CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  14.    SET i = i + 1;
  15.    END WHILE;
  16.    RETURN i;
  17. END;
  20. 2 |  SELECT mock_data();

测试索引

无索引时，查询时间

  1. 1 |  SELECT * FROM app_user WHERE `name` = '用户9999'; //1.23s
  2.      SELECT * FROM app_user WHERE `name` = '用户9999'; 1.202s
  3.      SELECT * FROM app_user WHERE `name` = '用户9999'; 1.299s
  5. 2 |  EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999'; //查了多少行数据才查到此数据

结果
image.png

新增索引

  1. --id_表名_字段名
  2.  --CREATE INDEX 索引名 on 表(字段)
  3.  CREATE INDEX id_app_user_name on app_user(`name`)//增加索引

新增索引后，查询时间

  1. 1 | SELECT * FROM app_user WHERE `name` = '用户9999'; 0s
  2.     SELECT * FROM app_user WHERE `name` = '用户9999'; 0s
  4. 2 | EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';//查了多少行数据才查到此数据

结果
image.png

删除索引

  1. 1 | drop index [indexname] on mytable;
  3. 例
  5. 2 |  drop index id_app_user_name on app_user

索引总结

为什么要使用索引

  • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
  • 可以加快数据的检索速度（减少的检索的数据量，这也是创建索引最主要原因）
  • 帮助服务器避免排序和临时表
  • 将随机IO变成顺序IO

  • 可以加速表和表之间的连接，特别是在实现数据的参考完整性方法特别有意义

    索引这么多优点，为啥不对表中每一个列创建一个索引呢？

  • 当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度

  • 索引需要占用物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立

聚簇索引，那么需要的空间就会更大

  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

    索引是如何提高查询速度

    将无序的数据变成相对有序的数据（就像查目录一样）

使用索引的注意事项

  • 在经常需要搜索的列上，可以加快搜索速度
  • 在经常使用where字句中的列上面创建索引，加快条件的判断速度
  • 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
  • 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
  • 在经常在连接的列上，这些列主要是一些外键，可以加快连接的速度
  • 避免where子句中对字段施加函数，这会造成无法命中索引
  • 在使用innoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键
  • 将打算加索引的列设置为not null，否则将导致引擎放弃使用索引而进行全表扫描
  • 删除长期为使用的索引，不用的索引的存在会造成不必要的性能损耗，mysql5.7可以通过查询sys库的chema_unused_indexes 视图来查询那些索引从未被使用
  • 在使用 limit offset查询缓慢时，可以借助索引来提高性能

mysql索引主要使用的两种数据结构

哈希索引

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为但条记录查询的时候，可以选中哈希索引，查询性能最快，其余大部分场景，建议选中BTree索引

BTree索引

mysql的BTree索引使用的是B树种的 B + Tree，但对于主要的两种存储引擎（myisam和innDB）的试下方式是不同的

面试题

① 为什么要用B+树索引而不用B树索引
因为B树索引非叶子节点存储了数据，也就是说，一般一个节点可以存储16K的数据，那么如果使用了B树索引，一个节点一般存16个记录，如果使用B+树索引，非叶子节点可以存1700个记录，这样会大大降低树的高度。

② 为什么使用B+树索引，而不用哈希索引？
虽然哈希索引的处理效率会比B+树索引快，但是，哈希索引由于它结构的问题，它没办法进行范围查询，而且哈希索引会发生哈希冲突。

③ 聚集索引和非聚集索引的区别。
聚集索引叶子节点存储的是数据，非聚集索引叶子节点存储的是地址。聚集索引的查询效率比非聚集索引快，一个表只能有一个聚集索引

④ 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
如果InnoDB表没有建主键，那么MySQL会自动维护一列自增的roleId，那会加大性能损耗。
因为使用整型只占8位，如果使用字符类型，那会加大磁盘损耗。还有，比较整型会比比较字符串快。
使用自增是防止插入数据的时候，由于B+树的自动平衡，会加大性能损耗。

⑤ 为什么非主键索引结构叶子节点存储的是主键值？
因为要保证一致性。就是可以先插入聚集索引，然后生成主键再插入非主键索引。
使用主键，可以避免存重复数据，减少磁盘空间的浪费。