索引原理与慢查询优化

索引原理与慢查询优化

索引是存储引擎中一种数据结构，或者说数据的组织方式，又称之为键key，是存储引擎用于快速找到记录的一种数据结构。为数据建立索引就好比是为书建目录，或者说是为字典创建音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

使用索引的好处

一般的应用系统，读写比例在9:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的、也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

理解索引的储备知识

要了解索引的数据结构我来先来储备一些知识。

储备知识1：机械磁盘一次IO的时间

机械磁盘一次io的时间 = 寻道时间 + 旋转延迟 + 传输时间

# 寻道时间
道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下

# 旋转延迟
旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；

# 传输时间
传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计

所以访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右

这9ms对于人来说可能非常短，但对于计算机来可是非常长的一段时间，长到什么程度呢？
一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

储备知识2：磁盘的预读

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

储备知识3：索引原理精髓提炼

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

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

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

索引分类

索引模型分为很多种类

#===========B+树索引（等值查询与范围查询都快）
二叉树->平衡二叉树->B树->B+树
        
#===========HASH索引(等值查询快，范围查询慢)
将数据打散再去查询

#===========FULLTEXT：全文索引 （只可以用在MyISAM引擎）
通过关键字的匹配来进行查询，类似于like的模糊匹配
like + %在文本比较少时是合适的
但是对于大量的文本数据检索会非常的慢
全文索引在大量的数据面前能比like快得多，但是准确度很低
百度在搜索文章的时候使用的就是全文索引，但更有可能是ES

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

InnoDB存储引擎
支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索引。
MyISAM存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引。
Memory存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引。

因为mysql默认的存储引擎是innodb，而innodb存储引擎的索引模型/结构是B+树，所以我们着重介绍B+树，那么大家最关注的问题来了：

B+树索引到底是如何加速查询的呢？

索引的数据结构

常见的数据结构有5种：二叉查找树、平衡二叉树、B树以及B+树。

创建索引的两大步骤

为某个字段创建索引，即以某个字段的值为基础构建索引结构，那么如何构建呢？分为两大步骤

1、提取每行记录中该字段的值，以该值当作key，至于key对的value是什么？每种索引结构各不相同
2、然后以key值为基础构建索引结构

以后的查询条件中使用了该字段，则会命中索引结构。

案例：

# 1、为user表的id字段创建索引，会以每条记录的id字段值为基础生成索引结构
create index 索引名 on user(id);

使用索引
select * from user where id = xxx;

# 2、为user表的name字段创建索引，会以每条记录的name字段值为基础生成索引结构
create index 索引名 on user(id);

使用索引
select * from user where name = xxx;

那么索引的结构到底长什么样子，让其能够加速查询呢？

innodb存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的。

二叉查找树

二叉查找树（Binary Search Tree），也称有序二叉树（ordered binary tree）,排序二叉树（sorted binary tree），是指一棵空树或者具有下列性质的二叉树：

1. 若任意节点的左子树不空，则左子树上所有结点的值均小于它的根结点的值；
2. 若任意节点的右子树不空，则右子树上所有结点的值均大于它的根结点的值；
1. 任意节点的左、右子树也分别为二叉查找树。
2. 没有键值相等的节点（no duplicate nodes）。

有user表，我们以id字段值为基础创建索引。

1、提取每一条记录的id值作为key值，value为本行完整记录，即：

id	user
10	zs
7	ls
13	ww
5	zl
8	xw
12	xm
17	dy

2、以key值的大小为基础构建二叉树，如上图所示

二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。
顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找id=12的用户信息

select * from user where id=12;

利用我们创建的二叉查找树索引，查找流程如下：

将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。
继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。
把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=1>2,name=xm。

利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

平衡二叉树

基于5.1所示的二叉树，我们确实可以快速地找到数据。但是让我们回到二叉查找树地特点上，只论二叉查找树，它的特点只是：任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。所以，依据二叉查找树的特点，二叉树可以是这样构造的，如图

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。

为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度不能超过1。下面是平衡二叉树和非平衡二叉树的对比：

由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

那么是不是说基于平衡二叉树构建索引的结构就可以了呢？答案是否！

B树

那么直接用平衡二叉树这种数据结构来构建索引有什么问题？

首先，因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。所以，如果我们单纯用平衡二叉树这种数据结构作为索引的数据结构，即每个磁盘块只放一个节点，每个节点中只存放一组键值对，此时如果数据量过大，二叉树的节点则会非常多，树的高度也随即变高，我们查找数据的也会进行很多次磁盘IO，查找数据的效率也会变得极低！

