面试官如果问你：你会从哪些维度进行MySQL性能优化？你会怎么回答？

所谓的性能优化，一般针对的是MySQL查询的优化。既然是优化查询，我们自然要先知道查询操作要经过哪些环节，然后思考可以在哪些环节进行优化。

我之前写过一条SQL查询语句是如何执行的？，感兴趣的朋友可以阅读一下，我用其中的一张图展示查询操作需要经历的基本环节。

下面从5个角度介绍一下MySQL优化的一些策略。

1. 连接配置优化

处理连接是MySQL客户端和MySQL服务端亲热的第一步，第一步都迈不好，也就别谈后来的故事了。

既然连接是双方的事情，我们自然从服务端和客户端两个方面来进行优化喽。

1.1 服务端配置

服务端需要做的就是尽可能地多接受客户端的连接，或许你遇到过error 1040: Too many connections的错误？就是服务端的胸怀不够宽广导致的，格局太小！

我们可以从两个方面解决连接数不够的问题：

1. 增加可用连接数，修改环境变量max_connections，默认情况下服务端的最大连接数为151个

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)
复制代码

2. 及时释放不活动的连接，系统默认的客户端超时时间是28800秒（8小时），我们可以把这个值调小一点

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)
复制代码

MySQL有非常多的配置参数，并且大部分参数都提供了默认值，默认值是MySQL作者经过精心设计的，完全可以满足大部分情况的需求，不建议在不清楚参数含义的情况下贸然修改。

1.2 客户端优化

客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接能凑合用就凑合用，别每次执行个SQL语句都创建个新连接，服务端和客户端的资源都吃不消啊。

解决的方案就是使用连接池来复用连接。

常见的数据库连接池有DBCP、C3P0、阿里的Druid、Hikari，前两者用得很少了，后两者目前如日中天。

但是需要注意的是连接池并不是越大越好，比如Druid的默认最大连接池大小是8，Hikari默认最大连接池大小是10，盲目地加大连接池的大小，系统执行效率反而有可能降低。为什么？

对于每一个连接，服务端会创建一个单独的线程去处理，连接数越多，服务端创建的线程自然也就越多。而线程数超过CPU个数的情况下，CPU势必要通过分配时间片的方式进行线程的上下文切换，频繁的上下文切换会造成很大的性能开销。

Hikari官方给出了一个PostgreSQL数据库连接池大小的建议值公式，CPU核心数*2+1。假设服务器的CPU核心数是4，把连接池设置成9就可以了。这种公式在一定程度上对其他数据库也是适用的，大家面试的时候可以吹一吹。

2. 架构优化

2.1 使用缓存

系统中难免会出现一些比较慢的查询，这些查询要么是数据量大，要么是查询复杂（关联的表多或者是计算复杂），使得查询会长时间占用连接。

如果这种数据的实效性不是特别强（不是每时每刻都会变化，例如每日报表），我们可以把此类数据放入缓存系统中，在数据的缓存有效期内，直接从缓存系统中获取数据，这样就可以减轻数据库的压力并提升查询效率。

2.2 读写分离（集群、主从复制）

项目的初期，数据库通常都是运行在一台服务器上的，用户的所有读写请求会直接作用到这台数据库服务器，单台服务器承担的并发量毕竟是有限的。

针对这个问题，我们可以同时使用多台数据库服务器，将其中一台设置为为小组长，称之为master节点，其余节点作为组员，叫做slave。用户写数据只往master节点写，而读的请求分摊到各个slave节点上。这个方案叫做读写分离。给组长加上组员组成的小团体起个名字，叫集群。

注：很多开发者不满master-slave这种具有侵犯性的词汇（因为他们认为会联想到种族歧视、黑人奴隶等），所以发起了一项更名运动。

受此影响MySQL也会逐渐停用master、slave等术语，转而用source和replica替代，大家碰到的时候明白即可。

使用集群必然面临一个问题，就是多个节点之间怎么保持数据的一致性。毕竟写请求只往master节点上发送了，只有master节点的数据是最新数据，怎么把对master节点的写操作也同步到各个slave节点上呢？

