mysql执行计划，sql优化，索引及B+树详解

索引概述

概念：索引是存储引擎用于快速找到记录的一种数据结构。
举例说明：如果查找一本书中的某个特定主题，一般会先看书的目录（类似索引），找到对应页面。那么在数据库中存储引擎采用类似的方法使用索引，高效获取查找的数据。

索引的优点

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

索引创建原则

1.最左前缀匹配原则
非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.索引列不能参与计算

保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

6．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

7．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

8．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

9．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

10．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

11．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

12．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

数据很多的情况下索引失效的操作

MySQL中决定使不使用某个索引执行查询的依据很简单：就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件
数据库索引如下：

1，存储引擎不能使用索引中范围条件右侧的列。

查询时间是：2020-05-23


2，尽量使用覆盖索引，减少select *

3，mysql在使用不等于时无法使用索引。

改为不等于，全表扫描

4，is null可以使用索引（成本小），is not null 无法使用索引（成本高）。


5，like ‘%…’ 无法使用索引，like ‘…%’ 可以使用索引。


解决like ‘%…’ 无法使用索引的方法：

Select主键：只要Select的字段刚好是主键，那么就会使用到索引(只对innodb数据库有效)

覆盖索引法：覆盖索引是一种特殊的多列索引，当多列索引指向一个查询语句中所有的字段时，该多列索引就被称为覆盖索引。（注意：当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！）

6，字符串不加单引号 索引失效。


7，少用or 用他来连接时 索引失效。

索引类型

主键索引

叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引

叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

聚簇索引

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。
因为B+TREE的原因，最好使用连续的整数字段，更好确定查找访问范围

回表

根据上面的索引结构说明，主键索引和普通索引的查询区别
1，如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

2如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。即：当多列索引指向一个查询语句中所有的字段时，该多列索引就被称为覆盖索引。

索引下推

索引下推是MySQL5.6版本推出的优化手段

默认是开启的，可以通过下面命令关闭

SET optimizer_switch = 'index_condition_pushdown=off';

假如有以下SQL

select * from tuser where name like '张%' and age=10 and ismale=1;

可以看到，无索引下推是，每次都需要回表查询,而下推的是在先过滤好结果集，回表拿select * 的数据

无索引下推

有索引下推

sql优化建议

1，查询语句无论是使用哪种判断条件 等于、小于、大于， WHERE 左侧的条件查询字段不要使用函数或者表达式

2，使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分析这条 sql 语句，这样方便我们分析，进行优化。

3，当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1

4，不要直接使用 SELECT *，而应该使用具体需要查询的表字段。

5，为每一张表设置一个 ID 属性

6，避免在 WHERE 字句中对字段进行 NULL 判断

7，避免在 WHERE 中使用 != 或 <> 操作符

8，使用 BETWEEN AND 替代 IN

9，为搜索字段创建索引

10，选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等

11，使用 LIKE %abc% 不会走索引，而使用 LIKE abc% 会走索引

12，对于枚举类型的字段(即有固定罗列值的字段)，建议使用ENUM而不是VARCHAR，如性别、星期、类型、类别等

13，拆分大的 DELETE 或 INSERT 语句

14，选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数。

15，字段设计尽可能使用 NOT NULL

explain详解

1，用法

explain select * from user;

2，执行计划包含的信息

字段解释：

1.id​：查询的顺序
由一组数字组成，表示一个查询中各个子查询的执行顺序。

2，select_type：查询类型

3，table：查询涉及到的表

如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

4，访问类型

性能从好到差依次为：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

所以，如果通过执行计划发现某张表的查询语句的type显示为ALL，那就要考虑添加索引，或者更换查询方式，使用索引进行查询
5，possible_keys：可能使用的索引

询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了

6，key：实际使用的索引

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

7， key_length：索引长度

char()、varchar()索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

8，ref：连接匹配条件

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值，如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

9，rows：估算的结果集数量

返回估算的结果集数目，注意这并不是一个准确值。

10，extra：额外信息
extra的信息非常丰富，常见的有如下4种类型：

索引底层数据结构

