关于mysql:mysql索引失效的情况

上篇文章提到，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 时，代表走了索引。

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

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

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

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

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

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

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

explain select * from t_user where age = 25;

比方上面这条语句查问条件中对 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)));

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

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

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

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

咱们个别应用 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%';

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

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

能够先理解 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;

关于mysql:mysql索引失效的情况

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

2. 对索引应用函数

3. WHERE 子句中的 OR

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

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

5.order by 索引生效