全文搜索在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表。
目前只有char、varchar，text 列上可以创建全文索引。
like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询

2.5 联合索引（组合索引）

用多个列组合构建的索引，这多个列中的值不允许有空值。在使用过程中有诸多规则，遵循最左前缀原则，顺序至关重要

2.6 空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。

空间索引适用于在 Oracle 和 PostgreSQL 中调用函数来对比几何的查询。


- 查看表结构
    desc 表名
 
- 查看生成表的SQL
    show create table 表名
 
- 查看索引
    show index from  表名
 
- 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;

如何选择唯一索引和普通索引呢？

其实这里考察的一个知识点是 change buffer。那change buffer又是个什么东西呢？

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，暂时不去更新数据也，这样就不需要从磁盘中读入这个数据页了。

那什么时候去更新呢？在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。

除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

那么，什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer用的是buffer pool里的内存，因此不能无限增大，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置，这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。

索引一定会走到最优索引么？

不是的。比如如果走A索引要扫描100行，B索引只要20行，但是他可能选择走A索引，为什么呢？

一般走错都是因为优化器在选择的时候发现，走A索引没有额外的代价，比如走B索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。他发现走A索引不需要回表，没有额外的开销，所有他选错了。

三、聚集索引与非聚集索引

聚合索引与非聚合索引是一种存储方式，而不是一种单独的索引类型。

在数据库中，B+树的高度一般都在2~4层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，2~4次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树索引可以分为聚集索引（clustered index）和非聚集索引（secondary index）。

按照索引的键是否为主键分类 主索引 和 辅助索引，一般情况下主键就是聚集索引。

3.1 聚集索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引，但该索引可以包含多个列（组合索引），定义聚集索引键时使用的列越少越好。

一般在主键上创建聚集索引，当然也可以在其他列创建。所以聚集索引有主索引、辅助索引：

主索引，叶子节点存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，所以索引的顺序和数据本身的顺序就是相同的；
辅助索引，叶子节点存储的是主键的值，而不是数据的物理地址。由于辅助索引存储的是主键键值，因此按照辅助索引搜索的时候需要检索两遍，第一遍找到对应的主键，第二遍在主索引到达叶子节点中找到数据

InnoDB存储引擎需要聚集索引，因为在创建表时，InnoD 存储引擎会生成一个 .frm文件存储表结构定义，而 Myisam 存储引擎会生成三个文件：.frm（存存储定义）、.MYD（存储数据）、.MYI（存储索引）。InnoDB 的表数据与索引数据是存储在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。

InnoDB如何选择聚集索引呢？

如果定义了主键，那么主键就作为聚集索引；
如果没有定义主键，那么会选择该表第一个唯一非空索引作为聚集索引；
如果没有定义主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增，该列和列值供内部使用，用户不能查看或访问。。

3.2 非聚集索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

非聚合索引也有主索引和辅助索引（两个索引几乎一样），但是主索引不允许为空，现在就需要考虑在索引分类中介绍的非聚合索引的概念，由于物理顺序与索引顺序不同，因此每一个叶子节点存储的是指向键值对应的数据的物理地址（数据记录的地址）

非聚簇索引的数据表和索引表是分开存储的（如 Myisam 存储引擎），获取数据的方式是首先根据B+树获取索引，取出对应数据记录的地址，之后再去读取相应的数据记录。

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

如何避免非聚集索引的二次查询呢？可以使用覆盖索引（稍后介绍）。

聚集索引和非聚集索引使用场景
动作描述使用聚集索引使用非聚集索引
列经常被分组排序应应
返回某范围内的数据应不应
一个或极少不同值不应不应
小数目的不同值应不应
大数目的不同值不应应
频繁更新的列不应应
外键列应应
主键列应应
频繁修改索引列不应应

聚集索引和非聚集索引使用场景
动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

3.3 覆盖索引

InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录，换句话说查询列要被所建的索引覆盖。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的磁盘IO操作。

注意：覆盖索引技术最早是在InnoDB Plugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性

四、联合索引生效条件、索引失效条件

4.1 联合索引生效条件

联合索引遵循最左前缀原则。

假设联合索引由列(a,b,c)组成，则一下顺序满足最左前缀规则：a、ab、abc；

selece、where、order by 、group by都可以匹配最左前缀。其它情况都不满足最左前缀规则就不会用到联合索引。

