关于mysql:聊一聊MySQL索引失效的问题

一、索引原理

索引是进步数据库查问性能的一个重要办法。

应用索引用可疾速找出某个列中蕴含特定值的行。不应用索引，必须从第一条记录开始读，可能要读完整个表，能力找出相干的行。

应用索引就像查字典一样，咱们能够依据拼音、笔画、偏旁部首等排序的目录(索引)，疾速查找到须要的字。

之前介绍MySQL存储引擎的文章（聊一聊MySQL的存储引擎），测试比照了两种存储引擎(MyISAM或者InnoDB)，应用主键索引查问，效率快了几十倍。

尽管索引大大提高了查问(select)速度，但同时也会升高更新(insert,update,delete)表的速度，因为更新表时，数据库不仅要更新和保留数据，还要更新和保留索引文件。当然索引文件也占用存储空间。

二、索引生效的场景

索引尽管放慢了查问效率，但应用办法不当，就会呈现索引生效。

上面实际操作，列举一些索引生效的场景。

CREATETABLE customer(id INTAUTO_INCREMENT,companyVARCHAR(30),nameVARCHAR(30),sex enum('male','female'),age INT,phoneVARCHAR(30),addressVARCHAR(60),PRIMARYKEY (id));CREATEINDEX index_company ON customer(company);CREATEINDEX index_age ON customer(age);CREATEINDEX index_phone ON customer(phone);CREATEINDEX index_phone_name ON customer(phone,name);

下面创立了一个客户表，以及三个单列索引和一个组合索引，咱们来查看一下索引：

mysql>show index from customer; # (因为排版问题，去掉了一些信息)+------------+------------------+--------------+-------------+------------+|Non_unique | Key_name         |Seq_in_index | Column_name | Index_type |+------------+------------------+--------------+-------------+------------+|          0 | PRIMARY          |           1 | id          | BTREE      ||          1 | index_company    |           1 | company     | BTREE      ||          1 | index_age        |            1 | age         | BTREE      ||          1 | index_phone      |            1 | phone       |BTREE      ||          1 | index_phone_name |            1 | phone       | BTREE      ||          1 | index_phone_name |            2 | name        | BTREE      |+------------+------------------+--------------+-------------+------------+6 rows inset (0.01 sec)

下面显示包含一个默认的主键索引，还有三个单列索引，两个组合索引项。其中主键索引为惟一(unique)索引，索引类别(Index_type)显示为BTREE，理论为B+树。

2.1 含糊匹配LIKE以%结尾，会导致索引生效。

explain select *from customer where company like '%abc'\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where

应该把含糊匹配放到最左边。

explain select * from customer where company like 'abc%'\G*************************** 1. row***************************           id: 1  select_type:SIMPLE        table:customer   partitions:NULL         type:rangepossible_keys: index_company          key:index_company      key_len: 123          ref:NULL         rows: 1     filtered:100.00        Extra:Using index condition

2.2 索引列进行计算，会导致索引生效。

explain select *from customer where age-1=20\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where

应该把运算放在左边。

explain select *from customer where age=20+1\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: refpossible_keys:index_age          key: index_age      key_len: 5          ref: const         rows: 1     filtered: 100.00        Extra: NULL

2.3 索引列应用函数，会导致索引生效。

explain select *from customer where lcase(company)='abc'\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where

2.4 索引列类型转换，会导致索引生效。

explain select *from customer where phone=13012345678\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: ALLpossible_keys:index_phone,index_phone_name          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where

应该放弃原有类型，防止类型转换。

explain select *from customer where phone='13012345678'\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: refpossible_keys:index_phone,index_phone_name          key: index_phone      key_len: 123          ref: const         rows: 1     filtered: 100.00        Extra: NULL

2.5 索引列比拟字符集不统一时，会导致索引生效。

依照下面客户表的格局，创立两个供应商的表supplier、supplier_utf8，字符集别离为utf8mb4、utf8。对于字符集能够看看之前文章(聊一聊MySQL的字符集)。

CREATETABLE supplier (id INTAUTO_INCREMENT,companyVARCHAR(30),nameVARCHAR(30),sexenum('male','female'),age INT,phoneVARCHAR(30),addressVARCHAR(60),PRIMARYKEY (id));CREATEINDEX index_company ON supplier(company);CREATEINDEX index_age ON supplier(age);CREATEINDEX index_phone ON supplier(phone);CREATEINDEX index_phone_name ON supplier(phone,name);

CREATETABLE supplier_utf8 (id INTAUTO_INCREMENT,companyVARCHAR(30),nameVARCHAR(30),sexenum('male','female'),age INT,phoneVARCHAR(30),addressVARCHAR(60),PRIMARYKEY (id)) CHARSET=utf8;

