一、MySQL架构

和其它数据库相比，MySQL有点异乎寻常，它的架构可以在多种不同场景中应用并发挥杰出作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的体系使命以及数据的存储提取相别离。这种架构可以依据事务的需求和实践需要选择适宜的存储引擎。

• 连接层：最上层是一些客户端和连接效劳。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。相同在该层上可以完成基于SSL的安全链接。效劳器也会为安全接入的每一个客户端验证它所具有的操作权限。
• 效劳层：第二层效劳层，主要完成大部分的核心效劳功用， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功用也都在这一层完成，包括触发器、存储过程、视图等
• 引擎层：第三层存储引擎层，存储引擎真实的负责了MySQL中数据的存储和提取，效劳器通过API与存储引擎进行通讯。不同的存储引擎具有的功用不同，这样我们可以依据自己的实践需要进行选取
• 存储层：第四层为数据存储层，主要是将数据存储在运转于该设备的文件体系之上，并完成与存储引擎的交互

?

画出 MySQL 架构图，这种失常问题都能问的出来

MySQL 的查询流程详细是？or 一条SQL语句在MySQL中怎么执行的？

客户端请求 ---> 连接器（验证用户身份，给予权限） ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作） ---> 优化器（主要对执行的sql优化选择最优的执行方案方法） ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回（假如开启查询缓存则会缓存查询成果）图：极客时间

---

?

说说MySQL有哪些存储引擎？都有哪些差异？

二、存储引擎

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水对等功用，使用不同的存储引擎，还可以取得特定的功用。

使用哪种引擎可以活络选择，一个数据库中多个表能够使用不同引擎以满足各种性能和实践需求，使用适宜的存储引擎，将会提高整个数据库的性能 。

MySQL效劳器使用可插拔的存储引擎体系结构，可以从运转中的 MySQL 效劳器加载或卸载存储引擎 。

查看存储引擎

-- 查看支撑的存储引擎SHOW ENGINES-- 查看默许存储引擎SHOW VARIABLES LIKE 'storage_engine'--查看详细某一个表所使用的存储引擎，这个默许存储引擎被修正了！show create table tablename--精确查看某个数据库中的某一表所使用的存储引擎show table status like 'tablename'show table status from database where name="tablename"

设置存储引擎

-- 建表时指定存储引擎。默许的就是INNODB，不需要设置CREATE TABLE t1 (i INT) ENGINE = INNODB;CREATE TABLE t2 (i INT) ENGINE = CSV;CREATE TABLE t3 (i INT) ENGINE = MEMORY;-- 修正存储引擎ALTER TABLE t ENGINE = InnoDB;-- 修正默许存储引擎，也能够在配置文件my.cnf中修正默许引擎SET default_storage_engine=NDBCLUSTER;

默许状况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默许存储引擎时，都会生成一个正告。为了防止在所需的引擎不可用时呈现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。假如所需的引擎不可用，则此设置将发生过错而不是正告，并且不会创建或更改表

存储引擎比照

常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。

InnoDB 现在是 MySQL 默许的存储引擎，支撑事务、行级锁定和外键

文件存储结构比照

在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，.frm 文件是用来保存每一个数据表的元数据(meta)信息，包括表结构的界说等，与数据库存储引擎无关，也就是任何存储引擎的数据表都有必要有.frm文件，命名方式为 数据表名.frm，如user.frm。

查看MySQL 数据保存在哪里：show variables like 'data%'

MyISAM 物理文件结构为：

• .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的界说信息等
• .MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据
• .MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

InnoDB 物理文件结构为：

• .frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的界说信息等
• .ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式可以通过配置来抉择是使用同享表空间存放存储数据，仍是用独享表空间存放存储数据。独享表空间存储方式使用.ibd文件，并且每一个表一个.ibd文件 同享表空间存储方式使用.ibdata文件，所有表一同使用一个.ibdata文件（或多个，可自己配置）

?

ps：正派公司，这些都有专业运维去做，数据备份、恢复啥的，让我一个 Javaer 搞这的话，加钱不？

面试这么答复

1. InnoDB 支撑事务，MyISAM 不支撑事务。这是 MySQL 将默许存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
2. InnoDB 支撑外键，而 MyISAM 不支撑。对一个包括外键的 InnoDB 表转为 MYISAM 会失败；
3. InnoDB 是聚簇索引，MyISAM 对错聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 有必要要有主键，通过主键索引功率很高。可是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不该该过大，因为主键太大，其他索引也都会很大。而 MyISAM 对错集合索引，数据文件是别离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. InnoDB 不保存表的详细行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被堵塞，因此并发拜访受限。这也是 MySQL 将默许存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

比照项MyISAMInnoDB主外键不支撑支撑事务不支撑支撑行表锁表锁，即便操作一条记载也会锁住整个表，不合适高并发的操作行锁,操作时只锁某一行，不对其它行有影响，合适高并发的操作缓存只缓存索引，不缓存真实数据不只缓存索引还要缓存真实数据，对内存要求较高，并且内存巨细对性能有抉择性的影响表空间小大重视点性能事务默许装置是是

?

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

假如表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记载到数据文件中，重启MySQL自增主键的最大ID也不会丢掉；

假如表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记载到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢掉。

?

哪一个存储引擎执行 select count(*) 更快，为何?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

• 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
• 在 InnoDB 存储引擎中，跟 MyISAM 不一样，未将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，终究返回总数量。

InnoDB 中 count(*) 语句是在执行的时分，全表扫描统计总数量，所以当数据愈来愈大时，语句就愈来愈耗时了，为何 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，因为多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

三、数据类型

主要包括以下五大类：

• 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
• 浮点数类型：FLOAT、DOUBLE、DECIMAL
• 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
• 日期类型：Date、DateTime、TimeStamp、Time、Year
• 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

?

CHAR 和 VARCHAR 的差异？

char是固定长度，varchar长度可变：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，其实不代表字节个数，比如 CHAR(30) 就能够存储 30 个字符。

存储时，前者不管实践存储数据的长度，直接按 char 规则的长度分配存储空间；然后者会依据实践存储的数据分配最终的存储空间

相同点：

1. char(n)，varchar(n)中的n都代表字符的个数
2. 超过char，varchar最大长度n的限制后，字符串会被截断。

不同点：

1. char不论实践存储的字符数都会占用n个字符的空间，而varchar只会占用实践字符应该占用的字节空间加1（实践长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记载长度（假如列声明长度大于255则使用两个字节来保存长度）。
2. 能存储的最大空间限制不一样：char的存储上限为255字节。
3. char在存储时会截断尾部的空格，而varchar不会。

