真实系统优化：业务慢查询问题排查与修复

本文链接：https://blog.csdn.net/weixin_45525272/article/details/144157109

文章目录

- 真实系统优化：慢查询问题排查与修复

真实系统优化：慢查询问题排查与修复

1. 如何优化SQL查询：避免索引失效的常见问题

在复杂的 SQL 查询中，尤其是涉及多个表连接时，索引的使用对于查询性能至关重要。然而，在某些情况下，即使表中已有合适的索引，SQL 查询也可能由于某些原因导致索引失效，从而降低查询效率。本文将展示如何通过优化 SQL 查询，避免常见的索引失效问题，并提出一些加索引的建议。

1.1 常见的索引失效原因

使用了不适当的数据类型或表达式
- 在 SQL 查询中，如果使用了不匹配的字段数据类型或对字段进行了运算，索引可能会失效。比如，在查询条件中对索引列进行了运算或类型转换，索引将无法被有效利用。
示例：
```
SELECT * FROM employees WHERE TO_DATE(hire_date, 'YYYY-MM-DD') = '2022-01-01';
```
在上述查询中，即使 hire_date 字段有索引，使用 TO_DATE() 函数将导致索引失效。

优化：直接使用日期字段进行比较，避免在字段上进行函数操作：
```
SELECT * FROM employees WHERE hire_date = '2022-01-01';
```
使用了 OR 条件
- 在查询中，如果涉及到多个条件的 OR 连接，数据库通常会选择不使用索引，或者无法使用复合索引。特别是在 OR 条件中，每个条件涉及的字段不一致时，索引失效的风险更高。
示例：
```
SELECT * FROM orders WHERE customer_id = 1001 OR order_date = '2022-01-01';
```
即使 customer_id 和 order_date 各自有索引，OR 条件会导致数据库选择全表扫描。

优化：将 OR 条件拆成两个查询，使用 UNION ALL 进行合并，这样可以分别利用每个条件的索引：
```
SELECT * FROM orders WHERE customer_id = 1001
UNION ALL
SELECT * FROM orders WHERE order_date = '2022-01-01';
```
使用 LIKE 查询时的前缀匹配
- 使用 LIKE 进行模糊查询时，如果查询模式的前缀包含 %，则无法利用索引。前缀 % 会导致数据库无法使用索引，进行全表扫描。
示例：
```
SELECT * FROM products WHERE product_name LIKE '%apple%';
```
优化：如果查询条件是以固定前缀开始，可以确保索引被有效利用：
```
SELECT * FROM products WHERE product_name LIKE 'apple%';
```
连接条件不合理
- 在 SQL 查询中，连接条件中没有使用合适的索引，或使用了不相关的字段，可能导致数据库不能使用索引进行连接，从而导致查询性能下降。
示例：
```
SELECT * FROM orders o 
LEFT JOIN customers c ON o.customer_id = c.customer_code;
```
在这个查询中，假设 orders 表中的 customer_id 字段和 customers 表中的 customer_code 字段没有适当的索引，就会导致索引失效。

优化：确保连接条件中使用的字段都有索引。比如，建立在 customer_id 和 customer_code 字段上的索引：
```
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_customer_code ON customers(customer_code);
```

1.2 加索引的建议

创建组合索引
- 对于多个查询条件经常一起使用的字段，可以考虑创建复合索引。复合索引会使得查询条件中涉及的多个字段可以被有效索引，从而提高查询效率。
示例：如果查询条件常常包含 retail_order_id 和 product_group_code 字段，建议创建一个组合索引：
```
CREATE INDEX idx_order_product 
ON BUSUSER.BUS_RETAIL_ORDER_ITEM (retail_order_id, product_group_code);
```
避免对索引字段进行函数操作
- 尽量避免在查询中对索引字段进行运算或转换，确保数据库能够利用索引。
示例：
```
SELECT * FROM orders WHERE order_date > SYSDATE - 30;
```
这里可以直接利用 order_date 字段的索引，避免使用 TO_DATE 等转换函数。
优化 JOIN 查询中的索引使用
- 确保在 JOIN 查询中的连接字段上有适当的索引。例如，在 LEFT JOIN 查询中，连接条件应该是被索引的字段，这样可以减少全表扫描的开销。
示例：
```
SELECT c.name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
```
确保 customer_id 在 customers 表和 orders 表中都有索引。
定期检查和重建索引
- 在大规模更新、删除数据后，索引可能会变得不再高效。定期对索引进行重建，可以确保索引的最佳性能。
示例：
```
ALTER INDEX idx_customer_id REBUILD;
```

