印刻数据库

外观

MariaDB 索引 ​

索引的基本概念 ​

索引是数据库中用于提高查询性能的数据结构，通过创建索引可以大幅减少数据库服务器需要扫描的数据量，从而加快查询速度。索引的本质是对表中一列或多列的值进行排序的一种结构。

索引的作用 ​

  • 加速查询：减少查询时需要扫描的数据行数
  • 加速排序：利用索引的有序性，避免额外的排序操作
  • 加速连接：在多表连接时，通过索引可以快速定位匹配的行
  • 保证数据唯一性：通过唯一索引可以确保列值的唯一性

索引的代价 ​

  • 存储空间：索引需要额外的存储空间
  • 写入开销：插入、更新、删除操作时需要维护索引
  • 查询优化器开销：查询优化器需要评估使用索引的成本

索引类型 ​

B-tree 索引 ​

B-tree 是 MariaDB 中最常用的索引类型，适用于范围查询、排序和分组操作。

特点：

  • 支持等值查询和范围查询
  • 支持排序操作
  • 支持前缀索引
  • 适用于大多数数据类型

创建示例：

sql
-- 单列索引
CREATE INDEX idx_user_name ON users(name);

-- 多列索引（复合索引）
CREATE INDEX idx_user_name_age ON users(name, age);

-- 唯一索引
CREATE UNIQUE INDEX idx_user_email ON users(email);

Hash 索引 ​

Hash 索引基于哈希表实现，适用于等值查询，但不支持范围查询和排序。

特点：

  • 等值查询速度极快
  • 不支持范围查询和排序
  • 不支持前缀索引
  • 仅适用于 Memory 存储引擎

创建示例：

sql
CREATE INDEX idx_hash_id ON users(id) USING HASH;

全文索引 ​

全文索引用于全文搜索，适用于 VARCHAR、TEXT 等文本类型的列。

特点：

  • 支持自然语言搜索和布尔搜索
  • 适用于大文本字段的搜索
  • 支持中文等多种语言

创建示例：

sql
-- 创建全文索引
CREATE FULLTEXT INDEX idx_article_content ON articles(content);

-- 使用全文搜索
SELECT * FROM articles WHERE MATCH(content) AGAINST('MariaDB 索引' IN NATURAL LANGUAGE MODE);

空间索引 ​

空间索引用于地理空间数据类型，如 POINT、LINESTRING、POLYGON 等。

特点：

  • 支持地理空间查询
  • 适用于地理信息系统（GIS）应用
  • 基于 R-tree 数据结构

创建示例：

sql
-- 创建空间索引
CREATE SPATIAL INDEX idx_location ON places(location);

-- 使用空间查询
SELECT * FROM places WHERE ST_Contains(area, POINT(116.397428, 39.90923));

前缀索引 ​

前缀索引是对列值的前缀部分创建索引，可以减少索引的存储空间。

特点：

  • 适用于长字符串列
  • 节省索引存储空间
  • 提高索引扫描速度

创建示例：

sql
-- 对 email 列的前 10 个字符创建索引
CREATE INDEX idx_user_email_prefix ON users(email(10));

索引设计原则 ​

1. 选择合适的列创建索引 ​

  • 高选择性列：选择区分度高的列创建索引，如主键、唯一键
  • 频繁查询的列：在 WHERE 子句、JOIN 条件中频繁使用的列
  • 排序和分组的列：在 ORDER BY、GROUP BY、DISTINCT 子句中使用的列
  • 外键列：外键列通常用于连接查询，创建索引可以加速连接操作

2. 复合索引的设计 ​

  • 最左前缀原则：复合索引的查询效率取决于查询条件是否匹配索引的最左前缀
  • 选择性高的列放在前面：将区分度高的列放在复合索引的前面
  • 考虑查询模式：根据实际查询场景设计复合索引

示例：

sql
-- 对于查询 SELECT * FROM orders WHERE status = 'paid' AND created_at > '2023-01-01'
-- 优化的复合索引设计
CREATE INDEX idx_orders_status_created_at ON orders(status, created_at);

3. 避免过度索引 ​

  • 每个表的索引数量不宜过多（建议不超过 5-10 个）
  • 避免在频繁更新的列上创建过多索引
  • 避免创建重复索引

4. 索引列的选择 ​

  • 避免在索引列上使用函数：会导致索引失效
  • 避免在索引列上进行计算：会导致索引失效
  • 避免在索引列上使用类型转换：会导致索引失效

反例：

sql
-- 索引失效的查询
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 优化后的查询
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

索引操作 ​

创建索引 ​

语法：

sql
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (column1 [(length)] [ASC | DESC], column2 [(length)] [ASC | DESC], ...);

示例：

