不断的学习,我们才能不断的前进
一个好的程序员是那种过单行线马路都要往两边看的人

Mysql面试

mysqlzhi-shi-tu-pu

InnoDB 内存结构和磁盘结构

InnnoDB 的数据都是放在磁盘上的,InnoDB 操作数据有一个最小的逻辑单位,叫做页(索引页和数据页)。我们对于数据的操作,不是每次都直接操作磁盘,因为磁盘的速度太慢了。InnoDB 使用了一种缓冲池的技术,也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫 Buffer Pool。

DML修改数据的时候,先修改缓冲池里面的页。当内存的数据页和磁盘数据不一致的时候,我们把它叫做脏页。InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘,每隔一段时间就一次性地把多个修改写入磁盘,这个动作就叫做刷脏。
innodbjie-gou

InnoDB 内存结构

InnoDB 内存结构有4个部分部分:Buffer Pool、Change Buffer、Adaptive HashIndex,log buffer

Buffer Pool :缓冲池,以Page页为单位,默认大小16K,Buffer Pool的底层采用链表数据结构管理Page。在InnoDB访问表记录和索引时会在Page页中缓存,以后使用可以减少磁盘IO操作,提升效率,数据可以直接在Buffer Pool中进行处理,当缓存满了以后,使用LRU算法淘汰最近最不常使用的页,并且 MySQL对LRU进行了优化,采用的是把双向链表分成两部分,进行冷热数据分离,冷数据占3/8左右,而且如果访问热点数据时,并且占链表的前1/4,则每次查询的时候不移动到头部,否则移动到头部;为了增加并发量,减少锁的竞争,还可以使用多个Buffer Pool。

Change Buffer:写缓冲区, 在进行DML(删除、更新、插入)操作时,如果Buffer Pool没有其相应的Page数据,并不会立刻将磁盘页加载到缓冲池,而是在Change Buffer 记录缓冲变更,等未来数据被读取时,再将数据合并恢复到Buffer Pool中。写缓冲区,仅适用于非唯一普通索引页, 因为如果在索引设置唯一性,在进行修改时,InnoDB必须要做唯一性校验,因此必须查询磁盘,做一次IO操作。会直接将记录查询到BufferPool中,然后在缓冲池修改,不会在ChangeBuffer操作。
Adaptive Hash Index:自适应哈希索引,用于优化对BP数据的查询。InnoDB引擎会监控对索引页的查询,如果发现建立哈希索引可以带来性能上的提升,就会建立哈希索引,这种称之为自适应哈希索引,InnoDB引擎不支持手动创建哈希索引。

log buffer: 日志缓冲区, 用来保存要写入磁盘上log文件(Redo/Undo)的数据,日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘,当遇到 BLOB或多行更新的大事务操作时,增加日志缓冲区可以节省磁盘I/O
redolog用来做持久化的,如果 Buffer Pool 里面的脏页还没有刷入磁盘时,数据库宕机或者重启,就会导致这些数据丢失,所以引入redo log 日志文件记录所有的修改操作,并在数据库启动时从这个文件进行恢复操作。

InnoDB 磁盘结构

InnoDB磁盘主要包含 表空间和redolog。

Tablespaces: 表空间,用于存储表结构和数据。分为四大类:System Tablespace,File-Per-Table Tablespaces,General Tablespaces,Undo Tablespaces。

  • System Tablespace: InnoDB data dictionary,doublewrite buffer, change buffer, undo logs。
    • doublewrite buffer:存储引擎正在将写入页的数据到磁盘时发生了宕机,可能出现页只写了一部分的情况,这种情况叫做部分写失效,就会导致页损坏,页损坏了就无法使用redo log来进行恢复,所以需要保存一个页的副本,用来和redolog恢复数据。
    • undo logs:undo log记录了单个事务对聚集索引数据记录的最近一次修改信息,用来保证在必要时实现回滚

Reference

InnoDB 存储结构

1. 数据库的索引,引擎,隔离级别,不同隔离级别遇到的问题,InnoDB默认的是什么级别? MVCC如何实现隔离级别?

索引

Mysql索引是存储引擎用于快速找到记录的一种数据结构,索引可以包含一个或多个列的值,如果索引包含多个列,那么列的顺序也十分重要,因为Mysql只能高效地使用索引的最左前缀列

Mysql索引

Mysql 底层数据是分页进行存储的,每一页里面放的是一行一行的数据,一页大小默认是16kb,一个区里面存放64页。因为磁盘IO是非常耗时的,所以每次进行IO读取的时候,读取的是一整页的数据,到B+树的叶子结点里面。
磁盘IO与预读
磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。
每一次IO读取的数据我们称之为一页(page),也就是我们读取一页内的数据时候,实际上才发生了一次IO。
索引的目的就是每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。

IO次数取决于b+数的高度h

存储引擎

InnoDB
InnoDB 是 MySQL 默认的事务型存储引擎,只要在需要它不支持的特性时,才考虑使用其他存储引擎。
InnoDB 采用 MVCC 来支持高并发,并且实现了四个标准隔离级别(未提交读、提交读、可重复读、可串行化)。其默认级别时可重复读(REPEATABLE READ),在可重复读级别下,通过 MVCC + Next-Key Locking 防止幻读
主键使用聚簇索引,在索引中保存了数据,从而避免直接读取磁盘,因此对主键查询有很高的性能。
InnoDB 内部做了很多优化,包括从磁盘读取数据时采用的可预测性读(预读策略,读取一页的大小),并且能够自动在内存中创建 hash 索引以加速读操作的自适应哈希索引,以及能够加速插入操作的插入缓冲区等。
InnoDB 支持真正的在线热备份,MySQL 其他的存储引擎不支持在线热备份。

MyISAM
设计简单,数据以紧密格式存储。对于只读数据,或者表比较小、可以容忍修复操作,则依然可以使用它。提供了大量的特性,包括压缩表、空间数据索引等。不支持事务。不支持行级锁,只能对整张表加锁,读取时会对需要读到的所有表加共享锁,写入时则对表加排它锁。但在表有读取操作的同时,也可以往表中插入新的记录,这被称为并发插入(CONCURRENT INSERT)。
可以手工或者自动执行检查和修复操作,但是和事务恢复以及崩溃恢复不同,可能导致一些数据丢失,而且修复操作是非常慢的。
如果指定了 DELAY_KEY_WRITE 选项,在每次修改执行完成时,不会立即将修改的索引数据写入磁盘,而是会写到内存中的键缓冲区,只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能,但是在数据库或者主机崩溃时会造成索引损坏,需要执行修复操作。
比较:

  • 事务:InnoDB 是事务型的,可以使用 Commit 和 Rollback 语句。
  • 并发:MyISAM 只支持表级锁,而 InnoDB 还支持行级锁。
  • 外键:InnoDB 支持外键。
  • 备份:InnoDB 支持在线热备份。
  • 崩溃恢复:MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多,而且恢复的速度也更慢。
  • 其它特性:MyISAM 支持压缩表和空间数据索引
    存储引擎

隔离级别

  • READ UNCOMMITTED 未提交读:在READ UNCOMMITTED级别中,事务的修改,即使没有提交,对其他事务也是可见的。事务可以读取未提交的数据,这就是脏读。

  • READ COMMITTED 提交读:READ COMMITTED满足隔离性,一个事务开始只能看见已经提交的事务所做的修改。

  • REPEATABLE READ 可重复读:REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一样的。可重复读是MYSQL的默认隔离级别。

  • SERIALIZABLE 可串行化:SERIALIZABLE是最高的隔离级别,它通过强制事务串行执行,避免了幻读的问题

事务隔离级别 脏读 不可重复读 幻读
读未提交 $ \surd $ $ \surd $ $ \surd $
不可重复读 $ \times $ $ \surd $ $ \surd $
可重复读 $ \times $ $ \times $ $ \surd $
串行化 $ \times $ $ \times $ $ \times $
  • 脏读:读取到的是其他事务未提交的数据。破坏了事务的隔离性、原子性。
  • 不可重复读:事务多次读取同一数据时,结果不一致。破坏了事务的隔离性。
  • 幻读:一个事务(同一个read view)在前后两次查询同一范围的时候,后一次查询看到了前一次查询多了没有看到的行(幻行)。破坏了事务的一致性。

不可重复读侧重于修改,幻读侧重于新增或删除。