eg:

(1) select * from table where a=3 and b=5 and c=4; ---- abc顺序

abc三个索引都在where条件里面用到了，而且都发挥了作用。

(2) select * from table where c=4 and b=6 and a=3;

where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样。

(3)  select * from table where a=3 and c=7;   ---- 没有b，中间断点，阻塞了c的索引

a用到索引，b没有用，所以c是没有用到索引效果的。

(4) select * from table where a=3 and b>7 and c=3; ---- b范围值，断点，阻塞了c的索引

a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引。

(5) select * from table where b=3 and c=4;   --- 联合索引必须按照顺序使用，并且需要全部使用

因为a索引没有使用，所以这里 bc都没有用上索引效果。

(6) select * from table where a>4 and b=7 and c=9; ---- a范围值，断点，阻塞了b c的索引

a用到了 b没有使用，c没有使用。

(7) select * from table where a=3 order by b;

a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的

(8) select * from table where a=3 order by c; ---- 没有b，中间断点，阻塞了c的索引

a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了。

(9） select * from table where b=3 order by a;   --- 需要全部使用

b没有用到索引，排序中a也没有发挥索引效果。

(10) select * from table where b=3 and c=2 order by a;

a b c都没有用到索引。

4.2 索引失效条件

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描；
存储引擎不能使用索引范围条件右边的列；
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *；
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描；
is null,is not null也无法使用索引；
like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。

问题：解决like‘%字符串%’时索引不被使用的方法？

字符串不加单引号索引失效

SELECT * from staffs where name='2000'; -- 因为mysql会在底层对其进行隐式的类型转换

SELECT * from staffs where name=2000; --- 未使用索引

五、索引方法

MySQL目前主要有以下几种索引方法：B-Tree，Hash，R-Tree，FULLTEXT。

5.1 B-Tree 索引

B-Tree是最常见的索引类型，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）。

B-Tree在MyISAM里的形式和Innodb稍有不同：

MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址

在 Innodb里，有两种形态：一是主索引（primary key），其叶子节点存储的是数据本身，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是辅助索引（secondary index），其l叶子节点存储的是主键的值，而不是数据的物理地址；

而在MyISAM里，主索和辅助索引并没有太大区别，叶子节点存储的是指向键值对应的数据的物理地址（数据记录的地址）。

5.2 Hash索引（Memory存储引擎）

1.仅支持"=","IN"和"<=>"精确查询，不能使用范围查询：

由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。。

2.不支持排序：

由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

3.不能利用部分索引键查询：

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

4.在任何时候都不能避免表扫描：

Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

5.检索效率高：

hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

6.只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突太多时也会影响查找性能。

Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引。

5.3 R-Tree 索引

R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

5.4 FULLTEXT 索引

表示全文收索，在检索长文本的时候，效果最好，短文本建议使用普通索引Index，但是在检索的时候数据量比较大的时候，先将数据放入一个没有全局索引的表中，然后在用Create Index创建的Full Text索引，要比先为一张表建立Full Text然后在写入数据要快的很多。

不同的存储引擎支持的索引类型也不一样

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

六、B-Tree和B+Tree

6.1 B-Tree B树

B树是为了磁盘或其它存储设备而设计的一种多叉（下面你会看到，相对于二叉，B树每个内节点有多个分支，即多叉）平衡查找树。

B 树又叫平衡多路查找树。一棵m阶的B 树 (m叉树)的特性如下：

树中每个节点最多含有m个孩子（m>=2）；
根节点的孩子数[2, m]；（特殊情况：没有孩子的根节点，即根节点为叶子节点，整棵树只有一个根节点）
除根节点和叶子节点外，其它每个节点有[ceil(m / 2)，m]个孩子；（其中ceil(x)是一个取上限的函数）
所有叶子节点都出现在同一层，叶子节点不包含任何关键字信息(可以看做是外部接点或查询失败的接点，实际上这些节点不存在，指向这些节点的指针都为null)；
每个非终端节点中包含有n个关键字信息： (P1，K1，P2，K2，P3，......，Kn，Pn+1)。其中：

a) Ki (i=1...n)为关键字，且关键字按顺序升序排序K(i-1)< Ki。

b) Pi为指向子树根的节点，且指针P(i)指向子树所有节点的关键字均小于Ki，但都大于K(i-1)。

