23MySQL

索引哪些情况会失效

InnoDB引擎: 主键索引、普通索引
InnoDB用了B+树的结构来存储索引数据。
索引失效口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
like 百分写最右， 覆盖索引不写 * ；
不等控制还有or，索引失效要少用；
var引号不能丢， sql 高级也不难
1）index_a_b_c 第一个没匹配上或者中间b断了，则索引失效。
2）函数计算
3）范围 between and 大于小于等于等情况
4）百分号在左边则失效，建议所有的模糊只做右模糊 -> 符合最左匹配
5）select *
6）is (not) null
7) 使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是
索引列的时候，才会生效
7) 字符串 加上 ’ ’ 假设字段为varchar 存的也是数字 0 1 2 ，那么赋值数字也是没问题的，但是索引会失效

索引不适合的场景

1、 数据量少的不适合加索引
2、更新比较频繁的也不适合加索引
3、区分度低的字段不适合加索引（如性别）

索引的潜规则

1、覆盖索引
2、回表
3、索引数据结构（B+树）
4、最左前缀原则
5、索引下推

调优

explain命令来查看sql的执行计划，然后针对性的进行调优即可

id

代表sql中查询语句的序列号，序列号越大则执行的优先级越高，序号一样谁在前谁先执行。id为null则最后执行。

select_type

查询类型，表示当前被分析的sql语句的查询的复杂度。这个字段有多个值。
SIMPLE：表示简单查询。
PRIMARY：表示复杂查询中的最外层的select查询语句。
SUBQUERY：表是子查询语句 跟在select 关键字后面的select查询语句；
DERIVED： 派生查询，跟在一个select查询语句的from关键字后面的select查询语句

table

表示当前访问的表的名称。

当from中有子查询时，table字段显示的是 N为derived的id的值。

partitions

