mysql索引介绍

 

索引是优化中最常用的手段之一，通过索引通常可以解决大多数SQL性能问题。

索引使用的场景

1.匹配全值，对索引的值都指定具体的值

mysql> explain select * from rental where rental_date='2015-05-25 17:22:10' and inventory_id=3 and customer_id=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
         type: const
possible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

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

3.匹配最左前缀匹配，仅仅对索引中最左列进行查询，比如复合索引 col1+col2+col3 ,使用索引的是 col1+col2,col1+col3,col1+col2+col3,不会使用索引的是col2+col3,col2.

mysql> explain select  *  from payment where payment_date='2006-02-14 15:16:04' and last_update='2006-02-15 22:12:32'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ref
possible_keys: id_payment_date
          key: id_payment_date
      key_len: 5
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

4.仅仅对索引进行查询，当查询列都在索引上，查询效率会更快,Extra部分变成了Using index,也就是经常说的覆盖索引，说明直接访问索引就可以获取到必须的数据，不需要进行回表查询，减少不必要的数据访问提高性能。

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

5.匹配列前缀，使用索引中的第一列，并且包含索引的第一列的开头一部分进行查询。Extra是Using where 说明优化器要通过索引回表查询数据

mysql> create index idx_title_desc_part on film_text (title(10),description(20));
Query OK, 0 rows affected (0.05 sec)
Records: 0  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
      key_len: 32
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

6.部分精确匹配其他范围匹配

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: ref
possible_keys: rental_date,idx_fk_customer_id
          key: rental_date
      key_len: 5
          ref: const
         rows: 181
        Extra: Using where; Using index
1 row in set (0.00 sec)

7.mysql5.6引入了 Index Condition Pushdow(ICP)的特性，进一步优化查询，Pushdown表示操作下方，某些情况下的条件过滤操作下放到存储引擎，Using index condition 表示Mysql 使用ICP进一步优化查询，把条件customer_id的过滤条件下推打破存储引擎，这样能够降低不必要的IO访问。

mysql> select  version();
+-----------+
| version() |
+-----------+
| 5.6.45    |
+-----------+
1 row in set (0.00 sec)
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: rental_date,idx_fk_customer_id
          key: rental_date
      key_len: 5
          ref: const
         rows: 181
        Extra: Using index condition
1 row in set (0.00 sec)

索引使用不到的情况

1.以%开头的like查询用不到索引

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)

2.数据类型出现隐式转换特别是当列是字符串，那么一定要在列上加上双引号。

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

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

3.mysql估计使用索引比全表扫描更慢，不用索引

mysql> update film_text set title =concat('s',title);
Query OK, 1000 rows affected (0.13 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)

4.用or分割时候，or前的条件列有索引，or后的条件列没有索引，那么就不会使用索引。

mysql> explain select  *  from payment where  amount=3.96 or customer_id = 203\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)

简单的优化方式

定期分析表和检查表

analyze [local|no_write_to_binlog] table  [tbl_name]

 此语句分析和存储表的关键词分布，分析结果将可以使得系统得到准确的统计信息，使得sql能够生成正确的执行计划。

check tbale tbl_name [,tbl_name]...[option]...option={quick|fast|medium|extended|changed}

检查表的作用就是检查一个或多个表的是否有错误。

定期优化表

optimize [local|no_write_to_binlog] table tbl_name[,tbl_name] 

如果已经删除了表的一大部分，或者如果已经对含有可变长度的表，进行了更改，则使用此语法，可以进行优化，把表中的空间进行合并，并且可以消除由于删除或者更新造成的浪费空间。