综上，如果我们能够在平衡二叉的树的基础上，把更多的节点放入一个磁盘块中，那么平衡二叉树的弊端也就解决了。即构建一个单节点可以存储多个键值对的平衡树，这就是B树。

注意：

1、图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，因为图的美观性，被省略了。
2、图中的每个节点里面放入了多组键值对，一个节点也称为一页，一页即一个磁盘块，在mysql中数据读取的基本单位都是页，即一次io读取一个页的数据，所以我们这里叫做页更符合mysql中索引的底层数据结构。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：

1、先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。
2、将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。
3、将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

注意：

1、B树的构造是有一些规定的，但这不是本文的关注点，有兴趣的同学可以令行了解。
2、B树也是平衡的，当增加或删除数据而导致B树不平衡时，也是需要进行节点调整的。

那么B树是否就是索引的最终结构了呢？答案是no，B树只擅长做等值查询，而对于范围查询（范围查询的本质就是n次等值查询），或者说排序操作，B树也帮不了我们

select * from user where id=3;  -- 擅长
select * from user where id>3;  -- 不擅长

上帝说要有光，于是有了B+树。

B+树

B+树是对B树的进一步优化。让我们先来看下B+树的结构图：

根据上图我们来看下B+树和B树有什么不同。

B+树非叶子节点non-leaf node上是不存储数据的，仅存储键，而B树的非叶子节点中不仅存储键，也会存储数据。B+树之所以这么做的意义在于：树一个节点就是一个页，而数据库中页的大小是固定的，innodb存储引擎默认一页为16KB，所以在页大小固定的前提下，能往一个页中放入更多的节点，相应的树的阶数（节点的子节点树）就会更大，那么树的高度必然更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。
B+树的阶数是等于键的数量的，例如上图，我们的B+树中每个节点可以存储3个键，3层B+树存可以存储333=9个数据。所以如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。而一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO，真是屌炸天的设计。
因为B+树索引的所有数据均存储在叶子节点leaf node，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。而且B+树中各个页之间也是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引（聚集索引和非聚集索引下面会讲到）。

通过上图可以看到，在innodb中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

索引管理

\1. 索引的功能就是加速查找

\2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

MySQL常用的索引分类

我们可以在创建上述索引的时候，为其指定索引类型，主要有以下几类

#===========B+树索引（innodb存储引擎默认）

聚集索引：即主键索引，PRIMARY KEY
    用途：
        1、加速查找
        2、约束（不为空、不能重复）

唯一索引：UNIQUE
    用途：
        1、加速查找
        2、约束（不能重复） 

普通索引INDEX：
    用途：
        1、加速查找

联合索引： 
    PRIMARY KEY(id,name):联合主键索引 
    UNIQUE(id,name):联合唯一索引 
    INDEX(id,name):联合普通索引

#===========HASH索引(查询单条快，范围查询慢)
将数据打散再去查询
Innodb和Myisam都不支持，设置完还是Btree
memery存储引擎支持


#===========FULLTEXT：全文索引 （只可以用在MyISAM引擎）
通过关键字的匹配来进行查询，类似于like的模糊匹配
like + %在文本比较少时是合适的
但是对于大量的文本数据检索会非常的慢
全文索引在大量的数据面前能比like快得多，但是准确度很低
百度在搜索文章的时候使用的就是全文索引，但更有可能是ES

#===========RTREE：R树索引
RTREE在mysql很少使用，仅支持geometry数据类型
geometry数据类型一般填写经纬度那样的数据
支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
RTREE范围查找很强，但Btree也不弱.

#不同的存储引擎支持的索引类型也不一样
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 等索引；

创建/删除索引的语法

#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             
#删除索引：
        DROP INDEX 索引名 ON 表名字;
        alter table country drop index 索引名字;

#查看索引
方法一：
mysql> desc t1;
+-----+
| Key |
+-----+
| PRI | 主键索引
| MUL | 普通索引
| UNI | 唯一键索引
+-----+

方法二：
mysql> show index from t1;

示例

# 1）主键索引(聚集索引)
#创建主键索引
mysql> alter table student add primary key pri_id(id);
mysql> create table student(id int not null, primary key(id)); 
mysql> create table student(id int not null primary key auto_increment comment '学号');

#提示：
database可以写 schema
index可以写 key


# 2）唯一键索引
#创建唯一建索引
mysql> alter table country add unique key uni_name(name);
mysql> create table student(id int unique key comment '学号');
mysql> create unique key index index_name on table_name(id);


