Mysql复习

一、一条sql执行的步骤

1、连接mysql服务端

先在程序中加入mysql驱动依赖（用于连接mysql）

注意：此时在客户端和mysql服务端，都存在各自的数据库连接池，主要用来限制和管理连接。客户端的连接池常见有DBCP、C3P0、Druid。

2、mysql接收sql语句，并进行解析

客户端通过连接发送sql语句到mysql，mysql有专门的线程监听sql请求，然后将sql传给SQL接口，由于此时这些语句mysql不能进行识别，所以需要sql解析器将sql语句进行解析。

3、选择最优执行计划

解析完后查询优化器会生成所有执行路径（执行计划），然后选择出一条最优路径。

4、由执行器调用存储引擎进行执行执行计划

此时执行器会调用存储引擎去执行相应的执行计划（由存储引擎去调用内存、磁盘中的数据）

在mysql中，sql接口、sql解析器、查询优化器都是通用的，只有存储引擎是不一样的。最常用的存储引擎就是Innodb。

⭐⭐⭐ 执行一条sql的流程：导入MySQL驱动进行连接mysql服务器（两端都有连接池）---》服务端接受到sql后发给sql接口，然后sql解析器进行解析---》查询优化器根据解析结果生成多个执行计划，然后选择一条最优执行计划---》执行器调用执行引擎执行该执行计划

二、了解Innodb中的Buffer pool，以及redo log(重做)、undo log（回滚）、binlog等日志的作用

以一条sql为例：update  user set  name='xxxx'  where id = 10

=========

Buffer Pool、redo log、undo log、binlog总结

1、Buffer Pool

一个innodb可以有多个buffer pool，一个页默认大小128M，每个buffer pool可以由多个chunk组成，而缓冲区最小的单位是缓冲页，多个线程竞争缓冲区的缓冲页、各种链表时会进行加独占锁保证线程安全。

1）、缓冲页：每个页有一个spaceId表空间Id、pageNo页号进行唯一标记，默认16k

2）、各种链表：free链表（空缓冲页链表）、flush链表（被修改过的脏缓冲页链表）、lru链表（已用了的缓冲页链表）（所以lru链表是包含了flush链表的）

free链表：如果需要缓冲页会去该链表取，执行sql时，如果需要数据会先去bufferpool获取（mysql会维护一个hash表<key=spaceId+pageNo，value=缓冲页地址>，可以根据这个hash结果快速定位是否有缓冲），没有则去磁盘获取。

flush链表：每次刷盘都是将该链表的缓冲页刷新到磁盘中

lru链表：如果free链表全满了，则会去lru链表淘汰尾步部分缓冲页（最近最少使用）

3）、一个sql执行过程缓冲页的变化流程

如果sql执行过程需要一些数据，先去看缓冲区是否有，没有则去磁盘获取，此时则需要去free链表申请空缓冲页，拿到正常执行，拿不到则去lru链尾步淘汰部分缓冲页（进行刷盘）。

lru链的设计思路：因为mysql存在一个预读机制，会读取一些连续的缓冲页进来（但是没用到），所以lru链设置了冷热区域，冷占37%，新加载的页会放在冷区域开头，1s内有被使用则放到热区域开头，如果热区域被再次调动，前1/4不会动，后3/4会调动到热区域开头。

lru的刷盘会有两个时机---》一个是定时任务，一个是满了会触发

2、undo log（回滚日志）

        保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

        用于事务回滚时，如果执行了一个insert语句，此时你在日志里就会记录insert语句的一些信息。如果需要回滚则执行该insert语句的逆向恢复delete操作即可。select不用。

3、redo log（重做日志，恢复日志）

记录对数据的修改、保证事务提交后修改的数据不会丢失、用于事务前滚恢复。防止宕机等情况下的数据丢失。

作用：确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

mysql重启时，会根据redo log恢复Buffer pool里的数据。

4、binlog

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。用于数据库的基于时间点的还原。

注意：redo log、undo、binlog都是在事务提交成功前就操作完的（日志数据可能存在内存、os cache、磁盘中）

1、Buffer pool缓冲区

进行数据的修改时会对缓冲区中的数据进行加独占锁，防止其他线程干扰。

（多个线程访问同一个buffer pool时需要进行加锁（对缓存页、各种链表等资源进行加锁），等一个线程执行完成后释放锁资源后下一个线程才能使用）

默认128M，通过innodb_buffer_pool_size配置其大小。

对于标记某个页用（space，page_no）其中space表示表空间id，page_no表示页号。

Innodb可以设置多个buffer pool，而每个Buffer Pool由多个chunk（默认128M，可以通过innodb_buffer_pool_chunk_size 来设置，可以通过chunk来动态的扩容单个buffe pool 的大小）。

1、Buffer pool的内部结构

Buffer pool中存在多个缓冲页（默认16k）+多个描述数据（用来描述对应缓冲页的）。

在Buffer pool中维护着几个链表：free链表（空缓存页链表）、flush链表（记录被修改过的脏缓冲页）、lru链表（管理已用的缓冲页）。所以lru链表是包含flush链表的。

1）、free链表

管理空的缓存页，需要申请缓存页时会去free链表取。（该链表有个基础节点专门用来记录free链的页数量等数据）。

我们进行sql操作时，如果需要某个记录，此时先会从buffer pool查看是否有缓存，mysql会维护一个hash表（key：表空间+数据页号，value：缓存页地址），此时根据这个hash结构进行查询是否有（此时只有一些行数据，怎么定位到他是哪个表空间、数据页号？）。如没有再去磁盘加载数据页（磁盘中用数据页进行存储）。

2）、flush链表

管理已修改的缓存页节点，同样有个基础节点记录该链表的信息。每次刷盘都是将flush链表的缓存页刷到磁盘中。

3）、lru链表

管理已用的缓存页，如果free没空缓存页了，此时就会淘汰lru（最近最少使用）的尾部的部分缓存页。

那么此时进行淘汰的？
什么节点放在链表头，什么节点放在链表尾？mysql就是基于lru算法（最近最少使用）进行改造的。下面说说改造的部分：

（1）、mysql存在预读机制，如果某个时机触发了预读机制，即本来现在我需要读数据页1，而预读机制把数据页2、3都读进来了，如果是传统lru那123都会放到链表头，但23却是没用的。还有如果进行全表扫描，我们需要的数据可能是部分，却把整个表的数据都加载进来了。（简单讲就是加载了一些没用的数据页）

此时mysql对lru设置了冷热区域，冷占37%（可以参数控制），以后每次新加载的缓存页都放在冷链头部，然后如果一个缓存页在被加载的1s（可以参数设置）之后还有访问则放到热数据的头部（即lru头部）。如果热数据被访问是直接调到lru前面？答案是否定的（热链数据多频繁调动消耗内存），mysql中热链后3/4被调用才会到lru链头，前1/4不会动。

上面我们将lru链的节点如何合理分配说明了，那么对lru尾部进行刷盘操作存在两个触发机制：1、mysql会有一个定时任务，会定时进行刷盘。2、如果free链没有空页且要申请页时会触发

---

1、数据预热：可以先用select+使用索引预加载数据到buffer pool。

5.6开始可以dump出buffer pool的数据（space+pageNo），重启后load这些数据。

