「MySQL高级篇」explain分析SQL，索引失效&&常见优化场景-星语课程网

返回
顶部

修改密码

* 旧密码：

* 新密码：

* 确认新密码：

首页 > 知识摘要 > 开发技术 > 正文

「MySQL高级篇」explain分析SQL，索引失效&&常见优化场景

2024-12-16 22:02:41

来源：本站编辑

阅读量：

in('2')就会走索引MySQL在**对查询结果排序**时使用临时表此时需要保证**所查询的字段**都是**索引字段**范围查询**之后**的索引字段当查询列中包含了非索引项使用 is null的时候不走索引（还不如全表扫描快）即不使用表连接或者子查询）、PRIMARY（主查询但是 select 中没有使用**覆盖索引**尽量使用**覆盖索引**（索引列完全包含查询列）直接能在索引列完成查询说明mysql会对数据使用一个**外部**的索引排序我们此处有一个id主键索引则使用 is not n

[TOC]

#【一、explain】分析SQL
==================
![](/u/cms/www/202412/08203657vxpg.png)

explain中，包含了如下几个字段（不同版本可能会有所差异）：

|字段   | 含义  |
| ------------ | ------------ |
| id  |  select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。 |
| select\_type  |表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等   |
| table  | 输出结果集的表  |
|partitions   | 查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。  |
|  type |  表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq\_ref ------> ref -------> ref\_or\_null----> index\_merge ---> index\_subquery -----> range -----> index ------> all ) |
|possible\_keys   | 表示查询时，**可能使用**的索引  |
| key  | 表示查询时，**实际使用**的索引  |
|  key\_len |索引字段的长度，可用来区分长短索引   |
|rows   |   扫描行的数量|
|filtered|表里符合条件的记录数所占的百分比|
|extra|执行情况的说明和描述|

> 看完是不是很懵，感觉好多要记忆的，别着急，下边我们通过实际案例，来加深记忆

id
--

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。  
id 情况有三种 ：  
1.  此处只是单表查询，id只有一个
![](/u/cms/www/202412/08203817f1zm.png)
2.  id一样，则从上到下
![](/u/cms/www/202412/08203858yys0.png)
3.  id不同，则id值越大，优先级越高
> 此处是嵌套子查询，最内部的子查询，自然是最先执行的
![](/u/cms/www/202412/08203940gnp4.png)
### 简而言之：

*   id值越大，优先级越高；
*   id值一样，则从上到下；
select\_type
--
| select\_type  | 含义  |
| ------------ | ------------ |
| SIMPLE  |  简单的select查询，查询中不包含子查询或者UNION |
|  PRIMARY | 查询中若包含任何复杂的子查询，最外层查询标记为该标识  |
| SUBQUERY|在SELECT 或 WHERE 列表中包含了子查询 |
|DERIVED|在**FROM 列表中包含的子查询**，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中|
|UNION|若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED|
|UNION RESULT|从UNION表获取结果的SELECT|
------------
### PRIMARY,SUBQUERY
![](/u/cms/www/202412/08205744l983.png)

### DERIVED(需要临时表，自然比上述效率低)
![](/u/cms/www/202412/08205811gooi.png)

| type  | 含义  |
| ------------ | ------------ |
| NULL  |  MySQL不访问任何表，索引，直接返回结果 |
|  system | 表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现  |
|const|表示通过**索引**一次就找到了，const 常用于**primary key 或者 unique 索引(本质上都是唯一索引)**。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较|
|eq\_ref|类似ref，区别在于使用的是**唯一索引**，使用主键的**关联查询**，关联查询出的记录只有一条。常见于主键或唯一索引扫描|
|ref|**非唯一性索引**扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）|
|range|只检索给定返回的行，使用一个**索引**来选择行。 where 之后出现 between ， < , > , in 等操作。|
|index|index 与 ALL的区别为 index 类型只是**遍历了索引树**， 通常比ALL 快， ALL 是遍历数据文件。|
|all|将遍历全表以找到匹配的行|
----
结果值从最好到最坏以此是：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    
    
    system > const > eq_ref > ref > range > index > ALL
    
