MySQL高级-索引是个什么东西？explain到底怎么用-MySQL查询优化大全

谁借莪１个温暖的怀抱￠ 2022-10-27 13:43 216阅读 0赞

**目录**

一、引出问题-MySQL的查询优化：

二、性能下降的原因：

三、索引到底是什么？怎么用？

1.索引操作

查看索引：

删除索引：

创建索引：

说明：

索引命名规范：

2.索引优势：

3.索引劣势：

4.索引分类：

5.哪些情况需要创建索引：

6.哪些情况不要创建索引：

7.mysql都有哪几种索引：

8.BTree索引是怎么存储、查找的：

9.索引到底是怎么工作的：

10.哪些情况下会造成索引失效：

四、怎么知道查询语句是否走了索引-explain到底是什么？（重头戏来了）

1.Explain（查看执行计划）：

2.explain到底是做什么的？

五、explain的用法（重中之重头戏）

explain的十列表头信息：

初始建表语句

1.id

2.select\_type

3.table

4.type（重要）

5.possible\_keys

6.key

7.key\_len:

8.ref

9.rows

10.Extra

--------------------

# 一、引出问题-MySQL的查询优化： #

相信很多刚入职的新人，经常会听到老员工说“看看怎么优化这个sql”、“这条select走没走索引”。。。

甚至更多地老员工，在面对跳槽面试的时候，面试官问及“mysql查询优化你是怎么做的”。

这篇文章，让你彻底明白，我们作为一个java程序员，是**如何做MySQL的查询优化的**！

# 二、性能下降的原因： #

性能下降sql慢的原因：（1.执行时间长；2.等待时间长）

查询语句写的差（自身问题）；

索引失效（单值索引或复合索引失效）；

关联查询太多join（设计缺陷或不得已的需求）

服务器调优及各个参数设置（缓冲、线程数等）

# 三、索引到底是什么？怎么用？ #

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于**图书的目录**，可以根据目录中的页码快速找到所需的内容。

简而言之：索引就是为了增加**查询和排序**的速度（就像图书的目录一样）！

一般来说索引本身也很大不可能全部存储在内存中，因此索引往往**以索引文件的形式存储在磁盘上**。

我们平常说的索引，如果没有特别指明，都是B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。

具体什么是B树，什么是B+树，以后再开博文细细来讨论~

## 1.索引操作 ##

### 查看索引： ###

SHOW INDEX FROM tb\_stu\_info2;

SHOW INDEX FROM tb\_stu\_info2\\G

### 删除索引： ###

DROP INDEX 索引名 ON 表名;

ALTER TABLE 表名 DROP INDEX 索引名;

### 创建索引： ###

ALTER TABLE 表名 ADD \[UNIQUE | FULLTEXT | SPATIAL\]  INDEX | KEY  \[索引名\] (字段名1 \[(长度)\] \[ASC | DESC\]) \[USING 索引方法\]；

CREATE  \[UNIQUE | FULLTEXT | SPATIAL\]  INDEX  索引名 ON  表名(字段名) \[USING 索引方法\]；

### 说明： ###

UNIQUE:可选。表示索引为唯一性索引。

FULLTEXT:可选。表示索引为全文索引。

SPATIAL:可选。表示索引为空间索引。

INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。

索引名:可选。给创建的索引取一个新名称。

字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。

长度:可选。指索引的长度，必须是字符串类型才可以使用。

ASC:可选。表示升序排列。

DESC:可选。表示降序排列

### 索引命名规范： ###

idx\_user\_name（user表的name索引）

idx\_user\_nameAge（user表的name、age）

## 2.索引优势： ##

增加查询、排序速率。

## 3.索引劣势： ##

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

## 4.索引分类： ##

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。

唯一索引：索引列的值必须唯一，但允许有空值。

复合索引：即一个索引包含多个列。

## 5.哪些情况需要创建索引： ##

1.主键自动建立唯一索引。

2.频繁作为查询条件的字段应该创建索引。

3.查询中与其他表关联的字段，外键关系建立索引。

4.频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引）

5.where条件里用不到的字段不创建索引。