InnoDB默认的是 REPEATABLE READ 可重复读,并且通过MVCC的快照读解决读操作的幻读问题当前读通过使用间隙锁+行锁(next-key locking)策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行,还会对索引中的间隙进行锁定,以防止幻影行的插入。间隙锁采用左开右闭原则。

MVCC

MVCC是多版本并发控制,通过版本来控制并发情况下的数据问题,在很多情况下避免了加锁操作,比如普通的select操作不会加锁,提高了数据库并发处理的能;而写操作也只是锁定必要的行。
InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,一个是保存了行的事务ID(trx_id),一个保存了行的回滚指针(roll_pointer),用这个指针来找到修改之前的数据;每开始一个新的事务,都会自动递增产生一个新的事务ID。事务开始时刻会把事务ID,放到当前事务影响的行事务id中,当查询时需要用当前事务id和每行记录的事务id进行比较。

MVCC只能在REPEATABLE READ和 READ COMMITTED两个隔离级别下工作。

  • 在提交读隔离条件下,一致读快照(Read View)是在每次SELECT后都会生成最新的Read View,即每次SELECT都能读取到已COMMIT的数据,就会存在不可重复读、幻读 现象
  • 在可重复读的隔离条件下,Read View在第一次SELECT发起时建立,之后不会再发生变化。如果在同一个事务中发出多个非锁定SELECT语句,那么这些SELECT语句在事务提交前返回的结果是一致的。

在可重复读隔离级别下的操作:

SELECT
InnoDB会根据两个条件来检查每行数据:

  • InnoDB只会查找行的事务ID 小于或等于 当前事务ID的数据行,这样可以确保事务读取的行,要么是在事务开始之前就已经存在的,要么是事务自身插入或者修改过的。
  • 行的删除版本号要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前没有被删除。
  • 只有满足这两个条件的行才会被返回。

INSERT
InnoDB为新插入的每一行保存当前事务ID作为行事务ID。
DELETE
InnoDB为删除的每一行保存当前事务ID作为行删除标识。
UPDATE
InnoDB为插入一行新纪录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。

这样的设计可以使得读数据操作简单,性能很好,并且也能保证读取到符合标准的行。不足就是没行记录都需要额外的存储空间,需要额外的检查和维护操作。

MVCC的实现

MVCC 在mysql 中的实现依赖的是 undo log 与 read view ;
Undo log
Undo log分为插入操作的回滚日志和修改操作的回滚日志;对于插入操作,是插入一个新行,所以roll_pointer指针为空,写入undo log日志为空;
而对于修改操作,会把之前旧的记录写入到undo log日志里面,然后新产生的记录的roll_pointer指针指向undo log里面的旧记录

Read view

快照读与当前读
在可重复读级别中,通过MVCC机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,是不及时的数据,不是数据库当前的数据!这在一些对于数据的时效特别敏感的业务中,就很可能出问题,对于这种读取历史数据的方式,我们叫它快照读 (snapshot read),而读取数据库当前版本数据的方式,叫当前读 (current read)。很显然,在MVCC中:

  • 快照读:MVCC 的 SELECT 操作是快照中的数据,不需要进行加锁操作。
  • 当前读:MVCC 其它会对数据库进行修改的操作(INSERT、UPDATE、DELETE、select ... lock in share mode、select ... for update)需要进行加锁(间隙锁+行锁)操作,从而读取最新的数据。

MVCC 并不是完全不用加锁,而只是避免了 SELECT 的加锁操作
使用间隙锁避免幻读

锁算法:

  • Record Lock(行锁):锁定一个记录上的索引,而不是记录本身。
  • Gap Lock(间隙锁):锁定索引之间的间隙,但是不包含索引本身。
  • Next-Key Lock:是 Record Locks 和 Gap Locks 的结合,不仅锁定一个记录上的索引,也锁定索引之间的间隙。例如一个索引包含以下值:10, 11, 13, and 20,那么就要锁定(-∞, 10]、(10, 11]、(11, 13]、(13, 20]、(20, +∞)。
// 对于间隙锁:不能在 t.c 中插入 15。
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE; // 锁定索引(-∞,10]、(10,15]、(15,+∞]

在 InnoDB 存储引擎中,SELECT 操作的不可重复读问题通过 MVCC 得到了解决,而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决,INSERT 的不可重复读问题是通过 Next-Key Lock(Record Lock + Gap Lock)解决的。

隔离级别的实现

读未提交

在此隔离级别下,事务之间可以读取彼此未提交的数据,在所有写操作执行时都会加排它锁,但是锁的释放是在执行完写操作之后立即释放,而不是事务提交之后,所以其他事务有可能读取到其他事务未提交的数据,所以会出现脏读、不可重复读、幻读

读已提交

读已提交的就是将锁的释放时机延迟到事务提交之后,从而可以实现读提交,解决了脏读的问题。但是锁的释放时间延迟了,所以在写写、读写的情况下锁竞争时间变长了,大大降低了并发量。
所以提出了MVCC的接近方案,在读写竞争的时候,使用快照读,每次select都读取当前事务最新的快照信息,不加任何锁,解决读写之间的并发问题。对于写写情况下的并发问题,采用的是加行锁的,而且在执行完WHERE条件筛选之后,会立即释放掉不符合条件的行锁。
但是也存在幻读 和 不可重复读的问题

  • 不可重复读:是因为每次select时,都会重新生成新的快照,这就意味着,如果事务A中执行多次的select,但是在每次select之间有其他事务更新了我们读取的数据并提交了,那就出现了不可重复读。
  • 幻读 是因为写写加的是行锁,其他事务往间隙里面插入或者删除数据时,就会出现幻读,也就是同一个事务两次读,会出现新增的或减少的数据。

可重复读

虽然读已提交的隔离级别依然有较大的数据可靠性能问题,但是做进一步限制即可避免幻读和不可重复读

  • 避免不可重复读:在每次生成的select快照,都是事务在第一次select时生成版本,后续的查询都是在这个版本上进行操作。
  • 避免幻读:采用行锁+间隙锁,从而阻塞其他事务在遍历范围内进行写操作,从而避免了幻读。
    虽然可重复读已经将数据可靠性和并发性能做的不错了,但是需要用户手动的进行加锁,使用select … lock in share mode和select … for update。否则还是会出现幻读,这是因为快照读只对读操作有效,对于写操作是无效的。

串行化

该级别下,会自动将所有普通select转化为select … lock in share mode执行,即针对同一数据的所有读写都变成互斥的了,可靠性大大提高,并发性大大降低,也就是所有的操作都会加锁,串行化执行,毫无并发。
在可重复读的普通select情况下还是有并发情况的,所以可重复读的并发性能要高一点。

2. 什么是事务?事务的特性?

事务是指满足 ACID 特性的一组操作,可以通过 Commit 提交一个事务,也可以使用 Rollback 进行回滚。
ACID

  • 原子性:一个事务必须被视为不可分割的最小工作单元,整个事务的所有操作要么全部成功,要么全部失败回滚。
  • 一致性:数据库总是从一个一致性的状态转换到另外一个一致性的状态。
  • 隔离性:一个事务所做的修改在最终提交以前,对其他事务是不可见的。
  • 持久性:一旦事务提交,则其所做的修改将会永远保存到数据库中。即使系统发生崩溃,事务执行的结果也不能丢。

原子性和一致性的的侧重点不同:原子性关注状态,要么全部成功,要么全部失败,不存在部分成功的状态。而一致性关注数据的可见性,中间状态的数据对外部不可见,只有最初状态和最终状态的数据对外可见。
隔离性是多个事务的时候, 相互不能干扰,一致性是要保证操作前和操作后数据或者数据结构的一致性,而我提到的事务的一致性是关注数据的中间状态,也就是一致性需要监视中间状态的数据,如果有变化,即刻回滚。
事务是怎么实现的
事务的原子性是通过 undo log 来实现的
事务的持久性性是通过 redo log 来实现的.
事务的隔离性是通过 (读写锁+MVCC)来实现的
事务的一致性是通过原子性,持久性,隔离性来实现的

3. 说一下联合索引和覆盖索引?

联合索引

联合索引就是对多个列创建索引,满足最左前缀原则。在建立索引的时候,尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间,还可以更容易的使用到索引覆盖。

覆盖索引

覆盖索引并不是说是索引结构,覆盖索引是一种很常用的优化手段。因为在使用二级索引的时候,我们只可以拿到主键值,相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况,在组合索引查询时,如果我只需要abc字段的,那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了,而不需要回表。这种情况就是覆盖索引。

