1. SQL没加索引

正例：

2. SQL 索引不生效

2.1 隐式的类型转换，索引失效

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  userId varchar(32) NOT NULL,
  age  varchar(16) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_userid (userId) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

userId 字段为字串类型，是B+树的普通索引，如果查询条件传了一个数字过去，会导致索引失效。如下：

如果给数字加上'',也就是说，传的是一个字符串呢，当然是走索引，如下图：

2.2 查询条件包含or，可能导致索引失效

其中 userId 加了索引，但是 age 没有加索引的。我们使用了 or，以下SQL是不走索引的，如下：

2.3. like通配符可能导致索引失效

把%放后面，发现索引还是正常走的，如下：

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id varchar(32) NOT NULL,
  age  varchar(16) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_userid_name (user_id,name) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

有一个联合索引 idx_userid_name，我们执行这个SQL，查询条件是 name，索引是无效：

在联合索引中，查询条件满足最左匹配原则时，索引才正常生效。

2.5 在索引列上使用mysql的内置函数

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `login_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE,
  KEY `idx_login_time` (`login_Time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然login_time加了索引，但是因为使用了mysql的内置函数Date_ADD()，索引直接GG，如图：

一般这种情况怎么优化呢？可以把内置函数的逻辑转移到右边，如下：

2.6 对索引进行列运算（如，+、-、*、/）,索引不生效

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

虽然age加了索引，但是因为它进行运算，索引直接迷路了。。。 如图：

所以不可以对索引列进行运算，可以在代码处理好，再传参进去。

2.7 索引字段上使用（！= 或者 < >），索引可能失效

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

虽然age加了索引，但是使用了！= 或者< >，not in这些时，索引如同虚设。如下：

其实这个也是跟 mySql优化器 有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。平时我们用！= 或者< >，not in的时候，留点心眼哈。

2.8 索引字段上使用is null， is not null，索引可能失效

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

单个 name 字段加上索引，并查询 name 为非空的语句，其实会走索引的，如下:

单个card字段加上索引，并查询name为非空的语句，其实会走索引的，如下:

但是它两用or连接起来，索引就失效了，如下：

2.9 左右连接，关联的字段编码格式不一样

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.10 优化器选错了索引

3. limit深分页问题

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),
  KEY idx_create_time (create_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

你知道以下SQL，执行过程是怎样的嘛？

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;

3.2 如何优化深分页问题

select  id,name,balance FROM account where id > 100000 limit 10;

select  acct1.id,acct1.name,acct1.balance FROM account acct1 
INNER JOIN 
(SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 ON acct1.id= acct2.id;

4. 单表数据量太大

4.1 单表数据量太大为什么会变慢？

4.2 一棵B+树可以存多少数据量

4.3 如何解决单表数据量太大，查询变慢的问题

5. join 或者子查询过多

6. in元素过多

select user_id,name from user where user_id in (1,2,3...1000000);

如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。尤其有时候，我们是用的子查询，in后面的子查询，你都不知道数量有多少那种，更容易采坑（所以我把in元素过多抽出来作为一个小节）。如下这种子查询：

select * from user where user_id in (select author_id from artilce where type = 1);

正例是，分批进行，每批500个：

select user_id,name from user where user_id in (1,2,3...500);

如果传参的ids太多，还可以做个参数校验什么的

if (userIds.size() > 500) {
    throw new Exception("单次查询的用户Id不能超过200");
}

7. 数据库在刷脏页

7.1 什么是脏页

7.2 一条更新语句是如何执行的？

update t set c=c+1 where id=666;

1. 对于这条更新SQL，执行器会先找引擎取id=666这一行。如果这行所在的数据页本来就在内存中的话，就直接返回给执行器。如果不在内存，就去磁盘读入内存，再返回。

2. 执行器拿到引擎给的行数据后，给这一行C的值加一，得到新的一行数据，再调用引擎接口写入这行新数据。

3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，但是此时redo log 是处于prepare状态的哈。

4. 执行器生成这个操作的binlog，并把binlog写入磁盘。

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

7.3 为什么会出现脏页呢？

7.4 什么时候会刷脏页（flush）？

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

它表示的意思就是：查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。

8.2 order by文件排序效率为什么较低

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，也就是图中的id=9；

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三个字段返回给客户端。

全字段排序

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，也就是图中的id=9；

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行返回给客户端。

8.3 如何优化order by的文件排序

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，就会走索引。如下：

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`)

11、group by使用临时表

11.1 group by的执行流程

CREATE TABLE `staff` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `age` int(4) NOT NULL COMMENT '年龄',
  `city` varchar(64) NOT NULL COMMENT '城市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';

我们查看一下这个SQL的执行计划：

explain select city ,count(*) as num from staff group by city;

• Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表

• xtra 这个字段的Using filesort表示使用了文件排序

group by是怎么使用到临时表和排序了呢？我们来看下这个SQL的执行流程

select city ,count(*) as num from staff group by city;

1. 创建内存临时表，表里有两个字段city和num；

2. 全表扫描staff的记录，依次取出city = 'X'的记录。

1. 判断临时表中是否有为 city='X'的行，没有就插入一个记录 (X,1);

2. 如果临时表中有city='X'的行，就将X这一行的num值加 1；

3. 遍历完成后，再根据字段city做排序，得到结果集返回给客户端。 这个流程的执行图如下：