「MySQL高级篇」COUNT聚合函数、模糊查询与索引失效本文整理了本人在之前的学习整理中遗漏的知识点，总共有以下两点需

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第九天，点击查看活动详情

大家好，我是Zhan，一名个人练习时长一年半的大二后台练习生，最近在学MySQL高级篇，现在正在看面经整理遗漏知识点，欢迎各路大佬一起交流讨论

👉本篇速览

在前面对MySQL高级篇的的学习中，我们从存储引擎、索引、锁、SQL语句优化、事务实现、常用工具这些方面学习了MySQL的底层知识，如果有需要了解的可以去看我前面的文章，在大概学完MySQL的知识点后，我便开始通过面经来检验自己学习的成果，以及找到自己知识点不牢固或者说学习过程中遗漏的知识点，大家有觉得可以补充的也可以在评论中一起沟通交流，本文整理了本人在之前的学习整理中遗漏的知识点，总共有以下两点需要补充的知识点：

1️⃣ COUNT聚合函数底层原理
2️⃣ 模糊查询与索引失效

1️⃣ COUNT底层原理

在MySQL基础篇的时候，就有提到在查询整张表的数量的时候，使用SELECT COUNT(* ) 的性能优于其他COUNT()，但是没有讲究竟为什么，我们今天就走入底层，寻找真正的原因：

📣 COUNT聚合函数回顾

在讲解之前，我们先回顾一下COUNT聚合函数的使用，函数的参数不仅可以是字段名，也可以是其他的任意的表达式，比如：0，1……

那么COUNT函数的作用实际上是：统计符合查询条件的记录中，参数不为NULL的记录数

这里提一下COUNT(1)，它代表的是：在查出的记录数中，1这个参数不为NULL的记录有多少条，很明显，就是符合查询条件的数据的总记录数

要比较COUNT函数的执行的效率，那么在此之前就需要了解COUNT函数底层究竟是如何再运作的，也就说要知道COUNT函数的执行过程，方能给出合理的依据，我们依次来看看参数为主键字段、1、* 、字段的时候，它们的执行过程：

🔴 COUNT(主键字段) 执行过程

其实COUNT的执行就是一个遍历的过程，遍历所有的记录，然后判断COUNT函数的参数是否为NULL，如果不为NULL就count++，直到所有记录被读完，退出循环，返回count

InnoDB 是通过B+Tree保存记录，根据索引的类型可以分为聚簇索引和二级索引，聚簇索引的叶子结点放的是行数据，二级索引的叶子结点放的主键值

对于参数为主键字段的情况，根据二级索引是否存在，分为两种：

如果表里面只有主键索引，没有二级索引，那么InnoDB直接遍历主键索引，得到记录，然后判断记录里面的ID是否为空做判断，也就是说，需要查出所有数据然后拿字段，通过实测，我们可以发现确实使用到的是主键索引：
如果表中除了主键索引，还有二级索引，InnoDB遍历的就是二级索引，根据二级索引拿到主键，判断主键是否为空，然后返回，因为相同数量的二级索引记录占用的空间更少，即每次查出的记录的空间不需要是整个行数据，只是主键然后做判断即可，那么它的IO成本就更低，同样的，我们去数据库测试一下，得到：

🟢 COUNT(1) 执行过程

对于COUNT(1)，在回顾中我们提到过，由于1不可能是NULL，那么我们查出的就是符合查询条件的数据行的总记录数

也就是说我们相对于COUNT(主键字段)，不需要去查询主键字段是否为NULL，即不会读取记录中的任何字段，也就是说每从InnoDB中读取一条记录，就count++

当然，如果表中存在二级索引，与参数为主键字段时一样，为了降低IO成本，会选择使用二级索引的B+Tree进行遍历，同样的少一个判断查询出来的主键是否为NULL的步骤

还有一点是，如果有多个二级索引，优化器会使用索引最短(key_len最小)的那个二级索引进行扫描，同样是为了降低IO成本，这个是MySQL的一个优化

因此我们得到的结论是：COUNT(1)执行效率会相较于COUNT(主键字段)高一点点

🟡 COUNT(* ) 执行过程

首先打消一个固定思维hhh，count(* )不是说所有的字段都不为NULL的记录数，不要看到* 这个字符就以为是所有字段值

实际上，COUNT(*) 相当于 COUNT(0)，即使用COUNT(* ) ，MySQL会把*转化为0来处理，也就是说COUNT(*) 的执行效率等于COUNT(1)

🟠 COUNT(字段) 执行过程

其实有了上面的讲述，我们应该能想到，COUNT(字段)的执行效率是最低的，如果不存在该字段的二级索引，它都需要全表扫描，然后找到对应的字段进行判空，因此它的执行效率是最差的

当然，如果该字段存在二级索引，我们是不需要进行全表查询的

💬 总结

首先我们能得到的结论是：

四者的执行效率的比较为：COUNT(*) = COUNT(1) > COUNT(主键字段) > COUNT(字段)
再就是count(1)、 count(* )、 count(主键字段)，都会存在二级索引的优化，可以减少IO成本，因此我们可以在执行相关语句时，建立二级索引
然后尽可能的少去使用COUNT(字段)，就算使用到了，也最好是给这个字段创建一个二级索引。

