SQL优化之不能使用索引的典型案例及解决办法

第一种情况like的值为以%开头

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

以%开头无法使用索引，推荐使用全文索引，
解决办法扫描二级索引获得满足条件的last_name like '%NI%'的主键actor_id,然后根据主键回表来检索记录，避开全表扫描产生的大量IO请求

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: SIMPLE
        table: actor
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: idx_actor_last_name
      key_len: 137
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)