MySQL索引优化

MySQL索引的建立对于MySQL的高效运行是很重要的。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。

下面介绍几种常见的MySQL索引类型。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

索引类型

主键索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD UNIQUE (column)

普通索引 INDEX

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD INDEX index_name (column)

组合索引 INDEX

组合索引，即一个索引包含多个列。可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD FULLTEXT (column)

索引结构及原理

mysql中普遍使用B+Tree做索引，但在实现上又根据聚簇索引和非聚簇索引而不同，本文暂不讨论这点。

b+树介绍

下面这张b+树的图片在很多地方可以看到，之所以在这里也选取这张，是因为觉得这张图片可以很好的诠释索引的查找过程。

如上图，是一颗b+树。浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

查找过程

在上图中，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

性质

(1) 索引字段要尽量的小。

通过上面b+树的查找过程，或者通过真实的数据存在于叶子节点这个事实可知，IO次数取决于b+数的高度h。

假设当前数据表的数据量为N，每个磁盘块的数据项的数量是m，则树高h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；

而m = 磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的；如果数据项占的空间越小，数据项的数量m越多，树的高度h越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。

(2) 索引的最左匹配特性。

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

建表、索引语句示例

建表：
create table student(
    stu_id int unsigned not null auto_increment,
    name varchar(32) not null default '',
    phone char(11) not null default '',
    stu_code varchar(32) not null default '',
    stu_desc text,
    primary key ('stu_id'),     //主键索引
    unique index 'stu_code' ('stu_code'), //唯一索引
    index 'name_phone' ('name','phone'),  //普通索引，复合索引
    fulltext index 'stu_desc' ('stu_desc'), //全文索引
) engine=myisam charset=utf8;
说明：
MySQL5.6版本后的InnoDB存储引擎开始支持全文索引，5.7版本后通过使用ngram插件开始支持中文。

更新：
alter table student
    add primary key ('stu_id'),     //主键索引
    add unique index 'stu_code' ('stu_code'), //唯一索引
    add index 'name_phone' ('name','phone'),  //普通索引，复合索引
    add fulltext index 'stu_desc' ('stu_desc'); //全文索引

删除：
alter table sutdent
    drop primary key,
    drop index 'stu_code',
    drop index 'name_phone',
    drop index 'stu_desc';

索引的使用原则

尽量选择区分度高的列作为索引

索引列不能参与计算，保持列“干净”

保证索引包含的字段独立在查询语句中，不能是在表达式中。

比如：Flistid+1>’2000000608201108010831508721’。原因很简单，假如索引列参与计算的话，那每次检索时，都会先将索引计算一次，再做比较，显然成本太大。

最左前缀匹配原则

对于多列索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。比如创建了多列索引(a,b,c)，会先匹配a字段，再匹配b字段，再匹配c字段的，中间不能跳过。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

语句	索引是否发挥作用
where a=3	是，只使用了a
where a=3 and b=5	是，使用了a,b
where a=3 and b=5 and c=4	是，使用了a,b,c
where b=3 or c=4	否
where a=3 and c=4	是，仅使用了a
where a=3 and b>10 and c=7	是，使用了a,b
where a=3 and b like ‘%xx%’ and c=7	使用了a,b

or的两边都有存在可用的索引，该语句才能用索引。

一般，在创建多列索引时，where子句中使用最频繁的一列放在最左边。

不要滥用索引，多余的索引会降低读写性能

=和in可以乱序

比如a = 1 and b = 2 and c = 3，建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

即使满足了上述原则，mysql还是可能会弃用索引，因为有些查询即使使用索引，也会出现大量的随机io，相对于从数据记录中的顺序io开销更大。

mysql 中能够使用索引的典型应用

测试库下载地址：https://downloads.mysql.com/d…

这里，先普及下explain之后的type和extra内容，具体可看以下文章：

• https://mengkang.net/1124.html
• https://www.cnblogs.com/kerrycode/p/9909093.html

匹配全值（match the full value）

对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。
例如，租赁表 rental 中通过指定出租日期 rental_date + 库存编号 inventory_id + 客户编号 customer_id 的组合条件进行查询，从执行计划的 key he extra 两字段的值看到优化器选择了复合索引 idx_rental_date:

关于explain结果值及其含义可以参考我的另一篇博客MySql数据库中的索引

