SQL 进阶 索引优化

1、选择合适的索引列

选择那些在查询中作为过滤条件、排序条件或是聚合函数参数的列进行索引。选择性高的列（即列中唯一值的比例较高）更适合索引，因为它们提供更好的过滤效果。如经常用于WHERE或ORDER BY子句的列是很好的索引候选。

2、使用复合索引

SQL 中，使用复合索引是一种提高查询性能的常用技术，特别是当查询条件涉及多个列时。复合索引（也称为多列索引）可以让数据库更有效地定位和检索数据。复合索引是在多个列上创建的索引，数据库可以利用这些索引在查询多个列时提供性能优势。在创建复合索引时，列的顺序非常关键，因为数据库会根据索引中列的顺序来优化数据检索。

若有一个名为 Orders 的表，其中包含以下列：CustomerID, OrderDate, 和 TotalAmount。如经常需要根据 CustomerID 和 OrderDate 来查询订单，那么在这两个列上创建一个复合索引将是有益的。

1）SQL Server

在 SQL Server 中，可以使用以下 SQL 语句来创建一个复合索引：

CREATE INDEX idx_customer_date ON Orders (CustomerID, OrderDate);

这条语句创建了一个名为 idx_customer_date 的索引，首先按 CustomerID 排序，然后是 OrderDate。

2）MySQL

在 MySQL 中，创建复合索引的语句与 SQL Server 类似：

CREATE INDEX idx_customer_date ON Orders (CustomerID, OrderDate);

3）PostgreSQL

PostgreSQL 也使用类似的语法：

CREATE INDEX idx_customer_date ON Orders (CustomerID, OrderDate);

4）Oracle

在Oracle中创建复合索引的基本语法是：

CREATE INDEX index_name ON table_name (column1, column2, ..., columnN);

3、考虑索引的选择性

选择性是指索引中不同值的比例。高选择性的索引（即有很多唯一值的索引）通常效果更好。选择性是指索引中不同值的比例，计算公式为不重复值的数量除以表中总行数。选择性的范围从 0 到 1，接近 1 的选择性表示索引的唯一性很高，每个索引值都非常独特，这通常可以提供最优的查询性能。

高选择性（接近 1）的索引对于查询性能来说是最理想的，因为它能够快速缩小搜索范围。

低选择性（接近 0）的索引意味着很多行共享相同的索引值，这种情况下，索引的帮助不大，甚至可能降低性能。

4、使用覆盖索引

覆盖索引是一种高效的索引优化策略，它可以显著提升查询性能。覆盖索引指的是一个索引包含了查询中需要的所有数据列，因此查询可以直接在索引上完成，而无需回表查询原始数据行。这样做可以减少磁盘I/O操作，提高查询速度。

使用覆盖索引的关键是确保索引中包含了查询中用到的所有列。这样做可以极大地优化读操作，特别是在数据表很大的情况下。

在实际应用中，需要根据具体的查询需求和数据表结构来决定创建哪些覆盖索引。过多的索引虽然可以加快查询速度，但也会增加维护成本和影响写操作的性能，因此需要权衡利弊。

5、定期维护索引

随着数据的增加和变更，索引会逐渐碎片化，定期重建或重新组织索引可以保持索引的性能。定期维护索引可以帮助保持数据库性能，防止索引因碎片化而退化。

1）重建索引

定期重建索引以减少碎片化。

2）监控索引使用情况

监控索引的使用情况，删除那些很少或从不使用的索引。

3）更新统计信息

保证优化器有最新的数据分布信息，这可以通过更新统计信息来实

4）使用示例

-- 重建索引
ALTER INDEX idx_customer_date REBUILD;

-- 监控索引使用情况（具体语句取决于使用的数据库管理系统）
-- 示例为 PostgreSQL
SELECT * FROM pg_stat_user_indexes WHERE idx_scan < 50;

-- 更新统计信息（以 PostgreSQL 为例）
ANALYZE orders;

6、分析查询并调整索引

大多数数据库管理系统（如MySQL、PostgreSQL）都提供了查看SQL查询执行计划的功能。可以通过命令如EXPLAIN或EXPLAIN ANALYZE来实现。执行计划会显示数据库是如何处理SQL语句的，特别是索引的使用情况。定位哪些查询运行缓慢是优化的第一步。使用如SHOW PROCESSLIST或特定的日志文件可以帮助找到需要优化的慢查询。观察哪些列经常用于WHERE子句、JOIN条件或作为ORDER BY的一部分。这些列是添加索引的好候选。

若有一个名为orders的表，其中包含id（主键）、customer_id、order_date和amount。

-- 创建基本索引
CREATE INDEX idx_customer_id ON orders(customer_id);

-- 创建复合索引
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

-- 分析查询
EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2022-01-01';

-- 检查索引覆盖
EXPLAIN SELECT customer_id, order_date FROM orders WHERE customer_id = 123;

7、避免过多索引

SQL的索引优化时，一个常见的问题是如何平衡索引的数量和查询性能。虽然索引可以显著提高查询速度，但过多的索引会增加维护成本并减慢写操作。每个额外的索引都会消耗更多的存储空间，并增加插入、更新和删除操作的成本。 定期审查索引的使用情况，移除那些很少或从未使用的索引。使用索引时，关键在于找到需求和资源之间的平衡点。