返回的是数据分区的信息(不经常用）

type

这个字段决定mysql如何查找表中的数据，查找数据记录的大概范围。这个字段的所有值表示的从最优到最差依次为：

system > const > eq_ref > ref > range > index > all;

一般来说我们优化到range就可以了 最好到ref。

1）null：type字段的值如果为null，那么表示当前的查询语句不需要访问表，只需要从索引树中就可以获取我们需要的数据；

一般如果是主键索引的话 ，查询主键字段或者唯一索引的话 查询主键字段 type字段的值就为null。

explain select id from table where id =1;

2）system/const
用户主键索引或者唯一索引查询时，只能匹配1条数据 一般可以对sql查询语句优化成一个常量，那么type一般就是system或者const，system是const的一个特例。

explain select * from (select * from table where id = 1) tmp;

3）eq_ref
在进行连接查询时，例如left join 时，如果是使用主键索引或者唯一索引连接查询 ，结果返回一条数据，则type的值为一般为eq_ref。

explain SELECT * from a left join b on b.id = a.bId;

on的连接的字段为主键/唯一键
4） ref
相比较eq_ref,不使用主键索引或者唯一索引，使用的是普通索引或者唯一索引的部分前缀，索引与一个值进行比较后可能获取到多个符合条件的行，不在是唯一的行了。
on的连接的字段为普通索引/唯一索引的部分前缀
5）range
通常使用范围查找，例如between，in，<,>,>=等使用索引进行范围检索。
6）index
扫描索引树就能获取到的数据，一般是扫描二级索引，并且不会从根节点扫描，一般直接扫描二级索引的叶子节点，速度比较慢。因为二级索引叶子节点不保存表中其他字段数据 只保存主键，所以二级索引还是比较小的，扫描速度相比All还是很快的。
这里用到了覆盖索引，什么是覆盖索引：可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历name索引树就可以获取到主键id的值就是覆盖索引

假设有个索引为index_name

select id, name from  table 

那么命中索引+主键索引
索引会扫描idx_name索引树的叶子节点，那么根据B+Tree树的结构，叶子节点保存的是name字段的索引值 和 data数据（主键id）。而正好我们只需要查询id和name两个字段，我们查询的字段被索引（二级索引）给覆盖了 这就是覆盖索引，因此 type的类型就是index。

7）ALL
这是一种效率最低的type，需要扫描主键索引树的叶子节点，获取数据是表中其他列的数据，即全表扫描。

select remark from table 

没索引，且没命中任何索引，则进行全表查询 select_type 为ALL

possible_keys

这个字段显示的是sql在查询时可能使用到的索引，但是不一定真的使用，只是一种可能。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提
高查询性能，然后用 explain 查看效果。

key

sql执行中真正用到的索引字段。

key_len

用到的索引字段的长度，通过这个字段可以显示具体使用到了索引字段中的哪些列（主要针对联合索引）：计算公式如下

字符串
char(n)：n字节长度
varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度

数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节

时间类型
date：3字节
timestamp：4字节
datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索
引。

ref

表示那些列或常量被用于查找索引列上的值

rows

表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据。

filtered

Extra

展示一些额外信息。

Using filesort
这种情况是在使用 order by 关键字的时候，如果待排序的内容无法通过索引直接直接进行排序，mysql就有可能进行文件排序。

当然不是说出现了此情况就会对sql语句的效率造成影响。但是由于查询次数过多的话，对于排序的效率还是有一定的影响的。
可以通过设置 max_length_for_sort_data 来 提高 order by 的效率。如果操作的数据大小高于max_length_for_sort_data 的缓存大小时，mysql会产生临时表进行查询，一定程度上会印象效率。 max_length_for_sort_data的默认值是1024。
优化方案：

优化业务逻辑，不使用 order by ，而在业务代码中执行排序方法
设置索引，将带排序的内容放在索引中，直接利用索引进行排序
Using index

使用索引，表示索引能够覆盖所有的查询字段,无需进行回表查询所以效率会高。大部分情况代表最优

Using where 单独出现
表示当前查询的字段不能被索引覆盖，所以可能会产生回表，效率比前者低

Using where;Using index
表示查询的列被索引覆盖，且where筛选条件是索引列前导列的一个范围，或者是索引列的非前导列。 效率也比较高

null
表示查询的列未被索引覆盖，且where筛选条件是索引的前导列，这意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表查询”来实现，因而性能也比前两者差。

Using index condition
表示查询条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。

explain分析sql：https://blog.csdn.net/bugNoneNull/article/details/107489256
explain sql的 extra解释：https://blog.csdn.net/Harswlgb/article/details/121907305

解决死锁问题

死锁理论:

1. 死锁，就是两个或者两个以上的线程在执行过程中，去争夺同一个共享资源导致互相等待的现象。
   在没有外部干预的情况下，线程会一直处于阻塞状态，无法往下执行。
2. 产生死锁需要满足的条件
   互斥条件， 共享资源x和y只能被一个线程占用
   请求和保持条件， 线程t1已经获取共享资源x，在等待共享资源y的时候，不释放共享资源x
   不可抢占 条件， 其他线程不能强行抢占线程t1占有的资源
   循环等待条件， 线程t1等到线程t2占有的资源，线程t2等待线程t1占有的资源，形成循环等待
3. 破环死锁
   线程在产生死锁以后，只能通过外部干预来解决，比如重启、或者kill线程等。
   所以我们在写代码的时候，就应该去刻意规避死锁的问题。
   也就是避免同时满足这四个条件。
   在这四个条件里面，互斥条件是锁本身的特性，无法被破坏，其他三个条件都可以被破坏。
   对于请求和保持条件，我们可以在第一次执行的时候一次性申请所有的共享资源
   比如常量，第一次请求的时候后放入缓存
   对于不可抢占条件，占用部分资源的线程在进一步申请其他资源的时候，如果申请不到，就主动释放
   它占有的资源。
   循环等待条件，可以按照顺序来申请资源，相当于给资源编号，按照编号顺序申请就可以避免循
   环等待。
   当然，死锁问题不仅仅局限在多线程领域，单反涉及到互斥锁的地方都有可能出现，
4. 具体的解决方法
   程序出现死锁，是因为在多线程环境里面两个或两个以上的线程同时满足
   互斥条件、请求保持条件、不可抢占条件、循环等待条件。
   出现死锁以后，可以通过jstack命令去导出线程的dump日志，
   然后从dump日志里面定位到具体死锁的程序代码。
   通过修改程序代码去破坏这四个条件里面的任意一个，就可以解决死锁问题。
   当然，因为互斥条件因为是锁本身的特性，所以不能被破坏。

数据库优化

mysql本身程序的优化

在my.cnf中修改。
binlog日志，默认是不开启
缓存池bufferpoll的默认大小配置等。
由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置官方只会提供一个默
认值，具体情况还得由使用者来修改。

关于配置项的修改，需要关注两个方面。

• 配置的作用域，分为会话级别和全局
• 是否支持热加载
  因此，针对这两个点，我们需要注意的是：
• 全局参数的设定对于已经存在的会话无法生效
• 会话参数的设定随着会话的销毁而失效
• 全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

搭建Mysql主从集群

单个Mysql服务容易单点故障，一旦服务器宕机，将会导致依赖Mysql数据库的应用全部无法响应。 主从集群或者主主集群可以保证服务的高可用性。
我的博客有配置主从的过程

读写分离设计

在读多写少的场景中，通过读写分离的方案，可以避免读写冲突导致的性能影响
springboot的使用场景：
引入
import com.baomidou.dynamic.datasource.annotation.DS;
使用注解
@DS（‘数据源’）
注意事项

• 不能使用事务，否则数据源不会切换，使用的还是第一次加载的数据源；
• 第一次加载数据源之后，第二次、第三次…操作其它数据源，如果数据源不存在，使用的还是第一次加载的数据源；
• 数据源名称不要包含下划线，否则不能切换。

引入分库分表机制

通过分库可以降低单个服务器节点的IO压力，通过分表的方式可以降低单表数据量，从而提升sql查询的效率。

针对热点数据

可以引入更为高效的分布式数据库，比如Redis、MongoDB等，他们可以很好的缓解Mysql的访问压力，同时还能提升数据检索性能。

分库分表

• 水平分库：以字段为依据，按照一定策略（hash、range 等），将一个库中的数
  据拆分到多个库中。
• 水平分表：以字段为依据，按照一定策略（hash、range 等），将一个表中的数
  据拆分到多个表中。
• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和
  扩展表）中。
  常用的分库分表中间件：
• sharding-jdbc（当当）
• Mycat  TDDL（淘宝）
• Oceanus(58 同城数据库中间件)  vitess（谷歌开发的数据库中间件）
• Atlas(Qihoo 360)

分库分表可能遇到的问题

• 事务问题：需要用分布式事务啦
  微服务可利用seata，但是存在bug
  之前有大佬给的建议是自己做回滚操作，比如事务中存在新增的接口，则需要提供一个回滚的减接口
• 跨节点 Join 的问题：解决这一问题可以分两次查询实现
• 跨节点的 count,order by,group by 以及聚合函数问题：分别在各个节点上得到
  结果后在应用程序端进行合并
• 数据迁移，容量规划，扩容等问题
• ID 问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单
  可以考虑 UUID
• 跨分片的排序分页问题（后台加大 pagesize 处理？）

三种应用场景

• 只分库不分表（并发高）
  当数据库的读写访问量过高，还有可能会出现数据库连接不够用的情况。这个时候我们就需要
  考虑分库，通过增加数据库实例的方式来获得更多的数据库连接，从而提升系统的并发性能。
• 只分表不分库（数据大）
  当单表存储的数据量非常大的情况下，并且并发量也不高，数据库的连接也还够用。但是数据
  写入和查询的性能出现了瓶颈，这个时候就需要考虑分表了。将数据拆分到多张表中来减少单表存储
  的数据量，从而提升读写的效率。
• 既分库又分表
  结合前面的两种情况，如果同时满足前面的两个条件，也就是数据连接也不够用，并且单表的
  数据量也很大，从而导致数据库读写速度变慢的情况，这个时候就要考虑既分库又分表。

InnoDB 与MyISAM 的区别

• 第一个，数据存储的方式不同，
  MyISAM中的数据和索引是分开存储的
  InnoDB是把索引和数据存储在同一个文件里面。
• 第二个，事务的支持不同
  MyISAM不支持事务，
  InnoDB支持ACID特性的事务处理
• 第三个 锁的支持不同
  MyISAM只支持表锁
  InnoDB可以根据不同的情况，支持行锁，表
  锁，间隙锁，临键锁
• 第四个
  MyISAM不支持外键
  InnoDB支持外键

因此基于这些特性我们在实际应用中，可以根据不同的场景来选择合适的存储引擎。
比如如果需要支持事务，那必须要选择InnoDB。如果大部分的表操作都是查询，可以选择MyISAM。

MyISAM和InnoDB到底有什么区别

MySQL 5.5以后的版本开始将InnoDB作为默认的存储引擎，之前的版本都是MyISAM。关于
MyISAM和InnoDB的区别，我总结为以下5个方面，希望能帮助到大家。
1）. 数据的存储结构不同
MyISAM，每个MyISAM在磁盘上存储成三个文件，它们以表的名字开头来命名。.frm文件存储表定义。.MYD(MYD)存储数据文件。.MYI(MYIndex)存储索引文件。
InnoDB在磁盘上保存为两个文件。.frm文件同样存储为表结构文件，.ibd文件存储的是
数据和索引文件。
MyISAM的索引和数据是分开存储的，因此索引查找的时候，MyISAM的叶子节点存
储的是数据所在的地址，而不是数据。
InnoDB叶子节点存储的是整个数据行所有的数据。
2）. 存储空间的消耗不同
MyISAM可被压缩，存储空间较小。支持三种不同的存储格式：静态表（默认，但是注
意数据末尾不能有空格，会被去掉）、动态表、压缩表。
InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数
据和索引。InnoDB所在的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表
空间），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
3）. 对事务的支持不同
MyISAM强调的是性能，每次查询具有原子性，其执行速度比Innodb类型更快，但是不提
供事务支持。
InnoDB除了提供事务支持和外部键等高级数据库功能。还具有事务提交（commit）、回
滚（rollback）和崩溃修复能力（crach recovery capabilities）等这些事务安全
（transaction-safe ACID compliant）型表。
4）. 对锁的支持不同
如果只是执行大量的查询, MyISAM是更好的选择。MyISAM在增删的时候需要锁定整个表
格，效率会低一些。
而innoDB支持行级锁，删除插入的时候只需要锁定操作行就行。如果有大量的插入、修改
删除操作，使用InnoDB性能能会更高。
5）. 对外键的支持不同
MyISAM不支持外键，而InoDB支持外键。当然，各种不同MySQL版本对两者的支持都有
所改进

总结

1）. 数据的存储结构不同
2）. 存储空间的消耗不同
3）. 对事务的支持不同
4）. 对锁的支持不同
5）. 对外键的支持不同

1、如果需要支持事务，选择InnoDB，不需要事务则选择MyISAM。
2、如果大部分表操作都是查询，选择MyISAM，有写又有读选InnoDB。
3、如果系统崩溃导致数据难以恢复，且成本高，不要选择MyISAM。

数据库索引的原理，为什么要用 B+树，为什么不用二叉树？

B树（英语：B-tree）是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。B树，概括来说是一个一般化的二叉查找树（binary search tree），可以拥有多于2个子节点。与自平衡二叉查找树不同，B树为系统大块数据的读写操作做了优化。B树减少定位记录时所经历的中间过程，从而加快存取速度。B树这种数据结构可以用来描述外部存储。这种数据结构常被应用在数据库和文件系统的实现上。

常规回答

B+树非叶子节点上是不存储数据的，仅存储键值
B 树节点中不仅存储键值，也会存储数据。
innodb 中页的默认大小是 16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数
（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数有会再
次减少，数据查询的效率也会更快。
B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+树使得
范围查找，排序查找，分组查找以及去重查找变得异常简单。

原理

为什么采用B树

• 常规的数据库存储引擎，一般都是采用B树或者B+树来实现索引的存储。
• B树是一种多路平衡树，用这种存储结构来存储大量数据，它的整个高度会相比二叉树来说，会
  矮很多。
• 对于数据库来说，所有的数据必然都是存储在磁盘上的，而磁盘IO的效率实际上是很低的，特别
  是在随机磁盘IO的情况下效率更低。
• 树的高度能够决定磁盘IO的次数，磁盘IO次数越少，对于性能的提升就越大

MySQL的innodb

• B+树的所有数据都存储在叶子节点，非叶子节点只存储索引。
• 叶子节点中的数据使用双向链表的方式进行关联。

B+树来实现索引的原因

1、从磁盘I/O效率方面来看：B+树的非叶子节点不存储数据，所以树的每一层就能够存储
更多的索引数量，也就是说，B+树在层高相同的情况下，比B树的存储数据量更多，间接会减少
磁盘I/O的次数。
2、从范围查询效率方面来看：在MySQL中，范围查询是一个比较常用的操作，而B+树的
所有存储在叶子节点的数据使用了双向链表来关联，所以B+树在查询的时候只需查两个节点进
行遍历就行，而B树需要获取所有节点，因此，B+树在范围查询上效率更高。
3、从全表扫描方面来看：因为，B+树的叶子节点存储所有数据，所以B+树的全局扫描能
力更强一些，因为它只需要扫描叶子节点。而B树需要遍历整个树。
4、从自增ID方面来看：基于B+树的这样一种数据结构，如果采用自增的整型数据作为主键，
还能更好的避免增加数据的时候，带来叶子节点分裂导致的大量运算的问题。

聚集索引与非聚集索引的区别

简短回答聚集索引与非聚集索引的区别

• 聚集索引就是通过主键来构建的索引结构。
• 非聚集索引就是除了主键以外的其他索引。

原理

聚集索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也
叫做二级索引。
聚集索引

• InnoDB引擎里面，一张表的数据对应的物理文件本身就是按照B+树来组织的一种索引结构，
  而聚集索引就是按照每张表的主键来构建一颗B+树，然后叶子节点里面存储了这个表的每一行数据
  记录。

• 基于InnoDB这样的特性，聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。
  同时也意味着每个表里面必须要有一个主键，如果没有主键，InnoDB会默认选择或者添加一个隐藏
  列作为主键索引来存储这个表的数据行。一般情况是建议使用自增id作为主键，这样的话id本身具有
  连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。否则，如果使用
  uuid这种随机id，那么在频繁插入数据的时候，就会导致随机磁盘IO，从而导致性能较低。

• InnoDB里面只能存在一个聚集索引，原因很简单，如果存在多个聚集索引，那么意
  味着这个表里面的数据存在多个副本，造成磁盘空间的浪费，以及数据维护的困难。

• 主键索引表示的是一种数据存储结构，所以如果是基于非聚集索引来查询一条
  完整的记录，最终还是需要访问主键索引来检索。

limit100000

• 方案一：如果 id 是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下 limit
  select id，name from employee where id>1000000 limit 10.
• 方案二：在业务允许的情况下限制页数：
  建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。
• 方案三：order by + 索引（id 为索引）
  select id，name from employee order by id limit 1000000，10
• 方案四：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获
  取的 id 段，然后再关联）

合适的分布式主键方案

数据库自增长序列或字段。

• UUID。
• Redis 生成 ID
• Twitter 的snowflake 算法
• 利用 zookeeper 生成唯一 ID
• MongoDB 的 ObjectId

事务的隔离级别有哪些？

读未提交（Read Uncommitted）
读已提交（Read Committed）
可重复读（Repeatable Read）
串行化（Serializable）
Mysql 默认的事务隔离级别是可重复读(Repeatable Read)

什么是幻读，脏读，不可重复读呢？

• 脏读
  假设有两个事务T1/T2同时在执行，T1事务有可能会读取到T2事务未提交的数据，但是未提交的事
  务T2可能会回滚，也就导致了T1事务读取到最终不一定存在的数据
• 不可重复读
  假设有两个事务T1/T2同时执行，事务T1在不同的时刻读取同一行数据的时候结果可能不一样
• 幻读
  假设有两个事务T1/T2同时执行，事务T1执行范围查询或者范围修改的过程中，事务T2插入了一条
  属于事务T1范围内的数据并且提交了，这时候在事务T1查询发现多出来了一条数据，或者在T1事务
  发现这条数据没有被修改，看起来像是产生了幻觉

读未提交，在这种隔离级别下，可能会产生脏读、不可重复读、幻读。
读已提交（RC)，在这种隔离级别下，可能会产生不可重复读和幻读。
可重复读（RR），在这种隔离级别下，可能会产生幻读
串行化，在这种隔离级别下，多个并行事务串行化执行，不会产生安全性问题。
这四种隔离级别里面，只有串行化解决了全部的问题，但也意味着这种隔离级别的性能是最低的。
在Mysql里面，InnoDB引擎默认的隔离级别是RR（可重复读），因为它需要保证事务ACID特性中
的隔离性特征

