一文应对MySQL面试常见场景

基础

1. 数据库的三范式是什么？

• 第一范式：数据表中的每一列（每个字段）都不可以再拆分。例如用户表，用户地址还可以拆分成国家、省份、市，这样才是符合第一范式的。
• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。例如订单表里，存储了商品信息（商品价格、商品类型），那就需要把商品ID和订单ID作为联合主键，才满足第二范式。
• 第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。例如订单表，就不能存储用户信息（姓名、地址）。

2. MySQL 支持哪些存储引擎?

MySQL 支持多种存储引擎,比如 InnoDB,MyISAM,Memory,Archive 等等.在大多数的情况下,直接选择使用 InnoDB 引擎都是最合适的,InnoDB 也是 MySQL 的默认存储引擎。

MyISAM 和 InnoDB 的区别有哪些：

• InnoDB 支持事务，MyISAM 不支持
• InnoDB 支持外键，而 MyISAM 不支持
• InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
• Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；
• InnoDB 不保存表的具体行数，MyISAM 用一个变量保存了整个表的行数。
• MyISAM 采用表级锁(table-level locking)；InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

3. 超键、候选键、主键、外键分别是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键：是最小超键，即没有冗余元素的超键。
• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

4. SQL 约束有哪几种？

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

5. MySQL 中的 varchar 和 char 有什么区别？

char：

• char表示定长字符串，长度是固定的；
• 如果插入数据的长度小于char的固定长度时，则用空格填充；
• 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
• 对于char来说，最多能存放的字符个数为255，和编码无关

varchar：

• varchar表示可变长字符串，长度是可变的；
• 插入的数据是多长，就按照多长来存储；
• varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
• 对于varchar来说，最多能存放的字符个数为65532

日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些。

6. MySQL中 in 和 exists 区别

MySQL中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

1. 如果查询的两个表大小相当，那么用in和exists差别不大。
2. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
3. not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

7. drop、delete与truncate的区别

三者都表示删除，但是三者有一些差别：

8. 什么是存储过程？有哪些优缺点？

存储过程是一些预编译的 SQL 语句。

1、更加直白的理解：存储过程可以说是一个记录集，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

2、存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量 T_SQL 语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java 开发手册》中禁止使用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好。

9.说说MySQL的架构

MySQL逻辑架构图主要分三层：

• 客户端：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
• Server层：大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
• 存储引擎层：第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。Server层通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

10.一条SQL查询语句是如何执行的

从大体上来说，MySQL可以分为Server层和存储引擎层。

• Server层，连接器、查询缓存、分析器、优化器、执行器等
• 存储引擎层，负责数据的存储和提取
• 不同的存储引擎共用一个Server层

连接器

• 第一步，通过连接器连接到数据库，连接器负责和客户端建立连接、获取权限和维护、管理连接
  • mysql -h$ip-P$port -u$user -p
  • mysql是客户端工具，用来和服务端建立连接，通过TCP握手建立连接
  • 之后连接器开始认证身份，就是所输入的用户名密码
  • 如果身份认证通过，连接器就会到权限表查出你所拥有的权限，之后这个连接的权限判断逻辑，都依赖于此，这意味着连接过程中即使管理员更改了权限，也不会立即生效，只有新建连接才会生效
  • 连接之后，如果没有后续动作，将处于空闲状态，如果长时间未做动作，连接器自动将他断开，默认8小时。长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接 则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
  • 但是全部使用长连接后，对于MySQL来说，使用的临时内存是管理在连接对象里面的，只有在连接断开之后，这些资源才会释放，因此如果这些长连接积累下来，就会造成MySQL占用内存太大， 从而导致MySQL OOM，就会异常重启。

查询缓存

• 第二步，查询缓存。MySQL在得到查询请求之后会先查询缓存，如果之前执行过，其结果可能以key-value的方式存在内存中。（key是查询的语句，value是 查询的结果。）
  • 如果缓存不存在，将会执行，并将执行结果放入查询缓存
  • 查询缓存往往弊大于利，因此大多数情况不建议使用
    • 查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空
    • MySQL提供了按需使用的方式

分析器

• 如果没有命中查询缓存，就要开始真正执行语句了。首先，需要对SQL语句做解析。
  • 分析器先会做“词法分析”，MySQL需要识 别出SQL语句里面的字符串分别是什么，代表什么
  • 做完了这些识别以后，就要做“语法分析”，根据词法分析的结果，语法分析器会根据语法规则， 判断你输入的这个SQL语句是否满足MySQL语法

优化器

• 在开始执行之前，还要先经过优化器的处理
  • 在表里面有多个索引的时候，决定使用哪个索引
  • 在一个语句有多表关联（join） 的时候，决定各个表的连接顺序
  • 关键作用在于决定使用哪一个执行方案

执行器

• MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶 段，开始执行语句。
  • 首先，对判断对这个表有无操作权限，如果有，打开表继续执行
  • 打开表的时候，执行器就会根据表的引擎定义，去使用这个引 擎提供的接口
  • 调用引擎接口依次取表的每一行，进行判断结果是否满足条件，遍历完成之后返回给客户端结果集

11.一条SQL更新语句是如何执行的

• 第一步，用连接器连接数据库
• 第二步，清空该表的查询缓存
• 第三步，依据分析器的词法解析和语法解析判断是更新语句
• 第四步，优化器决定使用哪个索引
• 第五步，执行器负责具体执行，找到这一行，然后更新。具体分为以下几步
  • 第一步，执行器先找引擎取到相应的行，如果根据ID查找，ID作为主键，引擎直接用树搜索找到这一行
  • 第二步，执行器拿到引擎给的数据，更改数据之后得到新的一行数据，调用引擎接口写入这行数据
  • 第三步，引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面。此时redo log处 于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
  • 第四步，执行器生成这个操作的binlog，并把binlog写入磁盘。
  • 第五步，执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成

12.数据库设计通常分为哪几步

• 需求分析：分析用户的需求，包括数据、功能和性能需求；
• 概念结构设计：主要采用E-R模型进行设计，包括画E-R图；
• 逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换；
• 数据库物理设计：主要是为所设计的数据库选择合适的存储结构和存取路径；
• 数据库的实施：包括编程、测试和试运行；
• 数据库运行与维护：系统的运行与数据库的日常维护。

13.为什么实际项目里建议不用外键

• 大并发的时候会对性能造成影响
  • 外键就想其他类型的索引一样，性能方面的影响，主要是写入操作（比如，UPDATE/INSERT/DELETE），但是它与单表索引不同的是，它会引用一张或多张父表，这样当对子表进行写入操作（UPDATE/INSERT）的时候，父表就会被加上“共享锁”，这样在对子表高并发进行写入操作的情况下，对父表的写入操作就会由于“共享锁”的存在，而会长时间不能得到更新！当然查询是可以的，但是企业中大多数项目都是有一定的并发量的，所以在选择的时候，大多数还是取消了物理外键的约束。
• 项目无法支持热更新（也就是不停机去进行维护）
  • 如果数据库有外键，可能会造成新更新的代码 无法运行。 因为要匹配现有的外键，可能要重启服务器。那么这样就达不到热更新的目的了。因为外键是强力执行的。注意点和分布式绝对是冲突的。
• 为了降低耦合度
  • 删除了外键，并不是我们不要外键，而是物理的外键，物理的这一层关系我们不需要了。但是逻辑上的话，这样的外键还是存在的。这样子适当的降低了表和表之间的耦合度。
• 后期很难进行分库分表操作
  • 项目中如果数据库表不多还好说，如果说我们的数据库有上百张表甚至上千张表的时候，有几十个外键甚至几百个外键以及更多关联。这个时候分库分表该怎么做呢，真的是难以进行，难以去实现。

14、MySQL常用的函数有哪些

15、如果你要删除一个表里面的前10000行数据，有以下三种方法可以做到： 你会选择哪一种？

• 第一种，直接执行delete fromT limit 10000;
• 第二种，在一个连接中循环执行20次 delete fromTlimit 500;
• 第三种，在20个连接中同时执行delete fromTlimit 500。

第二种方式是相对较好的。

第一种方式（即：直接执行delete fromTlimit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在20个连接中同时执行delete fromTlimit 500），会人为造成锁冲突。

16、MySQL”视图“

一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。 创建视图的语法是create view…，而它的查询方法与表一样。

另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

17、MySQL 的内连接、左连接、右连接有有什么区别？

MySQL的连接主要分为内连接和外连接，外连接常用的有左连接、右连接。

• inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集
• left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。
• right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

18、DATETIME和TIMESTAMP的异同？

相同点：

1. 两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS
2. 两个数据类型都包含「日期」和「时间」部分。
3. 两个数据类型都可以存储微秒的小数秒（秒后6位小数秒）

区别：

1. 日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC 到 ``2038-01-09 03:14:07.999999 UTC
2. 存储空间：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
3. 时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
4. 默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

12、UNION与UNION ALL的区别？

