关于数据库:MySQL-三万字精华总结-面试100-问和面试官扯皮绰绰有余

MySQL 三万字精髓总结 + 面试 100 问，和面试官扯皮入不敷出

写在之前：不倡议那种上来就是各种面试题列举，而后背书式的去记忆，对技术的晋升帮忙很小，对正经面试也没什么帮忙，有点货色的面试官深挖下就懵逼了。

集体倡议把面试题看作是费曼学习法中的回顾、简化的环节，筹备面试的时候，跟着题目先本人讲给本人听，看看本人会称心吗，不称心就持续学习这个点，如此重复，好的 offer 离你不远的，奥利给

一、MySQL 架构

和其它数据库相比，MySQL 有点不同凡响，它的架构能够在多种不同场景中利用并施展良好作用。次要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的零碎工作以及数据的存储提取相拆散。这种架构能够依据业务的需要和理论须要抉择适合的存储引擎。

• 连贯层 ：最上层是一些客户端和连贯服务。 次要实现一些相似于连贯解决、受权认证、及相干的平安计划。在该层上引入了线程池的概念，为通过认证平安接入的客户端提供线程。同样在该层上能够实现基于 SSL 的平安链接。服务器也会为平安接入的每个客户端验证它所具备的操作权限。
• 服务层：第二层服务层，次要实现大部分的外围服务性能，包含查问解析、剖析、优化、缓存、以及所有的内置函数，所有跨存储引擎的性能也都在这一层实现，包含触发器、存储过程、视图等
• 引擎层：第三层存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具备的性能不同，这样咱们能够依据本人的理论须要进行选取
• 存储层：第四层为数据存储层，次要是将数据存储在运行于该设施的文件系统之上，并实现与存储引擎的交互

画出 MySQL 架构图，这种变态问题都能问的进去

MySQL 的查问流程具体是？or 一条 SQL 语句在 MySQL 中如何执行的？

客户端申请 —> 连接器（验证用户身份，给予权限）—> 查问缓存（存在缓存则间接返回，不存在则执行后续操作）—> 分析器（对 SQL 进行词法剖析和语法分析操作）—> 优化器（次要对执行的 sql 优化抉择最优的执行计划办法）—> 执行器（执行时会先看用户是否有执行权限，有才去应用这个引擎提供的接口）—> 去引擎层获取数据返回（如果开启查问缓存则会缓存查问后果）

说说 MySQL 有哪些存储引擎？都有哪些区别？

二、存储引擎

存储引擎是 MySQL 的组件，用于解决不同表类型的 SQL 操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定程度等性能，应用不同的存储引擎，还能够取得特定的性能。

应用哪一种引擎能够灵便抉择，<mark> 一个数据库中多个表能够应用不同引擎以满足各种性能和理论需要 </mark>，应用适合的存储引擎，将会进步整个数据库的性能。

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 的重要起因之一；

一张表，外面有 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、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 高效获取数据的数据结构，所以说 索引的实质是：数据结构
• 索引的目标在于进步查问效率，能够类比字典、火车站的车次表、图书的目录等。
• 能够简略的了解为“排好序的疾速查找数据结构”，数据自身之外，<font color=#FF0000>数据库还维护者一个满足特定查找算法的数据结构</font>，这些数据结构以某种形式援用（指向）数据，这样就能够在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引形式示例。

右边的数据表，一共有两列七条记录，最右边的是数据记录的物理地址

为了放慢 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：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范畴域对应三个指针指向的子树的数据的范畴域。以根节点为例，关键字为 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 的数据了，这个是十分重要的性质，即 索引的最左匹配个性。

数据库为什么应用 B + 树而不是 B 树

• B 树只适宜随机检索，而 B + 树同时反对随机检索和程序检索；
• B+ 树空间利用率更高，可缩小 I / O 次数，磁盘读写代价更低。一般来说，索引自身也很大，不可能全副存储在内存中，因而索引往往以索引文件的模式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘 I / O 耗费。B+ 树的外部结点并没有指向关键字具体信息的指针，只是作为索引应用，其外部结点比 B 树小，盘块能包容的结点中关键字数量更多，一次性读入内存中能够查找的关键字也就越多，绝对的，IO 读写次数也就升高了。而 IO 读写次数是影响索引检索效率的最大因素；
• B+ 树的查问效率更加稳固。B 树搜寻有可能会在非叶子结点完结，越凑近根节点的记录查找时间越短，只有找到关键字即可确定记录的存在，其性能等价于在关键字选集内做一次二分查找。而在 B + 树中，程序检索比拟显著，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找门路长度雷同，导致每一个关键字的查问效率相当。
• B- 树在进步了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题。B+ 树的叶子节点应用指针程序连贯在一起，只有遍历叶子节点就能够实现整棵树的遍历。而且在数据库中基于范畴的查问是十分频繁的，而 B 树不反对这样的操作。
• 增删文件（节点）时，效率更高。因为 B + 树的叶子节点蕴含所有关键字，并以有序的链表构造存储，这样可很好进步增删效率。

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