主从复制技术来了！我在一条SQL更新语句是如何执行的？中粗浅地介绍了一下binlog日志，我直接搬过来了。

binlog是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。

这种集群的架构对减轻主数据库服务器的压力有非常好的效果，但是随着业务数据越来越多，如果某张表的数据量急剧增加，单表的查询性能就会大幅下降，而这个问题是读写分离也无法解决的，毕竟所有节点存放的是一模一样的数据啊，单表查询性能差，说的自然也是所有节点性能都差。

这时我们可以把单个节点的数据分散到多个节点上进行存储，这就是分库分表。

2.3 分库分表

分库分表中的节点的含义比较宽泛，要是把数据库作为节点，那就是分库；如果把单张表作为节点，那就是分表。

大家都知道分库分表分成垂直分库、垂直分表、水平分库和水平分表，但是每次都记不住这些概念，我就给大家详细说一说，帮助大家理解。

2.3.1 垂直分库

在单体数据库的基础上垂直切几刀，按照业务逻辑拆分成不同的数据库，这就是垂直分库啦。

2.3.2 垂直分表

垂直分表就是在单表的基础上垂直切一刀（或几刀），将一个表的多个字短拆成若干个小表，这种操作需要根据具体业务来进行判断，通常会把经常使用的字段（热字段）分成一个表，不经常使用或者不立即使用的字段（冷字段）分成一个表，提升查询速度。

拿上图举例：通常情况下商品的详情信息都比较长，而且查看商品列表时往往不需要立即展示商品详情（一般都是点击详情按钮才会进行显示），而是会将商品更重要的信息（价格等）展示出来，按照这个业务逻辑，我们将原来的商品表做了垂直分表。

2.3.3 水平分表

把单张表的数据按照一定的规则（行话叫分片规则）保存到多个数据表上，横着给数据表来一刀（或几刀），就是水平分表了。

2.3.4 水平分库

水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。

2.3.5 总结

水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。

2.4 消息队列削峰

通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。

这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。

3. 优化器——SQL分析与优化

处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。

只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。

3.1 慢查询

慢查询就是执行地很慢的查询（这句话说得跟废话似的。。。），只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。

因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)
复制代码

slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。

除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
复制代码

3.1.1 打开慢日志

有两种打开慢日志的方式

1. 修改配置文件my.cnf

此种修改方式系统重启后依然有效

# 是否开启慢查询日志
slow_query_log=ON
# 
long_query_time=2
slow_query_log_file=/var/lib/mysql/slow.log
复制代码

2. 动态修改参数（重启后失效）

mysql> set @@global.slow_query_log=1;
Query OK, 0 rows affected (0.06 sec)

mysql> set @@global.long_query_time=2;
Query OK, 0 rows affected (0.00 sec)
复制代码

3.1.2 慢日志分析

MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：

mysql> SELECT sleep(5);
复制代码

然后我们查询用时最多的1条慢查询：

