MySQL数据库索引优化及应用

这是一篇关于 mysql 数据库索引的全面介绍，从基本概念到高级特性，希望能帮助你深入理解。

一、索引是什么？

想象一下一本书的目录。如果没有目录，你想找到某一章节的内容，只能从第一页开始一页一页地翻找。而有了目录，你可以通过章节标题快速定位到对应的页码。

在 mysql 中，索引就是一种帮助数据库高效获取数据的“目录”。它是在存储引擎层实现的，而不是服务器层，因此不同的存储引擎（如 innodb、myisam）的索引工作方式有所不同（我们主要讨论最常用的 innodb）。

没有索引：数据库需要进行全表扫描，从第一行开始，逐行读取直到找到所有符合条件的行。对于海量数据，这非常缓慢。
有索引：数据库可以通过索引直接定位到数据的大概位置，然后只需检查很少的数据行即可得到结果，效率极高。

二、索引的优缺点

优点

• 大大加快数据的查询速度：这是最主要的原因，尤其是对 where、order by 和 group by 子句。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• ​加速表与表之间的连接：在进行表连接查询时，对有外键或连接键的列建立索引可以显著提高性能。

缺点

• 创建和维护索引需要耗费时间：对表进行增、删、改操作（insert、update、delete）时，索引也需要动态维护，这会降低数据写入的速度。
• 索引需要占用物理空间：除了数据表占据数据空间之外，每一个索引还要占一定的物理空间。
• 需要权衡：过多的索引或不恰当的索引不仅不能提高性能，反而会成为系统的负担。

三、索引的常见类型

1. 按数据结构划分

• b+tree 索引：mysql 最常用、默认的索引类型。innodb 和 myisam 都支持。
• 特点：
  • 数据都存储在叶子节点，非叶子节点只存储键值，因此查询效率稳定。
  • 叶子节点之间通过指针相连，形成了有序链表，非常适合范围查询（如 between、>、<）和排序。
  • 适用于：全键值、键值范围、键值前缀（最左前缀）查询。
• hash 索引：
  • 特点：通过对索引键计算一个哈希码来定位数据，所以等值查询（=）的效率极高，时间复杂度接近 o(1)。
• 局限性：
• 无法用于范围查询。
• 不支持排序。
  • 不支持部分索引键匹配（因为哈希值是基于整个索引键计算的）。
• 注意：innodb 引擎有一个“自适应哈希索引”功能，当某些索引值被非常频繁地访问时，它会在内存中基于 b-tree 索引的键再创建一个哈希索引，以加速查询。这是引擎自动完成的，用户无法手动创建哈希索引（memory 存储引擎支持）。
• r-tree (空间索引)：
  • 主要用于地理空间数据类型，如 geometry、point 等。日常业务中很少使用。
• full-text (全文索引)：
  • 用于快速查找文本中的关键字，类似于搜索引擎。它有自己的语法，使用 match ... against 子句。 性能差强人意，一般也很少使用。

2. 按物理存储划分（innodb 聚簇索引特性）

这是 innodb 引擎的一个核心概念。

• 聚簇索引
• 定义：表数据本身其实就是聚簇索引。索引的叶子节点直接存储了完整的数据行。
• 每个 innodb 表有且只有一个聚簇索引。
  • 它是如何被选择的？
  • 如果你定义了主键（primary key），那么主键就是聚簇索引。
  • 如果没有主键，则选择第一个不允许为 null 的唯一索引（unique key）作为聚簇索引。
  • 如果两者都没有，innodb 会隐式地创建一个隐藏的 rowid 字段作为聚簇索引。
• 优点：因为数据行就存放在叶子节点，所以通过聚簇索引访问数据非常快。
• 缺点：插入速度严重依赖于主键的顺序。乱序插入可能导致页分裂，影响性能。
• 非聚簇索引（也叫二级索引或辅助索引）
• 定义：索引的叶子节点存储的不是完整的数据行，而是该行对应的主键值。
  • 当通过非聚簇索引查询时，数据库需要先找到对应的主键，然后再用这个主键回到聚簇索引中查找完整的行数据。这个过程被称为回表。
  • 因此，基于非聚簇索引的查询通常比基于主键的查询要慢，尤其是需要回表很多行时。

3. 按字段特性划分

普通索引：最基本的索引，没有任何限制。

create index idx_name on table_name (column_name);

唯一索引：索引列的值必须是唯一的，但允许有空值。

create unique index idx_email on users (email);

主键索引：一种特殊的唯一索引，不允许有空值。每个表只能有一个主键索引。

组合索引（复合索引）：在多个列上建立的索引。

create index idx_name_age on employees (last_name, first_name, age);

最左前缀原则：这是组合索引最重要的特性。查询时，索引只能从最左边的列开始匹配。上面的 idx_name_age 索引对以下查询有效：

where last_name = ‘smith’

where last_name = ‘smith’ and first_name = ‘john’

where last_name = ‘smith’ and first_name = ‘john’ and age = 30

where last_name = ‘smith’ and age = 30 (只使用了 last_name，因为 first_name 断了)

但对以下查询无效：

where first_name = ‘john’