MyISAM 引擎的索引文件和数据文件是拆散的。<mark>MyISAM 引擎索引构造的叶子节点的数据域，寄存的并不是理论的数据记录，而是数据记录的地址 </mark>。索引文件与数据文件拆散，这样的索引称为 ”<mark>非聚簇索引</mark>”。MyISAM 的主索引与辅助索引区别并不大，只是主键索引不能有反复的关键字。

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

主索引是指主键索引，键值不可能反复；辅助索引则是一般索引，键值可能反复。

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

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

InnoDB 引擎索引构造的叶子节点的数据域，寄存的就是理论的数据记录（对于主索引，此处会寄存表中所有的数据记录；对于辅助索引此处会援用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB 的数据文件自身就是主键索引文件，这样的索引被称为 ”<mark>“聚簇索引”</mark>，一个表只能有一个聚簇索引。

主键索引：

咱们晓得 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 是一种多路均衡查问树，所以他的节点是人造有序的（左子节点小于父节点、父节点小于右子节点），所以对于范畴查问的时候不须要做全表扫描。

哈希索引不反对多列联结索引的最左匹配规定，如果有大量反复键值得状况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

InnoDB 表为什么要倡议用自增列做主键

1、InnoDB 引擎表是基于 B + 树的索引组织表(IOT)

对于 B + 树

（图片来源于网上）

B+ 树的特点:

（1）所有关键字都呈现在叶子结点的链表中(浓密索引)，且链表中的关键字恰好是有序的;

（2）不可能在非叶子结点命中;

（3）非叶子结点相当于是叶子结点的索引 (稠密索引)，叶子结点相当于是存储(关键字) 数据的数据层;

2、如果咱们定义了主键(PRIMARY KEY)，那么 InnoDB 会抉择主键作为汇集索引、如果没有显式定义主键，则 InnoDB 会抉择第一个不蕴含有 NULL 值的惟一索引作为主键索引、如果也没有这样的惟一索引，则 InnoDB 会抉择内置 6 字节长的 ROWID 作为隐含的汇集索引(ROWID 随着行记录的写入而主键递增，这个 ROWID 不像 ORACLE 的 ROWID 那样可援用，是隐含的)。

3、数据记录自身被存于主索引（一颗 B +Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键程序寄存，因而每当有一条新的记录插入时，MySQL 会依据其主键将其插入适当的节点和地位，如果页面达到装载因子（InnoDB 默认为 15/16），则开拓一个新的页（节点）

4、如果表应用自增主键，那么每次插入新的记录，记录就会程序增加到以后索引节点的后续地位，当一页写满，就会主动开拓一个新的页

5、如果应用非自增主键（如果身份证号或学号等），因为每次插入主键的值近似于随机，因而每次新纪录都要被插到现有索引页得两头某个地位，此时 MySQL 不得不为了将新记录插到适合地位而挪动数据，甚至指标页面可能曾经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这减少了很多开销，同时频繁的挪动、分页操作造成了大量的碎片，失去了不够紧凑的索引构造，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。

综上总结，如果 InnoDB 表的数据写入程序能和 B + 树索引的叶子节点程序统一的话，这时候存取效率是最高的，也就是上面这几种状况的存取效率最高：

1、应用自增列 (INT/BIGINT 类型) 做主键，这时候写入程序是自增的，和 B + 数叶子节点决裂程序统一；

2、该表不指定自增列做主键，同时也没有能够被选为主键的惟一索引 (下面的条件)，这时候 InnoDB 会抉择内置的 ROWID 作为主键，写入程序和 ROWID 增长程序统一；
除此以外，如果一个 InnoDB 表又没有显示主键，又有能够被抉择为主键的惟一索引，但该惟一索引可能不是递增关系时(例如字符串、UUID、多字段联结惟一索引的状况)，该表的存取效率就会比拟差。

页决裂

http://www.uxys.com/html/MySQL/20190722/70361.html

哪些状况须要创立索引

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

哪些状况不要创立索引

1. 表记录太少
2. 常常增删改的表
3. 数据反复且散布平均的表字段，只应该为最常常查问和最常常排序的数据列建设索引（如果某个数据类蕴含太多的反复数据，建设索引没有太大意义）
4. 频繁更新的字段不适宜创立索引（会减轻 IO 累赘）
5. where 条件里用不到的字段不创立索引

MySQL 高效索引

