为什么SELECT * 会导致查询效率低？

无论是在工作还是面试中，关于SQL中不要用“SELECT *，都是大家听不好的问题。虽然听不好，但普遍理解还是很浅的，没有多少人去追根究底，去探究原理。

废话不多说，本文带你深入了解"SELECT * "低效的原因和场景。

一、效率低的原因

先看最新的阿里java开发手册(泰山版) MySQL 部分描述：

4 - 1. 【强制】不要在表查询中使用 * 作为查询的字段列表，必须明确说明哪些字段。

说明：

• 增加查询分析器解析成本。

• 增减字段容易与 resultMap 配置不一致。

• 添加网络的无用字段 特别是消费 text 类型字段。

开发手册中提到了几个原因，让我们深入看看:

1. 不必要的列将增加数据传输时间和网络费用

1. 用“SELECT * 在 SQL 句子复杂，硬分析多，会对数据库造成沉重负担。

2. 增加网络费用；* 有时会误带上如log、IconMD5.大文本字段、数据传输等无用size若DB与应用程序不在同一台机器上的费用非常明显

3. 即使 mysql 服务器和客户端是在同一台机器上使用的协议还是 tcp，通信也需要额外的时间。

2. 对于无用的大字段，如 varchar、blob、text，会增加 io 操作

确切地说，长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作。（MySQL InnoDB）

3. 失去MySQL优化覆盖索引策略的可能性

SELECT * 基于MySQL优化器的覆盖索引策略是行业推荐的查询优化方法，速度极快，效率极高。

例如，有一个表t(a,b,c,d,e,f)，其中，a为主键，b列有索引。

所以磁盘上有两棵树 B 树，即聚集索引和辅助索引(包括单列索引和联合索引)，分别保存(a,b,c,d,e,f)和(a,b)，在查询条件中where如果用户只需要a列和b列的数据，就可以通过辅助索引直接知道用户查询的数据。

若用户使用select *，如果获得不必要的数据，首先通过辅助索引过滤数据，然后通过收集索引获得所有列，多一次b 树木查询，速度必然会慢很多。

图片取自博客我去，为什么最左前缀原则无效？

由于辅助索引的数据远低于聚合索引，在许多情况下，通过辅助索引覆盖索引（通过索引获得用户需要的所有列），不需要阅读磁盘，直接从内部访问，而聚合索引很可能在磁盘（外存）中（取决于buffer pool在这种情况下，一种是内存读，另一种是磁盘读，速度差异很大，几乎是数量级的差异。

二、索引知识延伸

上面提到了辅助索引MySQL中辅助索引包括单列索引、联合索引（多列联合索引），单列索引将不再重复。以下是联合索引的作用

联合索引 (a,b,c)

联合索引 (a,b,c) 实际建立了 (a)、(a,b)、(a,b,c) 三个索引

一级目录、二级目录、三级目录，如组合索引index(a,b,c)，相当于a是一级目录，b是一级目录下的二级目录，c是二级目录下的三级目录。要使用某一目录，除一级目录外，必须先使用其上级目录。

如下：

联合索引的优点

1） 减少开销

建立联合索引 (a,b,c) ，实际相当于建造 (a)、(a,b)、(a,b,c) 三个索引。每个索引都会增加写作和磁盘空间的成本。对于大量数据表，使用联合索引将大大降低成本！

2）覆盖索引

对联合索引 (a,b,c)，如果有如下 sql 的，

SELECTa,b,cfromtablewherea='xx'andb='xx'; 

那么 MySQL 数据可以通过遍历索引直接获取，而无需返回表格，这减少了很多随机性 io 操作。减少 io 特别是随机操作 io 其实是 DBA 主要的优化策略。因此，在实际应用中，覆盖索引是提高性能的主要优化手段之一。

3）效率高

通过联合索引筛选的索引列越多，数据就越少。 1000W 数据表如下SQL:

selectcol1,col2,col3fromtablewherecol1=1andcol2=2andcol3=3; 

假设：假设可以筛选出每个条件 10% 的数据。

• A. 如果只有单列索引，则可以通过该索引进行筛选 1000W10%=100w 条数据，然后回表从 100w 在条数据中找到符合 col2=2 and col3= 3 然后对数据进行排序和分页，以此类推(递归)；

• B. 如果是（col1,col2,col3)通过三列索引筛选联合索引 1000w10% 10% *10%=1w，可想而知，提高效率！

索引越多越好？

答案自然是否定的

• 数据量小的表不需要建立索引，这将增加额外的索引费用

• 不经常引用的列不应该建立索引，因为它用，即使建立了索引，也没有多大意义

• 不要建立经常更新的列索引，因为它肯定会影响插入或更新的效率

• 重复数据并平均分布字段，因此他建立索引的效果不大（例如，性别字段，只有男性和女性，不适合建立索引）

• 数据变更需要维护索引，这意味着索引越多，维护成本就越高。

• 更多的索引也需要更多的存储空间