复盘Sql | MySQL

vvccc
88 阅读8分钟

简介

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

复盘在项目中写过的Sql语句，使用explain命令查看索引的使用情况。

复盘

评论模块

为方便起见，将使用select *进行查询，关于是否应该使用select *进行查询，后面咱们再聊。

功能：查询评论。

SELECT
	* 
FROM
COMMENT 
WHERE
	STATUS = 0 
	AND entity_type = 1 
	AND entity_id = 228 
ORDER BY
	create_time 
	LIMIT 0,5

order by的作用是根据时间顺序展示和分页，我们应该重点关注where中的三个条件。

  1. status = 0：0表示应该被展示。其他数字可能有不同含义，如：1表示被删除，2表示审核中。
  2. entity_type = 1：被评论实体类型为1。可以给帖子评论，可以给视频评论，可以给评论评论，每个实体由不同的编号代表。
  3. entity_id = 228：被评论实体的id为228。

使用场景：用户点开视频，我们需要返回给用户这个视频的评论。但请注意评论的评论也应该一起返回。

现在我举例说明。视频实体编号为1，评论实体编号为2。
被点开的视频id为228。
第一次查询：entity_type = 1 AND entity_id = 228。此时可以拿到视频的commentList。
遍历commentList拿到comment。
再进行第二次查询entity_type = 2 AND entity_id = comment.getId()。
这样能查全么？

1 a->视频：666
2	b->a：777
3	a->b：888
4	c->a：999
第一行在第一次查询中一定能查到，因为这是对视频的评论。
第二行在第二次查询中一定能查到，也很好理解。
但是第三行能查到么？
第四行能查到么？

先说结论：上面的例子中，第三行和第四行都是在第二次查询中查到的。

首先直接对视频的评论没什么好讨论的，在第一次查询中一定能查出来。直接对评论进行评论也好理解，我们纠结的点在于评论的评论的评论如何处理。

回想下你在看b站的时候，评论的评论的评论（第三级评论）是怎样的？

aaa:说的对
	bbb:其实不对
	aaa回复@bbb：哪里不对
	bbb回复@aaa：就是不对

思考一下就破案了，例子中的3、4行（第三级评论）也是第二次查询中被查出来的，是评论表中还有一个target_id字段，用来解决我们的困惑。第三级评论一个明显的特征就是回复@xxx这个被@的用户id就存储在target_id，controller层处理comment对象时，target_id不为0，就知道这是一个三级评论，再通过用户id查到用户对象，构建评论。

索引：给entity_id字段加索引。

使用show keys from comment;查看comment表中的索引。

explain命令查看select详情如下：

+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------+
|  1 | SIMPLE      | COMMENT | NULL       | ref  | index_entity_id | index_entity_id | 5       | const |    9 |     1.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

补充信息：SQL中EXPLAIN命令详解 - SummerChill - 博客园 (cnblogs.com)

私信模块

会话列表

功能：查询当前用户的私信会话列表，每个会话只返回最近的一条信息。也就是说我们返回的是每个会话中最新的一条信息。

注释：两个用户之间的对话的集合称为会话。

SELECT
	* 
FROM
	message 
WHERE
	id IN (
	SELECT
		max( id ) 
	FROM
		message 
	WHERE
		STATUS != 2 
		AND from_id != 1 
		AND ( from_id = 111 OR to_id = 111 ) 
	GROUP BY
		conversation_id 
	) 
ORDER BY
	id DESC 
	LIMIT 0,
	5;

外层select容易理解，我们重点说内层（IN中）的查询。

  • conversation_id用来标记一个会话，这是一个冗余字段，由from_id和to_id由小到大拼接组成。
  • max(id)是因为我们要的是最新的消息，id越大，消息就越新。
  • status=2表示被删除，所以不能为2。
  • from_id=1表示系统通知，这里是用户之间的私信查询。
  • 当前登录用户的id是111，他发出的（from_id）或者收到的（to_id）我们才关心。

