• 君莫惜 置顶
    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
我们在线,来聊聊吧