mysql后台有很多线程例如日志线程、读写线程、刷脏页线程、dump线程等。当然可以调整这些线程的个数，例如调整写线程的个数。

---

checkPoint：用于刷盘脏页、缩短恢复时间。（缓冲池不够时会将脏页刷新到磁盘）

每个页有LSN，整个mysql实例也有一个全局LSN（也就是checkPoint，该lsn也是所有lsn最大的值），页更新时其页lsn也会更新。数据库宕机恢复是会读取全局lsn去日志中恢复数据（恢复lsn之前的数据）。注意：所有lsn都会被记录到redo log重做日志中。

在flush list中（管理脏页，即已使用的页）只存页第一次修改时的lsn，页刷到磁盘时，全局lsn才会被记录到redo log中。（redo宕机后重启的数据恢复原理）

        脏页的刷盘机制中，我们之前提过free list满时会将lru list末尾页刷进磁盘；重做日志不够用时，从flush 列表中选择一些页，强制刷盘；Master Thread以每秒或每十秒的速度从缓冲池的脏页列表（flush list）中刷新一定比例的页回磁盘。这个过程是异步的，不会阻塞查询线程。（脏页是同时存在flush、lru上的，有时从lru上刷盘，有时从flush上刷盘，大部分在flush上）

---

doublewrite机制：即一个页被写了两次，防止磁盘中页损坏不能恢复。

就是在内存中有一个doublewrite buffer（非mysql内），刷盘脏页时先拷贝一份到doblewrite buffer中，然后等将脏页写入磁盘后再则可以删除doublewrite buffer的数据。

2、undo log 回滚日志

内部结构、触发时机、作用

作用：保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。

---

用于事务回滚时，如果执行了一个insert语句，此时你在日志里就会记录insert语句的一些信息。如果需要回滚则执行该insert语句的逆向恢复delete操作即可。select不用。

每条日志的结构：

 如果要回滚，则拿着这条日志对某表的某些字段值进行回滚操作。

3、redo log 重做日志

1、作用及概述

记录对数据的修改、保证事务提交后修改的数据不会丢失、用于事务前滚恢复。

防止宕机等情况下的数据丢失。其中在Innodb中有个Redo log Buffer用来暂时存放redo日志信息等（后续将日志信息刷到磁盘）。所以一些修改操作都会写到redo log中。

作用：确保事务的持久性。redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

mysql重启时，会根据redo log恢复Buffer pool里的数据。

2、日志

redo 的日志大概格式：对xx表空间中的xxx数据页中的偏移量为xxx的地方更新了xxx数据。

（表空间号+数据页号+偏移量+修改几个字节的值+具体的值）

redo日志数据并不是一条一条的写入日志文件的，在mysql中用一个数据结构redo log block存放多行日志，刷盘的基本单位是以block为单位的（512k）。

redo log buffer就是这些日志行数据、block存在的内存缓存区（innodb中），redo log buffer默认16M（可参数调整）

---

redo刷盘时机：

那么redo log Buffer的日志信息缓冲什么时候进行刷盘操作的？

在刷盘过程中，一步用户空间缓冲区的数据不会直接刷新到磁盘中，而会经过操作系统缓冲区（OS Buffer），在由OS缓冲区调用fsync()将其刷进磁盘中的文件，

1）、mysql是通过innodb_flush_log_at_trx_commit参数来决定刷盘时机

当该参数为0（延迟写），每1s将redo log Buffer的数据刷到OS cache中，然后OS会即时的刷到磁盘中。为1（实时写）时每次提交事务都会即时的刷进磁盘。为2时（延时刷）每次事务提交都刷进os cache中，然后每隔1s将os cache的数据刷到磁盘中。

注意：OS刷到磁盘是花费时间比较长的，所以延迟刷的效率是高于实时写（0丢失，但写入性能低），低于延迟写（性能高但丢失数据多）。但默认使用的是实时写1。

延迟写，mysql奔溃而操作系统没崩溃时，会丢失1s数据。而延时刷，在这种情况下是不会丢失数据的。

2）、redo log buffer日志数据占了一半以上，就会触发刷盘。

3）、mysql关闭时

4）、后台线程每隔1s进行定时刷盘

---

磁盘中log文件个数？
默认情况下目录就只有连两个文件：ib_logfile0和ib_logfile1,每个48MB。写满第一个就写第二个，第二个写满了就覆盖第一个的内容。

---

注意：redo log、undo、binlog都是在事务提交成功前就操作完的（日志数据可能存在内存、os cache、磁盘中）

4、binlog

做一些归档的日志，注意用于主从复制。

作用：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。用于数据库的基于时间点的还原。

我们同样来看看binlog何时进行刷盘？
mysql也存在一个sync_binlog来决定binlog日志的刷盘策略，为0时，实时写进OS Cache，然后每段时间（可能是1s）刷新到磁盘；为1时，事务提交前实时刷到磁盘；当为N时，表示每提交N个事务才会写入磁盘。5.7后默认为1。

三、事务

事务总结：undolog保证原子性，redolog保证一致性

事务原子性：undo log日志回滚保证，持久性：redo+undo，一致性：redo log，唯一性：锁机制

---

要注意一个事务真正提交之前是要完成redo、binlog、undo等日志操作。

那么事务提交的真正时机是什么时候（事务提交成功标志）？

那么在上面redo 、bionlog日志都刷盘完了（也许还在OS中），此时会将binlog文件名+这次更新的文件Offset（偏移量）记录到redo log日志，并最后加上Commit标志。至此才叫一个事务的真正提交成功。如果没加commit标识也算没提交成功。

既然基于在redo记录binlog偏移量+commit标志代表事务提交成功，那么我们下一步需要知道的是这个有什么用？事务回滚原理是怎么实现的（基于undo？还是redo+binlog）？事务提交后mysql宕机，新数据没刷到磁盘，此时怎么恢复数据的？

---

mysql事务存在的问题

1、脏写

事务A改了c值，B也改了c值，然后A回滚c值（AB都没提交），导致把B的修改也回滚了。

（就是把别人的修改值回滚没了）

2、脏读

A去改c值，B去读c，然后A回滚，导致B读到的是脏数据。（读了回滚前的脏数据）

3、不可重复读（值被删除或者修改，导致前后读到的不同）（此时是已解决读未提交的）

A先读c值，B去修改c值后提交，A再次去读取时发现c值变了（读了提交前后的数据）

4、幻读（值增加了，导致前后读到的不同）

A查询一批数据，一开始是10条，过段时间再查询，发现是12条了。（多读出记录来）

---

mysql使用事务隔离级别解决

RU解决脏写（读未提交）

RC解决脏读（读已提交）

RR解决不可重复读和部分情况下的幻读（可重复读）

serializable解决所有幻读（串行化）

不可重复读重点在于update和delete，而幻读的重点在于insert

---

RR、RC级别的实现原理？RR能解决幻读？

说原理前需要先了解undo log版本链、ReadView机制、MVCC机制

1、undo log版本链

我们缓存页的每条数据存在两个隐藏字段，一个是trx_id，一个是roll_pointer。其中trx_id是最近一次更新这条数据的事务id，roll_point指向更新这个事务之前的undo log节点。

而利用roll_point字段就可以形成一条undo log版本链（该版本链节点信息包含：值、事务id、指针）

2、基于undo log版本链实现的ReadView机制