char是合适存储很短的、一般固定长度的字符串。例如，char十分合适存储密码的MD5值，因为这是一个定长的值。关于十分短的列，char比varchar在存储空间上也更有用率。

?

列的字符串类型可所以什么？

字符串类型是：SET、BLOB、ENUM、CHAR、CHAR、TEXT、VARCHAR

?

BLOB和TEXT有什么差异？

BLOB是一个二进制对象，可以容纳可变数量的数据。有四品种型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB

TEXT是一个不区分巨细写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

BLOB 保存二进制数据，TEXT 保存字符数据。

---

四、索引

?

说说你对 MySQL 索引的了解？

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

集合索引与非集合索引的差异？

InnoDB引擎中的索引策略，了解过吗？

创建索引的方式有哪些？

聚簇索引/非聚簇索引，mysql索引底层完成，为何不用B-tree，为何不用hash，叶子结点存放的是数据仍是指向数据的内存地点，使用索引需要留意的几个当地？

• MYSQL官方对索引的界说为：索引（Index）是协助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构
• 索引的意图在于提高查询功率，可以类比字典、 火车站的车次表、图书的目录等 。
• 可以简略的了解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就能够在这些数据结构上完成高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。左面的数据表，一共有两列七条记载，最左面的是数据记载的物理地点。为了加速Col2的查找，可以维护一个右边所示的二叉查找树，每一个节点分别包括索引键值，和一个指向对应数据记载物理地点的指针，这样就能够运用二叉查找在一定的杂乱度内获取到对应的数据，从而快速检索出契合条件的记载。
• 索引本身也很大，不可能悉数存储在内存中，一般以索引文件的形式存储在磁盘上
• 往常说的索引，没有特别指明的话，就是B+树（多路查找树，不一定是二叉树）结构组织的索引。其间集合索引，非必须索引，掩盖索引，契合索引，前缀索引，仅有索引默许都是使用B+树索引，统称索引。此外还有哈希索引等。

根本语法：

• 创建：
• 创建索引：CREATE [UNIQUE] INDEX indexName ON mytable(username(length));假如是CHAR，VARCHAR类型，length可以小于字段实践长度；假如是BLOB和TEXT类型，有必要指定 length。
• 修正表结构(添加索引)：ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)
• 删除：DROP INDEX [indexName] ON mytable;
• 查看：SHOW INDEX FROM table_name\G --可以通过添加 \G 来格局化输出信息。
• 使用ALERT命令
• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值有必要是仅有的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list 这条语句创建索引的值有必要是仅有的（除了NULL外，NULL可能会呈现屡次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引，索引值可呈现屡次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)该语句指定了索引为 FULLTEXT ，用于全文索引。

优势

• 提高数据检索功率，下降数据库IO本钱
• 下降数据排序的本钱，下降CPU的耗费

劣势

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记载，所以也需要占用内存
• 虽然索引大大提高了查询速度，同时却会下降更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不只要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

MySQL索引分类

数据结构角度

• B+树索引
• Hash索引
• Full-Text全文索引
• R-Tree索引

从物理存储角度

• 集合索引（clustered index）
• 非集合索引（non-clustered index），也叫辅助索引（secondary index）集合索引和非集合索引都是B+树结构

从逻辑角度

• 主键索引：主键索引是一种特殊的仅有索引，不允许有空值
• 普通索引或者单列索引：每一个索引只包括单个列，一个表可以有多个单列索引
• 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
• 仅有索引或者非仅有索引
• 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL要害字进行扩展，使得可以用于创建正规索引类型的语法创建空间索引。创建空间索引的列，有必要将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

?

为何MySQL 索引顶用B+tree，不用B-tree 或者其他树，为何不用 Hash 索引

聚簇索引/非聚簇索引，MySQL 索引底层完成，叶子结点存放的是数据仍是指向数据的内存地点，使用索引需要留意的几个当地？

使用索引查询一定能提高查询的性能吗？为何?

MySQL索引结构

首要要理解索引（index）是在存储引擎（storage engine）层面完成的，而不是server层面。不是所有的存储引擎都支撑所有的索引类型。即便多个存储引擎支撑某一索引类型，它们的完成和行为也可能有所不同。

B+Tree索引

MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一同，构成了一条数据链表，以加速相邻数据的检索功率。

先了解下 B-Tree 和 B+Tree 的差异

B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

体系从磁盘读取数据到内存时是以磁盘块（block）为根本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默许每一个页的巨细为16KB，可通过参数 innodb_page_size 将页的巨细设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的巨细：show variables like 'innodb_page_size';

而体系一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次请求磁盘空间时都会是若干地点接连磁盘块来达到页的巨细 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为根本单位，在查询数据时假如一个页中的每条数据都能有助于定位数据记载的方位，这将会减少磁盘I/O次数，提高查询功率。

B-Tree 结构的数据能够让体系高效的找到数据地点的磁盘块。为了描述 B-Tree，首要界说一条记载为一个二元组[key, data] ，key为记载的键值，对应表中的主键值，data 为一行记载中除主键外的数据。关于不同的记载，key值互不相同。

一棵m阶的B-Tree有如下特性：

1. 每一个节点最多有m个孩子
2. 除了根节点和叶子节点外，其它每一个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包括其它要害字信息
5. 每一个非终端节点包括n个要害字信息（P0,P1,…Pn, k1,…kn）
6. 要害字的个数n满足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为要害字，且要害字升序排序
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点要害字均小于ki，但都大于k(i-1)

B-Tree 中的每一个节点依据实践状况可以包括很多的要害字信息和分支，如下图所示为一个 3 阶的 B-Tree：

图片：DobbinSoong

每一个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的要害字和三个指向子树根节点的指针，指针存储的是子节点地点磁盘块的地点。两个要害词划分红的三个规模域对应三个指针指向的子树的数据的规模域。以根节点为例，要害字为17和35，P1指针指向的子树的数据规模为小于17，P2指针指向的子树的数据规模为17~35，P3指针指向的子树的数据规模为大于35。

模仿查找要害字29个过程：

1. 依据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较要害字29在区间（17,35），找到磁盘块1的指针P2。
3. 依据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较要害字29在区间（26,30），找到磁盘块3的指针P2。
5. 依据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的要害字列表中找到要害字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。因为内存中的要害字是一个有序表结构，可以使用二分法查找提高功率。而3次磁盘I/O操作是影响整个B-Tree查找功率的抉择因素。B-Tree相对AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询功率。

