慢sql查询优化解决方法

**慢sql查询的经典解决方案**

**一、前言**

SQL中，广义的查询就是crud操作，狭义的查询仅仅是select查询操作，慢查询就是指广义的查询，否则为什么不叫慢查询、慢更新、慢删除。
crud是指在做计算处理时的增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。
慢查询就是那些执行慢的sql语句，包括crud，一般是查询，所以称为慢查询

问题1：怎么一定一个sql语句是慢的？  
回答：根据实际需要，如果前端反馈，执行3s是慢的，就是在my.ini中（Windows是my.ini，Linux是my.conf）中设置long\_query\_time=3，表示操作3s的查询就是慢查询，要记录下来，好好分析。

问题：为什么这些sql语句会慢呢？为什么这些sql语句满足long\_query\_time设置的时间？  
回答：执行慢的sql语句不满足我们的要求，  
（1）找到原因 explain + profile；  
（2）要想办法解决，这就是SQL优化。

**二、慢查询概要**

**2.1 第一步，慢查询分析之前的配置**

**2.1.1 方式一：修改my.ini**

修改配置文件 在 my.ini 增加几行:

[mysqld]

long\_query\_time=2 // 慢查询的定义时间（超过2秒就是慢查询）

// 慢查询log日志记录（ slow\_query\_log）

## 5.5 版本以下配置

log-slow-queries="mysql-slow-query.log"

## 5.5 版本及以上配置

slow-query-log=on

slow\_query\_log\_file="mysql-slow-query.log"

//记录没有使用索引的query

log-query-not-using-indexes

**2.1.2 方式二：修改数据库**

mysql > set global slow\_query\_log=ON

mysql > set global long\_query\_time = 3600;

mysql > set global long\_queries\_not\_using\_indexes=ON;

**2.2 第二步，找到执行慢的sql语句**

1. **找到慢查询日志文件路径**：

mysql > show variables like 'slow\_query\_log\_file

1. **使用msql提供的日志分析工具mysqldumpslow分析找出查询时间最慢的十条sql**：

Root$ mysqldumpslow -s 10 /mysql/mysql01\_slow.log

**2.3 第三步，找到原因两种方式之一，explain分析，explain各个字段的解释**

问题：为什么使用explain来做慢查询分析？  
回答：**explain关键字可以模拟优化器执行SQL查询语句**，所以用来分析sql慢查询语句

**使用explain分析这10条sql**：

explain SQL\_NO\_CACHE select * from emp where name = 'Jefabc'