其实看到这里，可能大家会有疑问，为什么不使用一个值记录总记录数呢？

其实，早在MyISAM存储引擎中就采用了这种方法，记录了总记录数，每张MyISAM的数据表都有一个元数据信息为行数，由表级锁保证它的一致性

这里提到了表级锁以及它的一致性，但是对于InnoDB存储引擎，它支持事务，对于同一时刻的多个事务，它无法维护一个行记录数，我们以下面这个例子说明：

事务1	事务2
begin;
select * from order
	insert into order
select * from order	select * from order

结合我们MVCC的知识，我们可以知道事务1的两次查询的总记录数都会是COUNT，而事务2查询的记录数为COUNT+1

还有一点就是，如果加上了查询的条件，MyISAM与InnoDB便没有了区别，都是全表遍历，因为MyISAM记录的仅仅是没有查询条件时，所有数据的记录数

🎯 COUNT 优化

那我们想要优化COUNT(* )，应该怎么做呢？这里我们提供两种思路：

⚒Explain

如果我们不需要太准确的数据，我们可以使用Explain分析语句：

EXPLAIN SELECT COUNT(*) from order

这个的执行语句是很快的，但是并不准确，因此如果对于准确度要求不高，可以试试：

对于1300w的数据，仅仅需要0.004s就能得到一个大致的数据，虽然不准确，但是很快

🛠 Redis

如果我们需要追求速度与准确度，我们可以把数据缓存在Redis中，然后在插入的时候加，删除的时候。但是很明显，加快了查询的效率，但是降低了插入和删除的效率，因为我们需要额外去维护这个表的数据

2️⃣ 模糊查询与索引失效

记得在讲索引那一篇的时候，我们提到如果仅仅是尾部模糊查询，索引是不会失效的。而如果是头部模糊匹配，索引失效，但是，头部模糊匹配真的会失效吗？

在之前的讲解中，我们测试了：一张表有多个字段，其中name是索引字段、其他为非索引字段、id为自增主键索引，然后分别去测试：

SELECT * FROM user WHERE name like 'xxx';
SELECT * FROM user WHERE name like 'xxx%';
SELECT * FROM user WHERE name like '%xxx';
SELECT * FROM user WHERE name like '%xxx%';

我们测试的情况是：

前两句也就是：正常的根据索引查询、尾部模糊查询索引正常使用
后两句索引失效，进行全表扫描。

但是我们现在换一个场景，还是这四条SQL语句，但是表只有两个字段：一个是name作为索引字段，另一个就是id作为自增主键索引，也就是说少了其他的非索引字段

此时我们再次执行后两句SQL语句，其实会发现用上了二级索引，并且使用了覆盖索引，为什么呢？

由于需要查询的信息，即id和name，在二级索引的叶子结点就能拿到所有的信息

但是，实际上，它虽然使用了二级索引，但是没有使用到二级索引的范围查询，而是遍历了整个二级索引的B+Tree，这个我们能从type = index看出来，如果是范围查询，应该是type = range

那为什么要遍历二级索引的B+Tree，不去遍历聚簇索引的B+Tree呢？

因为，相较于聚簇索引，二级索引叶子结点记录的数据更少，少了事务ID、回滚指针以及所有的剩余列。因此，直接遍历二级索引的B+Tree的IO成本要比遍历聚簇索引的B+Tree的成本要低

也就是说，使用头部模糊匹配不一定会走全表扫描，关键还是要结合实际情况

如果这个数据表加上了非索引字段，执行同样的SQL语句，就会发生索引失效，当然，要注意的是，此处的索引尽管没有失效，但是也没有走范围查询，而是二级索引B+Tree的全表遍历

与此类似的还有一个场景，在讲到联合索引的时候，我们有提到最左前缀法则，但是对于一下这个场景，结合上面这个场景，思考下来，其实发现似乎也有一点问题：

对于一张数据库表，只有三个字段A、B、C，三者构成从A到B到C的联合索引，也就是说，按照之前的理论知识，如果只以C作为查询的条件，是不会走联合索引的，但是对于下面这个语句：

SELECT * FROM table WHERE c = 1

其实我们不难分析出来，由于联合索引的数据足以返回，不需要回表查询，导致最后索引并没有失效，与上面的情况一致~

💡 总结

尽管本文写的是MySQL面经攻读，但是这里并没有列出那些常见的面经，而是列举的我在之前的学习中遗漏的知识点，毕竟其实面经这个东西大把大把的，我能去做的仅仅是把一些我认为不错的知识点拿出来，自己学习的同时和大家分享，后续的文章中或许会有一些我认为重要的面经，但是不一定整理出所有的知识点，只是提炼主要的思路，毕竟篇幅有限，太多枯燥的知识点大家也可能不喜欢看。那我们明天见~

🍁 友链

✒写在最后

都看到这里啦~，给个点赞再走呗~，也欢迎各位大佬指正，在评论区一起交流，共同进步！也欢迎加微信一起交流：Goldfish7710。咱们明天见~

求赞.jpeg