B+Tree

B+Tree 是在 B-Tree 基础上的一种优化，使其更合适完成外存储索引结构，InnoDB 存储引擎就是用 B+Tree 完成其索引结构。

从上一节中的B-Tree结构图中可以看到每一个节点中不只包括数据的key值，还有data值。而每个页的存储空间是有限的，假如data数据较大时将会导致每一个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时相同会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询功率。在B+Tree中，所稀有据记载节点都是依照键值巨细顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每一个节点存储的key值数量，下降B+Tree的高度。

B+Tree相对B-Tree有几点不同：

1. 非叶子节点只存储键值信息；
2. 所有叶子节点之间都有一个链指针；
3. 数据记载都存放在叶子节点中

将上一节中的B-Tree优化，因为B+Tree的非叶子节点只存储键值信息，假设每一个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另外一个指向要害字最小的叶子节点，并且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是关于主键的规模查找和分页查找，另外一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记载，看不出B+Tree的利益，下面做一个推算：

InnoDB存储引擎中页的巨细为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也通常是4或8个字节，也就是说一个页（B+Tree中的一个节点）中大约存储16KB/(8B+8B)=1K个键值（因为是估值，为便利核算，这里的K取值为10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记载。

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

B+Tree性质

1. 通过上面的分析，我们知道IO次数取决于b+数的高度h，假设其时数据表的数据为N，每一个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的状况下，m越大，h越小；而m = 磁盘块的巨细 / 数据项的巨细，磁盘块的巨细也就是一个数据页的巨细，是固定的，假如数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为何每一个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为何b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2. 当b+树的数据项是复合的数据结构，比如(name,age,sex)的时分，b+数是依照从左到右的顺序来建立查找树的，比如当(张三,20,F)这样的数据来检索的时分，b+树会优先比较name来确定下一步的所搜方向，假如name相同再顺次比较age和sex，终究得到检索的数据；但当(20,F)这样的没有name的数据来的时分，b+树就不知道下一步该查哪一个节点，因为建立查找树的时分name就是第一个比较因子，有必要要先依据name来查找才干知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定查找方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个对错常重要的性质，即索引的最左匹配特性。

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是别离的。MyISAM引擎索引结构的叶子节点的数据域，存放的其实不是实践的数据记载，而是数据记载的地点。索引文件与数据文件别离，这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引差异其实不大，只是主键索引不能有重复的要害字。

在MyISAM中，索引（含叶子节点）存放在单独的.myi文件中，叶子节点存放的是数据的物理地点偏移量（通过偏移量拜访就是随机拜访，速度很快）。

主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了详细的数据。辅助索引类似。

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域，存放的就是实践的数据记载（关于主索引，此处会存放表中所有的数据记载；关于辅助索引此处会引用主键，检索的时分通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”，一个表只能有一个聚簇索引。

主键索引：

我们知道InnoDB索引是集合索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的id、stu_id、name数据项。

在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序摆放的，在数据段中。Innodb的数据文件可以依照表来切分（只需要开启innodb_file_per_table)，切分后存放在xxx.ibd中，默许不切分，存放在xxx.ibdata中。

辅助（非主键）索引：

这次我们以示例中学生表中的name列建立辅助索引，它的索引结构跟主键索引的结构有很大不同，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，依照ASCII码进行排序，第二行的整数是主键的值。

这就意味着，对name列进行条件查找，需要两个步骤：

① 在辅助索引上检索name，抵达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

InnoDB 索引结构需要留意的点

1. 数据文件本身就是索引文件
2. 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
3. 集合索引中叶节点包括了完好的数据记载
4. InnoDB 表有必要要有主键，并且引荐使用整型自增主键

正如我们上面介绍 InnoDB 存储结构，索引与数据是一同存储的，不论是主键索引仍是辅助索引，在查找时都是通过先查找到索引节点才干拿到相对应的数据，假如我们在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引，假如没有契合的列，则 MySQL 主动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。

?

那为何引荐使用整型自增主键而不是选择UUID？

• UUID是字符串，比整型耗费更多的存储空间；
• 在B+树中进行查找时需要跟通过的节点值比较巨细，整型数据的比较运算比字符串更快速；
• 自增的整型索引在磁盘中会接连存储，在读取一页数据时也是接连；UUID是随机发生的，读取的上下两行数据存储是涣散的，不合适执行where id > 5 && id < 20的条件查询语句。
• 在刺进或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左边子树的结构；UUID主键很容易呈现这样的状况，B+树为了维持本身的特性，有可能会进行结构的重构，耗费更多的时间。

?

为何非主键索引结构叶子节点存储的是主键值？

保证数据一致性和节省存储空间，可以这么了解：商城体系订单表会存储一个用户ID作为关联外键，而不引荐存储完好的用户信息，因为当我们用户表中的信息（真实名称、手机号、收货地点···）修正后，不需要再次维护订单表的用户数据，同时也节省了存储空间。

Hash索引

• 主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应方位；假如发生Hash碰撞（两个不同要害字的Hash值相同），则在对应Hash键下以链表形式存储。检索算法：在检索查询时，就再次对待查要害字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应方位取出数据即可，假如发生Hash碰撞，则需要在取值时进行筛选。现在使用Hash索引的数据库其实不多，主要有Memory等。MySQL现在有Memory引擎和NDB引擎支撑Hash索引。

full-text全文索引

• 全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支撑。
• 它用于代替功率较低的LIKE模糊匹配操作，并且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
• 相同使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据切割后再进行索引（一般每4个字节一次切割），索引文件存储的是切割前的索引字符串集合，与切割后的索引信息，对应Btree结构的节点存储的是切割后的词信息以及它在切割前的索引字符串集合中的方位。

R-Tree空间索引

空间索引是MyISAM的一种特殊索引类型，主要用于地舆空间数据类型

?

为何Mysql索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响，B树的每一个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的状况下，B树的高度更高，IO更频频。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引悉数加载到内存了，只能逐一加载每个磁盘页（对应索引树的节点）。其间在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。

?

面试官：为什么不选用Hash方式？

因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，关于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以关于规模查询的时分不需要做全表扫描。

哈希索引不支撑多列联合索引的最左匹配规则，假如有很多重复键值得状况下，哈希索引的功率会很低，因为存在哈希碰撞问题。

哪些状况需要创建索引

