MySQL 面试题汇总（持续更新中）

时间：2024-01-15 23:17:26

COUNT

COUNT(*) 和 COUNT(1)

根据 MySQL 官方文档的描述：

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

This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.

也就是说，在 InnoDB 中，COUNT(1) 和 COUNT(*) 并没有本质上的区别，在 MyISAM 中，只有在第一行数据不为 NULL 的时候，COUNT(1) 才会出现性能上的优化（在 MyISAM 中，表的行数被单独存储，因此统计行数无需遍历表，而 InnoDB 因为只存储了大概的行数，COUNT 会遍历整个表）

实际上，SELECT COUNT(2)、COUNT(3)、COUNT('OK I am just count, but the result always be the same.')，结果都是一样的。根据 What is the Difference Between COUNT(*), COUNT(1), COUNT(column name), and COUNT(DISTINCT column name)? 一文指出：

So what does the value in the parenthesis of COUNT() mean? It’s the value that the COUNT() function will assign to every row in the table. The function will then count how many times the asterisk (*) or (1) or (-13) has been assigned. Of course, it will be assigned a number of times that’s equal to the number of rows in the table. In other words, COUNT(1) assigns the value from the parentheses (number 1, in this case) to every row in the table, then the same function counts how many times the value in the parenthesis (1, in our case) has been assigned; naturally, this will always be equal to the number of rows in the table. The parentheses can contain any value; the only thing that won’t work will be leaving the parentheses empty.

COUNT(field) 和 COUNT(DISTINCE field)

COUNT(field) 用于统计该列的所有不为 NULL 的数据，与之不同的是，COUNT(*) 则包含了为 NULL 的数据。

COUNT(DISTINCE field) 正如字面意思，用于统计该列的不重复值得数量。

关于 COUNT(field) 有一个有趣的用法：

SELECT COUNT(CASE WHEN order_price > 1000 THEN 1 END)

AS significant_orders

FROM orders;

这里，当 order_price 大于 1000 时，COUNT 里面的字段被赋值为 1，否则为 NULL，最后结果则仅包含了值为 1 的行。

索引

索引的应用场景
  1. 表在业务中查询较多
  2. 更新频率较低的字段（增删改在存在索引的字段上效率受影响）
  3. 常常出现在 where 语句中的字段
  4. 在 join 查询中出现在 on 语句中的两边字段
索引建立的原则

MySQL索引原理及慢查询优化一文指出建立索引的 5 大原则：

  1. 最左前缀匹配原则，非常重要的原则，mysql 会一直向右匹配直到遇到范围查询 (>、<、between、like) 就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的，如果建立(a,b,d,c) 的索引则都可以用到，a,b,d 的顺序可以任意调整。

  2. = 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。

  3. 尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要 join 的字段我们都要求是 0.1 以上，即平均 1 条扫描 10 条记录。

  4. 索引列不能参与计算，保持列“干净”，比如 from_unixtime(create_time) = '2014-05-29' 就不能使用到索引，原因很简单，b+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 create_time = unix_timestamp('2014-05-29')。

  5. 尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。

使用索引也无法加快查询速度的情况？

原因：

  1. 字段区分度低（重复性高）
  2. SQL 写法问题（范围查询，计算查询等）

针对字段区分度低的案例：

举这个例子的目的在于颠覆我们对列的区分度的认知，一般上我们认为区分度越高的列，越容易锁定更少的记录，但在一些特殊的情况下，这种理论是有局限性的。

select

   *

from

   stage_poi sp

where

   sp.accurate_result=1

   and (

      sp.sync_status=0

      or sp.sync_status=2

      or sp.sync_status=4

   );

0.先看看运行多长时间，951 条数据 6.22 秒，真的很慢。

951 rows in set (6.22 sec)

1.先 explain，rows 达到了 361 万，type = ALL 表明是全表扫描。

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

|  1 | SIMPLE      | sp    | ALL  | NULL          | NULL | NULL    | NULL | 3613155 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

2.所有字段都应用查询返回记录数，因为是单表查询 0 已经做过了 951 条。

3.让 explain 的 rows 尽量逼近 951。

看一下 accurate_result = 1 的记录数：

select count(*),accurate_result from stage_poi  group by accurate_result;

+----------+-----------------+

| count(*) | accurate_result |

+----------+-----------------+

|     1023 |              -1 |

|  2114655 |               0 |

|   972815 |               1 |

+----------+-----------------+

我们看到 accurate_result 这个字段的区分度非常低，整个表只有 -1，0，1 三个值，加上索引也无法锁定特别少量的数据。

再看一下 sync_status 字段的情况：

select count(*),sync_status from stage_poi  group by sync_status;

+----------+-------------+

| count(*) | sync_status |

+----------+-------------+

|     3080 |           0 |

|  3085413 |           3 |

+----------+-------------+

同样的区分度也很低，根据理论，也不适合建立索引。

问题分析到这，好像得出了这个表无法优化的结论，两个列的区分度都很低，即便加上索引也只能适应这种情况，很难做普遍性的优化，比如当 sync_status 0、3 分布的很平均，那么锁定记录也是百万级别的。

4.找业务方去沟通，看看使用场景。业务方是这么来使用这个 SQL 语句的，每隔五分钟会扫描符合条件的数据，处理完成后把 sync_status 这个字段变成 1，五分钟符合条件的记录数并不会太多，1000 个左右。了解了业务方的使用场景后，优化这个 SQL 就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。

5.根据建立索引规则，使用如下语句建立索引

alter table stage_poi add index idx_acc_status(accurate_result,sync_status);

6.观察预期结果,发现只需要 200ms，快了 30 多倍。

952 rows in set (0.20 sec)

我们再来回顾一下分析问题的过程，单表查询相对来说比较好优化，大部分时候只需要把 where 条件里面的字段依照规则加上索引就好，如果只是这种“无脑”优化的话，显然一些区分度非常低的列，不应该加索引的列也会被加上索引，这样会对插入、更新性能造成严重的影响，同时也有可能影响其它的查询语句。所以我们第4步调差SQL的使用场景非常关键，我们只有知道这个业务场景，才能更好地辅助我们更好的分析和优化查询语句。

参考：MySQL索引原理及慢查询优化 - 美团技术团队 (meituan.com)

相关文章