• 如果使用UNION ALL，不会合并重复的记录行
• 效率 UNION 高于 UNION ALL

13、count(1)、count(*) 与 count(列名) 的区别？

执行效果：

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

执行速度：

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

14、一条SQL查询语句的执行顺序？

1. FROM：对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积（Cartesianproduct），产生虚拟表VT1
2. ON：对虚拟表VT1应用ON筛选，只有那些符合<join_condition>的行才被插入虚拟表VT2中
3. JOIN：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）～步骤3），直到处理完所有的表为止
4. WHERE：对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才被插入虚拟表VT4中
5. GROUP BY：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5
6. CUBE|ROLLUP：对表VT5进行CUBE或ROLLUP操作，产生表VT6
7. HAVING：对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才被插入虚拟表VT7中。
8. SELECT：第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中
9. DISTINCT：去除重复数据，产生虚拟表VT9
10. ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10。11）
11. LIMIT：取出指定行的记录，产生虚拟表VT11，并返回给查询用户

15、介绍下数据库分页

不同的数据库实现分页的方式是不同的，下面我们以MySQL为例来说明如何分页。在MySQL中，分页是通过LIMIT子句实现的，LIMIT的语法如下。总之，带有一个值的LIMIT总是从第一行开始，返回指定的行数。带两个值的LIMIT可以从指定行号处开始，返回指定的行数。

-- 返回前10行记录
SELECT * FROM t LIMIT 10;
-- 从第21行开始返回10行记录
SELECT * FROM t LIMIT 21,10;

在偏移量非常大的时候，例如 LIMIT 10000,20 这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面的10000条记录都将被抛弃，这样的代价是非常高的。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询：

LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

两种limit的执行过程

select * from page order by id limit 0, 10;  //第一页
select * from page order by id limit 990, 10;  //第一百页

mysql内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。

server层有很多模块，其中需要关注的是执行器是用于跟存储引擎打交道的组件。

执行器可以通过调用存储引擎提供的接口，将一行行数据取出，当这些数据完全符合要求（比如满足其他where条件），则会放到结果集中，最后返回给调用mysql的客户端（go、java写的应用程序）。

基于主键索引的limit执行过程

select * from page order by id limit 0, 10;

上面select后面带的是星号*，也就是要求获得行数据的所有字段信息。

server层会调用innodb的接口，在innodb里的主键索引中获取到第0到10条完整行数据，依次返回给server层，并放到server层的结果集中，返回给客户端。

而当我们把offset搞离谱点，比如执行的是

select * from page order by id limit 6000000, 10;

server层会调用innodb的接口，由于这次的offset=6000000，会在innodb里的主键索引中获取到第0到（6000000 + 10）条完整行数据，返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条，也就是10条数据，放到server层的结果集中，返回给客户端。

可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而获取的这些无用数据都是要耗时的。

因此，我们就知道了文章开头的问题的答案，mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大

优化

可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据跟只拷贝行数据里的其中一两个列字段耗时是不同的，这就让原本就耗时的操作变得更加离谱。

因为前面的offset条数据最后都是不要的，就算将完整字段都拷贝来了又有什么用呢，所以我们可以将sql语句修改成下面这样。

select * from page  where id >=(select id from page  order by id limit 6000000, 1) order by id limit 10;

上面这条sql语句，里面先执行子查询 select id from page order by id limit 6000000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到6000000+1条数据，然后server层会抛弃前6000000条，只保留最后一条数据的id。

但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。

在拿到了上面的id之后，假设这个id正好等于6000000，那sql就变成了

select * from page  where id >=(6000000) order by id limit 10;

这样innodb再走一次主键索引，通过B+树快速定位到id=6000000的行数据，时间复杂度是lg(n)，然后向后取10条数据。

这样性能确实是提升了，亲测能快一倍左右，属于那种耗时从3s变成1.5s的操作。

这······

属实有些杯水车薪，有点搓，属于没办法中的办法。

基于非主键索引的limit执行过程

上面提到的是主键索引的执行过程，我们再来看下基于非主键索引的limit执行过程。

比如下面的sql语句

select * from page order by user_name  limit 0, 10;

server层会调用innodb的接口，在innodb里的非主键索引中获取到第0条数据对应的主键id后，回表到主键索引中找到对应的完整行数据，然后返回给server层，server层将其放到结果集中，返回给客户端。

而当offset>0时，且offset的值较小时，逻辑也类似，区别在于，offset>0时会丢弃前面的offset条数据。

也就是说非主键索引的limit过程，比主键索引的limit过程，多了个回表的消耗。

但当offset变得非常大时，比如600万，此时执行explain。

可以看到type那一栏显示的是ALL，也就是全表扫描。

这是因为server层的优化器，会在执行器执行sql语句前，判断下哪种执行计划的代价更小。

很明显，优化器在看到非主键索引的600w次回表之后，摇了摇头，还不如全表一条条记录去判断算了，于是选择了全表扫描。

因此，当limit offset过大时，非主键索引查询非常容易变成全表扫描。是真·性能杀手。

这种情况也能通过一些方式去优化。比如

select * from page t1, (select id from page order by user_name limit 6000000, 100) t2  WHERE t1.id = t2.id;

通过select id from page order by user_name limit 6000000, 100。先走innodb层的user_name非主键索引取出id，因为只拿主键id，不需要回表，所以这块性能会稍微快点，在返回server层之后，同样抛弃前600w条数据，保留最后的100个id。然后再用这100个id去跟t1表做id匹配，此时走的是主键索引，将匹配到的100条行数据返回。这样就绕开了之前的600w条数据的回表。

当然，跟上面的case一样，还是没有解决要白拿600w条数据然后抛弃的问题，这也是非常挫的优化。

像这种，当offset变得超大时，比如到了百万千万的量级，问题就突然变得严肃了。

这里就产生了个专门的术语，叫深度分页。

深度分页问题

深度分页问题，是个很恶心的问题，恶心就恶心在，这个问题，它其实无解。

不管你是用mysql还是es，你都只能通过一些手段去"减缓"问题的严重性。

如果你是想取出全表的数据

有些需求是这样的，我们有一张数据库表，但我们希望将这个数据库表里的所有数据取出，异构到es，或者hive里，这时候如果直接执行

select * from page;

这个sql一执行，狗看了都摇头。

因为数据量较大，mysql根本没办法一次性获取到全部数据，妥妥超时报错。

于是不少mysql小白会通过limit offset size分页的形式去分批获取，刚开始都是好的，等慢慢地，哪天数据表变得奇大无比，就有可能出现前面提到的深度分页问题。

这种场景是最好解决的。

我们可以将所有的数据根据id主键进行排序，然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询。

这个操作，可以通过主键索引，每次定位到id在哪，然后往后遍历100个数据，这样不管是多少万的数据，查询性能都很稳定。

16、为什么mysql的count()方法这么慢？

select count(*) from sms where state = 0;

当数据表小的时候，这是没问题的，但当数据量大的时候，比如未发送的短信到了百万量级的时候，你就会发现，上面的sql查询时间会变得很长，最后timeout报错，查不出结果了。

count()方法的目的是计算当前sql语句查询得到的非NULL的行数。

虽然在server层都叫count()方法，但在不同的存储引擎下，它们的实现方式是有区别的。

比如同样是读全表数据 select count(*) from sms；语句。

使用 myisam引擎的数据表里有个记录当前表里有几行数据的字段，直接读这个字段返回就好了，因此速度快得飞起。

而使用innodb引擎的数据表，则会选择体积最小的索引树，然后通过遍历叶子节点的个数挨个加起来，这样也能得到全表数据。

追问1：为什么innodb不能像myisam那样实现count()方法

其中最大的区别在于myisam不支持事务，而innodb支持事务。

而事务，有四层隔离级别，其中默认隔离级别就是可重复读隔离级别（RR）。

innodb引擎通过MVCC实现了可重复隔离级别，事务开启后，多次执行同样的select快照读，要能读到同样的数据。

对于两个事务A和B，一开始sms表假设就2条数据，那事务A一开始确实是读到2条数据。事务B在这期间插入了1条数据，按道理数据库其实有3条数据了，但由于可重复读的隔离级别，事务A依然还是只能读到2条数据。

因此由于事务隔离级别的存在，不同的事务在同一时间下，看到的表内数据行数是不一致的，因此innodb，没办法，也没必要像myisam那样单纯的加个count字段信息在数据表上。

追问2：各种count()方法的原理

count方法的大原则是server层会从innodb存储引擎里读来一行行数据，并且只累计非null的值。但这个过程，根据count()方法括号内的传参，有略有不同。

• count(*)
  • server层拿到innodb返回的行数据，不对里面的行数据做任何解析和判断，默认取出的值肯定都不是null，直接行数+1。
• count(1)
  • server层拿到innodb返回的行数据，每行放个1进去，默认不可能为null，直接行数+1.