1. 主键主动建立仅有索引
2. 频频作为查询条件的字段
3. 查询中与其他表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题，高并发下倾向创建组合索引
5. 查询中排序的字段，排序字段通过索引拜访大幅提高排序速度
6. 查询中统计或分组字段

哪些状况不要创建索引

1. 表记载太少
2. 常常增删改的表
3. 数据重复且散布均匀的表字段，只应该为最常常查询和最常常排序的数据列建立索引（假如某个数据类包括太多的重复数据，建立索引没有太粗心义）
4. 频频更新的字段不合适创建索引（会加剧IO担负）
5. where条件里用不到的字段不创建索引

MySQL高效索引

掩盖索引（Covering Index）,或者叫索引掩盖， 也就是平时所说的不需要回表操作

• 就是select的数据列只用从索引中就可以够取得，没必要读取数据行，MySQL可以使用索引返回select列表中的字段，而没必要依据索引再次读取数据文件，换句话说查询列要被所建的索引掩盖。
• 索引是高效找到行的一个方法，可是一般数据库也能使用索引找到一个列的数据，因此它没必要读取整个行。毕竟索引叶子节点存储了它们索引的数据，当能通过读取索引就能够得到想要的数据，那就不需要读取行了。一个索引包括（掩盖）满足查询成果的数据就叫做掩盖索引。
• 判断规范使用explain，可以通过输出的extra列来判断，关于一个索引掩盖查询，显示为using index，MySQL查询优化器在执行查询前会抉择是否有索引掩盖查询

五、MySQL查询

?

count(*) 和 count(1)和count(列名)差异 ps：这道题说法有点多

执行效果上：

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

执行功率上：

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

?

MySQL中 in和 exists 的差异？

• exists：exists对表面用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句可以返回记载行时（无论记载行是的多少，只需能返回），条件就为真，返回其时loop到的这条记载；反之，假如exists里的条件语句不能返回记载行，则其时loop到的这条记载被丢弃，exists的条件就像一个bool条件，当能返回成果集则为true，不能返回成果集则为false
• in：in查询适当于多个or条件的叠加

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

假如查询的两个表巨细适当，那么用in和exists不同不大。

假如两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

?

UNION和UNION ALL的差异?

UNION和UNION ALL都是将两个成果集合并为一个，两个要联合的SQL语句 字段个数有必要一样，并且字段类型要“相容”（一致）；

• UNION在进行表连接后会筛选掉重复的数据记载（功率较低），而UNION ALL则不会去掉重复的数据记载；
• UNION会依照字段的顺序进行排序，而UNION ALL只是简略的将两个成果合并就返回；

SQL执行顺序

• 手写SELECT DISTINCT <select_list>
  FROM <left_table> <join_type>
  JOIN <right_table> ON <join_condition>
  WHERE <where_condition>
  GROUP BY <group_by_list>
  HAVING <having_condition>
  ORDER BY <order_by_condition>
  LIMIT <limit_number>
• 机读FROM <left_table>
  ON <join_condition>
  <join_type> JOIN <right_table>
  WHERE <where_condition>
  GROUP BY <group_by_list>
  HAVING <having_condition>
  SELECT
  DISTINCT <select_list>
  ORDER BY <order_by_condition>
  LIMIT <limit_number>
• 总结

?

mysql 的内连接、左连接、右连接有什么差异？

什么是内连接、外连接、交叉连接、笛卡尔积呢？

Join图

---

六、MySQL 事务

?

事务的阻隔级别有哪些？MySQL的默许阻隔级别是什么？

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

MySQL事务的四大特性以及完成原理

MVCC熟悉吗，它的底层原理？

MySQL 事务主要用于处理操作量大，杂乱度高的数据。比如说，在人员管理体系中，你删除一个人员，你即需要删除人员的根本资料，也要删除和该人员相关的信息，如Email，文章等等，这样，这些数据库操作语句就构成一个事务！

ACID — 事务根本要素

事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。

• A (Atomicity) 原子性：整个事务中的所有操作，要么悉数完成，要么悉数不完成，不可能停滞在中心某个环节。事务在执行过程当中发生过错，会被回滚（Rollback）到事务开始前的状态，就像这个事务历来没有执行过一样
• C (Consistency) 一致性：在事务开始之前和事务完毕今后，数据库的完好性约束没有被破坏
• I (Isolation)阻隔性：一个事务的执行不能其它事务搅扰。即一个事务内部的操作及使用的数据对其它并发事务是阻隔的，并发执行的各个事务之间不能相互搅扰
• D (Durability) 耐久性：在事务完成今后，该事务所对数据库所作的更改便耐久的保存在数据库之中，其实不会被回滚

并发事务处理带来的问题

• 更新丢掉（Lost Update)：事务A和事务B选择同一行，然后基于最初选定的值更新该行时，因为两个事务都不知道彼此的存在，就会发生丢掉更新问题
• 脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
• 不可重复读（Non-Repeatable Reads)：事务 A 屡次读取同一数据，事务B在事务A屡次读取的过程当中，对数据作了更新并提交，导致事务A屡次读取同一数据时，成果不一致。
• 幻读（Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另外一个并发事务B刺进了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记载，就好像发生了幻觉一样，所以称为幻读。

幻读和不可重复读的差异：

• 不可重复读的重点是修正：在同一事务中，相同的条件，第一次读的数据和第二次读的数据不一样。（因为中心有其他事务提交了修正）
• 幻读的重点在于新增或者删除：在同一事务中，相同的条件,，第一次和第二次读出来的记载数不一样。（因为中心有其他事务提交了刺进/删除）

并发事务处理带来的问题的解决方法：

• “更新丢掉”通常是应该完全防止的。但防止更新丢掉，其实不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢掉应该是应用的职责。
• “脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，有必要由数据库提供一定的事务阻隔机制来解决：
• 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修正。
• 另外一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

事务阻隔级别

数据库事务的阻隔级别有4种，由低到高分别为

• READ-UNCOMMITTED(读未提交)： 最低的阻隔级别，允许读取还没有提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读已提交)： 允许读取并发事务现已提交的数据，可以阻止脏读，可是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的屡次读取成果都是一致的，除非数据是被本身事务自己所修正，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的阻隔级别，完全遵守ACID的阻隔级别。所有的事务顺次逐个执行，这样事务之间就完全不可能发生搅扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

查看其时数据库的事务阻隔级别：

show variables like 'tx_isolation'