6.单键/组合索引的选择问题？（在高并发下倾向创建组合索引）

7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

8.查询中统计或者分组字段。

## 6.哪些情况不要创建索引： ##

1.表记录太少。

2.经常增删改的表。（提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）

3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值得分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0/99。一个索引的选择性越接近于1，这个索引的效率就越高。

## 7.mysql都有哪几种索引： ##

**BTree索引（多）**、Hash索引、full-text全文索引、R-Tree索引。

## 8.BTree索引是怎么存储、查找的： ##

（略...等待后续单独出一篇文章）

## 9.索引到底是怎么工作的： ##

（略...等待后续单独出一篇文章）

## 10.哪些情况下会造成索引失效： ##

（略...等待后续单独出一篇文章）

# 四、怎么知道查询语句是否走了索引-explain到底是什么？（重头戏来了） #

## 1.Explain（查看执行计划）： ##

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

用法：**explain + sql**。

注意：**explain只能用于select语句**！update、delete语句想知道是否走了索引，需要将后面的where条件单独拿出来放到select语句中，再进行分析！

## 2.explain到底是做什么的？ ##

使用explain，能够清楚的知道：

表的读取顺序；

数据读取操作的操作类型；

哪些索引可以使用；

哪些索引被实际使用；

表之间的引用；

每张表有多少行被优化器查询。

# 五、explain的用法（重中之重头戏） #

（因为涉及内容太过庞大，其中有解释不清楚的，可以自行再单独百度搜索其中的一种类型，然后深究。）

## explain的十列表头信息： ##

![20210204161232640.png][]

## 初始建表语句 ##

