MariaDB 索引学习总结

索引概述

索引是数据库中用于加速查询操作的数据结构，类似于书籍的目录。MariaDB支持多种索引类型，合理使用索引可以显著提高查询性能。

索引类型

1. 普通索引 (INDEX)

最基本的索引类型，没有唯一性限制。

CREATE INDEX idx_name ON table_name(column_name);

2. 唯一索引 (UNIQUE INDEX)

确保索引列的值唯一，允许NULL值。

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

3. 主键索引 (PRIMARY KEY)

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

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

4. 全文索引 (FULLTEXT)

用于全文搜索，适用于MyISAM和InnoDB存储引擎。

CREATE FULLTEXT INDEX idx_name ON table_name(column_name);

5. 组合索引 (复合索引)

在多个列上创建的索引。

CREATE INDEX idx_name ON table_name(col1, col2, col3);

索引操作

创建索引

-- 创建表时添加索引
CREATE TABLE table_name (
    id INT NOT NULL,
    name VARCHAR(30) NOT NULL,
    INDEX idx_name (name)
);

-- 在已有表上添加索引
CREATE INDEX idx_name ON table_name(column_name);
ALTER TABLE table_name ADD INDEX idx_name (column_name);

删除索引

DROP INDEX idx_name ON table_name;
ALTER TABLE table_name DROP INDEX idx_name;

查看索引

SHOW INDEX FROM table_name;

索引使用最佳实践

1. 选择合适的列建立索引：

   • WHERE子句中频繁使用的列
   • JOIN操作中使用的列
   • 排序(ORDER BY)和分组(GROUP BY)操作的列

2. 组合索引的列顺序：

   • 遵循"最左前缀"原则
   • 将选择性高的列放在前面

3. 避免过度索引：

   • 索引会占用存储空间
   • 增删改操作需要维护索引，影响性能

4. 避免在索引列上使用函数：

   -- 不推荐，无法使用索引
   SELECT * FROM table WHERE YEAR(date_column) = 2023;
   
   -- 推荐
   SELECT * FROM table WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';
   

5. 使用EXPLAIN分析查询：

   EXPLAIN SELECT * FROM table WHERE indexed_column = 'value';
   

索引限制与注意事项

1. 索引不是越多越好，每个额外的索引都会增加维护成本
2. 小表通常不需要索引
3. 数据重复率高、区分度低的列不适合建索引
4. 频繁更新的列建立索引需谨慎
5. 对于TEXT/BLOB类型的大字段，考虑使用前缀索引

性能优化示例

-- 创建组合索引
CREATE INDEX idx_name_age ON employees(last_name, age);

-- 使用索引的查询
SELECT * FROM employees WHERE last_name = 'Smith' AND age = 30;

-- 使用EXPLAIN分析
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

通过合理设计和使用索引，可以显著提高MariaDB数据库的查询性能，但需要根据实际业务场景和数据特点进行权衡和优化。