sql
-- 创建单列索引
CREATE INDEX idx_user_name ON users(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_order_user_product ON orders(user_id, product_id, created_at);

-- 创建前缀索引
CREATE INDEX idx_user_phone_prefix ON users(phone(11));

查看索引 ​

查看表的所有索引：

sql
SHOW INDEXES FROM users;
-- 或
SHOW KEYS FROM users;

查看索引详情：

sql
SHOW CREATE TABLE users;

删除索引 ​

语法：

sql
DROP INDEX index_name ON table_name;

示例：

sql
DROP INDEX idx_user_name ON users;

索引维护 ​

索引碎片整理 ​

随着数据的插入、更新和删除，索引会产生碎片，影响查询性能。定期整理索引碎片可以提高查询效率。

使用 OPTIMIZE TABLE 命令：

sql
OPTIMIZE TABLE users;

注意事项：

  • OPTIMIZE TABLE 会锁定表，建议在低峰期执行
  • 对于 InnoDB 表，OPTIMIZE TABLE 会重建表和索引
  • 对于 MyISAM 表，OPTIMIZE TABLE 会整理数据文件并重建索引

索引使用情况分析 ​

查看索引使用统计：

sql
-- 启用索引使用统计
SET GLOBAL userstat = 1;

-- 查看索引使用情况
SELECT * FROM information_schema.index_statistics WHERE table_schema = 'your_database' AND table_name = 'your_table';

分析查询执行计划：

sql
EXPLAIN SELECT * FROM users WHERE name = 'test';

识别无效索引 ​

使用 pt-index-usage 工具：

bash
pt-index-usage --user=root --password=password /var/lib/mysql/slow-query.log

手动识别无效索引：

  • 长时间未使用的索引
  • 重复索引（与其他索引的前缀相同）
  • 选择性低的索引

索引最佳实践 ​

1. 优先使用覆盖索引 ​

覆盖索引是指查询所需的所有列都包含在索引中，这样可以避免回表操作，提高查询效率。

示例：

sql
-- 复合索引包含查询所需的所有列
CREATE INDEX idx_user_name_age ON users(name, age);

-- 查询可以完全使用索引
SELECT name, age FROM users WHERE name = 'test';

2. 合理使用唯一索引 ​

唯一索引可以确保数据的完整性，同时提高查询效率。

适用场景：

  • 主键列
  • 唯一标识符列（如 email、phone 等）
  • 业务上需要唯一的列

3. 考虑数据分布 ​

在创建索引前，了解列的数据分布情况，选择选择性高的列创建索引。

查看列的选择性：

sql
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;

4. 定期监控和优化索引 ​

  • 定期分析查询日志，识别慢查询
  • 使用 EXPLAIN 分析查询执行计划
  • 定期检查索引使用情况
  • 根据业务变化调整索引策略

版本差异 ​

MariaDB 10.0+ ​

  • 引入了虚拟列索引，可以对表达式创建索引
  • 增强了全文索引的功能，支持更多语言

MariaDB 10.1+ ​

  • 引入了直方图统计，提高查询优化器的准确性
  • 支持索引合并优化，可以同时使用多个索引

MariaDB 10.2+ ​

  • 支持降序索引，直接存储倒序的数据
  • 增强了空间索引的功能

MariaDB 10.3+ ​

  • 引入了动态列索引
  • 支持不可见索引，可以在不删除索引的情况下测试索引的效果

常见问题（FAQ） ​

1. 为什么创建了索引，但查询没有使用？ ​

可能原因：

  • 查询条件没有匹配索引的最左前缀
  • 查询条件在索引列上使用了函数或计算
  • 索引的选择性太低，全表扫描效率更高
  • 查询优化器认为全表扫描更快
  • 统计信息不准确，导致查询优化器做出错误判断

解决方案：

  • 检查查询条件是否符合索引的最左前缀原则
  • 避免在索引列上使用函数或计算
  • 重新分析表，更新统计信息
  • 考虑使用强制索引（FORCE INDEX）

2. 如何选择复合索引的顺序？ ​

建议：

  • 将选择性高的列放在前面
  • 将频繁用于等值查询的列放在前面
  • 考虑查询的排序和分组需求
  • 遵循最左前缀原则

3. 什么时候应该删除索引？ ​

适用场景：

  • 索引长时间未被使用
  • 索引的选择性太低
  • 存在重复索引
  • 写入操作远多于查询操作
  • 索引占用过多存储空间

4. 前缀索引的长度如何选择？ ​

建议：

  • 对于字符串列，选择足够长的前缀，确保选择性足够高
  • 可以通过计算不同前缀长度的选择性来确定最佳长度
  • 一般来说，前缀长度应该覆盖列值的大部分唯一性

示例：

sql
-- 计算不同前缀长度的选择性
SELECT
  COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel_15
FROM users;

5. 如何优化 LIKE 查询？ ​

建议：

  • 对于前缀匹配（如 name LIKE 'test%'），可以使用索引
  • 对于后缀匹配（如 name LIKE '%test'），无法使用索引
  • 对于中间匹配（如 name LIKE '%test%'），无法使用索引
  • 考虑使用全文索引替代 LIKE 查询

6. 索引越多越好吗？ ​

不是。索引越多，写入操作的开销越大，因为每次插入、更新或删除操作都需要维护所有相关索引。同时，查询优化器需要评估更多的索引组合，增加查询优化的时间。

建议：

  • 每个表的索引数量不宜超过 5-10 个
  • 只创建必要的索引
  • 定期清理无效索引

总结 ​

索引是提高 MariaDB 查询性能的重要手段，但需要合理设计和维护。在创建索引时，需要考虑查询模式、数据分布和业务需求，遵循索引设计原则。定期监控和优化索引，可以确保索引始终处于最佳状态，提高数据库的整体性能。

通过本文的介绍，相信您对 MariaDB 索引有了更深入的了解，能够在实际生产环境中合理设计和使用索引，提高数据库的查询效率和性能。