** id: 1 select_type: SIMPLE table: rental type: const //使用唯一性索引或者primary key进行查询时是const possible_keys: idx_rental_date,idx_fk_inventory_id,idx_fk_customer_id key: idx_rental_date key_len: 13 ref: const,const,const rows: 1 Extra: 1 row in set (0.00 sec)
注意：等号或者in可以乱序 mysql> explain select * from rental where customer_id = 5 and rental_date = '2006-05-30 10:00:00'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: const,const rows: 1 Extra: 1 row in set (0.01 sec) mysql> explain select * from rental where rental_date = '2006-05-30 10:00:00' and customer_id = 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: const,const rows: 1 Extra: 1 row in set (0.00 sec)
//匹配值的范围查询，对索引的值能够进行范围查询 mysql> explain select * from rental where customer_id >=373 and customer_id <= 400\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: range possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 745 Extra: Using where //Using where表示优化器除了根据索引来加速访问之外，还根据索引回表查询数据。 1 row in set (0.00 sec)
Using where
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
//匹配最左匹配，仅仅使用索引中的最左边列进行查找。
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 NOT 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 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 UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> alter table payment add index idx_payment_date(payment_date,amount,last_update); Query OK, 16049 rows affected (2.85 sec) Records: 16049 Duplicates: 0 Warnings: 0
mysql> explain select * from payment where payment_date = '2006-02-14 15:16:03' and last_update='2006-02-15 22:12:32'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: idx_payment_date key: idx_payment_date key_len: 8 ref: const rows: 182 Extra: Using where 1 row in set (0.00 sec)
mysql> explain select * from payment where amount = 3 and last_update='2006-02-15 22:12:32'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ALL //不是使用最左匹配，全表扫描 possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16470 Extra: Using where 1 row in set (0.00 sec)
//仅仅对索引进行查询，当查询的字段在索引的字段中时，查询的效率更高。不必回表数据。 mysql> explain select |