极客时间-轻松学习，高效学习-极客邦

君莫惜置顶

2019-06-22

SELECT COUNT(*) ＞ SELECT COUNT(1) ＞ SELECT COUNT(具体字段)

之前看到的，好像Mysql对count(*)做了单独的优化

作者回复: 关于COUNT()的效率是一个很好的问题，欢迎探讨：
在MySQL InnoDB存储引擎中，COUNT(*)和COUNT(1)都是对的所有结果进行的COUNT。如果有WHERE子句，则是对所有符合筛选条件的数据行进行统计。如果没有WHERE子句，则是对数据表的数据行数进行统计。
因此COUNT(*)和COUNT(1)本质上没有区别，执行的复杂度都是O(N)，也就是采用全表扫描，进行循环+计数的方式进行统计。
如果是MySQL MyISAM存储引擎，统计数据表的行数只需要O(1)复杂度，这是因为每张MyISAM的数据表都有一个meta信息有存储了row_count值。而一致性由表级锁来保证。而InnoDB支持事务，采用行级锁和MVCC机制，所以无法像MyISAM一样，只维护一个row_count变量。因此就需要采用扫描全表，进行循环+计数的方式来完成统计。
需要注意的是，在实际执行中COUNT(*)和COUNT(1)执行时间可能略有差别，不过你还是可以把这两个在执行效率上看成是相等的。

另外在InnoDB引擎中，如果是采用COUNT(*)和COUNT(1)来统计数据行数，要尽量采用二级索引。
因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。
对于查找具体的行来说，采用主键索引效率更高。而对于COUNT(*)和COUNT(1)这种，不需要查找具体的行，只是统计行数来说，系统会自动采用占用空间更小的二级索引来进行统计。
如果有多个二级索引的时候，会使用key_len小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

优化总结：
1、一般情况下：COUNT(*) = COUNT(1) > COUNT(字段)
所以尽量使用COUNT(*)，当然如果你要统计的是就是某个字段的非空数据行数，那另当别论。毕竟执行效率比较的前提是要结果一样才行。
2、如果要统计COUNT(*)，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升，有时候提升几倍甚至更高都是有可能的。

 2

 76
C先生丶陈置顶

2019-06-22

