MYSQL 性能优化和sql调优----(3-1)基础概念理解图文理解

数据库索引介绍

索引是什么

MySQL官方对索引的定义为:索引(Index) 是帮助MySQL高效获取数据的数据结构。

可以得到索引的本质:索引是数据结构。你可以简单理解为“排好序的数据结构”，以便能够快速查找。
我们可以这么类比理解：

• 数据库就是学校的图书馆，
• 数据库里几十万条数据就是图书馆里几十万本书，
• 索引就是图书管理员手上的分类目录手册，这个手册上只写了图书特征和这个特征的图书所在位置，而且是按顺序排，索引上也是存了特征----》内存地址，这些特征也是按顺序存储下来的。
• 查询优化器就是图书馆的管理员，图书管理员帮我们选择他认为合适的分类目录手册，查询优化器帮我们选择它认为合适的索引。
  

我们只需要浏览这个手册，根据递增的规律找到对应作者名字，图书类别，出版时间来确认等关键内容能确定这本书的大概位置；索引就是通过通过关键字去树形结构或其他算法里找到这个数据存储的位置。
这时候我们再来理解这句话：数据库维护着一个满足特定查找算法的数据结构：这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
我们可以类似认为索引中数据是key-value键值对，key是查询条件，value是要找到的数据地址。

索引有什么用,为什么要用?

1. 提升查询速度，降低IO消耗
2. 提升排序顺序，降低CPU消耗

这就像我去图书馆借100本书，假如没有分类手册，我需要爬遍整个图书馆楼，打开所有门，去查看所有的书籍才能找到我想要的那些书，找到以后还需要重新排序，我会很累，没有精力再去看书。

而这里打开所有的门就类似于IO操作，很费时间。爬楼是要消耗能量的，就像是CPU操作，会让我没有力气做其他事情。

但是有了分类手册，我只需要站在图书馆门口按顺序找到我想要的书，然后直接去对应书柜拿我想要的书就可以，这显然要省时也更省力。而且手册是有顺序的，我想找作者是张三的数据，可以直接跳过以ABCD等开头的名字，直接扫描以张开头的目录就可以。

索引可能带来什么问题

1. 索引也是一条一条的数据，也是会占用磁盘或内存的空间的，所以如果在索引中加太多字段，会占用过多的空间；
   就像图书馆里的手册，你不仅把文章作者信息放到了分类手册中，你同时把文章内容也放到了手册里，这时候手册的厚度可能比原书籍还要厚。
2. 索引提升了查询的速度，但是更新的速度会变得很慢；
   就像图书馆中，没有手册时，我们只需要把新书籍放到对应的柜子上，或者把需要挪动的书籍移动到对应的书柜上就可以，但是有了手册我们就需要同时把所有涉及到的手册也更新一下。
3. 添加索引字段组合有很多种，我们需要花费大量时间去寻找合适的索引。
   就像图书馆中我们是按照年代分类呢，还是按照年份分类，或者是年代+作者区分类，这些都需要人为通过大量的经验和实践来设计。

索引分类

1. 结构上分类
   从数据结构上分类，常见的有：BTree索引、hash索引、R-tree索引、full-text索引。其中最常用的就是Btree索引，他的数据结构是一个树形结构，通过分块管理子节点。结构图如下。
   

2. 通过创建方式分类
   通过创建方式分类可分为 单值索引、复合索引、唯一索引。
   – 单值索引：索引中只包含一个字段，如我给手机号字段创建一个索引；
   – 复合索引：索引中包含2个或以上字段，如创建一个索引，包含姓名和年龄两个字段；
   – 唯一索引：创建单值索引或复合索引时，我们想让这个组合后的值是唯一的，例如为了数据准确给用户表的身份证号字段创建一个唯一索引，防止表里出现两个人身份证号相同、给成绩表里学号和科目创建一个复合类型的唯一索引，防止同一个学生同一科目出现两个成绩。

3. 通过查询过程分类
   – 聚集索引：通过唯一索引生成的一个核心索引表，这个表的节点里value直接存的最终数据的地址，一般是用主键作为索引的键值生成的索引表，如果没有主键就使用普通唯一索引，唯一索引也没有就是用一个隐藏的行号rownum 作为键值；
   – 普通索引：索引中的节点中value存的是聚合索引的key的地址，扫描完普通索引后，可能还需要在去聚集索引中再扫描一遍，取到最终数据的地址。

聚集索引和普通索引可能不太看好理解，通过以下索引关系模型可能会更好理解一些

数据库索引关系模型

索引关系模型是我自己抽象出来一个索引关系的图形化模型，通过这个模型我们能够理解一些MQSQL概念性的东西和一些数据查询现象的记忆和理解。

如图中所示，箭头指可以在查询中经过的查询路径，蓝色框是优化器可以直接访问的，虚线框是需要前置索引才能访问的，我们可以从模型中看出来一下几个结论，

1. 聚合索引是可以直接访问数据地址的，所以当我们直接通过主键查询时，效率会特别高，因为只扫描了一次主键索引，查询路径如下
   

2. 覆盖索引：当我们通过普通索引查询时，如果需要返回的字段在索引中已经全有了，就不需要去真正表中取这个数据，只扫描了索引表，所以效率会很高，例如通过作者auth字段查询type字段字段
   

3. 回表查询： 索引没有完全覆盖，这时候只通过索引中的数据无法返回完整结果，需要再去聚集索引扫描一下，找到最终数据，这就是回表查询，例如通过type查询name字段，需要回表查询，下图中黄色线为回表过程。如果我们查到1w条数据，都需要回表操作，效率肯定就会打折，变得很慢，所以尽量避免回表查询会提高效率。
   

4. 索引失效：从图中可以看出，从type索引是没有直接到year索引的箭头的，所以一旦没有使用auth索引就会导致，无法用到year索引，例如查询 select * from table where type=“35” and year = 1991,这时候的路径如下图:
   

有了这个图之后我们再去理解为什么索引失效，等问题时就更容易理解了