数据库索引的创建及失效

数据库索引

索引的概念

数据库索引是数据库管理系统（DBMS）使用的一种数据结构，用于提高检索数据的速度。没有索引，DBMS必须执行全表扫描（即检查数据库中的每一行），以找到与查询匹配的行。索引允许快速定位数据，而不必扫描每一行。索引可以显著加快数据检索速度，但会占用磁盘空间并可能在插入、删除和更新操作中降低性能。
简而言之图书的目录

索引的类型及创建

数据库索引的类型多种多样，每种类型都有其特定的用途和优点。下面是MySQL中常见的几种索引类型以及创建这些索引的示例代码：

1. 主键索引（Primary Key Index）

主键索引是唯一索引的一种，用于标识表中的每一行数据，不允许有重复值。

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

2. 唯一索引（Unique Index）

唯一索引确保列中的值是唯一的，但可以包含NULL值。

CREATE TABLE users (
    email VARCHAR(100),
    UNIQUE INDEX unique_email (email)
);

3. 普通索引（Index 或 General Index）

这是最常见的索引类型，用于加速查询。

CREATE TABLE products (
    product_id INT,
    name VARCHAR(50),
    price DECIMAL(10,2),
    INDEX idx_product_name (name)
);

4. 全文索引（Fulltext Index）

全文索引用于全文搜索，适用于较大的文本字段。

CREATE TABLE articles (
    article_id INT,
    title VARCHAR(100),
    content TEXT,
    FULLTEXT INDEX fulltext_content (content)
);

5. 复合索引（Composite Index 或 Multiple Column Index）

复合索引是在多个列上创建的索引，可以加速涉及这些列的查询。

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    INDEX idx_order_customer_product (customer_id, product_id)
);

6. 空间索引（Spatial Index）

空间索引用于存储和检索地理数据，如纬度和经度坐标。

CREATE TABLE locations (
    location_id INT,
    geom GEOMETRY,
    SPATIAL INDEX spatial_geom (geom)
);

7. 聚簇索引（Clustered Index）

在InnoDB存储引擎中，主键索引实际上就是聚簇索引，数据行与主键索引条目存储在一起。

CREATE TABLE items (
    item_id INT AUTO_INCREMENT,
    description VARCHAR(100),
    PRIMARY KEY (item_id)
);

示例：创建和使用索引

假设我们有一个customers表，包含id, first_name, last_name, 和email字段。我们可以创建以下索引：

-- 创建普通索引
CREATE INDEX idx_first_name ON customers(first_name);

-- 创建唯一索引
CREATE UNIQUE INDEX uq_email ON customers(email);

-- 创建复合索引
CREATE INDEX idx_full_name ON customers(first_name, last_name);

使用这些索引，查询可以变得更高效：

-- 使用索引加速查询
SELECT * FROM customers WHERE first_name = 'John';

-- 使用唯一索引进行查找
SELECT * FROM customers WHERE email = 'john.doe@example.com';

-- 使用复合索引进行查找
SELECT * FROM customers WHERE first_name = 'Jane' AND last_name = 'Doe';

索引失效的原因以优化策略

1. 使用了不等于（!= 或 <>）的查询条件：
   使用不等于的条件时，数据库可能会认为全表扫描更为高效，因此不会使用索引。例如：

   SELECT * FROM emp WHERE age != 18;
   

   优化策略是尽量避免在索引列上使用不等于操作。

2. 对索引列进行了计算或函数操作：
   在索引列上进行计算或使用函数，数据库无法有效使用索引。例如：

   SELECT * FROM emp WHERE LEFT(name, 3) = 'abc';
   

   优化策略是尽量避免在查询条件中对索引字段使用函数或计算。

3. 使用了 OR 条件：
   如果 OR 前后的任何一个条件列没有被索引，那么索引就会失效。例如：

   SELECT * FROM emp WHERE id = 10010 OR name = 'abcd';
   

   优化策略是避免在查询条件中使用 OR 语句，或者确保 OR 前后的每一个字段都被索引。