CREATE TABLE `t1` (
      `id` int(11) DEFAULT NULL,
      `oname` varchar(20) DEFAULT NULL,
      `address` varchar(30) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    INSERT INTO `t1` VALUES ('1', '一中', '青岛');
    INSERT INTO `t1` VALUES ('2', '二中', '济南');
    
    
    CREATE TABLE `t2` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(20) DEFAULT NULL,
      `age` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    INSERT INTO `t2` VALUES ('1', '张三', '12');
    INSERT INTO `t2` VALUES ('2', '李四', '13');
    
    
    CREATE TABLE `t3` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(20) DEFAULT NULL,
      `phoneno` varchar(20) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    INSERT INTO `t3` VALUES ('1', '王五', '155');
    INSERT INTO `t3` VALUES ('2', '赵六', '134');

## 1.id ##

id标识select查询的序列号，包含一个数字，表示查询中执行select子句或操作表的顺序；

有三种情况：

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

EXPLAIN SELECT
    	t2.*
    FROM
    	t1
    LEFT JOIN t3 ON t3.id = t1.id
    LEFT JOIN t2 ON t2.id = t1.id
    WHERE
    	t3. NAME = '';

![2021020416431094.png][]

如上，三个表，mysql的执行顺序是t1-t3-t2。

2）如果查询中携带子查询，id序号会自增，id值越大越先被执行

EXPLAIN SELECT
    	t2.*
    FROM
    	t2
    WHERE
    	t2.id = (
    		SELECT
    			id
    		FROM
    			t1
    		WHERE
    			t1.id = (SELECT id FROM t3 WHERE t3.id = 1)
    	)

![20210204164736803.png][]

如上，三个表的执行顺序是t3-t1-t2。

3）id有相同的有不同的，先执行id大的，如果有id相同的，按照从上往下执行

EXPLAIN SELECT
    	t2.*
    FROM
    	(
    		SELECT
    			t1.id
    		FROM
    			t1
    		WHERE
    			t1.oname = '一中'
    	) s1
    LEFT JOIN t2 ON s1.id = t2.id

![20210204165550396.png][]

如上，执行顺序为t1-t2-<derived2>（注：<derived2>是id为2的查询生成的一张临时表，后面的2代表id值）。

## 2.select\_type ##

共有六种情况：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT。

SIMPLE：简单的select查询，查询中不包含子查询或者UNION。

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

SUBQUERY：在select或where列表中包含了子查询。

DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。

UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为：DERIVED。

UNION RESULT：从UNION表获取结果的select。

EXPLAIN SELECT
    	t1.id
    FROM
    	t1
    LEFT JOIN t2 ON t1.id = t2.id
    UNION
    	SELECT
    		t2.id
    	FROM
    		t2
    	LEFT JOIN t3 ON t2.id = t3.id

![2021020417195068.png][]

## 3.table ##

显示这一行的数据是关于哪张表的。

## 4.type（重要） ##

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。  
const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

此时无索引：
    explain select * from (select id from t1 where t1.id = 1) t2;

![20210205084802989.png][]

-- 将id列设置为主键
    ALTER TABLE t1 ADD PRIMARY KEY(id) ;
    -- 再执行一下
    explain select * from (select id from t1 where t1.id = 1) t2;

![20210205085234347.png][]  
eq\_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或位移索引扫描。  
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的结合体。（联合索引，使用其中的某几个索引就会是ref）

-- 给t1表的oname字段加上索引，再按照oname筛选看一下
    ALTER TABLE t1 ADD INDEX (oname);
    explain select * from t1 where t1.oname = '一中';

![20210205085429522.png][]  
fulltext：全文搜索  
ref\_or\_null：与ref类似，但包括NULL  
index\_merge：表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）  
unique\_subquery：在in子查询中，就是value in (select…)把形如select unique\_key\_column的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！  
index\_subquery：同上，但把形如”select non\_unique\_key\_column“的子查询替换  
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

explain select * from t1 where id in (1, 2);
    explain select * from t1 where id BETWEEN 1 and 2;

![20210205085547822.png][]  
index：Full Index Scan，index与All区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。（也就是说虽然all和index读全表，但index是从索引中读取的，而all是从硬盘中读取的）  
all：全表扫描(full table scan)

explain select oname from t1;

![20210205085705703.png][]

以下性能越来越慢：system > const > eq\_ref > ref > range > index > ALL

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

## 5.possible\_keys ##

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，**但不一定被查询实际使用**。

## 6.key ##

实际使用的索引。如果为NULL，则没有使用索引。

查询中若使用了复合索引，则该索引仅出现在key列表中。（这种情况，创建的复合索引的个数和顺序，与查询字段的个数和顺序一致）

## 7.key\_len: ##

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

key\_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key\_len是根据表定义计算而得，不是通过表内检索出的。

## 8.ref ##

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

## 9.rows ##

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要的读取的行数。（通常数字越小性能越高）

## 10.Extra ##

包含不适合在其他列中显示但十分重要的额外信息。

1.Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称之为“文件排序”（索引也跟排序有关系的）

2.Using temporary（用了一个内部临时表）：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

3.Using index（使用索引-性能非常好）：

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找；

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

4.Using where：表明使用了where过滤

5.Using join buffer：使用了连接缓存

6.impossible where：where子句的值总是false，不能用来获取任何元组。

7.select tables optimized away：在没有group by子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(\*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

8.distinct：优化distinct，在找到第一匹配的元组后即停止找同样值得动作。

[20210204161232640.png]: /images/20221024/f953786e0d7e488aa0b6ba89b423374d.png
[2021020416431094.png]: /images/20221024/16165b2a26534f7a9610696cb6958311.png
[20210204164736803.png]: /images/20221024/8840aff3990a4722911e6752de47caa7.png
[20210204165550396.png]: /images/20221024/ddb49eb6674c41cf9b3f47e52618fd90.png
[2021020417195068.png]: /images/20221024/5ff83524b5894ffeb72cdeb38945665a.png
[20210205084802989.png]: /images/20221024/a83590fae90348aab5af8464139f6081.png
[20210205085234347.png]: /images/20221024/55f2135438e24405b9ed1ea24f4579c0.png
[20210205085429522.png]: /images/20221024/873879b1d3bf457c87cdac1ccebb5f5c.png
[20210205085547822.png]: /images/20221024/1cdb6283d0414b0f86af4e17875fc651.png
[20210205085705703.png]: /images/20221024/3556e64577384bc38fa69feb371d9fd5.png