Mariadb如何创建索引

在 MariaDB 中创建索引是优化查询性能的关键操作。以下是创建索引的详细方法和注意事项：

一、索引创建基础语法

1. 创建表时定义索引

CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    ...
    INDEX 索引名 (列名),               -- 普通索引
    UNIQUE INDEX 唯一索引名 (列名),     -- 唯一索引
    FULLTEXT INDEX 全文索引名 (列名)    -- 全文索引（仅适用 TEXT 类型）
);

2. 为已有表添加索引

-- 普通索引
CREATE INDEX 索引名 ON 表名 (列名);

-- 唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

-- 全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

3. 多列组合索引（复合索引）

二、常见索引类型及适用场景

索引类型	特点	示例场景
普通索引	允许重复值和NULL值	常用于WHERE条件或JOIN的列
唯一索引	列值必须唯一，允许NULL（但只能一个NULL）	用户名、邮箱等唯一性约束
全文索引	优化文本字段的模糊搜索	文章内容搜索（LIKE ‘%keyword%’ 优化）
组合索引	多列联合索引，遵循最左前缀原则	WHERE条件同时涉及多列时

三、操作示例

1. 创建用户表并定义索引

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    bio TEXT,
    created_at DATETIME,
    INDEX idx_username (username),          -- 普通索引
    UNIQUE INDEX uq_email (email),          -- 唯一索引
    FULLTEXT INDEX ft_bio (bio)             -- 全文索引
);

2. 为已有订单表添加组合索引

-- 假设已有 orders 表，包含 user_id 和 status 列
CREATE INDEX idx_user_status ON orders (user_id, status);

四、索引管理命令

1. 查看表索引

SHOW INDEX FROM 表名;

2. 删除索引

DROP INDEX 索引名 ON 表名;

3. 修改表结构添加索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

五、最佳实践建议

选择性高的列优先
选择区分度高的列（如唯一值多的列）创建索引，例如用户ID比性别字段更适合。
避免过度索引
每个索引会增加写操作（INSERT/UPDATE/DELETE）的开销，通常单表索引不超过5个。
组合索引顺序
按查询条件频率排序，高频条件列放在左侧。例如：
```
-- 假设查询多为 WHERE a=1 AND b=2
CREATE INDEX idx_a_b ON table1 (a, b);
```
全文索引限制
- 仅适用于 MyISAM 和 Aria 存储引擎（InnoDB 从 MariaDB 10.0+ 支持）。
- 需用 MATCH() AGAINST() 语法触发索引：
```
SELECT * FROM articles WHERE MATCH(content) AGAINST('database');
```
监控索引使用率
通过慢查询日志或 EXPLAIN 分析是否有效利用索引：
```
EXPLAIN SELECT * FROM users WHERE username = 'john';
```

六、性能测试示例

未使用索引的查询

SELECT * FROM orders WHERE status = 'shipped'; -- 全表扫描，耗时 120ms

添加索引后

CREATE INDEX idx_status ON orders (status);
SELECT * FROM orders WHERE status = 'shipped'; -- 索引扫描，耗时 5ms

总结

合理使用索引可显著提升 MariaDB 查询性能，但需平衡读写效率。建议结合业务查询模式设计索引，并通过 EXPLAIN 验证执行计划。定期使用 ANALYZE TABLE 表名; 更新索引统计信息，确保优化器选择最优路径。