数据库查询性能优化方法3：合理设计查询语句

查询性能优化，有3个方法。

• 如何设计最优的库表结构

• 如何建立最好的索引

• 合理的设计查询

库表结构优化、索引优化、查询优化需要齐头并进，一个不落。

在尝试编写快速的查询之前，需要清楚一点，真正重要是响应时间。

提升性能的两个方法：空间换时间，随机转顺序

方法1：设计最优的库表结构，参考：数据库查询性能优化方法1：设计最优的库表结构

方法2：建立最好的索引，参考：数据库查询性能优化方法2：建立最好的索引

本篇为方法3：合理设计查询语句

合理设计查询语句

1. 确认是否返回了大量的无用数据

查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效。

1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。

2. 确认MySQL 服务器层是否在分析大量超过需要的数据行。

一个常见的错误是常常会误以为MySQL 会只返回需要的数据，实际上MySQL 却是先返回全部结果集再进行计算。我们经常会看到一些了解其他数据库系统的人会设计出这类应用程序。这些开发者习惯使用这样的技术，先使用SELECT 语句查询大量的结果，然后获取前面的N行后关闭结果集（例如在新闻网站中取出100 条记录，但是只是在页面上显示前面10条）。他们认为MySQL会执行查询，并只返回他们需要的10条数据，然后停止查询。实际情况是MySQL会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。

每次看到SELECT * 的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗。因此，一些DBA是严格禁止SELECT *的写法的，这样做有时候还能避免某些列被修改带来的问题。

当然，查询返回超过需要的数据也不总是坏事。在我们研究过的许多案例中，人们会告诉我们说这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性，如果清楚这样做的性能影响，那么这种做法也是值得考虑的。如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。

例如，在用户评论的地方需要查询用户头像的URL，那么用户多次评论的时候，可能就会反复查询这个数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

2. 禁止使用select *，必须使用select<字段列表>查询

• 原因1：select * 查询不需要字段，消耗更多的cpu和io以及网络带宽资源
• 原因2：无法使用覆盖索引
• 原因3：可减少表结构变更带来的影响

3. 充分利用表上已经存在的索引

1. 避免使用双%号这样的查询条件， 如 a like ‘%123%或 b like ‘%123’，而c like ‘123%’是可以使用索引的

2. 一个sql只能利用到复合索引中的一列进行范围查询，如果某一列经常用到联合查找的时候，应该把索引放到联合索引的右侧

   比如，对于联合索引 index(a,b)，用到了这个索引，不需要进行filesort

   select * from t where a = ? order by b
   

3. 使用left join或not exists来优化not in操作，因为not in操作会使索引失效

   参考：10. 优化not in 和<>查询

4. where从句中禁止对列进行函数转换和计算

   索引列上不能使用表达式或函数

   养成简化WHERE 条件的习惯，始终将索引列单独放在比较符号的一侧

   对列进行函数转换或计算会导致无法使用索引，createtime列上有索引

   where date(createtime) = '20160901'
   

   改为下面就可以使用索引了

   where createtime >= '20160901' and createtime <= '20160901'
   

