数据库索引及命中条件解析

数据库索引及索引命中记录

   数据库索引是一种用于加速数据检索的数据结构。它类似于书籍的目录，可以帮助数据库快速定位到所需的数据，而不需要扫描整个表。索引可以显著提高查询性能，尤其是在处理大量数据时。

索引类型

单列索引：基于表中的单个列创建的索引。
复合索引：基于表中的多个列创建的索引。
唯一索引：确保索引列中的值是唯一的。
主键索引：一种特殊的唯一索引，用于唯一标识表中的每一行。
全文索引：用于全文搜索，通常用于文本字段。
哈希索引：基于哈希表的索引，适用于等值查询。
位图索引：适用于低基数列（即列中不同值的数量较少）。

索引命中

索引命中是指数据库在执行查询时，能够利用索引来加速数据检索的过程。如果查询条件能够匹配索引的列，数据库引擎就会使用索引来快速定位数据，而不是扫描整个表。

标题索引命中的条件

1.  查询条件匹配索引列：查询条件中的列必须与索引中的列一致或部分一致（对于复合索引）。
2. 索引选择性高：索引的选择性是指索引列中不同值的数量与总行数的比例。选择性高的索引（即不同值较多的列）更容易命中。
3. 查询类型适合索引：某些查询类型（如等值查询、范围查询）更适合使用索引，而其他类型（如模糊查询、非等值查询）可能不适合。

索引未命中的情况

1. 查询条件不匹配索引列：如果查询条件中的列不在索引中，数据库将无法使用索引。
2.索引选择性低：如果索引列的选择性很低（即不同值很少），数据库可能会选择全表扫描而不是使用索引。
3.索引失效：某些操作（如对索引列进行函数操作、类型转换等）可能导致索引失效。

创建索引

CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(name, age);

查询示例

1. 索引命中：
   SELECT * FROM users WHERE name = ‘Alice’;
   这个查询会命中 idx_name 索引。
   2. 复合索引命中：
   SELECT * FROM users WHERE name = ‘Alice’ AND age = 25;
   这个查询会命中 idx_name_age 索引。
   3. 索引未命中：
   SELECT * FROM users WHERE age = 25;
   这个查询不会命中 idx_name_age 索引，因为 age 不是索引的第一列。
   4. 索引失效：
   SELECT * FROM users WHERE LOWER(name) = ‘alice’;
   这个查询不会命中 idx_name 索引，因为对 name 列使用了函数操作。

避免索引失效，可以采取以下方法：

1. 避免在索引列上使用函数
   尽量将函数应用到查询条件的常量部分，而不是索引列本身。例如：

  错误示例：
    SELECT * FROM orders WHERE YEAR(order_date) = 2023;
  优化后：
   SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
  这样可以直接利用 order_date 上的索引。

2. 使用范围查询代替函数
   如果需要对时间字段进行范围查询，尽量使用 BETWEEN 或 >=、<= 等操作符，而不是函数。

  错误示例：
   SELECT * FROM orders WHERE MONTH(order_date) = 10;
 优化后：
  SELECT * FROM orders WHERE order_date >= '2023-10-01' AND order_date < '2023-11-01';

3. 使用函数索引（Function-Based Index）
   某些数据库（如 Oracle、PostgreSQL）支持函数索引，可以直接对函数的结果创建索引。

  CREATE INDEX idx_order_year ON orders (YEAR(order_date));
-- 查询时可以直接使用函数
   SELECT * FROM orders WHERE YEAR(order_date) = 2023;

   CREATE INDEX idx_order_year ON orders (EXTRACT(YEAR FROM order_date));
-- 查询时可以直接使用函数
  SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

etc;