理解 B+树索引《动手探索mysql原理》

目录

关于索引

局部性原理   

主键索引

一般情况为何推荐使用自增id，id值小一点？

辅助索引

最左前缀原则

索引覆盖

索引条件下推

postgresql索引使用

索引规约（阿里巴巴开发规约手册1.4）

---

---

学习笔记--mysql的索引原理

这个是学习笔记，我觉得学习了一样东西，自己写一写，把学到的东西讲给别人/自己听，这样做一遍是对自己理解知识很有好处的。     慢慢来，比较快。

内容主要是B+树索引。

关于索引

我知道他是用来优化我们对数据的查询的。那么如何优化的呢？

我们为什么需要用索引？是因为我们需要在某些时候，希望数据的查询操作能更快一点。

前人们思考出很多算法和模式或原理。在这些思想的支持下，经过人们的努力和思考将其用于实际。对我而言重要的是它是如何诞生的、人们是如何设计出它的。设计中每一个细致的小点都是我的追求，会使用它从不是我的目标。

我依稀记得有人曾经说过软件设计就是取舍。当我们想要在需要的时候查询的更快，我们可以在查询之前做好一系列的准备工作。我们知道当我们想去对一个数组使用二分法查询的时候，首先先得为数组排序，那么我们事先将所有用到的数据排好序，就为真正查询的时候省去了排序的时间。

有人说索引就像是目录，说的是他们的设计思想很像。我们小时候就会使用汉语字典。可以说那个时候我们就初步有了这种查询的思想。我们先查到某个字母，然后找到拼音，我们发现拼音后面标了页码，于是去翻找对应的页。你会发现这个页码是那个拼音的起始页，这样依次往后找直到找到我们需要的字。

对于这种结构，我们在数据结构中常常用树来存储。这个各种树的知识这里就不说了。什么是AVL树、B+树、hash表就不说了。

首先我们要知道在操作系统中有一个概念叫做 页。这个是一个逻辑概念，我们一般将数据存储在页中。如果你是用的mysql的innoDB引擎，那么默认的一页为16KB。这里引入一个原理：

局部性原理   

这个原理认为，你在取某些数据的时候，我认为你很有可能即将要取这个数据相邻的其他数据。因此在你取一个数据的时候我会把它相邻的数据也取出来放到内存，待用。

这里取多少呢？OS会取一页的数据，一般OS一页是4KB，这个原理可以减少磁盘的存取次数 即磁盘IO。查询就更快了。

在使用innoDB引擎的时候是将数据存在磁盘上的，取数据的时候先放到内存去，然后cpu去判断条件取。

 

主键索引

