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 type: ref possible_keys: idx_payment_date key: idx_payment_date key_len: 11 ref: const,const rows: 8 Extra: Using index //using index表示直接通过访问索引就足够获取所需要的数据，无需通过索引回表，using index也就是常说的覆盖索引扫描。只访问必须访问的数据，在一般情况下，减少不必要的数据访问能够提高效率。 1 row in set (0.04 sec)
//匹配列前缀 mysql> show create table film_text\G *************************** 1. row *************************** Table: film_text Create Table: CREATE TABLE `film_text` ( `film_id` smallint(6) NOT NULL, `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (`film_id`), FULLTEXT KEY `idx_title_description` (`title`,`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> create index idx_title_desc_part on film_text(title(10),description(20)); Query OK, 1000 rows affected (0.40 sec) Records: 1000 Duplicates: 0 Warnings: 0
mysql> explain select title from film_text where title like 'AFRICAN%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_text type: range possible_keys: idx_title_desc_part,idx_title_description key: idx_title_desc_part //使用列前缀匹配，不适用全局索引idx_title_description key_len: 32 ref: NULL rows: 1 Extra: Using where 1 row in set (0.16 sec)
//实现索引匹配是部分精确，而其他部分进行范围匹配。 mysql> alter table rental drop index idx_rental_date; Query OK, 16044 rows affected (0.86 sec) Records: 16044 Duplicates: 0 Warnings: 0 mysql> alter table rental add index idx_rental_date(rental_date,customer_id,inventory_id); Query OK, 16044 rows affected (1.43 sec) Records: 16044 Duplicates: 0 Warnings: 0 mysql> explain select inventory_id 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 type: range possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: NULL rows: 24 Extra: Using where; Using index 1 row in set (0.00 sec) 执行过程是先使用索引的首字段rental_date将符合rental_date = '2006-02-14 15:16:03'的索引过滤，通过IO取出数据（回表，因为还要进行customer_id条件进行过滤），然后通过customer_id>=300 <=400过滤记录。
注意：将range放在前面mysql查询优化器也会将语句优化为可以使用索引的查询。 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: range possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: NULL rows: 1 Extra: Using where 1 row in set (0.00 sec)
//如果列名是索引，那么使用column_name is null 就会使用索引。 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`), KEY `idx_payment_date` (`payment_date`,`amount`,`last_update`), 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_INCR |