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 表名; 更新索引統計信息，確保優化器選擇最優路徑。