简单讲就是你执行一个事务的时候，会给你生成一个ReadView，里面有4个东西比较关键：

1）、m_ids，记录在mysql中哪些事务还没提交

2）、min_trx_id，m_ids中的最小值

3）、max_tri_id，就是mysql下一个要生成的事务id，就是最大id（我猜mysql生成的事务id是呈增加趋势的）

4）、creator_trx_id，当前事务id。
然后事务访问该记录时，会根据这四个参数去判断要去使用版本链的哪个版本节点（具体可以看我文章）。

总结：1）、自己开启的事务，不能读取比自己早开启的事务但没提交修改的值
            2）、不能读取比自己晚开启的事务修改的值（如果晚开启的事务提交了且m_ids没有则可以读取）

（即不能读取比自己先开启/后开启的事务修改的值）
通过ReadView（用这些数据来判断undo log多版本链的事务id是发生在本身事务的前还是后，事务提交了没等情况）和undo log多版本链来控制实现，这样就只能读到事务开启前已经固定的数据了
注意这里的ReadView是事务开启时就生成的，在事务期间其他事务提交或开启了是不会进行实时更新的，此时的ReadView是旧的数据。

3、RC级别的实现原理

要达到的效果只会读到已提交事务的值，这样就能解决脏读。

RC利用undo log版本链+每次操作时更新ReadView（这样其他提交了的事务修改信息就会实时更新）的机制去实现读已提交。

MVCC机制就是基于ReadView机制+undo log多版本链条实现的

4、RR级别的实现原理

RR级别是可重复读的、且一些情况下解决了幻读

1）、RR解决不可重复读问题

就是不要读到其他已提交事务修改的值。

此时只要在第一次操作的时候生成readView，之后操作都不更新readView，这样其他已提交事务更新的值就不会被读取到了。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

READ COMMITTED —— 每次读取数据前都生成一个ReadView

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView
2）、基于undo log版本链+readview的MVCC机制、锁分别保证某些情况下的幻读。

---

事务可以进行组提交（多个事务一起提交）

mysql除了支持本地事务，还支持分布式事务XA（开启——xa start ‘事务名’；结束——xa end ‘事务名’；提交——xa prepare ‘事务名’；回滚——xa rollback ‘事务名’）

本地事务：开启——begin；提交——commit；rollback——回滚。

四、事务提交后，该将Buffer pool中的新数据刷到磁盘了

事务都提交完了，最后我是不是需要将Buffer pool中的新数据刷到磁盘中。mysql会有一个IO线程在某个时间去进行异步刷盘操作。（没刷新mysql就宕机了，此时可以根据前面的redo日志进行恢复）

五、磁盘中如何存放数据的？

1、数据页

默认16k，mysql的数据是一行一行的往数据页中存储的，一个页不够了则会存到下一个页（溢出页）（这也是行溢出现象）。

一个数据页中包含：多个行数据、文件头、数据页头、最小记录、最大记录、数据行数、空闲空间、数据页目录、文件尾部等。

可以对页进行压缩，压缩完的页对应的表叫做压缩表。

一般size>页大小的一半，则触发行溢出现象。超过8k则会将超出的部分存到其他页（而不是16k放满再存到其他页）

2、表空间

表空间就是我们创建的那一个个表，在物理上对应各个表名.ibd磁盘文件。一个表空间有多个数据页。

3、数据区

一个数据区有64个连续的数据页。256个数据区又被划分为一个组。

每个数据页中由多个行数据构成，页中有一个行格式（存放行数据的存储格式）

4、行数据格式

每行的数据存放格式：

变长字段的长度列表，null值列表，数据头，隐藏字段，column01的值，column02的值，column03的值........

变长字段列表：用来记录那些可变字段 

null列表：记录为null的字段 

数据头：记录delete_mask(标记该行数据是否被删除，所以我们删除一行数据时不是立即从磁盘中清除的)、数据类型、下一条数据的指针等信息

 注意：除了上面的几个列表，mysql还会自己新增一些隐藏字段（例如DB_ROW_ID字段，没主键时自定义为主键；db_trx_id:事务id，表示哪个事务更新的；db_roll_ptr:回滚指针，用来指针回滚的）

---

mysql对于数据页是随机读写，对redo、undo、binlog等日志是顺序读写。

十、两个mysql的生产案例

aa

六、锁机制

1、latch锁（轻量级锁）：（用来控制内存中数据结构、临界资源的并发访问，即类似我们java中的锁，锁住的对象是临界区）（latch锁的加锁释放锁过程是很快的，毫秒级别的，基本可以忽略latch锁）

2、 lock锁：（锁的是数据库层面的锁，innodb的锁住的对象是行，保护的是数据库中的内容） lock锁的持续时间是整个事务，如果事务没被提交，会一直持有。

其实两种锁的应用层面是不同的，latch我们可以理解为java中我们对某个对象的并发安全控制，而lock是对于行数据的保护。

lock锁中存在多种类型锁

show engine innodb status可以查看mysql中的锁情况

1）X锁（排它锁）

对某行进行增删改时需要对该行记录加排他锁。select要加上排他锁可以在语句最后加上for update，不过此时如果其他会话也是可以通过mvcc机制读这条记录的。

2）S锁（共享锁）

加上共享锁：sql后追加lock in share mode。

在页中会维护一个bitmap（位图减少内存），这个位图的位置对应各行数据，位图某位为1时表示这行数据被加锁了。

3）、意向锁

 意向锁都是表级别的。innodb不仅仅有行锁，还有意向锁等表锁。

如果我们要像表a的第n行加一个x锁，此时第一步就是要对表a加一个IX锁，然后在对行n加X锁。S和IS类似。为什么之前说意向锁是不互斥的，就是因为意向锁顾名思义就是我之后有要加某个锁的意向，即先预先说明下我下面可能要加X锁或者S锁。

意向锁和行锁中：只要意向共享锁+共享锁、意向独占锁+独占锁（互斥锁）是不互斥的。

行锁中：只要共享锁+共享锁不互斥。

加锁是需要进行层级加锁的，先对表加锁、然后是行数据。

4）、自增锁

对于自增列的并发处理的，其实实际上就是latch锁，执行完语句就释放，不会因为事务是否提交才释放锁。

---

锁的算法

锁的算法分为：记录锁（锁单个行记录，Record Lock）、范围锁（锁某个范围，不包含本身，Gap Lock）、范围+记录锁（Next-key Lock）。

Next-key Lock锁和前面两个锁算法不兼容

RR事务隔离级别用的锁算法大部分使用next-key锁算法（范围+记录），这个算法有一个问题就是插入的性能优点慢。有小部分情况下会进行优化使用record锁（语句使用的索引是唯一索引，且只返回一条记录的情况下）。

RC事务级别的锁大部分是记录锁。

---

RR级别下通过next-key锁算法解决部分情况下的幻读？幻读是其他事务进行插入导致的（例如本来只有一个6，一段时间后发现有2个6了）。

next-key算法会锁住记录本身+前一个记录+后一个记录。例如现在4、6、8、10。现在插入一个6的记录，那么（4，6]、（6，8）就会被加锁，而如果在页中有6的记录了，新的6如果插入会放在原6后面。这时[6,8)这个区间已经被锁了（没有6则锁的是前面的记录），其他线程就不会插入导致幻读现象。