数据库的乐观锁和悲观锁

悲观锁

悲观锁她专一且缺乏安全感了，她的心只属于当前事务，每时每刻都担心着它心爱的数据可能被别的
事务修改，所以一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待
锁被释放才可以执行。

乐观锁

乐观锁的“乐观情绪”体现在，它认为数据的变动不会太频繁。因此，它允许多 个事务同时对数据
进行变动。实现方式：乐观锁一般会使用版本号机制或 CAS 算法实现。//todo 后续拓展 版本号机制和CAS算法

在高并发情况下，如何做到安全的修改同一行数据？

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和
乐观锁两种方案~

悲观锁

悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外~ 比如， 可以使用 select…
for update ~
select * from talbe where name=‘czmczm’ for update
以上这条 sql 语句会锁定了 User 表中所有符合检索条件（name=‘jay’）的记录。本次事务提交
之前，别的线程都无法修改这些记录。 使用乐观锁

for update

select 查询语句是不会加锁的，但是 select for update 除了有查询的作用外， 还会加锁呢，而且它
是悲观锁哦。至于加了是行锁还是表锁，这就要看是不是 用了索引/主键啦。
没用索引/主键的话就是表锁，否则就是是行锁。

乐观锁

思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过， 就可以修改成功，如
果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或 CAS 算法实
现。