[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log

Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  SELECT sleep(N)
复制代码

其中，

• Count：表示这个SQL执行的次数
• Time：表示执行的时间，括号中的是累积时间
• Locks：表示锁定的时间，括号中的是累积时间
• Rows：表示返回的记录数，括号中的是累积数

更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。

3.2 查看运行中的线程

我们可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。

其中，

• Id：线程的唯一标志，可以使用Id杀死指定线程
• User：启动这个线程的用户，普通账户只能查看自己的线程
• Host：哪个ip和端口发起的连接
• db：线程操作的数据库
• Command：线程的命令
• Time：操作持续时间，单位秒
• State：线程的状态
• Info：SQL语句的前100个字符

3.3 查看服务器运行状态

使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。

-- 查看select的次数
mysql> SHOW GLOBAL STATUS LIKE 'com_select';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 168241 |
+---------------+--------+
1 row in set (0.05 sec)
复制代码

3.4 查看存储引擎运行信息

SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。

例如：

SHOW ENGINE INNODB STATUS;
复制代码

上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。

3.5 EXPLAIN执行计划

通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？

MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询...）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么，等等等等。

EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。

这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节，之后单独成篇。

3.6 SQL与索引优化

3.6.1 SQL优化

SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：

• 使用小表驱动大表；用join改写子查询；or改成union
• 连接查询中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本；被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低
• 大偏移量的limit，先过滤再排序

针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB），大家感受一下：

-- 1. 大偏移量的查询
mysql> SELECT * FROM user_innodb LIMIT 9000000,10;
Empty set (8.18 sec)

-- 2.先过滤ID（因为ID使用的是索引），再limit
mysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;
Empty set (0.02 sec)
复制代码

3.6.2 索引优化

为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。

我之前写过一篇用好MySQL索引，你必须知道的一些事情 ，感兴趣的读者可以看一下。

4. 存储引擎与表结构

4.1 选择存储引擎

一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。

建议根据不同的业务选择不同的存储引擎，例如：

• 查询操作、插入操作多的业务表，推荐使用MyISAM；
• 临时表使用Memory；
• 并发数量大、更新多的业务选择使用InnoDB；
• 不知道选啥直接默认。

4.2 优化字段

字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。

4.2.1 整数类型

MySQL提供了6种整数类型，分别是

• tinyint
• smallint
• mediumint
• int
• integer
• bigint

不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。

例如，是否被删除的标识，建议选用tinyint，而不是bigint。

4.2.2 字符类型

你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不够，还会直接设置成varchar(1024)的长度？

如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。

4.2.3 非空

非空字段尽量设置成NOT NULL，并提供默认值，或者使用特殊值代替NULL。

因为NULL类型的存储和优化都会存在性能不佳的问题，具体原因在这里就不展开了。

4.2.4 不要用外键、触发器和视图功能

这也是「阿里巴巴开发手册」中提到的原则。原因有三个：

1. 降低了可读性，检查代码的同时还得查看数据库的代码；
2. 把计算的工作交给程序，数据库只做好存储的工作，并把这件事情做好；
3. 数据的完整性校验的工作应该由开发者完成，而不是依赖于外键，一旦用了外键，你会发现测试的时候随便删点垃圾数据都变得异常艰难。

4.2.5 图片、音频、视频存储

不要直接存储大文件，而是要存储大文件的访问地址。

4.2.6 大字段拆分和数据冗余

大字段拆分其实就是前面说过的垂直分表，把不常用的字段或者数据量较大的字段拆分出去，避免列数过多和数据量过大，尤其是习惯编写SELECT * 的情况下，列数多和数据量大导致的问题会被严重放大！

字段冗余原则上不符合数据库设计范式，但是却非常有利于快速检索。比如，合同表中存储客户id的同时可以冗余存储客户姓名，这样查询时就不需要再根据客户id获取用户姓名了。因此针对业务逻辑适当做一定程度的冗余也是一种比较好的优化技巧。

5. 业务优化

严格来说，业务方面的优化已经不算是MySQL调优的手段了，但是业务的优化却能非常有效地减轻数据库访问压力，这方面一个典型例子就是淘宝，下面举几个简单例子给大家提供一下思路：

1. 以往都是双11当晚开始买买买的模式，最近几年双11的预售战线越拉越长，提前半个多月就开始了，而且各种定金红包模式丛出不穷，这种方式叫做预售分流。这样做可以分流客户的服务请求，不必等到双十一的凌晨一股脑地集体下单；
2. 双十一的凌晨你或许想查询当天之外的订单，但是却查询失败；甚至支付宝里的小鸡的口粮都被延迟发放了，这是一种降级策略，集结不重要的服务的计算资源，用来保证当前最核心的业务；
3. 双十一的时候支付宝极力推荐使用花呗支付，而不是银行卡支付，虽然一部分考量是提高软件粘性，但是另一方面，使用余额宝实际使用的阿里内部服务器，访问速度快，而使用银行卡，需要调用银行接口，相比之下操作要慢了许多。

6.SQL优化小技巧

前面聊了一些写SQL的基本功后，接着来聊一聊本文的核心：SQL优化，所谓的高手和普通人之间，最大的不同在于能将相同的事情做到更好，比如送外卖，相同的时间内一个人能够送的更多，这是个送外卖的高手。比如玩游戏，相同的角色和装备，一个人的战绩能够更出色，那这是个打游戏的高手......。

上述的道理放在编程中同样适用，一个人代码敲得更快、代码敲的更多、执行效率越高，这也可以被称为是一个写代码的高手，俗称“码农Pro Max”，那作为一个普通码农，如何达到“码农Pro、码农Plus、码农Pro Max.....”的境界呢？首先你得能够写出一手好SQL！

掌握了写SQL的基本功后，足以让你写代码的效率提升，但引言中就聊到过：写的快不代表写的好，就算你能够日码三万行，并且还能满足业务需求，这也不见得的能被称之为高手，真正的SQL高手除开编写效率够高之外，对于每条SQL的执行效率也要可控。如果写的多，但有些业务SQL在大数据的情况下，一跑就是十多秒，这是万万不可的！

那么问题又来了：如何让自己的SQL又快又好呢？答案其实非常简单，减小查询的数据量、提升SQL的索引命中率即可，接着先来说说撰写SQL时的一些注意点。

6.1、编写SQL时的注意点

在写SQL的时候，往往很多时候的细节不注意，就有可能导致索引失效，也因此会造成额外的资源开销，而我们要做的就是避开一些误区，确保自己的SQL在执行过程中能够最大程度上节省资源、缩短执行时间，下面罗列一些经典的SQL注意点。

6.1.1、查询时尽量不要使用*

一般在写SQL为了方便，所以通常会采用*来代替所有字段，毕竟用*号只要按键盘一下，写字段则需要一个个字段名去写。写*的确能让程序员更省力，但对机器就不太友好了，因此在写查询语句时一律不要使用*代替所有字段，这条准则相信大家都知道，但到底是为什么呢？

其实主要有如下几方面的原因：

• ①分析成本变高。

在《SQL执行篇》中聊过，一条SQL在执行前都会经过分析器解析，当使用*时，解析器需要先去解析出当前要查询的表上*表示哪些字段，因此会额外增加解析成本。但如果明确写出了查询字段，分析器则不会有这一步解析*的开销。

• ②网络开销变大。

当使用*时，查询时每条数据会返回所有字段值，然后这些查询出的数据会先被放到结果集中，最终查询完成后会统一返回给客户端，但线上Java程序和MySQL都是分机器部署的，所以返回数据时需要经过网络传输，而由于返回的是所有字段数据，因此网络数据包的体积就会变大，从而导致占用的网络带宽变高，影响数据传输的性能和资源开销。但实际上可能仅需要用到其中的某几个字段值，所以写清楚字段后查询，能让网络数据包体积变小，从而减小资源消耗、提升响应速度。

• ③内存占用变高。

在《MySQL内存篇》中曾详细讲到了InnoDB引擎的工作原理，当查询一条数据时都会将其结果集放入到BufferPool的数据缓冲页中，如果每次用*来查询数据，查到的结果集自然会更大，占用的内存也会越大，单个结果集的数据越大，整个内存缓冲池中能存下的数据也就越少，当其他SQL操作时，在内存中找不到数据，又会去触发磁盘IO，最终导致MySQL整体性能下降。

• ④维护性变差。

用过MyBatis框架的小伙伴应该都知道一点，一般为了对应查询结果与实体对象的关系，通常都需要配置resultMap来声明表字段和对象属性的映射关系，但如果每次使用*来查询数据，当表结构发生变更时，就算变更的字段结构在当前业务中用不到，也需要去维护已经配置好的resultMap，所以会导致维护性变差。但声明了需要的字段时，配置的resultMap和查询字段相同，因此当变更的表结构不会影响当前业务时，也无需变更当前的resultMap。

综上所述，使用*的情况下反而会带来一系列弊端，所以能显示写明所需字段的情况下，尽量写明所需字段，除开上述原因外，还有一点最关键的原因：基于非主键字段查询可能会产生回表现象，如果是基于联合索引查询数据，需要的结果字段在联合索引中有时，可能通过索引覆盖原理去读数据，从而减少一次回表查询。但使用*查询所有字段数据时，由于联合索引中没有完整数据，因此只能做一次回表从聚簇索引中拿数据，对于索引覆盖感兴趣的可参考之前的《索引应用篇-索引覆盖机制》。

6.1.2、连表查询时尽量不要关联太多表

对于这点的原因其实很简单，一旦关联太多的表，就会导致执行效率变慢，执行时间变长，原因如下：

• 数据量会随表数量呈直线性增长，数据量越大检索效率越低。
• 当关联的表数量过多时，无法控制好索引的匹配，涉及的表越多，索引不可控风险越大。

一般来说，交互型的业务中，关联的表数量应当控制在5张表之内，而后台型的业务由于不考虑用户体验感，有时候业务比较复杂，又需要关联十多张表做查询，此时可以这么干，但按照《高性能MySQL》上的推荐，最好也要控制在16~18张表之内（阿里开发规范中要求控制在3张表以内）。

6.1.3、多表查询时一定要以小驱大

所谓的以小驱大即是指用小的数据集去驱动大的数据集，说简单一点就是先查小表，再用小表的结果去大表中检索数据，其实在MySQL的优化器也会有驱动表的优化，当执行多表联查时，MySQL的关联算法为Nest Loop Join，该算法会依照驱动表的结果集作为循环基础数据，然后通过该结果集中一条条数据，作为过滤条件去下一个表中查询数据，最后合并结果得到最终数据集，MySQL优化器选择驱动表的逻辑如下：

• ①如果指定了连接条件，满足查询条件的小数据表作为驱动表。
• ②如果未指定连接条件，数据总行数少的表作为驱动表。

如果在做连表查询时，你不清楚具体用谁作为驱动表，哪张表去join哪张表，这时可以交给MySQL优化器自己选择，但有时候优化器不一定能够选择正确，因此写SQL时最好自己去选择驱动表，小表放前，大表放后！

举个例子感受一下两者之间的区别，假设zz_student学生表中有10000条数据，zz_class班级表中有100条数据，当需要关联这两张表查询数据时，SQL如下：

-- 大表在前，小表在后
select * from zz_student as s left join zz_class as c on s.class_id = c.class_id;
-- 小表在前，大表在后
select * from zz_class as c left join zz_student as s on c.class_id = s.class_id;
复制代码

上述是两种联查的SQL语法，如果学生表在前作为驱动表，根据Nest Loop Join算法会循环一万次查询数据，而反之如果班级表在前，则只需要循环100次即可查询出数据，因此诸位在写SQL时一定要记得将小表作为驱动表。

这个道理不仅仅只存在于多表关联查询中，只要涉及到多表查询的情况，都需遵循该原则，比如使用子查询进行多表查询时，请确保结果集小的SQL先执行。

举个子查询的小表驱动大表的例子：

select * from xxx where yyy in (select yyy from zzz where ....);
复制代码

MySQL在执行上述这条SQL时，会先去执行in后面的子查询语句，这时尽量要保证子查询的结果集小于in前面主查询的结果集，这样能够在一定程度上减少检索的数据量。通常使用in做子查询时，都要确保in的条件位于所有条件的最后面，这样能够在最大程度上减小多表查询的数据匹配量，如下：

- 优化前：select xxx,xxx,xxx from table where colum in(sql) and id = 10;
- 优化后：select xxx,xxx,xxx from table where id = 10 and colum in(sql);
复制代码

以小驱大这个规则也可以进一步演化，也就是当查询多张表数据时，如果有多个字段可以连接查询，记得使用and来拼接多个联查条件，因为条件越精准，匹配的数据量就越少，查询速度自然会越快。

对于单表查询时也是如此，比如要对数据做分组过滤，可以先用where过滤掉一部分不需要的数据后，再对处理后的数据做分组排序，因为分组前的数据量越小，分组时的性能会更好！

可以把SQL当成一个链式处理器，每一次新的子查询、关联查询、条件处理....等情况时，都可以看成一道道的工序，我们在写SQL时要注意的是：在下一道工序开始前尽量缩小数据量，为下一道工序尽可能提供更加精准的数据。

6.1.4、不要使用like左模糊和全模糊查询

对于这点的原因十分明显，因为在之前《索引应用篇-索引失效场景》中聊到过，如若like关键字以%号开头会导致索引失效，从而导致SQL触发全表查询，因此需要使用模糊查询时，千万要避免%xxx、%xxx%这两种情况出现，实在需要使用这两类模糊查询时，可以适当建立全文索引来代替，数据量较大时可以使用ES、Solr....这类搜索引擎来代替。

6.1.5、查询时尽量不要对字段做空值判断

select * from xxx where yyy is null;
select * from xxx where yyy not is null;
复制代码

当出现基于字段做空值判断的情况时，会导致索引失效，因为判断null的情况不会走索引，因此切记要避免这样的情况，一般在设计字段结构的时候，请使用not null来定义字段，同时如果想为空的字段，可以设计一个0、""这类空字符代替，一方面要查询空值时可通过查询空字符的方式走索引检索，同时也能避免MyBatis注入对象属性时触发空指针异常。

6.1.6、不要在条件查询=前对字段做任何运算

select * from zz_users where user_id * 2 = 8;
select * from zz_users where trim(user_name) = "熊猫";
复制代码

zz_users用户表中user_id、user_name字段上都创建了索引，但上述这类情况都不会走索引，因为MySQL优化器在生成执行计划时，发现这些=前面涉及到了逻辑运算，因此就不会继续往下走了，会将具体的运算工作留到执行时完成，也正是由于优化器没有继续往下走，因此不会为运算完成后的字段选择索引，最终导致索引失效走全表查询。

从这里可以得出一点，千万不要在条件查询的=前，对字段做任何运算，包括了函数的使用也不允许，因为经过运算处理后的字段会变成一个具体的值，而并非字段了，所以压根无法使用到索引！

6.1.7、 !=、!<>、not in、not like、or...要慎用

这点可参考《索引应用篇-索引失效场景》中给出的示例，简单来说就是这类写法也可能导致索引失效，因此在实际过程中可以使用其他的一些语法代替，比如or可以使用union all来代替：

select user_name from zz_users where user_id=1 or user_id=2;
-- 可以替换成：
select user_name from zz_users where user_id=1
union all
select user_name from zz_users where user_id=2;
复制代码

虽然这样看起来SQL变长了，但实际情况中查询效率反而更高一些，因为后面的SQL可以走索引（对于其他的一些关键字也一样，可以使用走索引的SQL来代替这些关键字实现）。

6.1.8、必要情况下可以强制指定索引

在表中存在多个索引时，有些复杂SQL的情况下，或者在存储过程中，必要时可强制指定某条查询语句走某个索引，因为MySQL优化器面对存储过程、复杂SQL时并没有那么智能，有时可能选择的索引并不是最好的，这时我们可以通过force index，如下：

select * from zz_users force index(unite_index) where user_name = "熊猫";
复制代码

这样就能够100%强制这条SQL走某个索引查询数据，但这种强制指定索引的方式，一定要建立在对索引结构足够熟悉的情况下，否则效果会适得其反。

6.1.10、避免频繁创建、销毁临时表

临时表是一种数据缓存，对于一些常用的查询结果可以为其建立临时表，这样后续要查询时可以直接基于临时表来获取数据，MySQL默认会在内存中开辟一块临时表数据的存放空间，所以走临时表查询数据是直接基于内存的，速度会比走磁盘检索快上很多倍。但一定要切记一点，只有对于经常查询的数据才对其建立临时表，不要盲目的去无限制创建，否则频繁的创建、销毁会对MySQL造成不小的负担。

6.1.11、尽量将大事务拆分为小事务执行

经过之前《MySQL事务机制》、《MySQL锁机制》、《MySQL事务与锁实现原理》这几章的学习后，咱们应该会知道：一个事务在执行事，如果其中包含了写操作，会先获取锁再执行，直到事务结束后MySQL才会释放锁。

而一个事务占有锁之后，会导致其他要操作相同数据的事务被阻塞，如果当一个事务比较大时，会导致一部分数据的锁定周期较长，在高并发情况下会引起大量事务出现阻塞，从而最终拖垮整个MySQL系统。

• show status like 'innodb_log_waits';查看是否有大事务由于redo_log_buffer不足，而在等待写入日志。

大事务也会导致日志写入时出现阻塞，这种情况下会强制触发刷盘机制，大事务的日志需要阻塞到有足够的空间时，才能继续写入日志到缓冲区，这也可能会引起线上出现阻塞。

因此基于上述原因，在面对一个较大的事务时，能走异步处理的可以拆分成异步执行，能拆分成小事务的则拆成小事务，这样可以在很大程度上减小大事务引起的阻塞。

6.1.12、从业务设计层面减少大量数据返回的情况

之前在做项目开发时碰到过一些奇葩需求，就是要求一次性将所有数据全部返回，而后在前端去做筛选展现，这样做虽然也可以，但如果一次性返回的数据量过于巨大时，就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现，因此如果项目中存在这类业务，一定要记住拆分掉它，比如分批返回给客户端。

分批查询的方式也被称之为增量查询，每次基于上次返回数据的界限，再一次读取一批数据返回给客户端，这也就是经典的分页场景，通过分页的思想能够提升单次查询的速度，以及避免大数据量带来的一系列后患问题。

6.1.13、尽量避免深分页的情况出现

前面刚刚聊过分页，分页虽然比较好，但也依旧存在问题，也就是深分页问题，如下：

select xx,xx,xx from yyy limit 100000,10; 
复制代码

上述这条SQL相当于查询第1W页数据，在MySQL的实际执行过程中，首先会查询出100010条数据，然后丢弃掉前面的10W条数据，将最后的10条数据返回，这个过程无异极其浪费资源。

哪面对于这种深分页的情况该如何处理呢？有两种情况。

如果查询出的结果集，存在递增且连续的字段，可以基于有序字段来进一步做筛选后再获取分页数据，如下：

select xx,xx,xx from yyy where 有序字段 >= nnn limit 10; 
复制代码

也就是说这种分页方案是基于递增且连续字段来控制页数的，如下：

-- 第一页
select xx,xx,xx from yyy where 有序字段 >= 1 limit 10; 
-- 第二页
select xx,xx,xx from yyy where 有序字段 >= 11 limit 10; 
-- 第N页.....

-- 第10000页
select xx,xx,xx from yyy where 有序字段 >= 100001 limit 10; 
复制代码

这种情况下，MySQL就会先按where条件筛选到数据之后，再获取前十条数据返回，甚至还可以通过between做优化：

select xx,xx,xx from yyy where 有序字段 between 1000000 and 1000010; 
复制代码

这种方式就完全舍弃了limit关键字来实现分页，但这种方式仅适合于基于递增且连续字段分页。

那么例如搜索分页呢？这种分页情况是无序的，因为搜索到的数据可以位于表中的任意行，所以搜索出的数据中，就算存在有序字段，也不会是连续的，这该如何是好？这种情况下就只能在业务上限制深分页的情况出现了，以百度为例：

虽然搜索mysql关键字之后，显示大约搜索到了一亿条数据，但当咱们把分页往后拉就会发现，最大只能显示76页，当你再尝试往后翻页时就会看到一个提示：“限于网页篇幅，部分结果未予显示”。

上述百度的这个例子中，就从根源上隔绝了深分页的出现，毕竟你都没给用户提供接下来的分页按钮了，这时自然也就无法根据用户操作生成深分页的SQL。

但上述这种思想仅局限于业务允许的情况下，以搜索为例，一般用户最多看前面30页，如果还未找到他需要的内容，基本上就会换个更精准的关键词重新搜索。

哪如果业务必须要求展现所有分页数据，此时又不存在递增的连续字段咋办？哪这种情况下要么选择之前哪种很慢的分页方式，要么就直接抛弃所有！每次随机十条数据出来给用户，如果不想重复的话，每次新的分页时，再对随机过的数据加个标识即可。

6.1.14、SQL务必要写完整，不要使用缩写法

很多开发者，包含我在内，往往都喜欢缩写语法，能够简写的绝不写全，比如：

-- 为字段取别名的简单写法
select user_name "姓名" from zz_users;
-- 为字段取别名的完整写法
select user_name as "姓名" from zz_users;

-- 内连表查询的简单写法
select * from 表1,表2... where 表1.字段 = 表2.字段 ...; 
-- 内连表查询的完整写法
select * from 表1 别名1 inner join 表2 别名2 on 别名1.字段 = 别名2.字段;

......
复制代码

这类情况下还有很多，在写的时候为了图简单，都会将一些能简写的SQL就简写，但其实这种做法也略微有些问题，因为隐式的这种写法，在MySQL底层都需要做一次转换，将其转换为完整的写法，因此简写的SQL会比完整的SQL多一步转化过程，如果你考虑极致程度的优化，也切记将SQL写成完整的语法。

6.1.15、基于联合索引查询时请务必确保字段的顺序性

在之前聊到过《联合索引的最左前缀原则》，想要基于建立的联合索引查询数据，就必须要按照索引字段的顺序去查询数据，否则可能导致所以完全利用联合索引，虽然MySQL8.0版本中推出了《索引跳跃扫描机制》，但这种方案也会存在较大的开销，同时还有很强的局限性，所以最好在写SQL时，依旧遵循索引的最左前缀原则撰写。

6.1.16、客户端的一些操作可以批量化完成

批量新增某些数据、批量修改某些数据的状态.....，这类需求在一个项目中也比较常见，一般的做法如下：

for (xxObject obj : xxObjs) {
    xxDao.insert(obj);
}

/**
 * xxDao.insert(obj)对应的SQL如下：
 * insert into tb_xxx values(......);
**/
复制代码

这种情况确实可以实现批量插入的效果，但是每次都需要往MySQL发送SQL语句，这其中自然会带来额外的网络开销以及耗时，因此上述实现可以更改为如下：

xxDao.insertBatch(xxObjs);

/**
 * xxDao.insertBatch(xxObjs)对应的SQL如下：
 * insert into tb_xxx values(......),(......),(......),(......),.....;
**/
复制代码

这样会组合成一条SQL发送给MySQL执行，能够在很大程度上节省网络资源的开销，提升批量操作的执行效率。

这样的方式同样适用于修改场景，如果一个业务会出现批量修改的情况时，也切记不要用for循环来调用update语句对应的接口，而是应该再写一个update/replace语句的批量修改接口。

6.1.17、明确仅返回一条数据的语句可以使用limit 1

select * from zz_users where user_name = "竹子";
select * from zz_users where user_name = "竹子" limit 1;
复制代码

上述这两条SQL语句都是根据姓名查询一条数据，但后者大多数情况下会比前者好，因为加上limit 1关键字后，当程序匹配到一条数据时就会停止扫描，如果不加的情况下会将所有数据都扫描一次。所以一般情况下，如果确定了只需要查询一条数据，就可以加上limit 1提升性能。

但在一些极端情况下，性能可能相差不大，比如要查询的数据位于表/索引文件的最后面，那么依旧会全部扫描一次。还有一种情况是基于主键/唯一索引字段查询数据时，由于这些字段值本身具备唯一性，因此MySQL在执行时，当匹配到第一个值时就会自动停止扫描，因此上述这个方案只适用于普通索引字段、或表中的普通字段。