where age = 30

四、索引的使用策略与优化

• 哪些情况需要创建索引？
  • 主键自动建立唯一索引。
  • 频繁作为查询条件（where）的字段。
  • 与其他表进行关联的字段（外键）。
  • 频繁需要排序（order by）和分组（group by）的字段。
  • 查询中需要统计或分组的字段。
• 哪些情况不适合创建索引？
  • 表记录太少（例如配置表，可能只有几十行）。
  • 频繁进行增、删、改的表（需要权衡读写比例）。
  • 数据重复且分布均匀的字段（如“性别”字段，只有‘男‘/’女‘，建立索引意义不大）。
  • 很少或从不参与查询的字段。
• 索引优化技巧
• 前缀索引：对于很长的字符列（如 varchar(255)），可以只对列的前 n 个字符建立索引，以节省空间。

• create index idx_url_prefix on websites (url(20));

• 覆盖索引：如果一个索引包含了查询所需要的所有字段，我们就称之为“覆盖索引”。这时，查询可以直接从索引中获取数据，而无需回表，极大地提升了性能。
• 例如，有一个索引 (a, b, c)，查询 select a, b from table where a = 1 and b = 2 就是一个覆盖索引查询。
• 索引下推：这是 mysql 5.6 引入的重要优化。在没有 icp 之前，存储引擎会通过索引检索到数据，然后将完整的数据行返回给服务器层，再由服务器层根据 where 条件过滤。有了 icp 之后，如果 where 条件中的某些列也存在于索引中，存储引擎会在索引层面就完成一部分过滤，从而减少回表的次数。

索引使用总结：

正确理解和使用索引是数据库性能优化的基石。它需要在查询速度和写入开销之间做出权衡，并通过不断的分析和测试来找到最佳实践。

五、查看和分析索引使用情况

1. 查看执行计划

使用 explain 命令可以查看 sql 语句的执行计划，这是优化索引最强大的工具。

explain select * from employees where last_name = ‘smith' and age > 30;

重点关注以下几个字段：

• type：访问类型
  •         从好到坏依次是 const、eq_ref、ref、range、index、all。至少要做到 range 级别，最好能达到 ref。
• key：实际使用的索引
• rows：预估需要扫描的行数
• extra：额外信息

        如果出现 using filesort（文件排序）或 using temporary（使用临时表），通常意味着需要优化索引。如果出现 using index，则说明使用了覆盖索引，性能很好。

2. 查看估算成本

使用 explain format=json 可以查看优化器估算的精确成本值。

explain format=json select * from employees where last_name = 'smith' and department_id = 5;

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "10.45"  // ← 这是整个查询的预估总成本！
    },
    "table": {
      "table_name": "employees",
      "access_type": "ref",  // 访问类型
      "possible_keys": ["idx_last_name", "idx_department"],
      "key": "idx_last_name",
      "used_key_parts": ["last_name"],
      "key_length": "62",
      "ref": ["const"],
      "rows_examined_per_scan": 45,  // 预估扫描行数
      "rows_produced_per_join": 5,   // 预估产出行数
      "filtered": "11.11",           // 过滤效率百分比
      "cost_info": {
        "read_cost": "9.95",         // 读取成本
        "eval_cost": "0.50",         // 计算评估成本
        "prefix_cost": "10.45",      // 当前表的总成本
        "data_read_per_join": "1k"   // 读取数据量
      },
      "used_columns": [...]
    }
  }
}

关键成本指标：

• query_cost：最重要的指标，表示优化器预估的查询总成本。这个值越小越好。
• read_cost：从存储引擎读取数据的成本。
• eval_cost：在服务器层处理、计算和比较数据的成本。
• rows_examined_per_scan：预估需要扫描的行数。
• filtered：where 条件过滤后，剩余行数的百分比。

3. 查看实际执行

使用 mysql 8.0 的 explain analyze，它不仅显示优化器的预估，还实际执行查询并返回实际执行的统计信息！

explain analyze select * from employees where last_name = 'smith' and department_id = 5;

输出示例：

-> filter: (employees.department_id = 5)  (cost=10.45 rows=5) (actual time=0.125..0.256 rows=3 loops=1)
    -> index lookup on employees using idx_last_name (last_name='smith')  (cost=10.45 rows=45) (actual time=0.120..0.248 rows=45 loops=1)

示例解读：

• cost=10.45 rows=5：优化器预估的成本和行数
• actual time=0.125..0.256 rows=3：实际执行的结果
• 0.125..0.256：第一行花费0.125ms，所有行花费0.256ms
• rows=3：实际返回3行

这是最可靠的性能分析工具，因为它对比了优化器的预估和实际执行情况。

六、访问类型详解

explain 命令输出中的 type 列是非常重要的，它告诉我们 mysql 是如何在表中查找行的。以下是从最优到最差排序的各个访问类型的详细解释。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

在实际优化中，我们最常关注的是 const, eq_ref, ref, range, index, all。

1.system

含义：这是 const 类型的一个特例。表示表中只有一行数据（等于系统表）。这是性能最好的情况。