下面通过事例逐个论述在事务的并发操作中可能会呈现脏读，不可重复读，幻读和事务阻隔级其他联络。

数据库的事务阻隔越严厉，并发反作用越小，但支付的价值就越大，因为事务阻隔实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务阻隔程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”其实不敏感，可能更关怀数据并发拜访的能力。

Read uncommitted

读未提交，就是一个事务可以读取另外一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。可是发工资时老板不当心按错了数字，按成3.9万/月，该钱现已打到程序员的户口，可是事务还没有提交，就在这时候，程序员去查看自己这个月的工资，发现比平常多了3千元，认为涨工资了十分快乐。可是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实践程序员这个月的工资仍是3.6万，可是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

Read committed

读提交，望文生义，就是一个事务要等另外一个事务提交后才干读取数据。

事例：程序员拿着信用卡去享用日子（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费体系事前检测到他的卡里有3.6万，就在这个时分！！程序员的妻子要把钱悉数转出充当家用，并提交。当收费体系准备扣款时，再检测卡里的金额，发现现已没钱了（第二次检测金额当然要等候妻子转出金额事务提交完）。程序员就会很抑郁，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等候这个更新操作事务提交后才干读取数据，可以解决脏读问题。但在这个事例中，呈现了一个事务规模内两个相同的查询却返回了不同数据，这就是不可重复读。

那怎么解决可能的不可重复读问题？Repeatable read ！

Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修正操作。MySQL的默许事务阻隔级别

事例：程序员拿着信用卡去享用日子（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修正操作），收费体系事前检测到他的卡里有3.6万。这个时分他的妻子不能转出金额了。接下来收费体系就能够扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该理解的一点就是，不可重复读对应的是修正，即UPDATE操作。可是可能还会有幻读问题。因为幻读问题对应的是刺进INSERT操作，而不是UPDATE操作。

什么时分会呈现幻读？

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记载（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时分，程序员花了1万买了一部电脑，即新增INSERT了一条消费记载，并提交。当妻子打印程序员的消费记载清单时（妻子事务提交），发现花了1.2万元，似乎呈现了幻觉，这就是幻读。

那怎么解决幻读问题？Serializable！

Serializable 序列化

Serializable 是最高的事务阻隔级别，在该级别下，事务串行化顺序执行，可以防止脏读、不可重复读与幻读。简略来说，Serializable会在读取的每一行数据上都加锁，所以可能导致很多的超时和锁争用问题。这种事务阻隔级别功率低下，比较耗数据库性能，一般不使用。

比较

事务阻隔级别读数据一致性脏读不可重复读幻读读未提交（read-uncommitted）最初级被，只能保证不读取物理上损坏的数据是是是读已提交（read-committed）语句级否是是可重复读（repeatable-read）事务级否否是串行化（serializable）第一流别，事务级否否否

需要说明的是，事务阻隔级别和数据拜访的并发性是对立的，事务阻隔级别越高并发性就越差。所以要依据详细的应用来确定适宜的事务阻隔级别，这个当地没有全能的原则。

MySQL InnoDB 存储引擎的默许支撑的阻隔级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

这里需要留意的是：与 SQL 规范不同的当地在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务阻隔级别下使用的是Next-Key Lock 算法，因此可以防止幻读的发生，这与其他数据库体系(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默许支撑的阻隔级别是 REPEATABLE-READ（可重读）现已可以完全保证事务的阻隔性要求，即达到了 SQL规范的 SERIALIZABLE(可串行化)阻隔级别，并且保留了比较好的并发性能。

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

MVCC 多版本并发控制

MySQL的大大都事务型存储引擎完成都不是简略的行级锁。基于提高并发性考虑，一般都同时完成了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是完成机制各不相同。

可以认为 MVCC 是行级锁的一个变种，但它在很多状况下防止了加锁操作，因此开支更低。虽然完成机制有所不同，但大都完成了非堵塞的读操作，写操作也只是锁定必要的行。

MVCC 的完成是通过保存数据在某个时间点的快照来完成的。也就是说不管需要执行多长时间，每一个事物看到的数据都是一致的。

典型的MVCC完成方式，分为乐观（optimistic）并发控制和失望（pressimistic）并发控制。下边通过 InnoDB的简化版行为来说明 MVCC 是怎么工作的。

InnoDB 的 MVCC，是通过在每行记载后边保存两个隐藏的列来完成。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的其实不是真实的时间，而是体系版本号（system version number）。每开始一个新的事务，体系版本号都会主动递增。事务开始时刻的体系版本号会作为事务的版本号，用来和查询到的每行记载的版本号进行比较。

REPEATABLE READ（可重读）阻隔级别下MVCC怎么工作：

• SELECTInnoDB会依据以下两个条件查看每行记载：只有契合上述两个条件的才会被查询出来
• InnoDB只查找版本早于其时事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前现已存在要么是事务本身刺进或者修正过的
• 行的删除版本号要么不决义，要么大于其时事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除
• INSERT：InnoDB为新刺进的每一行保存其时体系版本号作为行版本号
• DELETE：InnoDB为删除的每一行保存其时体系版本号作为行删除标识
• UPDATE：InnoDB为刺进的一行新纪录保存其时体系版本号作为行版本号，同时保存其时体系版本号到本来的行作为删除标识

保存这两个额定体系版本号，使大大都操作都不用加锁。使数据操作简略，性能很好，并且也能保证只会读取到契合要求的行。不足的地方是每行记载都需要额定的存储空间，需要做更多的行查看工作和一些额定的维护工作。

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种阻隔级别下工作。

事务日志

InnoDB 使用日志来减少提交事务时的开支。因为日志中现已记载了事务，就无须在每一个事务提交时把缓冲池的脏块改写(flush)到磁盘中。

事务修正的数据和索引通常会映射到表空间的随机方位，所以改写这些变更到磁盘需要很多随机 IO。

InnoDB 假设使用常规磁盘，随机IO比顺序IO贵重得多，因为一个IO请求需要时间把磁头移到正确的方位，然后等候磁盘上读出需要的部分，再转到开始方位。

InnoDB 用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就耐久化了，即便断电了，InnoDB可以重放日志并且恢复现已提交的事务。

InnoDB 使用一个后台线程智能地改写这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高功率。

事务日志可以协助提高事务功率：

• 使用事务日志，存储引擎在修正表的数据时只需要修正其内存拷贝，再把该修正行为记载到耐久在硬盘上的事务日志中，而不用每次都将修正的数据本身耐久到磁盘。
• 事务日志选用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个当地移动磁头，所以选用事务日志的方式相对来说要快得多。
• 事务日志耐久今后，内存中被修正的数据在后台可以慢慢刷回到磁盘。
• 假如数据的修正现已记载到事务日志并耐久化，但数据本身没有写回到磁盘，此时体系溃散，存储引擎在重启时可以主动恢复这一部分修正的数据。

现在来说，大大都存储引擎都是这样完成的，我们通常称之为预写式日志（Write-Ahead Logging），修正数据需要写两次磁盘。

事务的完成

事务的完成是基于数据库的存储引擎。不同的存储引擎对事务的支撑程度不一样。MySQL 中支撑事务的存储引擎有 InnoDB 和 NDB。

事务的完成就是怎么完成ACID特性。

事务的阻隔性是通过锁完成，而事务的原子性、一致性和耐久性则是通过事务日志完成 。

?

事务是怎么通过日志来完成的，说得越深化越好。

事务日志包括：重做日志redo和回滚日志undo

• redo log（重做日志） 完成耐久化和原子性在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)完成。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前改写到磁盘上耐久化，这就是DBA们口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢改写到磁盘。此时假如数据库溃散或者宕机，那么当体系重启进行恢复时，就能够依据redo log中记载的日志，把数据库恢复到溃散前的一个状态。未完成的事务，可以继续提交，也能够选择回滚，这基于恢复的策略而定。在体系启动的时分，就现已为redo log分配了一块接连的存储空间，以顺序追加的方式记载Redo Log，通过顺序IO来改善性能。所有的事务同享redo log的存储空间，它们的Redo Log按语句的执行顺序，顺次交替的记载在一同。
• undo log（回滚日志） 完成一致性undo log 主要为事务的回滚效劳。在事务执行的过程当中，除了记载redo log，还会记载一定量的undo log。undo log记载了数据在每一个操作前的状态，假如事务执行过程当中需要回滚，就能够依据undo log进行回滚操作。单个事务的回滚，只会回滚其时事务做的操作，其实不会影响到其他的事务做的操作。Undo记载的是已部分完成并且写入硬盘的未完成的事务，默许状况下回滚日志是记载下表空间中的（同享表空间或者独享表空间）

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修正的页操作，而undo_log是回滚行记载到特定版本。二者记载的内容也不同，redo_log是物理日志，记载页的物理修正操作，而undo_log是逻辑日志，依据每行记载进行记载。