会话数量

功能：统计用户会话数量。

SELECT
	count( m.maxid ) 
FROM
	(
	SELECT
		max( id ) AS maxid 
	FROM
		message 
	WHERE
		STATUS != 2 
		AND from_id != 1 
		AND ( from_id = 111 OR to_id = 111 ) 
	GROUP BY
	conversation_id 
	) AS m;

内层查询与上一个Sql基本无异，就是起了个别名。外层查询用来统计数量。

问题讨论

select *

学习文章：为什么大家都说 SELECT * 效率低？ - 掘金 (juejin.cn)

总结文章要点如下：

  • 查询中禁止使用select *。
  • 使用select *增加预处理器的执行成本。预处理器将*替换为表内所有字段，详见：执行一条 select 语句，期间发生了什么？ | 小林coding (xiaolincoding.com)
  • 使用select *查询出的无用字段的传输会增加网络开销，尤其是varchar、text等大字段。
  • 失去覆盖索引的可能，详见原文1.3小节。
  • 频繁更新的字段不建索引。类似性别、状态的字段不建索引。

索引

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

执行一条 select 语句，期间发生了什么？ | 小林coding (xiaolincoding.com)

私信表的conversation_id、from_id、to_id三个字段都有普通索引，那么私信模块中会话列表的查询使用了哪个索引呢？

使用explain命令，结果如下：

+----+-------------+---------+------------+-------+-------------------------------------------------+---------------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys                                   | key           | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+---------+------------+-------+-------------------------------------------------+---------------+---------+------+------+----------+-----------------------------------------------------+
|  1 | PRIMARY     | message | NULL       | index | NULL                                            | PRIMARY       | 4       | NULL |    5 |   100.00 | Using where; Backward index scan                    |
|  2 | SUBQUERY    | message | NULL       | range | index_from_id,index_to_id,index_conversation_id | index_from_id | 5       | NULL |   60 |    90.00 | Using index condition; Using where; Using temporary |
+----+-------------+---------+------------+-------+-------------------------------------------------+---------------+---------+------+------+----------+-----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

我们重点看子查询也就是第二行，其中possible_keys有三个，分别是我们上面提到的三个普通索引，而key是index_from_id，也就是from_id字段上的普通索引。也就是说MySQL的优化器从三个索引中选择了index_from_id索引来加快我们的查询。

索引失效

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

索引失效有哪些？ | 小林coding (xiaolincoding.com)

我们来验证一下上面的说法，使用drop index index_to_id on message;删除to_id字段上的索引。

再使用explain命令，结果如下：

+----+-------------+---------+------------+-------+-------------------------------------+---------------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys                       | key           | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+---------+------------+-------+-------------------------------------+---------------+---------+------+------+----------+-----------------------------------------------------+
|  1 | PRIMARY     | message | NULL       | index | NULL                                | PRIMARY       | 4       | NULL |    5 |   100.00 | Using where; Backward index scan                    |
|  2 | SUBQUERY    | message | NULL       | range | index_from_id,index_conversation_id | index_from_id | 5       | NULL |   60 |    90.00 | Using index condition; Using where; Using temporary |
+----+-------------+---------+------------+-------+-------------------------------------+---------------+---------+------+------+----------+-----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

看子查询的key依然为index_from_id，也就是走了from_id的索引，这是为什么呢？

我们观察Sql语句，发现前面有个from_id != 1，原来我们搞错，MySQL是根据这个条件走的索引查询，而不是( from_id = 111 OR to_id = 111 ) 这个条件。

为了验证我们的才想，现在我们将from_id != 1这个条件删除，再使用explain命令查询一次，结果如下：

