数据查询优化

数据查询优化

SQL 查找是否"存在", 别再 count 了!

根据某一条件从数据库表中查询 『有』与『没有』, 只有两种状态, 那为什么在写 SQL 的时候, 还要 SELECT count(*) 呢? 无论是刚入道的程序员新星, 还是精湛沙场多年的程序员老白, 都是一如既往的 count

目前多数人的写法

多次 REVIEW 代码时, 发现如现现象: 业务代码中, 需要根据一个或多个条件, 查询是否存在记录, 不关心有多少条记录。普遍的 SQL 及代码写法如下

SQL 写法:

SELECT count(*) FROM table WHERE a = 1 AND b = 2  

Java 写法:

int nums = xxDao.countXxxxByXxx(params);  
if ( nums > 0 ) {  
  //当存在时, 执行这里的代码  
} else {  
  //当不存在时, 执行这里的代码  
}  

是不是感觉很 OK, 没有什么问题

优化方案

推荐写法如下:

SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1  

Java 写法:

Integer exist = xxDao.existXxxxByXxx(params);  
if ( exist != NULL ) {  
  //当存在时, 执行这里的代码  
} else {  
  //当不存在时, 执行这里的代码  
}

SQL 不再使用 count, 而是改用 LIMIT 1, 让数据库查询时遇到一条就返回, 不要再继续查找还有多少条了业务代码中直接判断是否非空即可

总结

根据查询条件查出来的条数越多, 性能提升的越明显, 在某些情况下, 还可以减少联合索引的创建。

你是一直认为 count(1) 比 count(*) 效率高么?

MySQL count(1) 真的比 count(*) 快么? 反正同事们都是这么说的, 我也姑且觉得对吧, 那么没有自己研究一下究竟? 如果我告诉你他们一样, 你信么?

有 Where 条件的 count, 会根据扫码结果 count 一下所有的行数, 其性能更依赖于你的 Where 条件, 所以文章我们仅针对没有 Where 的情况进行说明。

MyISAM 引擎会把一个表的总行数记录了下来, 所以在执行 count(*) 的时候会直接返回数量, 执行效率很高。在 MySQL 5.5 以后默认引擎切换为 InnoDB, InnoDB 因为增加了版本控制 (MVCC) 的原因, 同时有多个事务访问数据并且有更新操作的时候, 每个事务需要维护自己的可见性, 那么每个事务查询到的行数也是不同的, 所以不能缓存具体的行数, 他每次都需要 count 一下所有的行数。那么 count(1) 和 count(*) 有区别么?

InnoDB handles SELECT count(*) and SELECT COUNT(1) operations in the same way. There is no performance difference. 这是官网的解释, 直接点击阅读原文查看官文, 所以两种实现其实一样, 那么具体为什么一样呢?

探究这个问题首先我们需要理解 count 的含义, 如下是官网给出的定义

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

大致的解释是返回 SELECT 语句检索的行中 expr 的非 NULL 值的计数, 到这里我们就明白了, 首先它是一个聚合函数, 然后对 SELECT 的结果集进行计数, 但是需要参数不为 NULL。那么我们继续阅读官网的内容:

count(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

大致的内容是说, count(*) 不同, 他不关心这个返回值是否为空都会计算他的 count, 因为 count(1) 中的 1 是恒真表达式, 那么 count(*) 还是 count(1) 都是对所有的结果集进行 count, 所以他们本质上没有什么区别。

当然这个地方 InnoDB 本身也做了一些优化, 它会使用最小的二级索引来进行 count 的查询优化。如果没有二级索引才会选择聚簇索引, 这样的设计单从 IO 的角度就节省了很多开销。

到这里我们明白了 count(*) 和 count(1) 本质上面其实是一样的, 那么 count(column) 又是怎么回事呢?

count(column) 也是会遍历整张表, 但是不同的是它会拿到 column 的值以后判断是否为空, 然后再进行累加, 那么如果针对主键需要解析内容, 如果是二级所以需要再次根据主键获取内容, 又是一次 IO 操作, 所以 count(column) 的性能肯定不如前两者喽, 如果按照效率比较的话: count(*)=count(1)>count(primary key)>count(column)

既然 count(*) 在查询上依赖于所有的数据集, 是不是我们在设计上也需要尽量的规避全量 count 呢? 通常情况我们针对可预见的 count 查询会做适当的缓存, 可以是 Redis, 也可以是独立的 MySQL count 表, 当然无论是哪种方式我们都需要考虑一致性的问题。

到这里文章就结束了, 你对 count() 有了重新的认识么? 文中提到了一些关键字: 聚簇索引、InnoDB、MyISAM、MVCC 不是本文的重点, 有兴趣可以持续关注订阅号或置顶, 后面会针对这些关键字逐一展开说明。

性能对比: Count(字段)、Count(主键)、Count(1)、Count(*)

以下讨论是基于 InnoDB 引擎。

至于分析性能差别的时候, 可以记住以下几个原则:

1. server 层要什么就给什么;
2. InnoDB 只给必要的值
3. 现在的优化器只优化了 count(*) 的语义为"取行数", 其它"显而易见"的优化并没有做。接下来, 我们一个个来进行分析。

对于 count(主键 id) 来说, InnoDB 引擎会遍历整张表, 把每一行的 id 值都取出来, 返回给 server 层。server 层拿到 id 后, 判断是不可能为空的, 就按行累加。

对于 count(1) 来说, InnoDB 引擎遍历整张表, 但不取值。server 层对于返回的每一行, 放一个数字"1"进去, 判断是不可能为空的, 按行累加。

单看这两个用法的差别的话, 你就能对比出来, count(1) 执行的要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行, 以及拷贝字段值的操作。

对于 count(字段)来说:

• 如果这个"字段"是定义为 not null 的话, 一行行地从记录里面读出这个字段, 判断不能为 null, 按行累加;
• 如果这个"字段"定义允许为 null, 那么执行的时候, 判断到有可能是 null, 还要把值取出来再判断一下, 不是 null 才累加。也就是前面的第一条原则, server 层要什么字段, InnoDB 就返回什么字段。

但是 count(*) 是例外, 并不会把全部字段取出来, 而是专门做了优化, 不取值。count(*) 肯定不是 null, 按行累加。

看到这里, 你一定会说, 优化器就不能自己判断一下吗, 主键 id 肯定非空啊, 为什么不能按照 count(*) 来处理, 多么简单的优化啊。

当然, mysql 专门针对这个语句进行优化, 也不是不可以。但是这种需要专门优化的情况太多了, 而且 mysql 已经优化过 count(*) 了, 直接使用这种用法就可以了。

所以结论是: 按照效率排序的话, count(字段) < count(主键 id) < count(1) ≈ count(*)。所以建议尽量使用 count(*)。

MySQL 多表关联查询和多次单表查询, 哪个效率高?

在数据量不大的情况下多表连接查询和多次单表查询的效率差不多。如果数据量足够大, 那肯定是多次单表查询的效率更高。

有的一些公司里面, 都会禁用多表连接查询, 原因就是一旦数据量足够大的时候多表连接查询效率会很慢, 而且不利于分库分表的查询优化。

这种仅限于大表关联查询, 如果小表还是建议 join 查询。

例:

SELECT p.id, sn.name AS nodeName, o.EndTs AS expiryTime, o.PaymentOrder AS consumptionPattern, cus.cusname AS cusName 
FROM cloudlink_port p 
INNER JOIN cloudlink_servernode sn ON sn.id=p.server_node_id AND sn.deleted =0 
INNER JOIN res.res_cusinfcontract o ON o.ContractNo=p.order_no
INNER JOIN res.res_cusinf cus ON cus.id=p.customer_id
WHERE p.order_no='L2021082459016';

可以分解成下面这些查询来代替:

SELECT p.id FROM cloudlink_port p LIMIT 1;
SELECT sn.name FROM cloudlink_servernode sn WHERE sn.deleted =0 LIMIT 1;
SELECT o.EndTs, o.PaymentOrder FROM res.res_cusinfcontract o LIMIT 1;
SELECT cus.cusname FROM res.res_cusinf cus LIMIT 1;

需要注意的是, 如果查询只有 1 条的情况下, 加上 limit 1 效率会更高,

大致一看, 也没什么大差别, 原本一条查询, 这里却变成了多条查询, 返回结果又是一模一样。到底为什么要这样做? 这样做的优势是啥? 带着问题往下看。

用分解关联查询的方式查询具有以下优势:

• 多次单表查询, 让缓存的效率更高; 许多应用程序可以方便地缓存单表查询对应的结果对象。对 MYSQL 的查询缓存来说, 如果关联中的某个表发生了变化, 那么就无法使用查询缓存了, 而拆分后, 如果某个表很少改变, 那么基于该表的查询就可以重复利用查询缓存结果了。
• 将查询分解后, 执行单个查询可以减少锁的竟争。
• 在应用层做关联, 可以更容易对数据库进行拆分, 更容易做到高性能和可扩展。很多高性能的应用都会对关联查询进行分解。
• 查询效率也可能会有所提升; 这个例子中, 使用 IN() 代替关联査询, 可以让 MYSQL 按照 ID 顺序进行査询, 这可能比随机的关联要更高效。
• 可以减少冗余记录的查询; 在应用层做关联査询, 意味着对于某条记录应用只需要查询一次, 而在数据库中做关联查询, 则可能需要重复地访问一部分数据。从这点看, 这样的重构还可能会减少网络和内存的消耗。
• 这样做相当于在应用中实现了哈希关联, 而不是使用 MYSQL 的嵌套循环关联。某些场景哈希关联的效率要高很多
• 单表查询有利于后期数据量大了分库分表, 如果联合查询的话, 一旦分库, 原来的 sql 都需要改动。
• 一些大公司明确规定禁用 join, 因为数据量大的时候查询会很慢, 所以在数据量不大的情况下, 两种方式的查询都没什么明显的差别, 使用多表连接查询更方便。但是如果在数据量达到几十万、几百万甚至上亿的数据, 或者在一些高并发、高性能的应用中, 一般建议使用单表查询。

我被骗好久了! count(*) 性能最差?

当我们对一张数据表中的记录进行统计的时候, 习惯都会使用 count 函数来统计, 但是 count 函数传入的参数有很多种, 比如 count(1)、count(*)、count(字段) 等。

到底哪种效率是最好的呢? 是不是 count(*) 效率最差?

我曾经以为 count(*) 是效率最差的, 因为认知上 selete * from t 会读取所有表中的字段, 所以凡事带有 * 字符的就觉得会读取表中所有的字段, 当时网上有很多博客也这么说。

但是, 当我深入 count 函数的原理后, 被啪啪啪的打脸了!

不多说, 发车!

哪种 count 性能最好?

我先直接说结论:

要弄明白这个, 我们得要深入 count 的原理, 以下内容基于常用的 innodb 存储引擎来说明。

count() 是什么?

count() 是一个聚合函数, 函数的参数不仅可以是字段名, 也可以是其他任意表达式, 该函数作用是 统计符合查询条件的记录中, 函数指定的参数不为 NULL 的记录有多少个。

假设 count() 函数的参数是字段名, 如下:

select count(name) from t_order;

这条语句是统计「 t_order 表中, name 字段不为 NULL 的记录」有多少个。也就是说, 如果某一条记录中的 name 字段的值为 NULL, 则就不会被统计进去。

再来假设 count() 函数的参数是数字 1 这个表达式, 如下:

select count(1) from t_order;

这条语句是统计「 t_order 表中, 1 这个表达式不为 NULL 的记录」有多少个。

1 这个表达式就是单纯数字, 它永远都不是 NULL, 所以上面这条语句, 其实是在统计 t_order 表中有多少个记录。

count(主键字段) 执行过程是怎样的?

在通过 count 函数统计有多少个记录时, MySQL 的 server 层会维护一个名叫 count 的变量。

server 层会循环向 InnoDB 读取一条记录, 如果 count 函数指定的参数不为 NULL, 那么就会将变量 count 加 1, 直到符合查询的全部记录被读完, 就退出循环。最后将 count 变量的值发送给客户端。

InnoDB 是通过 B+ 树来保持记录的, 根据索引的类型又分为聚簇索引和二级索引, 它们区别在于, 聚簇索引的叶子节点存放的是实际数据, 而二级索引的叶子节点存放的是主键值, 而不是实际数据。

用下面这条语句作为例子:

//id 为主键值
select count(id) from t_order;

如果表里只有主键索引, 没有二级索引时, 那么, InnoDB 循环遍历聚簇索引, 将读取到的记录返回给 server 层, 然后读取记录中的 id 值, 就会 id 值判断是否为 NULL, 如果不为 NULL, 就将 count 变量加 1。

![img3][img3]

但是, 如果表里有二级索引时, InnoDB 循环遍历的对象就不是聚簇索引, 而是二级索引。

![img4][img4]

这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间, 所以二级索引树比聚簇索引树小, 这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小, 因此「优化器」优先选择的是二级索引。

count(1) 执行过程是怎样的?

用下面这条语句作为例子:

select count(1) from t_order;

如果表里只有主键索引, 没有二级索引时。

![img5][img5]

那么, InnoDB 循环遍历聚簇索引(主键索引), 将读取到的记录返回给 server 层, 但是不会读取记录中的任何字段的值, 因为 count 函数的参数是 1, 不是字段, 所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL, 因此 server 层每从 InnoDB 读取到一条记录, 就将 count 变量加 1。

可以看到, count(1) 相比 count(主键字段) 少一个步骤, 就是不需要读取记录中的字段值, 所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。

但是, 如果表里有二级索引时, InnoDB 循环遍历的对象就二级索引了。

![img6][img6]

count(*) 执行过程是怎样的?

看到 * 这个字符的时候, 是不是大家觉得是读取记录中的所有字段值?

对于 selete * 这条语句来说是这个意思, 但是在 count(*) 中并不是这个意思。

count(*) 其实等于 count(0) , 也就是说, 当你使用 count(*) 时, MySQL 会将 * 参数转化为参数 0 来处理。

![img7][img7]

所以, count(*) 执行过程跟 count(1) 执行过程基本一样的 , 性能没有什么差异。

在 MySQL 5.7 的官方手册中有这么一句话:

InnoDB handles SELECT count(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

翻译: InnoDB 以相同的方式处理 SELECT count(*)和 SELECT COUNT(1)操作, 没有性能差异。

而且 MySQL 会对 count(*) 和 count(1) 有个优化, 如果有多个二级索引的时候, 优化器会使用 key_len 最小的二级索引进行扫描。

只有当没有二级索引的时候, 才会采用主键索引来进行统计。

count(字段) 执行过程是怎样的?

count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。

用下面这条语句作为例子:

//name 不是索引, 普通字段
select count(name) from t_order;

对于这个查询来说, 会采用全表扫描的方式来计数, 所以它的执行效率是比较差的。

![img8][img8]

小结

count(1)、 count(*)、 count(主键字段)在执行的时候, 如果表里存在二级索引, 优化器就会选择二级索引进行扫描。

所以, 如果要执行 count(1)、 count(*)、 count(主键字段) 时, 尽量在数据表上建立二级索引, 这样优化器会自动采用 key_len 最小的二级索引进行扫描, 相比于扫描主键索引效率会高一些。

再来, 就是不要使用 count(字段) 来统计记录个数, 因为它的效率是最差的, 会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数, 建议给这个字段建立一个二级索引。

为什么要通过遍历的方式来计数?

你可以会好奇, 为什么 count 函数需要通过遍历的方式来统计记录个数?

我前面将的案例都是基于 Innodb 存储引擎来说明的, 但是在 MyISAM 存储引擎里, 执行 count 函数的方式是不一样的, 通常在没有任何查询条件下的 count(*), MyISAM 的查询速度要明显快于 InnoDB。

使用 MyISAM 引擎时, 执行 count 函数只需要 O(1 ) 复杂度, 这是因为每张 MyISAM 的数据表都有一个 meta 信息有存储了 row_count 值, 由表级锁保证一致性, 所以直接读取 row_count 值就是 count 函数的执行结果。

而 InnoDB 存储引擎是支持事务的, 同一个时刻的多个查询, 由于多版本并发控制(MVCC)的原因, InnoDB 表"应该返回多少行"也是不确定的, 所以无法像 MyISAM 一样, 只维护一个 row_count 变量。

举个例子, 假设表 t_order 有 100 条记录, 现在有两个会话并行以下语句:

![img9][img9]

在会话 A 和会话 B 的最后一个时刻, 同时查表 t_order 的记录总个数, 可以发现, 显示的结果是不一样的。所以, 在使用 InnoDB 存储引擎时, 就需要扫描表来统计具体的记录。

而当带上 where 条件语句之后, MyISAM 跟 InnoDB 就没有区别了, 它们都需要扫描表来进行记录个数的统计。

如何优化 count(*)?

如果对一张大表经常用 count(*) 来做统计, 其实是很不好的。

比如下面我这个案例, 表 t_order 共有 1200+ 万条记录, 我也创建了二级索引, 但是执行一次 select count(*) from t_order 要花费差不多 5 秒!

![img10][img10]

面对大表的记录统计, 我们有没有什么其他更好的办法呢?

• 第一种, 近似值

如果你的业务对于统计个数不需要很精确, 比如搜索引擎在搜索关键词的时候, 给出的搜索结果条数是一个大概值。

![img11][img11]

这时, 我们就可以使用 show table status 或者 explain 命令来表进行估算。

执行 explain 命令效率是很高的, 因为它并不会真正的去查询, 下图中的 rows 字段值就是 explain 命令对表 t_order 记录的估算值。

![img12][img12]

• 第二种, 额外表保存计数值

如果是想精确的获取表的记录总数, 我们可以将这个计数值保存到单独的一张计数表中。

当我们在数据表插入一条记录的同时, 将计数表中的计数字段 +1。也就是说, 在新增和删除操作时, 我们需要额外维护这个计数表。