• count(某个列字段)
  • 由于指明了要count某个字段，innodb在取数据的时候，会把这个字段解析出来返回给server层，所以会比count(1)和count(*)多了个解析字段出来的流程。
  • 如果这个列字段是主键id，主键是不可能为null的，所以server层也不用判断是否为null，innodb每返回一行，行数结果就+1.
  • 如果这个列是普通索引字段，innodb一般会走普通索引，每返回一行数据，server层就会判断这个字段是否为null，不是null的情况下+1。当然如果建表sql里字段定义为not null的话，那就不用做这一步判断直接+1。
  • 如果这个列没有加过索引，那innodb可能会全表扫描，返回的每一行数据，server层都会判断这个字段是否为null，不是null的情况下+1。同上面的情况一样，字段加了not null也就省下这一步判断了。
• count(*) ≈ count(1) > count(主键id) > count(普通索引列) > count(未加索引列)

17、聊聊group by（索引实现）

用 explain 分析包含 group by 的 select 语句时，从输出结果的 Extra 列经常可以看到 Using temporary; Using filesort。看到这个，我们就知道 MySQL 使用了临时表来实现 group by。

使用索引实现 group by 的过程

• 存储引擎按顺序一条一条读取记录，返回给 server 层。
• server 层判断记录是否符合 where 条件。
• server 层对符合条件的记录进行聚合函数逻辑处理。

这种实现方式被称为紧凑索引扫描。

紧凑索引扫描会对满足 where 条件的所有记录进行聚合函数处理，而对于 min()、max() 来说，实际需要的只有每个分组中聚合函数字段值最小或最大的那条记录。

如果 server 层能直接从存储引擎读取到每个分组中聚合函数需要的那条记录，而不必读取每个分组中的所有记录进行聚合函数处理，是不是就可以节省很多时间了？

是的，这种只读取分组中部分记录实现 group by 的方式，被称为松散索引扫描。

紧凑索引扫描

group by 字段包含在索引中，并且满足索引最左匹配原则，server 层就可以顺序读取索引中的记录实现 group by，而不需要借助临时表。

紧凑索引扫描中的紧凑，表示 server 层从存储引擎读取记录时，以索引范围扫描或全索引扫描方式，按顺序一条一条读取记录，不会跳过中间的某条记录。

松散索引扫描

松散索引扫描，从存储引擎读取分组记录时，会跳着读，读取分组前缀之后，直接通过分组前缀（group by 字段的值）定位到分组中符合 where 条件的第一条或最后一条记录，而不需要读取分组的所有记录，然后就接着读取下一个分组的分组前缀，这样可以减少 select 语句执行过程中需要读取的记录数，从而比紧凑索引扫描更快

松散索引扫描用于 min()、max()，可以减少需要读取的记录数；用于 count(distinct)、sum(distinct)、avg(distinct) ，可以对记录去重，避免使用临时表去重。

两种索引扫描怎么选？

松散索引扫描虽然具备提升 select 语句执行效率的能力，但只有在适用的场景下才能发挥它的威力，因此，它的使用需要满足以下条件：

条件 1，select 语句只能是单表查询，不能是连接查询。

条件 2，group by 字段必须满足索引的最左匹配原则。例如：表中有一个索引包含 c1, c2, c3 三个字段，group by c1, c2 满足最左匹配原则。

条件 3，如果 select 字段列表中包含聚合函数，聚合函数必须满足这些条件：

• 所有聚合函数的参数都必须是同一个字段。
• 聚合函数字段必须是索引中的字段，并且 group by 字段 + 聚合函数字段也必须满足索引最左匹配原则。
• 聚合函数要么是 min()、max() 中的 1 ~ 2 个；要么是 count(distinct)、sum(distinct)、avg(distinct) 中的 1 ~ 3个。
• 松散索引扫描中，两类聚合函数不能同时存在，因为它们对于分组前缀处理逻辑不一样。在读取数据时，min()、max() 用 group by 字段值作为分组前缀；count(distinct)、sum(distinct)、avg(distinct) 用 group by 字段值 + 聚合函数中的字段值作为分组前缀。

条件 4，索引中所有字段必须是全字段索引，不能是前缀索引。

为什么松散索引扫描会比紧凑索引扫描成本高？

紧凑索引扫描，存储引擎按顺序一条一条读取记录，返回给 server 层，server 层判断记录是否符合 where 条件，然后对符合条件的记录进行聚合函数逻辑处理。

松散索引扫描，对于每个分组，都会从存储引擎读取两次数据，第一次是读取分组的第一条记录，得到分组前缀；第二次是根据分组前缀读取分组中索引扫描范围的第一条或最后一条记录。

如果分组中的记录数量多，第二次读取记录时，能跳过的记录就多，节省的成本就多，松散索引扫描就会比紧凑索引扫描更快。

如果分组中的记录数量少，第二次读取记录时，能跳过的记录就少，每个分组读取两次记录增加的成本超过了跳过记录节省的成本，松散索引扫描就会比紧凑索引扫描更慢。

解决

MySQL 把紧凑索引扫描中使用的顺序读取记录嵌入到松散索引扫描的逻辑里，当评估紧凑索引扫描成本比松散索引扫描低时，对于包含 distinct 关键字的聚合函数，就会用顺序读取记录代替跳着读取记录，并且在顺序读取记录的过程中完成记录去重。

18、聊聊Order By

我们日常工作中写 SQL 语句，经常会使用 order by 对记录进行排序。如果 order by 能够使用索引中记录已经排好序的特性，就不需要再借助内存或磁盘空间进行排序，这无疑是效率最高的。然而，还是有各种情况导致 order by 不能够使用索引，而是要进行额外的排序操作。MySQL 把需要借助内存或磁盘空间进行的排序操作统称为文件排序，而没有在概念上进一步分为文件排序和内存排序。

select city,name,age from t where city='杭州' order by name limit 1000 ; //city字段加索引

全字段排序

Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于 排序，称为sort_buffer。

为了说明这个SQL查询语句的执行过程，我们先来看一下city这个索引的示意图

从图中可以看到，满足city='杭州’条件的行，是从ID_X到ID_(X+N)的这些记录。

通常情况下，这个语句执行流程如下所示 ：

1. 初始化sort_buffer，确定放入name、city、age这三个字段；

2. 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；

3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；

4. 从索引city取下一个记录的主键id；

5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；

6. 对sort_buffer中的数据按照字段name做快速排序；

7. 按照排序结果取前1000行返回给客户端。

图中“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量 小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不 利用磁盘临时文件辅助排序。 MySQL将需要排序的数据分为12份，每一份单独排序完成后存在临时文件中，然后把12个有序文件再合并成一个有序的大文件。外部排序一般是归并排序算法。

rowid排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件 中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer里面 要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。所以如果单行很大，这个方法效率不够好。

新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。

但这时，排序的结果就因为少了city和age字段的值，不能直接返回了，整个执行流程就变成如 下所示的样子：

1. 初始化sort_buffer，确定放入两个字段，即name和id；

2. 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；

3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；

4. 从索引city取下一个记录的主键id；

5. 重复步骤3、4直到不满足city='杭州’条件为止，也就是图中的ID_Y；

6. 对sort_buffer中的数据按照字段name进行排序；

7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

rowid排序多访问了一次表t的主键索引

MySQL存储引擎

1.MySQL提供了哪些存储引擎

MySQL 支持多种存储引擎，可以通过 show engines 命令来查看 MySQL 支持的所有存储引擎。

MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎，也就是说只有 InnoDB 支持事务。

我这里使用的 MySQL 版本是 8.x，不同的 MySQL 版本之间可能会有差别。

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

2.MySQL 存储引擎架构了解吗？

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

并且，你还可以根据 MySQL 定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。这些非官方提供的存储引擎可以称为第三方存储引擎，区别于官方存储引擎。像目前最常用的 InnoDB 其实刚开始就是一个第三方存储引擎，后面由于过于优秀，其被 Oracle 直接收购了。

3.MyISAM 和 InnoDB 的区别是什么？

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

1.是否支持行级锁

MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

2.是否支持事务

MyISAM 不提供事务支持。

InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

3.是否支持外键

MyISAM 不支持，而 InnoDB 支持。

外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

4.是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

5.是否支持 MVCC

MyISAM 不支持，而 InnoDB 支持。

6.索引实现不一样。

• MyISAM，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引”
• InnoDB，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，这被称为“聚簇索引”或者聚集索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。

在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

事物

1.什么是数据库事务？

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

2. ACID特性指什么

事务就是一组原子性的操作，这些操作要么全部发生，要么全部不发生。事务把数据库从一种一致性状态转换成另一种一致性状态。

• 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
• 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

• 事务的隔离性是通过数据库锁的机制实现的。
• 事务的原子性由undo log来保证：undo log是逻辑日志，记录了事务的insert、update、deltete操作，回滚的时候做相反的delete、update、insert操作来恢复数据。
• 事务的持久性由redo log来保证：redolog被称作重做日志，是物理日志，事务提交的时候，必须先将事务的所有日志写入redo log持久化，到事务的提交操作才算完成。

3. 说一下MySQL 的四种隔离级别

• Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

• Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓 的 不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。

• Repeatable Read（可重读）

这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。

• Serializable（可串行化）

通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SjDib5Lh-1689353374899)(.\java基础\image-20210822180308501.png)]

MySQL 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到**SERIALIZABLE(可串行化)**隔离级别。

4.并发事务带来的问题

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
• 不可重复读（Unrepeatable read）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别 ：不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次查询同一条查询语句（DQL）时，记录发现记录增多或减少了。

5. 事务的实现原理

事务是基于重做日志文件(redo log)和回滚日志(undo log)实现的。

每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。

每当有修改事务时，还会产生 undo log，如果需要回滚，则根据 undo log 的反向语句进行逻辑操作，比如 insert 一条记录就 delete 一条记录。undo log 主要实现数据库的一致性。

6. MySQL事务日志介绍下？

innodb 事务日志包括 redo log 和 undo log。

undo log 指事务开始之前，在操作任何数据之前，首先将需操作的数据备份到一个地方。redo log 指事务中操作的任何数据，将最新的数据备份到一个地方。

事务日志的目的：实例或者介质失败，事务日志文件就能派上用场。

redo log

redo log 不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入 redo 中。具体的落盘策略可以进行配置 。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启 MySQL 服务的时候，根据 redo log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。RedoLog 是为了实现事务的持久性而出现的产物。

undo log

undo log 用来回滚行记录到某个版本。事务未提交之前，Undo 保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本快照供其他并发事务进行快照读。是为了实现事务的原子性而出现的产物,在 MySQL innodb 存储引擎中用来实现多版本并发控制。

7. 什么是MySQL的 binlog？

MySQL的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。

MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 有三种格式，各有优缺点：

• statement： 基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。
• row： 基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。
• mixed： 混合模式，根据语句来选用是 statement 还是 row 模式。

8. 在事务中可以混合使用存储引擎吗？

尽量不要在同一个事务中使用多种存储引擎，MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。

如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表）,在正常提交的情况下不会有什么问题。

但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

9. MySQL中是如何实现事务隔离的?

读未提交和串行化基本上是不需要考虑的隔离级别，前者不加锁限制，后者相当于单线程执行，效率太差。

MySQL 在可重复读级别解决了幻读问题，是通过行锁和间隙锁的组合 Next-Key 锁实现的。

详细原理看这篇文章：https://haicoder.net/note/MySQL-interview/MySQL-interview-MySQL-trans-level.html

10、事务的各个隔离级别都是如何实现的？

读未提交

读未提交，就不用多说了，采取的是读不加锁原理。

• 事务读不加锁，不阻塞其他事务的读和写
• 事务写阻塞其他事务写，但不阻塞其他事务读；

读取已提交&可重复读

读取已提交和可重复读级别利用了ReadView和MVCC，也就是每个事务只能读取它能看到的版本（ReadView）。

• READ COMMITTED：每次读取数据前都生成一个ReadView
• REPEATABLE READ ：在第一次读取数据时生成一个ReadView

串行化

串行化的实现采用的是读写都加锁的原理。

串行化的情况下，对于同一行事务，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

MVCC

什么是 MVCC？

MVCC，即Multi-Version Concurrency Control （多版本并发控制）。它是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

通俗的讲，数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在，在某个事务对其进行操作的时候，需要查看这一条记录的隐藏列事务版本id，比对事务id并根据事物隔离级别去判断读取哪个版本的数据。

数据库隔离级别读已提交、可重复读 都是基于MVCC实现的，相对于加锁简单粗暴的方式，它用更好的方式去处理读写冲突，能有效提高数据库并发性能。

MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

MVCC实现的关键知识点

事务版本号

事务每次开启前，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。这就是事务版本号。

隐式字段

对于InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer，如果表中没有主键和非NULL唯一键时，则还会有第三个隐藏的主键列row_id。

undo log

undo log，回滚日志，用于记录数据被修改前的信息。在表记录修改之前，会先把数据拷贝到undo log里，如果事务回滚，即可以通过undo log来还原数据。

可以这样认为，当delete一条记录时，undo log 中会记录一条对应的insert记录，当update一条记录时，它记录一条对应相反的update记录。

版本链

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。

快照读和当前读

快照读： 读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读

当前读：读取的是记录数据的最新版本，显式加锁的都是当前读

• 更新数据的时候，都是先读后写的，这时候的读，只能是当前读，因为如果还是查询的时候的快照读的化，就会造成前一个事务修改的数据修改丢失。
• 对于select操作加锁的情况下，也会是当前读。
• 表结构不支持“可重复读”？这是因为表结构没有对应的行数据，也没有 rowtrx_id，因此只能遵循当前读的逻辑。

​

Read View

事务执行SQL语句时，产生的读视图。实际上在innodb中，每个SQL语句执行前都会得到一个Read View。

主要是用来做可见性判断的，即判断当前事务可见哪个版本的数据。

Read view 的重要属性

• m_ids:当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。
• min_limit_id:表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
• max_limit_id:表示生成ReadView时，系统中应该分配给下一个事务的id值。
• creator_trx_id: 创建当前read view的事务ID

Read view 匹配条件规则

• 如果数据事务ID trx_id < min_limit_id，表明生成该版本的事务在生成Read View前，已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。
• 如果trx_id>= max_limit_id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
• 如果 min_limit_id =<trx_id< max_limit_id,需要分3种情况讨论
  • 如果m_ids包含trx_id,则代表Read View生成时刻，这个事务还未提交，但是如果数据的trx_id等于creator_trx_id的话，表明数据是自己生成的，因此是可见的。
  • 如果m_ids包含trx_id，并且trx_id不等于creator_trx_id，则Read View生成时，事务未提交，并且不是自己生产的，所以当前事务也是看不见的；
  • 如果m_ids不包含trx_id，则说明你这个事务在Read View生成之前就已经提交了，修改的结果，当前事务是能看见的。

查询一条记录，基于MVCC，是怎样的流程

1. 获取事务自己的版本号，即事务ID
2. 获取Read View
3. 查询得到的数据，然后Read View中的事务版本号进行比较。
4. 如果不符合Read View的可见性规则， 即就需要Undo log中历史快照;
5. 最后返回符合规则的数据

不同隔离级别下，Read view的工作方式不同

• 在读已提交（RC）隔离级别下，同一个事务里面，每一次查询都会产生一个新的Read View副本，这样就可能造成同一个事务里前后读取数据可能不一致的问题

• 在可重复读（RR）隔离级别下，一个事务里只会获取一次read view，都是副本共用的，从而保证每次查询的数据都是一样的。

锁

1. 为什么要加锁?

当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

保证多用户环境下保证数据库完整性和一致性。

2. 按照锁的粒度分数据库锁有哪些？

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

行级锁

• 行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
• 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 行锁，行锁是在需要的时候才加上的，但是并不是在不需要了的时候就立即释放，而是需要在事务结束的才会释放。（两阶段锁）

表级锁

• 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
• 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁

• 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁
• 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

3. 从锁的类别上分MySQL都有哪些锁呢？

从锁的类别上来讲，有共享锁和排他锁。

• 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
• 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。 一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。

锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。

他们的加锁开销从大到小，并发能力也是从大到小。

4.意向锁有什么作用？

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东东来快速判断是否可以对某个表使用表锁。

意向锁的出现是为了支持InnoDB的多粒度锁，它解决的是表锁和行锁共存的问题。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的。

	IS 锁	IX 锁
IS 锁	兼容	兼容
IX 锁	兼容	兼容

意向锁和共享锁和排它锁互斥（这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥）。

	IS 锁	IX 锁
S 锁	兼容	互斥
X 锁	互斥	互斥

5.InnoDB 有哪几类行锁？

间隙锁（Gap锁）

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。这个间隙可以跨一个索引记录，多个索引记录，甚至是空的。使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。

在 RC 隔离级别不会加任何锁，在 RR 隔离级别会加上间隙锁

只有在 Read Repeatable 、Serializable 隔离级别才有

间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入，所以加间隙 S 锁和加间隙 X 锁没有任何区别。

危害：若执行的条件是范围过大，则InnoDB会将整个范围内所有的索引键值全部锁定，很容易对性能造成影响。

Next-Key 锁

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。

和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有

快照读

RC 总是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本

当前读

它读的不再是数据的快照版本，而是数据的最新版本，并会对数据加锁，根据语句和加锁的不同，又分成三种情况：

• SELECT … LOCK IN SHARE MODE：加共享(S)锁
• SELECT … FOR UPDATE：加排他(X)锁
• INSERT / UPDATE / DELETE：加排他(X)锁

当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题。

MVCC解决的是快照读的幻读问题，而当前读的幻读问题需要Gap锁+行锁来解决。

6. 数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

7. InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

8. 隔离级别与锁的关系

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

9. 优化锁方面的意见？