上面的情况是基于索引的，没索引时为了防止幻读此时会锁住整个表。

---

1. RR隔离级别，如果事务中都是快照读，或者全都是当前读，都不会产生幻读。只有当前读和快照读混用，才会产生幻读。（一下读MVCC快照的数据，一下读当前数据，这样就无法保证了）
2. MVCC保证快照读不会幻读（如果没做修改操作，都是去读MVCC的快照，如果只读快照就只有一份数据所以不会出现幻读现象）
3. next-key lock（间隙锁）保证当前读（读取当前最新的数据）不会产生幻读（间隙锁在事务期间锁住一个范围，导致其他事务无法插入就不会出现幻读）

---

死锁

解决死锁：1、配置超时  --innodb_lock_timeout 超过时间就自动放弃。2、自动死锁检测：wait-for graph。（会根据锁的信息链+事务等待链推断出是不是存在死锁回路。如果存在则回滚某个事务（通过undo的量来判断回滚的事务，回滚量比较少的））

 show engine innodb status：只会显示最后一次死锁的信息。如果要记录所有死锁信息：开启参数innodb_print_all_deadlocks 。

七、索引及其查询原理

前置知识：

1、数据页

在磁盘文件中，存在多个数据页，而每个数据页都有两个指针，分别指向上一个、下一个数据页的物理地址。而数据页内部有多个数据行，每个行数据也有一个指针指向下一个数据行。

而且每个数据页中又会维护一个页目录，这个目录记录着行数据的主键、主键对应的槽位位置。（槽位放着各个数据行）我们如果查询数据时如果定位到某个数据页时，就会根据这个页目录根据主键进行二分查找，然后快速定位到对应槽位。（非主键会经过一次回表后找到其主键）

2、页分裂

一个页不够了则需要再弄一个页，而数据页中的索引是递增有序的，如果此时页A满了且其最后的主键为10，现在要插入主键为8的数据，此时8小于10应该插到页A的，而不是插入到新的页B。这时就出现了页分裂问题。

此时mysql为了解决页分裂，就将8放到页A中，然后溢出的行数据就放到新的页B。

---

主键索引

除了上面说的每个页维护一个页目录（主键：槽位），mysql还对各个页也维护一个主键目录（每个页中最小主键：页号，页号会指向相应的页地址）。

那么我们现在有一个主键6，先回去主键目录定位到属于哪个页，然后在进入页中通过页目录快速定位到数据槽位。

那么此时又有一个问题，如果有10000个页，那此时主键目录不是很长，那此时一个主键例如9669进行，岂不是要进行全部扫描了？

mysql中会将这些目录存进数据页中，该页被称为索引页。一个索引页不够就存多个索引页。

索引页太多也会查询过慢，所以索引页以B+树的形式依次排序。

（注意叶子节点是双向链表），而主键索引树的叶子节点直接存储数据也被称为聚集索引。

非主键索引树的叶子节点存放的是非主键字段（可能也是组合索引字段）+主键+指针。

注意：主键或非主键索引链表都是有序的

---

二级索引（辅助索引）

在二级索引中，叶子节点的索引页（数据页）存放的是主键+name数据。

如果我们此时根据name来查找数据，此时就会先在name对应的B+树找到相应的主键，然后拿着这个主键再去主键的索引树找到相应的真实数据。（这个过程就叫回表）

---

索引如何建立？sql如何尽量使用索引？

1、索引不是越多越好，索引、索引树维护起来是比较耗性能的，数据量少时用索引可能性能低于全表扫描。

2、where后面一般要用=进行等值匹配。且联合索引注意字段名称、顺序（即要符合最左原则）。

3、最左前缀匹配。用like ‘aa%’ 不用 like  ‘%aa’。

4、范围查找第一个用的到索引，后面的用不到。class_name>'1班'  and class_name<'5班'

（第一个走索引，第二个不走）

5、order by、group by 可以利用建索引，利用索引的排序性质使得性能提高。（没用索引就会走全表扫描、临时表等耗时操作）

6、尽量不要对字段基数低的字段建索引（例如这个字段只要0、1，如果建了索引那么二分查找的效率就和全表扫描差不多了）

尽量选择字段类型比较小的字段设计索引，例如tinyint字段类型小。搜索的性能小。如果必须要用大类型，例如varchar（255）此时可以用前20做为索引即可。

尽量不要用函数、计算等操作，否则大部分情况下用不到索引的。

尽量使用索引覆盖（不用进行回表）

八、执行计划+explain+sql优化

如果有表a、b，此时mysql会优先选择数据较大、有用到索引的表为内表（当然也不是一定，因为mysql会根据数据各种情况计算成本的），数据量小、没索引的为外表。此时比较次数会较少。

explain用来显示sql语句的执行计划。

其中select_type、type、key、ref、extra是比较重要的指标

1、看ref建立索引或者修改sql

const（唯一索引）、ref（非唯一索引）、range（范围索引）性能较高（此时都是走正常索引的）；index性能较差（尽量避免，没走正常索引，但用到了二级索引的叶子节点数据，此时遍历二级索引的所有叶子节点）；all全表扫描，性能最差，需要优化; 存在多个二级索引树先筛选再求并集/交集，然后进行回表操作（这样可以尽可能的提升性能）

2、看key使用了哪些索引，看是否需要优化

3、看Extra看是否进行排序、分组操作

filesort：表示进行了排序，此时会用到排序内存，可以适当配置排序内存进行优化

temporary：分组时就常使用临时表，此时可以调整临时表的默认内存大小

上面的排序内存、临时表等如果内存大小不够用，就会借用磁盘进行使用，那么就会降低性能。

九、主从、高可用

1、普通主从

slave从机只做备份，不提供读取，主机提供读写

‘2、读写分离的主从

master只做写，slave只做读。

3、主从之间的数据同步原理

mysql做增删改操作时会记录到binlog日志，从库会有个IO线程和主库做TCP连接，master一个IO dump线程通过tcp连接将binlog日志传给从库IO线程，然后从库将读取到的日志写入本地的relay日志文件，最后从库有一个sql线程回去读取relay日志内容进行日志重做。

主库IO dump线程——（通过TCP连接传送binlog日志信息）——》从库IO线程——（写入本地的relay日志文件）——》从库sql线程进行日志重做。

4、数据延迟问题？

主机宕机，有些数据还没到从库，此时如何解决？
此时我们需要尽可能缩短延迟时间

1）、从库并行多线程复制数据

从mysql5.7开始就支持并行复制，可以在从库中设置slave_parallel_workers > 0, 然后把slave_parallel_type设置为logical_clock。这样就设置成功了。

2）、半同步

默认主从的数据传输是异步的，可以配置为半同步模式。

半同步：当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，所以这样就保证了一个事务至少有两份日志，一份保存在主库的Binlog，另一份保存在其中一个从库的Relay-log中，从而保证了数据的安全性和一致性。

另外，在半同步复制时，如果主库的一个事务提交成功了，在推送到从库的过程当中，从库宕机了或网络故障，导致从库并没有接收到这个事务的Binlog，此时主库会等待一段时间（这个时间由rpl_semi_sync_master_timeout的毫秒数决定），如果这个时间过后还无法推送到从库，那MySQL会自动从半同步复制切换为异步复制，当从库恢复正常连接到主库后，主库又会自动切换回半同步复制。