注意：mysql8.0以下，SQL\_NO\_CACHE 指明了该查询不走缓存，避免了查询速度时高时低，影响判断。  
ps：使用explain一般看一看索引使用是否正确，尽量避免回表。  
![img](/media/202205//1652844397.8383136.png)  
![在这里插入图片描述](/media/202205//1652844397.8717911.png)  
explain各个字段解释  
**id**：每个执行计划都有一个 id，如果是一个联合查询union，这里还将有多个 id。  
**select\_type**：表示 SELECT 查询类型，常见的有四种  
SIMPLE（普通查询，即没有联合查询、子查询）、  
PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。  
**table**：当前执行计划查询的表，如果给表起别名了，则显示别名信息。  
**partitions**：访问的分区表信息。  
**type**：这是重要的列，**显示连接使用了何种类型**。从最好到最差的连接类型为：system > const > eq\_ref > ref > range > index > ALL。  
system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。如果是 B + 树索引，我们知道此时索引构造成了多个层级的树，当查询的索引在树的底层时，查询效率就越低。const 表示此时索引在第一层，只需访问一层便能得到数据。  
eq\_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。  
ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。  
range：索引范围扫描，比如，<，>，between 等操作。  
index：索引全表扫描，此时遍历整个索引树。  
ALL：表示全表扫描，需要遍历全表来找到对应的行。  
**possible\_keys**：可能使用到的索引。  
**key**：实际使用到的索引。  
**key\_len**：实际使用的索引的长度。  
**ref**：关联 id 等信息。  
**rows**：查找到记录所扫描的行数。  
**filtered**：查找到所需记录占总扫描记录数的比例。  
**Extra**：额外的信息。

**2.4 第四步，找到原因两种方式之一，profile分析，找到慢查询的本质原因，profile各个字段的解释**

问题：为什么使用profile做慢查询分析？  
回答：Show profile 是mysql 提供可以用来分析当前会话中语句执行的**资源消耗情况**。

以下四个中若出现一个或多个，表示sql 语句 必须优化。  
1、converting HEAP to MyISAM ： 查询结果太大，内存都不够用了，往磁盘上搬了；  
2、creating tmp table ：创建临时表，拷贝数据到临时表，然后再删除；  
3、copying to tmp table on disk ：把内存中临时表复制到磁盘，危险！！！  
4、 locked

**2.4.1 explain制造慢sql语句，profile找到慢的sql语句**

**第一步，查看一下我的数据库版本**

Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

mysql> Select version();

| version() |

| 5.0.82-community-nt |

版本是支持show profiles功能的。接下来进入mysql性能跟踪诊断的世界

**第二步，查看是否打开了profiles功能,默认是关闭的**

mysql> use test;

Database changed

mysql> show profiles;

Empty set (0.00 sec)

显示为空，说明profiles功能是关闭的。

**第三步，开启profile**

mysql> set profiling=1;

Query OK, 0 rows affected (0.00 sec)

* 1
* 2
* 3

**第四步，explain执行下面的查询**

mysql> explain select distinct player\_idfrom task limit 20;

mysql> select distinct player\_id from task ;

**第五步，执行 show profiles**

mysql> show profiles;

| Query\_ID | Duration | Query |

| 1 | 0.00035225 | explain select distinct player\_id from task limit 20 |

| 2 | 1.91772775 | select distinct player\_id from task |

Query\_ID 这个需要就是给show profile for query 具体id来用的。

此时可以看到执行select distinct player\_id from task 用了1.91772775秒的时间

**2.4.2 show profile for query 具体id**

**2.4.2.1 sending data发送数据慢，就是网络IO**

根据prifile进一步分析，

show profile for query 具体id

可以清楚的看到该sql的所有执行阶段，如锁等待、执行、优化、发送数据、内存排序，在下图中可以看到**Sending data发送数据**耗时1.39s。慢查询主要原因是网络IO。

![img](/media/202205//1652844397.915364.png)

**2.4.2.2 Copying to tmp table临时表慢**

**根据query\_id 查看某个查询的详细时间耗费，是Copying to tmp table **

mysql> show profile for query 2;

| Status | Duration |

| starting | 0.000052 |

| Opening tables | 0.000009 |

| System lock | 0.000003 |

| Table lock | 0.000007 |

| init | 0.000013 |

| optimizing | 0.000003 |

| statistics | 0.000009 |

| preparing | 0.000008 |

| Creating tmp table | 0.000074 |

| executing | 0.000002 |

| Copying to tmp table |1.916551 | // 大量时间 四条中第三条，很严重，临时表到

| Sending data | 0.000667 |

| end | 0.000004 |

| removing tmp table | 0.000065 |

| end | 0.000002 |

| query end | 0.000003 |

| freeing items | 0.000245 |

| closing tables | 0.000006 |

| logging slow query | 0.000002 |

| cleaning up | 0.000003 |

可以看到 Copying to tmp table 部分耗费了大量时间，这是因为distinct查看会用到临时表

**2.4.3 查看cpu、内存和IO信息**

那么可不可以查看占用cpu、 io等信息呢

mysql> show profile block io,cpu for query2;

另外还可以看到memory,swaps,context switches,source 等信息

**三、SQL优化**

**3.1 第一，明确SQL优化两个目标（IO成本+CPU成本）**

减少 IO 次数

IO永远是数据库最容易产生瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是IO操作所占用的，减少IO次数是SQL优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

降低 CPU 计算

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by，group by，distinct等操作都十分占用CPU资源(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标。

**3.2 第二，SQL执行的11个步骤，搞懂MySQL的语句执行顺序（11条）**

MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入。

select m.Province,S.Name from member m  
left join ShippingArea s on m.Province=s.ShippingAreaID;

下面具体分析一下查询处理的每一个阶段。

1、FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表vitual table 1

2、ON: 对虚表VT1进行ON筛选，只有那些符合< join-condition>的行才会被记录在虚表VT2中。

3、JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3，如果包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。

4、WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合< where-condition>的记录才会被插入到虚拟表VT4中。

5、GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5。

6、CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6。

7、HAVING： 对虚拟表VT6应用having过滤，只有符合< having-condition>的记录才会被插入到虚拟表VT7中。

8、SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。

9、DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9。

10、ORDER BY: 将虚拟表VT9中的记录进行排序操作，产生虚拟表VT10。

11、 LIMIT：取出指定行的记录，产生虚拟表VT11，并将结果返回。

**金手指：记忆方式：输入、判断、返回  
输入：from on join  
判断：where判断、group by分组、having判断  
返回：select输出，distinct去重、order by排序、limit返回**  
单表查询，设置条件：用 Where 子句替换 HAVING 子句 因为 HAVING 只会在检索出所有记录之后才对结果集进行过滤

大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作block或者page)为单位，一般为4KB，8KB…… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(blob等特殊类型字段除外)。  
所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。  
当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取 a，b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。  
大多数时候并不会影响到 IO 量，但是当还存在order by操作的时候，select子句中的字段多少会在很大程度上影响到排序效率。

**3.3 第三，表设计（表设计5条 + 字段设计5条）**

**3.3.1 表设计层面（5条）**

1、【表引擎】选择正确的表引擎 MyISAM Innodb

（1）MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好，甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。（2）MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

**金手指：与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能**。

2、【表设计-无逻辑意义自增id】给所有的InnoDB表都设计一个无逻辑意义的自增列做主键，对于绝大多数场景都是如此，真正纯只读用InnoDB表的并不多；

3、【表设计-反范式设计，减少回表】适当的使用字段冗余的反范式设计，以空间换时间有的时候会很高效，比如：有用户id字段的表，用户在查询时如果经常需要同时获得用户名，此时可以将用户名当一个冗余字段在该表中存储，这样就可以不做连接即可获得用户名。  
要求：该冗余字段的值一般不变或者很少变化。

4、【表存储——数据量过大，水平分表】通常地，单表物理大小不超过10GB，单表行数不超过1亿条，行平均长度不超过8KB，如果机器性能足够，这些数据量MySQL是完全能处理的过来的，不用担心性能问题，数据量过大就要水平分表，不要放在同一个表中。

5、【表存储——表中存储数据而不是运算】数据库直接存放数值，不要在数据库中做运算。

**优化数据库结构**  
合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。  
1、垂直分表，将字段很多的表分解成多个表  
对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。  
2、多对多关系表，增加中间表  
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

三范式优化+反范式优化+分库分表  
1）三大范式优化： 比如消除冗余（节省空间。。）  
2）反范式优化：比如适当加冗余等（减少 join）  
3）分库： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘 I/O，一个精心设置的分区可以将数据传输对磁盘 I/O 竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。  
4）分表：垂直拆分和水平拆分：  
案例： 简单购物系统暂设涉及如下表：  
1.产品表（数据量 10w，稳定）  
2.订单表（数据量 200w，且有增长趋势）  
3.用户表 （数据量 100w，且有增长趋势）  
以 mysql 为例讲述下水平拆分和垂直拆分，mysql 能容忍的数量级在百万静态数据可以到千万  
垂直拆分：  
解决问题：表与表之间的 io 竞争  
不解决问题：单表中数据量增长出现的压力  
方案： 把产品表和用户表放到一个 server 上 订单表单独放到一个 server 上  
水平拆分：  
解决问题：单表中数据量增长出现的压力  
不解决问题：表与表之间的 io 争夺  
方案：  
用户表通过性别拆分为男用户表和女用户表，男用户表放一个 server 上 女用户表放一个 server 上(女的爱购物)  
订单表通过已完成和完成中拆分为已完成订单和未完成订单 ，未完成订单放一个 server 上 已完成订单放到一个server上

**3.3.2 字段设计层面（5条）**

1、yes 两条【字段-数据类型够用选小的，加上not null提交性能】字段长度满足需求前提下，尽可能选择长度小的；字段属性尽量都加上NOT NULL约束，可一定程度提高性能；

2、no 过大不要【字段-数据类型尽量不使用Text/Blob，垂直分表】尽可能不使用TEXT/BLOB类型，确实需要的话，建议垂直分表，拆分到子表中，不要和主表放在一起，避免SELECT * 的时候读性能太差。

3、select 1条【字段-使用数字型而不是字符型】数据类型尽量用数字型，数字型比字符型的效率高，如果表中某个字段存储的都是数字，那么该字段设计为数字型字段，而不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

4、select 1条【字段-接上面，对于字符类型，使用固定长度而不是可变长度】  
char/nchar为固定长度，如果某个字段中字符长度已知固定，使用char/nchar效率比varchar/nvarchar效率高。

5、select 1条【取值有限用枚举Enum】  
如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

**3.4 第四，高效的SQL语句（索引+单表优化+多表优化）**

**3.4.1 索引（索引两方向：优先使用索引，避免索引失效）**

如下第二、第三句SQL，使用索引与不使用索引，查询速度相差巨大。（索引可用在select后，也可用于on，where后的条件中，参考**索引使用理论基础**）

SELECT * FROM test1; -- 1.863 2.008 2.062（260万条数据） -- 此处有个select*的误区，实际上不加索引的select* 比select col速度来得快

SELECT NAME FROM test1; -- 4.989 5.001 4.855 （260万条数据, name不加索引）

SELECT NAME FROM test1; -- 1.227 1.476 1.403 （260万条数据, name加索引）

**3.4.1.1 避免索引失效**

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

如：  
select id from t where num is null  
可以在 num 上设置默认值 0，确保表中 num列没有 null 值，然后这样查询：  
select id from t where num=0  
注意：关于 null,isNull,isNotNull 其实是要看成本的，是否回表等因素总和考虑，才会决定是要走索引还是走全表扫描

【索引】禁止任何对where字段的计算，函数等操作

索引没起作用的情况  
1、使用LIKE关键字的查询语句  
在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。  
2、使用多列索引的查询语句  
MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

**尽量避免在索引过的字符数据中，使用非打头字母搜索，导致索引无效**

SELECT id FROM table WHERE NAME LIKE ‘%L%’; // 错误

SELECT id FROM table WHERE NAME LIKE ‘L%’; // 正确

即使NAME字段建有索引， 第一个查询无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第二个查询能够使用索引来加快操作。

**不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，导致索引无效**

任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

SELECT * FROM T1 WHERE F1/2=100

应改为:

SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD\_NO,1,4)= '5378'

应改为:

SELECT * FROM RECORD WHERE CARD\_NO LIKE '5378%'

SELECT member\_number, first\_name, last\_name FROM members

WHERE DATEDIFF(yy,datofbirth,now()) > 21

应改为:

SELECT member\_number, first\_name, last\_name FROM members

WHERE dateofbirth < DATEADD(yy,-21,now())

**索引**：  
%% like查询, not in,not exist 都无法应用索引  
用了"聚合函数"查询,就无法应用索引；  
复合索引,一般都是看第一个条件索引

**3.4.1.2 优先使用索引**

【索引-前缀索引】对一个VARCHAR(N)列创建索引时，通常取其50%（甚至更小）左右长度创建前缀索引就足以满足80%以上的查询需求了，没必要创建整列的全长度索引；

【SQL语句-输入参数where子句-索引代替全表扫描】尽量避免在where子句中对字段进行null值判断。这会进行全表扫描 Select id,name from user where name is null;

【SQL语句-输入参数where子句-索引代替全表扫描】尽量避免在where子句中对字段进行表达式操作。这会导致引擎放弃使用索引而进行全表扫描 SELECT id,name FROM user where age/12;

【优先使用复合索引代替单个索引，减少索引数量】多用复合索引，少用多个独立索引，尤其是一些基数（Cardinality）太小（比如说，该列的唯一值总数少于255）的列就不要创建独立索引了；

【SQL语句-输入参数or子句-索引代替全表扫描】对于OR子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引。

**【复合索引的顺序】在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。**

**3.4.2 单表查询 10点**

**1、单表查询：避免索引失效，不要在 where 子句中对字段进行 null 值判断，这会进行全表扫描**

错误：应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致数据库放弃使用索引而进行全表扫描，降低查询速度。可以在col上设置默认值，确保表中col列not null值，然后查询。

以下实测结果表明：在不加索引的情况下，判空操作比判非空默认值操作速度快得多，加索引的情况下两种情况差不多。

SELECT id from table where col is null –- 2.003 1.963 1.975 （table col 未加入索引，260万数据）

SELECT id from table where col = ‘0’ -– 4.407 5.256 5.434 （table col 未加入索引，260万数据）

SELECT id from table where col is null –- 1.549 1.884 1.818 （table col 加入索引，260万数据）

SELECT id from table where col = ‘0’ -– 1.626 1.854 1.532 （table col 加入索引，260万数据）

**2、单表查询，开启慢查询日志，定位慢查询的SQL语句**

【单表查询-慢查询】开启慢查询日志，定期用explain或desc优化慢查询中的SQL语句

【单表查询-预处理语句】使用预处理语句。例如使用PDO来操作mysql  
在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements(预处理语句)定义一些参数，而MySQL只会解析一次。

**3、单表查询，数据库压力转移到Java后台**

**将数据库的压力转移至后台**  
数据库的资源是宝贵的，我们可以将不必要的操作转移至Java端处理。如判空，字段值拼接，字符串TRIM，日期格式化等等。  
如下SQL，均可由Java处理。

SELECT IFNULL(SUM(col),0) from table; // 判空

SELECT CONCAT(col, 'hello world') from table; //字段值拼接

SELECT TRIM(col) from table; // 字符串TRIM

SELECT DATE\_FORMAT(col,'%d %b %Y %T:%f') from table; // 日期格式化

**4、单表查询，where 查询条件数量 + 查询条件顺序**

where子句两个  
第一，查询条件尽量少  
第二，越精确的条件,应该放在前面，先过滤掉大数据;

**where子句中，尽量少的条件**  
如下面这句SQL，**如果** where条件中col1=1，col2=2是能够精确查找结果的最简条件，则无需加入冗余的其他条件。  
SELECT * FROM table where col1=1 and col2=2 and col3=3

**where子句中，越精准的条件越早过滤**  
where条件中，越精确的条件（能够剔除大部分结果的条件）放前面。  
又如，在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省IO操作所消耗的时间。

**5、单表查询，返回值和关键字大写优化（select *，limit）**

【SQL语句-返回值】满足业务需求下，返回值不要使用select * from 的形式查询数据，只查询需要使用的列  
【SQL语句-返回值】满足业务需求下，返回值使用limit减少返回的行数，减少网络数据传输时间和带宽浪费，但是CPU消耗还是要的

【SQL语句-关键字】所有的SQL关键字用大写，避免SQL语句重复编译造成系统资源的浪费

**6、单表查询：计数优化，不要使用 count(列名)或 count(常量)来替代 count(*)**

【单表查询】不要使用 count(列名)或 count(常量)来替代 count(*)  
【单表查询】使用 ISNULL()来判断是否为 NULL 值

**count(*) count(1) count(id)的效率**

SELECT COUNT(*) FROM test1; -- 0.749 0.752 0.750 你看，count(*)性能很好

SELECT COUNT(1) FROM test1; -- 1.578 0.900 1.455

SELECT COUNT(id) FROM test1; -- 0.740 0.763 0.751

很多人为了统计记录条数，就使用count(1)和count(primary\_key)而不是count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，因为数据库对count(*)计数操作做了一些特别的优化。

第一，定义不同  
count(column) 和 count(*)，count(1)是一个完全不一样的操作，所代表的意义也完全不一样。  
count(column) 是表示结果集中有多少个column字段不为空的记录。  
count(*)，count(1)是表示整个结果集有多少条记录。

查询尽量用具体的字段，而不是直接select * ；聚合函数不适用大量数据查询过滤；  
count(*)—会吧null空列也统计进去  
count(列)—只会统计非空字段  
在mysql5.6之后，count(*)速度比count(1)和count(列) 更快

**7、单表查询：去重优化，能用GROUP BY的就不用DISTINCT**

使用GROUP BY去重比DISTINCT效率高。

SELECT DISTINCT OrderID FROM Details

WHERE UnitPrice > 10

用下面的语句替换：

SELECT OrderID FROM Details

WHERE UnitPrice > 10 GROUP BY OrderID

**8、单表查询，LIMIT优化，巧借索引优化orderby排序**

【单表查询-limit】如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大

**单表查询-优化LIMIT分页，使用索引优化orderby排序，因为索引是有序的，加上覆盖索引**  
在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。  
一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。  
**优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描**，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。  
对于下面的查询：  
select id,title from collect limit 90000,10;  
该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置 开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。  
**方法一：虑筛选字段（title）上加索引**  
title字段加索引 （此效率如何未加验证）  
**方法二：先查询出主键id值（优化LIMIT分页，使用索引优化orderby排序）**  
select id,title from collect where id>=  
(select id from collect order by id limit 90000,1) limit 10;  
原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。  
**方法三：“关延迟联”**  
如果这个表非常大，那么这个查询可以改写成如下的方式：  
Select news.id, news.description from news  
inner join (select id from news order by title limit 50000,5) as myNew using(id);  
这里的“关延迟联”将大大提升查询的效率，它让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的limit。  
**方法四：建立复合索引 acct\_id和create\_time**  
select * from acct\_trans\_log WHERE acct\_id = 3095 order by create\_time desc limit 0,10  
注意sql查询慢的原因都是:引起filesort，所以一定要避免filesort

**9、单表查询：排序优化，order by 字段加索引**

索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回，因为数据库知道数据已经满足我们的排序需求了。

实际上，利用索引来优化有排序需求的 SQL，是一个非常重要的优化手段。

**10、单表查询：分组优化，group by 调节顺序为复合索引顺序**

**存在的问题**：通常情况下，SQL 语句中的 GROUP BY 子句会**导致数据库不得不通过一个排序（SORT）操作来实现对数据的分组**，而排序被认为是一个比较耗费 CPU 和内存的操作。实际上某些情况下，如果写法得当，当中的排序操作是可以避免的。

具体来说，在写 GROUP BY 子句的时候，应该考虑到数据库中已经存在的索引的情况。如果 GROUP BY 子句中所有的列恰好包括在某个索引的键（Key column）的范围之内而且是处于开始的位置，那么在写 GROUP BY 子句的时候，就应该按照该索引上键的先后顺序来写 GROUP BY 子句。

比如说有如下的 SQL 语句：

SELECT C2, C3, C1, AVG(C4)

FROM T1

GROUP BY C2, C3, C1

一般情况下，GROUP BY C2, C3, C1这样的写法都会导致数据库执行一个排序操作，消耗CPU。但是，如果表 T1 上已经存在一个索引 IX1(C1, C2, C3, C4), 这里注意到 GROUP BY 子句中引用到的列（C2，C3，C1）正好是索引 IX1 中的前三个键，那么就可以通过改变 GROUP BY 子句中列的顺序的办法来避免这个排序操作。

可以把 SQL 语句改写为如下所示：

SELECT C1, C2, C3, AVG(C4)

FROM T1

GROUP BY C1, C2, C3

通过这样改变 GROUP BY 子句中列的顺序使其与索引 IX1 中的键顺序一致，数据库就可以利用 IX1 来访问其已经排序的键值并直接返回进行下一步操作，从而避免额外的排序操作，从而带来查询性能上的提高。

**3.4.3 多表连接查询 4点**

**1、多表连接，五个：用 join 代替子查询，连接字段类型一致，连接字段加索引，主表是小表，orderby和groupby字段在主表，否则无用**

【多表连接-join代替子连接】通常情况下，子查询的性能比较差，建议改造成JOIN写法；

**多表查询：嵌套子查询如何优化**  
**嵌套子查询的效率低的原因**：执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，多了一个创建和销毁临时表的过程  
**嵌套子查询的优化方案**：  
1、使用join，join比嵌套子查询更高效，数据量较大时，无需真正带入不同参数循环迭代  
2、拆分为多个查询语句

**多表查询-分解关联查询**  
解释：将一个大的查询分解为多个小查询是很有必要的。  
很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效，例如：  
SELECT * FROM tag  
JOIN tag\_post ON tag\_id = tag.id  
JOIN post ON tag\_post.post\_id = post.id  
WHERE tag.tag = ‘mysql’;  
分解为：  
SELECT * FROM tag WHERE tag = ‘mysql’;  
SELECT * FROM tag\_post WHERE tag\_id = 1234;  
SELECT * FROM post WHERE post.id in (123,456,567);

【多表连接-连接字段CPU消耗一致】多表联接查询时，关联字段类型尽量一致，省去字段类型转换的CPU消耗；  
【多表连接-连接字段都要有索引】多表联接查询时，关联字段都要有索引；

【多表连接-主表是小表】多表连接查询时，把结果集小的表（注意，这里是指过滤后的结果集，不一定是全表数据量小的）作为驱动表，解释：小表全表扫描，用来比较大表。  
【多表查询-groupby orderby在主表上】多表查询中，如果使用group by,order by，最好都能选择驱动表的列或者只选择一个表上的列，否则无法排序和分组无法用到索引。

**多表查询，分析具体的SQL语句**  
1、两个表选哪个为驱动表，表面是可以以数据量的大小作为依据，但是实际经验最好交给mysql查询优化器自己去判断。  
例如： select * from a where id in (select id from b );  
对于这条sql语句它的执行计划其实并不是先查询出b表的所有id,然后再与a表的id进行比较。  
mysql内部会将 in子查询转换成exists相关子查询，所以它实际等同于这条sql语句：select * from a where exists(select * from b where b.id=a.id );  
而exists相关子查询的执行原理是: 循环取出a表的每一条记录与b表进行比较，比较的条件是a.id=b.id . 看a表的每条记录的id是否在b表存在，如果存在就行返回a表的这条记录。  
**exists查询有什么弊端？外面的a表无法使用索引，必须走全表扫描**  
由exists执行原理可知，a表(外表)使用不了索引，必须全表扫描，因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查（外表到里表中），顺序是固定死的。  
**如何优化？**  
加索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。  
**这样优化够了吗？还差一些。**  
由于exists查询它的执行计划只能拿着a表的数据到b表查（外表到里表中），虽然可以在b表的id字段建索引来提高查询效率。  
但是并不能反过来拿着b表的数据到a表查，exists子查询的查询顺序是固定死的。  
**为什么要反过来？**  
因为首先可以肯定的是反过来的结果也是一样的。这样就又引出了一个更细致的疑问：在双方两个表的id字段上都建有索引时，到底是a表查b表的效率高，还是b表查a表的效率高？  
**该如何进一步优化？**  
把查询修改成inner join连接查询：select * from a inner join b on a.id=b.id; （但是仅此还不够，接着往下看）  
**为什么不用left join 和 right join？**  
这时候表之间的连接的顺序就**被固定住了**，比如左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。仍然不是最好的选择。  
**为什么使用inner join就可以？**  
inner join中的两张表，如： a inner join b，但实际执行的顺序是跟写法的顺序没有半毛钱关系的，最终执行也可能会是b连接a，**顺序不是固定死的**。如果on条件字段有索引的情况下，同样可以使用上索引。  
**那我们又怎么能知道a和b什么样的执行顺序效率更高？**  
你不知道，我也不知道。谁知道？mysql自己知道。让mysql自己去判断（查询优化器）。具体表的连接顺序和使用索引情况，mysql查询优化器会对每种情况做出成本评估，最终选择最优的那个做为执行计划。  
**在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高，如果两个表都建有索引的情况下，mysql同样会评估使用a表条件字段上的索引效率高还是b表的。**  
**利用explain字段查看执行时运用到的key（索引）**  
而我们要做的就是：把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。

错误使用Left Join  
left join的表在where中出现，如  
Table1 left join Table2 on Table1.C1=Table2.c1 where Table2.column1=XXX  
实际上已经变成了inner join  
Left join会限制连接顺序，而且where条件只能在连接后过滤，影响性能

**2、多表查询，很多时候用exists代替in是一个好的选择（理由：虽然in与exists效率在不同场景效率有高有低，但not exists比not in的效率高）**

select num from a where num in (select num from b);

用下面的语句替换：

select num from a where exists (select 1 from b where num=a.num)

**3、尽量避免向客户端返回大数据量，应该使用分页**

若从数据库一次性返回的数据量过大，应该考虑相应需求是否合理，是否可以通过分页等方法处理。

【多表查询-分页SQL语句】类似分页功能的SQL，建议先用主键关联，然后返回结果集，效率会高很多；

分页SQL优化  
分页SQL可能包含order by，group by和多表join等操作，可以利用前面的优化手段  
后台分页取数SQL可以用cursor替换，分批fetch；使用分页SQL多次取，性能差，数据一致性没保证

**4、用Union ALL代替OR（比较少用，一般都用OR）**

SELECT * FROM test1 WHERE NAME = 'test1\_1' OR NAME = 'test1\_2'; -- 3.179 2.902 2.806（100万数据测试）

* 1

SELECT * FROM test1 WHERE NAME = 'test1\_1'

union all

SELECT * FROM test1 WHERE NAME = 'test1\_2'; -- 2.335 2.485 2.432（100万数据测试）

拆分复杂查询，使用联合查询，可以用多条sql语句来实现一条查询结果,提高查询性能。

**能用UNION ALL就不要用UNION**

UNION ALL不执行SELECT DISTINCT函数，少一个步骤，这样就会减少很多不必要的资源。

**3.5 第五，通用**

**【通用】**：  
Mysql数据库名和表名在 Windows 中是大小写不敏感的，而在大多数类型的 Unix/Linux 系统中是大小写敏感的。  
MySQL大小写敏感可以通过配置文件（Windows中的my.ini，Linux中的my.cnf）的lower\_case\_table\_names参数来控制。  
**【通用】**：数据库的存储三样东西----表结构,表数据，索引  
【通用 Mysql很少使用存储引擎】Mysql很少采用类似SqlServer中大量使用的 “存储过程”来实现业务逻辑（存储过程也不是mysql的优势）；更多的是用 简单的查询，而把复杂的查询业务，直接在程序代码中实现，压力转义(java中有很多优秀的orm框架)。 mysql虽然没有sqlserver和oracle强大，但是它是开源免费，可以部署多台sql服务器,实现分表分库,集群，从而实现以量换性能。