Mysql面试中的调优及注意事项【万字贴】第一版

• 数据库表存在一定数据量，就需要有对应的索引
• 发现慢查询时，检查是否走对索引，是否能用更好的索引进行优化查询速度，查看使用索引的姿势有没有问题
• 当索引解决不了慢查询时，一般由于业务表的数据量太大导致，利用空间换时间的思想
• 当读写性能均遇到瓶颈时，先考虑能否升级数据库架构即可解决问题，若不能则需要考虑分库分表
• 分库分表虽然能解决掉读写瓶颈，但同时会带来各种问题，需要提前调研解决方案和踩坑

查询缓存

我们先通过show variables like '%query_cache%'来看一下默认的数据库配置，此为本地数据库的配置。

概念

have_query_cache:当前的MYSQL版本是否支持“查询缓存”功能。

query_cache_limit:MySQL能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是1048576(1MB)

query_cache_min_res_unit:查询缓存分配的最小块（字节）。默认值是4096（4KB）。当查询进行时，MySQL把查询结果保存在query cache，但是如果保存的结果比较大，超过了query_cache_min_res_unit的值，这时候MySQL将一边检索结果，一边进行保存结果。他保存结果也是按默认大小先分配一块空间，如果不够，又要申请新的空间给他。如果查询结果比较小，默认的query_cache_min_res_unit可能造成大量的内存碎片，如果查询结果比较大，默认的query_cache_min_res_unit又不够，导致一直分配块空间，所以可以根据实际需求，调节query_cache_min_res_unit的大小。

注：如果上面说的内容有点弯弯绕，那举个现实生活中的例子，比如咱现在要给运动员送水，默认的是500ml的瓶子，如果过来的是少年运动员，可能500ml太大了，他们喝不完，造成了浪费，那我们就可以选择300ml的瓶子，如果过来的是成年运动员，可能500ml不够，那他们一瓶喝完了，又开一瓶，直接不渴为止。那么那样开瓶子也要时间，我们就可以选择1000ml的瓶子。

query_cache_size:为缓存查询结果分配的总内存。

query_cache_type:默认为on，可以缓存除了以select sql_no_cache开头的所有查询结果。

query_cache_wlock_invalidate:如果该表被锁住，是否返回缓存中的数据，默认是关闭的。

原理

MYSQL的查询缓存实质上是缓存SQL的hash值和该SQL的查询结果，如果运行相同的SQL,服务器直接从缓存中去掉结果，而不再去解析，优化，寻找最低成本的执行计划等一系列操作，大大提升了查询速度。

但是万事有利也有弊。

• 第一个弊端就是如果表的数据有一条发生变化，那么缓存好的结果将全部不再有效。这对于频繁更新的表，查询缓存是不适合的。

比如一张表里面只有两个字段，分别是id和name，数据有一条为1，张三。我使用select * from 表名 where name=“张三”来进行查询，MySQL发现查询缓存中没有此数据，会进行一系列的解析，优化等操作进行数据的查询，查询结束之后将该SQL的hash和查询结果缓存起来，并将查询结果返回给客户端。但是这个时候我有新增了一条数据2，张三。如果我还用相同的SQL来执行，他会根据该SQL的hash值去查询缓存中，那么结果就错了。所以MySQL对于数据有变化的表来说，会直接清空关于该表的所有缓存。这样其实是效率是很差的。

• 第二个弊端就是缓存机制是通过对SQL的hash，得出的值为key，查询结果为value来存放的，那么就意味着SQL必须完完全全一模一样，否则就命不中缓存。

我们都知道hash值的规则，就算很小的变化，哈希出来的结果差距是很多的，所以select * from 表名 where name=“张三”和SELECT * FROM 表名 WHERE NAME=“张三”和select * from 表名 where name = “张三”，三个SQL哈希出来的值是不一样的，大小写和空格影响了他们，所以并不能命中缓存，但其实他们搜索结果是完全一样的。

生产如何设置MySQL Query Cache

