一、单表索引失效的几种情况

建立员工记录表

CREATE TABLE `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `pos` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

 给表中name,age,pos字段添加索引(注意三个字段的顺序)

alter table staff
add index idx_staffs_nameAgePos(name,age,pos)

①最佳左前缀法则

指的是查询从索引的最左前列开始并且不跳过索引中的列

（带头大哥不能死，中间兄弟不能断)

（火车头带着车身跑，不能没有火车头，中间也不能缺少一段火车身）

第一种情况：name字段索引一直被使用

EXPLAIN SELECT * FROM staffs WHERE name = '小米'
EXPLAIN SELECT * FROM staffs WHERE name = '小米' AND age=25
EXPLAIN SELECT * FROM staffs WHERE name = '小米' AND age=25 AND pos='开发'

  上图可知，idx_staffs_nameAgePos索引有三个字段，name，age，pos

其中，只查找name可以使用索引

只查找name和age也可以使用索引

查找name，age，pos三个字段也可以使用索引

第二种情况，当不查找name而去查找后面age和pos字段时

  索引失效了

第三种情况，查找了name字段和pos字段，而缺少了中间的age字段

 上图对比了分别查找name字段和name,pos字段的两种情况，发现虽然都用到了idx_staffs_nameAgePos索引，但是key_len长度没变，ref索引用到的字段也没变，说明针对字段pos的索引失效了，原因是缺少了中间的age索引字段

②不要再索引列上做任何操作

不要在索引列上左任何操作(计算，函数，自动or手动类型转换),会导致索引失效转换为全表扫描

上图可以看到，没用left函数之前，是一个正常的索引引用，使用函数之后，索引失效了

③存储引擎不能使用索引中范围条件右边的列

与上面单表索引优化分析中的例子一样

查询中间使用了范围条件，结果导致pos的索引失效

按照BTree索引的工作原理，先排序name,如果遇到相同的name则再排序age，如果遇到相同的age则再排序pos，当age字段在复合索引中间时，因age>25条件是一个范围值（所谓range），MySQL无法利用索引再对后面的pos部分进行建索，即range类型查询字段后面的索引无效

④尽量使用覆盖索引

只访问索引列的查询，索引列和查询列一致，避免select *

  当只访问了索引列后，Extra中出现了Using index，表明是覆盖查询，这是好的

⑤使用不等于（!=或者<>）的时候

mysql 在使用不等于(!= 或者<>)时候，有时无法使用索引会导致全表扫描

⑥ 字段的is not null 和 is null

 当字段允许为null 的条件下：

 is not null 用不到索引，is null 可以用到索引。

⑦like以通配符(%)开头的索引会失效变成全表扫描

通配符出现最前面说明所有都适配，所有都要扫描

  不以%开头则不失效

 如何解决？

可以使用覆盖索引

staffs表中有id,name,age,pos四个字段，现在对name,age字段设置单独索引,id自动为主键索引

那么查询id,name,age字段都是覆盖索引，都可以使索引生效

但是如果使用explain select * from tb1_user where name like '%aa%'就会索引失效，因为此时不是覆盖索引，不是覆盖索引使用%会失效

⑧字符串不加单引号会导致索引失效

name字段为varchar类型

假设有一行name=‘2000’，这时如果用name=2000条件查找也会有结果

用EXPLAIN分析

 可以看出，用name=2000作为判断条件，不加单引号导致索引失效了，为什么？

因为name=2000也可以查出来是因为MySQL在底层做了一次类型转换，把整形2000转换成了字符串’2000’

违背了上面第②条的不要在索引列上左任何操作的原则，所以索引失效了

练习

假设index(a,b,c)

Where 语句	索引是否被使用
Where a =3	Y,使用到a
where a =3 and b = 5	Y，使用到a，b
where a = 3 and b=5 and c=4	Y，使用到a,b,c
where b =3 或者where b= 3 and c=4 或者where c=4	N
where a=3 and b=3 and c=5	使用a，但是c不可以，b中间断了
where a is null and b is not null	is null 支持索引，但是is not null 不支持，所以a 可以使用索引，但是b不可以使用
where a <> 3	不能使用索引
where abs(a)=3	不能使用索引
where a =3 and b like ’kk%’; and c=4	Y,使用到a,b,c
where a=3 and b like ‘%kk’ and c=4	Y，只使用到a
where a=3 and b like‘%kk%’ and c =4	Y,只用到a
where a =3 and b like’k%kk%’ and c=4	Y,使用到a，b，c

---

二、关联查询优化

1、建表语句

CREATE TABLE IF NOT EXISTS `class` ( 
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
`card` INT(10) UNSIGNED NOT NULL,
 PRIMARY KEY (`id`) );
 
 CREATE TABLE IF NOT EXISTS `book` (
 bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
 `card` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`bookid`) );

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

2、left join（必定有一张需要全表扫描，选择哪张？？）

①EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

 如何优化？在哪个表上建立索引？

ALTER TABLE `book` ADD INDEX  idx_card( `card`);

 删除book标的索引 ，drop index idx_card on book;
在class表上建立索引：alert table_class add index idx_card(card);

 结论：
① 在优化关联查询的时候，只有在被驱动表上建立索引才有效
② left join 时，左侧为驱动表，右侧的为被驱动表

3、inner join

EXPLAIN SELECT * FROM book inner join class on class.card=book.card;

 两个查询字段调换顺序，发现结果也是一样的！

 在book表中，删除9条记录

 结论：inner join 时，mysql会自己帮你把小结果集的表选为驱动表

straight_join :效果和inner join一样，但是会强制将左作为驱动表

结论：能够直接多表关联的尽量直接关联，不用子查询 

---

三、子查询优化

取所有不为掌门人的员工，按年两分组：

select age as '年龄', count(*) as '人数' from t_emp where id not in
(select ceo from t_dept where ceo is not null) group by age;

如何优化？

解决dept表的全表扫描，建立ceo字段的索引：

 此时再查询：

 进一步优化，替换 not in
我们可以替换为：

select age as '年龄',count(*) as '人数' 
from emp e 
left join dept d on e.id=d.ceo 
where d.id is null group by age;

 **结论：**在范围判断时，尽量别实用not in 和not exist，使用left join on xxx is null 替代

---

四、排序分组优化

本文只是个人笔记，如有错误还望提醒

最佳左前缀法则

范围查询右边失效

like索引失效原理

隐式转换造成的索引失效

看这个表

CREATE TABLE `test1` (
    `id` int(11) NOT NULL,
    `num1` int(11) NOT NULL DEFAULT '0',
    `num2` varchar(11) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    KEY `num1` (`num1`),
    KEY `num2` (`num2`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL 测试
先来看这组 SQL，一共四条，我们的测试数据表num1是int类型，num2是varchar类型，但是存储的数据都是跟主键id一样的顺序数字，两个字段都建立有索引。

1: SELECT * FROM `test1` WHERE num1 = 10000;
2: SELECT * FROM `test1` WHERE num1 = '10000';
3: SELECT * FROM `test1` WHERE num2 = 10000;
4: SELECT * FROM `test1` WHERE num2 = '10000';

其中 124 三条 SQL 基本都是瞬间出结果，大概在 0.001 ~ 0.005 秒，在千万级的数据量下这样的结果可以判定这三条 SQL 性能基本没差别了。但是第三条 SQL，多次测试耗时基本在 4.5~4.8 秒之间。
为什么 34 两条 SQL 效率相差那么大，但是同样做对比的 12 两条 SQL 却没什么差别呢？查看一下执行计划，下边分别 1234 条 SQL 的执行计划数据：
可以看到，124 三条 SQL 都能使用到索引，连接类型都为ref，扫描行数都为 1，所以效率非常高。再看看第三条 SQL，没有用上索引，所以为全表扫描，rows直接到达 1000 万了，所以性能差别才那么大。
查阅 MySQL 相关文档发现是隐式转换造成的，看一下官方的描述：

官方文档： 12.2 Type Conversion in Expression Evaluation
当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL会根据需要自动将字符串转换为数字，反之亦然。以下规则描述了比较操作的转换方式：

1. 两个参数至少有一个是NULL时，比较的结果也是NULL，特殊的情况是使用<=>对两个NULL做比较时会返回1，这两种情况都不需要做类型转换
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
3. 两个参数都是整数，按照整数来比较，不做类型转换
4. 十六进制的值和非数字做比较时，会被当做二进制串
5. 有一个参数是TIMESTAMP或DATETIME，并且另外一个参数是常量，常量会被转换为timestamp
6. 有一个参数是decimal类型，如果另外一个参数是decimal或者整数，会将整数转换为decimal后进行比较，如果另外一个参数是浮点数，则会把decimal转换为浮点数进行比较
7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较
   根据官方文档的描述，我们的第 23 两条 SQL 都发生了隐式转换，第 2 条 SQL 的查询条件num1 = ‘10000’，左边是int类型右边是字符串，第 3 条 SQL 相反，那么根据官方转换规则第 7 条，左右两边都会转换为浮点数再进行比较。

先看第 2 条 SQL：SELECT * FROMtest1WHERE num1 = ‘10000’; 左边为 int 类型10000，转换为浮点数还是10000，右边字符串类型’10000’，转换为浮点数也是10000。两边的转换结果都是唯一确定的，所以不影响使用索引。

第 3 条 SQL：SELECT * FROMtest1WHERE num2 = 10000; 左边是字符串类型’10000’，转浮点数为 10000 是唯一的，右边int类型10000转换结果也是唯一的。但是，因为左边是检索条件，‘10000’转到10000虽然是唯一，但是其他字符串也可以转换为10000，比如’10000a’，‘010000’，'10000’等等都能转为浮点数10000，这样的情况下，是不能用到索引的。
查阅相关资料发现规则如下：

1. 不以数字开头的字符串都将转换为0。如’abc’、‘a123bc’、'abc123’都会转化为0；
2. 以数字开头的字符串转换时会进行截取，从第一个字符截取到第一个非数字内容为止。比如’123abc’会转换为123，'012abc’会转换为012也就是12，'5.3a66b78c’会转换为5.3，其他同理。

分析和总结

通过上面的测试我们发现 MySQL 使用操作符的一些特性：

1. 当操作符左右两边的数据类型不一致时，会发生隐式转换。
2. 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
3. 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
4. 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

所以，我们在写 SQL 时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描。

字符集和排序规则不相同导致索引失效

其它：

1. 最好全值匹配
2. 不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描

explain select * from ti where b+1=5;  -- 对字段操作走不了索引

3. 尽量使用覆盖索引（只查询索引的列（索引列和查询列一致）），减少select *
4. 索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
5. 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
6. 索引字段使用 or 时，会导致索引失效而转向全表扫描

1、如何判断索引是否生效？

答：在查询语句前加上explain。
explain函数作用：显示了MYSQL如何使用索引来处理select语句以及连接表。

explain select id , name table where name like 'abc%'

2、索引失效的情况

• 2.1、条件中有or

• 2.2、like以%开头

• 2.3、存在索引列的数据类型隐式转换

• 2.4、where子句中对索引列有数学运算

2.5、where 子句里对有索引列使用函数，用不上索引

• 2.6、复合索引未用左列字段

3、为什么Mysql用B+树做索引而不用B-树或红黑树？

答：查询效率是由磁盘IO的次数决定。

B-树：

每个节点都有数据域，增加了节点的大小，磁盘的IO次数也增加（因为一次磁盘IO读出的数据量大小是固定的，每次读出的数据少，IO次数就会增加）。

B+树相对B树的优点：

1、B+树的磁盘读写代价更低。

因为只有叶子节点存放数据，非叶子节点只存放索引，B树的每个索引节点都有data域。

2、B+树带有顺序访问指针。

所有的叶子节点之间有一个链指针，每个叶子节点都代表一个区间，从左到右也是有顺序的。而在数据库中基于范围的查询是非常频繁的，B树不支持这种遍历操作。

3、B+树的查询效率更加稳定。

由于非叶子节点存放的是数据的索引，而真正的数据存放在叶子节点中。而关键字查询的路径长度相同，因此每个数据的查询效率相当。
B树的非叶子节点也存放数据，因此不同的数据在不同层，查询效率也有高有低。

红黑树

红黑树是二叉查找树，在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。

而B+树是多路查找树，树的深度比红黑树小得多，而磁盘IO的次数和树的深度有很大关系。

综上

B+树相对于红黑树的优点：

1、高度比红黑树小，有效地减少了磁盘的随机访问；

2、B+树的数据节点临近，因此能发挥磁盘顺序读取的优势；

3、B+树的数据全部存在叶子节点，存储浪费小。

红黑树和B+树的比较：

1、使用场合。红黑树常用于存储内存中的有序数据，增删很快；B+树常用于文件系统和数据库索引。
2、子节点数量。红黑树只有两个子节点，而B+树有多个子节点，因此存储相同量的数据，B+树的高度更小。