4. mybatis的#{}和\${}的区别是什么

#{}和\${}都是占位符号,#{}可以避免sql注入的问题。因为在预处理阶段对于\${}只会进行简单的字符替换(这里可能存在sql注入攻击),而对于#{},会使用一个占位符 ? 代替。

5. 索引用什么,B+树优点,为什么用B+树却不用AVL树、B树、红黑树?

InnoDB采用 B+树作为索引结构,B+树是B树的一个变体,相比较于B树 非叶结点只起索引的作用,叶子节点通过顺序访问指针进行实现,B+树是多路查找树,磁盘I/O低,通常用于数据库和操作系统的文件系统中。使用B+树作为索引的优点有:

  • 不再需要进行全表扫描,只需要对树进行搜索即可,所以查找速度快很多。
  • 因为 B+ Tree 的有序性,所以除了用于查找,还可以用于排序和分组。
  • 可以指定多个列作为索引列,多个索引列共同组成键
  • 适用于全键值、键值范围和键前缀查找
    还有聚簇索引、二级索引、哈希索引、全文索引、空间数据索引。

一颗三层B+树可以存放两千多万行数据

AVL树
平衡二叉树,左右孩子高度之差不超过1,平衡条件非常严格(树高差只有1),只要插入或删除不满足上面的条件就要通过旋转来保持平衡。由于旋转是非常耗费时间的。所以适用于插入/删除次数比较少,查找比较多的场景
红黑树
通过对从根节点到叶子节点路径上各个节点的颜色进行约束,规定根结点和叶子结点是黑色,没有连续的红色结点,任意一个结点到其叶子结点有相同个数的黑色结点,所以确保没有一条路径会比其他路径长2倍,因而是近似平衡的。所以相对于严格要求平衡的AVL树来说,它的旋转保持平衡次数较少。适合,查找少,插入/删除次数多的场景
B+ 树与红黑树比较
在文件系统和索引中使用B+树而不是红黑树的原因:

  • 磁盘I/O次数低:B+ 树一个节点可以存储多个元素,相对于红黑树的树高更低,磁盘 IO 次数更少, 磁盘IO次数跟树的高度有关。
  • 磁盘预读特性:为了减少磁盘 I/O 操作,磁盘往往不是严格按需读取,而是每次都会预读。预读过程中,磁盘进行顺序读取,顺序读取不需要进行磁盘寻道。每次会读取页的整数倍,而B+树的结点大小是一整页的数据。
    B + 树与 B 树的比较
  • B+ 树的磁盘 IO 更低:因为B+ 树的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对 B 树更小。
  • B+ 树的查询效率更加稳定:由于非叶子结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。
  • B+ 树元素遍历效率高:B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而 B 树不支持这样的操作。

6. 数据库隔离级别,可重复读级别下如何解决幻读,间隙锁能够解决所有的幻读问题吗?间隙锁具体是如何实现的?

可重复读级别下通过使用MVCC来解决幻读的问题,但是只能解决读数据情况下的幻读问题,对于修改的操作依旧存在幻读问题,需要通过加锁来解决
因为MVCC读数据的使用使用的是快照读和当前读,快照读就是会记录当前这次select后的结果,之后select 的时候就会返回这次快照的数据,即使其他事务提交了不会影响当前select的数据,这就实现了可重复读了。对于会修改数据的操作update、insert、delete、select ... lock in share mode、select ... for update,都采用的是当前读的模式,在执行这几个操作时会读取最新的记录,即使是别的事务提交的数据也可以查询到,这时需要加锁。

间隙锁(Gap Lock)是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制,在可重复读隔离级别下,数据库是通过行锁和间隙锁共同组成的(next-key lock),来实现的

  • Record Lock(行锁):锁定一个记录上的索引,而不是记录本身。
  • Gap Lock(间隙锁):锁定索引之间的间隙,但是不包含索引本身。
  • Next-Key Lock:是 Record Locks 和 Gap Locks 的结合,不仅锁定一个记录上的索引,也锁定索引之间的间隙

间隙锁不是互斥的,一个事务A获取到了(5,10]的间隙锁,另一个事务也可以获取到(5,10]的间隙锁。间隙锁存在死锁的问题。

间隙锁的具体实现

// 存在user_id 1,2,3...100,101
select * from  user where user_id > 100 for update;

查询user_id>100的记录,InnoDB不仅会对符合条件的user_id值为101的记录加锁,也会对user_id大于101(这些记录并不存在)的“间隙”加锁。这个时候插入user_id>100的记录就会被阻塞,从而避免了幻读,如果不使用间隙锁,如果本事务如果再次执行上述语句,就会发生幻读。

7. mysql 模糊匹配优化 like%%?

完全模糊匹配的查询(%%),即使列上有选择率很高的索引,也不会被使用。优化方案有:ICP特性,全文索引,基于生成列索引。
ICP特性
Mysql5.7之后开始支持索引条件下推(ICP),不支持ICP之前,当进行索引查询时,首先根据索引来查找数据(会先查询大量的数据),然后再根据where条件来过滤,扫描了大量不必要的数据,增加了数据库IO操作。
在支持ICP后,MySQL在取出索引数据的同时,判断是否可以进行where条件过滤,将where的部分过滤操作放在存储引擎层提前过滤掉不必要的数据,减少了不必要数据被扫描带来的IO开销。
注意ICP只能用于二级索引

ICP开启后,explain计划中的Extra="Using index condition"

Extra显示的索引扫描方式

  • using where:表示存储引擎返回的记录并不是所有的都满足查询条件,需要在服务器层进行where过滤。
  • using index condition:查询使用了索引,而且在存储引擎层进行来where条件过滤,需要回表查询数据。
  • using index:查询使用覆盖索引的时候会出现,而且where条件的列是索引的最左前缀列。
  • using index & using where:查询使用了索引,但是需要的数据都在索引列中能找到,不需要回表查询数据,但是where条件的列是索引列之一但是不是索引的最左前缀列。
// 对name和nickname创建复合索引,可以使用ICP来优化
select * from users01 where name = 'Lyn' and nickname like '%SK%';

// 对nickname创建索引,实际上等价于复合索引(id,nickname),但是不能用ICP来优化,可以使用延迟关联来利用覆盖索引解决
select * from users01 where nickname like '%SK%';

select  * from users01 a , (select id from users01 where nickname like '%SK%') b where a.id = b.id;//延迟关联的解决方案

全文索引

生成列索引
MySQL 5.7开始支持生成列,生成列是由表达式的值计算而来,有两种模式:VIRTUAL和STORED,如果不指定默认是VIRTUAL;VIRTUAL模式不占用存储空间。

  • VIRTUAL生成列用于复杂的条件定义,能够简化和统一查询,不占用空间,访问列是会做计算。
  • STORED生成列用作物化缓存,对于复杂的条件,可以降低计算成本,占用磁盘空间。

对于where条件后的 like '%xxx' 是无法利用索引扫描,可以利用MySQL 5.7的生成列模拟函数索引的方式解决,具体步骤如下:

  • 利用内置reverse函数将like '%风云'反转为like '云风%',基于此函数添加虚拟生成列。
    在虚拟生成列上创建索引。
  • 将SQL改写成通过生成列like reverse('%风云')去过滤,走生成列上的索引。

模糊匹配like %%优化

8. Innodb,B+树,聚簇索引和非聚簇索引区别?

B+树是索引结构
InnoDB采用 B+树作为索引结构,B+树是B树的一个变体,相比较于B树 非叶结点只起索引的作用,叶子节点通过顺序访问指针进行实现,B+树是多路查找树,磁盘I/O低,通常用于数据库和操作系统的文件系统中。使用B+树作为索引的优点有:

不再需要进行全表扫描,只需要对树进行搜索即可,所以查找速度快很多
因为 B+ Tree 的有序性,所以除了用于查找,还可以用于排序和分组
可以指定多个列作为索引列,多个索引列共同组成键
适用于全键值、键值范围和键前缀查找
还有聚簇索引、二级索引、哈希索引、全文索引、空间数据索引。

聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDb的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
当表中有聚簇索引时,它的数据行实际上存在索引的叶子页中,因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。
如果没有定义主键,InnoDB会选择一个唯一的非空索引代替,如果没有这样的索引,InnoDb会隐式定义一个主键作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。
优点:

  • 可以把相关数据保存在一起
  • 数据访问更快
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
    缺点:
  • 聚簇数据最大限度的提高了I/O密集型应用的性能,但如果数据全部都放在内存中,那么访问顺序就不重要了,聚簇索引就没有什么优势了。
  • 插入速度严重依赖于插入顺序。
  • 更新聚簇索引列的代码很高,因为会强制InnoDB将每个被更新的行移动到新的位置。
  • 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能会面临页分裂的问题。当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行。
    聚簇索引可能导致全表扫描变慢,尤其是行稀疏,或者由于页分裂导致数据存储不连续的时候。
    二级索引可能比想象大,因为二级索引的叶子节点包含了引用行的主键列。
    二级索引访问需要两次索引查找,而不是一次。(因为二级索引叶子节点保存的不是指向行的物理地址,而是行的主键值。先根据二级索引查找行对应的主键值,然后去聚簇索引中根据这个值查找查找对应的行。)
    二级索引的叶子节点存储的是主键值,可以减少当出现行移动或者数据页分裂时二级索引的维护工作。

主键避免使用UUID
避免使用随机的(不连续且分布范围非常大)聚簇索引,特别是对于I/O密集型的应用。使用UUID来作为聚簇索引会很糟糕,因为它使得聚簇索引的插入变得完全随机。插入新的一行的主键值不一定比之前插入的值大,所以InnoDB无法简单的总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置。通常是已有数据的中间位置并分配空间。但是这会导致数据分布不够优化,而且会有以下缺点:

由于频繁的页分裂,页会变得稀疏并被不规则填充,所以最终数据会有碎片
因为写入是乱序的,InnoDB不得不频繁地做页分裂操作,页分裂会导致移动大量数据
写入的目标页可能已经刷到磁盘上并从缓存中移除,InnoDb在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O
当达到页的最大填充因子时(默认是页大小的15/16,留出部分用于以后修改),下一条记录就会写入新的页中。
使用InnoDB时应该尽可能地按主键顺序插入数据,并且尽可能使用单调增加的聚簇键的值来插入新行。

避免主键太大
innodb 存储引擎的主键不能太大,因为innodb默认会对主键建立聚簇索引,每个索引都存在整行的数据,而二级索引都存储了主键值,用于查询聚簇索引,如果主键太大,占的内存空间就会变大,导致缓存里面存储的索引量就会减少,就会增加磁盘IO次数
MylSAM 存储引擎则没有这个问题,因为MylSAM的索引和数据分开存储,索引叶子用于存储指向数据的指针,跟数据本身无关。

非聚簇索引
也称为二级索引,叶子结点包含了引用行的主键列。需要进行两次索引查找,第一次查找对应的主键值,第二次进行聚簇索引查找对应的行。

9. 联合索引最左原则的底层原理?

最左原则:以最左边的为起点任何连续的索引都能匹配上。联合索引的底层是一颗B+树,只不过联合索引的健值数量不是一个,而是多个。可以知道对于联合索引(a,b,c),从最左边的列a开始都是有序的,如果从b开始的值是无序的,所以可以使用最左原则。

10. mysql 的 MVCC 以及是否解决幻读?

MVV解决幻读

11. redo log,bin log,undo log?

mysql里面的三种日志文件redo log,bin log,undo log,redo log用来持久化,保证事务的持久性,进行数据恢复;bin log用来做记录主服务做的DML,DDL修改,保证主从一致性;undo log用来保证事务的原子性

redo log

redo log的作用是为持久化而生的,记录事务对数据页做了哪些修改。redo log包括两部分:一个是内存中的日志缓冲(redo log buffer)另一个是磁盘上的日志文件。mysql每执行一条DML语句,先将记录写入redo log buffer,后续某个时间点再一次性将多个操作记录写到redo log file。写入到磁盘有三种方式

  • 0: 延迟写,事务提交后不立马进行写入磁盘,而是每秒都会把日志缓冲区的日志写入OS缓存,并刷新到磁盘里面。最多只会丢失一秒的数据
  • 1: 实时写、实时刷,事务提交后立马写入把日志写入到OS缓冲区里面,并刷新到磁盘文件里面
  • 2: 实时写、延迟刷,事务提交后立马写入把日志写入到OS缓冲区里面,但是每隔一秒从OS缓冲区里面刷新到磁盘文件。

redo log存在的意义主要就是降低对数据页刷盘的要求。启动innodb的时候,不管上次是正常关闭还是异常关闭,总是会进行恢复操作。因为redo log记录的是数据页的物理变化,因此恢复的时候速度比逻辑日志(如binlog)要快很多。
事务开始之后就产生redo log。当对应事务的脏页写入到磁盘之后,redo log的使命也就完成了,重做日志占用的空间就可以重用(被覆盖)

bin log

binlog记录了数据库表结构和表数据变更,比如DML和DDL语句,以二进制的形式保存在磁盘里面,主要用于主从复制和恢复数据

  • 主从服务器需要保持数据的一致性,通过binlog来同步数据
  • 如果整个数据库的数据都被删除了,binlog存储着所有的数据变更情况,那么可以通过binlog来对数据进行恢复。

对于InnoDB存储引擎而言,只有在事务提交时才会记录biglog,此时记录还在内存中,那么biglog是什么时候刷到磁盘中的呢?mysql通过sync_binlog参数控制biglog的刷盘时机,取值范围是0-N:

  • 0:不去强制要求,由系统自行判断何时写入磁盘;
  • 1:每次commit的时候都要将binlog写入磁盘;
  • N:每N个事务,才会将binlog写入磁盘。

bin log的日志形式

  • STATMENT:基于SQL语句的复制,只记录sql语句,优点是不需要记录行的变化,缺点是在某些情况下会导致主从数据不一致,比如执行date()、slepp()等跟当前系统有关的函数。
  • ROW:基于行的复制,不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了。缺点是会造成大量的日志。
  • MIXED:基于STATMENT和ROW两种模式的混合复制,一般的复制使用STATEMENT模式保binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

redo log与bin log 恢复数据的区别
redo log存储的是物理数据的变更,如果我们内存的数据已经刷到了磁盘了,那redo log的数据就无效了。
bin log用来存储数据的逻辑变化
redo log事务开始的时候,就开始记录每次的变更信息,而binlog是在事务提交的时候才记录。只有两个日志都写入成功了,事务才会真正成功。
redo日志是基于存储引擎层的,bin log是基于服务器层实现的。
redo日志是以循环写的方式记录,当写到结尾时,会从开始重新写;bin log是以追加的方式写入的。
redo 日志用于崩溃恢复,bin log 用于主从复制和数据恢复。

undo log

undo log用来保证事务的原子性,undo log主要有两个作用:回滚和多版本控制(MVCC)生成快照读的时候;
如果事务执行失败了,通过undo log进行回滚。undo log主要存储的也是逻辑日志,比如我们要insert一条数据了,那undo log会记录的一条对应的delete日志。我们要update一条记录时,它会记录一条对应相反的update记录。
因为undo log存储着修改之前的数据,相当于一个前版本,MVCC实现的是读写不阻塞,读的时候只要返回前一个版本的数据就行了。
undo是在事务开始之前保存的被修改数据的一个版本,产生undo日志的时候,同样会伴随类似于保护事务持久化机制的redolog的产生。当事务提交之后,undo log并不能立马被删除,而是放入待清理的链表,由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息,决定是否可以清理undo log的日志空间。

12. mysql调优?

在开发中可能遇见三种情况的需要mysql调优的问题:1. 单条sql语句执行慢 2. 部分sql语句执行慢、所有的sql语句执行慢

单条sql语句

造成单条sql语句执行慢的情况有可能是:没有使用索引或者索引没有命中、表中数据量比较大
解决方案1:索引
首先使用 explain语句查看当前sql语句索引的使用情况,如果没有索引,则使用create创建索引;如果存在索引,则检查sql语句是否正确的触发了索引。
使用索引时要避免在where子句中索引列参与运算、隐式转换、模糊匹配等,这些操作都会导致索引失效。
除了正常使用索引的话,还可以使用一些技巧来优化索引:

  • 尽量使用主键索引,可以避免回表
  • 使用延迟关联
  • 注意字段类型,int类型不要使用char
  • 查询语句尽可能的小,大语句拆分为小语句
    解决方案2:数据拆分
    当表中数据量太大时 SQL 的查询会比较慢,可以考虑拆分表,让每张表的数据量变小,从而提高查询效率。通常的策略有水平拆分和垂直拆分。
    水平拆分:对行数据进行拆分,可以根据主键ID进行取模运行或者hash运算进行拆分、将一个表的数据拆分到几个表中。
    垂直拆分:对列进行拆分,把常用的列和不常用的列分开、text列拆分开、查询要用到的列放到一起
    其他优化方案
  1. 选择的表结构数据类型应该尽可能的小
  2. 使用简单的数据类型
  3. 尽可能使用 not null 定义字段,因为 null 占用 4 字节空间,而且索引还要额外处理
  4. 尽量少用 text 类型
  5. 尽量使用 timestamp(可以跨时区,而且字段更小,只有4 字节)