https://www.cnblogs.com/myseries/p/11265849.html

笼罩索引（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 事务次要用于解决操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即须要删除人员的根本材料，也要删除和该人员相干的信息，如信箱，文章等等，这样，这些数据库操作语句就形成一个事务！

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

反复读，就是在开始读取数据（事务开启）时，不再容许批改操作。<mark>MySQL 的默认事务隔离级别 </mark>

事例：程序员拿着信用卡去享受生存（卡里当然是只有 3.6 万），当他埋单时（事务开启，不容许其余事务的 UPDATE 批改操作），免费零碎当时检测到他的卡里有 3.6 万。这个时候他的妻子不能转出金额了。接下来免费零碎就能够扣款了。

剖析：反复读能够解决不可反复读问题。写到这里，应该明确的一点就是，不可反复读对应的是批改，即 UPDATE 操作。然而可能还会有幻读问题。因为幻读问题对应的是插入 INSERT 操作，而不是 UPDATE 操作。

什么时候会呈现幻读？

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

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

Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化程序执行，能够防止脏读、不可反复读与幻读。简略来说，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 如何工作：

• SELECT

  InnoDB 会依据以下两个条件查看每行记录：

  • 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 表锁

MyISAM 的表锁有两种模式：

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

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

默认状况下，写锁比读锁具备更高的优先级：当一个锁开释时，这个锁会优先给写锁队列中等待的获取锁申请，而后再给读锁队列中等待的获取锁申请。

InnoDB 行锁

InnoDB 实现了以下两种类型的 行锁：

• 共享锁又称为读锁，简称 S 锁，顾名思义，共享锁就是多个事务对于同一数据能够共享一把锁，都能拜访到数据，然而只能读不能批改。
• 排他锁又称为写锁，简称 X 锁，顾名思义，排他锁就是不能与其余所并存，如一个事务获取了一个数据行的排他锁，其余事务就不能再获取该行的其余锁，包含共享锁和排他锁，然而获取排他锁的事务是能够对数据就行读取和批改。

为了容许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种外部应用的意向锁（Intention Locks），这两种意向锁都是 表锁：

• 动向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先获得该表的 IS 锁。
• 动向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先获得该表的 IX 锁。

索引生效会导致行锁变表锁。比方 vchar 查问不写单引号的状况。

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能生效，否则都会从行锁降级为表锁。

加锁机制

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

乐观锁会“乐观地”假设大概率不会产生并发更新抵触，拜访、解决数据过程中不加锁，只在更新数据时再依据版本号或工夫戳判断是否有抵触，有则解决，无则提交事务。用数据版本（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 Optimizer 时，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）

各字段解释

• <mark>id</mark>（select 查问的序列号，蕴含一组数字，示意查问中执行 select 子句或操作表的程序）

  • id 雷同，执行程序从上往下
  • id 全不同，如果是子查问，id 的序号会递增，id 值越大优先级越高，越先被执行
  • id 局部雷同，执行程序是先依照数字大的先执行，而后数字雷同的依照从上往下的程序执行

• <mark> select_type</mark>（查问类型，用于区别一般查问、联结查问、子查问等简单查问）

  • SIMPLE：简略的 select 查问，查问中不蕴含子查问或 UNION
  • PRIMARY：查问中若蕴含任何简单的子局部，最外层查问被标记为 PRIMARY
  • SUBQUERY：在 select 或 where 列表中蕴含了子查问
  • DERIVED：在 from 列表中蕴含的子查问被标记为 DERIVED，MySQL 会递归执行这些子查问，把后果放在长期表里
  • UNION：若第二个 select 呈现在 UNION 之后，则被标记为 UNION，若 UNION 蕴含在 from 子句的子查问中，外层 select 将被标记为 DERIVED
  • UNION RESULT：从 UNION 表获取后果的 select
• <mark> table</mark>（显示这一行的数据是对于哪张表的）

• <mark> type</mark>（显示查问应用了那种类型，从最好到最差顺次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL）

  • system：表只有一行记录（等于零碎表），是 const 类型的特例，平时不会呈现
  • const：示意通过索引一次就找到了，const 用于比拟 primary key 或 unique 索引，因为只有匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查问转换为一个常量
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或惟一索引扫描
  • ref：非唯一性索引扫描，范畴匹配某个独自值得所有行。实质上也是一种索引拜访，他返回所有匹配某个独自值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
  • range：只检索给定范畴的行，应用一个索引来抉择行。key 列显示应用了哪个索引，个别就是在你的 where 语句中呈现了 between、<、>、in 等的查问，这种范畴扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而完结于另一点，不必扫描全副索引
  • index：Full Index Scan，index 于 ALL 区别为 index 类型只遍历索引树。通常比 ALL 快，因为索引文件通常比数据文件小。（也就是说尽管 all 和 index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）
  • ALL：Full Table Scan，将遍历全表找到匹配的行

  tip: 一般来说，得保障查问至多达到 range 级别，最好达到 ref