• 使用较低的隔离级别
• 设计索引，尽量使用索引去访问数据，加锁更加精确，从而减少锁冲突
• 选择合理的事务大小，给记录显示加锁时，最好一次性请求足够级别的锁。列如，修改数据的话，最好申请排他锁，而不是先申请共享锁，修改时在申请排他锁，这样会导致死锁
• 不同的程序访问一组表的时候，应尽量约定一个相同的顺序访问各表，对于一个表而言，尽可能的固定顺序的获取表中的行。这样大大的减少死锁的机会。
• 尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 数据查询的时候不是必要，不要使用加锁。MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能：MVCC只在committed read（读提交）和 repeatable read （可重复读）两种隔离级别
• 对于特定的事务，可以使用表锁来提高处理速度活着减少死锁的可能。

10.一条简单SQL的加锁实现分析

• SQL1：select * from t1 where id = 10;
• SQL2：delete from t1 where id = 10;

select操作均不加锁，采用的是快照读

delete分情况讨论：

• 组合一：id列是主键，RC隔离级别

  • id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

• 组合二：id列是二级唯一索引，RC隔离级别

  • 若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。

• 组合三：id列是二级非唯一索引，RC隔离级别

  • 若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

• 组合四：id列上没有索引，RC隔离级别

  • 若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

• 组合五：id列是主键，RR隔离级别

  • id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

• 组合六：id列是二级唯一索引，RR隔离级别

  • 若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。

• 组合七：id列是二级非唯一索引，RR隔离级别(RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。)

  • Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。

• 组合八：id列上没有索引，RR隔离级别

  • 在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

• 组合九：Serializable隔离级别

  • 在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；Table Filter过滤条件，无论是否满足，都需要加X锁。

11、死锁和死锁检测

事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。

事务A和 事务B在互相等待对方的资源释放，就是进入了死锁状态。

解决策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。 （不可行，时间过长或过短都不合适）
• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事 务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。 （会出现大量消耗CPU资源的情况，）
• 可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多 条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账 户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少锁等 待个数，也就减少了死锁检测的CPU消耗。

12、MySQL中有哪几种锁，列举一下？

如果按锁粒度划分，有以下3种：

• 表锁：开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁。
• 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
• 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

如果按照兼容性，有两种，

• 共享锁（S Lock）,也叫读锁（read lock），相互不阻塞。
• 排他锁（X Lock），也叫写锁（write lock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它锁读取正在写入的数据。

13、说说InnoDB里的行锁实现?

• Record Lock 记录锁

记录锁就是直接锁定某行记录。当我们使用唯一性的索引(包括唯一索引和聚簇索引)进行等值查询且精准匹配到一条记录时，此时就会直接将这条记录锁定。例如select * from t where id =6 for update;就会将id=6的记录锁定。

• Gap Lock 间隙锁

间隙锁(Gap Locks) 的间隙指的是两个记录之间逻辑上尚未填入数据的部分,是一个左开右开空间。

间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询，并且没有命中任何一个record，此时就会将对应的间隙区间锁定。例如select * from t where id =3 for update;或者select * from t where id > 1 and id < 6 for update;就会将(1,6)区间锁定。

• Next-key Lock 临键锁

临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的(1,6]、(6,8]等。

临键锁就是记录锁(Record Locks)和间隙锁(Gap Locks)的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。当我们使用范围查询，并且命中了部分record记录，此时锁住的就是临键区间。注意，临键锁锁住的区间会包含最后一个record的右边的临键区间。例如select * from t where id > 5 and id <= 7 for update;会锁住(4,7]、(7,+∞)。mysql默认行锁类型就是临键锁(Next-Key Locks)。

加锁的基本单位是 next-key lock

唯一索引等值查询：

• 当查询的记录是存在的，next-key lock 会退化成「记录锁」。
• 当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。

非唯一索引等值查询：

• 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
• 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

• 唯一索引在满足一些条件的时候，next-key lock 退化为间隙锁和记录锁。
• 非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

间隙锁(Gap Locks)和临键锁(Next-Key Locks)都是用来解决幻读问题的，在已提交读（READ COMMITTED）隔离级别下，间隙锁(Gap Locks)和临键锁(Next-Key Locks)都会失效！

• Insert Intention Lock 插入意向锁

一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了意向锁 ，如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。但是事务在等待的时候也需要在内存中生成一个 锁结构 ，表明有事务想在某个 间隙 中插入新记录，但是现在在等待。这种类型的锁命名为 Insert Intention Locks ，也就是插入意向锁 。

假如我们有个T1事务，给(1,6)区间加上了意向锁，现在有个T2事务，要插入一个数据，id为4，它会获取一个（1,6）区间的插入意向锁，又有有个T3事务，想要插入一个数据，id为3，它也会获取一个（1,6）区间的插入意向锁，但是，这两个插入意向锁锁不会互斥。

索引

1. 索引是什么？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。而且索引是一个文件，它是要占据物理空间的。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。

2. 索引有哪些优缺点？

索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

3. MySQL有哪几种索引类型？

1、从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式，

2、从应用层次来分：普通索引，唯一索引，复合索引。

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
• 非聚簇索引： 不是聚簇索引，就是非聚簇索引

3、根据中数据的物理顺序与键值的逻辑（索引）顺序关系： 聚集索引，非聚集索引。

4、常见模型：哈希表、有序数组和搜索树

4. 说一说索引的底层实现？

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

B-Tree索引（MySQL使用B+Tree）

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+tree性质：

• n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
• 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
• 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
• B+ 树中，数据对象的插入和删除仅在叶节点上进行。
• B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

5. 为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B-tree： 从两个方面来回答

• B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
• 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash：

• 虽然可以快速定位，但是没有顺序，IO复杂度高；

• 基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；

• 适合等值查询，如=、in()、<=>，不支持范围查询 ；

• 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序；

• Hash索引在查询等值时非常快 ；

• 因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；

• 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 。

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树： 树的高度随着数据量增加而增加，IO代价高。

6. 讲一讲聚簇索引与非聚簇索引？

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。

而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

• 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
• 对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。
• 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可
• 注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

7. 非聚簇索引一定会回表查询吗

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。

8.覆盖索引

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

9. 联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

10. 讲一讲MySQL的最左前缀原则?

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。 mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

11. 讲一讲前缀索引？

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引 。

流程是：

先计算完整列的选择性 :select count(distinct col_1)/count(1) from table_1

再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1

找到最优长度之后，创建前缀索引 : create index idx_front on table_1 (col_1(4))

12. 了解索引下推吗？

MySQL 5.6引入了索引下推优化。默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

• 有了索引下推优化，可以在减少回表次数
• 在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下：

在 people_table中有一个二级索引(zipcode，lastname，address)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

• 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件
• 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。

13. 怎么查看MySQL语句有没有用到索引？

通过explain，如以下例子：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	filtered	rows	Extra
1	SIMPLE	titles	null	const	PRIMARY	PRIMARY	59	const,const,const	10	1

• id：在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id ，如explain select * from s1 where id = (select id from s1 where name = ‘egon1’);第一个select的id是1，第二个select的id是2。有时候会出现两个select，但是id却都是1，这是因为优化器把子查询变成了连接查询 。

• select_type：select关键字对应的那个查询的类型，如SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION 。

• table：每个查询对应的表名 。

• type：type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。如const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的覆盖索引) 。

  通常来说, 不同的 type 类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的. 而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.

• possible_key：查询中可能用到的索引*(可以把用不到的删掉，降低优化器的优化时间)* 。

• key：此字段是 MySQL 在当前查询时所真正使用到的索引。

• filtered：查询器预测满足下一次查询条件的百分比 。

• rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数. 这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。

• extra：表示额外信息，如Using where,Start temporary,End temporary,Using temporary等。

14. 为什么官方建议使用自增长主键作为索引？

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

插入连续的数据：

插入非连续的数据：

15. 如何创建索引？

创建索引有三种方式。

1、 在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

2、 使用ALTER TABLE命令去增加索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。 3、 使用CREATE INDEX命令创建。

CREATE INDEX index_name ON table_name (column_list);

16. 创建索引时需要注意什么？

1.选择合适的字段创建索引：

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

17. 建索引的原则有哪些？

1、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3、尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

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

5、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

17. 使用索引查询一定能提高查询的性能吗？

通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的I* NSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%。
• 基于非唯一性索引的检索。

18.使用索引的一些建议

• 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
• 避免 where 子句中对字段施加函数，这会造成无法命中索引。
• 在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
• 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

19.避免索引失效

• 全值匹配，对索引中所有列都指定具体值

• 最左前缀法则，查询从索引最左前列开始，并且不跳过索引的列（联合索引而言）

  • 匹配最左前缀法则，走索引
  • 违反最左前缀法则，失效
  • 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效

• 范围查询右边的列，不能使用索引

  • 大于号不走索引，大于等于会走索引

• 不要在索引列上进行运算操作，索引将失效

• 字符串不加单引号，造成索引失效

• 尽量使用覆盖索引，避免select *

  • using index ：使用覆盖索引的时候就会出现 
    using where：在查找使用索引的情况下，需要回表去查询所需的数据 
    using index condition：查找使用了索引，但是需要回表查询数据 
    using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据
    