?

又引出个问题：你知道MySQL 有多少种日志吗？

• 过错日志：记载犯错信息，也记载一些正告信息或者正确的信息。
• 查询日志：记载所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
• 慢查询日志：设置一个阈值，将运转时间超过该值的所有SQL语句都记载到慢查询的日志文件中。
• 二进制日志：记载对数据库执行更改的所有操作。
• 中继日志：中继日志也是二进制日志，用来给slave 库恢复
• 事务日志：重做日志redo和回滚日志undo

?

散布式事务相关问题，可能还会问到 2PC、3PC，，，

MySQL对散布式事务的支撑

散布式事务的完成方式有很多，既可以选用 InnoDB 提供的原生的事务支撑，也能够选用音讯行列来完成散布式事务的最终一致性。这里我们主要聊一下 InnoDB 对散布式事务的支撑。

MySQL 从 5.0.3 InnoDB 存储引擎开始支撑XA协议的散布式事务。一个散布式事务会触及多个举动，这些举动本身是事务性的。所有举动都有必要一同成功完成，或者一同被回滚。

在MySQL中，使用散布式事务触及一个或多个资源管理器和一个事务管理器。

如图，MySQL 的散布式事务模型。模型平分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）:

• 应用程序：界说了事务的鸿沟，指定需要做哪些事务；
• 资源管理器：提供了拜访事务的方法，通常一个数据库就是一个资源管理器；
• 事务管理器：协调参加了全局事务中的各个事务。

散布式事务选用两段式提交（two-phase commit）的方式：

• 第一阶段所有的事务节点开始准备，通知事务管理器ready。
• 第二阶段事务管理器通知每一个节点是commit仍是rollback。假如有一个节点失败，就需要全局的节点悉数rollback，以此保障事务的原子性。

---

七、MySQL锁机制

?

数据库的乐观锁和失望锁？

MySQL 中有哪几种锁，罗列一下？

MySQL中InnoDB引擎的行锁是怎么完成的？

MySQL 间隙锁有无了解，死锁有无了解，写一段会形成死锁的 sql 语句，死锁发生了怎么解决，MySQL 有无提供什么机制去解殊死锁

锁是核算机协调多个进程或线程并发拜访某一资源的机制。

在数据库中，除传统的核算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户同享的资源。数据库锁定机制简略来说，就是数据库为了保证数据的一致性，而使各种同享资源在被并发拜访变得有序所设计的一种规则。

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时分假如还有另外一个人买，那么怎么解决是你买到仍是另外一个人买到的问题？这里肯定要用到事物，我们先从库存表中取出物品数量，然后刺进订单，付款后刺进付款表信息，然后更新商品数量。在这个过程当中，使用锁可以对有限的资源进行保护，解决阻隔和并发的矛盾。

锁的分类

从对数据操作的类型分类：

• 读锁（同享锁）：针对同一份数据，多个读操作可以同时进行，不会相互影响
• 写锁（排他锁）：其时写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分类：

为了尽量提高数据库的并发度，每次锁定的数据规模越小越好，理论上每次只锁定其时操作的数据的方案会得到最大的并发度，可是管理锁是很耗资源的事情（触及获取，查看，开释锁等动作），因此数据库体系需要在高并发呼应和体系性能两方面进行平衡，这样就发生了“锁粒度（Lock granularity）”的概念。

• 表级锁：开支小，加锁快；不会呈现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎选用的是表级锁）；
• 行级锁：开支大，加锁慢；会呈现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支撑行级锁也支撑表级锁，但默许状况下是选用行级锁）；
• 页面锁：开支和加锁时间界于表锁和行锁之间；会呈现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更合适于以查询为主，只有少数按索引条件更新数据的应用，如Web应用；而行级锁则更合适于有很多按索引条件并发更新少数不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）体系。

行锁表锁页锁MyISAM

√

BDB

√√InnoDB√√

Memory

√

MyISAM 表锁

MyISAM 的表锁有两种模式：