# 3）普通索引(辅助索引)
#普通索引的创建
mysql> alter table student add index idx_gender(gender);
CREATE INDEX index_name ON table_name (column_list);


# 4）创建前缀索引
按照该列数据的前n个字母创建索引
mysql> alter table student add index idx_name(name(4));


# 5）全文索引
#针对content做了全文索引：
CREATE TABLE t1 (
id int NOT NULL AUTO_INCREMENT,
title char(255) NOT NULL,
content text,
PRIMARY KEY (id),
FULLTEXT (content));

查找时：
select * from table where match(content) against('想查询的字符串');

测试索引

测试索引的主要是为了测试出索引的性能。

准备

#1. 准备表
create table s1(
  id int,
  name varchar(20),
  gender char(6),
  email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'shanhe','male',concat('shanhe',i,'@helloworld'));
        set i=i+1;
        select concat('shanhe',i,'_ok');
    end while;
END$$ #$$结束
delimiter ;

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1()

在没有索引的前提下测试查询速度

#无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢
mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)

在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢

在索引建立完毕后，以该字段为查询条件时，查询速度提升明显

总结

#1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型。

正确的使用索引

并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题。

范围问题

条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like。

大于号、小于号

不等于！=

between ...and...

%或者_应该放在右边，并且左边的字符应该尽可能地精确一些，这样锁定的范围才会小一些，例如like "shanhe3%"而不是like "%shanhe3"、like "shan%"

查询优化神器-explain

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

执行计划：让mysql预估执行操作(一般正确)
    all < index < range < index_merge < ref_or_null < ref < eq_ref < const < system < NULL
    
    慢：
        select * from userinfo3 where name='alex'
        
        explain select * from userinfo3 where name='alex'
        type: ALL(全表扫描)
            select * from userinfo3 limit 1;
    快：
        select * from userinfo3 where email='alex'
        type: const(走索引)

各个字段详解

1. id：

包含一组数字，表示查询中执行select子句或操作表的顺序

Example（id相同，执行顺序由上至下）

如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2. select_type

示查询中每个select子句的类型（简单OR复杂）

a. SIMPLE：查询中不包含子查询或者UNION
b. 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c. 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d. 在FROM列表中包含的子查询被标记为：DERIVED（衍生）用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的
e. 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f. 从UNION表获取结果的SELECT被标记为：UNION RESULT

SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。
DEPENDENT意味着select依赖于外层查询中发现的数据。
UNCACHEABLE意味着select中的某些 特性阻止结果被缓存于一个item_cache中。

第一行：id列为1，表示第一个select，select_type列的primary表 示该查询为外层查询，table列被标记为<derived3>，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。
第二行：id为3，表示该查询的执行次序为2（ 4 => 3），是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。
第三行：select列表中的子查询，select_type为subquery，为整个查询中的第二个select。
第四行：select_type为union，说明第四个select是union里的第二个select，最先执行。
第五行：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。

3、 type   

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

常用的类型有： ALL, index,  range, ref, eq_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在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

4、table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

5、possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

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

6、Key      key列显示MySQL实际决定使用的键（索引）

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

7、key_len

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

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

8、ref

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

9、rows

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

10、Extra

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

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。

explain字段详解

例1：type为All，代表全部扫描

#1.什么是全表扫描
在explain语句结果中type为ALL，MySQL将遍历全表以找到匹配的行

#2.什么时候出现全表扫描?
1.业务确实要获取所有数据
    mysql> explain select * from city;
    
#2.不走索引导致的全表扫描
#2.1 没索引
mysql> explain select * from city where District='shanghai';

创建索引后速度会提升，变成索引扫描了
mysql> alter table city add index idx_District(District);
mysql> explain select * from city where District='shanghai';

#2.2 索引创建有问题
#2.3 语句有问题

例2：索引扫描

#常见的索引扫描类型:
1）index 全索引扫描
index与ALL区别为index类型只遍历索引树（性能也不高）
mysql> explain select population from city;

2）range    范围查询
只检索给定范围的行，使用一个索引来选择行，一般来说，SQL语句只要达到range级别，就可以了
mysql> explain select * from city where countrycode in ('USA','CHN');

3）ref 精确查找，匹配条件有普通索引
先给population列一个索引
mysql> alter table city add index idx_population(population);
然后查询
mysql> explain select * from city where population=300000000;

4）eq_ref    类似ref
区别就在使用的索引是唯一索引
explain select city.name,city.countrycode,country.name  from city join country on city.countrycode=country.code where city.population<100;