CREATEINDEX index_company ON supplier_utf8(company);CREATEINDEX index_age ON supplier_utf8(age);CREATEINDEX index_phone ON supplier_utf8(phone);CREATEINDEX index_phone_name ON supplier_utf8(phone,name);

explain select *from customer,supplier where customer.company = supplier.company\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: ALLpossible_keys:index_company          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where***************************2. row ***************************           id: 1  select_type: SIMPLE        table: supplier   partitions: NULL         type: refpossible_keys:index_company          key: index_company      key_len: 123          ref: mydb.customer.company         rows: 1     filtered: 100.00        Extra: NULL

能够看到字符集一样的，一个表应用了索引，另一个表走了全表扫描。

explain select *from customer,supplier_utf8 where customer.company = supplier_utf8.company\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: ALLpossible_keys:index_company          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: NULL***************************2. row ***************************           id: 1  select_type: SIMPLE        table: supplier_utf8   partitions: NULL         type: ALLpossible_keys:index_company          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where; Using join buffer(hash join)

字符集不一样的，两个表都走了全表扫描。

这种状况隐蔽性比拟强，常常业务上线了才被发现。

2.6 应用or查问，不管另一项是否为索引列，都会导致索引生效。

explainselect * from customer where company='abc' or address='abc'\Gexplainselect * from customer where company='abc' or age=20\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: ALLpossible_keys:index_company          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where

不过如果非要应用or进行查问，能够利用MRR性能，对回表查问进行排序优化。

2.7 组合索引，没有应用第一列索引，会导致索引生效。

explain select *from customer where name='abc'\G***************************1. row ***************************           id: 1  select_type: SIMPLE        table: customer   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where

组合索引并不要求依照排列程序，上面能够用到索引。

explain select * from customer where name ='abc' and phone='13012345678'\G*************************** 1. row***************************           id: 1  select_type:SIMPLE        table:customer   partitions:NULL         type: refpossible_keys: index_phone,index_phone_name          key:index_phone      key_len: 123          ref:const         rows: 1     filtered:100.00        Extra:Using where

如果数据库预计应用全表扫描比应用索引快，则不应用索引。MySQL最新版本中，对索引判断有了很大改善，之前版本应用in,!= ，<>，is null，is not null会导致索引生效，最新版测试，还是应用了索引查问。

MySQL5.6引入了MRR(Multi-Range Read Optimization)，专门来优化：二级索引的范畴扫描并且须要回表的状况。

它的原理是，将多个须要回表的二级索引依据主键进行排序，而后一起回表，将原来的回表时进行的随机I/O，转变成程序I/O，升高查问过程中的I/O开销，同时缩小缓冲池中数据页被替换的频次。

MySQL5.7引入了Generated Column，如果的确须要对索引列进行计算等操作，能够采纳虚构列的形式来解决，比方创立客户表时减少对应的列，公司名称小写lcase_company，和实岁full_age, 并建设索引。

CREATE TABLE customer2(id INT AUTO_INCREMENT,company VARCHAR(30),lcase_company VARCHAR(30)as(lcase(company)),name VARCHAR(30),sex enum('male','female'),age INT,full_age INT as(age-1),phone VARCHAR(30),address VARCHAR(60),PRIMARY KEY (id));

CREATE INDEX index_lcase_company ON customer2(lcase_company);CREATE INDEX index_full_age ON customer2(full_age);

之后间接对Cenerated Column进行查问，就相当于计算列用到了索引。

三、索引的应用标准

在应用索引的时候，不仅要留神防止索引生效，也要遵循肯定的标准，以便高效的应用索引。

上面总结了一些标准倡议，能够用来参考，并非绝对真理。

3.1 单表的索引数倡议不超过5个，组合索引的字段原则上不超过3个。

3.2 尽量不要在较长字符串的字段上建设索引，能够设置索引字段前缀长度。

3.3 抉择在查问过滤中使用率较高，如where，orderby，group by的列建设索引。

3.4 不要在区分度不高的列上建设索引，比方性别等，利用不了索引性能。

3.5 不要在常常更新的列上建设索引，数据更新也会更新索引，影响数据库性能。

3.6 建设组合索引时，区分度最高，或者查问频率最高的，放在最左侧。

3.7 正当利用笼罩索引来满足查问要求，防止回表查问，缩小I/O开销。

3.8 删除不再应用、少应用、或者反复的索引，缩小数据更新的开销。

3.9 利用explain来判断查问语句，是应用了索引，还是走了全表扫描。

KunlunDB我的项目已开源

【GitHub：】
https://github.com/zettadb

【Gitee：】
https://gitee.com/zettadb

END