• 表同享读锁 （Table Read Lock）：不会堵塞其他用户对同一表的读请求，但会堵塞对同一表的写请求；
• 表独占写锁 （Table Write Lock）：会堵塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程取得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。其他线程的读、 写操作都会等候，直到锁被开释为止。

默许状况下，写锁比读锁具有更高的优先级：当一个锁开释时，这个锁会优先给写锁行列中等候的获取锁请求，然后再给读锁行列中等候的获取锁请求。

InnoDB 行锁

InnoDB 完成了以下两品种型的行锁：

• 同享锁（S）：允许一个事务去读一行，阻止其他事务取得相同数据集的排他锁。
• 排他锁（X）：允许取得排他锁的事务更新数据，阻止其他事务取得相同数据集的同享读锁和排他写锁。

为了允许行锁和表锁共存，完成多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向同享锁（IS）：事务计划给数据行加行同享锁，事务在给一个数据行加同享锁前有必要先取得该表的 IS 锁。
• 意向排他锁（IX）：事务计划给数据行加行排他锁，事务在给一个数据行加排他锁前有必要先取得该表的 IX 锁。

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的状况。

加锁机制

乐观锁与失望锁是两种并发控制的思维，可用于解决丢掉更新问题

乐观锁会“乐观地”假定大约率不会发生并发更新冲突，拜访、处理数据过程当中不加锁，只在更新数据时再依据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记载机制完成，这是乐观锁最常用的一种完成方式

失望锁会“失望地”假定大约率会发生并发更新冲突，拜访、处理数据前就加排他锁，在整个数据处理过程当中锁定数据，事务提交或回滚后才开释锁。另外与乐观锁相对应的，失望锁是由数据库自己完成了的，要用的时分，我们直接调用数据库的相关语句就能够了。

锁模式(InnoDB有三种行锁的算法)

• 记载锁(Record Locks)：单个行记载上的锁。对索引项加锁，锁定契合条件的行。其他事务不能修正和删除加锁项；SELECT * FROM table WHERE id = 1 FOR UPDATE;它会在 id=1 的记载上加上记载锁，以阻止其他事务刺进，更新，删除 id=1 这一行在通过 主键索引 与 仅有索引 对数据行进行 UPDATE 操作时，也会对该行数据加记载锁：-- id 列为主键列或仅有索引列
  UPDATE SET age = 50 WHERE id = 1;
• 间隙锁（Gap Locks）：当我们使用规模条件而不是相等条件检索数据，并请求同享或排他锁时，InnoDB会给契合条件的已稀有据记载的索引项加锁。关于键值在条件规模内但其实不存在的记载，叫做“间隙”。InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。对索引项之间的“间隙”加锁，锁定记载的规模（对第一条记载前的间隙或终究一条将记载后的间隙加锁），不包括索引项本身。其他事务不能在锁规模内刺进数据，这样就防止了其他事务新增幻影行。间隙锁基于非仅有索引，它锁定一段规模内的索引记载。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必紧记：使用间隙锁锁住的是一个区间，而不只仅是这个区间中的每一条数据。SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;即所有在（1，10）区间内的记载行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的刺进会被堵塞，可是 1 和 10 两条记载行其实不会被锁住。GAP锁的意图，是为了防止同一事务的两次其时读，呈现幻读的状况
• 临键锁(Next-key Locks)：临键锁，是记载锁与间隙锁的组合，它的封锁规模，既包括索引记载，又包括索引区间。(临键锁的主要意图，也是为了防止幻读(Phantom Read)。假如把事务的阻隔级别降级为RC，临键锁则也会失效。)Next-Key 可以了解为一种特殊的间隙锁，也能够了解为一种特殊的算法。通过临建锁可以解决幻读的问题。每一个数据行上的非仅有索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引完成的，临键锁只与非仅有索引列有关，在仅有索引列（包括主键列）上不存在临键锁。关于行的查询，都是选用该方法，主要意图是解决幻读的问题。

?

select for update有什么意义，会锁表仍是锁行仍是其他

for update 仅适用于InnoDB，且有必要在事务块(BEGIN/COMMIT)中才干生效。在进行事务操作时，通过“for update”语句，MySQL会对查询成果集中每行数据都添加排他锁，其他线程对该记载的更新与删除操作都会堵塞。排他锁包括行锁、表锁。

InnoDB这种行锁完成特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，不然，InnoDB将使用表锁！假设有个表单 products ，里边有id跟name二个栏位，id是主键。

• 明确指定主键，并且有此笔资料，row lock

SELECT * FROM products WHERE id='3' FOR UPDATE;SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

• 明确指定主键，若查无此笔资料，无lock

SELECT * FROM products WHERE id='-1' FOR UPDATE;

• 无主键，table lock

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

• 主键不明确，table lock

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

• 主键不明确，table lock

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且有必要在交易区块(BEGIN/COMMIT)中才干生效。注2: 要测试锁定的状况，可以使用MySQL的Command Mode ，开二个视窗来做测试。

?

MySQL 遇到过死锁问题吗，你是怎么解决的？

死锁

死锁发生：

• 死锁是指两个或多个事务在同一资源上彼此占用，并请求锁定对方占用的资源，从而导致恶性循环
• 当事务试图以不同的顺序锁定资源时，就可能发生死锁。多个事务同时锁定同一个资源时也可能会发生死锁
• 锁的行为和顺序和存储引擎相关。以相同的顺序执行语句，有些存储引擎会发生死锁有些不会——死锁有两层原因：真实的数据冲突；存储引擎的完成方式。

检测死锁：数据库体系完成了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并当即返回一个过错。

死锁恢复：死锁发生今后，只有部分或完全回滚其间一个事务，才干打破死锁，InnoDB现在处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时有必要考虑怎么处理死锁，大都状况下只需要从头执行因死锁回滚的事务即可。

外部锁的死锁检测：发存亡锁后，InnoDB 一般都能主动检测到，并使一个事务开释锁并回退，另外一个事务取得锁，继续完成事务。但在触及外部锁，或触及表锁的状况下，InnoDB 其实不能完全主动检测到死锁， 这需要通过设置锁等候超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：死锁会影响性能而不是会发生严峻过错，因为InnoDB会主动检测死锁状况并回滚其间一个受影响的事务。在高并发体系上，当许多线程等候同一个锁时，死锁检测可能导致速度变慢。有时当发存亡锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有用，这时候可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM防止死锁：

• 在主动加锁的状况下，MyISAM 总是一次取得 SQL 语句所需要的悉数锁，所以 MyISAM 表不会呈现死锁。