半同步复制的“半”体现在，虽然主从库的Binlog是同步的，但主库不会等待从库执行完Relay-log后才返回，而是确认从库接收到Binlog，达到主从Binlog同步的目的后就返回了，所以从库的数据对于主库来说还是有延时的，这个延时就是从库执行Relay-log的时间。所以只能称为半同步。

需要在主从机上配插件进行配置。

3）、使用shading-jdbc、mycat等中间件配置同一连接内数据都从主库查询，这样数据就会实时更新。

5、高可用

6、分库分表

十、sql编程

1、service mysqld start开启mysql

创建、删除一个数据库：create database/schema  test;     drop  database/schema  tets；

一个数据库对应一个数据库实例（单进程），一个数据库对应datadir参数的目录下的一个文件夹，每张表又对应一组文件（5.7对应三个文件，8.0对应一个文件）。

查看表定义信息：show create  表名

2、各种日志（错误日志、慢查询日志）

1）、错误日志地址：查看log_error参数。启动mysql或者运行过程如果发生错误利用错误日志文件进行排查。

2）、慢查询日志地址：超过某个时间阈值的sql语句记录到文件。（默认名：机器名-slow.log，名字可以参数配置；阈值可以参数配置，默认10秒；地址可以查询slow_query_log_file参数；5.7默认不开启），慢查询日志一般用来调优慢sql。

如果慢查询日志太大，此时要对没用了的信息进行删除，首先要对原文件进行备份，让后重写生成一个慢日志文件（flush slow logs）。

慢查询日志还可以配置没索引就被记录、扫描行数多就记录等配置。

也可以配置慢查询日志到表，不过比较消耗性能。

有一些情况下sql执行时间很长，但没有记录到慢查询日志中：
        并发情况下，会话1对某个数据进行for update操作，然后会话2要对这个数据进行更新操作，但是此时该数据已经被会话1进行加锁了，所以会话2只能等待。等到会话2执行完成后，即使会话2的sql执行很长也不会记录到慢查询日志中。

        慢查询日志的记录中会有query_time和lock_time两个信息，分别代表整个过程的时间、过程中数据被加锁了等待时间。而慢查询时间阈值是根据两者之差去记录的。

所以并发下的锁等待时间是不计入慢查询时间阈值的。

3）、通用日志

记录数据库的所有操作，默认名机器名.log （可以配置general_log参数），由于是将所有操作都记录，所以开启了的性能会明显下降。如果不是非必须场景一般不要开启。

总结：一般启动或运行错误看错误文件，性能问题看慢查询文件，审计等场景看通用文件。

---

一般对于符号类型建议使用有符号类型（Signed，默认就是有符号类型）。

数字类型对应的一些函数：
1）、floor（）：向下（小）取整。

2）、round（）：向上取整

3）、rand（）：随机获取0-1的值。

如果要去n-m的值：floor( n + rand() * (m-1) ) 例如1-100  ：select floor（1+rand（）*99）；

其中可以看到存储的类型有分字符、二进制的类别。例如varchar（10）表示可以存10个字符。而binary（10）表示存10个字节。

 md5（）：加密函数

 concat()：拼接字符串

length（）：查看有多少个字节 （括号里面填充的是字符串，如果不是会进行隐形转换）  charilength（）：查看多少个字符

upper、lower：转换为大小写

concat_ws():根据某字符进行连接：

repeat（）：产生重复的字符

lpad、rpad：左填充和右填充

字符串类型其实还有一种枚举类型————ENUM & SET。 可以起到约束的作用：例如性别约束为男女，插入其他的就会报错。

now（）：查看当前时间。now（6）：表示显示毫秒的长度（5.6开始支持）

 一般都是使用datetime或者timestamp这两种类型。这两个类型是有区别的：

unix_timestamp():表示某个时间到1970-1-1 的秒数（这会转换为int类型）。

所以有些人在设计时间是喜欢将时间字段设置为int类型，但一般不建议这么做，所以一般还是使用datetime或者timestamp（2038年会被用完），不过还是建议使用datetime。

不过timestamp是可以跨时区的，如果业务跨不同时区则可以用timestamp。timestamp会根据时区来相应调整时间。

分区表（不是分库分表）：即我们看到的数据库可能是一张表，但是mysql会将这张大表分解为多张小表进行存储。5.7只支持水平分区，即根据某个字段按照某种规则进行分区。注意分区字段要么是索引、要么是索引的一部分，所以说mysql是局部分区（例如上面的col3不是索引，所以创建分区表会报错）

 1nf：就是每张表都要有主键，且所有属性依赖于主键

2nf：没有部分依赖（即没有属性依赖于主键的一部分）

3nf：没有传递依赖。

临时表：

想要创建一个tmp临时表： create temporary table tmp (a int);

注意这种临时表和我们讲group by时的临时表（内存表，在sql执行期间mysql隐式创建的）是不一样的。这种临时表是手工创建的。创建完临时表后如果查看表是看不到这个表的，但是可以对该临时表进行插入等操作：

 这种临时表是会话级别的，其他会话是不会收到影响的。这种临时表的内容是存放在mysql的data目录下的一个临时表空间文件（ibtmp1），每次重启mysql会初始化清除掉临时表空间的数据。

---

select + 函数（可以使用后面from获取到的字段）/字段（从from后面获取到的字段）+from +表名+where +group by +having +order by +limit

1、order by

ASE:从大到到小，降序。默认

DESC：升序。

mysql会给排序分配一个单独的内存进行操作。可以查看sort_buffer_size，可以适当调整下大小使得性能提高。

2、limit

取前n条数据：limit  n；
limit 10,10；  从第10条开始取，一共取10条。（实际就是取10条数据，然后将最后10条返回）

如果limit 的深度很深的话（例如limit 10000，10），这样的效率是比较慢的（会取10010条数据，再将最后10条返回）

如果深度较深的话性能会降低，那么此时就可以用索引去优化：

3、group by  分组

例如订单表中，我们需要查看每个月的订单总金额，此时就得使用group by进行查询了。

分组也可以接多个字段：group by  字段1，字段2；

则表示字段1和字段2相同的放在一组,一般分组后是要对每组的数据进行聚合运算，例如sum、count等。

group by的优化（非索引方式）

 进行数据分组，每组的数据都会存放到临时表内存（mysql会独立一个临时表内存用于分组操作等）中。那么此时可以对于临时表进行优化

临时表有一个参数tmp_table_size(默认16M)，可以用show variables like '%tmp%'; 查看

4、count(*)、count(1)、count（字段）

count(字段)：返回该字段非null值的个数

count（*）和count(1)（或者count（数字）的结果都一样，count 1、2、3、。。都一样）：表示返回记录行数。

5、having ：过滤

主要要区分having和where的区别。

 而having表示对select后面聚合之后的值进行过滤（例如这里使用了聚合之后的值count）。（where不能使用聚合后的值）

如果将上面改为下面：

会报字段不存在的错误，之前的表已经被group by进行分组了，最后只剩下month、count、sum这三个列，所以最后进行having会报字段不存在。而having一般是使用select后面的字段。改成这样才对：

group_concat(price,'：',1):可以对price进行分组后以：号隔开

---

子查询：
 

1、in （推荐用）