• <mark> possible_keys</mark>（显示可能利用在这张表中的索引，一个或多个，查问波及到的字段若存在索引，则该索引将被列出，但不肯定被查问理论应用）

• <mark> key</mark>

  • 理论应用的索引，如果为 NULL，则没有应用索引
  • 查问中若应用了笼罩索引，则该索引和查问的 select 字段重叠，仅呈现在 key 列表中

• <mark> key_len</mark>

  • 示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度。在不损失精确性的状况下，长度越短越好
  • key_len 显示的值为索引字段的最大可能长度，并非理论应用长度，即 key_len 是依据表定义计算而得，不是通过表内检索出的
• <mark> ref</mark>（显示索引的哪一列被应用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）
• <mark> rows</mark>（依据表统计信息及索引选用状况，大抵估算找到所需的记录所须要读取的行数）

• <mark> Extra</mark>（蕴含不适宜在其余列中显示但非常重要的额定信息）

  1. <font color=red>using filesort</font>: 阐明 mysql 会对数据应用一个内部的索引排序，不是依照表内的索引程序进行读取。mysql 中无奈利用索引实现的排序操作称为“文件排序”。常见于 order by 和 group by 语句中
  2. <font color=red>Using temporary</font>：应用了长期表保留两头后果，mysql 在对查问后果排序时应用长期表。常见于排序 order by 和分组查问 group by。
  3. <font color=red>using index</font>：示意相应的 select 操作中应用了笼罩索引，防止拜访了表的数据行，效率不错，如果同时呈现 using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
  4. using where：应用了 where 过滤
  5. using join buffer：应用了连贯缓存
  6. impossible where：where 子句的值总是 false，不能用来获取任何元祖
  7. select tables optimized away：在没有 group by 子句的状况下，基于索引优化操作或对于 MyISAM 存储引擎优化 COUNT(*)操作，不用等到执行阶段再进行计算，查问执行打算生成的阶段即实现优化
  8. distinct：优化 distinct 操作，在找到第一匹配的元祖后即进行找同样值的动作

case:

1. 第一行（执行程序 4）：id 列为 1，示意是 union 里的第一个 select，select_type 列的 primary 示意该查问为外层查问，table 列被标记为 \<derived3>，示意查问后果来自一个衍生表，其中 derived3 中 3 代表该查问衍生自第三个 select 查问，即 id 为 3 的 select。【select d1.name……】
2. 第二行（执行程序 2）：id 为 3，是整个查问中第三个 select 的一部分。因查问蕴含在 from 中，所以为 derived。【select id,name from t1 where other_column=”】
3. 第三行（执行程序 3）：select 列表中的子查问 select_type 为 subquery，为整个查问中的第二个 select。【select id from t3】
4. 第四行（执行程序 1）：select_type 为 union，阐明第四个 select 是 union 里的第二个 select，最先执行【select name,id from t2】
5. 第五行（执行程序 5）：代表从 union 的长期表中读取行的阶段，table 列的 <union1,4> 示意用第一个和第四个 select 的后果进行 union 操作。【两个后果 union 操作】

慢查问日志

MySQL 的慢查问日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应工夫超过阈值的语句，具体指运行工夫超过 long_query_time 值的 SQL，则会被记录到慢查问日志中。

• long_query_time 的默认值为 10，意思是运行 10 秒以上的语句
• 默认状况下，MySQL 数据库没有开启慢查问日志，须要手动设置参数开启

查看开启状态

SHOW VARIABLES LIKE '%slow_query_log%'

开启慢查问日志

• 长期配置：

mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;

​ 也可 set 文件地位，零碎会默认给一个缺省文件 host_name-slow.log

​ 应用 set 操作开启慢查问日志只对以后数据库失效，如果 MySQL 重启则会生效。

• 永恒配置

  批改配置文件 my.cnf 或 my.ini，在 [mysqld] 一行上面退出两个配置参数

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3

注：log-slow-queries 参数为慢查问日志寄存的地位，个别这个目录要有 MySQL 的运行帐号的可写权限，个别都将这个目录设置为 MySQL 的数据寄存目录；long_query_time=2 中的 2 示意查问超过两秒才记录；在 my.cnf 或者 my.ini 中增加 log-queries-not-using-indexes 参数，示意记录下没有应用索引的查问。

能够用 select sleep(4) 验证是否胜利开启。

在生产环境中，如果手工剖析日志，查找、剖析 SQL，还是比拟吃力的，所以 MySQL 提供了日志剖析工具mysqldumpslow。