• 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

• 以%开头的Like模糊查询，索引失效。

  • 如果仅仅是尾部模糊匹配，索引不会失效。
  • 如果是头部模糊匹配，索引失效。

• 如果MySQL评估使用索引比全表更慢，则不使用索引。

• is NULL ， is NOT NULL 有时索引失效。

• in 走索引， not in 索引失效。

• 尽量使用复合索引，而少使用单列索引 。

20.Hash索引和B+Tree索引的区别？

• Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
• Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，Hash 索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
• Hash索引不支持ORDER BY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面后模糊查询（比如%结尾）的话就可以起到优化作用。
• InnoDB 不支持哈希索引

21.在MySQL 5.6中，对索引做了哪些优化？

索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

索引下推优化原理

MySQL架构

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

在没有使用ICP的情况下，MySQL的查询：

• 存储引擎读取索引记录；
• 根据索引中的主键值，定位并读取完整的行记录；
• 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件

使用ICP的情况下，查询过程：

• 存储引擎读取索引记录（不是完整的行记录）；
• 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
• 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
• 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

索引下推使用条件

• 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
• 只能用于InnoDB和 MyISAM存储引擎及其分区表；
• 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

• 引用了子查询的条件不能下推；
• 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

23.为了减少IO，索引树会一次加载吗？

• 数据库索引是存储在磁盘上的，如果数据量很大，必然会导致索引的大小也会很大，超过几个G
• 当我们利用索引查询的时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

24.B+树的存储能力如何？为何说一般查找行记录，最多只需要1~3次IO

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3， 也就是说一个深度为3的B+Tree索引可以维护10^3
*10^3 * 10^3 =10亿条记录。(这里假定一个数据页也存储10^3条行记录数据了)

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2 ~ 4层。MysQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

25.为什么B+树比B树更适合实际应用中操作系统的文件索引和数据库索引

• B+树的磁盘读写代价更低。B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说Io读写次数也就降低了。
• B+树的查询效率更加稳定。由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

26.主键非自增会有什么问题？

自增ID

• 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费
• 新插入的行一定会在原有的最大数据行下一行,mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
• 减少了页分裂和碎片的产生

非自增问题：

• 写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO
• 因为写入是乱序的,innodb不得不频繁的做页分裂操作,以便为新的行分配空间,页分裂导致移动大量的数据，一次插入最少需要修改三个页以上
• 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片

27.如果某张表没有索引怎么办？

• 如果有主键，Innodb会把主键作为聚簇索引。
• 如果没有主键，Innodb会选择第一个不包含有 NULL 值的唯一索引作为主键索引
• 如果没有主键且没有唯一索引，Innodb会选择内置的rowId（Innodb内部对每一行数据维护了一个递增的rowId）作为聚簇索引。

索引底层：B+Tree，可以尽量减少数据查询时磁盘IO次数，同时树的高度直接影响了查询的性能，一般树的高度维持在 3~4 层。

B+Tree由三部分组成：根root、枝branch以及Leaf叶子，其中root和branch不存储数据，只存储指针地址，数据全部存储在Leaf Node，同时Leaf Node之间用双向链表链接。

28、在不同的业务场景下，应该选择普通索引，还是唯一索引？

从这两种索引对查询语句和更新语句的性能影响来进行分析。

查询过程

查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录

• 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

这个不同带来的性能差距微乎其微。

InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候， 并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每 个数据页的大小默认是16KB。

更新过程

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样 就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内 存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge

什么条件可以使用change buffer

• 对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存 才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer 了
• 唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用

change buffer的主要目的就是将记录的变更动 作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面 上要更新的次数越多），收益就越大。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程

插入数据的处理流程

第一种情况是，这个记录要更新的目标页在内存中

• 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

第二种情况是，这个记录要更新的目标页不在内存中

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束
• 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

总结

这两类索引在查询能力上 是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在 其他情况下，change buffer都能提升更新性能。

在实际使用中，普通索引和change buffer的配合使用，对于数据量大的表的更新优 化还是很明显的。

29、MySQL为什么有时候会选错索引？

优化器的逻辑

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库 里面，扫描行数是影响执行代价的因素之一。**扫描的行数越少，意味着访问磁盘数据的次数越 少，消耗的CPU资源越少。**扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行 综合判断。

扫描行数是怎么判断的？

MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根 据统计信息来估算记录数。 这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越 好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

MySQL是怎样得到索引的基数的呢？

MySQL采用采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均 值，然后乘以这个索引的页面数，就得到了这个索引的基数。

数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的 时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以是持久化存储（默认N20，M10），也可以存储在内存中（默认N8，M16）。

索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行

MySQL选错索引，这件事儿还得归咎到没能准确地判断出扫描行数。用explain命令的rows这个字段表示的是预计扫描行数。

30、索引的选择异常和处理

• 采用force index强行选择一个索引
• 修改语句，引导MySQL使用期望的索引
• 在某些场景下，可以新建一个更合适的索引，提供给优化器选择，或者删掉误用的索引。

日志

1.MySQL 中常见的日志有哪些？

• 错误日志（error log） ：对 MySQL 的启动、运行、关闭过程进行了记录。
• 二进制日志（binary log） ：主要记录的是更改数据库数据的 SQL 语句。
• 一般查询日志（general query log） ：已建立连接的客户端发送给 MySQL 服务器的所有 SQL 记录，因为 SQL 的量比较大，默认是不开启的，也不建议开启。
• 慢查询日志（slow query log） ：执行时间超过 long_query_time秒钟的查询，解决 SQL 慢查询问题的时候会用到。
• 事务日志(redo log 和 undo log) ：redo log 是重做日志，undo log 是回滚日志。
• 中继日志(relay log) ：relay log 是复制过程中产生的日志，很多方面都跟 binary log 差不多。不过，relay log 针对的是主从复制中的从库。
• DDL 日志(metadata log) ：DDL 语句执行的元数据操作。

2.慢查询日志有什么用

慢查询日志记录了执行时间超过 long_query_time（默认是 10s）的所有查询，在我们解决 SQL 慢查询（SQL 执行时间过长）问题的时候经常会用到。

3.redo log

物理日志

保证事务持久性

redo log（重做日志）是**InnoDB存储引擎**独有的，它让MySQL拥有了崩溃恢复能力。

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

特点

• redo log日志是顺序写入磁盘的，顺序IO
• 事务执行过程中，redo log不断记录

redo log组成

• 重做日志缓冲（redo log buffer）
• 重做日志文件(redo log file)

redo整体流程

• 第1步:先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
• 第2步:生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
• 第3步:当事务commit时，将redo log buffer中的内容刷新到 redo log file，对redo log file采用追加写的方式
• 第4步:定期将内存中修改的数据刷新到磁盘中

刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

• 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
  • 如果MySQL挂了或宕机可能会有1秒数据的丢失。
• 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
  • 只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。
  • 如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。
• 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache
  • 只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。
  • 如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8Q48dQUR-1689353374903)(.\java基础\image-20220329220401810.png)]

一个没有提交事务的 redo log 记录，也可能会刷盘。

在事务执行过程 redo log 记录是会写入redo log buffer 中，这些 redo log 记录会被后台线程刷盘。

除了后台线程每秒1次的轮询操作，还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。

每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

4.undo log

保证事务原子性、一致性

逻辑日志

作用：

• 回滚数据
• MVCC

5.binlog

逻辑日志

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。binlog是Server层的日志，所有引擎都可以使用

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。

写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

• 上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
• 上图的 fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数sync_binlog控制，默认是0。

• 为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。
  • 虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
• 设置为1，表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。
• 可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。
  • 在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。
  • 同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

binlog（归档日志）保证了MySQL集群架构的数据一致性。

两阶段提交

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

假设执行过程中写完redo log日志后，binlog日志写期间发生了异常,由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

原理：将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

那为什么要两阶段提交呢？

先写入redo log，后写入binlog：

在写完redo log之后，数据此时具有crash-safe能力，因此系统崩溃，数据会恢复成事务开始之前的状态。但是，若在redo log写完时候，binlog写入之前，系统发生了宕机。此时binlog没有对上面的更新语句进行保存，导致当使用binlog进行数据库的备份或者恢复时，就少了上述的更新语句。从而使得id=2这一行的数据没有被更新。

先写入binlog，后写入redo log：

写完binlog之后，所有的语句都被保存，所以通过binlog复制或恢复出来的数据库中id=2这一行的数据会被更新为a=1。但是如果在redo log写入之前，系统崩溃，那么redo log中记录的这个事务会无效，导致实际数据库中id=2这一行的数据并没有更新。

6.Buffer Pool

• 缓冲池（Buffer Pool）里面会缓存很多的数据，比如数据页、索引页、锁信息等等。
• MySQL表数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。
• 后续的查询先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。
• 更新表数据的时，如果 Buffer Pool 里命中数据，就直接在 Buffer Pool 里更新。

