MySQL之索引失效情况

目录

• 1 MySQL索引
  • 1.1 简介
    • 1.1.1 索引基础
  • 1.2 SQL优化
    • 1.2.1 查看执行计划
    • 1.2.2 show profile分析
    • 1.2.3 trace
• 2 索引失效
  • 2.1 准备工作
    • 2.1.1 创建user表
    • 2.1.2 插入数据
    • 2.1.3 查看数据库版本
  • 2.2 联合索引
    • 2.2.1 联合索引中索引有效
    • 2.2.2 联合索引中索引失效
    • 2.2.3 MySQL 8中索引有效
  • 2.3 select *
  • 2.4 索引列上有计算
  • 2.5 索引列用了函数
  • 2.6 字段类型不同
  • 2.7 like左边包含%
  • 2.8 列对比
  • 2.9 使用or关键字
  • 2.10 not in和not exists
    • 2.10.1 in关键字
      • 2.10.1.1 delete + in子查询不走索引
    • 2.10.2 exists关键字
    • 2.10.3 not in关键字
    • 2.10.4 not exists关键字
  • 2.11 order by的坑
    • 2.11.1 哪些情况走索引
      • 2.11.1.1 配合where一起使用
      • 2.11.1.2 两者都有
    • 2.11.2 哪些情况不走索引
      • 2.11.2.1 没加where
      • 2.11.2.2 只用limit
      • 2.11.2.3 混合索引排序
    • 2.11.3 order by文件排序效率很低
      • 2.11.3.1 rowid排序
      • 2.11.3.2 全字段排序
  • 2.12 左右连接问题
    • 2.12.1 join或者子查询过多
      • 2.12.1.1 join大小表问题
    • 2.12.2 关联的字段编码格式不一样
    • 2.12.3 关联更新、删除
    • 2.12.4 提前缩小范围
  • 2.13 优化器选错了索引
  • 2.14 索引合并
    • 2.14.1 什么是索引合并机制
    • 2.14.2 索引合并机的类型
      • 2.14.2.1 索引合并-取交集
      • 2.14.2.2 索引合并-取并集
      • 2.14.2.3 索引合并-取有序并集
    • 2.14.3 索引合并机制的开关
  • 2.15 count(1)、count(*) 与 count(列名)区别
    • 2.15.1 count(1) and count(*)
    • 2.15.2 count(1) and count(字段)
    • 2.15.3 count(*) 和 count(1)和count(列名)区别
    • 2.15.4 执行效率区别
  • 2.16 Null对索引影响
    • 2.16.1 简介
    • 2.16.2 操作Null
    • 2.16.3 使用Null的影响
    • 2.16.4 Null列对mysql索引影响

1 MySQL索引

1.1 简介

1.1.1 索引基础

点击了解索引创建，分类等相关知识

1.2 SQL优化

1.2.1 查看执行计划

explain或desc这个命令来查看一个这些SQL语句的执行计划，就是为了分析耗时，是否走索引
查看SQL是否使用索引，前面加上explain或desc即可，在Oracle中是explain plan for命令查看索引执行计划，还得紧接着查询表才有结果select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

explain select * from emp where name = 'Jefabc'

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra：
概要描述：

• id:选择标识符
• select_type:表示查询的类型。
• table:输出结果集的表
• partitions:匹配的分区
• type:表示表的连接类型
• possible_keys:表示查询时，可能使用的索引
• key:表示实际使用的索引
• key_len:索引字段的长度
• ref:列与索引的比较
• rows:扫描出的行数(估算的行数)
• filtered:按表条件过滤的行百分比
• Extra:执行情况的描述和说明

type：查询中使用了何种类型，结果值从最好到最坏：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
一般来说，得保证查询至少达到range级别，最好能到达ref

• null : MySQL不访问任何表或索引，直接返回结果
• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现
• const：表示通过索引一次就能够找到，表示使用了主键或者唯一索引与常量值进行比较
• eq_ref：唯一性索引扫描，对于每个索引键，表示只有一条记录与之匹配，常见于主键或唯一索引扫描，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行，返回单条记录，常出现在关联查询中，虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描
• range：采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式
• index：虽然index比all快（index是从索引中读取，all是从硬盘中读取），但是index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大，所以，要尽量避免全表扫描和全索引扫描。
• all：遍历全表才能找到