通过 mysqldumpslow –help 查看操作帮忙信息

• 失去返回记录集最多的 10 个 SQL

  mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log

• 失去拜访次数最多的 10 个 SQL

  mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log

• 失去依照工夫排序的前 10 条外面含有左连贯的查问语句

  mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log

• 也能够和管道配合应用

  mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more

也可应用 pt-query-digest 剖析 RDS MySQL 慢查问日志

Show Profile 剖析查问

通过慢日志查问能够晓得哪些 SQL 语句执行效率低下，通过 explain 咱们能够得悉 SQL 语句的具体执行状况，索引应用等，还能够联合 Show Profile 命令查看执行状态。

• Show Profile 是 MySQL 提供能够用来剖析以后会话中语句执行的资源耗费状况。能够用于 SQL 的调优的测量
• 默认状况下，参数处于敞开状态，并保留最近 15 次的运行后果

• 剖析步骤

  1. 是否反对，看看以后的 mysql 版本是否反对

     mysql>Show  variables like 'profiling';  -- 默认是敞开，应用前须要开启

  2. 开启性能，默认是敞开，应用前须要开启

     mysql>set profiling=1;  

  3. 运行 SQL

  4. 查看后果

      mysql> show profiles;
     +----------+------------+---------------------------------+
      | Query_ID | Duration   | Query                           |
     +----------+------------+---------------------------------+
      |        1 | 0.00385450 | show variables like "profiling" |
     |        2 | 0.00170050 | show variables like "profiling" |
      |        3 | 0.00038025 | select * from t_base_user       |
     +----------+------------+---------------------------------+

  5. 诊断 SQL，show profile cpu,block io for query id(上一步后面的问题 SQL 数字号码)

  6. 日常开发须要留神的论断

     • converting HEAP to MyISAM 查问后果太大，内存都不够用了往磁盘上搬了。
     • create tmp table 创立长期表，这个要留神
     • Copying to tmp table on disk 把内存长期表复制到磁盘
     • locked

查问中哪些状况不会应用索引？

性能优化

索引优化

1. 全值匹配我最爱
2. 最佳左前缀法令，比方建设了一个联结索引(a,b,c)，那么其实咱们可利用的索引就有(a), (a,b), (a,b,c)
3. 不在索引列上做任何操作（计算、函数、(主动 or 手动)类型转换），会导致索引生效而转向全表扫描
4. 存储引擎不能应用索引中范畴条件左边的列
5. 尽量应用笼罩索引(只拜访索引的查问(索引列和查问列统一))，缩小 select
6. is null ,is not null 也无奈应用索引
7. like “xxxx%” 是能够用到索引的，like “%xxxx” 则不行 (like “%xxx%” 同理)。like 以通配符结尾(‘%abc…’) 索引生效会变成全表扫描的操作，
8. 字符串不加单引号索引生效
9. 少用 or，用它来连贯时会索引生效
10. <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in，!= 则不行，会导致全表扫描

建索引的几大准则

1. 最左前缀匹配准则，十分重要的准则，mysql 会始终向右匹配直到遇到范畴查问 (>、<、between、like) 就进行匹配，比方 a = 1 and b = 2 and c > 3 and d = 4 如果建设 (a,b,c,d) 程序的索引，d 是用不到索引的，如果建设 (a,b,d,c) 的索引则都能够用到，a,b,d 的程序能够任意调整。
2. = 和 in 能够乱序，比方 a = 1 and b = 2 and c = 3 建设 (a,b,c) 索引能够任意程序，mysql 的查问优化器会帮你优化成索引能够辨认的模式。
3. 尽量抉择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，示意字段不反复的比例，比例越大咱们扫描的记录数越少，惟一键的区分度是 1，而一些状态、性别字段可能在大数据背后区分度就是 0，那可能有人会问，这个比例有什么经验值吗？应用场景不同，这个值也很难确定，个别须要 join 的字段咱们都要求是 0.1 以上，即均匀 1 条扫描 10 条记录。
4. 索引列不能参加计算，放弃列“洁净”，比方 from_unixtime(create_time) =’2014-05-29’就不能应用到索引，起因很简略，b+ 树中存的都是数据表中的字段值，但进行检索时，须要把所有元素都利用函数能力比拟，显然老本太大。所以语句应该写成 create_time = unix_timestamp(’2014-05-29’)。
5. 尽量的扩大索引，不要新建索引。比方表中曾经有 a 的索引，当初要加 (a,b) 的索引，那么只须要批改原来的索引即可。

一般性倡议