做一个搬运工，下面是从老师GitHub上找到的建表语句：
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for heros
-- ----------------------------
DROP TABLE IF EXISTS `heros`;
CREATE TABLE `heros` (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `hp_max` float NULL DEFAULT NULL,
  `hp_growth` float NULL DEFAULT NULL,
  `hp_start` float NULL DEFAULT NULL,
  `mp_max` float NULL DEFAULT NULL,
  `mp_growth` float NULL DEFAULT NULL,
  `mp_start` float NULL DEFAULT NULL,
  `attack_max` float NULL DEFAULT NULL,
  `attack_growth` float NULL DEFAULT NULL,
  `attack_start` float NULL DEFAULT NULL,
  `defense_max` float NULL DEFAULT NULL,
  `defense_growth` float NULL DEFAULT NULL,
  `defense_start` float NULL DEFAULT NULL,
  `hp_5s_max` float NULL DEFAULT NULL,
  `hp_5s_growth` float NULL DEFAULT NULL,
  `hp_5s_start` float NULL DEFAULT NULL,
  `mp_5s_max` float NULL DEFAULT NULL,
  `mp_5s_growth` float NULL DEFAULT NULL,
  `mp_5s_start` float NULL DEFAULT NULL,
  `attack_speed_max` float NULL DEFAULT NULL,
  `attack_range` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_main` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_assist` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `birthdate` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

展开

作者回复: Good Share!

 3

 9
Shame

2019-06-27

先交作业 select name,mp_max from heros order by hp_max desc limit 5;
然后就是楼下一个同学问的问题，我也有些疑惑，就是这个
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

对于这个语句，我还有一点疑问：既然HAVING的执行是在SELECT之前的，那么按理说在执行HAVING的时候SELECT中的count(*)应该还没有被计算出来才对啊，为什么在HAVING中就直接使用了num>2这个条件呢？
希望老师百忙之中能抽空帮忙解释一下，谢谢老师

展开

作者回复: 很好的问题，实际上在Step4和Step5之间，还有个聚集函数的计算。
如果加上这个计算过程，完整的顺序是：
1、FROM子句组装数据
2、WHERE子句进行条件筛选
3、GROUP BY分组
4、使用聚集函数进行计算；
5、HAVING筛选分组；
6、计算所有的表达式；
7、SELECT 的字段；
8、ORDER BY排序
9、LIMIT筛选
所以中间有两个过程是需要计算的：聚集函数和表达式。其余是关键字的执行顺序，如文章所示。

 1

 37
ack

2019-06-21

老师好，请问能把建表的sql给出来吗？

作者回复: 可以看下 https://github.com/cystanford/sql_heros_data

 5

 13
Samson

2019-06-24

老师，可以说下SELECT语句执行原理那个视例中HAVING关键字的作用嘛？

作者回复: HAVING一般配合GROUP BY使用，作为筛选分组的条件。作用实际上和WHERE一样，都适用于限定条件。只是WHERE子句用于对查询结果的分组前，通过WHERE来过滤。而HAVING子句用于筛选满足条件的组，用于在分组之后进行过滤。这个我在后面也会讲到。



 10
HeGuang

2019-06-22

-- 注释版本
CREATE TABLE `heros` (
  `id` SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `name` VARCHAR(255) DEFAULT NULL COMMENT '英雄名称',
  `hp_max` FLOAT NULL DEFAULT NULL COMMENT '最大生命',
  `hp_growth` FLOAT NULL DEFAULT NULL COMMENT '生命成长',
  `hp_start` FLOAT NULL DEFAULT NULL COMMENT '初始生命',
  `mp_max` FLOAT NULL DEFAULT NULL COMMENT '最大法力',
  `mp_growth` FLOAT NULL DEFAULT NULL COMMENT '法力成长',
  `mp_start` FLOAT NULL DEFAULT NULL COMMENT '初始法力',
  `attack_max` FLOAT NULL DEFAULT NULL COMMENT '最高物攻',
  `attack_growth` FLOAT NULL DEFAULT NULL COMMENT '物攻成长',
  `attack_start` FLOAT NULL DEFAULT NULL COMMENT '初始物攻',
  `defense_max` FLOAT NULL DEFAULT NULL COMMENT '最大物防',
  `defense_growth` FLOAT NULL DEFAULT NULL COMMENT '物防成长',
  `defense_start` FLOAT NULL DEFAULT NULL COMMENT '初始物防',
  `hp_5s_max` FLOAT NULL DEFAULT NULL COMMENT '最大每5秒回血',
  `hp_5s_growth` FLOAT NULL DEFAULT NULL COMMENT '每5秒回血成长',
  `hp_5s_start` FLOAT NULL DEFAULT NULL COMMENT '初始每5秒回血',
  `mp_5s_max` FLOAT NULL DEFAULT NULL COMMENT '最大每5秒回蓝',
  `mp_5s_growth` FLOAT NULL DEFAULT NULL COMMENT '每5秒回蓝成长',
  `mp_5s_start` FLOAT NULL DEFAULT NULL COMMENT '初始每5秒回蓝',
  `attack_speed_max` FLOAT NULL DEFAULT NULL COMMENT '最大攻速',
  `attack_range` VARCHAR(255) COMMENT '攻击范围',
  `role_main` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '主要定位',
  `role_assist` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '次要定位',
  `birthdate` DATETIME(0) NULL DEFAULT NULL COMMENT '上线时间'
) ENGINE = INNODB DEFAULT CHARSET=UTF8 COMMENT='王者荣耀数据表';

展开

 2

 8
啦啦啦

2019-06-21

select id from table where id=5
执行这条语句时，mysql会判断是否有缓存，有的话直接返回，如果没有则调用引擎接口查询数据，引擎比如说innodb会判断这条数据是否在内存中存在，如果存在直接返回给mysql的server层，如果不存在则会到磁盘里把id=5的这条数据所在的整个数据页都读取到内存中，这个内存就是innodb buffer pool，是一块固定大小的地方，可以由参数innodb_buffer_pool_size设置大小，既然是一块固定大小的地方，那么就要有淘汰机制，mysql是在lru算法的基础上做了优化，简单的来说就是最久未被使用的数据会被淘汰掉



 7
时间是最真的答案

2019-06-24

MySQL
SELECT `name`,mp_max FROM heros ORDER BY hp_max DESC LIMIT 5

作者回复: 正确，同时也考虑到了给查询字段使用反引号



 5
业余草

2019-06-21

create table 还没学吧，我是小白，教一下 create table 或者 create table like。就单拿 select 说，这章内容也不全啊，group，having 等都漏掉了

作者回复: GROUP, HAVING 在后面章节有

 1

 4
Samson

2019-06-24

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

对于这个语句，我还有一点疑问：既然HAVING的执行是在SELECT之前的，那么按理说在执行HAVING的时候SELECT中的count(*)应该还没有被计算出来才对啊，为什么在HAVING中就直接使用了num>2这个条件呢？

展开

 1

 3
hlz-123

2019-06-23

数据库，MySQL8.0
SELECT name as '姓名',mp_max as '最大法力' FROM heros ORDER BY hp_max LIMIT 5;

作者回复: 赞下用8.0的同学



 3
mickey

2019-06-21

1.SELECT就是告诉数据库要选什么字段
2.
  1)MySQL、PostgreSQL、MariaDB和SQLite:
    SELECT name, mp_max FROM heros ORDER BY hp_max DESC limit 5;
  2)SQL Server和Access:
    SELECT TOP 5 name, mp_max FROM heros ORDER BY hp_max DESC
  3)DB2:
    SELECT name, mp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY;
  4)Oracle:
    SELECT name, mp_max FROM (SELECT name, mp_max FROM heros ORDER BY hp_max) as t WHERE ROWNUM <=5