先来看线上参数：

我们发现将query_cache_type设置为OFF，其实网上资料和各大云厂商提供的云服务器都是将这个功能关闭的，从上面的原理来看，在一般情况下，他的弊端大于优点。

索引

例子

创建一个名为user的表，其包括id，name，age，sex等字段信息。此外，id为主键聚簇索引，idx_name为非聚簇索引。

CREATE TABLE `user` (
  `id` varchar(10) NOT NULL DEFAULT '',
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;复制代码

我们将其设置10条数据，便于下面的索引的理解。

INSERT INTO `user` VALUES ('1', 'andy', '20', '女');
INSERT INTO `user` VALUES ('10', 'baby', '12', '女');
INSERT INTO `user` VALUES ('2', 'kat', '12', '女');
INSERT INTO `user` VALUES ('3', 'lili', '20', '男');
INSERT INTO `user` VALUES ('4', 'lucy', '22', '女');
INSERT INTO `user` VALUES ('5', 'bill', '20', '男');
INSERT INTO `user` VALUES ('6', 'zoe', '20', '男');
INSERT INTO `user` VALUES ('7', 'hay', '20', '女');
INSERT INTO `user` VALUES ('8', 'tony', '20', '男');
INSERT INTO `user` VALUES ('9', 'rose', '21', '男');复制代码

聚簇索引（主键索引）

先来一张图镇楼，接下来就是看图说话。

他包含两个特点：

1.使用记录主键值的大小来进行记录和页的排序。

页内的记录是按照主键的大小顺序排成一个单项链表。

各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

2.叶子节点存储的是完整的用户记录。

注：聚簇索引不需要我们显示的创建，他是由InnoDB存储引擎自动为我们创建的。如果没有主键，其也会默认创建一个。复制代码

非聚簇索引（二级索引）

上面的聚簇索引只能在搜索条件是主键时才能发挥作用，因为聚簇索引可以根据主键进行排序的。如果搜索条件是name，在刚才的聚簇索引上，我们可能遍历，挨个找到符合条件的记录，但是，这样真的是太蠢了，MySQL不会这样做的。

如果我们想让搜索条件是name的时候，也能使用索引，那可以多创建一个基于name的二叉树。如下图。

他与聚簇索引的不同：

1.叶子节点内部使用name字段排序，叶子节点之间也是使用name字段排序。

2.叶子节点不再是完整的数据记录，而是name和主键值。

为什么不再是完整信息？

MySQL只让聚簇索引的叶子节点存放完整的记录信息，因为如果有好几个非聚簇索引，他们的叶子节点也存放完整的记录绩效，那就不浪费空间啦。

如果我搜索条件是基于name，需要查询所有字段的信息，那查询过程是啥？

1.根据查询条件，采用name的非聚簇索引，先定位到该非聚簇索引某些记录行。

2.根据记录行找到相应的id，再根据id到聚簇索引中找到相关记录。这个过程叫做回表。

联合索引

图就不画了，简单来说，如果name和age组成一个联合索引，那么先按name排序，如果name一样，就按age排序。

一些原则

1.最左前缀原则。一个联合索引（a,b,c）,如果有一个查询条件有a，有b，那么他则走索引，如果有一个查询条件没有a，那么他则不走索引。

2.使用唯一索引。具有多个重复值的列，其索引效果最差。例如，存放姓名的列具有不同值，很容易区分每行。而用来记录性别的列，只含有“男”，“女”，不管搜索哪个值，都会得出大约一半的行，这样的索引对性能的提升不够高。

3.不要过度索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

4、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.一定要设置一个主键。前面聚簇索引说到如果不指定主键，InnoDB会自动为其指定主键，这个我们是看不见的。反正都要生成一个主键的，还不如我们设置，以后在某些搜索条件时还能用到主键的聚簇索引。

6.主键推荐用自增id，而不是uuid。上面的聚簇索引说到每页数据都是排序的，并且页之间也是排序的，如果是uuid，那么其肯定是随机的，其可能从中间插入，导致页的分裂，产生很多表碎片。如果是自增的，那么其有从小到大自增的，有顺序，那么在插入的时候就添加到当前索引的后续位置。当一页写满，就会自动开辟一个新的页。

注：如果自增id用完了，那将字段类型改为bigint，就算每秒1万条数据，跑100年，也没达到bigint的最大值。复制代码

万年面试题（为什么索引用B+树）

1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

优化器

在开篇的图里面，我们知道了SQL语句从客户端经由网络协议到查询缓存，如果没有命中缓存，再经过解析工作，得到准确的SQL，现在就来到了我们这模块说的优化器。

首先，我们知道每一条SQL都有不同的执行方法，要不通过索引，要不通过全表扫描的方式。

那么问题就来了，MySQL是如何选择时间最短，占用内存最小的执行方法呢？

什么是成本？

1.I/O成本。数据存储在硬盘上，我们想要进行某个操作需要将其加载到内存中，这个过程的时间被称为I/O成本。默认是1。

2.CPU成本。在内存对结果集进行排序的时间被称为CPU成本。默认是0.2。

单表查询的成本

先来建一个用户表dev_user，里面包括主键id，用户名username，密码password，外键user_info_id，状态status，外键main_station_id，是否外网访问visit，这七个字段。索引有两个，一个是主键的聚簇索引，另一个是显式添加的以username为字段的唯一索引uname_unique。

如果搜索条件是select * from dev_user where username='XXX'，那么MySQL是如何选择相关索引呢？

1.使用所有可能用到的索引

我们可以看到搜索条件username，所以可能走uname_unique索引。也可以做聚簇索引，也就是全表扫描。

2.计算全表扫描代价

我们通过show table status like ‘dev_user’命令知道rows和data_length字段，如下图。

rows：表示表中的记录条数，但是这个数据不准确，是个估计值。

data_length:表示表占用的存储空间字节数。

data_length=聚簇索引的页面数量X每个页面的大小

反推出页面数量=1589248÷16÷1024=97

I/O成本：97X1=97

CPU成本：6141X0.2=1228

总成本：97+1228=1325

3.计算使用不同索引执行查询的代价

因为要查询出满足条件的所有字段信息，所以要考虑回表成本。

I/O成本=1+1X1=2(范围区间的数量+预计二级记录索引条数)

CPU成本=1X0.2+1X0.2=0.4(读取二级索引的成本+回表聚簇索引的成本)

总成本=I/O成本+CPU成本=2.4

4.对比各种执行方案的代价，找出成本最低的那个

上面两个数字一对比，成本是采用uname_unique索引成本最低。

多表查询的成本

对于两表连接查询来说，他的查询成本由下面两个部分构成：

• 单次查询驱动表的成本
• 多次查询被驱动表的成本（具体查询多次取决于对驱动表查询的结果集有多少个记录）

index dive

如果前面的搜索条件不是等值，而是区间，如select * from dev_user where username>'admin' and username<'test'这个时候我们是无法看出需要回表的数量。

步骤1：先根据username>'admin'这个条件找到第一条记录，称为区间最左记录。

步骤2：再根据username<'test'这个条件找到最后一条记录，称为区间最右记录。

步骤3：如果区间最左记录和区间最右记录相差不是很远，可以准确统计出需要回表的数量。如果相差很远，就先计算10页有多少条记录，再乘以页面数量，最终模糊统计出来。

Explain

产品来索命

产品：为什么这个页面出来这么慢？

开发：因为你查的数据多呗，他就是这么慢

产品：我不管，我要这个页面快点，你这样，客户怎么用啊

开发：。。。。。。。你行你来

哈哈哈哈，不瞎BB啦，如果有些SQL贼慢，我们需要知道他有没有走索引，走了哪个索引，这个时候我就需要通过explain关键字来深入了解MySQL内部是如何执行的。

id

一般来说一个select一个唯一id，如果是子查询，就有两个select，id是不一样的，但是凡事有例外，有些子查询的，他们id是一样的。

这是为什么呢？

那是因为MySQL在进行优化的时候已经将子查询改成了连接查询，而连接查询的id是一样的。

select_type

• simple：不包括union和子查询的查询都算simple类型。
• primary：包括union，union all，其中最左边的查询即为primary。
• union：包括union，union all，除了最左边的查询，其他的查询类型都为union。

table

显示这一行是关于哪张表的。

type：访问方法

• ref：普通二级索引与常量进行等值匹配
• ref_or_null：普通二级索引与常量进行等值匹配，该索引可能是null
• const：主键或唯一二级索引列与常量进行等值匹配
• range：范围区间的查询
• all：全表扫描

possible_keys

对某表进行单表查询时可能用到的索引

key

经过查询优化器计算不同索引的成本，最终选择成本最低的索引

rows

• 如果使用全表扫描，那么rows就代表需要扫描的行数
• 如果使用索引，那么rows就代表预计扫描的行数

filtered

• 如果全表扫描，那么filtered就代表满足搜索条件的记录的满分比
• 如果是索引，那么filtered就代表除去索引对应的搜索，其他搜索条件的百分比

redo日志（物理日志）

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作都是将页的数据加载到内存中，然后进行操作，再将数据刷回到硬盘上。

那么问题就来了，如果我要给张三转账100块钱，事务已经提交了，这个时候InnoDB把数据加载到内存中，这个时候还没来得及刷入硬盘，突然停电了，数据库崩了。重启之后，发现我的钱没有转成功，这不是尴尬了吗？

解决方法很明显，我们在硬盘加载到内存之后，进行一系列操作，一顿操作猛如虎，还未刷新到硬盘之前，先记录下，在XXX位置我的记录中金额减100，在XXX位置张三的记录中金额加100，然后再进行增删改查操作，最后刷入硬盘。如果未刷入硬盘，在重启之后，先加载之前的记录，那么数据就回来了。

这个记录就叫做重做日志，即redo日志。他的目的是想让已经提交的事务对数据的修改是永久的，就算他重启，数据也能恢复出来。

log buffer（日志缓冲区）

为了解决磁盘速度过慢的问题，redo日志不能直接写入磁盘，咱先整一大片连续的内存空间给他放数据。这一大片内存就叫做日志缓冲区，即log buffer。到了合适的时候，再刷入硬盘。至于什么时候是合适的，这个下一章节说。

我们可以通过show VARIABLES like 'innodb_log_buffer_size'命令来查看当前的日志缓存大小，下图为线上的大小。

redo日志刷盘时机

由于redo日志一直都是增长的，且内存空间有限，数据也不能一直待在缓存中， 我们需要将其刷新至硬盘上。 

那什么时候刷新到硬盘呢？

•  log buffer空间不足。上面有指定缓冲区的内存大小，MySQL认为日志量已经占了 总容量的一半左右，就需要将这些日志刷新到磁盘上。 
• 事务提交时。我们使用redo日志的目的就是将他未刷新到磁盘的记录保存起来，防止 丢失，如果数据提交了，我们是可以不把数据提交到磁盘的，但为了保证持久性，必须 把修改这些页面的redo日志刷新到磁盘。 
• 后台线程不同的刷新 后台有一个线程，大概每秒都会将log buffer里面的redo日志刷新到硬盘上。
•  checkpoint 下下小节讲

redo日志文件组

我们可以通过show variables like 'datadir'命令找到相关目录，底下有两个文件， 分别是ib_logfile0和ib_logfile1,如下图所示。

 

我们将缓冲区log buffer里面的redo日志刷新到这个两个文件里面，他们写入的方式 是循环写入的，先写ib_logfile0,再写ib_logfile1,等ib_logfile1写满了，再写ib_logfile0。 那这样就会存在一个问题，如果ib_logfile1写满了，再写ib_logfile0，之前ib_logfile0的内容 不就被覆盖而丢失了吗？ 这就是checkpoint的工作啦。

checkpoint

redo日志是为了系统崩溃后恢复脏页用的，如果这个脏页可以被刷新到磁盘上，那么 他就可以功成身退，被覆盖也就没事啦。

 冲突补习 

从系统运行开始，就不断的修改页面，会不断的生成redo日志。redo日志是不断 递增的，MySQL为其取了一个名字日志序列号Log Sequence Number，简称lsn。 他的初始化的值为8704，用来记录当前一共生成了多少redo日志。

 redo日志是先写入log buffer，之后才会被刷新到磁盘的redo日志文件。MySQL为其 取了一个名字flush_to_disk_lsn。用来说明缓存区中有多少的脏页数据被刷新到磁盘上啦。 他的初始值和lsn一样，后面的差距就有了。

 做一次checkpoint分为两步 

• 计算当前系统可以被覆盖的redo日志对应的lsn最大值是多少。redo日志可以被覆盖， 意味着他对应的脏页被刷新到磁盘上，只要我们计算出当前系统中最早被修改的oldest_modification, 只要系统中lsn小于该节点的oldest_modification值磁盘的redo日志都是可以被覆盖的。
•  将lsn过程中的一些数据统计。

undo日志（这部分不是很明白，所以大概说了）

基本概念

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

举个例子:

insert into a(id) values(1);(redo)
这条记录是需要回滚的。
回滚的语句是delete from a where id = 1;(undo)

试想想看。如果没有做insert into a(id) values(1);(redo)
那么delete from a where id = 1;(undo)这句话就没有意义了。

现在看下正确的恢复:
先insert into a(id) values(1);(redo)
然后delete from a where id = 1;(undo)
系统就回到了原先的状态，没有这条记录了

存储方式

是存在段之中。

事务

引言

事务中有一个隔离性特征，理论上在某个事务对某个数据进行访问时，其他事务应该排序，当该事务提交之后，其他事务才能继续访问这个数据。

但是这样子对性能影响太大，我们既想保持事务的隔离性，又想让服务器在出来多个事务时性能尽量高些，所以只能舍弃一部分隔离性而去性能。

事务并发执行的问题

• 脏写（这个太严重了，任何隔离级别都不允许发生）

sessionA：修改了一条数据，回滚掉

sessionB：修改了同一条数据，提交掉

对于sessionB来说，明明数据更新了也提交了事务，不能说自己啥都没干

• 脏读：一个事务读到另一个未提交事务修改的数据

session A：查询，得到某条数据

session B：修改某条数据，但是最后回滚掉啦

session A：在sessionB修改某条数据之后，在回滚之前，读取了该条记录

对于session A来说，读到了session回滚之前的脏数据

• 不可重复读：前后多次读取，同一个数据内容不一样

session A：查询某条记录
session B : 修改该条记录，并提交事务
session A : 再次查询该条记录，发现前后查询不一致

• 幻读：前后多次读取，数据总量不一致

session A：查询表内所有记录
session B : 新增一条记录，并查询表内所有记录
session A : 再次查询该条记录，发现前后查询不一致

四种隔离级别

数据库都有的四种隔离级别，MySQL事务默认的隔离级别是可重复读，而且MySQL可以解决了幻读的问题。

• 未提交读：脏读，不可重复读，幻读都有可能发生
• 已提交读：不可重复读，幻读可能发生
• 可重复读：幻读可能发生
• 可串行化：都不可能发生

但凡事没有百分百，emmmm，其实MySQL并没有百分之百解决幻读的问题。

举个例子：

session A：查询某条不存在的记录。

session B：新增该条不存在的记录，并提交事务。

session A：再次查询该条不存在的记录，是查询不出来的，但是如果我尝试修改该条记录，并提交，其实他是可以修改成功的。

MVCC

版本链：对于该记录的每次更新，都会将值放在一条undo日志中，算是该记录的一个旧版本，随着更新次数的增多，所有版本都会被roll_pointer属性连接成一个链表，即为版本链。

readview：

• 未提交读：因为可以读到未提交事务修改的记录，所以可以直接读取记录的最新版本就行
• 已提交读：每次读取之前都生成一个readview
• 可重复读：只有在第一次读取的时候才生成readview
• 可串行化：InnoDB涉及了加锁的方式来访问记录

不要使用SELECT *的原因

“不要使用SELECT *”几乎已经成为了MySQL使用的一条金科玉律，就连《阿里Java开发手册》也明确表示不得使用*作为查询的字段列表，更是让这条规则拥有了权威的加持。

 

不过我在开发过程中直接使用SELECT *还是比较多的，原因有两个：

1. 因为简单，开发效率非常高，而且如果后期频繁添加或修改字段，SQL语句也不需要改变；
2. 我认为过早优化是个不好的习惯，除非在一开始就能确定你最终实际需要的字段是什么，并为之建立恰当的索引；否则，我选择遇到麻烦的时候再对SQL进行优化，当然前提是这个麻烦并不致命。

但是我们总得知道为什么不建议直接使用SELECT *，本文从4个方面给出理由。

1. 不必要的磁盘I/O

我们知道 MySQL 本质上是将用户记录存储在磁盘上，因此查询操作就是一种进行磁盘IO的行为（前提是要查询的记录没有缓存在内存中）。

查询的字段越多，说明要读取的内容也就越多，因此会增大磁盘 IO 开销。尤其是当某些字段是 TEXT、MEDIUMTEXT或者BLOB 等类型的时候，效果尤为明显。

那使用SELECT *会不会使MySQL占用更多的内存呢？

理论上不会，因为对于Server层而言，并非是在内存中存储完整的结果集之后一下子传给客户端，而是每从存储引擎获取到一行，就写到一个叫做net_buffer的内存空间中，这个内存的大小由系统变量net_buffer_length来控制，默认是16KB；当net_buffer写满之后再往本地网络栈的内存空间socket send buffer中写数据发送给客户端，发送成功（客户端读取完成）后清空net_buffer，然后继续读取下一行并写入。

也就是说，默认情况下，结果集占用的内存空间最大不过是net_buffer_length大小罢了，不会因为多几个字段就占用额外的内存空间。

2. 加重网络时延

承接上一点，虽然每次都是把socket send buffer中的数据发送给客户端，单次看来数据量不大，可架不住真的有人用*把TEXT、MEDIUMTEXT或者BLOB 类型的字段也查出来了，总数据量大了，这就直接导致网络传输的次数变多了。

如果MySQL和应用程序不在同一台机器，这种开销非常明显。即使MySQL服务器和客户端是在同一台机器上，使用的协议还是TCP，通信也是需要额外的时间。

3. 无法使用覆盖索引

为了说明这个问题，我们需要建一个表

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_NAME_PHONE` (`name`,`phone`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
复制代码

我们创建了一个存储引擎为InnoDB的表user_innodb，并设置id为主键，另外为name和phone创建了联合索引，最后向表中随机初始化了500W+条数据。

InnoDB会自动为主键id创建一棵名为主键索引（又叫做聚簇索引）的B+树，这个B+树的最重要的特点就是叶子节点包含了完整的用户记录，大概长这个样子。

如果我们执行这个语句

SELECT * FROM user_innodb WHERE name = '蝉沐风';
复制代码

使用EXPLAIN查看一下语句的执行计划：

发现这个SQL语句会使用到IDX_NAME_PHONE索引，这是一个二级索引。二级索引的叶子节点长这个样子：

InnoDB存储引擎会根据搜索条件在该二级索引的叶子节点中找到name为蝉沐风的记录，但是二级索引中只记录了name、phone和主键id字段（谁让我们用的是SELECT *呢），因此InnoDB需要拿着主键id去主键索引中查找这一条完整的记录，这个过程叫做回表。

想一下，如果二级索引的叶子节点上有我们想要的所有数据，是不是就不需要回表了呢？是的，这就是覆盖索引。

举个例子，我们恰好只想搜索name、phone以及主键字段。

SELECT id, name,  phone FROM user_innodb WHERE name = "蝉沐风";
复制代码

使用EXPLAIN查看一下语句的执行计划：

可以看到Extra一列显示Using index，表示我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是使用了覆盖索引，能够直接摒弃回表操作，大幅度提高查询效率。

4. 可能拖慢JOIN连接查询

我们创建两张表t1，t2进行连接操作来说明接下来的问题，并向t1表中插入了100条数据，向t2中插入了1000条数据。

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `m` int DEFAULT NULL,
  `n` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT;

CREATE TABLE `t2` (
  `id` int NOT NULL,
  `m` int DEFAULT NULL,
  `n` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT;
复制代码

如果我们执行下面这条语句

SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.m = t2.m;
复制代码

这里我使用了STRAIGHT_JOIN强制令t1表作为驱动表，t2表作为被驱动表

对于连接查询而言，驱动表只会被访问一遍，而被驱动表却要被访问好多遍，具体的访问次数取决于驱动表中符合查询记录的记录条数。由于已经强制确定了驱动表和被驱动表，下面我们说一下两表连接的本质：

1. t1作为驱动表，针对驱动表的过滤条件，执行对t1表的查询。因为没有过滤条件，也就是获取t1表的所有数据；
2. 对上一步中获取到的结果集中的每一条记录，都分别到被驱动表中，根据连接过滤条件查找匹配记录

用伪代码表示的话整个过程是这样的：

// t1Res是针对驱动表t1过滤之后的结果集
for (t1Row : t1Res){
  // t2是完整的被驱动表
  for(t2Row : t2){
  	if (满足join条件 && 满足t2的过滤条件){
      发送给客户端
    }  
  }
}
复制代码

这种方法最简单，但同时性能也是最差，这种方式叫做嵌套循环连接（Nested-LoopJoin，NLJ）。怎么加快连接速度呢？

其中一个办法就是创建索引，最好是在被驱动表（t2）连接条件涉及到的字段上创建索引，毕竟被驱动表需要被查询好多次，而且对被驱动表的访问本质上就是个单表查询而已（因为t1结果集定了，每次连接t2的查询条件也就定死了）。

既然使用了索引，为了避免重蹈无法使用覆盖索引的覆辙，我们也应该尽量不要直接SELECT *，而是将真正用到的字段作为查询列，并为其建立适当的索引。

但是如果我们不使用索引，MySQL就真的按照嵌套循环查询的方式进行连接查询吗？当然不是，毕竟这种嵌套循环查询实在是太慢了！

在MySQL8.0之前，MySQL提供了基于块的嵌套循环连接（Block Nested-Loop Join，BLJ）方法，MySQL8.0又推出了hash join方法，这两种方法都是为了解决一个问题而提出的，那就是尽量减少被驱动表的访问次数。

这两种方法都用到了一个叫做join buffer的固定大小的内存区域，其中存储着若干条驱动表结果集中的记录（这两种方法的区别就是存储的形式不同而已），如此一来，把被驱动表的记录加载到内存的时候，一次性和join buffer中多条驱动表中的记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价，大大减少了重复从磁盘上加载被驱动表的代价。使用join buffer的过程如下图所示：

我们看一下上面的连接查询的执行计划，发现确实使用到了hash join（前提是没有为t2表的连接查询字段创建索引，否则就会使用索引，不会使用join buffer）。

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。我们可以使用join_buffer_size这个系统变量进行配置，默认大小为256KB。如果还装不下，就得分批把驱动表的结果集放到join buffer中了，在内存中对比完成之后，清空join buffer再装入下一批结果集，直到连接完成为止。

重点来了！并不是驱动表记录的所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录，减少分批的次数，也就自然减少了对被驱动表的访问次数。

资料查询不易，点个赞再走。