B-Tree和B+Tree

B-tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1，根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17, 35），找到磁盘块1的指针P2。

2，根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26, 30），找到磁盘块3的指针P2。

3， 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

B+Tree

B+Tree是在B-Tree基础上的一种优化，InnoDB存储引擎就是用B+Tree实现其索引结构。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

从图中也可以看到，B+树与B树的不同在于：
（1）所有关键字存储在叶子节点，非叶子节点不存储真正的data
（2）为所有叶子节点增加了一个链指针
可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护 10^3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1至3次磁盘I/O操作。

那么问题来了，为什么用B/B+树这种结构来实现索引呢？？

答：红黑树等结构也可以用来实现索引，但是文件系统及数据库系统普遍使用B/B+树结构来实现索引。mysql是基于磁盘的数据库，索引是以索引文件的形式存在于磁盘中的，索引的查找过程就会涉及到磁盘IO(为什么涉及到磁盘IO请看文章后面的附加理解部分)消耗，磁盘IO的消耗相比较于内存IO的消耗要高好几个数量级，所以索引的组织结构要设计得在查找关键字时要尽量减少磁盘IO的次数。为什么要使用B/B+树，跟磁盘的存储原理有关。

局部性原理与磁盘预读

磁盘IO与预读

数据库保存的数据是存储在磁盘上，查找数据时需要将磁盘中的数据加载到内存中，在介绍索引的实现之前，先了解下磁盘IO与预读。

磁盘预读

内存比磁盘的读写速度要快很多，但内存容量要远小于磁盘，数据，程序的执行要调入内存后才能执行，所以内存和磁盘要经常进行I/O操作，I/O操作是个费事的过程，虽然现代系统已经有了通道（I/O处理机）技术的支持，但这还不够

上面是两张磁盘的示意图 ，扇区为磁盘的最小存储单元，当需要从磁盘读取数据时，系统会将逻辑地址发给磁盘，磁盘的控制电路将逻辑地址转换为物理地址（哪个磁道，哪个扇区）这时候就会有磁头的机械运动，磁头找到相应磁道–寻道，时间成为寻道时间，磁头找到对应磁道的对应扇区，成为旋转时间。 这是很大的时间消耗，所以系统一般采取预读的方式，减少磁盘I/O。

系统是分页读取和存储的 一般一页为4KB，每次读取和存取的最小单元为一页，预读即在读取的起始地址连续读取多个页面（现在不需要的页面也读取了，这样以后用时就不用再读取，当一个页面用到时，大多数情况下，它周围的页面也会被用到）

至于磁盘分页，参考计算机操作系统的分页，分段存储管理 逻辑地址和物理地址被分为大小相同的页面，逻辑地址中叫页，物理地址中叫块

磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4. 17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4. 17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

为了提升效率，要尽量减少磁盘IO的次数。实际过程中，磁盘并不是每次严格按需读取，而是每次都会预读。磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中，这样做的理论依据是计算机科学中注明的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用
程序运行期间所需要的数据通常比较集中

（1）由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，
因此对于具有局部性的程序来说，预读可以提高I/O效率.预读的长度一般为页(page)的整倍数。
（2）MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K(这个值可以修改)。linux 默认页大小为4K。

B-Tree借助计算机磁盘预读的机制，并使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个结点只需一次I/O。
假设 B-Tree 的高度为 h,B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3，也即索引的B+树层次一般不超过三层，所以查找效率很高）。
而红黑树这种结构，明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

为什么mysql的索引使用B+树而不是B树呢？？
（1）B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
（2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，区间查找效率低。

原文：
https://blog.csdn.net/weixin_42782897/article/details/89960484

原文：https://juejin.im/post/5e9f1789f265da480a1ac644

原文：https://www.cnblogs.com/lisq/p/12634457.html

原文：
https://juejin.im/post/5e9a65326fb9a03c5d5c90e6

原文：https://blog.csdn.net/xfcy1990/article/details/90026089

原文：
https://www.jianshu.com/p/0371c9569736

原文：https://blog.csdn.net/tengdazhang770960436/article/details/94065557