即存在某个子查询返回的结果，匹配子集中的任一元素

2、any、all、some等

3、exists

主要区别in 和 exists

in和exists的结果一般的相同的，但是其运行机制不同

in的写法：（独立子查询：in后面的子查询和外面的没有关联）

 修改为existst的sql：（相关子查询，里面的子查询需要关联外面的进行过滤）（即将in语句逻辑改到子查询中取）

其实用exists和in都可以实现一样的效果，那么什么时候用in、什么时候用exists？
外层查询表小于子查询表（数据少于），则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个。

n的逻辑：子查询一次查完，查出的结果一条一条的去外查询表中匹配扫描（被扫描表：外表。扫描次数：子表行数）

existst逻辑：外查询表每条数据都去子查询中关联执行。（被扫描表：子表，扫描次数：外表行数）

如果要效率最高，此时要求扫描次数少，那么in时适用于子表较小的时候，即外层查询表大于子查询表；而同样exists适用于外表较小，即外层查询表小于子查询表。

not in  和  not existst？
not exists性能是高于not  in的，not in是用不到索引的，因为not in实质上等于!= and != ···，因为!=不会使用索引，故not in不会使用索引。

总结：

exists和in区别总结：
 in：独立子查询，适用于外表大于子表的情况，not in不能使用索引，not in会出现子查询中有null则查出的结果永远为空的情况。

existst：关联子查询，适用于外表小于子表的情况。不会出现not in 子查询null查询结果空的问题。

not的推荐使用not exists，in和exists看场景使用。

---

联表

1、inner join /join  内联 （求交集）

2、outer  join  外联  

分为左联left join/left outer join、右连 right join/right outer join

3、union：拼接两者的结果

笛卡尔积: select  *  from a, b

---

Prepare  sql

可以防止sql注入，动态查询条件。

sql注入：比如我们现在有一个语句：select * from  a  where  id = 100；

此时被人找到接口，将sql修改为：select * from  a  where  id = 100 or  1=1；此时不管怎样，都会查出所有数据。

---

存储过程、自定义函数、触发器、视图、事件event

十一、存储引擎

Inoodb支持事务，myisam不支持事务。

5.5默认myisam（不支持事务、表锁、容易丢失数据、性能低，所以一般不要使用）

现在基本都是默认innodb（支持事务、行锁、性能高）

mysql也不是只用innodb，例如mysql的临时表操作就会用到memory存储引擎。

        虽然说一般用innodb存储引擎，事实上就是以innodb为主体，一些操作如果其他存储引擎好mysql内部还是会用到其他的存储引擎的，例如这里的临时表操作mysql内部就会用到memory存储引擎，所以该引擎不能被禁用。

补充：

1、使用自增id的缺点

那么使用自增的id就完全没有坏处了吗？并不是，自增id也会存在以下几点问题：

①：别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况

②：对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争

③：Auto_Increment锁机制会造成自增锁的抢夺,有一定的性能损失

 =========================常见面试题====================

1、关系型数据和非关系型数据库的区别

关系型：用关系模型来组织数据，数据更新的开销小，支持复杂查询

非关系：无需进行sql解析且基于键值对，读写效率高，支持多种数据存储（图片文档等）

2、

原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？
原因：从大到小可分为四种情况

MySQL 数据库本身被堵住了，比如：系统或网络资源不够。

SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。

确实是索引使用不当，没有走索引。

表中数据的特点导致的，走了索引，但回表次数庞大。

解决：

考虑采用 force index 强行选择一个索引

考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

如果确定是索引根本没必要，可以考虑删除索引。

3、MySQL 是如何保证数据不丢失的？
只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据

在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

4、误删数据怎么办？
DBA 的最核心的工作就是保证数据的完整性，先要做好预防，预防的话大概是通过这几个点：

1）、权限控制与分配(数据库和服务器权限)

2）、制作操作规范

3）、定期给开发进行培训

4）、搭建延迟备库

5）、做好 SQL 审计，只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核

6）、做好备份。备份的话又分为两个点 (1)如果数据量比较大，用物理备份 xtrabackup。定期对数据库进行全量备份，也可以做增量备份。(2)如果数据量较少，用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份binlog 文件也是很有必要的

如果发生了数据删除的操作，又可以从以下几个点来恢复:

1）、DML 误操作语句造成数据不完整或者丢失。可以通过 flashback，美团的 myflash，也是一个不错的工具，本质都差不多，都是先解析 binlog event，然后在进行反转。把 delete 反转为insert，insert 反转为 delete，update前后 image 对调。所以必须设置binlog_format=row 和 binlog_row_image=full，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。

2）、DDL语句误操作(truncate和drop)，由于DDL语句不管 binlog_format 是 row 还是 statement ，在 binlog 里都只记录语句，不记录 image 所以恢复起来相对要麻烦得多。

只能通过全量备份+应用 binlog 的方式来恢复数据。一旦数据量比较大，那么恢复时间就特别长

3）、rm 删除：使用备份跨机房，或者最好是跨城市保存。

4、drop、truncate 和 delete 的区别
DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATE TABLE  则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

drop语句将表所占用的空间全释放掉。

在速度上，一般来说，drop> truncate > delete。

如果想删除部分数据用 delete，注意带上 where 子句，回滚段要足够大；

如果想删除表，当然用 drop；如果想保留表而将所有数据删除，如果和事务无关，用 truncate 即可；

如果和事务有关，或者想触发 trigger，还是用 delete；如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据。

5、如何理解 MySQL 的边读边发
如果客户端接受慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间会很长。

服务端并不需要保存一个完整的结果集，取数据和发数据的流程都是通过一个 next_buffer 来操作的。

内存的数据页都是在 Buffer_Pool中操作的。

InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。

6、MySQL 的大表查询为什么不会爆内存？
由于 MySQL 是边读变发，因此对于数据量很大的查询结果来说，不会再 server 端保存完整的结果集，所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。

InnoDB 引擎内部，由于有淘汰策略，InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法，使用链表实现，实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。对冷数据的全扫描，影响也能做到可控制。

7、MySQL 临时表的用法和特性
只对当前session可见。

可以与普通表重名。

增删改查用的是临时表。

show tables 不显示普通表。

在实际应用中，临时表一般用于处理比较复杂的计算逻辑。

由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理时临时表的重名问题，在线程退出的时候，临时表会自动删除。
 

8、都说 InnoDB 好，那还要不要使用 MEMORY 引擎？
内存表就是使用 memory 引擎创建的表

为什么我不建议你在生产环境上使用内存表。这里的原因主要包括两个方面：锁粒度问题；数据持久化问题。

由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双 M 架构，还可能导致主库的内存表数据被删掉。

9、如果数据库误操作, 如何执行数据恢复?

数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的bin log，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。

==============主从备份相关
10、MySQL 是如何保证主备同步？
主备关系的建立：

一开始创建主备关系的时候，是由备库指定的，比如基于位点的主备关系，备库说“我要从binlog文件A的位置P”开始同步，主库就从这个指定的位置开始往后发。

而主备关系搭建之后，是主库决定要发给数据给备库的，所以主库有新的日志也会发给备库。

MySQL 主备切换流程：

客户端读写都是直接访问A，而节点B是备库，只要将A的更新都同步过来，到本地执行就可以保证数据是相同的。