7.redo log 和 binlog的不同点

• redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
• redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的 是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
• redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件 写到一定大小后会切换到下一个，并不会覆盖以前的日志

分库分表

1. 为什么要分库分表？

分表

比如你单表都几千万数据了，你确定你能扛住么？绝对不行，单表数据量太大，会极大影响你的 sql执行的性能，到了后面你的 sql 可能就跑的很慢了。一般来说，就以我的经验来看，单表到几百万的时候，性能就会相对差一些了，你就得分表了。

分表就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户 id 来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在 200 万以内。

分库

分库就是你一个库一般我们经验而言，最多支撑到并发 2000，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒 1000 左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

这就是所谓的分库分表。

2. 用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？

这个其实就是看看你了解哪些分库分表的中间件，各个中间件的优缺点是啥？然后你用过哪些分库分表的中间件。

比较常见的包括：

• cobar
• TDDL
• atlas
• sharding-jdbc
• mycat

cobar

阿里 b2b 团队开发和开源的，属于 proxy 层方案。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。

TDDL

淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。

atlas

360 开源的，属于 proxy 层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以，现在用的公司基本也很少了。

sharding-jdbc

当当开源的，属于 client 层方案。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且目前推出到了 2.0 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。

mycat

基于 cobar 改造的，属于 proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于 sharding jdbc 来说，年轻一些，经历的锤炼少一些。

3. 如何对数据库如何进行垂直拆分或水平拆分的？

水平拆分的意思，就是把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的，所有库表的数据加起来就是全部数据。水平拆分的意义，就是将数据均匀放更多的库里，然后用多个库来抗更高的并发，还有就是用多个库的存储容量来进行扩容。

垂直拆分的意思，就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里去，然后将较多的访问频率很低的字段放到另外一个表里去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。

两种分库分表的方式：

• 一种是按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了。
• 或者是按照某个字段hash一下均匀分散，这个较为常用。

range 来分，好处在于说，扩容的时候很简单，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据。实际生产用 range，要看场景。

hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表

4、水平分表有哪几种路由方式？

水平分表主要有三种路由方式：

• 范围路由：选取有序的数据列 （例如，整形、时间戳等） 作为路由的条件，不同分段分散到不同的数据库表中。

  • 范围路由设计的复杂点主要体现在分段大小的选取上，分段太小会导致切分后子表数量过多，增加维护复杂度；分段太大可能会导致单表依然存在性能问题，一般建议分段大小在 100 万至2000 万之间，具体需要根据业务选取合适的分段大小。

    范围路由的优点是可以随着数据的增加平滑地扩充新的表。例如，现在的用户是 100 万，如果增加到 1000 万，只需要增加新的表就可以了，原有的数据不需要动。范围路由的一个比较隐含的缺点是分布不均匀，假如按照 1000 万来进行分表，有可能某个分段实际存储的数据量只有 1000 条，而另外一个分段实际存储的数据量有 900 万条。

• Hash 路由：选取某个列 （或者某几个列组合也可以） 的值进行 Hash 运算，然后根据 Hash 结果分散到不同的数据库表中。

  • Hash 路由设计的复杂点主要体现在初始表数量的选取上，表数量太多维护比较麻烦，表数量太少又可能导致单表性能存在问题。而用了 Hash 路由后，增加子表数量是非常麻烦的，所有数据都要重分布。Hash 路由的优缺点和范围路由基本相反，Hash 路由的优点是表分布比较均匀，缺点是扩充新的表很麻烦，所有数据都要重分布。

• 配置路由：配置路由就是路由表，用一张独立的表来记录路由信息。同样以订单id 为例，我们新增一张 order_router 表，这个表包含 orderjd 和 tablejd 两列 , 根据 orderjd 就可以查询对应的 table_id。

  • 配置路由设计简单，使用起来非常灵活，尤其是在扩充表的时候，只需要迁移指定的数据，然后修改路由表就可以了。

5、你觉得分库分表会带来什么问题呢？

从分库的角度来讲：

• 事务的问题

使用关系型数据库，有很大一点在于它保证事务完整性。

而分库之后单机事务就用不上了，必须使用分布式事务来解决。

• 跨库 JOIN 问题

在一个库中的时候我们还可以利用 JOIN 来连表查询，而跨库了之后就无法使用 JOIN 了。

此时的解决方案就是在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张表，然后利用代码来关联得到最终的结果。

这种方式实现起来稍微比较复杂，不过也是可以接受的。

还有可以适当的冗余一些字段。比如以前的表就存储一个关联 ID，但是业务时常要求返回对应的 Name 或者其他字段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。

还有一种方式就是数据异构，通过binlog同步等方式，把需要跨库join的数据异构到ES等存储结构中，通过ES进行查询。

从分表的角度来看：

• 跨节点的 count,order by,group by 以及聚合函数问题

只能由业务代码来实现或者用中间件将各表中的数据汇总、排序、分页然后返回。

• 数据迁移，容量规划，扩容等问题

数据的迁移，容量如何规划，未来是否可能再次需要扩容，等等，都是需要考虑的问题。

• ID 问题

数据库表被切分后，不能再依赖数据库自身的主键生成机制，所以需要一些手段来保证全局主键唯一。

1. 还是自增，只不过自增步长设置一下。比如现在有三张表，步长设置为3，三张表 ID 初始值分别是1、2、3。这样第一张表的 ID 增长是 1、4、7。第二张表是2、5、8。第三张表是3、6、9，这样就不会重复了。
2. UUID，这种最简单，但是不连续的主键插入会导致严重的页分裂，性能比较差。
3. 分布式 ID，比较出名的就是 Twitter 开源的 sonwflake 雪花算法

读写分离、主从同步（复制）

1. 什么是MySQL主从同步？

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

2. MySQL主从同步的目的？为什么要做主从同步？

1. 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
2. 提高数据安全-因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
3. 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能
4. 数据备份。一般我们都会做数据备份，可能是写定时任务，一些特殊行业可能还需要手动备份，有些行业要求备份和原数据不能在同一个地方，所以主从就能很好的解决这个问题，不仅备份及时，而且还可以多地备份，保证数据的安全

3. 如何实现MySQL的读写分离？

其实很简单，就是基于主从复制架构，简单来说，就搞一个主库，挂多个从库，然后我们就单单只是写主库，然后主库会自动把数据给同步到从库上去。

读写分离的基本实现是:

• 数据库服务器搭建主从集群，一主一从、一主多从都可以。
• 数据库主机负责读写操作，从机只负责读操作。
• 数据库主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据。
• 业务服务器将写操作发给数据库主机，将读操作发给数据库从机。

4. MySQL主从复制流程和原理?

基本原理流程，是3个线程以及之间的关联

• 主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
• 主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
• 从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。
• 从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。

复制过程如下：

Binary log：主数据库的二进制日志

Relay log：从服务器的中继日志

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

5. MySQL主从同步延时问题如何解决？

主从同步延迟的原因

一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个，当某个 SQL 在从服务器上执行的时间稍长 或者由于某个 SQL 要进行锁表就会导致，主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

主从同步延迟的解决办法

MySQL 实际上在有两个同步机制，一个是半同步复制，用来 解决主库数据丢失问题；一个是并行复制，用来 解决主从同步延时问题。

• 半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
• 并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

MySQL优化

1. 如何定位及优化SQL语句的性能问题？

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。

而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

2. 大表数据查询，怎么优化

• 优化shema、sql语句+索引；
• 第二加缓存，memcached, redis；
• 主从复制，读写分离；
• 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
• 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

3. 超大分页怎么处理?

数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10 这种查询其实也是有可以优化的余地的. 这条语句需要 load1000000 数据然后基本上全部丢弃,只取 10 条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也 load 了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快。

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种.

【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联：

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

4. 统计过慢查询吗？对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的我都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？ 是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

5. 如何优化查询过程中的数据访问

• 访问数据太多导致查询性能下降
• 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
• 确认MySQL服务器是否在分析大量不必要的数据行
• 查询不需要的数据。解决办法：使用limit解决
• 多表关联返回全部列。解决办法：指定列名
• 总是返回全部列。解决办法：避免使用SELECT *
• 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
• 是否在扫描额外的记录。解决办法： 使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化： 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
• 改变数据库和表的结构，修改数据表范式
• 重写SQL语句，让优化器可以以更优的方式执行查询。

6. 如何优化关联查询

• 确定ON或者USING子句中是否有索引。
• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

7. 数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1. 将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

1. 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

1. 增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

8. MySQL数据库cpu飙升到500%的话他怎么处理

（1）使用 top 命令观察，确定是 mysqld 导致还是其他原因。

（2）如果是 mysqld 导致的，show processlist，查看 session 情况，确定是不是有消耗资源的 sql 在运行。

（3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

（1）kill 掉这些线程 (同时观察 cpu 使用率是否下降)，

（2）进行相应的调整 (比如说加索引、改 sql、改内存参数)

（3）重新跑这些 SQL。