MySQL [sakila]> 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
   partitions: NULL
         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
     filtered: 100.00
        Extra: NULL
 1 row in set, 1 warning (0.00 sec)

explain 输出结果中字段 type 的值为 const，表示是常量；字段 key 的值为 rental_date, 表示优化器选择索引 rental_date 进行扫描。

匹配值的范围查询（match a range of values）

对索引的值能够进行范围查找。

例如，检索租赁表 rental 中客户编号 customer_id 在指定范围内的记录：

MySQL [sakila]> explain select * from rental where customer_id >= 373 and customer_id < 400 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 718
     filtered: 100.00
        Extra: Using index condition
 1 row in set, 1 warning (0.05 sec)

类型 type 为 range 说明优化器选择范围查询，索引 key 为 idx_fk_customer_id 说明优化器选择索引 idx_fk_customer_id 来加速访问，注意到这个列子中 extra 列为 using index codition ,表示 mysql 使用了 ICP（using index condition） 来进一步优化查询。

匹配最左前缀（match a leftmost prefix）

仅仅使用索引中的最左边列进行查询，比如在 col1 + col2 + col3 字段上的联合索引能够被包含 col1、(col1 + col2)、（col1 + col2 + col3）的等值查询利用到，可是不能够被 col2、（col2、col3）的等值查询利用到。

最左匹配原则可以算是 MySQL 中 B-Tree 索引使用的首要原则。

仅仅对索引进行查询（index only query）

当查询的列都在索引的字段中时，查询的效率更高，所以应该尽量避免使用 select *，需要哪些字段，就只查哪些字段。

匹配列前缀（match a column prefix）

仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。

例如，现在需要查询出标题 title 是以 AFRICAN 开头的电影信息，从执行计划能够清楚看到，idx_title_desc_part 索引被利用上了：

MySQL [sakila]> create index idx_title_desc_part on film_text(title (10), description(20));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [sakila]> explain select title from film_text where title like 'AFRICAN%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: range
possible_keys: idx_title_desc_part,idx_title_description
          key: idx_title_desc_part
      key_len: 32
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
 1 row in set, 1 warning (0.00 sec)
extra 值为 using where 表示优化器需要通过索引回表查询数据。

能够实现索引匹配部分精确而其他部分进行范围匹配（match one part exactly and match a range on another part）

例如，需要查询出租日期 rental_date 为指定日期且客户编号 customer_id 为指定范围的库存：

MySQL [sakila]> MySQL [sakila]> 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
   partitions: NULL
         type: ref
possible_keys: rental_date,idx_fk_customer_id
          key: rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.85
        Extra: Using where; Using index
 1 row in set, 1 warning (0.00 sec)

如果列名是索引，那么使用 column_name is null 就会使用索引。

例如，查询支付表 payment 的租赁编号 rental_id 字段为空的记录就用到了索引：

MySQL [sakila]> explain select * from payment where rental_id is null \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using index condition
 1 row in set, 1 warning (0.00 sec)

存在索引但不能使用索引的典型场景

有些时候虽然有索引，但是并不被优化器选择使用，下面举例几个不能使用索引的场景。

以%开头的 like 查询不能利用 B-Tree 索引，执行计划中 key 的值为 null 表示没有使用索引

MySQL [sakila]> 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)

因为 B-Tree 索引的结构，所以以%开头的插叙很自然就没法利用索引了。一般推荐使用全文索引（Fulltext）来解决类似的全文检索的问题。或者考虑利用 innodb 的表都是聚簇表的特点，采取一种轻量级别的解决方式：一般情况下，索引都会比表小，扫描索引要比扫描表更快，而Innodb 表上二级索引 idx_last_name 实际上存储字段 last_name 还有主键 actot_id,那么理想的访问应该是首先扫描二级索引 idx_last_name 获得满足条件的last_name like ‘%NI%’ 的主键 actor_id 列表，之后根据主键回表去检索记录，这样访问避开了全表扫描演员表 actor 产生的大量 IO 请求。

MySQL [sakila]> 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

从执行计划中能够看出，extra 字段 using wehre；using index。理论上比全表扫描更快一下。

数据类型出现隐式转换的时候也不会使用索引

当列的类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则即便这个列上有索引，mysql 也不会用到，因为 MySQL 默认把输入的常量值进行转换以后才进行检索。