Extra：执行情况的描述和说明

• Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
• Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化
• Using index：表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错，如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。
• Using where：表明使用了where进行过滤
• impossible where：如果where子句的值总是false，不能用来获取任何元组
• Using join buffer：使用了连接缓存
• Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
• select table optimized away：在没有group by子句的情况下，基于索引优化min/max操作或者对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进行计算

1.2.2 show profile分析

了解SQL执行的线程的状态及消耗的时。
默认是关闭的，开启语句set profiling = 1;

SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

1.2.3 trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

2 索引失效

2.1 准备工作

2.1.1 创建user表

创建一张user表，表中包含：id、code、age、name和height字段

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

此外，还创建了三个索引：

• id：数据库的主键
• idx_code_age_name：由code、age和name三个字段组成的联合索引。
• idx_height：普通索引

2.1.2 插入数据

为了方便给大家做演示，我特意向user表中插入了3条数据

INSERT INTO `user` (id, CODE, age, NAME, height,address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO `user` (id, CODE, age, NAME, height,address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO `user` (id, CODE, age, NAME, height,address) VALUES (3, '103', 23, '苏三', 174,'成都');

2.1.3 查看数据库版本

为了防止以后出现不必要的误会，在这里有必要查一下当前数据库的版本。

select version();
查出当前的mysql版本号为：8.0.21

2.2 联合索引

之前我已经给code、age和name这3个字段建好联合索引：idx_code_age_name。
该索引字段的顺序是：

• code
• age
• name

如果在使用联合索引时，没注意最左前缀原则，很有可能导致索引失效

注意：特例条件，如果除了主键剩下的列都是联合索引，会导致最左匹配失效，而依然走索引

2.2.1 联合索引中索引有效

explain select * from user where code='101';

explain select * from user where code='101' and age=21 

explain select * from user where code='101' and age=21 and name='周星驰';

上面三种情况，sql都能正常走索引，但还有一种比较特殊的场景

explain select * from user where code = '101'  and name='周星驰';

查询条件原本的顺序是：code、age、name，但这里只有code和name中间断层了，掉了age字段，这种情况也能走code字段上的索引
这4条sql中都有code字段，它是索引字段中的第一个字段，也就是最左边的字段。只要有这个字段在，该sql已经就能走索引。
这就是我们所说的 最左匹配原则

2.2.2 联合索引中索引失效

前面我已经介绍过，建立了联合索引后，在查询条件中有哪些情况索引是有效的。
接下来，我们重点看看哪些情况下索引会失效

explain select * from user where age=21;
explain select * from user where name='周星驰';
explain select * from user where age=21 and name='周星驰'

从图中看出这3种情况下索引确实失效了。

说明以上3种情况不满足最左匹配原则，说白了是因为查询条件中，没有包含给定字段最左边的索引字段，即字段code

2.2.3 MySQL 8中索引有效

MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，没有任何毛病。但是在MySQL 8.0中，就不一定了

MySQL 8.0.13 版本中，对于range查询，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描

点此了解MySQL中联合索引数据结构分析

2.3 select *

在《阿里巴巴开发手册》中明确说过，查询sql中禁止使用select *
那么，你知道为什么吗？

explain select * from user where name='苏三';

执行结果：

在该sql中用了select *，从执行结果看，走了全表扫描，没有用到任何索引，查询效率是非常低的。
如果查询的时候，只查我们真正需要的列，而不查所有列，结果会怎么样？

explain  select code,name from user  where name='苏三';

执行结果：

从图中执行结果不难看出，该sql语句这次走了全索引扫描，比全表扫描效率更高。

其实这里用到了：覆盖索引
如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。
而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。

2.4 索引列上有计算

介绍本章节内容前，先跟大家一起回顾一下，根据id查询数据的sql语句：

explain select * from user where id=1;

执行结果：

从图中可以看出，由于id字段是主键，该sql语句用到了主键索引。
但如果id列上面有计算，比如：

explain select * from user where id+1=2;

执行结果：

从上图中的执行结果，能够非常清楚的看出，该id字段的主键索引，在有计算的情况下失效了。

2.5 索引列用了函数

有时候我们在某条sql语句的查询条件中，需要使用函数，比如：截取某个字段的长度。
假如现在有个需求：想查出所有身高是17开头的人，如果sql语句写成这样：

explain select * from user  where height=17;

该sql语句确实用到了普通索引：

但该sql语句肯定是有问题的，因为它只能查出身高正好等于17的，但对于174这种情况，它没办法查出来。

为了满足上面的要求，我们需要把sql语句稍稍改造了一下：

explain select * from user  where SUBSTR(height,1,2)=17;

这时需要用到SUBSTR函数，用它截取了height字段的前面两位字符，从第一个字符开始。
执行结果：

在使用该函数之后，该sql语句竟然走了全表扫描，索引失效了。

2.6 字段类型不同

在sql语句中因为字段类型不同，而导致索引失效的问题，很容易遇到，可能是我们日常工作中最容易忽略的问题。

注意观察一下user表中的code字段，它是varchar字符类型的。
在sql语句中查询数据时，查询条件我们可以写成这样：

explain select * from user where code="101";

执行结果：

从上图中看到，该code字段走了索引。
温馨提醒一下，查询字符字段时，用双引号“和单引号'都可以。
MySQL中单双引号分析
但如果你在写sql时，不小心把引号弄掉了，把sql语句变成了：

explain select * from user where code=101;

执行结果：

你会发现，该sql语句竟然变成了全表扫描。因为少写了引号，这种小小的失误，竟然让code字段上的索引失效了。

为什么索引会失效呢？
因为code字段的类型是varchar，而传参的类型是int，两种类型不同。

此外，还有一个有趣的现象，如果int类型的height字段，在查询时加了引号条件，却还可以走索引：

explain select * from user  where height='175';

执行结果：

从图中看出该sql语句确实走了索引。int类型的参数，不管在查询时加没加引号，都能走索引。
mysql发现如果是int类型字段作为查询条件时，它会自动将该字段的传参进行隐式转换，把字符串转换成int类型。
mysql会把上面列子中的字符串175，转换成数字175，所以仍然能走索引。

接下来，看一个更有趣的sql语句：

select 1 + '1';

结果是2。
mysql自动把字符串1，转换成了int类型的1，然后变成了：1+1=2

但如果你确实想拼接字符串该怎么办？可以使用concat关键字。
具体拼接sql如下：

select concat(1,'1');

接下来，关键问题来了：为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？
根据mysql官网上解释，字符串'1'、' 1 '、'1a'都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么，mysql怎么知道该把int类型的1转换成哪种字符串，用哪个索引快速查值?
感兴趣的小伙伴可以再看看官方文档：https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

2.7 like左边包含%

模糊查询，在我们日常的工作中，使用频率还是比较高的。
比如现在有个需求：想查询姓李的同学有哪些?
使用like语句可以很快的实现：

select * from user where name like '李%';

但如果like用的不好，就可能会出现性能问题，因为有时候它的索引会失效。
目前like查询主要有三种情况：

like '%a'
like 'a%'
like '%a%'

假如现在有个需求：想查出所有code是10开头的用户。sql语句如下：

explain select * from user where code like '10%';

执行结果：

图中看出这种%在10右边时走了索引。
而如果把需求改了：想出现出所有code是1结尾的用户。
查询sql语句改为：

explain select * from user where code like '%1';

执行结果：

从图中看出这种%在1左边时，code字段上索引失效了，该sql变成了全表扫描。

此外，如果出现以下sql：

explain select * from user where code like '%1%';

该sql语句的索引也会失效。
下面用一句话总结一下规律：当like语句中的%，出现在查询条件的左边时，索引会失效。

为什么会出现这种现象呢？
其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。
我们在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。

2.8 列对比

上面的内容都是常规需求，接下来，来点不一样的。
假如我们现在有这样一个需求：过滤出表中某两列值相同的记录。比如user表中id字段和height字段，查询出这两个字段中值相同的记录。
这个需求很简单，sql可以这样写：

explain select * from user  where id=height

执行结果：

为什么会出现索引失效这种结果
id字段本身是有主键索引的，同时height字段也建了普通索引的，并且两个字段都是int类型，类型是一样的。
但如果把两个单独建了索引的列，用来做列对比时索引会失效

2.9 使用or关键字

我们平时在写查询sql时，使用or关键字的场景非常多，但如果你稍不注意，就可能让已有的索引失效。
某天你遇到这样一个需求：想查一下id=1或者height=175的用户。

explain select * from user  where id=1 or height='175';

执行结果：

没错，这次确实走了索引，因为刚好id和height字段都建了索引。
假如需求改了：除了前面的查询条件之后，还想加一个address='成都'。

explain select * from user where id=1 or height='175' or address='成都';

执行结果：

结果悲剧了，之前的索引都失效了。
因为你最后加的address字段没有加索引，从而导致其他字段的索引都失效了。

注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。

2.10 not in和not exists

在我们日常工作中用得也比较多的，还有范围查询，常见的有：

• in
• exists
• not in
• not exists
• between and

今天重点聊聊前面四种，区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。

2.10.1 in关键字

假如我们想查出height在某些范围之内的用户，这时sql语句可以这样写：

explain select * from user where height in (173,174,175,176);

执行结果：

从图中可以看出，sql语句中用in关键字是走了索引的。

如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行

2.10.1.1 delete + in子查询不走索引

之前见到过一个生产慢SQL问题，当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select + in子查询，却可以走索引。
当时的MySQL版本是5.7，假设当前有两张表account和old_account,表结构如下

CREATE TABLE `old_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的账户表';

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

执行的SQL如下：

delete from account where name in (select name from old_account);

查看执行计划，发现不走索引：

但是如果把delete换成select，就会走索引。如下：

为什么select + in子查询会走索引，delete + in子查询却不会走索引呢？
我们执行以下SQL看看：

explain select * from account where name in (select name from old_account);
show WARNINGS; //可以查看优化后,最终执行的sql

结果如下：

select `test2`.`account`.`id` AS `id`,`test2`.`account`.`name` AS `name`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS `create_time`,`test2`.`account`.`update_time` AS `update_time` from `test2`.`account` 
semi join (`test2`.`old_account`)
where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)

可以发现，实际执行的时候，MySQL对select in子查询做了优化，把子查询改成join的方式，所以可以走索引。但是很遗憾，对于delete in子查询，MySQL却没有对它做这个优化

2.10.2 exists关键字

有时候使用in关键字时性能不好，这时就能用exists关键字优化sql了，该关键字能达到in关键字相同的效果：

explain select * from user  t1 
where  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

执行结果：

从图中可以看出，用exists关键字同样走了索引。

2.10.3 not in关键字

上面演示的两个例子是正向的范围，即在某些范围之内。
那么反向的范围，即不在某些范围之内，能走索引不？

explain select * from user where height not in (173,174,175,176);

执行结果：

索引失效了。
看如果现在需求改了：想查一下id不等于1、2、3的用户有哪些，这时sql语句可以改成这样：

explain select * from user where id  not in (173,174,175,176);

执行结果：

可能会惊奇的发现，主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。

2.10.4 not exists关键字

除此之外，如果sql语句中使用not exists时，索引也会失效。具体sql语句如下：

explain select * from user  t1
where  not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

执行结果：

从图中看出sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。

2.11 order by的坑

在sql语句中，对查询结果进行排序是非常常见的需求，一般情况下我们用关键字：order by就能搞定。
但order by挺难用的，它跟where或者limit关键字有很多千丝万缕的联系，一不小心就会出问题。

2.11.1 哪些情况走索引

一起看看order by的哪些情况可以走索引。
我之前说过，在code、age和name这3个字段上，已经建了联合索引：idx_code_age_name

2.11.1.1 配合where一起使用

order by还能配合where一起遵循最左匹配原则。

explain select * from user where code='101' order by age;

执行结果：

code是联合索引的第一个字段，在where中使用了，而age是联合索引的第二个字段，在order by中接着使用。
假如中间断层了，sql语句变成这样，执行结果会是什么呢？

explain select * from user where code='101' order by name;

执行结果：

虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。

2.11.1.2 两者都有

如果某个联合索引字段，在where和order by中都有，结果会怎么样？

explain select * from user where code='101' order by code, name;

执行结果：

code字段在where和order by中都有，对于这种情况，从图中的结果看出，还是能走了索引的。

2.11.2 哪些情况不走索引

前面介绍的都是正面的用法，是为了让大家更容易接受下面反面的用法。
好了，接下来，重点聊聊order by的哪些情况下不走索引？

2.11.2.1 没加where

如果order by语句中没有加where关键字，该sql语句将不会走索引。

explain select * from user order by code, name;

执行结果：

从图中看出索引真的失效了。

2.11.2.2 只用limit

只是用limit也不走索引
order by后面的条件，也要遵循联合索引的最左匹配原则。具体有以下sql：

explain select * from user order by code limit 100;

explain select * from user order by code,age limit 100;

explain select * from user order by code,age,name limit 100;

执行结果：

从图中看出这3条sql都不能正常走索引
转载于：https://mp.weixin.qq.com/s/rjIT8LJo_ie8YWceoWBySA

2.11.2.3 混合索引排序

MySQL 不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。

SELECT *
FROM   my_order o
       INNER JOIN my_appraise a ON a.orderid = o.id
ORDER  BY a.is_reply ASC,
         a.appraise_time DESC
LIMIT  0, 20

执行计划显示为全表扫描：
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+--------+-------------+---------+---------+---------------+---------+-+
| 1 | SIMPLE | a | ALL | idx_orderid | NULL | NULL | NULL | 1967647 | Using filesort |
|  1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 122     | a.orderid |       1 | NULL |
+----+-------------+-------+--------+---------+---------+---------+-----------------+---------+-+

由于 is_reply 只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。

SELECT *
FROM  ((SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 0
         ORDER  BY appraise_time DESC
         LIMIT  0, 20)
        UNION ALL
        (SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 1
         ORDER  BY appraise_time DESC
         LIMIT  0, 20)) t
ORDER  BY  is_reply ASC,
          appraisetime DESC
LIMIT  20;

2.11.3 order by文件排序效率很低

如果查看explain执行计划的时候，可以看到Extra这一列，有一个Using filesort，它表示用到文件排序

select name,age,city from staff where city = '深圳' order by age limit 10;

查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。

order by用到文件排序时，为什么查询效率会相对低呢

order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序

2.11.3.1 rowid排序

rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。以下这个SQL，使用rowid排序，执行过程是这样

select name,age,city from staff where city = '深圳' order by age limit 10;

1. MySQL为对应的线程初始化sort_buffer，放入需要排序的age字段，以及主键id；
2. 从索引树idx_city， 找到第一个满足 city='深圳’条件的主键id
3. 到主键id索引树拿到id=9的这一行数据， 取age和主键id的值，存到sort_buffer；
4. 从索引树idx_city拿到下一个记录的主键id，即图中的id=13；
5. 重复步骤 3、4 直到city的值不等于深圳为止；
6. 前面5步已经查找到了所有city为深圳的数据，在sort_buffer中，将所有数据根据age进行排序；
7. 遍历排序结果，取前10行，并按照id的值回到原表中，取出city、name 和 age三个字段返回给客户端
   

2.11.3.2 全字段排序

同样的SQL，如果是走全字段排序是这样的

select name,age,city from staff where city = '深圳' order by age limit 10;

1. MySQL 为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段；
2. 从索引树idx_city， 找到第一个满足 city='深圳’条件的主键 id
3. 到主键id索引树拿到id=9的这一行数据， 取name、age、city三个字段的值，存到sort_buffer；
4. 从索引树idx_city 拿到下一个记录的主键id，即图中的id=13；
5. 重复步骤 3、4 直到city的值不等于深圳为止；
6. 前面5步已经查找到了所有city为深圳的数据，在sort_buffer中，将所有数据根据age进行排序；
7. 按照排序结果取前10行返回给客户端
   
   sort_buffer的大小是由一个参数控制的：sort_buffer_size

• 如果要排序的数据小于sort_buffer_size，排序在sort_buffer内存中完成
• 如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序。
  借助磁盘文件排序的话，效率就更慢一点。因为先把数据放入sort_buffer，当快要满时。会排一下序，然后把sort_buffer中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件。

2.12 左右连接问题

2.12.1 join或者子查询过多

一般来说，不建议使用子查询，可以把子查询改成join来优化。而数据库有个规范约定就是：尽量不要有超过3个以上的表连接。为什么要这么建议呢?

MySQL中，join的执行算法，分别是：Index Nested-Loop Join和Block Nested-Loop Join

• Index Nested-Loop Join：这个join算法，跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引。
• Block Nested-Loop Join：这种join算法，被驱动表上没有可用的索引,它会先把驱动表的数据读入线程内存join_buffer中，再扫描被驱动表，把被驱动表的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

join过多的问题：

一方面，过多的表连接，会大大增加SQL复杂度。另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join是在join_buffer内存做的，如果匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联。

一般情况下，如果业务需要的话，关联2~3个表是可以接受的，但是关联的字段需要加索引。如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成map，然后在业务层进行数据的拼装

2.12.1.1 join大小表问题

• 什么是小表驱动大表
  小表驱动大表指的是用小的数据集驱动大的数据集
• 为什么要用小表驱动大表
  例如：现有两个表A与B ，表A有200条数据，表B有20万条数据 ;
  按照循环的概念举个例子
  小表驱动大表即 A驱动表，B被驱动表
  for(200条){for(20万条){...}}
  大表驱动小表 > B驱动表，A被驱动表
  for(20万){for(200条){...}}
  总结：
  如果小的循环在外层，对于表连接来说就只连接200次 ;
  如果大的循环在外层，则需要进行20万次表连接，从而浪费资源，增加消耗 ;
  小表驱动大表的主要目的是通过减少表连接创建的次数，加快查询速度
• 怎么区分那个是驱动表与被驱动表
  通过EXPLAIN查看SQL语句的执行计划可以判断在谁是驱动表，EXPLAIN语句分析出来的第一行的表即是驱动表
• JOIN查询如何选择驱动表与被驱动表
  在JOIN查询中经常用到的inner join、left join、right join
  • 当使用left join时，左表是驱动表，右表是被驱动表 ;
  • 当使用right join时，右表是驱动表，左表是被驱动表 ;
  • 当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表

2.12.2 关联的字段编码格式不一样

新建两个表，一个user，一个user_job

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `user_job` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

user表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8


执行左外连接查询,user_job表还是走全表扫描，如下：

如果把它们的name字段改为编码一致，相同的SQL，还是会走索引

在做表关联时，注意一下关联字段的编码问题

2.12.3 关联更新、删除

虽然 MySQL5.6 引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN
比如下面 UPDATE语句，MySQL 实际执行的是循环/嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。

UPDATE operation o
SET   status = 'applying'
WHERE  o.id IN (SELECT id
               FROM   (SELECT o.id,
                               o.status
                       FROM   operation o
                       WHERE  o.group = 123
                               AND o.status NOT IN ( 'done' )
                       ORDER  BY o.parent,
                                 o.id
                       LIMIT  1) t);

执行计划：

+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type       | table | type | possible_keys | key     | key_len | ref   | rows | Extra                                               |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY           | o     | index |               | PRIMARY | 8       |       | 24   | Using where; Using temporary                       |
| 2 | DEPENDENT SUBQUERY |       |       |               |         |         |       |     | Impossible WHERE noticed after reading const tables |
| 3  | DERIVED           | o     | ref   | idx_2,idx_5   | idx_5   | 8       | const | 1   | Using where; Using filesort                         |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+

重写为 JOIN 之后，子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED，执行速度大大加快，从7秒降低到2毫秒。

UPDATE operation o
       JOIN  (SELECT o.id,
                           o.status
                     FROM   operation o
                     WHERE  o.group = 123
                           AND o.status NOT IN ( 'done' )
                     ORDER  BY o.parent,
                               o.id
                     LIMIT  1) t
         ON o.id = t.id
SET   status = 'applying'
执行计划简化为：

+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                                               |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY     |       |     |               |       |         |       |     | Impossible WHERE noticed after reading const tables |
| 2 | DERIVED     | o     | ref | idx_2,idx_5   | idx_5 | 8       | const | 1   | Using where; Using filesort                         |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------------------

2.12.4 提前缩小范围

先上初始 SQL 语句
该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。

SELECT *
FROM   my_order o
       LEFT JOIN my_userinfo u
              ON o.uid = u.uid
       LEFT JOIN my_productinfo p
              ON o.pid = p.pid
WHERE  ( o.display = 0 )
       AND ( o.ostaus = 1 )
ORDER  BY o.selltime DESC
LIMIT  0, 15

执行计划：
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
|  1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 909119 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
|  1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL |      6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+

由于最后 WHERE 条件以及排序均针对最左主表，因此可以先对 my_order 排序提前缩小数据量再做左连接。SQL 重写后如下，执行时间缩小为1毫秒左右

再检查执行计划：子查询物化后（select_type=DERIVED)参与 JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及 LIMIT 子句后，实际执行时间变得很小。

SELECT *
FROM (
SELECT *
FROM   my_order o
WHERE  ( o.display = 0 )
       AND ( o.ostaus = 1 )
ORDER  BY o.selltime DESC
LIMIT  0, 15
) o
     LEFT JOIN my_userinfo u
              ON o.uid = u.uid
     LEFT JOIN my_productinfo p
              ON o.pid = p.pid
ORDER BY  o.selltime DESC
limit 0, 15

执行计划：
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
|  1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL |     15 | Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | o.uid | 1 | NULL |
|  1 | PRIMARY | p | ALL | PRIMARY | NULL | NULL | NULL |      6 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | o | index | NULL | idx_1 | 5 | NULL | 909112 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+

2.13 优化器选错了索引

MySQL 中一张表是可以支持多个索引的。你写SQL语句的时候，没有主动指定使用哪个索引的话，用哪个索引是由MySQL来确定的。

我们日常开发中，不断地删除历史数据和新增数据的场景，有可能会导致MySQL选错索引。那么有哪些解决方案呢：

• 使用force index 强行选择某个索引
• 修改你的SQl，引导它使用我们期望的索引
• 优化你的业务逻辑
• 优化你的索引，新建一个更合适的索引，或者删除误用的索引。

注意：当mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。某些场景下即便强制SQL语句走索引，也同样会失效。

2.14 索引合并

一般情况下，针对单表的一个简单where查询只会使用一个索引，但是这样的话，针对单表中多个字段建立的普通索引就没有了意义。那么，一个简单查询中如何使用多个索引呢？这就要提到Mysql中的索引合并机制了。

2.14.1 什么是索引合并机制

MySQL5.0 之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描，然后将结果进行合并处理，然后在进行回表查询。

索引合并的流程大致如下：

SELECT * FROM tbl_name WHERE key1 = 10 and key2 = 20;

• 同时根据index1和index2的索引进行查询。
• 根据查询返回的主键id取交集。
• 根据主键id列表执行回表查询，返回结果。

这样做的好处是，可以同时根据index1、index2两个索引去过滤id值，只对共有的id值执行回表操作，节省了很多回表操作带来的开销。

普通索引回表查询说明：

每次从二级索引中读取到一条记录后，就会根据该记录的主键值执行回表操作。
而在某个扫描区间中的二级索引记录的主键值是无序的，也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。
每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机I/O 带来的性能开销比较大。所以Mysql中通过 MRR 多范围读取对回表查询进行了优化，先读取一部分二级索引记录，将他们的主键值排好序之后，再统一执行回表操作。

注意：

• 这里要把索引合并机制和多字段的组合索引区分开，优先还是考虑建立组合索引，一般来说索引合并会有性能消耗，相比而言组合索引查询效率会更高
• 只有当合并的索引具有相似的离散度时索引合并才比较有效。在两个索引行数悬殊的情况下，通常只使用其中最好的一个索引负责读取数据，而另外的索引只负责检验即可

2.14.2 索引合并机的类型

index merge: 同一个表的多个索引的范围扫描可以对结果进行合并，合并方式分为三种：intersection，union , Sort-Union。

2.14.2.1 索引合并-取交集

采用多索引AND等值查询。

EXPLAIN SELECT * from t_user t where t.name = '阿龙' and t.age = 26;

执行结果：

查询的type为：index_merge，说明使用了索引合并
Extra中为：Using intersect(idx_name,ix_age); Using where，说明索引合并后取的交集

2.14.2.2 索引合并-取并集

采用多索引OR等值查询。

EXPLAIN SELECT * from t_user t where t.name = '阿龙' or t.age = 26;

执行结果：

查询的type为：index_merge，说明使用了索引合并
Extra中为：Using union(idx_name,ix_age); Using where，说明索引合并后取的并集

2.14.2.3 索引合并-取有序并集

当 WHERE 子句转换为 OR 组合的多个范围条件时，可以采用排序联合算法Sort-Union。但 Index Merge 联合算法不适用。
排序联合算法Sort-Union和联合算法Union的区别在于，排序联合算法必须首先获取所有行的行 id，并在返回任何行之前对它们进行排序。
示例：

EXPLAIN SELECT * from t_user t where   t.age > 30 or t.score <60;

执行结果中没有进行索引合并，可能和表数据以及查询优化器的分析有关，认为在在当前数据下，OR 组合的多个范围条件采用全表扫描更合适。

2.14.3 索引合并机制的开关

索引合并机制有4个开关选项：

• index_merge：索引合并机制的总开关
• index_merge_intersection：索引合并-取交集
• index_merge_union：索引合并-取并集
• index_merge_sort_union：索引合并-排序并集

默认情况下，这些优化开关的值都是on，即打开状态

查看优化开关配置：

mysql> SELECT @@optimizer_switch;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on,firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on
1 row in set (0.00 sec)

2.15 count(1)、count(*) 与 count(列名)区别

2.15.1 count(1) and count(*)

从执行计划来看，count(1)和count(*)的效果是一样的。
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多，当数据量在1W以内时，count(1)会比count(*)的用时少些，不过也差不了多少。
如果count(1)是聚集索引时，那肯定是count(1)快，但是差的很小。
因为count(*)，会自动优化指定到那一个字段。所以没必要去count(1)，使用count(*)，sql会帮你完成优化的
因此：在有聚集索引时count(1)和count(*)基本没有差别

2.15.2 count(1) and count(字段)

count(1) 会统计表中的所有的记录数，包含字段为null 的记录。
count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null的记录

2.15.3 count(*) 和 count(1)和count(列名)区别

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略为NULL的值。
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略为NULL的值。
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计

2.15.4 执行效率区别

列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count(1) 的执行效率优于 count（*）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（*）最优

2.16 Null对索引影响

2.16.1 简介

NULL 值是一种对列的特殊约束，我们创建一个新列时，如果没有明确的使用关键字not null声明该数据列，Mysql会默认的为我们添加上NULL约束。 有些开发人员在创建数据表时，由于懒惰直接使用 Mysql 的默认推荐设置。即允许字段使用NULL值，而这一陋习很容易在使用NULL的场景中得出不确定的查询结果以及引起数据库性能的下降

2.16.2 操作Null

NULL 并不意味着什么都没有，我们要注意 NULL 跟 ''(空值)是两个完全不一样的值 MySQL 中可以操作NULL值操作符主要有三个.

• IS NULL
• IS NOT NULL
• <=> 太空船操作符，这个操作符很像=，select NULL<=>NULL 可以返回true，但是select NULL=NULL返回false
  虽然select NULL=NULL的结果为false，但是在我们使用distinct,group by,order by时，NULL又被认为是相同值.
• IFNULL 一个函数

在任何有返回值的表达式中有NULL参与时,都会得到另外一个NULL值

select ifnull(null,'First is null'),ifnull(null+10,'First is null'),ifnull(concat('abc',null),'First is null');
+------------------------------+---------------------------------+--------------------------------------------+
| ifnull(null,'First is null') | ifnull(null+10,'First is null') | ifnull(concat('abc',null),'First is null') |
+------------------------------+---------------------------------+--------------------------------------------+
| First is null                | First is null                   | First is null                              |
+------------------------------+---------------------------------+--------------------------------------------+
1 row in set (0.00 sec)

2.16.3 使用Null的影响

使用Null的影响：

• 列中使用NULL值容易引发不受控制的事情发生，有时候还会严重托慢系统的性能
• 对含有 NULL 值的列进行统计计算，eg：count(),max(),min()，结果并不符合我们的期望值
• 干扰排序，分组,去重结果
• 有的时候为了消除NULL带来的技术债务,我们需要在 SQL 中使用IFNULL()来确保结果可控，但是这使程序变得复杂
• NULL值不是占用原有的字段空间存储，而是额外申请一个字节去标注，这个字段添加了NULL约束。(就像额外的标志位一样)

2.16.4 Null列对mysql索引影响

MySQL 中支持在含有NULL值的列上使用索引（需要看情况，如下测试），但是Oracle不支持。这就是我们平时所说的如果列上含有NULL那么将会使索引失效.

Null列对mysql索引影响（使用is null或者is not null 测试一样，就用一个测试）：

• 如果列是主键分析则不会走索引，下面的id是主键

explain select * from student where id is null;

• 如果列是索引且列上没有非空限制，不论是唯一索引还是联合索引，则走索引

explain select * from student where `name` is null;

• 如果列是索引且列上有非空限制，不论是 唯一索引还是联合索引，都不走索引

explain select * from student where `name` is null;