5）const 使用的索引是主键索引
mysql> explain select * from country where code='CHN';

6）system system是const类型的特例,当查询的表只有一行的情况下
mysql> delete from country where code != 'CHN';
有外键删除不了

7）null 执行过程不用访问表或索引，直接选取索引列的最小值
mysql> explain select min(population) from city;

慢日志管理

1、将MySQL服务器中影响数据库性能的相关SQL语句记录到日志文件中

2、通过对这些特殊的SQL语句进行分析和改进，提高数据库的性能。

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

1、配置慢日志

vim /etc/my.cnf

#开启慢查询
slow_query_log = on
#慢查询中记录没有使用索引的query
log_queries_not_using_indexes=on
#返回较慢的日志mysql5.6版本以上，取消了参数log-slow-queries，更改为slow-query-log-file 
slow_query_log_file=/tmp/slow.log
#慢查询时间,这里为2秒,超过2秒会被记录
long_query_time=0.05

查看是否开启

mysql> show variables like '%slow_query_log%';
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| slow_query_log      | ON            |
| slow_query_log_file | /tmp/slow.log |
+---------------------+---------------+
2 rows in set (0.00 sec)

测试慢日志

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

root@756ad135dd2e:/tmp# cat slow.log 
# Time: 2021-09-28T16:15:29.967489Z
# User@Host: skip-grants user[root] @ localhost []  Id:     2
# Query_time: 2.000316  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1632845729;
select sleep(2);

慢查询优化的基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

innodb存储引擎索引知识小节

InnoDB存储引擎支持两种常见的索引：B+树索引、Hash索引。

B+树索引是目前关系型数据库系统中最常见、最有效的索引。

B+树中的B代表的不是二叉（binary），而是平衡（balance），所以，B+树是平衡树并不是二叉树。

B+树索引能找到的只是被查找数据行所在的页。然后数据库把页读入内存，再从内存中进行查找，最后得到要查找的数据。

在数据库中，B+树的高度一般在2-3层，也就意味着在查找某一个键所对应的值的时候，大概需要2-3次IO。

数据库中的B+树索引分为聚集索引和非聚集索引（辅助索引）

聚集索引就是按照每张表的主键构造一个B+树，B+树的一个叶子几点中记录着表中一行记录的所有值。只要找到这个叶子节点也就得到了这条记录的所有值。

辅助索引的叶节点中不包含行记录的所有值。只包含一个键值和一个书签（bookmark），书签用于定位与索引对应的数据行。

每张表只能有一个聚集索引，可以有多个辅助索引。

辅助索引通过叶级别的指针获得指向主键的索引，然后再通过聚集索引定位数据行。

对于索引的添加或删除操作，MySql数据库会先创建一张临时表，然后把数据导入临时表中，删除原表，再把临时表名改为原来的表名。所以，增加和删除索引有成本。

当某个字段的取值分布范围比较广的时候（高选择性）适合使用B+树索引。如果某字段只有Y和N两个取值，那么没必要使用索引。

如果要查询的字段具有高选择性，但是本次检索的数据占总数据量的一半以上时，MySql就不会使用索引进行查询。

联合索引是指对表上的多个列做索引。

如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col3)和(col1,col2,col3)上建立了索引；这就是最左前缀原则。

搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

使用短索引。如果对字符串列进行索引，应该指定一个前缀长度,只要有可能就应 该这样做。例如，如果有一个 CHAR(200)列,如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。

不要过度索引。

Hash只用于使用=或<=>操作符的等式比较。

B+树索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者 LIKE ‘pattern'(其
中’pattern’不以通配符开始)操作符时，都可以使用相关列上的索引。

索引原理与慢查询优化

索引原理与慢查询优化

使用索引的好处

理解索引的储备知识

储备知识1：机械磁盘一次IO的时间

储备知识2：磁盘的预读

储备知识3：索引原理精髓提炼

索引分类

索引的数据结构

创建索引的两大步骤

二叉查找树

平衡二叉树

B树

B+树

索引管理

MySQL常用的索引分类

创建/删除索引的语法

测试索引

准备

在没有索引的前提下测试查询速度

在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢

在索引建立完毕后，以该字段为查询条件时，查询速度提升明显

总结

正确的使用索引

范围问题

大于号、小于号

不等于！=

between ...and...

查询优化神器-explain

各个字段详解

慢日志管理

1、配置慢日志

查看是否开启

测试慢日志

慢查询优化的基本步骤

innodb存储引擎索引知识小节

推荐阅读