當前位置: 首頁>>AGI專欄>>正文


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. 多列組合索引(複合索引)

CREATE INDEX 索引名 ON 表名 (1,2, ...);

二、常見索引類型及適用場景

索引類型 特點 示例場景
普通索引 允許重複值和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 索引名 (列名);

五、最佳實踐建議

  1. 選擇性高的列優先
    選擇區分度高的列(如唯一值多的列)創建索引,例如用戶ID比性別字段更適合。
  2. 避免過度索引
    每個索引會增加寫操作(INSERT/UPDATE/DELETE)的開銷,通常單表索引不超過5個。
  3. 組合索引順序
    按查詢條件頻率排序,高頻條件列放在左側。例如:

    -- 假設查詢多為 WHERE a=1 AND b=2
    CREATE INDEX idx_a_b ON table1 (a, b);
  4. 全文索引限製
    • 僅適用於 MyISAM 和 Aria 存儲引擎(InnoDB 從 MariaDB 10.0+ 支持)。
    • 需用 MATCH() AGAINST() 語法觸發索引:
      SELECT * FROM articles WHERE MATCH(content) AGAINST('database');
  5. 監控索引使用率
    通過慢查詢日誌或 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 表名; 更新索引統計信息,確保優化器選擇最優路徑。

本文由《純淨天空》出品。文章地址: https://vimsky.com/zh-tw/article/4846.html,轉載請注明來源鏈接。