聯合索引和多個單列索引使用中的索引命中情況及索引建立原則

happywho250發表於2022-10-10
索引

聯合索引和多個單列索引使用中的索引命中情況

mysql Ver 8.0.28

聯合索引

建立表

CREATE TABLE `multi_index_test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `a` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `b` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `c` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

新增聯合索引

ALTER TABLE `multi_index_test` ADD index index_a_b_c(`a`, `b`, `c`);

sql命中情況

SELECT * FROM `multi_index_test` WHERE a='xxx' and b='xxx' and c='xxx'; 命中
SELECT * FROM `multi_index_test` WHERE a='xxx' and b='xxx'  命中
SELECT * FROM `multi_index_test` WHERE a='xxx' and c='xxx'  命中
SELECT * FROM `multi_index_test` WHERE c='xxx' and b='xxx' and a='xxx';  命中
SELECT * FROM `multi_index_test` WHERE a='xxx'; 命中

---
SELECT * FROM `multi_index_test` WHERE a='xxx' or b='xxx' or c='xxx'; 未命中
SELECT * FROM `multi_index_test` WHERE  b='xxx' and c='xxx';未命中
SELECT * FROM `multi_index_test` WHERE  b='xxx'; 未命中
SELECT * FROM `multi_index_test` WHERE  c='xxx'; 未命中
  • 建立聯合索引時一定要注意建立的列順序 對索引中的所有列或者前幾列執行搜尋時 多列索引非常有用

多個單列索引

建立表

CREATE TABLE `single_index_test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `a` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `b` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `c` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

建立多個單獨的索引

ALTER TABLE `single_index_test` ADD index index_a(a);
ALTER TABLE `single_index_test` ADD index index_b(b);
ALTER TABLE `single_index_test` ADD index index_c(c);

*索引命中情況

SELECT * FROM `single_index_test` WHERE a='xxx';命中index_a
SELECT * FROM `single_index_test` WHERE b='xxx';命中index_b
SELECT * FROM `single_index_test` WHERE c='xxx';命中index_c
SELECT * FROM `single_index_test` WHERE a='xxx' and b='xxx' and c='xxx';只命中index_a 
SELECT * FROM `single_index_test` WHERE a='xxx' and b='xxx'; 只命中index_a
SELECT * FROM `single_index_test` WHERE b='xxx' and a='xxx'; 只命中index_a
SELECT * FROM `single_index_test` WHERE b='xxx' and c='xxx'; 只命中index_b
SELECT * FROM `single_index_test` WHERE c='xxx' and b='xxx'; 只命中index_b

SELECT * FROM `single_index_test` WHERE a='xxx' or b='xxx'; 未命中
  • 多個單列索引都有效的時候 mysql的最佳化策略選取了它認為最高效的一個index，而不會都使用

總結

  1. 建立聯合索引時一定要注意建立的列順序 對索引中的所有列或者前幾列執行搜尋時 多列索引非常有用
  2. 多個單列索引都有效的時候 mysql的最佳化策略選取了它認為最高效的一個index，而不會都使用

構建索引的原則

  • 使用唯一索引
    索引的基數越大，索引的效果越好。

  • 使用短索引

    儘量選擇區分度高的列去建立索引
如果對字串列進行索引，儘可能的指定一個字首長度，例如一個char(200)的列，前面10個或者20個字元進行索引能夠進可能的減少索引的空間，也能使查詢更快，較小的索引涉及的磁碟IO也較小，較短的值比較起來更快。

  • 利用最左索引

    建立一個N列索引的時候，實際上建立的是一個mysql可利用的n個索引，多列索引可以起到幾個索引的作用，可以利用索引中最左邊的列集來匹配行，這樣的列集稱為最左索引。

  • 不要過度的使用索引

    儘量去根據需求修改索引 而不是去新增索引
每個額外的索引都會佔用額外的空間，降低寫操作效能，在修改表的內容的時候，表的索引也會更新，索引越多，所花時間越長，索引太多，也會使mysql選擇不到索要使用的最好索引，只保持所需要的索引有利於查詢最佳化。

  • 對於innDB儲存引擎的表

    預設記錄會按照一定的順序儲存，如果有明確定義的主鍵，則按照主鍵順序儲存，如果沒有主鍵，但是有唯一索引，會按照唯一索引的順序儲存，如果即沒有主鍵，也沒有唯一索引，那麼表中會自動生成一個內部列（按照主鍵和內部列進行訪問是最快的），inndb表的普通索引都會儲存主鍵的鍵值，所以主鍵要儘可能的選擇較短的資料型別，可以有效的減少索引的磁碟佔用，提高索引的快取效果。
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章