MySQL中使用索引的典型場(chǎng)景

• 匹配全值
  對(duì)索引中所有列都指定具體值，即是對(duì)索引中的所有列都有等值匹配的條件。

如我們有以下表結(jié)構(gòu)：

mysql> show create table rental\G;
*************************** 1. row ***************************
  Table: rental
  Create Table: CREATE TABLE `rental` (
  `rental_id` int NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint unsigned NOT NULL,
  `customer_id` smallint unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> show create table payment\G;
*************************** 1. row ***************************
       Table: payment
Create Table: CREATE TABLE `payment` (
  `payment_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` smallint unsigned NOT NULL,
  `staff_id` tinyint unsigned NOT NULL,
  `rental_id` int DEFAULT NULL,
  `amount` decimal(5,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `fk_payment_rental` (`rental_id`),
  KEY `idx_payment_date` (`payment_date`,`amount`,`last_update`),
  CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

當(dāng)我們執(zhí)行以下sql語(yǔ)句：

mysql> explain select * from rental where rental_date="2005-05-25 17:22:10" and inventory_id=373 and customer_id=343\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: const
possible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

字段key的值為rental_date,表示優(yōu)化器選擇索引rental_date進(jìn)行掃描。

• 匹配值的范圍查詢
  對(duì)索引的值能夠進(jìn)行范圍查詢。
  如檢索租賃表 rental 中客戶編號(hào)customer_id在指定范圍內(nèi)的記錄：

mysql> explain select * from rental where customer_id>= 373 and customer_id < 400\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 718
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

Extra: Using index condition 表示MySQL使用了ICP來(lái)進(jìn)一步優(yōu)化查詢，在檢索的時(shí)候，把條件customer_id的過(guò)濾操作下推到存儲(chǔ)引擎層來(lái)完成，這樣可以降低不必要的IO訪問(wèn)。

• 匹配最左前綴
  僅僅使用索引中的最左邊列進(jìn)行查找，比如在col1+col2+col3字段上的聯(lián)合索引能夠被包含col1、（col1+col2）、（col1+col2+col3）的等值查詢利用到，可是不能被col2、（col2+col3）的等值查詢利用到。

• 僅僅對(duì)索引進(jìn)行查詢，當(dāng)查詢的列都在索引的字段上時(shí)，查詢的效率更高。

mysql> explain select last_update from payment where payment_date='2006-02-14 15:16:03' and amount=3.98\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 8
          ref: const,const
         rows: 8
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Extra部分變成了Using index,也就意味著，現(xiàn)在直接訪問(wèn)索引就足夠獲取到所需要的數(shù)據(jù)，不需要通過(guò)索引回表，Using index也就是平常說(shuō)的覆蓋索引掃描。只訪問(wèn)必須訪問(wèn)的數(shù)據(jù)，在一般情況下，減少不必要的數(shù)據(jù)訪問(wèn)能提高效率。

如何計(jì)算 key_len 列的值？

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過(guò)這個(gè)值可以算出具體使用了索引中的哪些列。

ken_len計(jì)算規(guī)則如下：

• 如果字段允許為 NULL，需要1字節(jié)記錄是否為 NULL 索引最大長(zhǎng)度是768字節(jié)，當(dāng)字符串過(guò)長(zhǎng)時(shí)，mysql會(huì)做一個(gè)類似左前綴索引的處理，將前半部分的字符提取出來(lái)做索引。

如何理解ICP特性（索引下推）。

MySQL 5.6 引入了Index Condition Pushdown （ICP）的特性，進(jìn)一步優(yōu)化了查詢。PushDown 表示操作下放，某些情況下的條件過(guò)濾操作下放到存儲(chǔ)引擎。

如執(zhí)行以下sql語(yǔ)句，其內(nèi)部執(zhí)行流程如圖所示：

mysql> explain select * from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <= 400 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: rental_date,idx_fk_customer_id
          key: rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.85
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

尚未使用ICP特性

使用ICP特性

深入淺出索引
索引與Index Condition Pushdown
參考鏈接