MySQL · Select * 对性能的影响

发表于 | 更新于 | 分类于 | 阅读次数：

背景

编程规范中，经常会看到一条，不建议使用 select * 语句，然而实际开发中又屡禁不止，有两个问题：

  • 为什么 select * 影响 SQL 性能？
  • 有多大的影响？

为什么 select * 是低效语句？

主要体现在三个方面：

  • select * 会让优化器无法使用覆盖索引优化。
  • 网络开销，这种开销非常明显。
  • 额外的 IO / CPU 开销，因为多取了不必要的列。

有多大的影响？

  • 测试表信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> desc salaries;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| salary    | int(11) | NO   |     | NULL    |       |
| from_date | date    | NO   | PRI | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+
  • 执行速度对比：查询返回字段越多，耗时越多。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from salaries;
2844047 rows in set (1.01 sec)

mysql> select emp_no from salaries;
2844047 rows in set (0.56 sec)

mysql> select salary,to_date from salaries;
2844047 rows in set (0.67 sec)

mysql> select emp_no,salary,to_date from salaries;
2844047 rows in set (0.86 sec)

mysql> select emp_no,salary,from_date,to_date from salaries;
2844047 rows in set (1.00 sec)
  • 覆盖索引：是可以不用读 data，直接使用 index 里面的值就返回结果。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

mysql> explain select last_name from employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298980 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+

mysql> explain select emp_no from employees;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | PRIMARY | 4       | NULL | 298980 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+