部分sql语句优化

部分sql语句执行比较慢,需要开启慢查询,找到sql执行时间比较慢的语句,然后定位出具体的sql,再使用单条sql语句优化方案进行优化

show variables like '%slow_query_log%'; # 查看慢查询是否开启
set global slow_query_log=1; # 开启慢查询

全部sql语句优化

所有的sql语句执行都比较慢, 则是数据库达到了瓶颈,则需要考虑进行分库,使用mysql主从架构的解决方案,主库用来写,从库用来读。当主库进行了DML语句时,记录binlog日志,然后把日志同步到从库里面,从库再执行binlog中的记录。

explain

  1. 预先跑explain sql
  2. 排除缓存 sql no cache
  3. 看explain的结果行数是否正确,可以用analyze table t 矫正
  4. 添加索引,索引不一定是最优的,可以使用force index强制使用索引
  5. 是否存在索引回表的情况,是否使用覆盖索引避免回表。
  6. 查看索引是否满足最左前缀原则,合理安排联合索引、索引字段是否做了函数请求,注意字符串存在隐式转换(索引列id是字符串类型,where id=1 会把 1隐式转换成"1")。
  7. 可以使用索引下推进行优化,减少回表次数

Explain的字段含义:

  • id:序号
  • select_type:查询类型
  • table:表名
  • partitions:匹配的分区
  • type:表的连接的类型
    • all — 扫描全表数据
    • index — 遍历索引
    • range — 索引范围查找
    • fulltext — 使用全文索引
    • ref — 使用非唯一索引查找数据
    • eq_ref — 在 join 查询中使用主键或唯一索引关联
    • ....
  • prossible_keys:可能会选择的索引
  • key:实际选择的索引
  • key_len:索引的长度
  • ref:与索引做比较的列
  • rows:检索的行数(可能值)
  • filtered:查询条件过滤的行数的百分比
  • Extra:额外的信息
    • distinct:在select中使用了distinct
    • Using filesort:进行了额外的排序,需要优化
    • Using index:使用覆盖索引
    • Using temporary:使用了临时表,一般出现于排序, 分组和多表 join 的情况,需要优化
    • using where:表示存储引擎返回的记录并不是所有的都满足查询条件,需要在服务器层进行where过滤
    • using index condition:在存储引擎层就进行了过滤,需要回表查询数据。
    • using index & using where:使用了索引,但是数据都在索引列中能找到,不需要回表。

在建表的时候如何设计索引的?有没有做过索引优化?

Mysql的InnoDB引擎使用B+Tree结构来存储索引,可以尽量减少数据查询时磁盘IO次数,同时树的高度直接影响了查询的性能,一般为3/4层,三层的B+树索引就可以存储两千多万行数据。索引策略有聚簇索引、联合索引(需要回表查询数据)。
设计高效的索引?

  1. 对单独的列建立索引,即这些列不能参与计算或者做函数的参数。
  2. 选择合适的前缀索引,对于长度很大的字符列建立索引(text、varchar很长的列),可以索引开始的部分字符,从而提高索引的效率,但是会降低索引的选择性(即不重复的索引值 与 总的记录数的比值),通过不断计算完整列的选择性来选择合适的前缀长度。
  3. 多列联合索引,满足最左匹配原则
  4. 选择合适的索引顺序,将选择性最高的列放在最前面。
    索引优化
  5. 使用覆盖索引避免回表的情况。
  6. 查看索引列是否因为参数计算、作为函数参数、进行隐式转换导致索引失效
  7. 使用延迟关联优化like模糊查询和limit查询。

分库分表做了吗? 怎么实现的?

分表方案

水平切分
水平切分又称为 Sharding,它是将同一个表中的记录拆分到多个结构相同的表中

当一个表的数据不断增多时,Sharding 是必然的选择,它可以将数据分布到集群的不同节点上,从而缓存单个数据库的压力。

垂直切分
垂直切分是将一张表按列分成多个表,通常是按照列的关系密集程度进行切分,也可以利用垂直气氛将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不通的库中,例如将原来电商数据部署库垂直切分称商品数据库、用户数据库等。
Sharding切分的策略

  • 哈希取模:hash(key)%N
  • 范围:可以是 ID 范围也可以是时间范围
  • 映射表:使用单独的一个数据库来存储映射关系
    Sharding的问题
  • 事务的问题:使用分布式事务来解决
  • 连接的问题:分解成多个单表查询,然后在用户程序中进行连接
  • 唯一性的问题:使用全局唯一ID、为每个分片指定一个 ID 范围、分布式 ID 生成器。

分区表?

分区表是一个独立的逻辑表,但是底层是由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求,都会通过句柄对象转化成对存储引擎的接口调用。

Mysql实现分区表的方式--对底层表的封装--意味着索引也是按照分区的子表定义的,而没有全局索引。Mysql在创建表时使用PARTITION BY 子句定义每个分区存放的数据,在执行查询的时候,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询就无须扫描所有的分区----只需要查找包含需要数据的分区就可以了。

分区的一个主要目的就是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起。

分区表本身也有一些限制:

  • 一个表最多只能有1024个分区。
  • 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来。
  • 分区表无法使用外键约束

分区表对用户来说是透明的,用户无法访问底层的各个分区。

分区表的原理

分区表由多个底层表实现,这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引只是在各个底层表上各自加上一个完全相同的索引。

SELECT查询

当查询一个分区表的时候,分区表先打开并锁住所有的底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据。

INSERT操作

当写入一条记录时,分区表先打开并锁住所有的底层表,然后确定那个分区接收这条记录,再将记录写入对应底层表。

DELETE操作

当删除一条记录时,分区表先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作。

UPDATE操作

当更新一条记录时,分区表先打开并锁住所有的底层表,Mysql先确定需要更新的记录在那个分区,然后取出数据并更新,再判断更新后的数据应该放在那个分区,最后对底层表进行写入操作,并对原数据所在的底层表进行删除操作。

虽然每个操作都会先打开并锁住所有的底层表,但这并不是说分区表在处理过程中是锁住全表的,如果存储引擎能够自己实现行级锁,例如InnoDB,则会在分区层释放对应表锁。

什么情况下会出问题?

为了保证大数据量的可扩展性,一般有下面两个策略:

  • 全量扫描数据,不要任何索引
  • 索引数据,并分离热点

这两个分区策略都基于两个非常重要的假设:查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代码。实际上这两个假设在很多情况下都会有问题:

  • NULL值会使分区过来无效:分区表达式的值可能是NULL:第一个分区是一个特殊分区,用来记录分表表达式结果为NULL的值或者一个非法值。 可以直接使用列来进行分区来避免这个情况。
  • 分区列和索引列不匹配:如果定义的索引列和分区列不匹配,会导致查询无法进行分区过滤。
  • 选择分区的成本可能很高:对于范围分区,符合查询条件的行在那个分区 的这样的问题的成本可能会非常高。
  • 打开并锁住所有底层表的成本可能很高:当查询访问分区表的时候,Mysql需要打开并锁住所有的底层表,这是分区表的另一个开销,这个操作在分区过滤之前发生,所以无法通过分区过滤降低此开销。
  • 维护分区的成本可能很高

所有的分区必须使用相同的存储引擎。MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区,而不能根据表达式的值去过滤分区,即使这个表达式就是分区函数也不行。

通过explain可以定位到数据所在的分区

MySQL 主从同步怎么做的?binlog清楚吗?

主从复制
主要涉及三个线程:binlog 线程、I/O 线程和 SQL 线程:

  • binlog 线程:负责将主服务器上的数据更改写入二进制日志(Binary log)中。
  • I/O线程:负责从主服务器上读取二进制日志,并写入从服务器的中继日志(Relay log)。
  • SQL 线程 :负责读取中继日志,解析出主服务器已经执行的数据更改并在从服务器中重放(Replay)

