MySQL - SELECT 行构造器表达式

---

前言

最近在查找 MySQL 5.7 文档时，发现一个有趣的表达式——行构造器表达式（Row Constructor Expression），以下简称行构造器。
说行构造器之前我们先看这么个数据表：

CREATE TABLE `student_score` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `subject` varchar(50) NOT NULL COMMENT '科目',
  `score` tinyint NOT NULL DEFAULT '0' COMMENT '分数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_idx_name_subject` (`name`,`subject`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生分数表';

INSERT INTO student_score(name, subject, score)
VALUES('小明', '语文', 68);
INSERT INTO student_score(name, subject, score)
VALUES('小红', '语文', 84);
INSERT INTO student_score(id, name, subject, score)
VALUES('小强', '语文', 77);
INSERT INTO student_score(name, subject, score)
VALUES('小明', '数学', 96);
INSERT INTO student_score(name, subject, score)
VALUES('小红', '数学', 72);
INSERT INTO student_score(name, subject, score)
VALUES('小强', '数学', 60);
INSERT INTO student_score(name, subject, score)
VALUES('小明', '英语', 63);
INSERT INTO student_score(name, subject, score)
VALUES('小红', '英语', 90);
INSERT INTO student_score(name, subject, score)
VALUES('小强', '英语', 81);

现我们有这么个需求：

1. 查询出小明的语文成绩
2. 查询出小红的数学成绩
3. 查询出小强的英语成绩

在知道行构造器之前是这么实现的：

SELECT *
FROM student_score ss 
WHERE name = '小明' AND subject = '语文'
   OR name = '小红' AND subject = '数学'
   OR name = '小强' AND subject = '英语'

使用行构造器之后：

SELECT *
FROM student_score ss 
WHERE (name, subject) IN 
(('小明', '语文'),
 ('小红', '数学'),
 ('小强', '英语'));

对比下是不是简洁很多，特别是当这个查询人数多的时候，可以减少发送给 MySQL 服务端的数据大小。

性能

在 student_score 表中是有一个唯一索引的，首先我们先看下使用 OR 操作符的索引使用情况:

EXPLAIN SELECT *
FROM student_score ss 
WHERE name = '小明' AND subject = '语文'
   OR name = '小红' AND subject = '数学'
   OR name = '小强' AND subject = '英语'

可以看到，是有可能使用到 u_idx_name_subject 这个索引的，接下来看看行构造器：

EXPLAIN SELECT *
FROM student_score ss 
WHERE (name, subject) IN 
(('小明', '语文'),
 ('小红', '数学'),
 ('小强', '英语'));

OR 和行构造器的区别就只有 Extra，Using index condition 肯定是比 Using where 的好，不过这里的行构造器的字段都在唯一索引里，所以这个例子的性能是等价的。

资源

8.2.1.19 Row Constructor Expression Optimization
12.4.1 Operator Precedence
EXPLAIN Output Format
‘Using index condition’ vs ‘Using where’