1.2 实际案例：SQL查询优化

假设你有以下查询，它涉及到多个表的连接，并且使用了 LEFT JOIN 来获取 order 和 product 的信息：

SELECT
    o.order_id,
    o.order_date,
    p.product_name
FROM
    orders o
LEFT JOIN
    products p ON o.product_id = p.product_id
WHERE
    o.customer_id = 1001
    AND p.product_group_code = 'A1';

在这个查询中，确保以下几点来避免索引失效：

为 orders.customer_id 和 products.product_group_code 创建单独的索引，或者组合索引。
确保 product_id 是 orders 和 products 表的主键或具有唯一索引。
避免在 WHERE 子句中对索引字段进行函数操作，如 TO_DATE 或 CONCAT。

2. 真实系统慢查询优化

下面我们到真实的系统中看一下具体的慢查询问题

2.1 原始SQL查询分析

假设我们有一个查询如下：

SELECT
    T.* 
FROM
    (
    SELECT
        C.INSURANCE_CLAIMS_ID,
        C.POLICY_NO,
        C.REGIST_NO,
        C.CLAIM_NO,
        C.POLICY_START_DATE,
        C.POLICY_END_DATE,
        C.DAMAGE_DATE,
        C.REPORT_DATE,
        C.CLAIM_DATE,
        C.PAY_TIME,
        C.RISK_NAME,
        C.ACCITYPE_NAME,
        C.DAMAGE_ADDRESS,
        C.MODEL_CODE,
        C.MODEL_NAME,
        C.VIN,
        C.DEDUCTIBLE_RATE,
        C.DEDUCTIBLE_REASON,
        C.ENDOR_AMOUNT,
        C.CLAIM_PROGRESS,
        C.CLAIM_STATUS,
        C.CREATED_BY,
        C.CREATION_DATE,
        C.LAST_UPDATED_BY,
        C.LAST_UPDATE_DATE,
        C.IS_HAND_WRITE_DATA,
        C.SIGN_GUIDE_CODE,
        C.SIGN_DATE,
        C.STATUS,
        C.FILE_ID,
        C.DEDUCTIBLE_AMOUNT,
        C.AM_PAY_STATUS,
        C.AM_PAY_TIME,
        C.SVC_SOURCE,
        o.retail_order_no AS lostRetailOrderNo,
        o.actual_controller_code AS lostActualControllerCode,
        o.actual_controller_name AS lostActualControllerName,
        o.distributor_code AS lostDistributorCode,
        o.distributor_name AS lostDistributorName,
        o.dot_code AS lostDotCode,
        o.dot_name AS lostDotName,
        dot.city_name AS LostCityName,
        O.SETS_OF_BOOKS_ID,
        O.ORG_ID AS lostOrgId,
        O.ORG_NAME AS lostOrgName,
        i.guide_code AS lostGuideCode,
        i.guide_name AS lostGuideName,
        item.design_code AS lostDesignCode,
        item.design_name AS lostDesignName,
        item.item_name AS lostItemName,
        item.item_code AS lostItemCode,
        item.out_time AS lostItemOutTime,
        item.frame_number AS lostFrameNumber,
        item.retail_price AS oldRetailPrice,
        i.customer_name AS lostCustomerName,
        i.card_number AS lostCardNumber,
        i.linker_mobile AS lostLinkerMobile,
        o1.retail_order_no,
        o1.org_name,
        o1.actual_controller_code,
        o1.actual_controller_name,
        o1.distributor_code,
        o1.distributor_name,
        o1.dot_code,
        o1.dot_name,
        o1.created_by AS guide_code,
        ( SELECT u.name FROM system_centerDB.system_user u WHERE O1.CREATED_BY = u.login_id ) AS guide_name,
        item1.design_code,
        item1.design_name,
        item1.item_code,
        item1.item_name,
        item1.frame_number,
        item1.retail_price,
        r1.insurance_type,
        r1.policy_no AS newPolicyNo 
    FROM
        bususerDB.bus_insurance_claims c
    LEFT JOIN bususerDB.bus_insurance_record r ON c.policy_no = r.policy_no
    LEFT JOIN bususerDB.bus_retail_order_insurance i ON i.retail_order_insurance_id = r.retail_order_insurance_id
    LEFT JOIN bususerDB.bus_retail_order o ON o.retail_order_id = i.retail_order_id
    LEFT JOIN archives_center.archives_dot dot ON dot.dot_code = o.dot_code
    LEFT JOIN bususerDB.bus_retail_order_item item ON item.retail_order_id = o.retail_order_id 
    AND item.product_group_code = 'A1'
    LEFT JOIN bususerDB.bus_retail_order o1 ON o1.insurance_claims_id = c.insurance_claims_id
    LEFT JOIN bususerDB.bus_retail_order_item item1 ON item1.retail_order_id = o1.retail_order_id 
    AND item1.product_group_code = 'A1'
    LEFT JOIN bususerDB.bus_insurance_record r1 ON r1.retail_order_id = o1.retail_order_id 
    ) T 