c) 关键字的个数n必须满足： [ceil(m / 2)-1]<= n <= m-1，即孩子数-1。

（图片来源：https://blog.csdn.net/baidu_35813686/article/details/84434404）

6.1.1 B树的插入

插入操作是指插入一条记录，即（key, value）的键值对。如果B树中已存在需要插入的键值对，则用需要插入的value替换旧的value。若B树不存在这个key，则一定是在叶子结点中进行插入操作。

根据要插入的key的值，找到叶子结点并插入；
判断当前结点key的个数是否小于等于m-1，若满足则结束，否则进行第3步；
以结点中间的key为中心分裂成左右两部分，然后将这个中间的key插入到父结点中，这个key的左子树指向分裂后的左半部分，这个key的右子支指向分裂后的右半部分，然后将当前结点指向父结点，继续进行第3步。(核心思想：找到中间值，提升为父节点，之后分裂为左右)

下面是往B树（4阶）中依次插入

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

演示动图：（来源：https://blog.csdn.net/weixin_42228338/article/details/97684517）

6.1.2 B树的删除

删除操作是指，根据key删除记录，如果B树中的记录中不存对应key的记录，则删除失败。

如果当前需要删除的key位于非叶子结点上，则用后继key（这里的后继key均指后继记录（下一个记录）的意思）覆盖要删除的key，然后在后继key所在的子支中删除该后继key。此时后继key一定位于叶子结点上，这个过程和二叉搜索树删除结点的方式类似。删除这个记录后执行第2步；
该结点key个数大于等于ceil(m/2)-1，结束删除操作，否则执行第3步；
如果兄弟结点key有富余（大于ceil(m/2)-1），则父结点中的key下移到该结点，兄弟结点中的一个key上移，删除操作结束；否则执行第3步；
如果兄弟结点key没有富余，将父结点中的key下移与当前结点及它的兄弟结点中的key合并，形成一个新的结点。原父结点中的key的两个孩子指针就变成了一个孩子指针，指向这个新结点。然后当前结点的指针指向父结点，重复上第2步。

有些结点它可能即有左兄弟，又有右兄弟，那么我们任意选择一个兄弟结点进行操作即可。

6.2 B+Tree B+树

B+树包含2种类型的结点：内部结点（也称索引结点）和叶子结点。根结点本身即可以是内部结点，也可以是叶子结点。根结点的关键字个数最少可以只有1个。
有n棵子树的节点中含有n个关键字； (而B树是n棵子树有n-1个关键字)
所有的叶子结点中包含了全部关键字的信息，也就是说所有的关键字都在叶子节点出现；
B+树与B树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中；
内部结点中的关键字都按照从小到大的顺序排列，对于内部结点中的一个关键字，左树中的所有关键字都小于它，右子树中的关键字都大于等于它。叶子结点中的记录也按照关键字的大小排列；
每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。

（图片来源：https://blog.csdn.net/baidu_35813686/article/details/84434404）

6.2.1 B+树的插入

以5阶为例，5阶B+树的结点最少2个key，最多5个key。

若为空树，创建一个叶子结点，然后将记录插入其中，此时这个叶子结点也是根结点，插入操作结束；
叶子结点：由于在B+树中叶子结点实际保存了数据，如果当前节点小于等于m-1 (5-1 = 4)，直接插入；否则就将当前节点分解，左叶子结点包含前 floor(m/2 )个（2个），后floor(m/2)+1个（3个）记录到右节点，将第floor(m/2)+1个（3个）记录的key进位到父结点中（父结点一定是索引类型结点），进位到父结点的key左孩子指针向左结点，右孩子指针向右结点。将当前结点的指针指向父结点，然后执行第3步；
索引节点：若当前结点key的个数小于等于m-1（4），则插入结束。否则，将这个索引类型结点分裂成两个索引结点，左索引结点包含前floor(m/2)个key（2个），右结点包含floor(m/2)+1个key（3个），将第floor(m/2)+1key进位到父结点中，进位到父结点的key左孩子指向左结点,，进位到父结点的key右孩子指向右结点。将当前结点的指针指向父结点，然后重复第3步。

6.2.2 B+树的删除

以5阶为例，5阶B+树的结点最少2个key，最多5个key。

如果叶子结点中没有相应的key，则删除失败。否则执行下面的步骤。

