Mysql中使用索引的典型场景

来源：互联网发布：idea 测试java代码编辑：程序博客网时间：2024/05/17 03:47

（1）匹配全值，对索引中的所有列都指定具体值，也就是对索引中的所有列都有等值匹配的条件。

mysql> show create table rental\G;
*************************** 1. row ***************************
Table: rental
Create Table: CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
KEY `idx_rental_date` (`rental_date`,`inventory_id`,`customer_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_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)

上面是表结构，我们用explain分析下面的语句

    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
         type: ref
possible_keys: idx_fk_inventory_id,idx_fk_customer_id,idx_rental_date
          key: idx_rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
        Extra: NULL
1 row in set (0.01 sec)

从上面我们可以看到，利用了索引idx_rental_date，因为idx_rental_date中的所有列，都有指定的值。

（2）匹配值的范围查询，对索引的值能够进行范围查找。

    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: 682
        Extra: Using index condition
1 row in set (0.01 sec)

从上面的分析中我们可以看出，对索引idx_fk_customer_id而言，给定了查找的范围，从而利用索引来加速访问。Using index condition 表示mysql使用到了ICP来进行进一步的优化，在检索的时候，把条件customer_id的过滤操作下推到存储引擎来完成，这样能够降低不必要的IO访问（注意，这个是mysql5.6的新特性，如果是mysql5.5或者之前的版本，会显示Using where）

（3）匹配最前缀，仅仅使用索引中的最左边列进行查找。例如col1+col2+col3字段上的联合索引能够被包含col1,(col1+col2),(col1+col2+col3)的等值查询利用到，可是不能被col2,(col2+col3)的等值查询利用到。

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 `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_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

我们分析下面的语句：

    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: 5
          ref: const
         rows: 182
        Extra: Using index condition
1 row in set (0.00 sec)

上面我们可以看到，利用了索引idx_payment_date,所以我们可以知道(col1+col3)的模式，也可以利用到索引，但是(col2+col3)的模式，是不会利用到索引的，我们看下面的语句：

    mysql> explain select * from payment where amount = 3.98 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: 16086
        Extra: Using where
1 row in set (0.00 sec)

（4）仅仅对索引列进行查找，当查询的列，都在索引字段中的时候，查询的效率更高。

    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
         type: ref
possible_keys: idx_payment_date
          key: idx_payment_date
      key_len: 8
          ref: const,const
         rows: 8
        Extra: Using index
1 row in set (0.00 sec)

上面的例子，就是典型的利用到了索引来查询（Using index）

（5）匹配列前缀，仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。

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`),
KEY `idx_title_desc_part` (`title`(10),`description`(20)),
FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

分析下面的语句：

    mysql> explain select title from film_text where title like 'AFRIC%'\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
      key_len: 32
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

从上面我们可以看出，利用到了idx_title_desc_part，其实，Using where表示优化器需要通过索引回表来查询数据。

（6）能够使用索引匹配部分精确而其他部分进行范围匹配，例如，需要查询出租日期rental_date为指定日期客户编号customer_id为指定范围的库存。

    mysql> explain select inventory_id from rental where rental_date = '2006-02-14 15:15:03' and customer_id>300 and customer_id<400\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: 5
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

从上面我们可以看出，使用了索引idx_rental_date

（7）如果列名是索引，使用column_name is null 就会使用索引.

    mysql> explain select * from payment where rental_id is null\G;
*************************** 1. row ***************************
           id: 1
select_type: SIMPLE
        table: payment
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: const
         rows: 5
        Extra: Using index condition
1 row in set (0.00 sec)

（8）Index Condition PushDown(ICP)，这个特性，只针对mysql5.6之后的版本

    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
         type: ref
possible_keys: idx_fk_customer_id,idx_rental_date
          key: idx_rental_date
      key_len: 5
          ref: const
         rows: 182
        Extra: Using index condition
1 row in set (0.00 sec)

0 0