• 对于单键索引，尽量抉择针对以后 query 过滤性更好的索引
• 在抉择组合索引的时候，以后 Query 中过滤性最好的字段在索引字段程序中，地位越靠前越好。
• 在抉择组合索引的时候，尽量抉择能够可能蕴含以后 query 中的 where 字句中更多字段的索引
• 尽可能通过剖析统计信息和调整 query 的写法来达到抉择适合索引的目标
• 少用 Hint 强制索引

查问优化

永远小标驱动大表（小的数据集驱动大的数据集）

slect * from A where id in (select id from B)` 等价于
#等价于
select id from B
select * from A where A.id=B.id

当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists

select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`

当 A 表的数据集小于 B 表的数据集时，用 exists 优于用 in

留神：A 表与 B 表的 ID 字段应建设索引。

order by 关键字优化

• order by 子句，尽量应用 Index 形式排序，防止应用 FileSort 形式排序
• MySQL 反对两种形式的排序，FileSort 和 Index，Index 效率高，它指 MySQL 扫描索引自身实现排序，FileSort 效率较低；

• ORDER BY 满足两种状况，会应用 Index 形式排序；

  • ORDER BY 语句应用索引最左前列
  • 应用 where 子句与 ORDER BY 子句条件列组合满足索引最左前列
• 尽可能在索引列上实现排序操作，遵循建索引的最左前缀

• 如果不在索引列上，filesort 有两种算法，mysql 就要启动双路排序和单路排序

  • 双路排序：MySQL 4.1 之前是应用双路排序，字面意思就是两次扫描磁盘，最终失去数据
  • 单路排序：从磁盘读取查问须要的所有列，依照 order by 列在 buffer 对它们进行排序，而后扫描排序后的列表进行输入，效率高于双路排序

• 优化策略

  • 增大 sort_buffer_size 参数的设置
  • 增大 max_lencth_for_sort_data 参数的设置

GROUP BY 关键字优化

• group by 本质是先排序后进行分组，遵循索引建的最佳左前缀
• 当无奈应用索引列，增大 max_length_for_sort_data 参数的设置，增大 sort_buffer_size 参数的设置
• where 高于 having，能写在 where 限定的条件就不要去 having 限定了

数据类型优化

MySQL 反对的数据类型十分多，抉择正确的数据类型对于获取高性能至关重要。不论存储哪种类型的数据，上面几个简略的准则都有助于做出更好的抉择。

• 更小的通常更好：个别状况下，应该尽量应用能够正确存储数据的最小数据类型。

  简略就好：简略的数据类型通常须要更少的 CPU 周期。例如，整数比字符操作代价更低，因为字符集和校对规定（排序规定）使字符比拟比整型比较复杂。

• 尽量避免 NULL：通常状况下最好指定列为 NOT NULL

说一下大表的优化计划

https://blog.csdn.net/u011516972/article/details/89098732

九、分区、分表、分库

MySQL 分区

个别状况下咱们创立的表对应一组存储文件，应用 MyISAM 存储引擎时是一个 .MYI 和.MYD文件，应用 Innodb 存储引擎时是一个 .ibd 和.frm（表构造）文件。

当数据量较大时（个别千万条记录级别以上），MySQL 的性能就会开始降落，这时咱们就须要将数据扩散到多组存储文件，保障其单个文件的执行效率

能干嘛

• 逻辑数据宰割
• 进步繁多的写和读利用速度
• 进步分区范畴读查问的速度
• 宰割数据可能有多个不同的物理文件门路
• 高效的保留历史数据

怎么玩

首先查看以后数据库是否反对分区

• MySQL5.6 以及之前版本：

  SHOW VARIABLES LIKE '%partition%';

• MySQL5.6：

  show plugins;

分区类型及操作

• RANGE 分区：基于属于一个给定间断区间的列值，把多行调配给分区。mysql 将会依据指定的拆分策略，, 把数据放在不同的表文件上。相当于在文件上, 被拆成了小块. 然而, 对外给客户的感觉还是一张表，通明的。

  依照 range 来分，就是每个库一段间断的数据，这个个别是按比方 工夫范畴 来的，比方交易表啊，销售表啊等，能够依据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。

  range 来分，益处在于说，扩容的时候很简略。

• LIST 分区：相似于按 RANGE 分区，每个分区必须明确定义。它们的次要区别在于，LIST 分区中每个分区的定义和抉择是基于某列的值从属于一个值列表集中的一个值，而 RANGE 分区是从属于一个间断区间值的汇合。

• HASH 分区：基于用户定义的表达式的返回值来进行抉择的分区，该表达式应用将要插入到表中的这些行的列值进行计算。这个函数能够蕴含 MySQL 中无效的、产生非负整数值的任何表达式。

  hash 散发，益处在于说，能够平均分配每个库的数据量和申请压力；害处在于说扩容起来比拟麻烦，会有一个数据迁徙的过程，之前的数据须要从新计算 hash 值重新分配到不同的库或表