MySQL 事务的四大特性以及实现原理

• 原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都
  不执行。
• 一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给 B 账户转 10 块钱，不管成功与否，A 和B 的总金额是不变的。
• 隔离性： 多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效
  果。简言之，就是事务之间是进水不犯河水的。
• 持久性： 表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

如果某个表有近千万数据，CRUD比较慢，如何优化

• 分表方案（水平分表，垂直分表，切分规则hash等）
• 分库分表中间件（Mycat，sharding-jdbc等）
• 分库分表一些问题（事务问题？跨节点Join的问题）
• 解决方案（分布式事务等）
• 索引优化

mysql中in和exists的区别

select * from A where id in (select id from B)
select * from A where exists(select id from Bwhere A.id = B.aId)

mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。
B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exists，这就是in和exists的区别。

数据库自增主键可能遇到什么问题

• 使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。
  解决方案话，简单点的话可以考虑使用 UUID 哈
• 自增主键会产生表锁，从而引发问题
• 自增主键可能用完问题。

MVCC原理：

MVCC原理解释

• 事务版本号
• 表的隐藏列
• undo log
• read view

数据库中间件

• sharding-jdbc 目前是基于 jdbc 驱动，无需额外的 proxy，因此也无需关注proxy 本身的高
  可用。
• Mycat 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个MySQL 数据
  库，而 Sharding-JDBC 是基于 JDBC 接口的扩展，是以 jar 包的形式提供轻量级服务的。