CREATE TABLE `user` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `bcde` (`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

 

我们创建一个表  里面有5个字段 a、b、c、d、e，行数据为1 112c    （表示一行的数据 ） 空格前的是主键

当我们设定主键之后，mysql就会自动创建主键索引，如果不设定会把唯一性约束的那个字段当成主键设定索引，如果还没有，会默认用db_row_id（mysql自动给每一行添加的隐藏字段）创建索引。所以创建了一个表之后，就已经有了一个主键索引。

db_row_id是mysql给每一行数据加的一个隐藏字段，实际上还有两个隐藏字段：事务id和回滚指针。之后学事务的时候再看。

我们画一下B+树索引的结构如下：

 

关于这个图，我们假设一个页可以存4个数据，数据库中存了8个数据。一页中不同的颜色相当于分了个组。主键索引设定好后，数据就会这样按照B+树的存储。叶子节点是按照主键排好序的，并且前一个拥有指向后一个的指针，形成一个链表。叶子节点我们称为数据页，而非叶子节点称为索引页。

目录页中包括主键和页地址。这样我们想找主键=2 就会从目录页中拿着2去比较，然后走左子树访问页地址，最后逐步找到2_3152a 这条数据。这样我们就很快的找到一条按主键查找的数据。

当我存进一条数据时，会把这个数据存到一页中。随着逐步的增加，数据越来越多就需要多个页。mysql创建页的方案：第一页写完了，mysql会去复制一下第一页，然后开辟第二页，再将之前的第一页变成目录页。按照这种操作这个起始页是一直不变的。这样就可以把这个起始页缓存起来加载到内存，查找就又更快了。

一般情况为何推荐使用自增id，id值小一点？

自增因为，如果你新增一个主键不自增你存储的时候会涉及到链表的移动，会将前一页的元素挤到下一页去，这是很不方便的，如果是自增的就可以直接往后加，一页加完在加一页。小一点是因为如果你存的很大，比如uuid，那你一页可以村的数据可能会少一点，同样的数据，相对而言就会uuid的页数就会多一点。b树可能就会高一点。查数据就会慢一点。

画一下比较容易看懂的。和上图其实差不多，但是mysql在真正实现的时候并不是完全按照B+树的结构定义，他进行了一些改造，如图，有固定的指向上一页和指向下一页的指针。这样可能让更多的情况可以使用到索引。可以看到设计的时候实际上是需要我们根据具体的需求去用前人的成果的，一定要先理解，然后发展。

我们可以把这个既有索引又有数据的称为聚集索引（聚簇索引）：表记录的排列顺序和与数据的存储顺序一致(主键)，一个表只能有一个,但该索引可以包含多个列（组合索引），他规定了数据文件的存储顺序。

在mysql的innodb引擎中只有主键索引是这种聚簇结构。

当然也有非聚集索引：表记录的排列顺序和与数据的存储顺序不一致(唯一)，一个表可以有多个。
-->是一个单独存放的指针表,用来快速找到对应点的地址

我们可以通过字典理解一下：字典的拼音目录就是聚集索引，笔画目录就是非聚集索引。

 

辅助索引

假设有这样几行，命名随意了

id  b   c   d   e --字段名
1   2   3   4   q
2   3   3   4   f
3   5   7   1   g
4   4   3   2   h

alter table user add index bcd (b,c,d);    //这里给user表的b,c,d三个字段添加索引

我们可以画一下这个索引的结构

简单看上去就是按照bcd这三个字段排序了。这样排序，明显b字段会优先排，b相同在按c排。。。

那么当我们查询b=2的时候，就相当于查询 2** 拿着这个去比较，最后拿到这个字段的主键，然后通过主键索引拿到所有信息（回表）。

看下面这种查询：

explain select * from user where b = 2 and c = 3 and d = 4;

我们有多种找法：比如1：全局查找 2：先用辅助索引，找到主键，然后再用主键索引找到数据返回（这称为回表）。这里呢会有一个查询优化器会帮你优化sql帮你看用什么方法查询。这里我们使用explain关键字可以看type属性查看 它使用哪种方法查询的。

这里查询结果是：

 

这里的possible keys就是你可能会用到的索引，key就是你实际用到的索引。如果是ALL就是全表扫描。

我们可以使用这个关键字查看你的sql有没有用到索引。帮助你去检查设计的索引。

mysql会自动帮我们选择走哪种方式，有的时候数据量比较多，采用索引磁盘IO比全表扫描还要多，可能就不会走索引，而使用ALL 全表扫描。

 

最左前缀原则

假设我们现在创建了一个索引alter table user add index bc_index(b,c,d); 

执行查询条件：EXPLAIN SELECT * FROM num where b = 3;

发现它走的是bc_index索引

接着我们查询EXPLAIN SELECT * FROM num where c = 2;

一看结果，使用的是ALL全局查询，为什么呢？

分析一下b+树的查询过程：当我们查b=2的时候实际上可以看成查   3**。之后呢会拿着3**和b树去比较搜索。 我们看上述索引图从上往下会先查3再2，4之间，就会去查234的子树--这样一直查下去，最后可以拿到结果。也就是说走这个索引不需要查右边的树。当我们可以这样查数据的时候，就有可能会走这个索引去查。

再看c=2.相当于查询  *2*   由于我们比较不了第一个数这样我们无法判断从b树顶端哪里开始往下查。这时候就不会走这个索引查了。

这其实就是最左前缀原则。那么我们想使用索引的时候，往往要考虑到这一点，为了使用索引有时还要将最左边的条件给上。

 

到了这里我搞出了一个问题，当我把上述表的e字段给删了，这时候我创建了b,c,d索引。a是主键索引。

这个时候查询EXPLAIN SELECT * FROM num where c = 2;居然使用的是bcd索引！！ 为啥？讲道理这时候拿着 *2*去这个树是搜索不了的，不符合最左前缀原则。

 

这个问题是这样的，我们称之为：

索引覆盖

如果你查的字段都建立了索引，那么innoDB引擎会直接在索引表中查询而不会访问原始数据，这叫做索引覆盖。

实际上我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。（不要为每个字段建立索引，否则索引的树很大搜索的时间就会长。）

 

我们在用索引的时候，最主要明确一点，使用这个索引到底能不能给我们的查询带来好处？

这就需要我们对sql究竟时怎么查的有深入的了解。下面再举几个例子：a为主键，索引如上两个。

explain select * from user where a>2;

explain select * from user where b='2';

explain select b,c from user order by b asc;

explain select b from user order by b,c,d ;

explain select * from user order by b asc,c asc,d desc ;

1、关于大于，其实和等于一样，只不过是找到a>2的一个最小数之后再把它后面的全部都拿出来。就可以用到主键索引。

2、关于b是一个int类型，在mysql中如果 select  1= ‘1’；返回的是1、 select  1= ‘a’；返回的是0 、 select  0= ‘a’；返回的是1.也就是说mysql是会把字符转化为数字，如果引号里是数字那么直接转化为数字。如果是字母，转化为0.所以这里也会用到索引。

注意：上述你会发现 如果你where b=‘a’的 会返回b=0的数据。这对于你得业务逻辑可能是存在问题的。

3、排序的话，如果Extra出现file sort，他是将一些字段数据内容存到内存的一个buffer中然后在内存中快速排序。

4、按照b，c，d进行排序，而我们的bcd_index索引已经排好序了。当然就会直接用索引了。

5、结果可以自己看，值得一提的时mysql 8之后可以指定索引是升序或降序索引。

索引条件下推

我们在使用explain关键字的时候发现Extra中有Using index condition。

如：

explain select * from user where b>2 and c like '%1';

他会在回表之前先执行and后面的条件。用筛选出来的数据再进行回表。这样就比较快。这就是索引条件下推。

 

 

最后回顾看什么是索引呢？我们发现它就是对你指定的数据做了一些的排序操作，并且mysql会给你优化你的查询，选择用什么方式来查，从而让我们查询的时候会比较快点。我们真正要会的是知道什么时候需要索引，根据业务选择是否设计索引，如何设计索引。

那么索引就学到这里，以后如果有了更多的发现再更新吧。

 

最后的最后来浅出一下：之前说了mysql的索引使用。现在看下postgresql的使用，先不谈原理是否相同吧。

postgresql索引使用

创建索引     

以user_info表为例子

CREATE INDEX user_info_b_index  ON  public.user_infoUSING btree (b);
//USING btree可以省略。

按降序
create index idx_user_info_name_index on user_info(name desc);

指定空值排在前面或者后面
1. create index idx_name on user_info(name desc nulls first);
2. create index idx_name on user_info(name desc nulls last);

指定存储参数
create index idx_name on user_info(name) with (fillfactor=50) ;


修改索引
alter index idx_name rename to idx_name_new;
alter index idx_name set(fillfactor=70);


查看索引

   查看user_info表的索引

select * from pg_indexes  where tablename= 'user_info';

//查看所有表的索引使用情况

select
    relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from
    pg_stat_user_indexes
order by
    idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;

//查看某一个表的索引使用情况
 select
    relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from
    pg_stat_user_indexes
where
    relname = 'user_info'
order by
    idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;

删除

//如果有对象依赖这个索引，则删除会失败。
DROP INDEX index_name;  
//使用“cascade”选项，表示当有依赖这个索引的对象时，一并把这些对象删除掉。
drop index idx_name cascade;


ANALYSE user_info;

索引规约（阿里巴巴开发规约手册1.4）

1. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

2. 【强制】超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询时， 保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

3. 【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度即可。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分 度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度 来确定。

4. 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索 引。

5. 【推荐】如果有 order by 的场景，请注意利用索引的有序性。

order by 最后的字段是组合 索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。 正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。

6. 【推荐】利用覆盖索引来进行查询操作，避免回表。

说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览 一下就好，这个目录就是起到覆盖索引的作用。 正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查 询的一种效果，用 explain 的结果，extra 列会出现：using index。

7. 【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过 特定阈值的页数进行 SQL 改写。 正例：先快速定位需要获取的 id 段，然后再关联：       SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

8. 【推荐】 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

说明：  1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。  2）ref 指的是使用普通的索引（normal index）。  3）range 对索引进行范围检索。 反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级 别比较 range 还低，与全表扫描是小巫见大巫。

9. 【推荐】建组合索引的时候，区分度最高的在最左边。

正例：如果 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。 说明：存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即索引 idx_d_c。

10. 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

11. 【参考】创建索引时避免有如下极端误解： 

1）宁滥勿缺。认为一个查询就需要建一个索引。

2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。

3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决

 

---

如有理解错误、不透彻或者有歧义的地方还请路过的大佬们指正！

 

 

海明威说，这是个美好的世界，值得我们为之奋斗。我同意后半句。——《七宗罪》