+----+-------------+---------+------------+-------+-------------------------------------+-----------------------+---------+------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type  | possible_keys                       | key                   | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+---------+------------+-------+-------------------------------------+-----------------------+---------+------+------+----------+----------------------------------+
|  1 | PRIMARY     | message | NULL       | index | NULL                                | PRIMARY               | 4       | NULL |    5 |   100.00 | Using where; Backward index scan |
|  2 | SUBQUERY    | message | NULL       | index | index_from_id,index_conversation_id | index_conversation_id | 137     | NULL |  302 |    17.10 | Using where                      |
+----+-------------+---------+------------+-------+-------------------------------------+-----------------------+---------+------+------+----------+----------------------------------+

发现确实不走index_from_id索引了，而走index_conversation_id的原因是GROUP BY conversation_id。

然后我在删除from_id != 1的条件下，使用create index index_to_id on message(to_id);，对to_id重新构建索引，再使用explain命令查询结果如下：

+----+-------------+---------+------------+-------------+-------------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys                                   | key                       | key_len | ref  | rows | filtered | Extra                                                                |
+----+-------------+---------+------------+-------------+-------------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------------------------------+
|  1 | PRIMARY     | message | NULL       | index       | NULL                                            | PRIMARY                   | 4       | NULL |    5 |   100.00 | Using where; Backward index scan                                     |
|  2 | SUBQUERY    | message | NULL       | index_merge | index_from_id,index_conversation_id,index_to_id | index_from_id,index_to_id | 5,5     | NULL |  130 |    90.00 | Using union(index_from_id,index_to_id); Using where; Using temporary |
+----+-------------+---------+------------+-------------+-------------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

可以看到使用了index_from_id和index_to_id两个索引。

事实证明我们的猜想是对的，我做总结如下：

  1. 原版私信模块中会话列表的查询进行索引三选一，使用了index_from_id索引，是因为from_id != 1的条件。
  2. 我们删除了index_to_id索引，不影响因为from_id != 1的条件继续使用index_from_id索引。
  3. 我们删除了from_id != 1的条件，又因为要想在where子句中有or的情况下使用索引，or前和or后都要有索引，而我们已经把index_to_id索引删除了，所以只能因为GROUP BY conversation_id使用index_conversation_id索引。
  4. 然后我们在删除from_id != 1的条件下，重新对to_id构建索引，发现MySQL使用了index_from_id和index_to_id两个索引。

优化器

优化器会帮助我们选择索引，在上面的例子中，优化器为什么要根据from_id != 1的条件选择index_from_id索引，而不是根据( from_id = 111 OR to_id = 111 ) 的条件同时使用index_from_id和index_to_id两个索引。

首先我们要知道explain命令查询后，rows表示MySQL估算查询到所需记录读取的行数，应该越小越好。

根据from_id != 1的条件查询rows=60，而根据( from_id = 111 OR to_id = 111 ) 的条件查询rows=130。MySQL自然会选择rows小的查询方式。造成这样的原因是：表中from_id=1的有311行，from_id!=1的有59行。

那我们多加入一些from_id!=1的数据看看会发生什么。

我们加入了301行from_id!=1的数据，现在是360>311，您猜怎么着？

使用explain查询结果，如下：

+----+-------------+---------+------------+-------------+-------------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys                                   | key                       | key_len | ref  | rows | filtered | Extra                                                                |
+----+-------------+---------+------------+-------------+-------------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------------------------------+
|  1 | PRIMARY     | message | NULL       | index       | NULL                                            | PRIMARY                   | 4       | NULL |    5 |   100.00 | Using where; Backward index scan                                     |
|  2 | SUBQUERY    | message | NULL       | index_merge | index_from_id,index_conversation_id,index_to_id | index_from_id,index_to_id | 5,5     | NULL |  381 |    90.00 | Using union(index_from_id,index_to_id); Using where; Using temporary |
+----+-------------+---------+------------+-------------+-------------------------------------------------+---------------------------+---------+------+------+----------+----------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

可以看到，现在使用了index_from_id和index_to_id索引。

可以看出，MySQL的优化器十分智能，可以根据数据的不同动态选择索引。

avatar
文章
阅读
粉丝