MYSQL 的主从延迟

主从复制过程

分了五个步骤进行：

• 步骤一：主库的更新事件(update、insert、delete)被写到 binlog
• 步骤二：从库发起连接，连接到主库。
• 步骤三：此时主库创建一个 binlog dump thread，把binlog 的内容发送到从库。
• 步骤四：从库启动之后，创建一个 I/O 线程，读取主库传过来的 binlog 内容并写入到 relay
  log
• 步骤五：还会创建一个 SQL 线程，从 relay log 里面读取内容，从Exec_Master_Log_Pos
  位置开始执行读取到的更新事件，将更新内容写入到slave 的db

主从同步延迟的原因

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

主从同步延迟的解决办法

• 主服务器要负责更新操作，对安全性的要求比从服务器要高，所以有些设置参数可以修改，
  比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置等。
• 选择更好的硬件设备作为 slave。
• 把一台从服务器当度作为备份使用， 而不提供查询， 那边他的负载下来了， 执行relay log 里面的 SQL 效率自然就高了。
• 增加从服务器喽，这个目的还是分散读的压力，从而降低服务器负载。

大表查询的优化方案

• 优化 shema、sql 语句+索引；
• 可以考虑加缓存，memcached, redis，或者 JVM 本地缓存；
• 主从复制，读写分离；
• 分库分表。

MySql逻辑图

逻辑架构图三层结构

• 第一层负责连接处理，授权认证，安全等等
• 第二层负责编译并优化 SQL
• 第三层是存储引擎。

第二层

• 先检查该语句是否有权限
• 如果没有权限，直接返回错误信息
• 如果有权限，在 MySQL8.0 版本以前，会先查询缓存。
• 如果没有缓存，分析器进行词法分析，提取 sql 语句 select 等的关键元素。然后
  判断sql 语句是否有语法错误，比如关键词是否正确等等。
• 优化器进行确定执行方案
• 进行权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引
  擎接口，返回执行结果

InnoDB引擎中的索引策略

• 覆盖索引
• 最左前缀原则
• 索引下推
• 索引下推优化是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字
  段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
• 最左前缀原则：MySQl 建立联合索引时，会遵循最左前缀匹配的原则，
  即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了
  (a)、(a,b)、(a,b,c)三个索引。
• 覆盖索引：只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无
  需回表，速度更快

MYSQL数据库服务器性能分析的方法命令有哪些

Show status, 一些值得监控的变量值

Bytes_received 和 Bytes_sent 和服务器之间来往的流量。

• Com_*服务器正在执行的命令。
• Created_*在查询执行期限间创建的临时表和文件。
• Handler_*存储引擎操作。
• Select_*不同类型的联接执行计划。
• Sort_*几种排序信息
  

Show profiles 是MySql 用来分析当前会话 SQL 语句执行的资源消耗情况

Blob 和text 有什么区别

• Blob 用于存储二进制数据，而 Text 用于存储大字符串。
• Blob 值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基
  于列值中的字节的数值。
• text 值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符
  集的排序规则对值进行排序和比较

Mysql中几种锁

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

mysql的内连接、左连接、右连接

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

内连接、外连接、交叉连接、笛卡尔积

内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。

外连接（outer join）：取得两张表中满足存在连接匹配关系的记录，以及某张表

（或两张表）中不满足匹配关系的记录。

左外连接

左外连接 = 内连接 + 左边表中失配的元组
其中，缺少的右边表中的属性值用NULL表示

右外连接

右外连接 = 内连接 + 右边表中失配的元组
其中，缺少的左边表中的属性值用NULL表示

全外连接

全外连接 = 内连接 + 左边表中失配的元组 + 右边表中失配的元组。
其中，缺少的左边表或者右边表中的属性值用NULL表示

交叉连接（cross join）

显示两张表所有记录一一对应，没有匹配关系进行筛选，、也被称为：笛卡尔积。

数据库的三大范式

• 第一范式：数据表中的每一列（每个字段）都不可以再拆分。
• 第二范式：在第一范式的基础上，分主键列完全依赖于主键，而不能是依赖于主键
  的一部分。
• 第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其
  他非主键。

Mysql 的binlog 有几种录入格式

• statement，记录的是 SQL 的原文。不需要记录每一行的变化，减少了binlog 日志量，节约了 IO，提高性能。由于 sql 的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row，不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。记录单元为
  每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改
  动(比如 alter table)，因此这种模式的文件保存的信息太多，日志量太大。
• mixed，一种折中的方案，普通操作使用 statement 记录，当无法使用 statement 的时候使用 row。

