您知道嗎, MySQL 也能做functional index

長久以來大多數MySQL的用戶認為它沒有functional index,當我們在where條件的比對值中放入函式,它就只能做全表掃瞄,例如: select * from sakila.payment where month(payment_date) = 2; 我們想查2月支付的款項,MySQL 的優代器只能做全表掃瞄.這個問題在5.7版以後己經不是個問題了.
我們看看這個例子:

在sakila demo database 中未加functional index到payment表時,我們的表結構如下:

mysql> show create table payment\G

*************************** 1. row ***************************

       Table: payment

Create Table: CREATE TABLE `payment` (

  `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

  `customer_id` smallint(5) unsigned NOT NULL,

  `staff_id` tinyint(3) unsigned NOT NULL,

  `rental_id` int(11) 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`),

  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=utf8

我們explain 上述的SQL指令顯示它是全表掃瞄:

mysql> explain select count(*) from sakila.payment where month(payment_date)=2;

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+

| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+

|  1 | SIMPLE      | payment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16086 |   100.00 | Using where |

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+

我們加上索引:

mysql> alter table payment add index pay_month_idx ((month(payment_date)));

表結構顯示索引"pay_month_idx"加在"payment_date"欄位上:

mysql> show create table payment\G

*************************** 1. row ***************************

       Table: payment

Create Table: CREATE TABLE `payment` (

  `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

  `customer_id` smallint(5) unsigned NOT NULL,

  `staff_id` tinyint(3) unsigned NOT NULL,

  `rental_id` int(11) 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 `pay_month_idx` ((month(`payment_date`))),

  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=utf8

explain 同樣的SQL指令顯示它是透過索引找到所要的資料

mysql> explain select count(*) from sakila.payment where month(payment_date)=2\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: payment

   partitions: NULL

         type: ref

possible_keys: pay_month_idx

          key: pay_month_idx

      key_len: 5

          ref: const

         rows: 182

     filtered: 100.00

        Extra: NULL

同樣的WHERE MONTH(payment_date)為查詢條件的SQL命令由於加上了month(payment_date) 為內容的functional index將全表掃瞄16086筆row改成透過pay_month_idx索引,只訪問了182筆 row