常见查询优化自学笔记

一、数据库查询优化基础

（一）理解查询执行计划

什么是查询执行计划

查询执行计划是数据库查询优化器为执行查询语句所生成的一种策略，它描述了数据库如何从存储中获取数据、如何对数据进行操作以满足查询要求。通过查看执行计划，我们可以了解数据库实际执行查询的步骤，从而发现可能存在的性能瓶颈。

如何查看执行计划

不同的数据库有不同的查看执行计划的方式。例如，在 MySQL 中，可以使用EXPLAIN关键字，如EXPLAIN SELECT * FROM your_table;。在 Oracle 中，使用EXPLAIN PLAN FOR语句，然后通过SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);查看执行计划。

（二）索引的作用与使用

索引的概念

索引是一种数据结构，它可以帮助数据库快速定位到满足查询条件的数据行。类似于书籍的目录，通过索引，数据库可以避免全表扫描，大大提高查询效率。

索引的类型

常见的索引类型有 B - Tree 索引、哈希索引等。B - Tree 索引适用于范围查询和排序操作，而哈希索引则在等值查询时表现出色。例如，在一个用户表中，对user_id字段建立 B - Tree 索引，当查询SELECT * FROM users WHERE user_id = 123;时，数据库可以快速定位到user_id为 123 的记录。

何时创建索引

频繁用于WHERE子句中的字段：如SELECT * FROM orders WHERE order_date > '2023 - 01 - 01';，对order_date字段建立索引可以提高查询效率。

连接条件中的字段：在多表连接查询时，如SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;，对customer_id字段建立索引可以加速连接操作。

二、SQL 语句优化

（一）避免全表扫描

合理使用索引

确保查询条件中的字段上有合适的索引，避免在索引字段上使用函数或表达式。例如，SELECT * FROM users WHERE UPPER(username) = 'ADMIN';这样的查询会导致索引失效，应改为SELECT * FROM users WHERE username = 'admin';

限定查询范围

在WHERE子句中尽量明确地限定查询条件，避免使用通配符开头的LIKE操作。例如，SELECT * FROM products WHERE product_name LIKE 'abc%';比SELECT * FROM products WHERE product_name LIKE '%abc';更高效，因为前者可以利用索引。

（二）优化多表连接

连接类型的选择

常见的连接类型有内连接（INNER JOIN）、外连接（LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN）。内连接用于返回两个表中满足连接条件的所有行，性能相对较高。外连接则用于返回一侧表中的所有行以及另一侧表中满足连接条件的行。在选择连接类型时，要根据业务需求来确定。例如，如果只需要获取同时存在于两个表中的数据，应使用内连接。

连接顺序的优化

一般来说，将数据量小的表放在连接条件的左侧。例如，在SELECT * FROM small_table JOIN large_table ON small_table.id = large_table.id;中，将小表small_table放在左侧，数据库在处理连接时可以减少中间结果集的大小，提高查询效率。

（三）子查询与 JOIN 的选择

子查询的问题

子查询在某些情况下会导致性能问题，因为它通常会先执行内部查询，然后再将结果用于外部查询。例如，SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders WHERE order_amount > 100);这种嵌套子查询可能会多次扫描表，导致性能下降。

使用 JOIN 替代子查询

上述子查询可以改写为SELECT users.* FROM users JOIN orders ON users.user_id = orders.user_id WHERE orders.order_amount > 100;，JOIN 操作通常可以更有效地利用索引，提高查询性能。

三、数据库配置优化

（一）内存分配优化

缓冲区大小调整

数据库的缓冲区用于缓存数据和查询结果。适当增大缓冲区大小可以减少磁盘 I/O 操作。例如，在 MySQL 中，可以通过修改my.cnf文件中的innodb_buffer_pool_size参数来调整 InnoDB 存储引擎的缓冲区大小。根据服务器的内存情况和业务需求，合理分配缓冲区大小，以提高数据读取和写入的速度。

查询缓存设置

查询缓存用于缓存查询语句及其结果。如果相同的查询再次执行，数据库可以直接从缓存中获取结果，而无需重新执行查询。在 MySQL 中，可以通过query_cache_type和query_cache_size参数来配置查询缓存。然而，需要注意的是，查询缓存对于数据频繁更新的数据库可能效果不佳，因为每次数据更新都可能导致缓存失效。