• KEY 分区：相似于按 HASH 分区，区别在于 KEY 分区只反对计算一列或多列，且 MySQL 服务器提供其本身的哈希函数。必须有一列或多列蕴含整数值。

看上去分区表很帅气，为什么大部分互联网还是更多的抉择本人分库分表来程度扩大咧？

• 分区表，分区键设计不太灵便，如果不走分区键，很容易呈现全表锁
• 一旦数据并发量上来，如果在分区表施行关联，就是一个劫难
• 本人分库分表，本人掌控业务场景与拜访模式，可控。分区表，研发写了一个 sql，都不确定 mysql 是怎么玩的，不太可控

随着业务的倒退，业务越来越简单，利用的模块越来越多，总的数据量很大，高并发读写操作均超过单个数据库服务器的解决能力怎么办？

这个时候就呈现了 数据分片，数据分片指依照某个维度将寄存在繁多数据库中的数据扩散地寄存至多个数据库或表中。数据分片的无效伎俩就是对关系型数据库进行分库和分表。

区别于分区的是，分区个别都是放在单机里的，用的比拟多的是工夫范畴分区，不便归档。只不过分库分表须要代码实现，分区则是 mysql 外部实现。分库分表和分区并不抵触，能够联合应用。

说说分库与分表的设计

MySQL 分表

分表有两种宰割形式，一种垂直拆分，另一种程度拆分。

• 垂直拆分

  垂直分表，通常是依照业务性能的应用频次，把次要的、热门的字段放在一起做为次要表。而后把不罕用的，依照各自的业务属性进行汇集，拆分到不同的主要表中；次要表和主要表的关系个别都是一对一的。

• 程度拆分(数据分片)

  单表的容量不超过 500W，否则倡议程度拆分。是把一个表复制成同样表构造的不同表，而后把数据依照肯定的规定划分，别离存储到这些表中，从而保障单表的容量不会太大，晋升性能；当然这些构造一样的表，能够放在一个或多个数据库中。

  程度宰割的几种办法：

  • 应用 MD5 哈希，做法是对 UID 进行 md5 加密，而后取前几位（咱们这里取前两位），而后就能够将不同的 UID 哈希到不同的用户表（user_xx）中了。
  • 还可依据工夫放入不同的表，比方：article_201601，article_201602。
  • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到肯定的贴数后，再把低热度的表独自拆分成一张表。
  • 依据 ID 的值放入对应的表，第一个表 user_0000，第二个 100 万的用户数据放在第二 个表 user_0001 中，随用户减少，间接增加用户表就行了。

MySQL 分库

为什么要分库?

数据库集群环境后都是多台 slave，根本满足了读取操作; 然而写入或者说大数据、频繁的写入操作对 master 性能影响就比拟大，这个时候，单库并不能解决大规模并发写入的问题，所以就会思考分库。

分库是什么？

一个库里表太多了，导致了海量数据，零碎性能降落，把本来存储于一个库的表拆分存储到多个库上，通常是将表依照功能模块、关系密切水平划分进去，部署到不同库上。

长处：

• 缩小增量数据写入时的锁对查问的影响
• 因为单表数量降落，常见的查问操作因为缩小了须要扫描的记录，使得单表单次查问所需的检索行数变少，缩小了磁盘 IO，时延变短

然而它无奈解决单表数据量太大的问题

分库分表后的难题

分布式事务的问题，数据的完整性和一致性问题。

数据操作维度问题：用户、交易、订单各个不同的维度，用户查问维度、产品数据分析维度的不同比照剖析角度。跨库联结查问的问题，可能须要两次查问 跨节点的 count、order by、group by 以及聚合函数问题，可能须要别离在各个节点上失去后果后在应用程序端进行合并 额定的数据管理累赘，如：拜访数据表的导航定位 额定的数据运算压力，如：须要在多个节点执行，而后再合并计算程序编码开发难度晋升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

配主从，正经公司的话，也不会让 Javaer 去搞的，但还是要晓得

十、主从复制

复制的基本原理

• slave 会从 master 读取 binlog 来进行数据同步

• 三个步骤

  1. master 将扭转记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
  2. salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;
  3. slave 重做中继日志中的事件，将扭转利用到本人的数据库中。MySQL 复制是异步且是串行化的。

复制的根本准则

• 每个 slave 只有一个 master
• 每个 salve 只能有一个惟一的服务器 ID
• 每个 master 能够有多个 salve

复制的最大问题

• 延时

十一、其余问题

说一说三个范式