读写分离
主服务器处理写操作以及实时性要求比较高的读操作,而从服务器处理读操作。写分离能提高性能的原因在于:

  • 主从服务器负责各自的读和写,极大程度缓解了锁的争用;
  • 从服务器可以使用 MyISAM,提升查询性能以及节约系统开销
  • 增加冗余,提高可用性。
    读写分离常用代理方式来实现,代理服务器接收应用层传来的读写请求,然后决定转发到哪个服务器

Mysql的缓冲区?

Mysql的索引分裂?

在MySQL中,同一个表空间内的一组连续的数据页为一个区,默认区的大小为1MB,页的大小为16KB。16*64 = 1024KB ,也就是说一个区里面会有64个连续的数据页。连续的256个数据区为一组数据区。

聚簇索引采用的是B+树算法,而且每个叶节点都保存了该主键所对应行的数据,如果插入数据的主键是自增长的,那么根据二叉树算法会很快的把该数据添加到某个节点下,而其他的节点不用改变;但是如果插入的是不规则的数据(例如UUID),那么每次插入都会改变二叉树之前的数据状态,从而导致了页分裂
页分裂
如果相邻两个页已经被占满了,但是又来了一个主键索引,应该插入到两个页之间(保证主键是递增的),但是两个都满了,容纳不下,这时就会触发页分裂:

  • 创建新页
  • 判断当前页可以从哪里进行分裂
  • 移动记录行,并重新定义页之间的关系