InnoDB引擎的 4 大特性

• 插入缓冲（insert buffer)
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

后面拓展这些的概念

索引有哪些优缺点

优点

• 索引，是一种能够帮助Mysql高效从磁盘上检索数据的一种数据结构。

• 在Mysql中的InnoDB引擎中，采用了B+树的结构来实现索引和数据的存储

• 通过B+树的结构来存储数据，可以大大减少数据检索时的磁盘IO次数，从而提升数据查询的性能
  B+树索引在进行范围查找的时候，只需要找到起始节点，然后基于叶子节点的链表结构往下读取即
  可，查询效率较高。

• 通过唯一索引约束，可以保证数据表中每一行数据的唯一性

缺点

• 数据的增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，索引的维护会带来较大
  的性能开销。
• 一个表中允许存在一个聚簇索引和多个非聚簇索引，但是索引数不能创建太多，否则造成的索引维护
  成本过高。
• 创建索引的时候，需要考虑到索引字段值的分散性，如果字段的重复数据过多，创建索引反而会带来
  性能降低。

索引类型

• 主键索引: 数据列不允许重复，不允许为 NULL，一个表只能有一个主键。
• 唯一索引: 数据列不允许重复，允许为 NULL 值，一个表允许多个列创建唯一索引。
• 普通索引: 基本的索引类型，没有唯一性的限制，允许为 NULL 值。
• 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
• 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
• 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

创建索引原则

-最左前缀匹配原则