5. 通过覆盖索引进行回表优化

   回表优化案例：

   create table UserInfo(
       userid int not null auto_increment,
       username varchar(30),
       registdate datetime,
       email varchar(50),
       primary key(userid),
       key idex_registdate(registdate)
   )
   

   做下面的查询

   select email from userinfo where username = 'david';
   

   方法1：使用覆盖索引covering index(username, email)

   加入unique key idx_username(username, email)

   create table UserInfo(
       userid int not null auto_increment,
       username varchar(30),
       registdate datetime,
       email varchar(50),
       primary key(userid),
       unique key idx_username(username, email),
       key idex_registdate(registdate)
   )
   

   这样可以，但是会带来一个副作用，我们并不需要对username进行排序，但由于iindex(username, email）这个二级索引，导致要对username进行排序，会影响插入效率

   方法2：拆表的使用场景1：index with included column

   原建表语句

   create table idx_username_include_email(
       userid int not null auto_increment,
       username varchar(30),
       email varchar(50),
       primary key(username, email),
       unique key idx_username(userid)
   )
   

   begin;
   insert into UserInfo xxx
   insert into idx_username_include_email xxx
   commit;
   -- better to use Stored Procedure
   

   插入两张表没有问题，可以使用store procedure 一个事务或触发器trigger（本身就是事务的）来做

   但是应用要改，应用要使用右侧这张表

   select email from idx_username_include_email where username = 'david';
   

   其实思路也是以空间换时间

   通常可能并不会这样做，因为即便使用方法1复合索引，与这种拆表法相比，可能开销也不是很大

4. 增加索引

如果查询没有办法找到合适的获取数据的方式（explain type字段），那么解决的最好办法通常就是增加一个合适的索引，这正是在方法2中讨论过的问题。现在应该明白为什么索引对于查询优化如此重要了。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为

1. 在索引中使用where条件来过滤不匹配的记录。这是在存储引擎层完成的。

   比如：where 主键列=xxx 这种方式

2. 使用索引覆盖扫描（在Extra 列中出现了Using index ）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。

   比如：where 二级索引列=xxx 这种方式

3. 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。MySQL需要先从数据表读出记录然后在MySQL服务器层完成过滤。

   比如：where 无索引列 = xxx 这种方式

5. 尽量减少同数据库的交互次数

如可以一次提取100条数据来进行分页显示，而不是每次请求10条数据

数据库更适合处理批量操作，结果集返回100条，还是10条，其实对数据库内部资源的消耗差不多

合并多个相同的操作到一起，可以提高处理效率。

6. 拆分复杂的大sql为多个小sql

在某些版本的MySQL 上，即使在一个通用服务器上，也能够运行每秒超过10万的查询，即使是一个千兆网卡也能轻松满足每秒超过2000 次的查询。所以运行多个小查询现在已经不是大问题了。

MySQL 内部每秒能够扫描内存中上百万行数据，相比之下，MySQL 响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。所以在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。

但是有时候，将一个大查询分解为多个小查询是很有必要的。

删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE 语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。

7. 优化子查询

子查询的优点是清晰，但效率并不好

当子查询在In子句中，并且子查询是一个简单的sql时，即不包含union，group by，order by，limit这些，可以把子查询优化为join操作

子查询的缺点

子查询的结果集无法使用索引，子查询会产生临时表操作，临时表都不会存在索引，效率就比较差

8. sql优化-join算法

simple nested_loop join

算法可以理解成两个for循环，外循环走一次，内循环走N次（N=外循环的次数）

算法伪代码如下：

For each row r in R do #扫描R表
	For each row s in S do #扫描S表
		if r and s satisfy the join condition #如果r和s满足join条件
			then output the tuple <r,s>

1. R表，该表只扫描了一次
2. S表，该表扫描了count®次

该方法相当于是一个笛卡尔积，实际上数据库不会使用该算法

注意，simple nested_loop join算法在mysql中根本不会使用，即便关联的两个表上根本没有索引。

其使用的是block nested_loop join算法

index nested_loop join

通过索引进行扫描，这个索引是在内表中，索引中查询的代价，我们认为是固定的，所以优化器倾向于使用小表作为外表，大表通过索引来查询。

倒转的关联顺序会让查询进行更少的嵌套循环和回溯操作。用小表去左关联大表

# 伪代码
For each row r in R do
	loopup r in S index
		if found s==r
			then output the tuple <r,s>

扫描成本：O(Rn)

优化器倾向于使用小表做驱动表

类似与 select * from a join b where a.x = b.x

很多人在表a 的 列x 和 表b的 列x都创建索引，其实并没有这个必要，执行的时候用到的索引只是一个索引（优化器倾向于使用小表做驱动程序），在这里R是titles表，Index S是titles表上的列x索引，S表是employees表

可以看到，employees是外表，titles是内表，关联的列是emp_no

但也存在问题：

如果数据量大，index nested loop join的成本也是高的，尤其是在join二级索引的情况下，需要大量的回表操作，所以IO成本也是比较高的。

mysql 5.6中的BKA(batched key access) JOIN

不再对一条记录回表了，而是对多条记录进行排序（利用Multi-Range Read, MRR）

batched key access join

not enabled by default, optimize random I/O

# 伪代码
For each tuple in R do
	store used columns as p from R in join buffer
	For each tuple in S do
		if p and s satisfy the join condition
			use mrr interface to sort row Id
			then output the tuple<p,s>

set optimizer_switch='mrr=on, mrr_cost_based=off, batched_key_access=on'

通过MRR和BKA Join可以看到，mysql的优化器还是不够智能的，在join这块是handle不住的。我们谈的是OLAP，MRR和BKA Join都是要手动打开的，告诉优化器不对这两项进行cost计算，否则优化器算出来的cost都是很大的，也就是不会采用的。

block nested_loop join

优化simple nested_loop join，减少内部表的扫描次数

# 伪代码
For each tuple in R do
	store used columns as p from R in join buffer #这一步simple nested_loop不存在
	For each tuple s in S do 
		if p and s satisfy the join condition
			then output the tuple <p,s>

系统变量join_buffer_size决定了join buffer的大小，join buffer可被用于联结是ALL，Index，Range的类型

join_buffer_size默认是256kb，与MRR类似，用的也是物理页，也是线程级别的

调大这个参数是要在使用block nested_loop join算法，那什么情况下使用block nested_loop join算法呢？当两个列上都没有索引的时候

要明确join buffer size中存储的是什么？只存储需要进行查询操作的相关列数据，而不是整行的记录。

下面看一下其扫描成本

伪代码看起来像是两层for循环，实际上并不是这样

其实是先for循环外表得到join buffer

然后循环内表，内表中循环join buffer，从而得到

对比Simple nested_loop join 和 block nested_loop join

比如R.x列为1,2,3,

S.x列为1,2,3,4

R.x = S.x

join算法	外表R scan次数	内表S scan次数	compare次数
simple nested_loop join	1	4	12
block nested_loop join 其join buffer中有1,2,3	1	1	12
如果是hash join的方式	1	1	4

内外表scan表示的是IO，compare次数表示的是cpu，所以可以看到hash nested_loop join算法对于IO和CPU都是节省的

比如说我内外表都是100W，那么用block其比较次数是100W*100W，而使用hash，其比较次数就是100W。所以说关联的时候，还是最好要创建索引（使用索引是Log(N)，应该是介于block和hash之间）

有一个前提条件是join buffer把A表中的列cache起来，如果都能cache起来，那类似于就是hash join了。如果join buffer中一次性放不下A表的数据，那B表还是要被扫描多次。假设A表有1000W 条数据，join buffer能存放10W 条数据，那B表需要被扫描100 次。

这10W条，扫描B表一次，之后再装载下面的10W，再扫描B表一次…

hash join

mysql不支持hash join，mariaDB支持

# 伪代码
For each tuple r in R do
	store used columns as p from R in join buffer
	build hash table according join buffer
	for each tuple s in S do
		probe hash table
		if find
			then output the tuple<p,s>

set join_cache_level = 4+;
set optimizer_switch = 'join_cache_hashed=on'

仍然是两个for循环，对比block nested_loop join

block nested_loop join：inner table(join buffer) may compare many times

hash join：inner table(join buffer) compare only once

是先for循环外表得到一张哈希表

然后循环内表，内表中匹配上面的哈希表，哈希表时间复杂度O(1)，而不是像block nested_loop join 是O(N），从而得到

9. 避免使用join关联太多的表

每join一个表会多占用一部分内存（join_buffer_size关联缓存大小），每关联一个表，就加入一个join_buffer_size

10. 优化not in 和<>查询

Not in 和 <> 操作无法使用索引

存在not in和<>的子查询，查询优化器不会把子查询转换为关联

如下面这个 查询没有缴费记录的用户名单

select customer_id, first_name, last_name, email
from customer
where customer_id not in (select customer_id from payment)

优化方法1：使用left join

select a.customer_id, a.first_name, a.last_name, a.email
from customer a
left join payment b on a.customer_id = b.customer_id
where b.customer_id is null;

优化方法2：使用not exists

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或者逻辑假值“false”

select * from t1 where t1.a in (select b from t2);

select * from t1 where exists (select 1 from t2 where t2.b == t1.a);

NOT EXISTS 和NOT IN两种写法

需求：没有产生过订单的nationkey为15的客户

NOT EXISTS写法

SELECT 
   count(*)
FROM
    customer
WHERE
    customer.c_nationkey = 15
        AND NOT EXISTS( SELECT 
            *
        FROM
            orders
        WHERE
            customer.c_custkey = orders.o_custkey)

NOT IN写法

SELECT 
    count(*)
FROM
    customer
WHERE
    customer.c_nationkey = 15
        AND c_custkey NOT IN(SELECT 
            o_custkey
        FROM
            orders);

比较两条sql语句的执行结果

NOT EXISTS和左外连接的性能比较

查询	qps（每秒查询数结果）
NOT EXISTS子查询	360 QPS
LEFT OUTER JOIN	425 QPS

11. 存在NULL值在in中是一个很危险的情况

注意NULL值的过滤，NULL表示不确定的值。所以存在NULL值在in中是一个很危险的情况

这时如果向order表里面插入一条空值

insert into orders(o_custkey) values (NULL)

由于not IN永远返回false和NULL，也就是后面这个条件永远不成立，返回就为空

SELECT 
    count(*)
FROM
    customer
WHERE
    customer.c_nationkey = 15
        AND c_custkey NOT IN(SELECT 
            o_custkey
        FROM
            orders);

所以我们在有NULL值的情况下使用not IN的时候就要小心点，要加上where o_custkey IS NOT NULL

SELECT 
    count(*)
FROM
    customer
WHERE
    customer.c_nationkey = 15
        AND c_custkey NOT IN(SELECT 
            o_custkey
        FROM
            orders where o_custkey IS NOT NULL);

12. 分解join关联查询

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

事实上，用分解关联查询的方式重构查询有如下的优势

1. 让缓存的效率更高。

   许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了ID为123、567、9098的内容，那么第三个查询的IN() 中就可以少几个ID。另外，对MySQL的查询缓存来说 ，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。

2. 将查询分解后，执行单个查询可以减少锁的竞争。

3. 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

4. 查询本身效率也可能会有所提升。这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。

5. 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。

6. 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联（block nested_loop join）。某些场景哈希关联的效率要高很多。

在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如：

• 当应用能够方便地缓存单个查询的结果的时候
• 当可以将数据分布到不同的MySQL服务器上的时候
• 当能够使用IN()的方式代替关联查询的时候
• 当查询中使用同一个数据表的时候

13. 使用union 或 union all

除非确实需要通过mysql服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字， MySQL会给临时表加上DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL 关键字，MySQL 仍然会使用临时表存储结果。

当明显不会有重复值时进行union all

• union会把所有数据放到临时表中后再进行去重操作

  对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。

• union all不会再对结果集进行去重操作

  仍然会使用临时表存储结果

14. 优化分页查询

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。

这里的“延迟关联”将大大提升查询效率，它让MySQL 扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。

先快速定位需要获取的id段，然后再关联：

SELECT a.字段 FROM 表l a, (select id from 表1 where 条件 LIMIT 100000, 20 ) b where
a.id=b.id

15. order by、group by、distinct优化

无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。

我们知道一旦使用了DISTINCT 和GROUP BY，那么在查询的执行过程中，通常需要产生临时中间表

可以使用索引来实现排序，这也是最有效的优化办法

只有当

1. 索引的列顺序和ORDER BY子句的顺序完全一致

2. 所有列的排序方向（倒序或正序）都一样时

3. Order by中的字段全部在关联表中的第一张表中

MySQL 能够使用索引来对结果做排序 条件还是比较苛刻的

16.禁止使用order by rand()进行随机排序

比如在用户中随机选取10个用户进行回访

对性能有很大影响

其执行过程是把表中所有符合条件的数据装载到内存中进行排序

推荐在程序中获取一个随机值，然后从数据中获取数据的方式

17. 优化统计结果集的行数

在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用COUNT(*) ，这样写意义清晰，性能也会很好。

参考：面试官：说说count(*)、count(1)、count(列名)有什么区别？

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
• 列名为主键，count(列名)会比count(1)快 。

18. 使用用户自定义变量

用户自定义变量是一个容易被遗忘的MySQL特性，但是如果能够用好，发挥其潜力，在某些场景可以写出非常高效的查询语句。在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能会非常有用。

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。

使用预编译语句进行数据库操作

避免sql注入

第一次传sql，之后只传参数，效率更高，一次解析，多次使用

#hypotenuse 勾股定理 求斜边
mysql> prepare stmt1
-> from 'select sqrt(pow(?,2) + pow(?,2)) as hypotenuse';  

mysql> set @a = 3;
mysql> set @b = 4;
mysql> execute stmt1 using @a, @b;  

mysql> DEALLOCATE prepare stmt1;

19. 使用缓存衍生值或缓存衍生表(汇总表)

缓存衍生值也是有用的。如果需要显示每个用户发了多少消息（像很多论坛做的），可以每次执行一个昂贵的子查询来计算并显示它；也可以在user 表中建一个num_messages列，每当用户发新消息时更新这个值。

有时提升性能最好的方式是在同一张表中保存衍生的冗余数据。然而，有时也需要创建一张完全独立的汇总表或缓存表（特别是为满足检索的需求时）。如果能容许少量的脏数据，这是非常好的方案，但是有时确实没有选择的余地（例如，需要避免复杂、昂贵的实时更新操作）。

我们用术语“缓存表”来表示存储那些可以比较简单地从schema 其他表获取（但是每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据）。

而术语“汇总表”时，则保存的是使用GROUP BY语句聚合数据的表（例如，数据不是逻辑上冗余的）

实时计算统计值是很昂贵的操作，因为要么需要扫描表中的大部分数据，要么查询语句只能在某些特定的索引上才能有效运行，而这类特定索引一般会对UPDATE操作有影响，所以一般不希望创建这样的索引。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引（这会更加高效）。

案例：使用汇总表优化查询

select count(*) from product_comment where product_id = 999;

每天凌晨进行一次维护，统计出截止到前一天，所有商品的评论数的汇总

如果评论是没有删除的情况，那么就可以每天的累加更新，否则就要进行全表更新

下面使用union all实现累加更新

create table product_comment_cnt(product_id INT, cnt INT);

SELECT SUM(cnt) from (
    select cnt from product_comment_cnt where product_id = 999
)
union all
select count(*) from product_comment where product_id = 999 and timestr > DATE(NOW())

由于进行了时间的过滤，所以可以使用覆盖索引来对数据的查询进行优化

当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用。这就需要通过使用“影子表”来实现，“影子表”指的是一张在真实表“背后”创建的表。当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。例如，如果需要重建my_summary,则可以先创建my_summary_new，然后填充好数据，最后和真实表做切换。

20. 把一个大事务拆成小事务

原因1：这样执行起来比较快（binlog写的比较快）。原因2：主从复制的时候，延时会小

21. 避免数据类型的隐式转换

一般出现where从句中，当列上的参数与传入的数据类型不一致的时候，可能发生，而且隐式转换会导致索引失效。

select name, phone from customer where id = '111';

id列定义为整形，但是传入的参数是字符串

22. 禁止使用不含有字段列表的insert语句

insert into t values('a','b','c');

可减少表结构变更带来的影响

23. 使用in 代替 or

比如a = 1 or a = 2，可以使用 a in (1,2)，可以有效的利用索引

再比如复合索引 index(a,b) 就能覆盖到下面这个查询

select * from xxx where a = ? and b=?

而这个查询

select * from xxx where a = ? or b = ?

or：mysql优化器会使用union来重写，不会用到这个复合索引