ORDER BY
    T.policy_no DESC;

该查询通过多表联接来获取多个字段信息，主要是从 bususerDB.bus_insurance_claims、bususerDB.bus_insurance_record、bususerDB.bus_retail_order 等多个表中进行联接，并最终按 T.policy_no DESC 进行排序。

2.2 问题分析：索引以及索引失效的原因

在这个查询中，可能导致索引失效的几个常见原因包括：

联合查询中的 OR 语句：
在查询中，存在多个 LEFT JOIN 子句，这些子句连接条件不完全基于索引列。例如，LEFT JOIN bususerDB.bus_retail_order_item item ON item.retail_order_id = o.retail_order_id AND item.product_group_code = 'A1' 和 LEFT JOIN bususerDB.bus_retail_order_item item1 ON item1.retail_order_id = o1.retail_order_id AND item1.product_group_code = 'A1'，如果在 product_group_code 列上没有索引，查询性能可能会大幅下降。
排序字段没有索引：
查询最后是通过 ORDER BY T.policy_no DESC 来排序，如果 policy_no 字段没有建立索引，数据库就需要进行全表扫描或排序操作，影响查询性能。
复杂的子查询：
查询中有 SELECT u.name FROM system_centerDB.system_user u WHERE O1.CREATED_BY = u.login_id 子查询，虽然通常会返回单个值，但仍会导致额外的查询操作，从而影响性能。

2.3 优化步骤

为了优化这个查询，主要的目标是避免全表扫描，确保索引的有效利用。以下是优化的步骤：

2.3.1 使用适当的索引

根据查询的连接条件和排序字段，首先检查以下几个字段的索引：

policy_no：在 bususerDB.bus_insurance_claims 和 bususerDB.bus_insurance_record 表上添加 policy_no 字段的索引。
retail_order_id：在 bususerDB.bus_retail_order_item 和 bususerDB.bus_retail_order 表上添加 retail_order_id 字段的索引。
product_group_code：如果你经常根据 product_group_code 进行筛选，建议为 product_group_code 列添加索引。
insurance_claims_id：为 bususerDB.bus_retail_order 和 bususerDB.bus_retail_order 表的 insurance_claims_id 字段创建索引，以加速关联。

索引的建议：

CREATE INDEX idx_policy_no ON bususerDB.bus_insurance_claims(policy_no);
CREATE INDEX idx_retorder_id ON bususerDB.bus_retail_order_item(retail_order_id);
CREATE INDEX idx_product_group_code ON bususerDB.bus_retail_order_item(product_group_code);
CREATE INDEX idx_insurance_claims_id ON bususerDB.bus_retail_order(insurance_claims_id);
CREATE INDEX idx_created_by ON system_centerDB.system_user(login_id);

2.3.2 子查询优化

对于子查询 (SELECT u.name FROM system_centerDB.system_user u WHERE O1.CREATED_BY = u.login_id)，可以将其转换为 JOIN，从而避免每次执行子查询时的额外开销：

LEFT JOIN system_centerDB.system_user u ON O1.CREATED_BY = u.login_id

2.3.3 使用 `EXPLAIN` 分析查询计划

