mysql高效索引之覆蓋索引

標籤：

概念

如果索引包含所有滿足查詢需要的資料的索引成為覆蓋索引(Covering Index)，也就是平時所說的不需要回表操作

判斷標準

使用explain，可以通過輸出的extra列來判斷，對於一個索引覆蓋查詢，顯示為 using index,MySQL查詢最佳化工具在執行查詢前會決定是否有索引覆蓋查詢  注意1、覆蓋索引也並不適用於任意的索引類型，索引必須儲存列的值2、Hash 和full-text索引不儲存值，因此MySQL只能使用B-TREE3、並且不同的儲存引擎實現覆蓋索引都是不同的4、並不是所有的儲存引擎都支援它們5、如果要使用覆蓋索引，一定要注意SELECT 列表值取出需要的列，不可以是SELECT *，因為如果將所有欄位一起做索引會導致索引檔案過大，查詢效能下降，不能為了利用覆蓋索引而這麼做  InnoDB1、覆蓋索引查詢時除了除了索引本身的包含的列，還可以使用其預設的 叢集索引列 2、這跟INNOB的索引結構有關係，主索引是B+樹索引儲存，也即我們所說的資料行即索引，索引即資料3、對於INNODB的輔助索引，它的葉子節點儲存的是索引值和指向主鍵索引的位置，然後需要通過主鍵在查詢表的欄位值，所以輔助索引儲存了主鍵的值4、覆蓋索引也可以用上INNODB 預設的叢集索引5、innodb引擎的所有儲存了主鍵ID，事務ID，復原指標,非主鍵ID，他的查詢就會是非主鍵ID也可覆蓋來取得主鍵ID 覆蓋索引是一種非常強大的工具，能大大提高查詢效能，只需要讀取索引而不用讀取資料有以下一些優點
1、索引項目通常比記錄要小，所以MySQL訪問更少的資料
2、索引都按值的大小順序儲存，相對於隨機訪問記錄，需要更少的I/O
3、大多資料引擎能更好的緩衝索引，比如MyISAM只緩衝索引
4、覆蓋索引對於InnoDB表尤其有用，因為InnoDB使用叢集索引組織資料，如果二級索引中包含查詢所需的資料，就不再需要在叢集索引中尋找了
在sakila的inventory表中，有一個複合式索引(store_id,film_id)，對於只需要訪問這兩列的查詢，MySQL就可以使用索引，如下表結構

CREATE TABLE `inventory` (  `inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `film_id` smallint(5) unsigned NOT NULL,  `store_id` tinyint(3) unsigned NOT NULL,  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`inventory_id`),  KEY `idx_fk_film_id` (`film_id`),  KEY `idx_store_id_film_id` (`store_id`,`film_id`),  CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,  CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8 |

 查詢語句

mysql>  EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: inventory         type: indexpossible_keys: NULL          key: idx_store_id_film_id      key_len: 3          ref: NULL         rows: 4581        Extra: Using index1 row in set (0.03 sec)

 在大多數引擎中，只有當查詢語句所訪問的列是索引的一部分時，索引才會覆蓋。但是，InnoDB不限於此，InnoDB的二級索引在葉子節點中儲存了 primary key的值。因此，sakila.actor表使用InnoDB，而且對於是last_name上有索引，所以，索引能覆蓋那些訪問actor_id的查 詢，如下

mysql> EXPLAIN SELECT actor_id, last_name  FROM sakila.actor WHERE last_name = ‘HOPPER‘\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: actor         type: refpossible_keys: idx_actor_last_name          key: idx_actor_last_name      key_len: 137          ref: const         rows: 2        Extra: Using where; Using index1 row in set (0.00 sec)

使用索引進行排序

MySQL中，有兩種方式產生有序結果集：一是使用filesort，二是按索引順序掃描

利用索引進行排序操作是非常快的，而且可以利用同一索引同時進 行尋找和排序操作。當索引的順序與ORDER BY中的列順序相同且所有的列是同一方向(全部升序或者全部降序)時，可以使用索引來排序，如果查詢是串連多個表，僅當ORDER BY中的所有列都是第一個表的列時才會使用索引，其它情況都會使用filesort

CREATE TABLE `actor` (  `actor_id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(16) NOT NULL DEFAULT ‘‘,  `password` varchar(16) NOT NULL DEFAULT ‘‘,  PRIMARY KEY (`actor_id`),  KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;insert into actor(name,password) values (‘cat01‘,‘1234567‘),(‘cat02‘,‘1234567‘),(‘ddddd‘,‘1234567‘),(‘aaaaa‘,‘1234567‘);

1、 explain select actor_id from actor order by actor_id \G

mysql> explain select actor_id from actor order by actor_id \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: actor         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 4          ref: NULL         rows: 4        Extra: Using index1 row in set (0.00 sec)

 2、explain select actor_id from actor order by password \G

mysql> explain select actor_id from actor order by password \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: actor         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4        Extra: Using filesort1 row in set (0.00 sec)

 3、explain select actor_id from actor order by name \G

mysql> explain select actor_id from actor order by name \G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: actor         type: indexpossible_keys: NULL          key: name      key_len: 50          ref: NULL         rows: 4        Extra: Using index1 row in set (0.00 sec)

 

當MySQL不能使用索引進行排序時，就會利用自己的排序演算法(快速排序演算法)在記憶體(sort buffer)中對資料進行排序，如果記憶體裝載不下，它會將磁碟上的資料進行分塊，再對各個資料區塊進行排序，然後將各個塊合并成有序的結果集（實際上就是外排序）

對於filesort，MySQL有兩種排序演算法

1、兩遍掃描演算法(Two passes)

實現方式是先將須要排序的欄位和可以直接定位到相關行資料的指標資訊取出，然後在設定的記憶體（通過參數sort_buffer_size設定）中進行排序，完成排序之後再次通過行指標資訊取出所需的Columns
註：該演算法是4.1之前採用的演算法，它需要兩次訪問資料，尤其是第二次讀取操作會導致大量的隨機I/O操作。另一方面，記憶體開銷較小

2、 一次掃描演算法(single pass)

該演算法一次性將所需的Columns全部取出，在記憶體中排序後直接將結果輸出
註：從 MySQL 4.1 版本開始使用該演算法。它減少了I/O的次數，效率較高，但是記憶體開銷也較大。如果我們將並不需要的Columns也取出來，就會極大地浪費排序過程所需要的記憶體。在 MySQL 4.1 之後的版本中，可以通過設定 max_length_for_sort_data 參數來控制 MySQL 選擇第一種排序演算法還是第二種。當取出的所有大欄位總大小大於 max_length_for_sort_data 的設定時，MySQL 就會選擇使用第一種排序演算法，反之，則會選擇第二種。為了儘可能地提高排序效能，我們自然更希望使用第二種排序演算法，所以在 Query 中僅僅取出需要的 Columns 是非常有必要的。

當對串連操作進行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進行filesort操作，然後進行串連處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結果集產生一個暫存資料表，在串連完成之後進行filesort操作，此時，EXPLAIN輸出“Using temporary;Using filesort”

mysql高效索引之覆蓋索引