删除叶子结点中对应的key：删除后若结点的key的个数大于等于ceil(m/2) – 1（>=2），删除操作结束，否则执行第2步；
若结点的key的个数小于ceil(m/2) – 1（<2），且兄弟结点key有富余（大于ceil(m/2)– 1）（>2），向兄弟结点借一个记录，同时用借到的key替换父结点（指当前结点和兄弟结点共同的父结点）中的key，删除结束。否则执行第3步；
若结点的key的个数小于ceil(m/2) – 1（<2），且兄弟结点中没有富余的key（小于ceil(m/2)– 1）（<2），则当前结点和兄弟结点合并成一个新的叶子结点，并删除父结点中的key，将当前结点指向父结点（必为索引结点），执行第4步（第4步以后的操作和B树就完全一样了，主要是为了更新索引结点）；
若索引结点的key的个数大于等于ceil(m/2) – 1（>=2），则删除操作结束。否则执行第5步；
若兄弟结点有富余，父结点key下移，兄弟结点key上移，删除结束。否则执行第6步；
当前结点和兄弟结点及父结点下移key合并成一个新的结点。将当前结点指向父结点，重复第4步；

注意，通过B+树的删除操作后，索引结点中存在的key，不一定在叶子结点中存在对应的记录。

6.3 B-Tree和B+Tree的对比

考虑磁盘IO的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，树越高，IO次数越多。

6.3.1 B-Tree 的优点

B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

6.3.2 B+Tree 的优点

所有的叶子结点使用链表相连，便于区间查找和遍历。B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好；
B+树的中间节点不保存数据，能容纳更多节点元素，而“矮胖”就是B+树的特征之一，IO读写次数减少。

6.3.3 B-Tree 和 B+Tree 的区别

B树种的同一键不会出现多次，可能在叶子节点上也可能在非叶子节点上；B+树的键一定会出现在叶子节点上，同时也可能在非叶子节点上重复出现。
B树的每个节点存储的是真实数据，会导致每个节点的存储的数据量变小，所以整个b树的高度会相对变高。随着数据量的变大，维护代价也增加；B+树的非叶子节点只存储键值，键值对应的具体数据都存储在叶子节点上，相对而言，一个非叶子节点存储的记录个数要比B树多的多。B树是纵向扩展，最终树的高度越来越高（高瘦子）； B+树是横向扩展，随着数据增加，会变成一个矮胖子。
B树的查询效率与键在B树的位置有关，在叶子节点的时候最大复杂度与B+树相同；B+树复杂度对某个建成的树是固定的。
B树的键的位置不固定并且整个树结构中只出现一次，增删改查操作复杂度增加；B+树种，非叶子节点对于叶子节点来说就像一个索引，增删改的时候只要找到键值（索引）的位置，再一层层的向下找即可，只有在遇到一个节点存储满了会对B+树分裂。
B树种所有的数据都只存储一份；B+树除存储了所有数据的叶子节点外，还要存储键值数据的非叶子节点。所以，B+树比B树会多占存储空间，多占的空间就是B+树的非叶子节点的所有空间。

七、SQL语句执行计划 explain

在工作中我们遇到一些执行时间比较长的SQL语句（慢日志中获取），通常会用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句是否用了索引，是否做全表扫描等。

注：在MySQL8.0之前数据库是存在缓存的，所以在执行SQL的时候，记得加上SQL NoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。

为什么缓存会失效，而且是经常失效？

如果我们当前的MySQL版本支持缓存而且我们又开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，一个请求会先去看缓存是否存在，不存在才会走解析器。

缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空。

大家如果是8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰。

怎么处理explain的结果预估的rows值跟实际情况差距比较大的问题呢？

用analyze table tablename 就可以重新统计索引信息，可以采用这个方法来处理。

expain出来的信息有10（12）列，分别是id、select_type、table、（partitions、）type、possible_keys、key、key_len、ref、rows、（filtered、）Extra，现在详细分析理解这都是什么东东。

mysql> EXPLAIN SELECT sql_no_cache * FROM `test`;

7.1 id 选择标识符

SELECT识别符。这是SELECT的查询序列号，SQL从大到小执行。

id相同时，执行顺序由上至下；
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

# 查看在研发部并且名字以Jef开头的员工，经典查询
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部';

7.2 select_type 表示查询的类型

显示查询中每个select子句的类型