使用 EXPLAIN 或 EXPLAIN ANALYZE 来查看查询执行计划，并确定是否使用了索引。这样可以帮助你确认哪些部分的查询没有充分利用索引。

EXPLAIN SELECT ...

2.3.4 使用合适的表连接顺序

尽量让最具过滤能力的表（通常是包含较少数据的表或有过滤条件的表）出现在查询的前面，这样可以减少连接的行数，从而提高查询效率。

2.4 优化后的SQL示例

SELECT
    T.* 
FROM
    (
    SELECT
        C.INSURANCE_CLAIMS_ID,
        C.POLICY_NO,
        C.REGIST_NO,
        C.CLAIM_NO,
        C.POLICY_START_DATE,
        C.POLICY_END_DATE,
        C.DAMAGE_DATE,
        C.REPORT_DATE,
        C.CLAIM_DATE,
        C.PAY_TIME,
        C.RISK_NAME,
        C.ACCITYPE_NAME,
        C.DAMAGE_ADDRESS,
        C.MODEL_CODE,
        C.MODEL_NAME,
        C.VIN,
        C.DEDUCTIBLE_RATE,
        C.DEDUCTIBLE_REASON,
        C.ENDOR_AMOUNT,
        C.CLAIM_PROGRESS,
        C.CLAIM_STATUS,
        C.CREATED_BY,
        C.CREATION_DATE,
        C.LAST_UPDATED_BY,
        C.LAST_UPDATE_DATE,
        C.IS_HAND_WRITE_DATA,
        C.SIGN_GUIDE
        C.SIGN_DATE,
        C.STATUS,
        C.FILE_ID,
        C.DEDUCTIBLE_AMOUNT,
        C.AM_PAY_STATUS,
        C.AM_PAY_TIME,
        C.SVC_SOURCE,
        o.retail_order_no AS lostRetailOrderNo,
        o.actual_controller_code AS lostActualControllerCode,
        o.actual_controller_name AS lostActualControllerName,
        o.distributor_code AS lostDistributorCode,
        o.distributor_name AS lostDistributorName,
        o.dot_code AS lostDotCode,
        o.dot_name AS lostDotName,
        dot.city_name AS LostCityName,
        O.SETS_OF_BOOKS_ID,
        O.ORG_ID AS lostOrgId,
        O.ORG_NAME AS lostOrgName,
        i.guide_code AS lostGuideCode,
        i.guide_name AS lostGuideName,
        item.design_code AS lostDesignCode,
        item.design_name AS lostDesignName,
        item.item_name AS lostItemName,
        item.item_code AS lostItemCode,
        item.out_time AS lostItemOutTime,
        item.frame_number AS lostFrameNumber,
        item.retail_price AS oldRetailPrice,
        i.customer_name AS lostCustomerName,
        i.card_number AS lostCardNumber,
        i.linker_mobile AS lostLinkerMobile,
        o1.retail_order_no,
        o1.org_name,
        o1.actual_controller_code,
        o1.actual_controller_name,
        o1.distributor_code,
        o1.distributor_name,
        o1.dot_code,
        o1.dot_name,
        o1.created_by AS guide_code,
        u.name AS guide_name,
        item1.design_code,
        item1.design_name,
        item1.item_code,
        item1.item_name,
        item1.frame_number,
        item1.retail_price,
        r1.insurance_type,
        r1.policy_no AS newPolicyNo 
    FROM
        bususerDB.bus_insurance_claims c
    LEFT JOIN bususerDB.bus_insurance_record r ON c.policy_no = r.policy_no
    LEFT JOIN bususerDB.bus_retail_order_insurance i ON i.retail_order_insurance_id = r.retail_order_insurance_id
    LEFT JOIN bususerDB.bus_retail_order o ON o.retail_order_id = i.retail_order_id
    LEFT JOIN archives_center.archives_dot dot ON dot.dot_code = o.dot_code
    LEFT JOIN bususerDB.bus_retail_order_item item ON item.retail_order_id = o.retail_order_id 
    AND item.product_group_code = 'A1'
    LEFT JOIN bususerDB.bus_retail_order o1 ON o1.insurance_claims_id = c.insurance_claims_id
    LEFT JOIN bususerDB.bus_retail_order_item item1 ON item1.retail_order_id = o1.retail_order_id 
    AND item1.product_group_code = 'A1'
    LEFT JOIN bususerDB.bus_insurance_record r1 ON r1.retail_order_id = o1.retail_order_id 
    LEFT JOIN system_centerDB.system_user u ON o1.created_by = u.login_id
    ) T 
