这6个SQL查询性能优化方法，让查询效率提高80%!
SQL 查询性能的优化，是面试中的高频知识点，也是必知必会的技能。
SQL 查询性能的好坏，直接影响程序性能和用户体验。特别是一些数据量大、复杂多样的应用场景中，对 SQL 查询性能优化就更加刚需。
本文主要介绍六个常用的 SQL 查询性能优化方法。
1. 禁止使用 select *
《阿里巴巴开发手册》中指出：
【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明
说明。
select * 的问题及影响
在 SQL 语句中，select * 是最常见的写法之一，表示返回所有的字段。
当查询的表中包含大量字段的时候，这种写法既浪费带宽和 I/O 资源，还会造成缓存和 CPU 的过度使用，严重影响 SQL 的查询性能。
如何正确使用 select 语句
正确使用 select 语句的方法是仅选择需要的字段，既能提升查询效率，还能让结果集更易于理解和处理。
例如：对于一张包含 100 个字段的表，如果只需要其中的 10 个字段，那么就应该这样写：
SELECT field1, field2, field3, field4, field5, field6, field7, field8, field9, field10
FROM table_name;这样，我们只会返回需要的 10 个字段，就能极大地减少查询的时间和资源消耗。
2. 用小表驱动大表
小表和大表的区别
在 SQL 查询中，一个表可能与其他多个表进行关联，关联的表之间可能存在大小的差异。
如果我们在关联时让小表驱动大表，就能提高查询性能。这是因为小表的数据量较小，更容易被缓存，而大表的数据量很大，会占用大量的 I/O 资源，导致查询变慢。
小表驱动大表的作用及示例
具体来说，就是我们可以通过将大表作为外部表（right join/left join），从而实现小表驱动大表。这样在查询时就可以优先使用小表的缓存，减少 I/O 开销。
例如：
我们有两张表 A 和 B，其中 A 表只有 100 行，而 B 表有 10000 行。
如果我们使用 A 表作为驱动表，那么我们只需要对 A 表进行 100 次查询，就可以完成整个查询过程，这比使用 B 表作为驱动表要快得多。
下面是一个使用小表驱动大表的示例：
SELECT *
FROM small_table
LEFT JOIN big_table
ON small_table.id = big_table.id;这个查询语句中，small_table 是小表，big_table 是大表，通过 left join 关键字，我们让 small_table 作为驱动表。
这样，在查询时，系统会优先使用 small_table 的缓存，减少 I/O 开销，提高查询效率。
3. join 关联表不宜过多
在 SQL 查询中，join 语句是常用的语句之一，但过多的 join 关联表会极大地影响 SQL 查询的性能。
这是因为：每个 join 语句都需要执行一次关联操作，从而导致了 SQL 查询的复杂度成倍增加，同时也降低了数据库的响应速度。
如何减少 join 的使用
- 可以使用子查询来代替 join 语句，尤其是在需要关联的数据量不大的情况下。
- 对于一些特定的需求，例如需要取出某个表的前 N 条数据，可以直接使用 limit 语句进行操作，而不必使用 join 语句。
- 可以使用数据库中的视图（View）来简化 SQL 查询中的 join 操作，通过将一些常用的 join 操作定义为视图，从而可以方便地在其他查询中使用。
join 关联表过多解决方法
举个例子：
有 A 和 B 两张表 ，需要通过 join 语句将它们关联在一起。
但是，B 表中的数据过于庞大，如果直接使用 join 语句进行关联，就会导致 SQL 查询的性能极差。
这种情况下，我们就可以考虑使用其他方式来减少关联表的数量。譬如，先将 B 表中的数据按照某种条件进行筛选，再将其与 A 表进行关联。这样就能将关联的数据量减少到一个可控范围内，提高了 SQL 查询的性能。
4. 禁止使用左模糊或者全模糊查询
在 SQL 查询中，左模糊和全模糊查询都是非常耗费资源的查询方式，会造成严重的性能问题。
这是因为：在查询过程中，需要对每一条数据进行全文搜索，这样就会导致查询速度非常慢，甚至导致数据库的性能严重下降。
正确使用模糊查询，才能提高查询性能，减少查询开销。
使用模糊查询时，要注意以下几点：
- 尽量避免在模糊查询中使用“%”通配符，尤其是在左侧使用，在可能的情况下，使用具体的字符范围来代替它。
- 尽量避免使用左模糊或者全模糊查询，可以使用右模糊查询（like 'value%'），又或者其他查询方式来代替模糊查询。
- 如果必须使用模糊查询，可以考虑使用全文索引（full-text index）来优化查询性能。
- 优先考虑使用前缀索引来加速查询。
示例：假设我们要查询某个表中以 “abc” 开头的用户名：
SELECT * FROM user WHERE name LIKE '%abc';使用上面的语句，即便表中只有少量以 “abc” 开头的用户名，这样的查询也会导致全表扫描，造成不必要的开销，极大地影响查询性能。
我们可以使用下面这个语句：
SELECT * FROM user WHERE name LIKE 'abc%';利用前缀索引来加速查询，就避免了全表扫描，大大提高了查询性能。
5. 索引访问类型至少达到 range 级别
索引访问类型是指 MySQL 在查询数据时使用的索引方式，通常分为全值匹配、范围查询、索引扫描和全表扫描等多种方式。
其中，索引访问类型不到 range 级别的查询方式，就会对查询性能造成较大的影响。
索引访问类型的问题及影响
当索引访问类型不到 range 级别时，MySQL 在查询数据时需要对索引进行全表扫描或索引扫描，导致查询效率低下，查询速度变慢，严重影响系统的性能。
如何正确使用索引
一些使用索引的建议：
- 在经常查询的列上创建索引。
- 在经常用于排序、分组和联合查询的列上创建索引。
- 限制索引列的数量，避免创建过多的索引。
- 避免在索引列上使用函数或表达式。
索引访问类型不到 range 级别的解决方法
下面是一个索引访问类型不到 range 级别的实例：
SELECT * FROM orders WHERE order_date > '2022-01-01';在这个查询语句中，如果 orders 表的 order_date 字段没有创建索引，MySQL 就会对该字段进行全表扫描，从而导致查询效率低下。
为了避免这种情况，我们可以在 order_date 字段上创建索引，将查询类型从全表扫描变为范围查询，从而提高查询效率。
6. 更优雅的使用联合索引
联合索引是由多个列组成的索引，可以在多个列上进行查询，它同时包含了多个列的索引，多个列组合成一个键来进行索引。
相较于单列索引，联合索引可以提高查询效率和优化数据库性能。
联合索引的好处
联合索引可以减少磁盘 I/O 操作，提高查询效率，减少系统负载。
- 提高查询效率：联合索引可以根据多个列组合进行查询，能够快速定位所需要的记录，减少扫描数据表的时间，提高查询效率。
- 优化数据库性能：联合索引可以避免使用多个单列索引，从而减少索引的数量和空间，缩短查询的响应时间，优化数据库性能。
- 能够满足多个查询需求：由于联合索引包含多个列，因此能够满足多个查询需求，避免创建过多的索引。
联合索引的创建和使用注意事项：
- 列的顺序非常重要，应该将最经常被过滤的列放在索引的前面。
- 不要创建过多的联合索引，只创建必要的索引。
- 要注意索引的大小和内存使用情况，避免出现性能问题。
- 定期监控索引的使用情况，及时调整索引以适应不同的查询需求。
- 在经常用于排序、分组和联合查询的列上创建联合索引。
- 在经常同时查询多个列的情况下，可以使用联合索引替代单个列索引。
如果联合索引使用不当，将导致查询效率降低、占用过多的磁盘空间、更新数据时效率低下等问题。
联合索引的使用示例
我们假设有一个用户表，包含用户 ID、用户名和电子邮件地址三个列，我们想要按照用户名和电子邮件地址进行查询，可以创建以下联合索引：
CREATE INDEX idx_username_email ON users(username, email);接着，再执行以下查询：
SELECT * FROM users WHERE email = 'test@example.com';这个查询虽然可以使用到 idx_username_email 索引，但是它并不会很快，因为 email 列排在了索引的第二个位置，查询时需要先按照用户名进行排序，然后再根据电子邮件地址进行过滤，而这个过程可能需要耗费大量的时间。
正确的做法是将 email 列放在第一个位置：
CREATE INDEX idx_email_username ON users(email, username);这样，查询时就可以直接使用索引来过滤电子邮件地址了，而不需要再按照用户名进行排序，极大地提高了查询性能。
SQL 优化面试题
详见：MySQL 面试题精选大全及答案（最新整理，附备面路线） – Java面试题宝
总结
在本文中，我们总结了 SQL 查询性能优化的一些经验和注意事项，包括禁止使用 select * 、用小表驱动大表、join 关联表不宜过多、禁止使用左模糊或者全模糊查询、索引访问类型至少达到 range 级别、更优雅的使用联合索引等。同时，还列举了一些具体示例，来帮助更好地理解和掌握这些方法。
在实际应用过程中，一定要结合具体情况灵活运用，以满足不同的业务需求和应用场景。
我是爱分享的程序员宝妹儿，谢谢关注 Java面试题宝。
如果觉得不错，请一键三连支持下。
---end---