• 频繁作为查询条件的字段才去创建索引
• 频繁更新的字段不适合创建索引
• 索引列不能参与计算，不能有函数操作
• 优先考虑扩展索引，而不是新建索引，避免不必要的索引
• 在 order by 或者group by 子句中，创建索引需要注意顺序
• 区分度低的数据列不适合做索引列(如性别）
• 定义有外键的数据列一定要建立索引。
• 对于定义为 text、image 数据类型的列不要建立索引。
• 删除不再使用或者很少使用的索引

创建索引

• 在执行 CREATE TABLE 时创建索引
• 使用 ALTER TABLE 命令添加索引
  ALTER TABLE table_name ADD INDEX index_name (column);
• 使用 CREATE INDEX 命令创建所以
  CREATE INDEX index_name ON table_name (column);

百万级别或以上的数据，你是如何删除的？

• 我们想要删除百万数据的时候可以先删除索引
• 然后批量删除其中无用数据
• 删除完成后重新创建索引

覆盖索引、回表

• 覆盖索引： 查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列
  要被所使用的索引覆盖。
• 回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，
  再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。
  比如：
  有一个索引 index_age
  select age from student where age<20
  列包含在索引中，则不需要回表。
  反而则需要回表。

联合索引是什么

联合索引，用户可以在多个列上建立索引,这种索引叫做联合索引。
联合索引遵守最左原则

数据库隔离级别与锁的关系（这个知识点我感觉有点绕）

可以先阐述四种隔离级别，再阐述它们的实现原理。隔离级别就是依赖锁和MVCC实现的。

InnoDb的锁

1、共享/排他锁（行级）

**行级锁**
共享锁（简称 S 锁）：简称为 S 锁，**在事务要读取一条记录时，需要先获取该记录的 S 锁**
排他锁（简称 X锁）：简称 X 锁，**在事务需要改动一条记录时，需要先获取该记录的 X 锁**

事务T1 拥有R的S锁，T2请求访问这记录
- T2请求S锁 ，结果T1T2共同拥有R行的S锁
- T2请求X锁，此操作被堵塞
事务T1 持有R的X锁， T2请求R的X、S锁都不被允许，T2必须等待T1释放X锁。因为X锁和任何的锁都不兼容

X 锁和S锁

X锁和S锁的定义， 既可以在行锁，又可以在表锁。
表锁：
如果一个事务给表已经加了 S 锁，则：

• 别的事务可以继续获得该表的 S 锁，也可以获得该表中某些记录的 S 锁。
• 别的事务不可以继续获得该表的 X 锁，也不可以获得该表中某些记录的 X 锁。

2、意向锁（表/行级）

• 意向锁是表级锁，是一种不与行级锁冲突的表级锁。
• 在某个时刻，事务可能要加共享或者排它锁。
• 加共享锁或排他锁时的 时候，需要提前声明个意向锁

为什么要意向锁

因为InnoDB 是支持表锁和行锁共存的，如果一个事务 A 获取到某一行的排他
锁，并未提交，这时候事务 B 请求获取同一个表的表共享锁。因为共享锁和排
他锁是互斥的，因此事务 B 想对这个表加共享锁时，需要保证没有其他事务持
有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁。
然后问题来了，你要保证没有其他事务持有表中任意一行的排他锁的话，去遍
历每一行？这样显然是一个效率很差的做法。为了解决这个问题，InnoDb 的
设计大叔提出了意向锁。

意向锁分两类

• 意向共享锁：简称 IS锁，当事务准备在某些记录上加 S 锁时，需要现在表级别加
  一个 IS锁。
• 意向排他锁：简称 IX锁，当事务准备在某条记录上加上 X 锁时，需要现在表级别
  加一个 IX锁。

如果一个事务 A 获取到某一行的排他锁，并未提交,这时候表上就有意向排他
锁 和这一行的排他锁。这时候事务 B 想要获取这个表的共享锁，此时因为检
测到事务 A 持有了表的意向排他锁，因此事务 A 必然持有某些行的排他锁，
也就是说事务 B 对表的加锁请求需要阻塞等待，不再需要去检测表的每一行数
据是否存在排他锁啦。

3、记录所（Record Lock）

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE

记录锁是最简单的行锁，仅仅锁住一行
如果 C1 字段是主键或者是唯一索引的话，这个 SQL 会加一个记录锁（Record Lock）

记录锁永远都是加在索引上的，即使一个表没有索引，InnoDB 也会隐式的创建一个索引，并使用这个索引实施记录锁。
它会阻塞其他事务对这行记录的插入、更新、删除。

一般我们看死锁日志时，都是找关键词，比如 lock_mode X locks rec but not
gap），就表示一个 X 型的记录锁。记录锁的关键词就是 rec but not gap。
以下就是一个记录锁的日志

4、间隙锁（Gap Lock）

为了解决幻读问题，InnoDB 引入了间隙锁(Gap Lock)。间隙锁是一种加在两
个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。它
锁住的是一个区间，而不仅仅是这个区间中的每一条数据

5、临键锁（Next-Key Lock）

Next-key 锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录
前面间隙上的锁。说得更具体一点就是:临键锁会封锁索引记录本身，以及索引
记录之前的区间，即它的锁区间是前开后闭，比如(5,10]。

如果一个会话占有了索引记录 R 的共享/排他锁，其他会话不能立刻在 R 之前的
区间插入新的索引记录。

6、插入意向锁

插入意向锁,是插入一行记录操作之前设置的一种间隙锁，这个锁释放了一种插
入方式的信号。 它解决的问题：多个事务，在同一个索引，同一个范围区间插
入记录时，如果插入的位置不冲突，不会阻塞彼此。
假设有索引值 4、7，几个不同的事务准备插入 5、6，每个锁都在获得插入
行的独占锁之前用插入意向锁各自锁住了 4、7 之间的间隙，但是不阻塞对方
因为插入行不冲突。

锁模式兼容矩阵（横向是已持有锁，纵向是正在请求的锁）：

7、自增锁（表级）

自增锁是一种特殊的表级别锁。它是专门针对 AUTO_INCREMENT类型的列，对
于这种列，如果表中新增数据时就会去持有自增锁。简言之，如果一个事务正
在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，
是连续的主键值。

eg: 假设有一张表设置了自增主键


可以看出b还没提交事务，a提交事务，但是出现了3.

自增锁是一个表级别锁，那为什么会话 A 事务还没结束，事务 B 可以执行插入
成功呢？不是应该锁表嘛？这是因为在参数 innodb_autoinc_lock_mode上，这个
参数设置为 1的时候，相当于将这种 auto_inc lock弱化为了一个更轻量级的互斥
自增长机制去实现，官方称之为 mutex。
innodb_autoinc_lock_mode 还可以设置为 0 或者 2。

• 0：表示传统锁模式，使用表级 AUTO_INC锁。一个事务✁ INSERT-LIKE语句在语
  句执行结束后释放 AUTO_INC 表级锁，而不是在事务结束后释放。

• 1: 连续锁模式,连续锁模式对于 Simple inserts不会使用表级锁，而是使用一个轻
  量级锁来生成自增值，因为 InnoDB 可以提前直到插入多少行数据。自增值生成
  阶段使用轻量级互斥锁来生成所有的值，而不是一直加锁直到插入完成。对于bulk
  inserts类语句使用 AUTO_INC 表级锁直到语句完成。

• 2:交错锁模式,所有的INSERT-LIKE语句都不使用表级锁，而是使用轻量级互
  斥锁（我的数据库是2）

• INSERT-LIKE:指所有的插入语句，包括： INSERT、REPLACE、INSERT…
  SELECT、REPLACE…SELECT,LOAD DATA 等。

• Simple inserts:指在插入前就能确定插入行数的语句，包括：
  INSERT、REPLACE，不包含 INSERT…ON DUPLICATE KEY UPDATE 这类
  语句。

• Bulk inserts: 指在插入钱不能确定行数的语句，包括：INSERT …
  SELECT/REPLACE … SELECT/LOAD DATA。

count(1)、count(*) 与 count(列名)

• count()包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
  count() ，也就是整条数据，由于整条数据肯定不会为null，所以，专门做了优化，
  不会去取全部字段。直接扫描多少数据统计数据即可。

• count(1)包括了忽略所有列，用 1 代表代码行，在统计结果的时候，不会忽略列
  值为NULL

  count(1) 扫描到一条数据，直接返回一个数字1，不会取扫描的字段，也不会去判断是否为null。
  这2个也是我们用得最多的，性能最好的。

• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空
  不是只空字符串或者 0，而是表示 null）的计数，即某个字段值为 NULL 时，不
  统计。
  1.首先去看字段是否能走索引，如果没索引，那么扫描数据不会走索引树，比有索引的字段慢。
  2.字段是否可为null，如果可为null，那么必须要去判断扫描出来的数据是否为null，为null不统计，所以不可为null的比可为null的要快。 如果是必填的会比不必填快

varchar(50)中 50 的涵义

• 字段最多存放 50 个字符
• 如 varchar(50) 和 varchar(200) 存储 “jay” 字符串所占空间是一样的，后者在
  排序时会消耗更多内存

UNION 与UNION ALL 的区别？

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
• UNION 的效率高于 UNION ALL

SQL 的生命周期？

• 服务器与数据库的立连接
• 数据库进程拿到请求 sql
• 解析并生成执行计划，执行
• 读取数据到内存，并进行逻辑处理
• 通过步骤一的连接，发送结果到客户端
• 关掉连接，释放资源

SQL的执行顺序

1.from (左表的名字)
2.on ( join 的条件)
3.(join的类型) (右表的名字)
4.where(where的条件)
5.group by (group by 的字段)
6.Having( having 的条件)
7.select
8.distinct(要查询的字段)
9.order by desc/asc
10.limit

自增id / uuid

如果是单机的话，选择自增 ID；如果是分布式系统，优先考虑 UUID 吧，但
还是最好自己公司有一套分布式唯一 ID 生产方案吧。

• 自增 ID：数据存储空间小，查询效率高。但是如果数据量过大,会超出自增长的值
  范围，多库合并，也有可能有问题。
• uuid：适合大量数据的插入和更新操作，但是它无序的，插入数据效率慢，占用
  空间大。
• 雪花id：雪花算法生成ID是Long类型，长度64位。
  第 1 位： 符号位，暂时不用。
  第 2~42 位： 共41位，时间戳，单位是毫秒，可以支撑大约69年
  第 43~52 位： 共10位，机器ID，最多可容纳1024台机器
  第 53~64 位： 共12位，序列号，是自增值，表示同一毫秒内产生的ID，单台机器每毫秒最多可生成4096个订单ID
  – 接入非常简单，不需要搭建服务集群，。代码逻辑非常简单，，同一毫秒内，订单ID的序列号自增。同步锁只作用于本机，机器之间互不影响，每毫秒可以生成四百万个订单ID，非常强悍。
  – 生成规则不是固定的，可以根据自身的业务需求调整。如果你不需要那么大的并发量，可以把机器标识位拆出一部分，当作业务标识位，标识是哪个业务线生成的订单ID。### mysql 自增主键用完了怎么办?
  – 雪花算法严重依赖系统时钟。如果时钟回拨，就会生成重复ID
  自增主键一般用 int 类型，一般达不到最大值，可以考虑提前分库分表

读写分离常见方案

• 应用程序根据业务逻辑来判断，增删改等写操作命令发给主库，查询命令发给备库。
  从库-> 查询 主库增删改
• 利用中间件来做代理，负责对数据库的请求识别出读还是写，并分发到不同的数据
  库中。（如：amoeba，mysql-proxy）

MySQL的复制原理以及流程

• 主数据库有个 bin-log 二进制文件，纪录了所有增删改 Sql 语句。（binlog 线程）
• 从数据库把主数据库的 bin-log 文件的 sql 语句复制过来。（io 线程）
• 从数据库的relay-log 重做日志文件中再执行一次这些 sql 语句。（Sql 执行
  线程）
  

上图主从复制分了五个步骤进行：
步骤一：主库的更新事件(update、insert、delete)被写到 binlog
步骤二：从库发起连接，连接到主库。
步骤三：此时主库创建一个 binlog dump thread，把 binlog 的内容发送到从库。
步骤四：从库启动之后，创建一个 I/O 线程，读取主库传过来的binlog 内容并写入到 relay log
步骤五：还会创建一个 SQL 线程，从 relay log 里面读取内容，从Exec_Master_Log_Pos 位置开始
执行读取到的更新事件，将更新内容写入到slave 的 db

Innodb 的事务实现原理(ACID)

• 原子性：是使用 undo log 来实现的，如果事务执行过程中出错或者用户执行
  了rollback，系统通过 undo log 日志返回事务开始的状态。
• 持久性：使用 redo log 来实现，只要 redo log 日志持久化了，当系统崩溃，即可
  通过redo log 把数据恢复。
• 隔离性：通过锁以及 MVCC,使事务相互隔离开。
• 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

Innodb 的事务与日志的实现方式

innodb 两种日志 redo 和 undo

redo

在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的
文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。

undo

在 MySQL5.5 之前， undo 只能存放在 ibdata 文件里面， 5.6 之后，
可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata 之外。

事务是如何通过日志来实现的

• 因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo的 redo， 然
  后修改数据页，再记数据页修改的redo。 Redo（里面包括 undo ✁修改） 一
  定要比数据页先持久化到磁盘。
• 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的状态，崩溃恢复
  时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo 把该事
  务修改回滚到事务开始之前。
• 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

一个 6 亿的表 a，一个 3 亿的表 b，通过外间 tid 关联，你如何最快地查询出满足条件的第 50000 到第 50200 中的这200 条数据记录。

1、如果 A 表 TID 是自增长,并且是连续的,B 表的 ID 为索引 select * from a,bwhere
a.tid = b.id and a.tid>500000 limit 200;
2、如果 A 表的 TID 不是连续的,那么就需要使用覆盖索引.TID 要么是主键,要
么是辅助索引,B 表 ID 也需要有索引。 select * from b , (select tid from alimit
50000,200) a where b.id = a .tid;

Mysql 一条 SQL 加锁分析

一条 SQL 加锁，可以分 9 种情况进行：

• 组合一：id 列是主键，RC 隔离级别
• 组合二：id 列是二级唯一索引，RC 隔离级别
• 组合三：id 列是二级非唯一索引，RC 隔离级别
• 组合四：id 列上没有索引，RC 隔离级别
• 组合五：id 列是主键，RR 隔离级别
• 组合六：id 列是二级唯一索引，RR 隔离级别
• 组合七：id 列是二级非唯一索引，RR 隔离级别
• 组合八：id 列上没有索引，RR 隔离级别
• 组合九：Serializable 隔离级别

Mysql如何解决幻读问题

在RR(也就是可重复读)的事务隔离级别下，InnoDB采用了MVCC机制来解决幻读问题。
MVCC就是一种乐观锁的机制，它通过对不同事务生成不同的快照版本，通过UNDO版本链进行管
理并且在MVCC里面，规定了高版本能够看到低版本的事务变更，低版本看不到高版本的事务变更从
而实现了不同事务之间的数据隔离，解决了幻读的问题。
但是在当前读的情况下，是直接读取内存的数据，跳过了快照度，所以还是会出现幻读问题。
我认为可以通过两个方式来解决。
第一种是尽量避免当前读的情况
第二种是引入LBCC的方式