9. 大表怎么优化？

类似的问题：某个表有近千万数据，CRUD比较慢，如何优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

• 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
• 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
• 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑；
• 通过分库分表的方式进行优化，主要有垂直分表和水平分表。

Explain

当Explain 与 SQL语句一起使用时，MySQL 会显示来自优化器关于SQL执行的信息。也就是说，MySQL解释了它将如何处理该语句，包括如何连接表以及什么顺序连接表等。

• 表的加载顺序
• sql 的查询类型
• 可能用到哪些索引，哪些索引又被实际使用
• 表与表之间的引用关系
• 一个表中有多少行被优化器查询 …

Explain有哪些信息

Explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。

Explain执行计划详解

一、id

表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行。

• id相同
  • 具有同样的优先级，执行顺序由上而下，具体顺序由优化器决定。
• id不同
  • id值越大优先级越高，越先被执行
• 以上两种同时存在
  • id的以上两种同时存在，相同id划分为一组，同组的从上往下顺序执行，不同组 id值越大，优先级越高，越先执行。

二、select_type

表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。

• SIMPLE：表示最简单的 select 查询语句，也就是在查询中不包含子查询或者 union交并差集等操作。
• PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
• 当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY 。
• DERIVED：表示包含在from子句中的子查询的select，在我们的 from 列表中包含的子查询会被标记为derived 。
• UNION：如果union后边又出现的select 语句，则会被标记为union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived。
• UNION RESULT：代表从union的临时表中读取数据，而table列的<union1,4>表示用第一个和第四个select的结果进行union操作。

三、table

查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表。

四、partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

五、type（重要）

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• system，当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。
• const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。
• eq_ref：查询时命中主键primary key 或者 unique key索引， type 就是 eq_ref。
• 区别于eq_ref ，ref表示使用非唯一性索引，会找到很多个符合条件的行。
• ref_or_null：这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含NULL值的行。
• index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
• unique_subquery：替换下面的 IN子查询，子查询返回不重复的集合。
• index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
• range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。
• index：Index 与ALL 其实都是读全表，区别在于index是遍历索引树读取，而ALL是从硬盘中读取。
• ALL：将遍历全表以找到匹配的行，性能最差。

六、possible_keys

表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。

七、key

区别于possible_keys，key是查询中实际使用到的索引，若没有使用索引，显示为NULL。

八、key_len

• 单列索引，那么需要将整个索引长度算进去；
• 多列索引，不是所有列都能用到，需要计算查询中实际用到的列。

九、Extra

Extra ：不适合在其他列中显示的信息，Explain 中的很多额外的信息会在 Extra 字段显示。

• Using index，我们在相应的 select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。
  • 查询的列被索引覆盖，并且where筛选条件是索引的是前导列，Extra中为Using index
• Using where，查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。
  • 查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra中为Using where
  • 查询的列未被索引覆盖，where筛选条件非索引列，Extra中为Using where
• Using index condition
  • 查询的列不全在索引中，where条件中是一个前导列的范围
  • 查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）
• Using where Using index
  • 查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的不是前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据
  • 查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据

SQL慢查询

1、SQL没加索引

很多时候，我们的慢查询，都是因为没有加索引。如果没有加索引的话，会导致全表扫描的。因此，应考虑在where的条件列，建立索引，尽量避免全表扫描。

2、SQL 索引不生效

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

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

不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。

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

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

对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就完事。Mysql优化器出于效率与成本考虑，遇到or条件，让索引失效，看起来也合情合理嘛。

如果or条件的列都加了索引，索引可能会走也可能不走

遇到不走索引的时候，考虑拆开两条SQL。

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

并不是用了like通配符，索引一定会失效，而是like查询是以%开头，才会导致索引失效。把%放后面，发现索引还是正常走的。

既然like查询以%开头，会导致索引失效。我们如何优化呢？

• 使用覆盖索引
• 把%放后面

2.4 查询条件不满足联合索引的最左匹配原则

MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了(a)、(a,b)、(a,b,c)三个索引。

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

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

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

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

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

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

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

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

其实这个也是跟mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。

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

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

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

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

很多时候，也是因为数据量问题，导致了MySQL优化器放弃走索引。同时，平时我们用explain分析SQL的时候，如果type=range,要注意一下哈，因为这个可能因为数据量问题，导致索引无效。

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

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

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

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

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

2.10 优化器选错了索引

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

日常开发中，不断地删除历史数据和新增数据的场景，有可能会导致MySQL选错索引。

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

3. limit深分页问题

limit深分页为什么会导致SQL变慢呢？

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

这个SQL的执行流程：

1. 通过普通二级索引树idx_create_time，过滤create_time条件，找到满足条件的主键id。
2. 通过主键id，回到id主键索引树，找到满足记录的行，然后取出需要展示的列（回表过程）
3. 扫描满足条件的100010行，然后扔掉前100000行，返回。

limit深分页，导致SQL变慢原因有两个：

• limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
• limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

解决方式

我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。

• 标签记录法

  • 标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。

  • 假设上一次记录到100000，则SQL可以修改为

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

  • 这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。

• 延迟关联法

  • 延迟关联法，就是把条件转移到主键索引树，然后减少回表

  • 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;
    

  • 优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。

4. 单表数据量太大

一个表的数据量达到好几千万或者上亿时，加索引的效果没那么明显啦。性能之所以会变差，是因为维护索引的B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

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

InnoDB存储引擎最小储存单元是页，一页大小就是16k。

B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

• 如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
• 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。

如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

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

一般超过千万级别，我们可以考虑分库分表了。

分库分表可能导致的问题：

• 事务问题
• 跨库问题
• 排序问题
• 分页问题
• 分布式ID

5. 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条件的，作为结果集的一部分返回。

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

解决

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

6. in元素过多

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

如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。

7. 数据库在刷脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

更新SQL只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致,就出现脏页。

什么时候会刷脏页

InnoDB存储引擎的redo log大小是固定，且是环型写入的

那什么时候会刷脏页？有几种场景：

1. redo log写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住。

2. 内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页

   InnoDB 用缓冲池（buffer pool）管理内存,而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

3. MySQL 认为系统空闲的时候，也会刷一些脏页

4. MySQL 正常关闭时，会把内存的脏页都 flush 到磁盘上

为什么刷脏页会导致SQL变慢呢？

1. redo log写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢呀。一般要杜绝出现这个情况。
2. 一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长。

8. order by 文件排序

9. 拿不到锁

有时候，我们查询一条很简单的SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是表被锁住了，或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。

10. delete + in子查询不走索引！

当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select + in子查询，却可以走索引。

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

11、group by使用临时表

group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。

关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

慢SQL如何定位呢？

• 慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
• 服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。

SQL优化

分页优化

索引优化

JOIN优化

排序优化

UNION优化

不停机扩容怎么实现？

第一阶段：在线双写，查询走老库

1. 建立好新的库表结构，数据写入旧库的同时，也写入拆分的新库
2. 数据迁移，使用数据迁移程序，将旧库中的历史数据迁移到新库
3. 使用定时任务，新旧库的数据对比，把差异补齐

第二阶段：在线双写，查询走新库

1. 完成了历史数据的同步和校验
2. 把对数据的读切换到新库

第三阶段：旧库下线

1. 旧库不再写入新的数据
   数据做对比，满足join条件的，作为结果集的一部分返回。

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

解决

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

6. in元素过多

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

如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。

7. 数据库在刷脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

更新SQL只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致,就出现脏页。

什么时候会刷脏页

InnoDB存储引擎的redo log大小是固定，且是环型写入的

那什么时候会刷脏页？有几种场景：

1. redo log写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住。

2. 内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页

   InnoDB 用缓冲池（buffer pool）管理内存,而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

3. MySQL 认为系统空闲的时候，也会刷一些脏页

4. MySQL 正常关闭时，会把内存的脏页都 flush 到磁盘上

为什么刷脏页会导致SQL变慢呢？

1. redo log写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢呀。一般要杜绝出现这个情况。
2. 一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长。

8. order by 文件排序

9. 拿不到锁

有时候，我们查询一条很简单的SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是表被锁住了，或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。

10. delete + in子查询不走索引！

当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select + in子查询，却可以走索引。

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

11、group by使用临时表

group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。

关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

慢SQL如何定位呢？

• 慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
• 服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。

SQL优化

分页优化

索引优化

JOIN优化

排序优化

UNION优化

不停机扩容怎么实现？

第一阶段：在线双写，查询走老库

1. 建立好新的库表结构，数据写入旧库的同时，也写入拆分的新库
2. 数据迁移，使用数据迁移程序，将旧库中的历史数据迁移到新库
3. 使用定时任务，新旧库的数据对比，把差异补齐

第二阶段：在线双写，查询走新库

1. 完成了历史数据的同步和校验
2. 把对数据的读切换到新库

第三阶段：旧库下线

1. 旧库不再写入新的数据
2. 经过一段时间，确定旧库没有请求之后，就可以下线老库