ORDER BY
    T.policy_no DESC;

3. 业务拆解优化：通过业务拆解提高查询性能

在处理复杂的 SQL 查询时，拆解业务往往是提高查询性能的有效手段。尤其是在涉及大量数据和多个连接表的查询时，将部分查询拆解成子查询或独立查询，可以减少不必要的计算和数据加载，从而优化整体性能。本文将基于一个实际的查询场景，介绍如何通过拆解查询来优化性能。

3.1 问题背景

接着上面的优化，我们在查询一个涉及车辆保险理赔信息的查询，查询中包含多个 LEFT JOIN 连接，特别是两个 LEFT JOIN 连接 bus_retail_order o1 和 bus_retail_order_item item1。在原始查询中，所有的数据都在一个 SQL 查询中一次性加载，这不仅会增加数据库的负担，还可能导致查询速度变慢，甚至在导出大数据量时导致查询失败。

例如，查询包括了丢车的车辆订单信息和赔付新车的车辆订单信息，这两部分信息是不同的业务逻辑，但它们在查询中都被一次性加载，造成了不必要的数据处理。

3.2 拆解查询

为了提高查询性能，我们可以考虑将涉及赔付新车的 LEFT JOIN 查询拆解出来，单独处理。只有在需要赔付新车相关信息时，才单独查询。这种方法可以减轻查询的复杂度和数据量，避免一次性加载过多不必要的数据。

3.3 优化方案：业务拆解

我们将原有查询中与赔付新车相关的部分拆解为一个独立的查询，在需要时才进行查询，避免了在查询所有理赔信息时引入不必要的 LEFT JOIN。

3.3.1 拆解后查询

将 LEFT JOIN bususer.bus_retail_order o1 和 LEFT JOIN bususer.bus_retail_order_item item1 部分拆解为单独的查询，只有在需要时才查询：

-- 第一步：查询基本的理赔信息
SELECT
    C.INSURANCE_CLAIMS_ID,
    C.POLICY_NO,
    C.REGIST_NO,
    C.CLAIM_NO,
    C.POLICY_START_DATE,
    C.POLICY_END_DATE,
    C.DAMAGE_DATE,
    C.REPORT_DATE,
    C.CLAIM_DATE,
    C.PAY_TIME,
    C.RISK_NAME,
    C.ACCITYPE_NAME,
    C.DAMAGE_ADDRESS,
    C.MODEL_CODE,
    C.MODEL_NAME,
    C.VIN,
    C.DEDUCTIBLE_RATE,
    C.DEDUCTIBLE_REASON,
    C.ENDOR_AMOUNT,
    C.CLAIM_PROGRESS,
    C.CLAIM_STATUS,
    C.CREATED_BY,
    C.CREATION_DATE,
    C.LAST_UPDATED_BY,
    C.LAST_UPDATE_DATE,
    C.IS_HAND_WRITE_DATA,
    C.SIGN_GUIDE_CODE,
    C.SIGN_DATE,
    C.STATUS,
    C.FILE_ID,
    C.DEDUCTIBLE_AMOUNT,
    C.AM_PAY_STATUS,
    C.AM_PAY_TIME,
    C.SVC_SOURCE,
    o.retail_order_no AS lostRetailOrderNo,
    o.actual_controller_code AS lostActualControllerCode,
    o.actual_controller_name AS lostActualControllerName,
    o.distributor_code AS lostDistributorCode,
    o.distributor_name AS lostDistributorName,
    o.dot_code AS lostDotCode,
    o.dot_name AS lostDotName,
    dot.city_name AS LostCityName,
    O.SETS_OF_BOOKS_ID,
    O.ORG_ID AS lostOrgId,
    O.ORG_NAME AS lostOrgName,
    i.guide_code AS lostGuideCode,
    i.guide_name AS lostGuideName,
    item.design_code AS lostDesignCode,
    item.design_name AS lostDesignName,
    item.item_name AS lostItemName,
    item.item_code AS lostItemCode,
    item.out_time AS lostItemOutTime,
    item.frame_number AS lostFrameNumber,
    item.retail_price AS oldRetailPrice,
    i.customer_name AS lostCustomerName,
    i.card_number AS lostCardNumber,
    i.linker_mobile AS lostLinkerMobile