一般至少要达到range级别，最好达到ref 。

### const

**唯一**索引，非关联查询

### eq\_ref，ref

eq\_ref 跟 const 的区别是：两者都利用唯一索引，但前者是关联查询，后者只是普通查询？  
eq\_ref 跟 ref 的区别：后者是**非唯一索引**

### index，all

都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。  
![](/u/cms/www/202412/082040273z2d.png)
不走索引就会遍历全表  
![](/u/cms/www/202412/08204102muu9.png)

possible\_keys，key
------------------

possible\_keys : 显示可能应用在这张表的索引， 一个或多个。

key ：**实际使用**的索引， 如果为NULL， 则没有使用索引。（可能是没有走索引，需要分析）

key\_len : 表示索引中使用的字节数， 在不损失精确性的前提下， **长度越短越好 。**

*   单列索引，那么需要将整个索引长度算进去；
*   多列索引，不是所有列都能用到，需要计算查询中实际用到的列。
![](/u/cms/www/202412/0820413183wv.png)
ref
---

显示**索引的哪一列**被使用了，如果可能的话，是一个常数。

*   当使用常量等值查询，显示const
*   当关联查询时，会显示相应关联表的关联字段
*   如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func
*   其他情况为null

1.  id是索引，而且是id=1，一个常数，故ref = const
2.  user\_id不是索引，ref直接为null
![](/u/cms/www/202412/08204203xro1.png)
t1.id是索引，且=号后边不是常量，故显示t1.id，即显示相应关联表的关联字段  
![](/u/cms/www/202412/08204226jglv.png)
rows
----

> 扫描行的数量，一般越小越好

*   用索引 rows 就为1，**无论是唯一索引还是非唯一索引**
*   其他情况一般是全表扫描，rows等于表的行数。
![](/u/cms/www/202412/08204251303q.png)
filtered
--------

表里**符合条件**的记录数的所占的百分比。

extra
-----

其他的额外的执行计划信息，在该列展示 ，需要把前两个优化为using index。

|  EXTRA | 含义  |
| ------------ | ------------ |
|  using filesort | 说明mysql会对数据使用一个**外部**的索引排序，而不是按照表内的索引顺序进行读取，表示**无法利用索引**完成的排序操作， 称为 “文件排序”, 效率低。  |
| using temporary  | 使用了**临时表**保存中间结果，MySQL在**对查询结果排序**时使用临时表。常见于 order by 和 group by； 效率低  |
|using index|表示相应的select操作使用了**覆盖索引**， 直接从索引中过滤掉不需要的结果，无需回表， 效率不错。|
|using index condition|**索引下推！！**查找使用了索引，但是需要**回表查询**数据，此时就是因为索引列没有完全包含查询列|

