Mysql中存在索引但是不能使用索引的典型情况

    （1）以%开头的LIKE查询不能利用B-Tree索引

    mysql> show create table actor\G;
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

    分析一下下面的语句

    mysql> explain select * from actor where last_name like '%NI%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using where
1 row in set (0.00 sec)

    通过上面的语句，我们会发现，虽然存在last_name的索引，但是还是没有被利用到。上面的情况，我们可以使用下面的语句，来进行查询

    mysql> explain select * from (select actor_id from actor where last_name like '%NI%') a ,actor b where a.actor_id = b.actor_id\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: b
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: a.actor_id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: actor
         type: index
possible_keys: NULL
          key: idx_actor_last_name
      key_len: 137
          ref: NULL
         rows: 200
        Extra: Using where; Using index
3 rows in set (0.00 sec)

    上面的sql语句，利用到Innodb索引的聚簇索引的特性（idx_last_name实际上存储的字段是last_name和主键ID），我们首先扫二级索引idx_last_name获得满足条件last_name like ‘%NI%’ 的主键actor_id，然后再根据主键去回表去检索记录，这样就避开了全表扫描产生的大量的IO请求。

    （2）数据类型出现隐式转化的时候，也不会利用索引，特别是当列索引是字符串的时候，那么一定要记得在where条件中把字符串常量的引号引起来，否则即使这个列上有索引，那么也不会利用到索引，因为mysql默认将输入的常量值进行转换之后才进行检索。

    mysql> explain select * from actor where last_name = 1\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: idx_actor_last_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using where
1 row in set (0.03 sec)

    从上面的语句中我们可以看出，还是全表扫描了，extra的信息为Using where，对于这种情况，我们可以如下操作：

    mysql> explain select * from actor where last_name = '1'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.01 sec)

    通过上面的两个语句我们可以看出，加了引号的话，我们可以看到，type为ref，extra的信息为Using index condition

    （3）复合索引下，如果查询条件不包含最左边的部分，即不满足最左原则，是不会利用到复合索引的。

    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)

    分析下面的语句，我们会发现，索引存在联合索引`idx_payment_date，但是没有利用到payment_date，索引没有利用到这个联合索引进行查询。

    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.01 sec)

    （4）如果mysql估计使用索引会比全表扫描更慢，也是不会使用到索引的。

    mysql> update film_text set title = concat('S',title);
Query OK, 1000 rows affected (0.21 sec)
Rows matched: 1000  Changed: 1000  Warnings: 0

mysql> explain select * from film_text where title like 'S%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
         type: ALL
possible_keys: idx_title_desc_part,idx_title_description
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

    更换一个查询率更高的值，就会发现，mysql优化器更倾向于选择索引扫描。

    mysql> explain select * from film_text where title like 'SW%'\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: 43
        Extra: Using where
1 row in set (0.00 sec)

    （5）用or分割开的条件，如果or前的条件中的列有索引，但是or后面的列中没有索引，那么涉及的索引都不会被用到。

    mysql> explain select * from payment where customer_id = 203 or amount = 3.96\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ALL
possible_keys: idx_fk_customer_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
        Extra: Using where
1 row in set (0.00 sec)