FROM
    bususer.bus_insurance_claims c
LEFT JOIN bususer.bus_insurance_record r ON c.policy_no = r.policy_no
LEFT JOIN bususer.bus_retail_order_insurance i ON i.retail_order_insurance_id = r.retail_order_insurance_id
LEFT JOIN bususer.bus_retail_order o ON o.retail_order_id = i.retail_order_id
LEFT JOIN archives_center.archives_dot dot ON dot.dot_code = o.dot_code
LEFT JOIN bususer.bus_retail_order_item item ON item.retail_order_id = o.retail_order_id 
AND item.product_group_code = 'A1';

-- 第二步：查询赔付新车的车辆订单信息
SELECT
    o1.retail_order_no,
    o1.org_name,
    o1.actual_controller_code,
    o1.actual_controller_name,
    o1.distributor_code,
    o1.distributor_name,
    o1.dot_code,
    o1.dot_name,
    o1.created_by AS guide_code,
    (SELECT u.name FROM system_center.system_user u WHERE O1.CREATED_BY = u.login_id) AS guide_name,
    item1.design_code,
    item1.design_name,
    item1.item_code,
    item1.item_name,
    item1.frame_number,
    item1.retail_price,
    r1.insurance_type,
    r1.policy_no AS newPolicyNo
FROM
    bususer.bus_retail_order o1
LEFT JOIN bususer.bus_retail_order_item item1 ON item1.retail_order_id = o1.retail_order_id 
AND item1.product_group_code = 'A1'
LEFT JOIN bususer.bus_insurance_record r1 ON r1.retail_order_id = o1.retail_order_id;

3.4 优化效果

通过将查询拆解为两个子查询，我们实现了：

减小数据加载量：当只需要丢车的订单信息时，第二个查询不会被执行，减少了不必要的数据计算。
提高查询灵活性：只有在需要赔付新车订单信息时，才会执行第二个查询，避免一次性加载所有数据。

4. MySQL 和 Oracle 索引信息查询与创建

索引是数据库中用于加速数据查询的重要工具。为了有效地利用索引并优化查询性能，了解如何查询现有索引信息、创建合适的索引至关重要。不同的数据库系统（如 MySQL 和 Oracle）提供了不同的方式来查询和创建索引。本文将详细介绍如何在 MySQL 和 Oracle 中查询索引信息，并展示如何创建各种类型的索引。

4.1 Oracle 索引信息查询与创建

在 Oracle 数据库中，索引是一个非常重要的优化工具，尤其在处理大数据量时。可以通过系统视图查询索引信息，并通过 SQL 语句来创建各种类型的索引。

4.1.1 查询 Oracle 索引信息

要查询一个表的索引信息，通常会用到 ALL_INDEXES 和 USER_IND_COLUMNS 视图。

ALL_INDEXES：返回当前用户有权限访问的所有索引的元数据，包括索引名称、索引的表名、唯一性等。
USER_IND_COLUMNS：返回索引的列信息，包括每个索引的列名和列的位置。

查询表的所有索引：

SELECT 
    index_name,        -- 索引名称
    table_name,        -- 表名
    uniqueness,        -- 索引的唯一性（UNIQUE 或 NONUNIQUE）
    column_name,       -- 索引列名
    column_position    -- 索引列的位置
FROM 
    user_ind_columns   -- 查询当前用户的索引列信息
WHERE 
    table_name = 'YOUR_TABLE_NAME'  -- 指定表名
ORDER BY 
    index_name, column_position;   -- 按索引名称和列位置排序

index_name：索引名称
table_name：索引所在的表名
uniqueness：索引的唯一性，UNIQUE 表示唯一索引，NONUNIQUE 表示非唯一索引
column_name：索引列名
column_position：列在索引中的位置（如果是复合索引，位置从 1 开始）

查询索引的详细信息：

SELECT 
    i.index_name,
    i.table_name,
    i.uniqueness,
    c.column_name,
    c.column_position
FROM 
    all_indexes i           -- 索引信息