当需要切换的时候就把节点换一下，A的节点B的备库

一个事务完整的同步过程：

备库B和主库A建立来了长链接，主库A内部专门线程用于维护了这个长链接。

在备库B上通过changemaster命令设置主库A的IP端口用户名密码以及从哪个位置开始请求binlog包括文件名和日志偏移量

在备库B上执行start-slave命令备库会启动两个线程：io_thread和sql_thread分别负责建立连接和读取中转日志进行解析执行

备库读取主库传过来的binlog文件备库收到文件写到本地成为中转日志

后来由于多线程复制方案的引入，sql_thread演化成了多个线程。

11、什么是主备延迟
主库和备库在执行同一个事务的时候出现时间差的问题，主要原因有：

有些部署条件下，备库所在机器的性能要比主库性能差。

备库的压力较大。

大事务，一个主库上语句执行10分钟，那么这个事务可能会导致从库延迟10分钟。

12、为什么要有多线程复制策略？
因为单线程复制的能力全面低于多线程复制，对于更新压力较大的主库，备库可能是一直追不上主库的，带来的现象就是备库上seconds_behind_master值越来越大。

在实际应用中，建议使用可靠性优先策略，减少主备延迟，提升系统可用性，尽量减少大事务操作，把大事务拆分小事务。

13、MySQL 的并行策略有哪些？
按表分发策略：如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个 worker 不会更新同一行。缺点：如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。

按行分发策略：如果两个事务没有更新相同的行，它们在备库上可以并行。如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row。缺点：相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。

14、MySQL的一主一备和一主多从有什么区别？
在一主一备的双 M 架构里，主备切换只需要把客户端流量切到备库；而在一主多从架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。

15、主库出问题如何解决?
基于位点的主备切换：存在找同步位点这个问题

MySQL 5.6 版本引入了 GTID，彻底解决了这个困难。那么，GTID 到底是什么意思，又是如何解决找同步位点这个问题呢？

GTID：全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识；它由两部分组成，格式是：GTID=server_uuid:gno

每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。

在基于 GTID 的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例 B 需要的日志已经不存在，A’就拒绝把日志发给 B。

16、MySQL 读写分离涉及到过期读问题的几种解决方案?
强制走主库方案

sleep 方案

判断主备无延迟方案

配合 semi-sync 方案

等主库位点方案

GTID 方案。

实际生产中，先客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。

17、MySQL的并发链接和并发查询有什么区别？
在执行show processlist的结果里，看到了几千个连接，指的是并发连接。而"当前正在执行"的语句，才是并发查询。

并发连接数多影响的是内存，并发查询太高对CPU不利。一个机器的CPU核数有限，线程全冲进来，上下文切换的成本就会太高。

所以需要设置参数：innodb_thread_concurrency 用来限制线程数，当线程数达到该参数，InnoDB就会认为线程数用完了，会阻止其他语句进入引擎执行。

================性能相关

48、如何最快的复制一张表？
为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表

一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句

另一种方法是直接将结果导出成.csv 文件。MySQL 提供语法，用来将查询结果导出到服务端本地目录：select * from db1.t where a>900 into outfile '/server_tmp/t.csv';得到.csv 导出文件后，你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中：load data infile '/server_tmp/t.csv' into table db2.t;

物理拷贝：在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。

49、grant 和 flush privileges语句
grant语句会同时修改数据表和内存，判断权限的时候使用的内存数据，因此，规范使用是不需要加上 flush privileges 语句。

flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。

50、要不要使用分区表？
分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。

分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时的 drop 掉。

51、join 用法
使用 left join 左边的表不一定是驱动表

如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面

标准的 group by 语句，是需要在 select 部分加一个聚合函数，比如select a,count(*) from t group by a order by null;

52、MySQL 有哪些自增ID？各自场景是什么？
表的自增 ID 达到上限之后，在申请值不会变化，进而导致联系插入数据的时候报主键冲突错误。

row_id 达到上限之后，归 0 在重新递增，如果出现相同的 row_id 后写的数据会覆盖之前的数据。

Xid 只需要不在同一个 binlog 文件出现重复值即可，理论上会出现重复值，但概率极小可忽略不计。

InnoDB 的 max_trx_id 递增值每次 MySQL 重启会保存起来。

Xid 是由 server 层维护的。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。

thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。使用了insert_unique算法

53、Xid 在 MySQL 内部是怎么生成的呢？
MySQL 内部维护了一个全局变量 global_query_id，每次执行语句（包括select语句）的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。

而 global_query_id 是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件，这就保证了，同一个 binlog 文件里，Xid 一定是惟一的

===============其他

1、说一下 MySQL 的锁
MySQL 在 server 层 和 存储引擎层 都运用了大量的锁

MySQL server 层需要讲两种锁，第一种是MDL(metadata lock) 元数据锁，第二种则 Table Lock 表锁。

MDL 又名元数据锁，那么什么是元数据呢，任何描述数据库的内容就是元数据，比如我们的表结构、库结构等都是元数据。那为什么需要 MDL 呢？

主要解决两个问题：事务隔离问题；数据复制问题

InnoDB 有五种表级锁：IS（意向读锁）；IX（意向写锁）；S（读）；X（写）；AUTO-INC

在对表进行select/insert/delete/update语句时候不会加表级锁

IS和IX的作用是为了判断表中是否有已经被加锁的记录

自增主键的保障就是有 AUTO-INC 锁，是语句级别的：为表的某个列添加 AUTO_INCREMENT 属性，之后在插⼊记录时，可以不指定该列的值，系统会⾃动为它赋上单调递增的值。

InnoDB 4 种行级锁

RecordLock：记录锁

GapLock：间隙锁解决幻读；前一次查询不存在的东西在下一次查询出现了，其实就是事务A中的两次查询之间事务B执行插入操作被事务A感知了

Next-KeyLock：锁住某条记录又想阻止其它事务在改记录前面的间隙插入新纪录

InsertIntentionLock：插入意向锁;如果插入到同一行间隙中的多个事务未插入到间隙内的同一位置则无须等待

行锁和表锁的抉择

全表扫描用行级锁

56、为什么 MySQL 会抖一下？
脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

57、为什么删除了表，表文件的大小还是没变？
数据项删除之后 InnoDB 某个页 page A 会被标记为可复用。

delete 命令把整个表的数据删除，结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

经过大量增删改的表，都是可能是存在空洞的。这些空洞也占空间所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

重建表，就可以达到这样的目的。可以使用 alter table A engine=InnoDB 命令来重建表。

58、count(*)实现方式以及各种 count 对比
对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

但是 count * 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(※)，所以建议尽量使用 count(*)。

59、orderby 排序内部原理
MySQL 会为每个线程分配一个内存（sort-buffer）用于排序该内存大小为 sort_buffer_size；

如果排序的数据量小于 sort_buffer_size，排序就会在内存中完成；

内部排序分为两种

全字段排序：到索引树上找到满足条件的主键ID根据主键ID去取出数据放到sort_buffer然后进行快速排序

rowid排序：通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据

如果数据量很大，内存中无法存下这么多，就会使用磁盘临时文件来辅助排序，称为外部排序；

外部排序，MySQL会分为好几份单独的临时文件来存放排序后的数据，一般是磁盘文件中进行归并，然后将这些文件合并成一个大文件；

