上篇文章提到，InnoDb默认应用B+树作为索引存储构造。然而有时候就会呈现一种状况：明明在字段上增加了索引，但却并未失效。

本篇则用实例来讲述一下mysql索引生效的状况。

咱们先创立一个表：有 username、age、address 等字段, 并且建设了 (username)、(age,address)等字段

CREATE TABLE t_user (    id INT AUTO_INCREMENT PRIMARY KEY,    age INT,    username VARCHAR(255),    address VARCHAR(255),    INDEX idx_username (username),    INDEX idx_age_address (age, address));

再插入几条数据:

INSERT INTO t_user (age, username, address)VALUES(25, 'person1', '北京'),(30, 'person2', '上海'),(22, 'person3', '广州'),(28, 'person4', '深圳'),(35, 'person5', '成都'),(40, 'person6', '重庆'),(29, 'person7', '杭州'),(26, 'person8', '武汉'),(31, 'person9', '南京'),(27, 'person10', '西安');

如何理解 select 语句是否走了索引呢？咱们能够应用 explain 语句。

explain select * from t_user where id = 1;

当 key 不为null时，代表走了索引。

那么，索引生效的状况有哪些呢？

1.联结索引不满足最左匹配准则

联结索引听从最左匹配准则，顾名思义，在联结索引中，最左侧的字段优先匹配。因而，在创立联结索引时，where子句中应用最频繁的字段放在组合索引的最左侧。

而在查问时，要想让查问条件走索引，则需满足：最右边的字段要呈现在查问条件中。

比方咱们方才建设了 (age, address)联结索引, 索引树是依照 age 排序之后，再依照address排序

当咱们只用 address 查问的时候，就走不了这条联结索引

explain select * from t_user where address = '北京';

而用 age 查问的时候，就能够走这条索引

explain select * from t_user where age = 25;

2.对索引应用函数

比方上面这条语句查问条件中对 name 字段应用了 LENGTH 函数，执行打算中的 type=ALL，代表了全表扫描

EXPLAIN select * from t_user where length(username) = 7;

从 MySQL 8.0 开始，索引个性减少了函数索引，即能够针对函数计算后的值建设一个索引，也就是说该索引的值是函数计算后的值，所以就能够通过扫描索引来查问数据。

通过上面这条语句，对 length(name) 的计算结果建设一个名为 idx_name_length 的索引。就能够应用索引了。

alter table t_user add key idx_name_length ((length(username)));

3. WHERE 子句中的 OR

比方咱们应用 or 进行剖析，id有索引。address 没有索引

explain select * from t_user where id = 2 or address = '上海';

能够看到，即便id有索引也是走全表扫描。

因为 address 字段作为条件很显然是全表扫描，既然曾经进行了全表扫描了，后面id的条件再走一次索引反而是节约了。所以，在应用or关键字时，切记两个条件都要增加索引，否则会导致索引生效，走全表扫描。

对索引应用Like左或者左右含糊匹配

咱们个别应用like应用形式有这几种，左含糊，右含糊，左右含糊

  • like '%abc';
  • like 'abc%';
  • like '%abc%';

而应用左或者左右含糊是无奈走索引的，会走全表扫描。

如果索引关键字的类型是字符串类型，索引的排列程序是依据比拟字符串的首字母排序的，如果首字母雷同，就依据比拟第二个字母进行排序，以此类推。

比方咱们要找 "absne"， 咱们就须要判断第一个字符“a”须要往左边走， 再判断第二个字符"b"应该往右走。

所以当应用左含糊或者左右含糊时，就无奈依据字符一一走上来。

左含糊：走全表扫描

explain select * from t_user where username like '%son';

右含糊：走索引

explain select * from t_user where username like 'person%';

左右含糊：走全表扫描

explain select * from t_user where username like '%son%';

4.对索引进行表达式计算

在查问条件中对索引进行表达式计算，也是无奈走索引的。

explain select * from t_user where id + 1 = 10;

5.order by索引生效

能够先理解 order by 怎么工作的。

order by 分为 全字段排序 和 rowid排序

MySQL会给每个线程调配一块内存用于排序，称为 sort_buffer。 而走哪个排序，正取决与这个内存的大小。

select city,name,age from t where city='杭州' order by name limit 1000  ;

当内存足够时，会优先走全字段索引，把二级索引对应获取到主键索引，再把主意索引都装入sort_buffer中，而后进行排序，排序后到后果集就是最终后果。

当内存不够时，会走rowid排序, 只把orderby的字段以及主键id装入sort_buffer中， 依照name排序，在依据主键id进行回表，最初就是后果集。 也就是说：rowid排序会多进行一次回表

explain select * from t_user order by username ;

从extra里呈现了Using filesort，阐明这里是没有走索引的，而且type为ALL，阐明进行了一次全表扫描。

username建设了索引，然而orderby仍旧走全表扫描。

orderBy是否应用索引比较复杂。

MySQL 的优化器可能会依据查问的复杂性和表的大小抉择执行打算。在某些状况下，全表扫描可能比应用索引更快，尤其是当表很小或索引不够浓密时。

优化器的地位如下。

所以mysql通过各种因素剖析，即便username有索引, 也走全表扫描，这并不一定意味着查问性能会受到重大侵害，特地是当表的大小较小或查问性能曾经足够快时。查问性能优化是一个综合思考多个因素的简单工作。

在咱们特地清晰该sql的目标的状况下，能够应用强制索引

EXPLAIN SELECT * FROM t_user FORCE INDEX (idx_username) ORDER BY username;

同时，笼罩索引也是一种很好的形式，因为username曾经是索引，索引树曾经是排序好的。

EXPLAIN SELECT username FROM t_user ORDER BY username;