例如，演员表 actor 中的姓氏字段 last_name 是字符型的，但是 sql 语句中的条件值 1 是一个数值型值，因此即便存在索引 idx_last_name, mysql 也不能正确的用上索引，而是继续进行全表扫描：

MySQL [sakila]> explain select * from actor where last_name = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: idx_actor_last_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 10.00
        Extra: Using where
 1 row in set, 3 warnings (0.00 sec)

MySQL [sakila]> explain select * from actor where last_name = '1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
 1 row in set, 1 warning (0.00 sec)

where条件不符合最左前缀原则时

复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 leftmost，是不会使用复合索引的。

使用！= 或 <> 操作符时

尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

索引列参与计算

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'

对字段进行null值判断

应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

低效：select * from t_credit_detail where Flistid is null ;

可以在Flistid上设置默认值0，确保表中Flistid列没有null值，然后这样查询：

高效：select * from t_credit_detail where Flistid =0;

使用or来连接条件

应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

低效：select * from t_credit_detail where Flistid = ‘2000000608201108010831508721’ or Flistid = ‘10000200001’;

可以用下面这样的查询代替上面的 or 查询：

高效：select from t_credit_detail where Flistid = ‘2000000608201108010831508721’ union all select from t_credit_detail where Flistid = ‘10000200001’;

如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。

查看索引使用情况

如果索引正在工作， Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表名增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正在进行大量的表扫描，Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引，具体如下。

MySQL [sakila]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 5     |
| Handler_read_last     | 0     |
| Handler_read_next     | 200   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

使用索引的小技巧

字符串字段权衡区分度与长度的技巧

截取不同长度，测试区分度

# 这里假设截取6个字符长度计算区别度，直到区别度达到0.1，就可以把这个字段的这个长度作为索引了
mysql> select count(distinct left([varchar]],6))/count(*) from table;

#注意：设置前缀索引时指定的长度表示字节数，而对于非二进制类型(CHAR, VARCHAR, TEXT)字段而言的字段长度表示字符数，所
#      以，在设置前缀索引前需要把计算好的字符数转化为字节数，常用字符集与字节的关系如下：
# latin      单字节：1B
# GBK        双字节：2B
# UTF8       三字节：3B
# UTF8mb4    四字节：4B     
# myisam 表的索引大小默认为 1000字节，innodb 表的索引大小默认为 767 字节，可以在配置文件中修改 innodb_large_prefix 
# 项的值增大 innodb 索引的大小，最大 3072 字节。

区别度能达到0.1，就可以。

左前缀不易区分的字段索引建立方法

这样的字段，左边有大量重复字符，比如url字段汇总的http://

1. 倒过来存储并建立索引
2. 新增伪hash字段 把字符串转化为整型

索引覆盖

概念：如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘，这种查询，速度极快，江湖人称——索引覆盖

延迟关联

在根据条件查询数据时，如果查询条件不能用的索引，可以先查出数据行的id，再根据id去取数据行。

//普通查询 没有用到索引
select * from post where content like "%新闻%";
//延迟关联优化后 内层查询走content索引，取出id,在用join查所有行
select a.* from post as a inner join (select id from post where content like "%新闻%") as b on a.id=b.id;

索引排序　

排序的字段上加入索引，可以提高速度。
任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

重复索引和冗余索引

重复索引：在同一列或者相同顺序的几个列建立了多个索引，成为重复索引，没有任何意义，删掉
冗余索引：两个或多个索引所覆盖的列有重叠，比如对于列m,n ，加索引index m(m),indexmn(m,n),称为冗余索引。

在Join表的时候使用相同类型的字段，并将其索引

如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

索引碎片与维护

在数据表长期的更改过程中，索引文件和数据文件都会产生空洞，形成碎片。修复表的过程十分耗费资源，可以用比较长的周期修复表。

//清理方法
alert table xxx engine innodb; 
//或
optimize table xxx;

innodb引擎的索引注意事项

Innodb 表要尽量自己指定主键，如果有几个列都是唯一的，要选择最常作为访问条件的列作为主键，另外，Innodb 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效的减少索引的磁盘占用，提高索引的缓存效果。

参考：

https://machenxing.github.io/2018/12/19/Mysql%E7%B4%A2%E5%BC%95%E4%BC%98%E5%8C%96/

https://segmentfault.com/a/1190000009717352

https://mp.weixin.qq.com/s/KDIpY22tfmsrOIAuZNw4ZQ

https://cloud.tencent.com/developer/article/1004912