• 第一范式（1NF）：数据库表中的字段都是繁多属性的，不可再分。这个繁多属性由根本类型形成，包含整型、实数、字符型、逻辑型、日期型等。
• 第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的局部函数依赖（局部函数依赖指的是存在组合关键字中的某些字段决定非关键字段的状况），也即所有非关键字段都齐全依赖于任意一组候选关键字。
• 第三范式（3NF）：在第二范式的根底上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则合乎第三范式。所谓传递函数依赖，指的是如果存在 ”A → B → C” 的决定关系，则 C 传递函数依赖于 A。因而，满足第三范式的数据库表应该不存在如下依赖关系：关键字段 → 非关键字段 x → 非关键字段 y

百万级别或以上的数据如何删除

对于索引：因为索引须要额定的保护老本，因为索引文件是独自存在的文件, 所以当咱们对数据的减少, 批改, 删除, 都会产生额定的对索引文件的操作, 这些操作须要耗费额定的 IO, 会升高增 / 改 / 删的执行效率。所以，在咱们删除数据库百万级别数据的时候，查问 MySQL 官网手册得悉删除数据的速度和创立的索引数量是成正比的。

1. 所以咱们想要删除百万数据的时候能够先删除索引（此时大略耗时三分多钟）
2. 而后删除其中无用数据（此过程须要不到两分钟）
3. 删除实现后从新创立索引 (此时数据较少了) 创立索引也十分快，约十分钟左右。
4. 与之前的间接删除相对是要疾速很多，更别说万一删除中断, 所有删除会回滚。那更是坑了。

limit 100000 加载很慢的话，你是怎么解决的呢？

在 mysql 中 limit 能够实现疾速分页，然而如果数据到了几百万时咱们的 limit 必须优化能力无效的正当的实现分页了，否则可能卡死你的服务器

当一个表数据有几百万的数据的时候成了问题！

日常分页 SQL 语句

select id,name,content from users order by id asc limit 100000,20

扫描 100020 行

如果记录了上次的最大 ID

 select id,name,content from users where id>100073 order by id asc limit 20

扫描 20 行。

总数据有 500 万左右，以下例子

select * from wl_tagindex where byname='f' order by id limit 300000,10 

执行工夫是 3.21s

优化后：

select * from (
  select id from wl_tagindex
where byname='f' order by id limit 300000,10
) a
left join wl_tagindex b on a.id=b.id

执行工夫为 0.11s 速度显著晋升

这里须要阐明的是 我这里用到的字段是 byname ,id 须要把这两个字段做复合索引，否则的话成果晋升不显著

在高并发状况下，如何做到平安的批改同一行数据？

1、应用乐观锁

乐观锁实质是以后只有一个线程执行操作，排挤内部申请的批改。遇到加锁的状态，就必须期待。完结了唤醒其余线程进行解决。尽管此计划确实解决了数据安全的问题，然而，咱们的场景是“高并发”。也就是说，会很多这样的批改申请，每个申请都须要期待“锁”，某些线程可能永远都没有机会抢到这个“锁”，这种申请就会死在那里。同时，这种申请会很多，霎时增大零碎的均匀响应工夫，后果是可用连接数被耗尽，零碎陷入异样。

2、FIFO（First Input First Output，先进先出）缓存队列思路

间接将申请放入队列中，就不会导致某些申请永远获取不到锁。看到这里，是不是有点强行将多线程变成单线程的感觉哈。

而后，咱们当初解决了锁的问题，全副申请采纳“先进先出”的队列形式来解决。那么新的问题来了，高并发的场景下，因为申请很多，很可能一瞬间将队列内存“撑爆”，而后零碎又陷入到了异样状态。或者设计一个极大的内存队列，也是一种计划，然而，零碎解决完一个队列内申请的速度根本无法和疯狂涌入队列中的数目相比。也就是说，队列内的申请会越积攒越多，最终 Web 零碎均匀响应工夫还是会大幅降落，零碎还是陷入异样。

3、应用乐观锁

这个时候，咱们就能够讨论一下“乐观锁”的思路了。乐观锁，是绝对于“乐观锁”采纳更为宽松的加锁机制，大都是采纳带版本号（Version）更新。实现就是，这个数据所有申请都有资格去批改，但会取得一个该数据的版本号，只有版本号合乎的能力更新胜利，其余的返回抢购失败。这样的话，咱们就不须要思考队列的问题，不过，它会增大 CPU 的计算开销。然而，综合来说，这是一个比拟好的解决方案。

参考与感激：

https://zhuanlan.zhihu.com/p/29150809

https://juejin.im/post/5e3eb616f265da570d734dcb#heading-105

https://blog.csdn.net/yin767833376/article/details/81511377

本文由 mdnice 多平台公布