页分裂的目的就是保证:后一个数据页中的所有行主键值比前一个数据页中主键值大
页可以空或者填充满(100%),行记录会按照主键顺序来排列。当插入数据时,如果数据(大小)能够放的进页中的话,那他们是按顺序将页填满的;若当前页满,则下一行记录会被插入下一页(NEXT)中。
页合并
当页中删除的记录达到MERGE_THRESHOLD(默认页体积的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。页合并发生在删除或更新操作中,关联到当前页的相邻页。

页分裂可能导致碎片化和页面的空间利用率极低。
页分裂会发生在插入或更新,并且造成页的错位

合并和分裂的过程,InnoDB会在索引树上加写锁(x-latch)。在操作频繁的系统中这可能会是个隐患,它可能会导致索引的锁争用。

结点页分裂

Mysql 分库分表保证唯一ID?

  1. 使用额外的表来保存自增ID,当向分库分表插入数据的时候,先向额外的这个表插入数据,获取自增的ID,拿到这个 id 之后再往对应的分库分表里去写入。
  2. UUID、系统时间:不推荐这种方式,因为如果有B+树索引的话,会导致页分裂,导致性能太差。
  3. 雪花算法: 生成的ID是递增的,推荐使用,缺点是跟系统时间有关,如果系统时间出现问题,可能会出现重复的ID。

Mysql 锁?

MySQL常用引擎有MyISAM和InnoDB,而InnoDB是mysql默认的引擎。MyISAM不支持行锁,而InnoDB支持行锁和表锁。
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁。

select * from table where id>60 lock in share mode;# 加共享锁(读锁)
select * from table where id >60 for update; # 加互斥锁(写锁)

读锁会阻塞写,写锁会阻塞读和写

行锁
在MySQL的InnoDB引擎支持行锁,MySQL的行锁是通过索引加载的,也就是说,行锁是加在索引上的,要是对应的SQL语句没有走索引,则会全表扫描,行锁则无法实现,取而代之的是表锁,此时其它事务无法对当前表进行更新或插入操作。
注意:

  • 行锁必须有索引才能实现,否则会自动锁全表,那么就不是行锁了。
  • 两个事务不能锁同一个索引。
  • insert,delete,update在事务中都会自动默认加上排它锁

间隙锁
间隙锁就是对索引之间的间隙加锁,而不是索引本身。

next-key锁
行锁+间隙锁组成 next-key锁 用来解决MVCC的幻读问题。

一条sql语句在Mysql如何执行的

Mysql服务器层的组件:

  • 连接器: 身份认证和权限相关(登录 MySQL 的时候)
  • 查询缓存: 执行查询语句的时候,会先查询缓存(MySQL 8.0 版本后移除,因为这个功能不太实用)。
  • 分析器: 没有命中缓存的话,SQL 语句就会经过分析器,分析器包括词法分析、语法分析,说白了就是要先看你的 SQL 语句要干嘛,再检查你的 SQL 语句语法是否正确。
  • 优化器: 按照 MySQL 认为最优的方案去执行。
  • 执行器: 执行语句,然后从存储引擎返回数据。会进行权限校验,如果没有权限就会返回错误信息,如果有权限就会调用数据库引擎接口

mysql的整体架构

查询语句的执行过程

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

更新语句的执行过程

  • 先查询更新语句这一条数据,如果有缓存,也是会用到缓存。
  • 然后拿到查询的语句,进行更新,然后调用引擎 API 接口,写入这一行数据,InnoDB 引擎把数据保存在内存中,同时记录 redo log,此时 redo log 进入 prepare 状态,然后告诉执行器,执行完成了,随时可以提交。
  • 执行器收到通知后记录 binlog,然后调用引擎接口,提交 redo log 为提交状态
  • 更新完成

为什么redo log要分为两个阶段,一个是准备状态,一个是提交状态?

目的是为了保证数据一致性

  • 先写 redo log 直接提交,然后写 binlog假设写完 redo log 后,机器挂了,binlog 日志没有被写入,那么机器重启后,这台机器会通过 redo log 恢复数据,但是这个时候 bingog 并没有记录该数据,后续进行机器备份的时候,就会丢失这一条数据,同时主从同步也会丢失这一条数据
  • 先写 binlog,然后写 redo log,假设写完了 binlog,机器异常重启了,由于没有 redo log,本机是无法恢复这一条记录的,但是 binlog 又有记录,那么和上面同样的道理,就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了,写完 binglog 后,然后再提交 redo log就会防止出现上述的问题,从而保证了数据的一致性。那么问题来了,有没有一个极端的情况呢?假设 redo log 处于预提交状态,binglog 也已经写完了,这个时候发生了异常重启会怎么样呢? 这个就要依赖于 MySQL 的处理机制了,MySQL 的处理过程如下:

  • 判断 redo log 是否完整,如果判断是完整的,就立即提交。
  • 如果 redo log 只是预提交但不是 commit 状态,这个时候就会去判断 binlog 是否完整,如果完整就提交 redo log, 不完整就回滚事务。

binlog和redolog的两阶段提交

Mysql join的底层原理

mysql只支持一种join算法:Nested-Loop Join(嵌套循环连接),但Nested-Loop Join有三种变种:Simple Nested-Loop Join,Index Nested-Loop Join,Block Nested-Loop Join
有两个表一个是驱动表,一个是匹配表,n m 分别是两个表的行数。

Simple Nested-Loop Join

从驱动表里面分别取出一行,去匹配 匹配表里面的每一行数据,最后再合并数据,一共访问的次数是n * m 。 这个方法对数据库开销比较大。

Index Nested-Loop Join

索引嵌套,前提条件是匹配表上面有索引,可以通过索引来减少比较,加速查询。在查询时,驱动表会根据关联字段的索引进行查找,当在匹配表索引上找到符合的值,再回表进行查询,也就是只有当匹配到索引以后才会进行回表查询
如果匹配的关联健是主键的话,性能会非常高
如果不是主键,要进行多次回表查询,先关联二级索引,然后根据二级索引的主键ID进行回表操作,性能上比索引是主键要慢。

Block Nested-Loop Join

如果有索引,会选取第二种方式进行join,但如果join列没有索引,就会采用Block Nested-Loop Join。
中间会有个join buffer缓冲区,是将驱动表的所有join相关的列都先缓存到join buffer中,然后批量与匹配表进行匹配,将第一种多次比较合并为一次,降低了非驱动表的访问频率。
默认情况下join_buffer_size=256K,在查找的时候MySQL会将所有的需要的列缓存到join buffer当中,包括select的列,而不是仅仅只缓存关联列。

在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

Mysql 视图?

视图是一个虚拟表,是sql的查询结果,其本身并不存储任何数据。同真实的表一样,视图包含一系列带有名称的列和行数据,在使用视图时动态生成
视图的数据变化会影响到基表,基表的数据变化也会影响到视图 ; 创建视图需要使用create view。

** 视图可以持久化吗?**
这个记不清楚了,但是我认为的话,视图不需要持久化,因为试图是通过sql语句生成的,基表是肯定会进行持久化的,只需要在重新执行sql语句就可以生成试图,所以不需要持久化。

如何优化批量插入数据?

  1. 先删除innodb的主键和索引,设置存储引擎为myalsam,然后再批量插入数据,最后再重新设置成innodb,再设置主键和索引即可。这是因为使用innodb的时候,每次插入数据都要维护索引,从而导致变慢。
  2. 合并insert语句,一个insert插入多条数据。
  3. 关闭autocommit,默认事务是开启的,也就是一个插入语句一个事务,所以存在重复的开启事务、删除事务。
  4. 插入数据有序,如果插入的数据是无序的话,会导致索引的分裂和合并,从而导致性能下降。

Mysql OrderBy排序的过程

Mysql中Order By语句的实现原理、及优化手段
比如语句是select id,name,age from user where name='张三' order by age;

假设name有索引,age无索引

  1. 首先根据name 二级索引,找到对应的主键id,
  2. 然后回表找到聚簇索引对应的select 查询的字段(id, name, gae),并放入sort_buffer_size
  3. 继续在 name 索引树中查找下一条 name="张三"的结点,重复步骤 1、2步骤,直到在 name 索引树上找到第一条 name 不等于张三时,停止查找。
  4. 在sort_buffer_size中根据age 进行排序,并返回结果。
  5. 如果sort_buffer_size存放不下的话,先把当前缓冲区里面的数据进行排序,并保存到文件里面,最后使用归并排序,对小文件进行合并。

sort_buffer_size每次放入的字段太多了,浪费sort_buffer_size的空间大小:优化手段,只存放主键 和 排序的那个字段,这里也就是id 和 age:

  1. 首先根据name 二级索引,找到对应的主键id,
  2. 然后回表找到聚簇索引对应的主键和排序的字段,并放入sort_buffer_size
  3. 继续在 name 索引树中查找下一条 name="张三"的结点,重复步骤 1、2步骤,直到在 name 索引树上找到第一条 name 不等于张三时,停止查找。
  4. sort_buffer 中,将所有数据根据 age 进行排序
  5. 根据排好序的字段,找到主键,然后回表找到具体select需要的字段。

PS: 这种方式虽然sort_buffer_size可以存放的数据变多了,但是增加了回表的次数。

如果name,age有联合索引
则查询出来的数据就是根据name、age有序的,就不需要额外的处理。

PS: 就算age有索引,也不一定要走索引,mysql会选择cost花费最低的一种方式,当数据量小时,走索引可能花销还比较大。

Mysql 缓存池的LRU设计?

对于Innodb引擎,数据在磁盘里面是按照页进行存储的,mysql 在使用的时候,会把磁盘上的数据页加载到内存的缓存池(Buffer Pool) 中,Buffer Pool 里面可以存放多个缓存页。最开始的时候Buffer Pool是空的,随着mysql的使用,缓存页就会慢慢被占满,当所有的缓存页都被使用之后,从磁盘读取新的数据页的时候,就要进行缓存页淘汰,一般使用LRU 淘汰掉最不常使用的那个缓存页。

一般的LRU实现,是在内存里面维护一个双向链表,链表头部是经常使用的缓存页,尾部是最久未被使用的数据,但是这样的LRU设计有问题

  • 全表扫描:Mysql 有时候会进行全表扫描,也就会把所有的数据页加载到缓存池里面,这样的话就有可能加载的是不常使用的缓存页,而把经常使用的缓存页淘汰掉。所以后面的操作就会导致缓存的命中率降低,导致性能降低。
  • 预读:预读是 InnoDB 引擎的一个优化机制,当你从磁盘上读取某个数据页,InnoDB 可能会将与这个数据页相邻的其他数据页也读取到 Buffer Pool 中。 所以预读也可能读取到的是不常使用的缓存页,而把经常使用的缓存页淘汰掉。

优化方案

使用冷热数据分离,把这个LRU双向链表分成两个部分,对数据进行冷热分离,一部分用来存放冷数据,也就是刚从磁盘读进来的数据,另一部分用来存放热点数据,也就是经常被访问到数据,冷数据默认占链表的37%,八分之三左右
当从磁盘读取数据页后,会先将数据页存放到 LRU 链表冷数据区的头部,如果这些缓存页在 1 秒之后被访问,那么就将缓存页移动到热数据区的头部;如果是 1 秒之内被访问,则不会移动,缓存页仍然处于冷数据区中。1 秒这个数值,是由参数 innodb_old_blocks_time 控制的。

当遇到全表扫描或者预读时,如果没有空闲缓存页来存放它们,那么将会淘汰一个数据页,而此时淘汰地是冷数据区尾部的数据页。冷数据区的数据就是不经常访问的,因此这解决了误将热点数据淘汰的问题。如果在 1 秒后,因全表扫描和预读机制额外加载进来的缓存页,仍然没有人访问,那么它们会一直待在冷数据区,当再需要淘汰数据时,首先淘汰地就是这一部分数据。

更进一步优化
一般访问某个缓存页的时候,会把这个缓存页移动到LRU双向链表的头部,表示当前是最常使用的页面。
但是对链表进行移动的时候,会对整个链表加锁,避免高并发的时候出现问题,这个时候会都在性能出现降低。所以当一个缓存页处于热点区域数据的时候,而且在链表的前1/4,这个时候访问这个缓存页的时候,就不用把它移动到热数据区域的头部,在后3/4的时候就需要移动到头部

再进一步优化
Mysql对缓冲池进行修改的时候,会进行加锁,避免高并发的时候出现问题。使用一个缓冲池的话并发锁的竞争比较大,所以使用多个 Buffer Pool 实例,降低锁的竞争。对于缓存页进行哈希运算,判断出属于那个缓冲池。

B+树节点合并和分裂的并发安全是怎么保障的?

B+树并发控制机制淘宝数据库内核月报,推荐看

Mysql 5.7的B+树并发控制机制,引入了SX锁(读写锁)、写操作尽可能的锁住修改的分支、减少加锁的范围:

  • S:读锁,可以加多个S锁
  • X:写锁
  • SX:读写锁,加了SX锁,只能加S锁、不能加SX/X锁

读操作

  1. 每个读操作首先对树结构加S锁
  2. 其次一直访问树结构直到对应的叶节点,读操作对经过的所有非叶结点加S锁。
  3. 然后对叶节点的page记录加S锁,然后释放索引结构的锁和非叶结点的S锁。
  4. 访问完毕后,释放叶结点的锁。

乐观写操作

  1. 每个写操作首先对树结构加S锁
  2. 其次一直访问树结构直到对应的叶节点,读操作对经过的所有非叶结点加S锁。
  3. 然后对叶节点的page记录加 X 锁,然后释放索引结构的锁和非叶结点的S锁。
  4. 修改叶子节点后,释放叶结点的锁。

悲观写操作

  1. 写操作首先对树结构加SX锁,在遍历树结构的过程中对被影响的分支加X锁
  2. 对叶节点加X锁,然后修改树结构(可能发生叶的合并和分裂)后,释放非叶节点索引的锁
  3. 最后修改叶节点后,并释放锁

优化:只是锁住被修改的分支,从而没有冲突的读操作可以并发执行,减少了线程之间的冲突
存在两种锁:页锁、索引锁
SX锁:读写锁,让写操作在到达被修改的分支之前,可以使用S锁,也就是读锁,增加了读写的并发量。

Mysql 主从复制的延迟

mysql主从复制的延迟?主从延迟就是同一个事务在主库执行完毕之后,又在从库执行之后的延迟。

PS:可能产生原因:

  • 从库的性能比主库差、主库大事务的执行,比如执行10分钟,而binlog只能等主库执行完毕后传入从库;
  • 主库产生大量的DDL语句,而从库是单线程重新执行binlog日志;
  • 从库在同步数据的时候会跟其他的读操作进行锁竞争;
  • binlog日志传输的时候有网络延时。

PS:简单的解决方案:采用mysql集群+分库分表 降低压力,加入redis缓存,降低mysql读写压力。

mysql5.7 之后使并发复制技术, 永久解决复制延迟问题(同一个事务发给同一个worker结点来执行、更新同一行的事务必须分发到同一个worker中),最好的策略是主库怎么进行并行执行的,从库就怎么并行恢复。

Select、where、group by、having、oreder by、limit 执行顺序

当一个查询语句同时出现了select、where,group by,having,order by、limit的时候,执行顺序和编写顺序是:

  1. 执行where xx对全表数据做筛选。
  2. 执行 group by 对数据进行分组。
  3. 对每组数据进行having xx 过滤、筛选
  4. 执行select 筛选出需要返回的列
  5. 执行order by 排序
  6. 根据limit 返回具体的行

对于select操作,有两种解决方案:

  • 加载所有select 需要的列数据和排序需要的列到内存里面
    • 缺点:所有的列都加载到缓存里面,缓存存放的数据行就会变少,如果缓存不够的话,就会保存一部分到文件里面,通过使用文件来做归并排序来完成任务
  • 只加载order by需要的列 和主键索引
    • 优点:缓存可以存放更多的数据行
    • 缺点:多了回表操作,因为最后只包含了主键数据,所以每一个数据行都会执行一次回表,查找select 需要的列。

查看索引是否失效?

查看explain分析结果

  • 查看 key_len 字段的值,该字段表示生效的索引的长度
    • null 表示没有走索引
    • 大于0,根据索引的字段的大小,判断出走了那些索引
  • 查看 rows 的值,该字段表示受影响的行数
    • 如果返回的结果集很小,但是rows很大,则说明没有走索引
  • 查看 Extra 的值
    • Using index 表示走了索引,查询的列满足覆盖索引
    • Using where 表示没有走索引
    • Using index condition 表示走了索引,有可能部分走了索引,结合rows判断,但是查询的列不满足覆盖索引
    • Using where; Using index 表示部分走了索引,但是查询的列满足覆盖索引
    • null 值 表示走了索引,但是查询的列不满足覆盖索引

DEMO

比如给(a,b,c)三列加上索引,简单的总结:

  • 最左匹配原则
    • 不满足,则索引失效
    • 部分满足,则满足部分生效
  • 如果是范围查询,比如 a>1
    • 查询的列满足覆盖索引,则走索引
    • 查询的列不满足覆盖索引,则不走索引
    • PS:a=3 and b>2,不管查询列是否满足覆盖索引,都会走索引,因为索引a 一定会生效。
  • c=2 and b=2 and a=2: 索引也会生效,因为是常量,mysql 优化器会进行优化。
  • 索引列不能参与函数运算
  • 索引列也不能进行隐式转换
create database IndexTest;
use IndexTest;
create table testIndex(
    id int(11),
    a int(11),
    b int(11),
    c int(11),
    primary key (`id`)
);
alter table testIndex add column d int(11);
create index index_abc on testIndex (a,b,c); # abc三列上面建立索引
# 插入7条数据,是1到7


# 查询的列全在索引中,但是where条件不满足索引 extra=Using where Using index
explain select a,b,c from testIndex where a>2 and b=3 and c=3;

# 不走索引,不满足
explain select a,b,c,d from testIndex where a>2 and b=3 and c=3;

# 对于单个索引的范围查询a>2,如果查询的列满足联合索引,则会走索引;如果查询的列不满足联合索引,则不会走索引
explain select a from testIndex where a>2; # 查询的列满足联合索引 Using where; Using index
explain select d from testIndex where a>2; # 查询的列不满足联合索引,没走索引 Using where
# extra=Using where 不走索引,查询列不满足覆盖索引
explain select a,d from testIndex where a>2;


# extra=Using where 表示没有走索引
explain select d from testIndex where b=2 and a>2;
explain select a from testIndex where a>2; # 走了a的索引,满足覆盖索引 Using where; Using index
explain select d from testIndex where a>2; # 没走索引 Using where,查询列不满足覆盖索引
explain select d from testIndex where a=2; # 走了索引,进行了回表Extra=null,查询列不满足覆盖索引

explain select d from testIndex where a>2 and b=3; # 没走索引

# extra=Using index 表示走了索引
explain select a from testIndex where a=2;

# extra=Using index 表示走了索引,且满足覆盖索引
explain select b from testIndex where a=2 and b=2;

# extra=null 表示走了索引,但是查询的字段不满足覆盖索引
explain select d from testIndex where a=2 and b=2;

# 虽然c、b、a不满足最左前缀原则,但是因为是常量,优化器会进行优化
# 虽然where后面的条件列是有执行先后顺序的,但是编译器会进行优化
explain select a from testIndex where c=2 and b=2 and a=2;

# extra=Using index condition 表示走了索引,进行列回表,不满足
explain select d from testIndex where a=2 and c=2 order by b;

# extra=Using where; Using index 表示走了索引,因为key_len=5 说明只有a走了索引,而且查询的列满足覆盖索引
explain select b from testIndex where a=2 and c=2 order by b;
explain select b from testIndex where a=2 and c=2; # key_len=5 ,只有a走了索引

# extra=Using index condition 走了索引,查询的列不满足覆盖索引
explain select d from testIndex where a=2 and c=2;# key_len =5,说明只有a走了索引
# extra=Using index  查询的列满足覆盖索引
explain select b from testIndex where a=2 and b=2 and c=2; # key_len=15,abc都走了索引
# extra=null  走了索引,但是查询的列不满足覆盖索引
explain select b,d from testIndex where a=2 and b=2 and c=2; # key_len=15,abc都走了索引


## PS:如何查看索引是否生效
## 用explain 分析sql语句
## 查看key_len 的值,就知道索引生效的长度:null表示没有走索引
## 查看 rows 的值,表示受影响的行数,如果返回结果集很小,但是rows值很大,则表示没有走索引
## 查看Extra

## extra=Using index 表示走了索引,且满足覆盖索引
## extra=Using where Using index 表示部分走了索引,但是查询的列满足覆盖索引
## extra=Using where 表示不走索引
## extra=null 表示走了索引,但是进行了回表,因为查询的字段不满足覆盖索引
## 还可以看rows 的值,表示受影响的行的大小;如果走索引就是返回的结果大小,不走索引的话就是全表的大小
## 所以综合extra 、 rows、key_len 来确定有没有走索引

Mysql 加锁的过程

分隔离级别(提交读、可重复读)讨论,再分查询类型讨论(范围查询、还是等值查询)、再分索引(主键索引、唯一索引、非唯一索引、不走索引)类型讨论、

X是互斥锁、S是共享锁、gap是间隙锁、next-key锁=X+gap锁

提交读隔离级别

在提交读隔离级别下会出现幻读的问题,加的锁是X或S锁;这个隔离级别下是不会加间隙锁的。

对于范围查询、等值查询是一致的

select * from t1 where id > 10 for update
select * from t1 where id = 10 for update

分索引情况讨论加锁:

  • 查询列是主键,则主键上返回的行,加上X锁
  • 查询列是唯一索引,给唯一索引上对应的记录加上X锁,而且还要给主键索引对应的记录加上X锁
  • 查询列是非唯一索引,给二级索引上满足条件的记录加上X锁,而且还要给主键索引对应的记录加上X锁
  • 如果没有索引,则会走全表扫描,所有的记录都会加X锁,但是会进行优化,对于不满足条件的记录会释放锁。

可重复读隔离级别 🌟

在可重复读隔离级别下会解决幻读问题,加的锁有X、S、gap锁

等值查询

select * from t1 where id = 10 for update

分索引情况讨论:

  • 查询列是主键索引,则对满足条件行加上X锁;如果条件不满足,则会在不满足条件的左右两个记录之间加上gap 锁,避免插入满足条件的记录。
  • 查询列是唯一索引:则对满足条件的唯一索引加上X锁,而且对应的聚餐索引也要加上X锁。
  • 查询列是非唯一索引:对满足条件的记录加上X锁,而且对该记录的左右两个记录之间加上gap锁;然后还会对聚簇索引加上X锁;然后读取下一条满足记录的数据并且重复加锁过程。(非唯一索引,可能有多行记录的值是一样的)。 这里加gap锁的目的是避免被其他事务插入满足查询条件的记录,否则就会出现幻读。
  • 查询列无索引:会进行全表扫描,会加上表锁,整个记录和间隙都会加上锁。

范围查询

select * from t1 where id > 10 for update
  • 查询列是主键、唯一索引、非唯一索引:都会加上间隙锁+X锁

总结:在可重复读隔离级别情况下,对于范围查询都会加上间隙锁+X锁;但是对于等值查询,有非唯一索引的情况下才会加上间隙锁+X锁,还有查询条件不满足的时候也会加上间隙锁;当然如果不走索引,肯定会全表扫描,整个表都会加上锁;注意二级索引上面加了锁,还要回到一级索引上面加上X锁。

参考博客:Mysql 加锁过程


目录