InnoDB防止死锁：

• 为了在单个InnoDB表上执行多个并发写入操作时防止死锁，可以在事务开始时通过为预期要修正的每一个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即便这些行的更改语句是在之后才执行的。
• 在事务中，假如要更新记载，应该直接请求足够级其他锁，即排他锁，而不该先请求同享锁、更新时再请求排他锁，因为这时候候当用户再请求排他锁时，其他事务可能又现已取得了相同记载的同享锁，从而形成锁冲突，乃至死锁
• 假如事务需要修正或锁定多个表，则应在每一个事务中以相同的顺序使用加锁语句。在应用中，假如不同的程序会并发存取多个表，应尽量约好以相同的顺序来拜访表，这样可以大大下降发生死锁的时机
• 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，假如其时事务再需要对该记载进行更新操作，则很有可能形成死锁。
• 改变事务阻隔级别

假如呈现死锁，可以用 show engine innodb status;命令来确定终究一个死锁发生的原因。返回成果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务现已取得的锁，正在等候什么锁，以及被回滚的事务等。据此可以分析死锁发生的原因和改善措施。

---

八、MySQL调优

?

日常工作中你是怎么优化SQL的？

SQL优化的一般步骤是什么，怎么看执行方案（explain），怎么了解其间各个字段的意义？

怎么写sql可以有用的使用到复合索引？

一条sql执行过长的时间，你怎么优化，从哪些方面下手？

什么是最左前缀原则？什么是最左匹配原则？

影响mysql的性能因素

• 事务需求对MySQL的影响(适宜合度)
• 存储定位对MySQL的影响
• 体系各种配置及规则数据
• 活跃用户的根本信息数据
• 活跃用户的个性化定制信息数据
• 准实时的统计信息数据
• 其他一些拜访频频但变更较少的数据
• 二进制多媒体数据
• 流水行列数据
• 超大文本数据
• 不合适放进MySQL的数据
• 需要放进缓存的数据
• Schema设计对体系的性能影响
• 尽量减少对数据库拜访的请求
• 尽量减少无用数据的查询请求
• 硬件环境对体系性能的影响

性能分析

MySQL Query Optimizer

1. MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功用：通过核算分析体系中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行方案（他认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最消耗时间）
2. 当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，差异出是 SELECT 并转发给 MySQL Query Optimize r时，MySQL Query Optimizer 首要会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或清楚明了的条件、结构调整等。然后分析 Query 中的 Hint 信息（假如有），看显示 Hint 信息是否可以完全确定该 Query 的执行方案。假如没有 Hint 或 Hint 信息还不足以完全确定执行方案，则会读取所触及对象的统计信息，依据 Query 进行写相应的核算分析，然后再得出终究的执行方案。

MySQL常见瓶颈

• CPU：CPU在饱和的时分一般发生在数据装入内存或从磁盘上读取数据时分
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时分
• 效劳器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看体系的性能状态

性能下降SQL慢 执行时间长 等候时间长 原因分析

• 查询语句写的烂
• 索引失效（单值、复合）
• 关联查询太多join（设计缺陷或不得已的需求）
• 效劳器调优及各个参数设置（缓冲、线程数等）

MySQL常见性能分析手法

在优化MySQL时，通常需要对数据库进行分析，常见的分析手法有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询体系状态及体系变量，通过定位分析性能的瓶颈，才干更好的优化数据库体系的性能。

性能瓶颈定位

我们可以通过 show 命令查看 MySQL 状态及变量，找到体系的瓶颈：

Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）Mysql> show variables ——显示体系变量（扩展show variables like ‘XXX’）Mysql> show innodb status ——显示InnoDB存储引擎的状态Mysql> show processlist ——查看其时SQL执行，包括执行状态、是否锁表等Shell> mysqladmin variables -u username -p password——显示体系变量Shell> mysqladmin extended-status -u username -p password——显示状态信息

Explain(执行方案)

是什么：使用 Explain 要害字可以模仿优化器执行SQL查询语句，从而知道 MySQL 是怎么处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

精干吗：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引能够使用
• 哪些索引被实践使用
• 表之间的引用
• 每张表有多少行被优化器查询

怎么玩：

• Explain + SQL语句
• 执行方案包括的信息（假如有分区表的话还会有partitions）

expalin

各字段解释

• id（select 查询的序列号，包括一组数字，表明查询中执行select子句或操作表的顺序）
• id相同，执行顺序从上往下
• id全不同，假如是子查询，id的序号会递增，id值越大优先级越高，越先被执行
• id部分相同，执行顺序是先依照数字大的先执行，然后数字相同的依照从上往下的顺序执行
• select_type（查询类型，用于差异普通查询、联合查询、子查询等杂乱查询）
• SIMPLE ：简略的select查询，查询中不包括子查询或UNION
• PRIMARY：查询中若包括任何杂乱的子部分，最外层查询被标记为PRIMARY
• SUBQUERY：在select或where列表中包括了子查询
• DERIVED：在from列表中包括的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把成果放在暂时表里
• UNION：若第二个select呈现在UNION之后，则被标记为UNION，若UNION包括在from子句的子查询中，外层select将被标记为DERIVED
• UNION RESULT：从UNION表获取成果的select
• table（显示这一行的数据是关于哪张表的）
• type（显示查询使用了那品种型，从最好到最差顺次摆放 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）tip: 一般来说，得保证查询至少达到range级别，最好抵达ref
• system：表只有一行记载（等于体系表），是 const 类型的特例，平时不会呈现
• const：表明通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只需匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就可以将该查询转换为一个常量
• eq_ref：仅有性索引扫描，关于每一个索引键，表中只有一条记载与之匹配，常见于主键或仅有索引扫描
• ref：非仅有性索引扫描，规模匹配某个单独值得所有行。本质上也是一种索引拜访，他返回所有匹配某个单独值的行，然而，它可能也会找到多个契合条件的行，多以他应该属于查找和扫描的混合体
• range：只检索给定规模的行，使用一个索引来选择行。key列显示使用了哪一个索引，一般就是在你的where语句中呈现了between、<、>、in等的
  <<上一篇：网站建设明细报价表:湖南网页建站公司有哪些比较好的外包公司?

  下一篇：傻瓜式创建网站:千万粉丝的翔翔高文战，被MCN机构捅了一刀，账号 >>

  相关内容

  • 【创建官网需要多少钱】怎么自己建
  • 自己建立网站:详解涨粉案例，解剖1