展开



 3
石维康

2019-06-21

作业: SELECT name, mp_max FROM heros ORDER BY hp_max DESC LIMIT 5;
MySQL数据库

作者回复: Good Job

 1

 3
lincan

2019-09-01

老师讲得很棒，但有一处困惑：limit是最后执行的话，执行limit时全表扫描和所有的虚拟表都已生成了，那使用limit为什么还能提高效率呢？

作者回复: 你可以通过 SHOW PROFILE 来查看 SQL 的具体执行成本，如果我们使用LIMIT进行限制，至少可以减少数据传输量，这样在Sending data这项上可以减少大量传输时间，而这一项又在整个SQL执行成本中占比比较高。

 2

 2
峻铭

2019-08-31

从文中顺序6和顺序7描述的order by和limit的关系与 “最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果” 的描述，说明limit是在排序完成后才执行的，那么理论上对数据排序后用的时间和加上limit 1的时间应该是几乎相等的。于是做了个小实验验证下：
增加数据量让时间差异更明显，对5个表做了笛卡尔积
select * from player join team join player_score join team_score join height_grades order by player.height; #用时0.123秒
select * from player join team join player_score join team_score join height_grades order by player.height limit 1; #用时0.027秒
说明limit不是在order by完成后执行的，应该是参与了边排序的过程中边判断是否达到了limit条件，具体过程我也不清楚：）

展开



 2
极客时间

2019-06-26

老师我有一个疑问场景是这样的，我有三张表，一张表存储文章posts，一张表存储文章标签tags，

一篇文章可以有多个标签，一个标签可以被多个文章拥有，文章和标签是多对多的关系，

此时我又增加了一个关系表post_tag,这个表只有两个字段，post_id和tag_id.

我现在有一个需求查询出所有文章，查询出的文章数据中每篇文章都有一个tags属性，这个属性包含所有这篇文章的标签信息，这个查询应该怎么做呢？或者说sql只能做一部分，然后在通过其他脚本语言再处理呢？

我将问题发在了 segmentfaul 链接地址 https://segmentfault.com/q/1010000019472412

展开

作者回复: 下面有同学做了回答，你可以参考下
另外你这个头像和昵称，哈哈哈

 2

 2
马哲富

2019-06-24

讲得非常好，就这一篇文章就值专栏的价格了！另外老师能讲解一下where语句的过滤顺序吗，能理解where语句的执行顺序对sql的优化应该也会有很大的帮助，谢谢老师的解答！



 2
👽

2019-06-21

再有一个建议，能不能在课中嵌套一些常见的面试题，例如：
索引的作用与弊端是什么？



 2
crazypokerk

2019-06-21

SELECT是所有查询关键字最后一个执行的关键字。



 2
月牙天冲

2019-06-21

1.作业：SELECT name,mp_max FROM heros ORDER BY hp_max DECS LIMIT 5；
2.老师能提供下创建表的sql语句么，谢谢。

 3

 2