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