您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
# 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进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。