Mysql Index extends最佳化

標籤：

  Innodb通過自動把主鍵列添加到每個二級索引來擴充它們：

 

CREATE TABLE t1 (  i1 INT NOT NULL DEFAULT 0,  i2 INT NOT NULL DEFAULT 0,  d DATE DEFAULT NULL,  PRIMARY KEY (i1, i2),  INDEX k_d (d)) ENGINE = InnoDB;

 

   該表定義（t1,t2）為聯合主鍵，也定義個二級索引k_id 在列（d）上，但是內部innodb會擴充它，變成列index（d,i1,i2）;

 

   版本5.6.9之前，最佳化器不會這麼最佳化，但在5.6.9，開始支援，可以得到更好的效能和更有效執行計畫；

   最佳化器可以用擴充的二級索引來進行ref,range,index_merge等類型index access,鬆散的index sacns,join串連和排序最佳化，和min()/max()最佳化；

   資料：

INSERT INTO t1 VALUES(1, 1, ‘1998-01-01‘), (1, 2, ‘1999-01-01‘),(1, 3, ‘2000-01-01‘), (1, 4, ‘2001-01-01‘),(1, 5, ‘2002-01-01‘), (2, 1, ‘1998-01-01‘),(2, 2, ‘1999-01-01‘), (2, 3, ‘2000-01-01‘),(2, 4, ‘2001-01-01‘), (2, 5, ‘2002-01-01‘),(3, 1, ‘1998-01-01‘), (3, 2, ‘1999-01-01‘),(3, 3, ‘2000-01-01‘), (3, 4, ‘2001-01-01‘),(3, 5, ‘2002-01-01‘), (4, 1, ‘1998-01-01‘),(4, 2, ‘1999-01-01‘), (4, 3, ‘2000-01-01‘),(4, 4, ‘2001-01-01‘), (4, 5, ‘2002-01-01‘),(5, 1, ‘1998-01-01‘), (5, 2, ‘1999-01-01‘),(5, 3, ‘2000-01-01‘), (5, 4, ‘2001-01-01‘),(5, 5, ‘2002-01-01‘);

查詢sql:

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01‘;

    這種情況下，最佳化器不會使用主鍵，因為主鍵有（t1,t2）組成，但是該查詢中沒有引用i2;最佳化器會選擇二級索引 k_d(d) ，執行計畫依賴與是否擴充index被使用；

    當最佳化器沒有使用index extensions時，他對待 k_d 僅僅為（d）.

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01‘\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: refpossible_keys: PRIMARY,k_d          key: k_d      key_len: 4          ref: const         rows: 5        Extra: Using where; Using index

當最佳化器把index extensions考慮在內的話，對待k_d （d,i1,i2)， 這種情況下,他可以使用最左首碼（d,i1）開得到一個更好的執行計畫；

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01‘\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: refpossible_keys: PRIMARY,k_d          key: k_d      key_len: 8          ref: const,const         rows: 1        Extra: Using index

兩種情況下，key列顯示最佳化器都會選擇用二級索引k-d，但是 ：

    1：key_len列從4bytes 變成了8 bytes,說明了key 是尋找的 列 d 和 i1,而不是僅僅d;

    2: rows列計數從5減少到1，說明 innodb檢測更少的行來得到結構；

    3：Extra列從 using where;using index 變成了using index,意味著結果只使用了index,沒有access資料行；

 

最佳化器使用擴充的Index行為不同也可以通過show status指令來觀看:

FLUSH TABLE t1;FLUSH STATUS;SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01‘;SHOW STATUS LIKE ‘handler_read%‘

flush table :清除Table cache;

flush status:清除狀態計數;

沒有index extendsions,show status:

+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     || Handler_read_key      | 1     || Handler_read_last     | 0     || Handler_read_next     | 5     || Handler_read_prev     | 0     || Handler_read_rnd      | 0     || Handler_read_rnd_next | 0     |+-----------------------+-------+

有index extensions,show status: handler_read_next 從5變成1

+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     || Handler_read_key      | 1     || Handler_read_last     | 0     || Handler_read_next     | 1     || Handler_read_prev     | 0     || Handler_read_rnd      | 0     || Handler_read_rnd_next | 0     |+-----------------------+-------+

optimizer_switch系統變數的use_index_extensions標誌可以控制是否最佳化器進行二級索引擴充，預設，是開啟的，

SET optimizer_switch = ‘use_index_extensions=off‘;

Mysql Index extends最佳化