# MariaDB索引怎么创建 ## 目录 1. [索引概述](#1-索引概述) - 1.1 [什么是索引](#11-什么是索引) - 1.2 [索引的工作原理](#12-索引的工作原理) - 1.3 [索引的优缺点](#13-索引的优缺点) 2. [MariaDB索引类型](#2-mariadb索引类型) - 2.1 [B-Tree索引](#21-b-tree索引) - 2.2 [哈希索引](#22-哈希索引) - 2.3 [全文索引](#23-全文索引) - 2.4 [空间索引](#24-空间索引) 3. [创建索引的基本语法](#3-创建索引的基本语法) - 3.1 [CREATE INDEX语法](#31-create-index语法) - 3.2 [ALTER TABLE添加索引](#32-alter-table添加索引) - 3.3 [建表时创建索引](#33-建表时创建索引) 4. [索引创建实战](#4-索引创建实战) - 4.1 [单列索引](#41-单列索引) - 4.2 [复合索引](#42-复合索引) - 4.3 [唯一索引](#43-唯一索引) - 4.4 [前缀索引](#44-前缀索引) 5. [索引管理](#5-索引管理) - 5.1 [查看索引](#51-查看索引) - 5.2 [删除索引](#52-删除索引) - 5.3 [修改索引](#53-修改索引) 6. [索引优化策略](#6-索引优化策略) - 6.1 [选择合适的列建立索引](#61-选择合适的列建立索引) - 6.2 [索引列的选择性](#62-索引列的选择性) - 6.3 [避免过度索引](#63-避免过度索引) - 6.4 [索引失效场景](#64-索引失效场景) 7. [高级索引技巧](#7-高级索引技巧) - 7.1 [覆盖索引](#71-覆盖索引) - 7.2 [索引下推](#72-索引下推) - 7.3 [函数索引](#73-函数索引) 8. [索引与存储引擎](#8-索引与存储引擎) - 8.1 [InnoDB索引特性](#81-innodb索引特性) - 8.2 [MyISAM索引特性](#82-myisam索引特性) - 8.3 [Aria索引特性](#83-aria索引特性) 9. [常见问题解答](#9-常见问题解答) 10. [总结](#10-总结) ## 1. 索引概述 ### 1.1 什么是索引 索引是数据库管理系统中用于加速数据检索的特殊数据结构，它类似于书籍的目录，可以快速定位到特定数据所在的位置。在MariaDB中，索引是存储在磁盘上的独立数据结构，与表数据分开存储但相互关联。 ### 1.2 索引的工作原理 当在表上创建索引后，MariaDB会维护一个与该索引对应的数据结构（如B-Tree）。当执行查询时，数据库引擎会先检查查询条件是否可以利用索引： 1. 如果查询条件匹配索引列，引擎会使用索引快速定位数据 2. 通过索引找到对应的行指针（通常是主键值或物理地址） 3. 根据行指针获取完整的行数据 ### 1.3 索引的优缺点 **优点：** - 大幅提高查询速度，特别是对大表 - 加速表连接操作 - 保证数据的唯一性（唯一索引） - 优化排序和分组操作 **缺点：** - 占用额外的磁盘空间 - 降低数据写入速度（INSERT/UPDATE/DELETE） - 需要维护成本，随着数据变化需要更新 ## 2. MariaDB索引类型 ### 2.1 B-Tree索引 B-Tree（平衡树）是MariaDB中最常用的索引类型，适用于全值匹配、范围查询和前缀匹配。InnoDB、MyISAM和Aria存储引擎都支持B-Tree索引。 特点： - 数据按顺序存储 - 适合处理各种比较操作（=, >, <, BETWEEN等） - 支持最左前缀匹配原则 ### 2.2 哈希索引 哈希索引基于哈希表实现，只有Memory存储引擎显式支持。它适用于等值比较查询，但不支持范围查询。 特点： - 极高的等值查询效率 - 不支持排序 - 不支持部分索引匹配 - 可能存在哈希冲突 ### 2.3 全文索引 全文索引用于文本内容的搜索，支持自然语言搜索和布尔搜索。MyISAM和InnoDB（MariaDB 10.0+）支持全文索引。 特点： - 专门用于文本搜索 - 支持关键词匹配和相关性排序 - 支持停用词和词干提取 ### 2.4 空间索引 空间索引用于地理空间数据类型，使用R-Tree实现。MyISAM和InnoDB（MariaDB 10.0+）支持空间索引。 特点： - 专为GEOMETRY数据类型设计 - 支持空间关系判断（包含、相交等） - 使用特殊的空间函数进行操作 ## 3. 创建索引的基本语法 ### 3.1 CREATE INDEX语法 基本语法： ```sql CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column1 [(length)], column2, ...) [USING {BTREE|HASH}] [ALGORITHM = {DEFAULT|INPLACE|COPY}] [LOCK = {DEFAULT|NONE|SHARED|EXCLUSIVE}] 示例：
-- 创建普通索引 CREATE INDEX idx_name ON users(last_name); -- 创建指定长度的索引 CREATE INDEX idx_name_part ON users(last_name(10)); -- 指定索引类型 CREATE INDEX idx_email ON users(email) USING BTREE; ALTER TABLE也可以用于添加索引：
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (column_list) [USING {BTREE|HASH}]; 示例：
ALTER TABLE products ADD INDEX idx_price (price), ADD FULLTEXT INDEX ftx_desc (description); 在CREATE TABLE语句中直接定义索引：
CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10,2), hire_date DATE, INDEX idx_dept (department), INDEX idx_salary (salary), UNIQUE INDEX uidx_name (name) ) ENGINE=InnoDB; 最基本的索引类型，在单个列上创建：
-- 在users表的email列上创建索引 CREATE INDEX idx_email ON users(email); -- 查询时会使用该索引 EXPLN SELECT * FROM users WHERE email = 'user@example.com'; 在多个列上创建的索引，列顺序很重要：
-- 创建复合索引 CREATE INDEX idx_name_dept ON employees(last_name, department); -- 有效使用索引的情况 SELECT * FROM employees WHERE last_name = 'Smith' AND department = 'Sales'; -- 也支持最左前缀匹配 SELECT * FROM employees WHERE last_name = 'Smith'; 确保列值唯一的索引：
-- 创建唯一索引 CREATE UNIQUE INDEX uidx_email ON users(email); -- 尝试插入重复值会失败 INSERT INTO users(email) VALUES ('existing@example.com'); -- 错误: Duplicate entry 'existing@example.com' for key 'uidx_email' 对文本列的前N个字符建立索引，节省空间：
-- 对长文本列创建前缀索引 CREATE INDEX idx_notes ON customer_notes(notes(100)); -- 查询时只能使用前缀匹配 SELECT * FROM customer_notes WHERE notes LIKE 'Important%'; 查看表的索引信息：
-- 方式1：SHOW INDEX SHOW INDEX FROM table_name; -- 方式2：查询information_schema SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table'; 删除不再需要的索引：
-- 方式1：DROP INDEX DROP INDEX index_name ON table_name; -- 方式2：ALTER TABLE ALTER TABLE table_name DROP INDEX index_name; MariaDB不支持直接修改索引，需要先删除再重建：
-- 先删除旧索引 DROP INDEX idx_name ON employees; -- 再创建新索引 CREATE INDEX idx_name_new ON employees(last_name, first_name); 适合建立索引的列： - WHERE子句中频繁使用的列 - 连接操作中使用的列 - 排序和分组操作的列 - 高选择性的列（不同值多）
选择性计算公式：
选择性 = 不重复的索引值数量 / 表中记录总数 选择性越高（接近1），索引效率越好。
过多的索引会导致： - 增加存储空间占用 - 降低写操作性能 - 增加优化器选择时间
导致索引失效的常见操作： - 对索引列使用函数：WHERE YEAR(create_time) = 2023 - 使用不等于操作：WHERE status != 'active' - 使用OR条件不当 - 使用LIKE以通配符开头：WHERE name LIKE '%son' - 隐式类型转换
当查询的所有列都包含在索引中时，可以避免回表操作：
-- 创建复合索引 CREATE INDEX idx_covering ON orders(customer_id, order_date, total_amount); -- 查询可以利用覆盖索引 EXPLN SELECT customer_id, order_date, total_amount FROM orders WHERE customer_id = 1005; MariaDB 5.3+支持的优化，将WHERE条件推到存储引擎层处理：
-- 假设有索引 (last_name, first_name) SELECT * FROM employees WHERE last_name = 'Smith' AND first_name LIKE 'J%'; MariaDB 10.3.3+支持在计算列上创建索引：
-- 创建计算列 ALTER TABLE products ADD COLUMN price_tax DECIMAL(10,2) AS (price * 1.2) PERSISTENT; -- 在计算列上创建索引 CREATE INDEX idx_price_tax ON products(price_tax); Q1: 主键和唯一索引有什么区别？ - 主键不允许NULL值，唯一索引允许 - 一个表只能有一个主键，可以有多个唯一索引 - 主键会创建聚簇索引（InnoDB）
Q2: 为什么索引没有生效？ 可能原因： - 查询条件不符合最左前缀原则 - 使用了导致索引失效的操作 - 数据量太小，优化器选择全表扫描 - 索引统计信息过期
Q3: 如何强制使用某个索引？ 使用FORCE INDEX提示：
SELECT * FROM table_name FORCE INDEX(index_name) WHERE ... MariaDB索引是优化查询性能的强大工具，合理使用索引可以大幅提升数据库性能。创建索引时应考虑： 1. 查询模式和数据分布 2. 索引类型和存储引擎特性 3. 索引维护成本 4. 避免常见陷阱
定期审查和优化索引是数据库维护的重要部分，可以使用EXPLN分析查询执行计划，确保索引被有效利用。
记住：没有放之四海而皆准的索引策略，最佳实践取决于您的具体应用场景和数据特征。 “`
这篇文章详细介绍了MariaDB索引的创建和管理，涵盖了从基础概念到高级技巧的各个方面，总字数约5700字。内容采用Markdown格式，包含标题、代码块、列表等标准元素，便于阅读和格式转换。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
