Mysql 索引該如何設計與優化

Posted on by

什麼是索引？

數據庫索引是一種數據結構，它以額外的寫入和存儲空間為代價來提高數據庫表上數據檢索操作的速度。通俗來說，索引類似於書的目錄，根據其中記錄的頁碼可以快速找到所需的內容。——維基百科
常見索引有哪些？

  • 普通索引：最基本的索引，沒有任何限制
  • 唯一索引：與”普通索引“類似，不同的就是：索引列的值必須是唯一，但允許有空值
  • 主鍵索引：它是一種特殊的索引，不允許有空值
  • 全文索引：僅可用於 MyISAM 表，針對較大的數據，生成全文索引很耗時占空間
  • 組合索引：為瞭提高多條件查詢效率，可建立組合索引，遵循”最左前綴匹配原則”

這裡以相對復雜的組合為例，介紹如何優化。

最左前綴匹配原則

首先我們要知道什麼是最左前綴匹配原則。

最左前綴匹配原則是指在使用 B+Tree 聯合索引進行數據檢索時，MySQL 優化器會讀取謂詞（過濾條件）並按照聯合索引字段創建順序一直向右匹配直到遇到范圍查詢或非等值查詢後停止匹配，此字段之後的索引列不會被使用，這時計算 key_len 可以分析出聯合索引實際使用瞭哪些索引列。

如何計算 key_len

通過 key_len 計算也幫助我們瞭解索引的最左前綴匹配原則。

key_len 表示得到結果集所使用的選擇索引的長度[字節數]，不包括 order by，也就是說如果 order by 也使用瞭索引則 key_len 不計算在內。

在計算 key_len 之前，先來溫習一下基本數據類型（以UTF8 編碼為例）：

類型 所占空間 不允許為NULL額外占用
char 一個字符三個字節 一個字節
varchar 一個字符三個字節 一個字節
int 四個字節 一個字節
tinyint 一個字節 一個字節

測試數據表如下：

CREATE TABLE `test_table` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NOT NULL,
 `b` int(11) DEFAULT NOT NULL,
 `c` int(11) DEFAULT NOT NULL,
 PRIMARY KEY (`id`),
 KEY `test_table_a_b_c_index` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

命中索引：

mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys     | key          | key_len | ref        | rows | filtered | Extra    |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE   | test_table | NULL    | ref | test_table_a_b_c_index | test_table_a_b_c_index | 12   | const,const,const |  1 |  100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

可以看到 key_len = 12，這是如何計算的呢？
因為字符集是 UTF8，一個字段占用四個字節，三個字段就是 4 * 3 = 12 字節。

是否允許為 NULL，如果允許為 NULL，則需要用額外的字節來標記該字段，不同的數據類型所需的字節大小不同。

mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11) NULL;
mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys     | key          | key_len | ref        | rows | filtered | Extra    |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE   | test_table | NULL    | ref | test_table_a_b_c_index | test_table_a_b_c_index | 15   | const,const,const |  1 |  100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+

可以看到，當字段允許為空時，這時的key_len 變成瞭15 = 4 3 + 1 3（INT 類型為空時，額外占用一個字節）。

索引優化

有瞭這些基礎知識之後，再來根據實際的SQL 判斷索性性能好壞。

還是以上面那張數據表為例，為 a、b、c 三個字段創建聯合索引。

SQL 語句 是否索引
explain select * from test_table where a = 1 and b = 2 and c = 3; Extra:Using index key_len: 15
explain select * from test_table where a = 1 and b = 2 and c = 3 order by c; Extra:Using index key_len: 15
explain select * from test_table where b = 2 and c = 3; Extra:Using where; Using index key_len: 15
explain select * from test_table where a = 1 order by c; Extra:Using where; Using index; Using filesort key_len: 5
explain select * from test_table order by a, b, c; Extra:Using index key_len: 15
explain select * from test_table order by a, b, c desc; Extra:Using index; Using filesort key_len:15
explain select * from test_table where a in (1,2) and b in (1,2,3) and c = 1; Extra:Using where; Using index key_len: 15

通常在查看執行計劃時， Extra 列為 Using index 則表示優化器使用瞭覆蓋索引。

  • SQL1 可以使用覆蓋索引，性能好
  • SQL2 可以使用覆蓋索引，同時避免排序，性能好
  • SQL3 可以使用覆蓋索引，但是需要根據 where 字句進行過濾
  • SQL4 可以使用部分索引 a，但無法避免排序，性能差
  • SQL5 可以完全使用覆蓋索引，同時可以避免排序，性能好
  • SQL6 可以使用覆蓋索引，但無法避免排序，（這是因為 MySQL InnoDB 創建索引時默認asc升序，索引無法自動倒序排序）
  • SQL7 可以使用覆蓋索引，但是需要根據 where 子句進行過濾（非定值查詢）

創建索引規范

  • 考慮到索引維護的成本，單張表的索引數量不超過 5 個，單個索引中的字段數不超過 5 個
  • 不在低基數列上建⽴索引，例如“性別”。 在低基數列上創建的索引查詢相比全表掃描不一定有性能優勢，特別是當存在回表成本時。
  • 合理創建聯合索引，(a,b,c) 相當於 (a) 、(a,b) 、(a,b,c)。 
  • 合理使用覆蓋索引減少IO，避免排序。

以上就是Mysql 索引該如何設計與優化的詳細內容，更多關於MySQL 索引設計與優化的資料請關註WalkonNet其它相關文章！

推薦閱讀：