CentOS环境下SQL*Plus索引优化策略

一、索引创建前的规划与设计

1. 选择高选择性列创建索引

索引的选择性（不同值数量/总记录数）是衡量其有效性的核心指标。**选择性高于30%**的列（如唯一值多的主键、外键或有明显区分度的字段）创建索引后，能显著减少查询扫描的行数。例如，在employees表的employee_id（唯一值）或last_name（假设区分度高）上创建索引，可大幅提升条件查询效率。

2. 优先为高频查询条件列建索引

针对WHERE、JOIN、ORDER BY、GROUP BY子句中频繁出现的列创建索引。例如，若查询常通过department_id筛选员工或关联departments表，应在employees.department_id上建立索引；若需按salary排序，可在salary列上创建索引。

3. 合理设计复合索引

复合索引（多列索引）的顺序需遵循最左前缀原则：查询条件必须包含索引的最左列才能命中索引。例如，创建(last_name, first_name)的复合索引后，WHERE last_name = 'Smith'或WHERE last_name = 'Smith' AND first_name = 'Li'能命中索引，但WHERE first_name = 'Li'则无法使用该索引。此外，复合索引的列顺序应根据查询频率和选择性调整，将高频、高选择性的列放在左侧。

二、避免索引失效的关键注意事项

1. 禁止在索引列上进行计算或函数操作

在索引列上使用函数（如YEAR(register_date) = 2023）、计算（如amount + 100 > 500）或类型转换（如user_id = '10086'，其中user_id为整型），会导致索引失效，转为全表扫描。优化方法是将计算转移到条件右侧，例如register_date BETWEEN '2023-01-01' AND '2023-12-31'或amount > 400。

2. 避免前导通配符查询

LIKE '%abc%'这样的前导通配符查询（通配符在开头）会使索引失效，因为B+树索引无法从中间开始匹配。若需模糊查询，尽量使用后导通配符（如LIKE 'abc%'），此时索引仍可生效。

3. 避免使用OR连接未索引的条件

若OR连接的多个条件中有一个列未创建索引，则整个查询会转为全表扫描。例如，WHERE last_name = 'Smith' OR department_id = 10（假设department_id无索引），即使last_name有索引，也无法使用。优化方法是将OR改为UNION：SELECT * FROM employees WHERE last_name = 'Smith' UNION SELECT * FROM employees WHERE department_id = 10。

4. 避免不等操作符（< > ）

< > （不等于）操作符通常会导致索引失效，因为数据库无法通过索引快速定位不符合条件的行。若需查询不等于某值的记录，可考虑使用NOT IN或NOT EXISTS替代，但需注意其性能影响。

三、索引的日常维护与优化

1. 定期重建或重组碎片化索引

随着数据的增删改，索引会产生碎片，导致查询性能下降。可通过ALTER INDEX index_name REBUILD（重建索引，适用于大索引）或ALTER INDEX index_name COALESCE（重组索引，适用于小索引）来整理碎片，恢复索引性能。

2. 删除冗余或未使用的索引

冗余索引（如重复创建的索引、复合索引中包含的单列索引已被复合索引覆盖）会增加写操作（INSERT、UPDATE、DELETE）的开销，降低数据库性能。可通过数据库工具（如Oracle的DBA_INDEXES视图）识别冗余索引并删除。同时，定期检查sys.dm_db_index_usage_stats（SQL Server）或v$object_usage（Oracle）视图，删除未使用的索引。

3. 更新统计信息

过时的统计信息会导致查询优化器选择错误的执行计划（如选择全表扫描而非索引扫描）。需定期对表和索引进行统计信息收集（如Oracle的ANALYZE TABLE table_name COMPUTE STATISTICS或MySQL的ANALYZE TABLE table_name），确保优化器能准确评估索引的使用价值。

四、SQL*Plus中的索引使用技巧

1. 使用EXPLAIN PLAN分析索引使用情况

在执行SQL语句前，用EXPLAIN PLAN FOR SELECT ...分析查询计划，通过SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)查看结果，确认是否命中了预期的索引。若未命中，需检查索引是否存在、是否违反最左前缀原则或存在索引失效的情况。

2. 开启SQL*Plus的统计信息显示

通过SET TIMING ON显示SQL语句的执行时间，SET AUTOTRACE ON显示执行计划和统计信息（如逻辑读、物理读、执行时间），帮助快速识别慢查询和索引问题。例如，SET AUTOTRACE ON可显示“Table Access Full”（全表扫描），提示需创建索引。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！