SIMPLE：简单SELECT，不使用UNION或子查询等；
PRIMARY：子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY；
UNION：UNION中的第二个或后面的SELECT语句；
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询；
UNION RESULT：UNION的结果，union语句中第二个select开始后面所有select；
SUBQUERY：子查询中的第一个SELECT，结果不依赖于外部查询；
DEPENDENT SUBQUERY：子查询中的第一个SELECT，依赖于外部查询；
DERIVED：派生表的SELECT, FROM子句的子查询；
UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行；

7.3 table 输出结果集的表

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d；也可能是第几步执行的结果的简称，看到的是derivedX(X个是数字)，例如：


mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t1         | const  | PRIMARY,idx_t1_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

7.4 partitions 匹配的分区

显示匹配的分区。

7.5 type 表示表的连接类型

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有：ALL，index，range，ref，qe_ref，const，system，NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行；
index：Full Index Scan，index与ALL区别为index类型只遍历索引树；
range：只检索给定范围的行，使用一个索引来选择行；
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值；
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件；
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system；
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

7.6 possible_keys 表示查询时，可能使用的索引

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）。

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

7.7 key 表示实际使用的索引

显示MySQL实际决定使用的键（索引），必然包含在possible_keys中。

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7.8 key_len 索引字段的长度

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。

不损失精确性的情况下，长度越短越好。

7.9 ref 列与索引的比较

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

7.10 rows 扫描出的行数(估算的行数)

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

为什么不是精确的行数而是估算呢？

MySQL中数据的单位都是页，MySQL又采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

我们数据是一直在变的，所以索引的统计信息也是会变的，会根据一个阈值，重新做统计。

7.11 filtered 按表条件过滤的行百分比

查询的表行占表的百分比。

7.12 Extra 执行情况的描述和说明

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤；
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by 、order by；
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”；
Using join buffer：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能；
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）；
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行；
No tables used：Query语句中使用from dual 或不含任何from子句（如：EXPLAIN SELECT VERSION();）。

7.13 总结：

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

八、MySQL慢查询

8.1 概念

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句

MySQL的慢查询（慢查询日志）是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10S以上的语句。

默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

8.2 慢查询日志相关参数

MySQL 慢查询的相关参数解释：

slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭；

log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log；

slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log；

long_query_time：慢查询阈值，当查询时间大于设定的阈值时，记录日志；

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）；

log_output：日志存储方式。log_output='FILE' 表示将日志存入文件，默认值是'FILE'。log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

8.3 慢查询日志配置

8.3.1 查看是否开启慢查询

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的

可以用 show variables like '%slow_query_log%'; 查看：

slow_query_log = OFF，表示没有开启慢查询

slow_query_log_file 表示慢查询日志存放的目录

8.3.2 开启慢查询 slow_query_log slow_query_log_file

需要的时候才开启，因为很耗性能，建议使用即时性的。

方法一：（即时性的，重启mysql之后失效，常用的）

set global slow_query_log=1; 或者 set global slow_query_log=ON;

开启之后我们会发现 /var/lib/mysql下已经存在 localhost-slow.log了，未开启的时候默认是不存在的。

方法二：（永久性的）

在Linux系统中 /etc/my.cfg文件中的[mysqld]中加入（在Windows系统中一般是my.ini找到[mysqld]下面加上）：


slow_query_log=ON
slow_query_log_file=/var/lib/mysql/localhost-slow.log

然后重启MySQL服务器。

8.3.3 设置慢查询记录的时间 long_query_time

这个是由参数 long_query_time 控制，默认情况下 long_query_time 的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。

可以使用命令 show variables like 'long_query_time' 查看，默认是10秒钟，意思是大于10秒才算慢查询（等于10秒不会被记录）。

从MySQL 5.1开始，long_query_time 开始以微秒记录SQL语句运行时间，之前仅用秒为单位记录。

如果记录到表里面，只会记录整数部分，不会记录微秒部分。


mysql> show variables like 'long_query_time%';
 +-----------------+-----------+
 | Variable_name   | Value     |
 +-----------------+-----------+
 | long_query_time | 10.000000 |
 +-----------------+-----------+
 1 row in set (0.00 sec)
 
mysql> set global long_query_time=4;
 Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'long_query_time';
 +-----------------+-----------+
 | Variable_name   | Value     |
 +-----------------+-----------+
 | long_query_time | 10.000000 |
 +-----------------+-----------+
 1 row in set (0.00 sec)