JOIN 
    all_ind_columns c       -- 索引列信息
ON 
    i.index_name = c.index_name
WHERE 
    i.table_name = 'YOUR_TABLE_NAME'
    AND i.table_owner = 'YOUR_SCHEMA_NAME'
ORDER BY 
    i.index_name, c.column_position;

4.1.2 创建 Oracle 索引

Oracle 提供了多种类型的索引，适用于不同的查询场景。常见的索引类型包括普通索引、唯一索引、位图索引和函数索引。

普通索引：最常用的索引类型，用于加速查询操作。
唯一索引：确保索引列中的值是唯一的，通常用于主键和唯一约束。
位图索引：通常用于低基数的列（例如性别、状态等）。
函数索引：对列值进行计算后创建索引，适用于需要处理表达式或函数的查询。

创建普通索引：

CREATE INDEX index_name  -- 创建索引
ON your_table_name (column1, column2);  -- 索引列（可以多个列）

创建唯一索引：

CREATE UNIQUE INDEX index_name  -- 创建唯一索引
ON your_table_name (column1, column2);

创建位图索引：

CREATE BITMAP INDEX index_name  -- 创建位图索引
ON your_table_name (column1);

创建函数索引：

CREATE INDEX index_name  -- 创建函数索引
ON your_table_name (UPPER(column1));  -- 对列应用函数（如大写转换）

4.1.3 删除索引

删除不再需要的索引可以减少数据库的维护成本，尤其是在索引增多时。

DROP INDEX index_name;

4.2. MySQL 索引信息查询与创建

MySQL 也支持多种类型的索引，可以通过查询 information_schema 来获取表的索引信息。MySQL 提供了高效的查询方式和索引优化工具，尤其在处理大量数据时。

4.2.1 查询 MySQL 索引信息

MySQL 中，索引信息存储在 information_schema 数据库中的 KEY_COLUMN_USAGE 和 STATISTICS 表中。

查询表的所有索引信息：

SELECT 
    table_name,       -- 表名
    index_name,       -- 索引名称
    non_unique,       -- 是否为非唯一索引（1 表示非唯一，0 表示唯一）
    column_name,      -- 索引列名
    seq_in_index      -- 列在索引中的顺序
FROM 
    information_schema.key_column_usage
WHERE 
    table_schema = 'your_database_name'   -- 指定数据库名称
    AND table_name = 'your_table_name'     -- 指定表名;

table_name：表名
index_name：索引名称
non_unique：是否为非唯一索引，1 表示非唯一，0 表示唯一
column_name：索引列名
seq_in_index：列在索引中的顺序

4.2.2 创建 MySQL 索引

MySQL 支持普通索引、唯一索引、全文索引和空间索引等类型的索引。

创建普通索引：

CREATE INDEX index_name  -- 创建普通索引
ON your_table_name (column1, column2);  -- 索引列（可以多个列）

创建唯一索引：

CREATE UNIQUE INDEX index_name  -- 创建唯一索引
ON your_table_name (column1, column2);

创建全文索引（适用于 TEXT 类型）：

CREATE FULLTEXT INDEX index_name  -- 创建全文索引
ON your_table_name (column1);

创建空间索引（适用于 GIS 数据类型）：

CREATE SPATIAL INDEX index_name  -- 创建空间索引
ON your_table_name (column1);

4.2.3 删除索引

和 Oracle 一样，MySQL 也提供了删除索引的功能，避免不必要的索引浪费空间。

DROP INDEX index_name ON your_table_name;

4.3 总结

Oracle 索引查询：通过 USER_IND_COLUMNS 和 ALL_INDEXES 视图查询索引详细信息，可以查询索引的唯一性、列信息等。
MySQL 索引查询：通过 information_schema 查询表的索引信息，包含索引的名称、唯一性、列信息等。
索引创建：无论是 Oracle 还是 MySQL，都支持多种类型的索引，如普通索引、唯一索引、全文索引等。
优化建议：根据查询的实际需求选择合适的索引类型，避免过多不必要的索引，同时定期评估和维护索引，确保查询性能。

通过合理地管理和创建索引，可以显著提高数据库查询性能，减少 I/O 操作。在实际应用中，根据查询模式选择合适的索引类型和策略，将是提升数据库性能的关键。