======================

1、一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

2、mysql和oracle的区别

3、CHAR和VARCHAR的区别？

以下是CHAR和VARCHAR的区别：

• CHAR和VARCHAR类型在存储和检索方面有所不同
• CHAR列长度固定为创建表时声明的长度，长度值范围是1到255

4、

如果一个表有一列定义为TIMESTAMP，将发生什么？

每当行被更改时，时间戳字段将获取当前时间戳。

5、

列设置为AUTO INCREMENT时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

6、

LIKE声明中的％和_是什么意思？

％对应于0个或更多字符，_只是LIKE语句中的一个字符。

7、

什么是SQL注入？如何防止SQL注入攻击？

SQL注入是黑客通过在用户输入的数据中插入恶意SQL代码来攻击数据库系统。为了防止SQL注入，可以使用参数化查询或预编译语句，并对输入数据进行严格的验证和过滤。

举个例子，假设我们有一个用户登录的功能。当用户输入用户名和密码后，我们可以使用参数化查询来构建SQL语句，将用户输入的数据作为参数传递给SQL查询。这样可以防止恶意用户输入恶意的SQL代码，从而避免SQL注入攻击。

8、

 如何备份和恢复MySQL数据库？

可以使用物理备份（如拷贝数据文件）或逻辑备份（如使用mysqldump命令）进行MySQL数据库的备份。恢复时，可以将备份文件还原到目标服务器即可。

举例来说，使用mysqldump命令可以将整个数据库导出为一个SQL文件。如果需要恢复数据库，只需执行导出的SQL文件即可将数据重新加载到数据库中。

9、

 如何备份和恢复MySQL数据库？

可以使用物理备份（如拷贝数据文件）或逻辑备份（如使用mysqldump命令）进行MySQL数据库的备份。恢复时，可以将备份文件还原到目标服务器即可。

举例来说，使用mysqldump命令可以将整个数据库导出为一个SQL文件。如果需要恢复数据库，只需执行导出的SQL文件即可将数据重新加载到数据库中。

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

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

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

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

11、什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量，交由主语言进一步处理

12、大表数据查询，怎么优化（SQL 性能优化梳理）

优化shema、sql语句+索引；

第二加缓存，memcached, redis；

主从复制，读写分离；

垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

13、

超大分页怎么处理？

超大的分页一般从两个方向上来解决.

数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于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了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.

从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至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

14、

慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

开启慢查询日志

配置项：slow_query_log

可以使用show variables like ‘slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件。

设置临界时间

配置项：long_query_time

查看：show VARIABLES like 'long_query_time'，单位秒

设置：set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

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

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

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

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

首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

===========优化

1、如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

2、优化查询过程中的数据访问

访问数据太多导致查询性能下降

确定应用程序是否在检索大量超过需要的数据，可能是太多行或列

确认MySQL服务器是否在分析大量不必要的数据行

避免犯如下SQL语句错误

查询不需要的数据。解决办法：使用limit解决

多表关联返回全部列。解决办法：指定列名

总是返回全部列。解决办法：避免使用SELECT *

重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存

是否在扫描额外的记录。解决办法：

使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：

使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。

改变数据库和表的结构，修改数据表范式

重写SQL语句，让优化器可以以更优的方式执行查询。

3、优化长难的查询语句

一个复杂查询还是多个简单查询

MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多

使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。

切分查询

将一个大的查询分为多个小的相同的查询

一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。

分解关联查询，让缓存的效率更高。

执行单个查询可以减少锁的竞争。

在应用层做关联更容易对数据库进行拆分。

查询效率会有大幅提升。

较少冗余记录的查询。

4、优化特定类型的查询语句

count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)

MyISAM中，没有任何where条件的count(*)非常快。

当有where条件时，MyISAM的count统计不一定比其它引擎快。

可以使用explain查询近似值，用近似值替代count(*)

增加汇总表

使用缓存

5、优化关联查询

确定ON或者USING子句中是否有索引。

确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

6、优化子查询

用关联查询替代

7、优化GROUP BY和DISTINCT

这两种查询据可以使用索引来优化，是最有效的优化方法

关联查询中，使用标识列分组的效率更高

如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。

WITH ROLLUP超级聚合，可以挪到应用程序处理

8、优化LIMIT分页

LIMIT偏移量大的时候，查询效率较低

可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

9、优化UNION查询

UNION ALL的效率高于UNION

10、优化WHERE子句

解题方法

对于此类考题，先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑以上几个方面，数据访问的问题，长难查询句的问题还是一些特定类型优化的问题，逐一回答。

11、数据库结构优化

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

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

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

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

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

增加中间表

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

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

增加冗余字段

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

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

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

12、MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。如何快速定位当前数据库消耗 CPU 最高的 sql 语句？

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

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

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

限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；

读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；

缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；还有就是通过分库分表的方式进行优化，主要有垂直分表和水平分表(MySQL 分库分表方案，总结的非常好！)

分库分表

14、

分库分表后面临的问题

事务支持 分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

跨库join 只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。分库分表方案产品

跨节点的count,order by,group by以及聚合函数问题 这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

数据迁移，容量规划，扩容等问题 来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。

ID问题

一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由. 一些常见的主键生成策略

所以，在 这四种情况下，才是考虑分库分表的时候！不是只要是优化就分库分表。

UUID 使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。Twitter的分布式自增ID算法Snowflake 在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

跨分片的排序分页

般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片，而当排序字段非分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。如图

15、

备份计划，mysqldump以及xtranbackup的实现原理

(1)备份计划

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小，压缩之后更小)。

100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。高逼格企业级MySQL数据库备份方案，原来是这样....

(2)备份恢复时间

物理备份恢复快，逻辑备份恢复慢

这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考

20G的2分钟（mysqldump）

80G的30分钟(mysqldump)

111G的30分钟（mysqldump)

288G的3小时（xtra)

3T的4小时（xtra)

逻辑导入时间一般是备份时间的5倍以上

(3)备份恢复失败如何处理

首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。

(4)mysqldump和xtrabackup实现原理

mysqldump

mysqldump 属于逻辑备份。加入–single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)，之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。如果加上–master-data=1 的话，在刚开始的时候还会加一个数据库的读锁(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(showmaster status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务

Xtrabackup:

xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo 日志并保存下来。最后完成 innodb 的备份后，会做一个 flush engine logs 的操作(老版本在有 bug，在5.6 上不做此操作会丢数据)，确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交

概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事

情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完后解锁。这样就做到了完美的热备。

16、数据表损坏的修复方式有哪些？

使用 myisamchk 来修复，具体步骤：

1）修复前将mysql服务停止。

2）打开命令行方式，然后进入到mysql的/bin目录。

3）执行myisamchk –recover 数据库所在路径/*.MYI

使用repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表 REPAIR TABLE 用于修复被破坏的表。OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）

MySQL 数据库面试题（2021最新版）

=============场景题

1、你们公司对大表怎么处理的？

2、怎么用mysql实现一个分布式锁？

3、mysql有用分库分表吗？怎么做的？举例说明

4、分库分表怎么做路由的，路由策略

5、跨表查询怎么做的？分布式事务怎么解决的

6、全局ID怎么做的？多数据源怎么做的？

7、如果我想从mysql读取百万数据怎么做？

8、