如上所示，我修改了变量long_query_time，但是查询变量long_query_time的值还是10，难道没有修改到呢？

注意：使用命令 set global long_query_time=4修改后，需要重新连接或新开一个会话才能看到修改值。用show variables like 'long_query_time'查看是当前会话的变量值。

8.3.4 设置慢查询存储的方式 log_output

log_output='FILE' 表示将日志存入文件，默认值是'FILE'。

log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。

MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。


mysql> show variables like '%log_output%';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | log_output    | FILE  |
 +---------------+-------+
 1 row in set (0.00 sec)
 
mysql> set global log_output='TABLE';
 Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%log_output%';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | log_output    | TABLE |
 +---------------+-------+
 1 row in set (0.00 sec)
 
mysql> select sleep(5) ;
 +----------+
 | sleep(5) |
 +----------+
 |        0 |
 +----------+
 1 row in set (5.00 sec)
 
mysql>
 
mysql> select * from mysql.slow_log;
 +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
 | start_time          | user_host                 | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text        | thread_id |
 +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
 | 2016-06-16 17:37:53 | root[root] @ localhost [] | 00:00:03   | 00:00:00  |         1 |             0 |    |              0 |         0 |         1 | select sleep(3) |         5 |
 | 2016-06-16 21:45:23 | root[root] @ localhost [] | 00:00:05   | 00:00:00  |         1 |             0 |    |              0 |         0 |         1 | select sleep(5) |         2 |
 +---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+-----------------+-----------+
 2 rows in set (0.00 sec)

8.3.5 系统变量 log-queries-not-using-indexes

未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的SQL 也会被记录到慢查询日志。


mysql> show variables like 'log_queries_not_using_indexes';
 +-------------------------------+-------+
 | Variable_name                 | Value |
 +-------------------------------+-------+
 | log_queries_not_using_indexes | OFF   |
 +-------------------------------+-------+
 1 row in set (0.00 sec)
 
mysql> set global log_queries_not_using_indexes=1;
 Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'log_queries_not_using_indexes';
 +-------------------------------+-------+
 | Variable_name                 | Value |
 +-------------------------------+-------+
 | log_queries_not_using_indexes | ON    |
 +-------------------------------+-------+
 1 row in set (0.00 sec)

8.3.6 系统变量 log_slow_admin_statements

是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志。


mysql> show variables like 'log_slow_admin_statements';
 +---------------------------+-------+
 | Variable_name             | Value |
 +---------------------------+-------+
 | log_slow_admin_statements | OFF   |
 +---------------------------+-------+
 1 row in set (0.00 sec)

8.3.7 系统变量 Slow_queries

查询有多少条慢查询记录。


mysql> show global status like '%Slow_queries%';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | Slow_queries  | 2104  |
 +---------------+-------+
 1 row in set (0.00 sec)

8.3.8 系统变量 `log_slow_slave_statements`

默认从服务器不会将复制的查询写入慢速查询日志。

8.3.9 系统变量 `--log-short-format`

如果使用--log short format选项，服务器将向慢速查询日志写入较少的信息。

8.4 日志分析工具 mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow。

查看mysqldumpslow的帮助信息：


[root@DB-Server ~]# mysqldumpslow --help
 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
 
Parse and summarize the MySQL slow query log. Options are
 
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
 
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default（排序方式）
                 al: average lock time（平均锁定时间）
                 ar: average rows sent（平均返回记录数）
                 at: average query time（平均查询时间）
                  c: count（访问计数）
                  l: lock time（锁定时间）
                  r: rows sent（返回记录）
                  t: query time（查询时间）
   -r           reverse the sort order (largest last instead of first)
   -t NUM       just show the top n queries（返回前面n条数据）
   -a           don't abstract all numbers to N and strings to 'S'
   -n NUM       abstract numbers with at least n digits within names
   -g PATTERN   grep: only consider stmts that include this string（正则匹配模式，大小写不敏感）
   -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
                default is '*', i.e. match all
   -i NAME      name of server instance (if using mysql.server startup script)
   -l           don't subtract lock time from total time

部分参数详解：

-s：表示排序方式；

al：平均锁定时间；ar：平均返回记录数；at：平均查询时间；c：访问计数；l：锁定时间；r：返回记录；t：查询时间。