> 具体using index condition中的索引下推是什么意思，可以参考这篇 [索引的原理&&设计原则](https://juejin.cn/post/7060427613742825502)

### using where

> 不同版本好像不一样

5.7：表示 MySQL 首先从数据表（存储引擎）中读取记录，返回给 MySQL 的 server 层，然后在 server 层过滤掉不满足条件的记录，即无法直接在存储引擎过滤掉。  
简单来说，就是查询时**where中用的不是索引。**  
![](/u/cms/www/202412/082043318c88.png)

> 现在，我们知道怎么用explain来分析SQL语句了，自然可以来剖析我们的SQL语句的性能，不过早有先人给我们总结了几个需要优化的场景-->**索引失效**

# 【二、索引失效】的几个场景
===============

## 0\. SQL准备
---------

create table `tb_seller` (
    	`sellerid` varchar (100),
    	`name` varchar (100),
    	`nickname` varchar (50),
    	`password` varchar (60),
    	`status` varchar (1),
    	`address` varchar (100),
    	`createtime` datetime,
        primary key(`sellerid`)
    )engine=innodb default charset=utf8mb4; 
    
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    
    -- 创建联合索引
    create index idx_seller_name_sta_addr on tb_seller(name,status,address);

## 1\. 不满足最左前缀
-----------

所谓最左前缀，可以想象成一个爬楼梯的过程，假设我们有一个**复合索引**：name，status，address，那这个楼梯由低到高依次顺序是：name，status，address，最左前缀，要求我们不能出现跳跃楼梯的情况，否则会导致我们的索引失效：

1.  按楼梯从低到高，无出现跳跃的情况--此时符合最左前缀原则，索引不会失效
![](/u/cms/www/202412/0820442355k3.png)
2.  出现跳跃的情况

*   直接第一层name都不走，当然都**失效**

![](/u/cms/www/202412/08204448uq95.png)
*   走了第一层，但是后续直接第三层，只有**出现跳跃情况前的不会失效（此处就只有name成功）**
![](/u/cms/www/202412/08204517nsm8.png)
*   同时，这个顺序并不是由我们where中的排列顺序决定，比如：
    *   where name='小米科技' and status='1' and address='北京市'
    *   where status='1' and name='小米科技' and address='北京市'

这两个尽管where中字段的顺序不一样，第二个看起来越级了，但实际上效果是一样的

> 其实是因为我们MySQL有一个**Optimizer**（查询优化器），查询优化器会将SQL进行优化，选择最优的查询计划来执行。

## 2\. 范围查询之后
----------

范围查询**之后**的索引字段，会失效！！！但本身用来范围查询的那个索引字段依然有效，如图中的status。

*   而图中address失效了，对比一下长度便可看出来。
![](/u/cms/www/202412/082045445f8z.png)
## 3\. 索引字段做运算
-----------

对索引字段做运算，使用函数等都会导致索引失效。  
![](/u/cms/www/202412/082046097r4a.png)
## 4\. 字符串不加' '
------------

索引字段为字符串类型，由于在查询时，没有对字符串加**单引号**，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

## 5\. 避免select \*
---------------

### 危害

*   消耗更多的 CPU 和 IO 以网络带宽资源
*   可减少表结构变更带来的影响
*   无法使用**覆盖索引**

### 🎈覆盖索引

尽量使用**覆盖索引**（索引列完全包含查询列），减少select \*

当查询列中包含了非索引项，虽然我们还是能够利用到索引，但是为了获取非索引项字段，我们需要**回表**去查询数据，效率会比较低。

## 6\. or分割开的条件
------------

用or分割开的条件， 如果**or前的条件中的列有索引**，而**后面的列中没有索引**，那么涉及的索引**都不会被用到**。

示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

*   因为有一个不走索引，又是or条件，两个都要判断一下，相当于不管如何，**都还是得去走全表查询，没有利用到索引。**

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G; 
![](/u/cms/www/202412/08204639fk4e.png)
## 7\. 以%开头的Like模糊查询
-----------------

可以联系**字典树Trie的匹配**吧。

*   比如要找‘abc’，如果是%bc，一开始的根都找不到了，自然没办法利用到索引树
    
*   而如果是ab%，还能利用到前两个。
    
*   %开头的失效，%结尾的还能利用索引（实际上这里就相当于字符串的最左前缀原则，可以这么理解）
![](/u/cms/www/202412/08204704rec9.png)
### 解决方法：使用覆盖索引

当真的需要两边都使用%来模糊查询时，只有当 **作为模糊查询的条件字段**（例子中的name）以及 **想要查询出来的数据字段**（例子中的 name & status & address）**都在索引列上**时，才能真正使用索引。

> 关于覆盖索引，可以参考这篇 -> [索引原理，设计原则](https://juejin.cn/post/7060427613742825502)
![](/u/cms/www/202412/0820472654xd.png)
8\. MySQL认为全表更快
---------------
![](/u/cms/www/202412/082047516kgz.png)
此处是由于数据的特殊性，‘北京市’所占的比例很高，还不如全表扫描  
![](/u/cms/www/202412/08204820ao2g.png)

### 8.1 is null 和 is not null

> 本质上**跟上边是一样的**

_MySQL底层会自动判断，如果全表扫描快，则直接使用全表扫描，不走索引。如果表中该索引列数据绝大多数是非空值，则使用 is not null的时候走索引，使用 is null的时候不走索引（还不如全表扫描快），全表扫描；反之亦然。_  
__

如果表中is null的比较多，那自然就直接全表扫描，如果is null的很少，会走索引。  
![](/u/cms/www/202412/08204904qchs.png)
## 8.2 in 和 not in
![](/u/cms/www/202412/08204930g4zf.png)
为了方便测试，我们单独建了一个status索引，观察该表数据，status中2很少，而1很多。

所以in('1')的话，不如走全表，没有用到索引  
in('2')就会走索引  
![](/u/cms/www/202412/0820495485a1.png)
### 总结

我们建立索引的时候，对于**数据分布均匀且重复的字段**，我们一般不考虑对其添加索引，因为此时MySQL会认为全表更快，会走全表扫描而非索引，导致我们的索引失效。

## 9\. !=或者<>
----------

使用不等式也会导致索引失效  
![](/u/cms/www/202412/082050230ofa.png)
相关习题
----
![](/u/cms/www/202412/08205046e0tb.png)

> 说完几个**索引失效的场景**，下边呢，是我们**具体的应用场景**，在如下几种特定情况下，我们需要采取不同的SQL优化方式，或采用索引，或利用外部条件

#【三、优化场景】1. 大批量插入数据
====================

环境准备
----

CREATE TABLE `tb_user_2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(45) NOT NULL,
      `password` varchar(96) NOT NULL,
      `name` varchar(45) NOT NULL,
      `birthday` datetime DEFAULT NULL,
      `sex` char(1) DEFAULT NULL,
      `email` varchar(45) DEFAULT NULL,
      `phone` varchar(45) DEFAULT NULL,
      `qq` varchar(32) DEFAULT NULL,
      `status` varchar(32) NOT NULL COMMENT '用户状态',
      `create_time` datetime NOT NULL,
      `update_time` datetime DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `unique_user_username` (`username`)  -- 唯一性约束
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

load命令
------

适当的设置可以提高导入的效率。  
![](/u/cms/www/202412/08205150iym0.png)
对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

### 1） 主键顺序插入

因为InnoDB类型的表是**按照主键的顺序**保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

脚本文件介绍 :

sql1.log  ----> 主键有序  
  sql2.log  ----> 主键无序

插入主键顺序排列数据：  
![](/u/cms/www/202412/08205216jeu7.png)
主键无序：  
![](/u/cms/www/202412/08205313ip9h.png)

#### 出现了权限问题
![](/u/cms/www/202412/082053499p0v.png)
执行：set global local\_infile=on;

#### 但又出现了另一个问题：
![](/u/cms/www/202412/082054243qs7.png)
其实我们开启之后， 需要**退出重新连接**，再次连接时便可以正常操作了

*   如果还是不行的话，连接的时候可以这样连接：

mysql --local_infile=1 -u root -ppassword

### 2）关闭唯一性校验

_在**导入数据前**执行 SET UNIQUE\_CHECKS=0，关闭唯一性校验，在**导入结束后**执行 SET UNIQUE\_CHECKS=1，恢复唯一性校验，可以提高导入的效率。_

🎪2. order by 排序
================

环境准备
----

CREATE TABLE `emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) NOT NULL,
      `age` int(3) NOT NULL,
      `salary` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
    
    insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
    
    create index idx_emp_age_salary on emp(age,salary);

两种排序方式
------

### using index

直接能在索引列完成查询，无需回表，关于回表查询，可以参考 [这篇文章](https://juejin.cn/post/7060427613742825502) ，此时需要保证**所查询的字段**都是**索引字段**，才会是using index  
![](/u/cms/www/202412/08205506h6pe.png)

> 但这个不太现实，不可能说我们要查的，都是索引的字段，所以很多情况下，我们并没有办法把using filesort优化为using index，只能退而求其次，尽量从filesort的角度去优化，通过外部条件。

### 🎑using filesort
![](/u/cms/www/202412/08205600olxs.png)

#### 何时会出现：

1.  order by的字段不是索引
2.  order by 字段是索引字段，但是 select 中没有使用**覆盖索引**
3.  order by 中同时存在 ASC 升序排序和 DESC 降序排序
![](/u/cms/www/202412/082056313ywp.png)
4.  order by中用到的是复合索引，但没有保持复合索引中字段的**先后顺序（即违背了最左前缀原则）**

> 比如图中的 select id,age,salary from emp order by salary,age；

为什么呢？这里我们得回顾一下复合索引是如何存储的，比如：我们建立一个复合索引(name，status，address)，索引中也是按这个字段来存储的，类似图中表格这样：

复合索引树(只存储索引列和主键用于回表)，而且是**先按name排序**，name相同了**再按status排序**，以此类推

name

status

address

id(主键)

小米1

小米2

所以如果我们不按照**索引的先后顺序**来order by的话，就跟索引树中的排序规则不一样了，索引此时排好的序，我们都没办法合理利用到，自然MySQL不会去走索引了。

🎏Filesort的优化
-------------

### 两种扫描算法

对于Filesort ， MySQL 有两种排序算法：  
以这条SQL语句为例，我们来看看他是怎么执行的：

select * from emp where age=1 order by salary；

1） **两次扫描算法** ：MySQL4.1 之前，使用该方式排序。

①首先根据where条件，过滤得到相应的满足age=1的salary，取出**排序字段salary**和对应的**行指针信息**(用于回表)，然后在排序区 **sort buffer** 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。

②完成排序之后，再根据**行指针回表读取所有字段**，而次该操作可能会导致大量随机I/O操作，是我们需要改进的地方。

这就是所谓的两次扫描，第一次扫描，我们拿到的只是排序字段，然后在**sort buffer**排好序；第二次扫描，才去**回表**读取所有字段，最终返回。

> 该如何优化呢？为什么要分成两次，有没有一种可能是空间不够呢？那我们如果有足够的空间，以空间换时间，是不是就可以开辟出一种新的方法，只需要一次扫描即可

2）**一次扫描算法**：一次性取出满足条件的**所有字段**，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高，典型的以空间换时间的思想。

具体使用哪种算法呢？  
MySQL 通过比较系统变量 **max\_length\_for\_sort\_data** 的大小和 **Query语句取出的字段总大小**， 来判定使用那种排序算法，如果**max\_length\_for\_sort\_data** 更大，那么使用**一次扫描算法**；否则使用两次扫描算法。

### 优化方案

① 增大前者 **max\_length\_for\_sort\_data**  
可以适当 **max\_length\_for\_sort\_data** 系统变量，来增大排序区的大小，提高排序的效率，这是典型的**空间换时间**的思想。  
![](/u/cms/www/202412/08205927wv73.png)
② 减小后者 **Query语句取出的字段总大小**  
如果内存实在不够富裕的话，我们可以减少查询的字段，避免select \*

③ 提高 **sort\_buffer\_size** ：  
由上文可知，通过增大该参数，可以让 MySQL 尽量减少在排序过程中对须要排序的数据**进行分段**，避免需要使用到临时表 temporary table 来存储排序结果，再把多次的排序结果串联起来。

可惜，MySQL**无法查看**它用了哪个算法。如果增加了max\_Length\_for\_sort\_data变量的值，磁盘使用率上升了，CPU使用率下降了，并且Sort\_merge\_passes状态变量相对于修改之前开始很快地上升，也许是MySQL强制让很多的排序使用了**一次扫描算法**。

> 具体的实战修改过程，需要结合MySQL中另一个工具--**trace分析优化器**，来分析执行计划，后续有机会，我们再来详细聊一聊！
3\. group by 分组
===============

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

1\. 使用索引
--------

先来看看无索引的情况：using temporary；using filesort  
![](/u/cms/www/202412/08210013f1wi.png)
### 创建索引

create index idx_emp_age_salary on emp(age,salary)；
    
![](/u/cms/www/202412/08210037d0cf.png)
2\. 加上order by null 禁止排序
------------------------

如果查询包含 **group by** 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：  
![](/u/cms/www/202412/08210059l9vb.png)
3\. 需要排序 (则跟order by的优化大体相同)
----------------------------

4\. 优化子查询
=========

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。

但是，有些情况下，子查询是可以被更高效的**连接（JOIN）替代的**！！  
__

示例 ，查找有角色的所有的用户信息 :

explain select * from t_user where id in (select user_id from user_role );

执行计划为 :  
![](/u/cms/www/202412/08210129qmy9.png)
优化后 :

explain select * from t_user u , user_role ur where u.id = ur.user_id;
![](/u/cms/www/202412/0821015780vr.png)

> 连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中**创建临时表**来完成这个逻辑上需要两个步骤的查询工作。

5\. 优化OR条件
==========

对于包含OR的查询子句，如果要利用索引，则OR之间的**每个条件列都必须用到索引** ， 而且**不能使用到复合索引**； 如果没有索引，则应该考虑增加索引。

我们此处有一个id主键索引，和一个age，salary复合索引：  
![](/u/cms/www/202412/08210221f5oz.png)

单列+复合中的某一个
----------

explain select * from emp where id = 1 or age = 30;
![](/u/cms/www/202412/082102477311.png)
单列+单列（两个一样）
-----------

> 实际上等效于range，此处只是提供一个示例
![](/u/cms/www/202412/08210308en7v.png)
解决：使用union优化！！！
---------------

### 优化前

*   type：index\_merge
![](/u/cms/www/202412/08210343zy6l.png)
### 优化后

*   type：一个是const，一个是ref，都比index快
![](/u/cms/www/202412/08210408g6z4.png)
6\. 使用SQL提示
===========

SQL提示，是优化数据库的一个重要手段，简单 来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

use index
---------

在查询语句中表名的后面，添加 use index 来提供希望MySQL去**参考**的索引列表，就可以让MySQL不再考虑其他可用的索引。  
![](/u/cms/www/202412/08210429fo9x.png)
ingore index
------------

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

force index
-----------

强制走索引，即使MySQL认为全表更快，我们用force也可以强制走索引。

### 跟use的区别

*   use只是提供一个参考，具体用不用还得看MySQL的优化器怎么想的
![](/u/cms/www/202412/08210451je3g.png)
✨7. 优化limit分页
=============

一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL_**排序**_ 前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

比如我们有这样一条语句，select \* from tb\_item limit 2000000,10 ;

此时默认是根据 **id** 排序的。

#### 优化思路一

在**索引上**完成排序分页操作，最后根据**主键关联**回原表查询所需要的其他列内容。

![](/u/cms/www/202412/082105159868.png)

#### 优化思路二

该方案适用于**主键自增**的表，可以把Limit 查询转换成某个位置的查询 。(局限性：主键不能断层)

*   如果要根据其他字段来排序的话，此方法就无法做到了。
![](/u/cms/www/202412/08210535xlcc.png)
8\. 优化insert操作
==============

一次连接，多次插入
---------

比如我们需要插入三条数据：

insert into tb_test values(1,'Tom');
    insert into tb_test values(2,'Cat');
    insert into tb_test values(3,'Jerry');

此时需要建立三次连接，每次连接都要消耗资源，为了提高单次连接的执行效率，我们会采取：

insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

同时，insert的时候最好是保持数据的**有序插入**

🎊总结
====

1.  explain分析SQL中，其中比较重要的主要是type，key，ref以及extra，我们不需要死记硬背，多拿几条语句去explain比对比对，更有利于我们辅助记忆。
    
2.  索引失效的几个场景，借用b站热评：

全值匹配我最爱，最左前缀要遵守；  
带头大哥不能死，中间兄弟不能断；  
索引列上少计算，范围之后全失效；  
Like百分写最右，覆盖索引不写星；  
不等空值还有or，索引失效要少用；  
VAR引号不可丢，SQL高级也不难！

3.  优化基本原则：巧用索引，减少连接次数。

上一篇： CWnd类总结

下一篇：没有了

已有0条评论

0/150

提交

热门评论

查看更多评论

目 录

目录