mysql索引介绍

谁借莪１个温暖的怀抱￠ 2023-06-22 13:58 12阅读 0赞

索引是优化中最常用的手段之一，通过索引通常可以解决大多数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]

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