触发场景：通常是查询系统表，或者使用 myisam 存储引擎且只有一条记录的表。

示例：

-- 假设只有一条记录的系统配置表
explain select * from system_config where id = 1;

2.const

• 含义：通过主键（primary key） 或唯一索引（unique index） 进行等值查询时，最多只返回一条记录。
• 说明：因为索引是唯一的，所以查询速度极快，性能最佳。mysql 将查询转换为一个常量来优化。
• 示例：

explain select * from users where id = 10; -- id 是主键
explain select * from users where email = 'admin@example.com'; -- email 有唯一索引

3.eq_ref

含义：在表连接时使用，通常出现在被驱动表的连接条件上。它使用主键或唯一索引作为关联条件，对于驱动表的每一行，在被驱动表中只能找到唯一的一行与之对应。

说明：这是除了 system 和 const 之外最好的连接类型。

示例：

explain select *
from orders
join customers on orders.customer_id = customers.id; -- customers.id 是主键

在这个例子中，对于 orders 表中的每一个 customer_id，到 customers 表中通过主键 id 查找，每次查找都只返回一条记录。

4.ref

• 含义：使用非唯一性索引进行等值查询，或者连接查询使用了非唯一索引的列。
• 说明：这是一种非常常见的、性能良好的访问类型。它可能返回0行、1行或多行记录。
• 示例：

-- 假设 last_name 字段有一个普通索引
explain select * from employees where last_name = 'smith';
-- 表连接中使用非唯一索引
explain select *
from orders o
join products p on o.product_sku = p.sku; -- p.sku 是一个普通索引（非唯一）

5.ref_or_null

• 含义：类似于 ref，但 mysql 会额外搜索包含 null 值的行。这种类型通常发生在对具有索引的列进行等值比较并包括 is null 条件时。
• 示例：

explain select * from users where phone_number = '123456' or phone_number is null;
-- 假设 phone_number 上有索引

6.range

• 含义：使用索引来检索给定范围的行。通常出现在 in、between、>、<、>=、<= 等操作中。
• 说明：这时 key_len 列会显示索引中使用的字节数，表示只使用了索引的一部分。
• 示例：

explain select * from employees where age between 25 and 35; -- age 有索引
explain select * from users where id in (1, 5, 10); -- id 是主键
explain select * from products where price > 50; -- price 有索引

7.index

• 含义：全索引扫描。它遍历整个索引树来查找数据，与全表扫描 all 类似，但因为它只扫描索引（通常比数据文件小），所以比 all 快。
• 触发场景：
  • 查询的列都包含在某个索引中（即覆盖索引），但需要扫描整个索引。
  • 使用 order by 主键的查询。
• 示例：

-- 假设 (status, created_at) 有一个复合索引
explain select status, created_at from articles; -- 覆盖索引，但无 where 条件
explain select id from users order by id; -- 按主键排序

8.all

• 含义：全表扫描。mysql 会读取表中的每一行来找到匹配的行。
• 说明：这是性能最差的访问类型，对于大表来说是灾难性的。优化目标就是通过创建合适的索引来避免出现 all。
• 触发场景：没有索引可用于查询。
• 示例：

explain select * from products where name = 'laptop'; -- name 列上没有索引

9. 其他较少见的类型

• index_merge：表示查询使用了索引合并优化。mysql 会使用多个索引，然后将各自的结果进行合并（取交集、并集等）。
• unique_subquery / index_subquery：这两种类型都与 in 子查询相关，unique_subquery 用于唯一索引的子查询，效率更高。

访问类型总结：

核心建议：
在优化时，我们的目标是让 type 至少达到 range 级别，最好能达到 ref。一旦看到 all，就应该立即检查是否可以为相关列创建有效的索引。

七、实际应用：成本对比分析

让我们通过一个实际例子来展示如何用成本分析来比较不同索引的效果：

-- 情况1：无合适索引
explain format=json select * from orders where order_date between '2023-01-01' and '2023-12-31' and customer_id = 100;
-- 结果：query_cost = "1250.25", type = "all"
-- 添加索引后
alter table orders add index idx_customer_date (customer_id, order_date);
-- 情况2：使用新索引
explain format=json select * from orders where order_date between '2023-01-01' and '2023-12-31' and customer_id = 100;
-- 结果：query_cost = "8.75", type = "range"

结论：通过成本对比，我们可以量化索引带来的性能提升：从 1250.25 降到 8.75，性能提升了 140多倍！

成本评估总结：

• 日常快速检查：使用 explain 看 type 和 rows
• 深度优化分析：使用 explain format=json 查看详细的 query_cost
• 最准确验证：在 mysql 8.0+ 中使用 explain analyze 对比预估和实际性能
• 长期监控：使用 performance schema 监控查询的历史性能
• 优化目标：通过创建合适的索引，观察 query_cost 值的下降来验证优化效果

记住：成本值本身没有绝对的好坏标准，重要的是通过对比不同查询方案或索引设计的成本值，来选择最优的执行计划。通常成本值降低 10倍以上就说明优化非常有效。

到此这篇关于mysql数据库索引全面介绍的文章就介绍到这了,更多相关mysql数据库索引内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！