-r：表示倒序；
-t：表示 top n 的意思，返回前n条记录；
-g：后边可以写一个正则匹配模式，大小写不敏感的。

案例：

1.取出耗时最长的两条SQL：

格式：mysqldumpslow -s t -t 2 慢日志文件

mysqldumpslow -s t -t 2 /var/lib/mysql/localhost-slow.log

参数分析：

Count:出现次数；
Time:执行最长时间(累计总耗费时间)；
Lock:等待锁的时间；
Rows:发送给客户端的行总数(扫描的行总数)；
用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示)。

2.取出查询次数最多，且使用了in关键字的1条SQL：

mysqldumpslow -s c -t 1 -g 'in' /var/lib/mysql/localhost-slow.log；

3.得到返回记录集最多的10个SQL：

mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log

4.得到访问次数最多的10个SQL：

mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log

5.得到按照时间排序的前10条里面含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/localhost-slow.log

另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

8.5 show profile

Show Profile 是 mysql 提供的可以用来分析当前会话中SQL语句执行的资源消耗情况的工具。可用于SQL调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。

8.5.1 查看是否开启profile，mysql默认是不开启的，因为开启很耗性能

show variables like 'profiling%';

8.5.2 开启profile（会话级别的，关闭当前会话就会恢复原来的关闭状态）

set profiling=1; 或者 set profiling=ON;

8.5.3 关闭profile

set profiling=0; 或者 set profiling=OFF;

8.5.4 显示当前执行的语句和耗时

show profiles;

8.5.5 显示当前查询语句执行的时间和系统资源消耗

上图中显示Query_ID为4的SQL查询的时间很慢，我们就来看看这条SQL语句的执行情况。

show profile cpu,block io for query 4;（分析show profiles中query_id等于4的sql所占的CPU资源和IO操作）

或者直接： show profile for query 4;

8.5.6 show profile的常用查询参数

ALL：显示所有的开销信息。
BLOCK IO：显示块IO开销。
CONTEXT SWITCHES：上下文切换开销。
CPU：显示CPU开销信息。
IPC：显示发送和接受开销信息。
MEMORY：显示内存开销信息。
PAGE FAULTS：显示页面错误开销信息。
SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
SWAPS：显示交换次数开销信息。

8.5.7 需要注意的参数

converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
locked。

show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

8.5.8 总结

show profile默认是关闭的，并且开启后只存活于当前会话，也就说每次使用前都需要开启。如果永久开启，修改配置文件。
通过show profiles查看sql语句的耗时时间，然后通过show profile命令对耗时时间长的sql语句进行诊断。
注意show profile诊断结果中出现相关字段的含义，判断是否需要优化sql语句。
可更多的关注MySQL官方文档，获取更多的知识。

九、写在最后的话

感谢以下各位博主，小弟是站在巨人的肩膀上，参考各位博主的文章加上自己的理解，才能总结出来这篇文章，如有错误，欢迎指出更正。另外，本篇文章不做任何商业用途，仅方便自己日后查阅。同时也希望能够帮助其他小伙伴~

https://blog.csdn.net/qq_32679835/article/details/94166747 （索引分类，聚集索引、非聚集索引）

https://blog.csdn.net/qq_35275233/article/details/87888809 （mysql 联合索引生效的条件、索引失效的条件）

https://www.cnblogs.com/xinruyi/p/11335535.html （索引方法）

https://www.jb51.net/article/140749.htm （不同的存储引擎支持的索引类型）

https://blog.csdn.net/endlu/article/details/51720299（B树、B+树特征）

https://www.cnblogs.com/nullzx/p/8729425.html （B树、B+树插入、删除操作）

https://www.cnblogs.com/tufujie/p/9413852.html （SQL语句执行计划）

https://www.cnblogs.com/xuanzhi201111/p/4175635.html （SQL语句执行计划）

https://blog.csdn.net/qq_40884473/article/details/89455740 （mysql慢查询）

https://www.jianshu.com/p/13311c49bc97 （mysql 慢查询）

https://www.cnblogs.com/kerrycode/p/5593204.html（mysql慢查询）

https://www.cnblogs.com/FondWang/p/12195567.html （show profile）

https://mp.weixin.qq.com/s/e0CqJG2-PCDgKLjQfh02tw （change buffer）

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小舞很执着/article/detail/1006637