4. 索引列上有 NULL 值：
   如果索引列上有 NULL 值，可能会导致索引失效。例如：

   EXPLAIN SELECT * FROM emp WHERE name IS NOT NULL;
   

   优化策略是尽量避免索引列的值为 NULL，可以在创建表的时候设置默认值或者将 NULL 替换为其他特殊值。

5. 类型转换导致索引失效：
   查询条件中的类型和索引列的类型不一致，会导致索引失效。例如：

   EXPLAIN SELECT * FROM emp WHERE name = 123;
   

   优化策略是确保查询条件中的类型和索引列的类型一致。

6. 不符合最佳左前缀原则：
   查询条件没有遵循最佳左前缀原则，导致索引失效。例如：

   EXPLAIN SELECT * FROM emp WHERE deptId = 1 AND name = 'abcd';
   

   优化策略是在创建索引和编写查询条件时，尽量遵循最佳左前缀原则。

7. 索引列中的范围查询导致索引失效：
   如果查询条件中包含范围查询，可能会导致索引失效。例如：

   EXPLAIN SELECT * FROM emp WHERE age = 30 AND deptId > 1000 AND name = 'abc';
   

   优化策略是调整查询条件，确保范围查询的字段在索引中的位置合适。

8. **使用了 SELECT ***：
   使用 SELECT * 会查询所有列，这可能导致需要回表查询非索引列，从而降低效率。优化策略是尽量只查询需要的列。

9. LIKE 查询左边有 %：
   当 LIKE 查询的模式以 % 开头时，索引会失效。例如：

   SELECT * FROM emp WHERE name LIKE '%abc';
   

   优化策略是确保 LIKE 查询的模式不以 % 开头。

10. 使用了 IN 或 NOT IN：
    当 IN 或 NOT IN 的取值范围较大时，可能会导致索引失效。优化策略是尽量避免使用大范围的 IN 或 NOT IN。

11. 使用了 OR 操作：
    如果 OR 两边的条件中有一个字段没有索引，可能会导致索引失效。优化策略是确保 OR 两边的条件字段都有索引。

12. 两列做比较：
    如果两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。例如：

    SELECT * FROM emp WHERE id > age;
    

    优化策略是避免在查询条件中进行两列的比较。

最左匹配原则

最左匹配原则（Most Left Prefix Principle）是复合索引（也称为多列索引或多字段索引）中一个重要的概念。这个原则决定了数据库管理系统（DBMS）如何在复合索引上进行搜索和匹配。简单来说，复合索引按照从左至右的顺序对多个列进行排序，而查询优化器在使用这样的索引时也会遵循相同的顺序。

最左匹配原则的含义

当你在一个包含多个列的复合索引上执行查询时，DBMS会尝试从索引的最左边的列开始匹配查询条件。如果第一个列的值匹配，则DBMS将继续向右移动到下一个列进行匹配，以此类推，直到所有的查询条件都被满足或者索引列用完为止。

例如，你有一个复合索引 (column1, column2, column3)，那么DBMS可以有效地处理以下类型的查询：

• WHERE column1 = value1
• WHERE column1 = value1 AND column2 = value2
• WHERE column1 = value1 AND column2 = value2 AND column3 = value3

但是，如果查询条件不符合这个从左至右的顺序，比如只包含 column2 或者 column3 的条件，或者条件顺序为 column3 和 column2，那么复合索引可能不会被充分利用，甚至完全不被使用。

实例说明

假设我们有一个表 orders，它具有以下结构：

• order_id (INT)
• customer_id (INT)
• order_date (DATE)

并且我们在 customer_id 和 order_date 上创建了一个复合索引：

CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

现在，以下查询可以有效利用这个复合索引：

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-01-01';

但是，以下查询则可能不会利用复合索引：

SELECT * FROM orders WHERE order_date = '2023-01-01'; // 缺少最左前缀
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01'; // 右侧列使用了范围查询

在最后一个例子中，虽然查询包含了 customer_id（复合索引的最左列），但由于 order_date 列使用了范围查询（>），所以索引可能不会被完全利用，因为范围查询通常只能利用到范围条件之前的部分索引。