本章是整理知识内容，为强化知识长期更新。
Mysql基础架构图
简单介绍上图相关模块功能
连接器：管理连接，权限验证
查询缓存：命中缓存则直接返回结果
语法解析：词法分析，语法分析
查询优化：执行计划生成，索引选择
执行器：操作引擎，返回结果
存储引擎：存储数据，提供读写结构。
连接器
我们需要连接到mysql服务端才能进行各种操作。
1 | # 使用密码登录到时候我们会使用这种格式。 |
执行上述命令会提示输入密码。输入完密码后就进入了mysql。
如果用户名或者密码输入错误，会提示一个
Access denied for user到错误，然后客户端程序结束执行。如果用户名和密码认证通过，连接器会去权限表中查询该用户拥有的所有权限。此后该连接里面的权限逻辑判断都会依赖认证成功时候读取到的权限。
因此当修改用户的权限之后，如果被修改用户的在登录状态是不会修改已经连接的权限。需要让被修改权限用户从新登录。
- 实际上连接的方式有多种，上面通过密码的方式只是其中的一种方式。当客户端与服务端mysql进程进程建立连接，服务器的进程就会创建一个单独的线程来专门处理与这个客户端的交互，当该客户断开与服务端连接时，服务端并不会马上销毁对应的交互线程，而是缓存起来。当另一个新的客户端连接进来的时候，在把这个缓存的线程分配给新的客户端。这样就避免的频繁创建和修改线程，节省系统的开销。但是连接线程多了也会影响服务端，所以也有默认的参数限制客户端的连接数量。
1 | # 这里是查询服务端存在的连接。 |
我使用的阿里云的数据库，这里展示下结果集。这里需要注意，登录的账号需要有PROCESS的权限否则只能看到自己的连接信息（线程），我这里采用的root所以看到全部到。
连接完成后如果没有执行后续操作则 Command会显示Sleep的状态，长时间Sleep会导致连接自动断开。默认的时间是8小时，也就是参数
wait_timeout。当客户端连接断开后，若客户端再次发出请求就会提示一个
Lost connection to MySQL server during query，此时就只能重新建立连接了。
半双工
- MySQL客户端/服务端通信协议是
“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。 - 服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用
SELECT *以及加上LIMIT限制的原因之一。
长连接与短连接
在数据库里面，长连接是指数据库建立连接，如果客户端有持续的请求则使用同一个连接，短连接是指每次执行几次短的查询就断开连接，下次查询再次创建连接。由于创建的过程复杂，一般来说尽量减少创建连接的动作，尽可能使用长连接。特别是在开发的时候出现大伙突然连接不上测试库，此时就检查下连接是不是太多了。因为连接多了会导致内存消耗特别快。
- mysql的执行过程中临时使用的内存管理是在连接线程对象里面，服务端断开的时候才会释放，为了避免内存消耗过大，每次执行较大的操作之后，可以通过
mysql_reset_connection来重新初始化连接资源。这个操作是不需要重新做权限，只是恢复到创建的初始状态。
- mysql的执行过程中临时使用的内存管理是在连接线程对象里面，服务端断开的时候才会释放，为了避免内存消耗过大，每次执行较大的操作之后，可以通过
查询缓存
一个不被建议使用的功能，在新版的8.0中已经被删除了。总结就是弊大于利。
- 建立完成后，假设执行一次
SELECT语句，执行逻辑就会到第二部查询缓存。mysql拿到一个查询之后，会先到缓存中寻找释放有完全对应的，因为之前执行的查询语句和结果集会直接被缓存起来，以Keys-value的形式。keys是查询语句-value是查询结果集。如果能命中这个key则直接返回value。咋一看挺有用的，但实际上查询缓存的实效的太频繁。并且keys的命中条件太不聪明了，如果两次查询语句在任何的字符上存在不通（空格、大小写）都不会缓存命中。为什么会频繁失效，因为如果对查询语句中的表进行更新，就会导致缓存失效。比如INSERT、UPDATE 、DELETE 、TRUNCATE TABLE 、ALTER TABLE等等就会导致缓存失效。
语法解析
如果没有命中查询缓存，就要开始执行sql语句了。mysql需要做什么，因此需要对sql进行解析。MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。
- 本质上发送过来的是一个文本信息，这里就需要对该文本信息进行编译，涉及到
词法解析、语法解析、语义解析等阶段。需要注意的是，这里会对sql语句进行一些检查，比如设计的相关表、表字段。这里检查通过的才会进入查询优化模块。 - 根据SQL语言的功能可以划分成4个部分
- DDL，英文叫做 Data Definition Language，也就是数据定义语言，它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。
- DML，英文叫做 Data Manipulation Language，数据操作语言，我们用它操作和数据库相关的记录，比如增加、删除、修改数据表中的记录。
- DCL，英文叫做 Data Control Language，数据控制语言，我们用它来定义访问权限和安全级别。
- DQL，英文叫做 Data Query Language，数据查询语言，我们用它查询想要的记录，它是 SQL 语言的重中之重。在实际的业务中，我们绝大多数情况下都是在和查询打交道，因此学会编写正确且高效的查询语句，是学习的重点。
查询优化
通过之前的语法解析，基本可以判定语法树是合法的，此时mysql就知道文本内容要做什么。
- mysql会对查询语句进行优化，优化的结果就是生成一个执行计划，这个计划会表明使用了那些查询索引，表之间的连接是什么样子的。有时候出现多种执行方式，只是效率不通，优化器会决定使用哪一个执行方案，这个时候就涉及到
EXPLAIN语句，该语句可以查看sql到执行计划，这将涉及到查询优化。 - MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划。
- 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
- 优化函数
- 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
- 优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于
I/O密集型应用，效率会高很多。
执行器
进入到执行sql到环节。
- 通过之前到查询优化，进入执行阶段，此时会先判断该连接用户是否有相关表的权限，如果没有会返回权限错误。如果有权限就会打开表，执行器会根据表定义的引擎去使用这个引擎提供的接口完成流程。
存储引擎
关于存储引擎，这里明确提醒。不是三种也不是四种。而是多种因为随时可能出现新的引擎。
- 我们常用的就是
InnoDB和MyISAM，其它的不常见就不做过多介绍。mysql现在默认的引擎是InnoDB所以主要也是了解InnoDB
1 | # 查看当前服务支持的存储引擎 |
innodb
- 插入缓冲（insert buffer),
- 二次写(double write),
- 自适应哈希索引(ahi),
- 预读(read ahead)
- 支持事务操作，具有事务 ACID 隔离特性，默认的隔离级别是
可重复读(repetable-read)、通过MVCC（并发版本控制）来实现的。能够解决脏读和不可重复读的问题。 - InnoDB 支持外键操作。
- InnoDB 默认的锁粒度
行级锁，并发性能比较好，会发生死锁的情况。 - 和 MyISAM 一样的是，InnoDB 存储引擎也有
.frm文件存储表结构定义，但是不同的是，InnoDB 的表数据与索引数据是存储在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。 - InnoDB 有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。
- InnoDB 和 MyISAM 支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。
- 增删改查性能方面，如果执行大量的增删改操作，推荐使用 InnoDB 存储引擎，它在删除操作时是对行删除，不会重建表。
myisam与innodb
- InnoDB支持事物，而MyISAM不支持事物
- InnoDB支持行级锁，而MyISAM支持表级锁
- InnoDB支持MVCC, 而MyISAM不支持
- InnoDB支持外键，而MyISAM不支持
- InnoDB不支持全文索引，而MyISAM支持。
索引
主键
- 数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
超键
- 在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键
- 是最小超键，即没有冗余元素的超键。
外键
- 在一个表中存在的另一个表的主键称此表的外键。
为什么要使用自增列作为主键？
自增ID有序，会按顺序往最后插入，而UUID无序，随机生成，随机插入，会造成频繁页分裂，内存碎片化，大量随机IO
如果没有显示的指定主键，则InnoDB会选择一个不包含NULL值的唯一索引作为主键索引。如果不满足条件，InnoDB会选择内置6字节长度的ROWID作为隐含的聚集索引。
InnoDB数据记录本身存放主索引（B+Tree）的叶子结点上面，同一个叶子结点内的各条数据是按照主键顺序存放。因此每次插入数据的时候，mysql会根据其主键插入适当的位置，如果页达到装载因子条件，则开辟一个新的页。
使用非自增，每次插入的值都是几乎无序的，导致叶节点不够紧凑，频繁移动分叶造成大量碎片。增加维护工作。
InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力。
索引是什么
在了解索引之前，大概理解下mysql存储数据的大小划分
表空间 -> 段 -> 区 -> 数据页。
索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构
索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
索引优缺点
- 索引加快数据库的检索速度。
- 索引降低了插入、删除、修改等维护任务的速度。
- 唯一索引可以确保每一行数据的唯一性。
- 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。
- 索引需要占物理和数据空间。
表空间、段、区、数据页
记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。因此在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。
- 一个页中可以存储多个行记录（Row），同时在数据库中，还存在着区（Extent）、段（Segment）和表空间（Tablespace）。行、页、区、段、表空间的关系如下图所示：
索引的优点
天生排序
快速查找
索引的缺点
- 占用空间
- 降低更新表的速度。
注意点
小表使用全表扫描更快，中大表才使用索引。超级大表索引基本无效。
索引实现，分成 2 种：聚集索引和辅助索引（也叫二级索引或者非聚集索引）
功能区分，分为 6 种：普通索引，唯一索引，主键索引，复合索引，外键索引，全文索引。
1、普通索引：最基本的索引，没有任何约束。
2、唯一索引：与普通索引类似，但具有唯一性约束。
3、主键索引：特殊的唯一索引，不允许有空值。
4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。
5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎（ES，Solr）。注意：主键就是唯一索引，但是唯一索引不一定是主键，唯一索引可以为空，但是空值只能有一个，主键不能为空。
常见索引类型
索引是存储在一张表中特定列上的
数据结构，索引是在列上创建的。并且，索引是一种数据结构。
全局索引(FULLTEXT)
- 全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题。
哈希索引(HASH)
- 哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。
B-Tree索引
- B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。
R-Tree索引
- R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。
B+Tree与Hash索引的区别
- B+Tree是一个平衡多叉树，从根节点到每个叶子节点点高度差值不超过1，同层级的节点互相有指针互相链接，是有序的。
- Hash索引是采用一定的Hash算法，把健值换成哈希值，检索的时候不需要类似b+树从根节点逐渐查找，值需要一次哈希运算即可，是无序的。
- Hash索引适合等值查询，哈希索引具有绝对优势（前提是：没有⼤量重复键值，如果⼤量重复键值时，哈希索引的效率很低，因为存在所谓的 哈希碰撞问题。不使用范围查询、也不支持索引完成排序。不支持联合索引最左匹配原则。通过列的基数很大也不建议使用Hash索引。
InnoDB存储引擎的B+树索引
- 每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。
- InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
- 我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
- B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。
- 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。
- 二者SELECT COUNT(*)哪个更快，为什么
- myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引失效
- 两张表联合查询的时候表的编码集不一样会导致索引失效。
- 列类型是字符串，一定要在条件中将数据用
引号引用，否则失效（隐式转换） - or语句前后未使用索引字段 ，字段A做了索引 WHERE A = 1 OR B = 2 这时候也不回走索引。
- like未使用最左前缀，where A like “%China”。
- 在索引上使用函数 、!= 、<> 、not in 。
- where a = b + 1 查询条件做运算
- is null 、is not null
覆盖索引和回表
- 覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。而要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可。
索引下推
在根据索引查询过程中就根据查询条件过滤掉一些记录，减少最后的回表操作
使用索引查询一定能提高查询的性能吗？
通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.
- 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
- 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
- 基于非唯一性索引的检索
事务
事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。
ACID
一说到 MySQL 事务，你肯定能想起来四大特性：
原子性、一致性、隔离性、持久性。ACID模式是一组数据库设计原则强调的是，对于业务数据和关键任务应用重要的可靠性方面。MySQL包含诸如的组件
InnoDB存储引擎与ACID模型紧密结合，因此数据不会损坏，并且不会因软件崩溃和硬件故障等特殊情况而导致结果失真。当您依赖符合ACID的功能时，您无需重新发明一致性检查和崩溃恢复机制。如果您有其他软件安全措施，超可靠硬件或可以容忍少量数据丢失或不一致的应用程序，您可以调整MySQL设置以交换一些ACID可靠性以获得更高的性能或吞吐量。
原子性(Atomicity)
- 原子性指的就是 MySQL 中的包含事务的操作要么
全部成功、要么全部失败回滚，因此事务的操作如果成功就必须要全部应用到数据库，如果操作失败则不能对数据库有任何影响。
一致性(Consistency)
- 一致性指的是一个事务在执行前后其状态一致。比如 A 和 B 加起来的钱一共是 1000 元，那么不管 A 和 B 之间如何转账，转多少次，事务结束后两个用户的钱加起来还得是 1000，这就是事务的一致性。
持久性(Durability)
- 持久性指的是一旦事务提交，那么发生的改变就是永久性的，即使数据库遇到特殊情况比如故障的时候也不会产生干扰。
隔离性(Isolation)
当多个事务同时进行时，就有可能出现
脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)的情况，为了解决这些并发问题，提出了隔离性的概念。
读未提交：读未提交指的是一个事务在提交之前，它所做的修改就能够被其他事务所看到。读已提交：读已提交指的是一个事务在提交之后，它所做的变更才能够让其他事务看到。可重复读：可重复读指的是一个事务在执行的过程中，看到的数据是和启动时看到的数据是一致的。未提交的变更对其他事务不可见。串行化：顾名思义是对于同一行记录，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
隔离性四个特性
最重要的就是一致性。而一致性由原子性，隔离性，持久性来保证。
- 原子性由 Undo log 保证。Undo Log 会保存每次变更之前的记录，从而在发生错误时进行回滚。
- 隔离性由 MVCC 和 Lock 保证。
- 持久性由 Redo Log 保证。每次真正修改数据之前，都会将记录写到 Redo Log 中，只有 Redo Log 写入成功，才会真正的写入到 B+ 树中，如果提交之前断电，就可以通过 Redo Log 恢复记录。
| 事务隔离级别 | 值 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 读未提交（read-uncommitted） | 1 | 是 | 是 | 是 |
| 读已提交（read-committed） | 2 | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 3 | 否 | 否 | 是（x） |
| 串行化（serializable） | 4 | 否 | 否 | 否 |
读已提交和可重复读实现原理就是MVCC Read View不同的生成时机。可重复读只在事务开始时生成一个Read View；读已提交每次执行前都会生成Read View。
事物带来的问题
- 更新丢失
- 解决：让事务变成串行操作，而不是并发的操作，即对每个事务开始—对读取记录加排他锁
- 脏读
- 解决：隔离级别为Read uncommitted
- 不可重复读
- 解决：使用Next-Key Lock算法来避免
- 幻读
- 解决：间隙锁（Gap Lock）
读未提交
一个事物未提交的时候，它做的数据变更就能被其它事物看到。
- 原理：直接读取数据，不能解决任何并发问题
读提交
一个事物提交后，它做的数据变更才能被其它事物看到。
- 原理：利用MVCC实现，每一句语句执行前都会生成Read View（一致性视图）
可重复读 （默认隔离级别）
一个事物执行的过程中看到的数据，总是跟这个事物在启动时候看的数据一致。只有事务开始时会创建Read View，之后事务里的其他查询都用这个Read View。解决了脏读、不可重复读，快照读（普通查询，读取历史数据）
- 原理：使用MVCC解决了幻读，当前读（读取最新提交数据）通过间隙锁解决幻读（lock in share mode、for update、update、detete、insert），间隙锁在可重复读下才生效。
串行化
对同一行数据
写会加写锁,读会加读锁。当出现读写冲突的时候，必须按数据执行事务。
- 原理：使用锁，读加共享锁，写加排他锁，串行执行
并发事务的问题
脏读
一个事务正在对一条记录进行修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态。这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开启事务 | 开启事务 |
| T2 | 查询账户余额为1000 | |
| T3 | 充值500，余额修改为1500 | |
| T4 | 查询余额为1500 | |
| T5 | 撤销事务，余额改回1000 | |
| T6 | 汇入500，余额修改为2000 | |
| T7 | 提交事务 |
不可重复读
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了变更、或者某些记录已经被删除了。
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开启事务 | 开启事务 |
| T2 | select * from user where user_id=100 假设为小明的用户信息 | |
| T3 | 将user_id=100的用户信息对应的年龄修改为18 | |
| T4 | 提交事务 | |
| T5 | 再次查询发现用户的年龄变更了 | |
| T6 | … | |
| T7 | 提交事务 |
幻读
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其它事务插入了满足其查询条件的新数据。
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开启事务 | 开启事务 |
| T2 | select * from user where age=18 假设得到两条记录 | |
| T3 | 向user表插入一条age=18的新记录 | |
| T4 | 提交事务 | |
| T5 | 再次查询得到三条记录 | |
| T6 | .. | |
| T7 | 提交事务 |
- 在RC可重复读隔离级别下
- 普通的查询是快照读，是不会看到其它事物插入的数据.
- 在RR隔离级别下间隙锁才有效果，RC隔离级别下并没有间隙锁。
- RR隔离级别下，快照读依然靠MVCC版本控制，当前读通过间隙锁解决。
- 为了解决幻读，可以采用读一提交，间隙锁是在可重复读隔离级别下才生效。但是也需要解决数据与日志不一致的问题。需要吧binglog格式设置成row。也就是说RC隔离级别+binglog_format_row的组合。
幻读和不可重复读的区别
- 不可重复读的重点是修改：在同一事务中，相同的条件，第一次和第二次读到的数据不一致（中间有其它事务提交了修改）。
- 幻读的重点是新增或者删除：在同一事务中，相同的条件，第一次和第二次读到的记录数不一样（中间有其它事务提交了新增或者删除）。
LOCK
这很重要不管是面试工作，还是排查异常，出现问题的时候需要发现、定位、解决能力。也是开发人员必须掌握的一项能力。
理解下锁几种维度
类型维度
共享锁
- 读锁（read lock），也叫共享锁（shared lock）（读锁 / S 锁）针对同一份数据，多个读操作可以同时进行而不会互相影响（select）
排它锁（写锁 / X 锁）
写锁（write lock），也叫排他锁（exclusive lock）当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）
意向共享锁（IS）
- 一个事务给一个数据行加共享锁时，必须先获得表的IS锁
意向排他（互斥）锁（IX）
- 一个事务给一个数据行加排他锁时，必须先获得该表的IX锁
悲观锁（使用锁，即 for update）
乐观锁（使用版本号字段，类似 CAS 机制，即用户自己控制。缺点：并发很高的时候，多了很多无用的重试）
锁的粒度（粒度维度）
表锁
- 对整张表加锁；开销小、加锁快、无死锁、锁粒度大，发生锁冲突概率大，并发性极低。
页锁（Mysql BerkeleyDB 引擎）
- 开销、加锁时间和锁粒度介于表锁和行锁之间，会出现死锁，并发处理能力一般。开发基本不用。
行锁（InnoDB）
- 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 对一行数据加锁；开销大、加锁慢、会出现死锁、锁力度小、发送锁冲突较低，并发性适中。
全剧锁 （锁的是整个
database）全库备份的时候时全局锁
锁的算法（算法维度）
- Record Lock（单行记录）
- Gap Lock（间隙锁，锁定一个范围，但不包含锁定记录）
- Next-Key Lock（Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身， MySql 防止幻读，就是使用此锁实现）
加锁方式
- 表锁隐式加锁
1 | select # 隐式上读锁 |
- 表锁显式加锁
1 | lock table talbeName read; # 表加读锁 |
- 行锁隐式加锁
1 | insert update delete # 隐式上写锁 |
- 行锁显式加锁
1 | select * from tableName in share mode; # 读锁 |
- 行锁显式解锁
1 | commit # 提交事物 |
Record Lock
对单个记录加锁,行锁只有在事务结束的时候才会释放。也就时两阶段协议完成后。
1 | ## 对id=3的记录加S型Record Lock S锁 |
Gap Lock
锁住记录前面的间隙，不允许插入记录
1 | ##不允许别的事务在id=3的记录前面的间隙插入新记录，即id值在(1, 3)这个区间内的记录是不允许立即插入的。直到加间隙锁的事务提交后，id值在(1, 3)这个区间中的记录才可以被提交。 |
Next-Key Lock
同时锁住数据和数据前面的间隙，即数据和数据前面的间隙都不允许插入记录
1 | -- 对id=3的聚集索引记录加S型Record Lock |
- 未加索引的字段
只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)
- 每个间隙锁是前开后闭区间。间隙锁语句同样能会锁定大量范围，影响并发速度。
行锁查询
1 | show status like 'innodb_row_lock%'; |
- 行锁使用建议
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
- 合理设计索引，尽量使用小范围的锁。
- 避免长事物，占用大量资源。
乐观锁与悲观锁
数据库通过锁机制来解决并发场景-共享锁（读锁）和排他锁（写锁）。读锁是不阻塞的，多个客户端可以在同一时刻读取同一个资源。写锁是排他的，并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
- 表锁，锁定整张表，开销最小，但是会加剧锁竞争。
- 行锁，锁定行级别，开销最大，但是可以最大程度的支持并发。
- 共享锁（多个事物共享）共享锁也叫读锁或 S 锁。
- 排它锁（单个事物占有）排它锁也独占锁、写锁或 X 锁
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
- 不过通过数据库的版本机制实现，通过版本号字段或者时间戳字段来实现。
死锁
在并发系统中出现不同线程循环依赖资源，涉及的线程都在等待其他线程释放资源，就会导致相关线程进入无限等待的状态，简称死锁。
1 | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
- 检查死锁
show engine innodb status - 设置超时时间，防止线程事务一直等待。
innodb_lock_wait_timeout设置超时时间，该值默认的时间时50s。 - 主动回滚相关线程中某个事物，
show processlist手动释放资源kill pid。
InnoDB引擎的行锁的实现方式
InnoDB是基于索引来完成行锁
1 | -- 例: -- |
InnDb架构
- 这是官方文档中的架构图
Buffer Pool
在使用InnoDB的时候，索引都是以页的形式存在在
表空间中的，本质上是存储在硬盘上的。设计InnoDB的大叔为了缓存磁盘中的页，在MySQL服务器启动的时候就向操作系统申请了一片连续的内存，他们给这片内存起了个名，叫做Buffer Pool（中文名是缓冲池）
1 | [server] |
Mysql log
日志是mysql中非常重要的组成部分，记录着数据库运行期间的各种状态信息。Mysql的日志主要包括
错误日志、查询日志、慢查询日志、事物日志、二进制日志大类。
错误日志 Error Log
- 记录出错信息，也记录一些警告信息或者正确的信息。默认开启。
show vaiables like '%log_err%'
查询日志 General query log
- 记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
show vaiables like '%general%'
慢查询日志 Show query log
- 设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
show variables like '%slow_query_log%'show variables like '%slow_query_time%'慢查询日志记录执行时间阀值
二进制日志 binary log
- 记录对数据库执行更改的所有操作。主要数据库的恢复、主从数据库复制同步。
show vaiables like '%log_bin%'
中继日志
- 中继日志也是二进制日志，用来给slave 库恢复
事物日志
重做日志redo和回滚日志undo
事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”。
binlog
binlog用于记录数据库执行的写入性操作（不包括查询）的信息、以二进制的信息保保存在磁盘中，binlog是Mysql的逻辑日志，又server层进行记录。使用任何的存储引擎都会进行记录。binlog是通过追加的方式写入，当文件达到一定大小值以后，会生成全新文件来保存日志。
逻辑日志：记录sql语句的日志。
物理日志：
mysql innodb数据是保存在数据页中，物理记录的就是数据页变更。主从复制：Master端开启
binlog，然后将binlog发送到Slave端，Slave端通过重放binlog达到数据一致。数据恢复：通过
mysqlbinlog工具来恢复数据库。
日志格式
在
MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定。
STATMENT：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中 。- 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO , 从而提高了性能；
- 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、 slepp() 等 。
ROW：基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。- 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；
- 缺点：会产生大量的日志，尤其是
alter table的时候会让日志暴涨
MIXED：基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog
statement
每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能 与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条 件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所 产生的日志量会增加多少，以及带来的IO性能问题。)
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的 一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题)。
使用以下函数的语句也无法被复制：
- LOAD_FILE()
- UUID()
- USER()
- FOUND_ROWS()
- SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)
同时在INSERT …SELECT 会产生比 RBR 更多的行级锁
row
不记录sql语句上下文相关信息，仅保存哪条记录被修改。
- 优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题
- 缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比 如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。
mixedlevel
- Mixed是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则 采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择 一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。
2PC两阶段提交
mysql中在server层级别有个binlog日志，归档日志，用于备份，主从同步复制，如果采用一主多从架构，主备切换，那就必须用到binlog进行主从同步。
- 事务提交就必须保证redolog与binlog的一致性，一般情况没有开启binlog日志，事务提交不会两阶段提交，若需要主从同步就必须开启binlog使用两阶段提交保证数据一致性。
- Prepare 阶段：InnoDB 将回滚段undolog设置为 prepare 状态；将 redolog 写文件并刷盘；先写redolog，事务进入prepare状态）
- Commit 阶段：Binlog 写入文件；binlog 刷盘；InnoDB commit；
刷盘机制
Innodb下只有在事物提交的情况下才会记录binlog，此时记录还在内存中。mysql通过sync_binlog参数控制binlog刷盘时机。取值范围0-n
- 0：不去强制要求，由系统自行判断何时写入磁盘；
- 1：每次
commit的时候都要将binlog写入磁盘； - N：每N个事务，才会将
binlog写入磁盘。
redo Log
事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态 。
保持一致性
最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，
redo log包含两个部分，一个是内存中的日志缓冲redo log buffer, 另一个是磁盘上的日志文件redo logfile
Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太消耗资源了。一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！
- mysql 每执行一条
DML语句，先将记录写入redo log buffer,后续某个时间点再一次性将多个操作写到redo log file。这种先写日志，再写磁盘的技术就是Mysql里经常说到的WAL (write-ahead Logging)技术。 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。
- mysql 每执行一条
在计算机中，用户的空间
user space下的缓冲区一般情况下是不能直接写入磁盘，中间需要经过系统内核空间kerne space缓冲区Os Buffer，因此redo log buffer写入redo logfile实际上是写入Os Buffer，然后再通过系统调用fsunc()将数据刷入redo log file
刷盘机制
mysql 支持三种将
redo log buffer写入redo log file的机制，可以通过innodb_flush_log_at_trx_commit配置
| innodb_flush_log_at_trx_commit | 描述 |
|---|---|
| 0 | 事物提交不将redo log buffer只日志写入os buffer，而是每秒写入os buffer并调用fsync()写入redo log file中，也就是说设置0时，大概每秒刷新一次进入磁盘，如果系统出现故障，会丢失1秒的数据。 |
| 1 | 每次提交事物都将redo log buufer 写入 os buffer并调用fsync()。这种情况系统故障也不会丢失数据，io性能太差。 |
| 2 | 每次提交事物都将redo log buufer 写入 os buffer，每秒调用fsync()将os buffer中日志写入redo log file |
undo Log
数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。
- 原子性 底层就是通过
undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。 undo log也是MVCC(多版本并发控制)实现的关键。在事物提交之前Undo Log保存了未提交的版本数据,Undo log 中的数据可以作为数据旧版本提供快照读，且为其他事物提供快照读。读取顺序是Undo buffer 中查找数据，未找到就从Undo log file 中查找数据。
Redolog与Binlog
- Redo Log时属于InnoDB引擎功能，Binlog时属于Mysql Server的功能，且以二进制文件记录。
- Redo Log属于物理日志,记录数据叶更新状态内容。binLog时逻辑日志，记录更新过程。
- Redo Log日志循环写入，日志大小空间时固定的，binLog追加写入日志，不会覆盖写入。
- Redo Log在服务器异常之后可以自动恢复使用，binLog可以作为主从和数据恢复使用。binLog没有自动crash-safe能力。
MVCC
MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) 与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control 基于多版本的并发控制协议
- 通过 MVCC 我们可以解决以下几个问题：
- 读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。
- 降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。
- 解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。
隐藏字段
InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer，如果表中没有主键和非NULL唯一键时，则还会有第三个隐藏的主键列row_id。
| 列名 | 是否必须 | 描述 |
|---|---|---|
| row_id | 否 | 单调递增的行ID，不是必需的，占用6个字节。 |
| trx_id | 是 | 记录操作该数据事务的事务ID |
| roll_pointer | 是 | 这个隐藏列就相当于一个指针，指向回滚段的undo日志 |
LBCC：：Lock-Based Concurrency Control，基于锁的并发控制
MVCC：：Multi-Version Concurrency Control 基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提⾼了并发量。 MVCC最⼤的好处：读不加锁，读写不冲突。在读多写少的OLTP应⽤中，读写不冲突是⾮常重要的，极⼤的增加了系统的并发性 能，现阶段⼏乎所有的RDBMS，都⽀持了MVCC。
在MVCC并发控制中，读操作可以分成两类
- 快照读 (snapshot read)：读取的是记录的可⻅版本 (有可能是历史版本)，不⽤加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写） 。
- 当前读 (current read)：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
快照读与当前读区别
- 快照读读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，比如这样：
1 | SELECT * FROM player_users WHERE ... |
当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT，或者对数据进行增删改都会进行当前读，比如：
1 | -- 这里加的是读锁。 update/insert/delete语句 |
Read View
每次开启事务的时候都会生成一个ReadView，ReadView保存了当前事务开启时候所有活跃（还未提交的事务）事务列表。
- 读已提交
- 一个事务中每一次SELECT查询都会获取一次ReadView
- 可重复读
- 一个事务第一次查询SELECT时会获取一次ReadView,此后的SELECT查询都会复用这个ReadView
MVCC如何查询记录
MVCC 的核心就是 Undo Log+ Read View，“MV”就是通过 Undo Log 来保存数据的历史版本，实现多版本的管理，“CC”是通过 Read View 来实现管理，通过 Read View 原则来决定数据是否显示。同时针对不同的隔离级别，Read View 的生成策略不同，也就实现了不同的隔离级别。
- 获取事务自己的版本号，即事务ID
- 获取Read View
- 查询得到的数据，然后Read View中的事务版本号进行比较。
- 如果不符合Read View的可见性规则， 即就需要Undo log中历史快照;
- 最后返回符合规则的数据
如何解决幻读
- Next key锁+MVVC避免幻读。
SQL语句的执行顺序
我们在编写一个查询语句的时候
1 | SELECT DISTINCT |
FROM
- 首先，对 SELECT 语句执行查询时，对
FROM关键字两边的表执行连接，会形成笛卡尔积，这时候会产生一个虚表VT1(virtual table)
首先先来解释一下什么是
笛卡尔积现在我们有两个集合 A = {0,1} , B = {2,3,4}
那么，集合 A * B 得到的结果就是
A * B = {(0,2)、(1,2)、(0,3)、(1,3)、(0,4)、(1,4)};
B * A = {(2,0)、{2,1}、{3,0}、{3,1}、{4,0}、(4,1)};
上面 A * B 和 B * A 的结果就可以称为两个集合相乘的
笛卡尔积我们可以得出结论，A 集合和 B 集合相乘，包含了集合 A 中的元素和集合 B 中元素之和，也就是 A 元素的个数 * B 元素的个数
ON
- 然后对 FROM 连接的结果进行 ON 筛选，创建 VT2，把符合记录的条件存在 VT2 中。
JOIN
第三步，如果是 OUTER JOIN(left join、right join) ，那么这一步就将添加外部行，如果是 left join 就把 ON 过滤条件的左表添加进来，如果是 right join ，就把右表添加进来，从而生成新的虚拟表 VT3。
WHERE
第四步，是执行 WHERE 过滤器，对上一步生产的虚拟表引用 WHERE 筛选，生成虚拟表 VT4。
WHERE 和 ON 的区别
- 如果有外部列，ON 针对过滤的是关联表，主表(保留表)会返回所有的列;
- 如果没有添加外部列，两者的效果是一样的;
应用
- 对主表的过滤应该使用 WHERE;
- 对于关联表，先条件查询后连接则用 ON，先连接后条件查询则用 WHERE;
GROUP BY
- 根据 group by 字句中的列，会对 VT4 中的记录进行分组操作，产生虚拟机表 VT5。果应用了group by，那么后面的所有步骤都只能得到的 VT5 的列或者是聚合函数（count、sum、avg等）。
HAVING
- 紧跟着 GROUP BY 字句后面的是 HAVING，使用 HAVING 过滤，会把符合条件的放在 VT6
SELECT
- 第七步才会执行 SELECT 语句，将 VT6 中的结果按照 SELECT 进行刷选，生成 VT7
DISTINCT
- 在第八步中，会对 TV7 生成的记录进行去重操作，生成 VT8。事实上如果应用了 group by 子句那么 distinct 是多余的，原因同样在于，分组的时候是将列中唯一的值分成一组，同时只为每一组返回一行记录，那么所以的记录都将是不相同的。
ORDER BY
- 应用 order by 子句。按照 order_by_condition 排序 VT8，此时返回的一个游标，而不是虚拟表。sql 是基于集合的理论的，集合不会预先对他的行排序，它只是成员的逻辑集合，成员的顺序是无关紧要的。
ORDER BY注意事项
排序的列名：ORDER BY 后面可以有一个或多个列名，如果是多个列名进行排序，会按照后面第一个列先进行排序，当第一列的值相同的时候，再按照第二列进行排序，以此类推。
排序的顺序：ORDER BY 后面可以注明排序规则，ASC 代表递增排序，DESC 代表递减排序。如果没有注明排序规则，默认情况下是按照 ASC 递增排序。我们很容易理解 ORDER BY 对数值类型字段的排序规则，但如果排序字段类型为文本数据，就需要参考数据库的设置方式了，这样才能判断 A 是在 B 之前，还是在 B 之后。比如使用 MySQL 在创建字段的时候设置为 BINARY 属性，就代表区分大小写。
非选择列排序：ORDER BY 可以使用非选择列进行排序，所以即使在 SELECT 后面没有这个列名，你同样可以放到 ORDER BY 后面进行排序。
ORDER BY 的位置：ORDER BY 通常位于 SELECT 语句的最后一条子句，否则会报错。
查询优化
避免使用 select * 而是查询需要的字段，这样可以提升速度，以及减少网络传输的带宽压力。
尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，但 Join 语句并不会创建临时表，因此性能会更高。
尽量使用覆盖查询。通过创建联合主键的方式减少回表操作。
不要在列上进行运算操作
适当增加冗余字段，防止多度连表查询，通常不建议join超过三张表。
最小数据长度，一般说来数据库的表越小，那么它的查询速度就越快，因此为了提高表的效率
使用最简单数据类型，能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高。
尽量少定义 text 类型，text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率。
适当分表、分库策略，分表和分库方案也是我们经常说的垂直分隔（分表）和水平分隔（分库）。
分表是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率。
分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库，一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询，这样就把一个库的读和写的压力，分摊给了多个库，从而提高了数据库整体的运行效率。
SQL优化explain
- explain出来的各种item的意义；
1 | select_type |
- 表示查询中每个select子句的类型，有几种值：simple（表示简单的select，没有union和子查询），primary（有子查询，最外面的select查询就是primary），union（union中的第二个或随后的select查询，不依赖外部查询结果），dependent union（union中的第二个或随后的select查询，依赖外部查询结果）
1 | type |
- 表示MySQL在表中找到所需行的方式，又称“访问类型”，有几种值：system（表仅有一行（=系统表），这是const连接类型的一个特例），const（常量查询）, ref(非唯一索引访问，只有普通索引)，eq_ref（使用唯一索引或组件查询），all（全表查询），index（根据索引查询全表），range（范围查询）
1 | possible_keys |
- 指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
1 | key |
- 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
1 | key_len |
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
1 | ref |
- 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
1 | Extra |
包含不适合在其他列中显示但十分重要的额外信息
profile的意义以及使用场景；
查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等。
innodb的读写参数优化
- 读取参数
1 | global buffer pool以及 local buffer； |
- 写入参数；
1 | innodb_flush_log_at_trx_commit |
- 与IO相关的参数；
1 | innodb_write_io_threads = 8 |
视图
- 视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
- 只暴露部分字段给访问者，所以就建一个虚表，就是视图。
- 查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异。
存储过程
存储过程是一些预编译的SQL语句。
- 更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。
- 存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全。
Mysql Master slave 的原理
主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
从：sql执行线程——执行relay log中的语句；
备份
备份计划，mysqldump以及xtranbackup的实现原理
备份计划
- 这里每个公司都不一样
备份恢复时间,这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考
20G的2分钟（mysqldump）
80G的30分钟(mysqldump)
111G的30分钟（mysqldump)
288G的3小时（xtra)
3T的4小时（xtra)
逻辑导入时间一般是备份时间的5倍以上
xtrabackup实现原理
- 在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。
mysqldump中备份出来的sql，如果我想sql文件中，一行只有一个insert….value()的话，怎么办？如果备份需要带上master的复制点信息怎么办？
1 | --skip-extended-insert |
扩展
MYSQL优化
SQL语句、索引优化和数据配置优化比较常见，硬件优化不是很常见。
配置优化
show variables like '%max_connections%';这个参数表示 MySQL 可以接收到的最大连接数，show status like 'Threads%';MySQL 的实际连接数innodb_buffer_pool_sizeInnoDB 存储引擎下 MySQL 的内存缓冲区大小。- 从磁盘读取数据效率是很低的，为了避免这个问题，MySQL 开辟了基于内存的缓冲池，核心做法就是把经常请求的热数据放入池中，如果请求交互的数据都在缓冲池中则会很高效，所以一般数据库缓冲池设置得会比较大，占到操作系统内存值的 70%～80%。
show status like 'Innodb_buffer_pool_read_%'
慢SQL
如果发现系统慢了，又说不清楚是哪里慢，那么就该用这个工具了。只需要为mysql配置参数，mysql会自己记录下来慢的sql语句。
long_query_timeSQL执行时间必须超过，不包括锁等待时间。
1 | #查看慢查询日志是否开启 OFF关闭 ON开启 |
- 修改my.cnf
1 | slow_query_log=1 # 设置为1打开慢日志。 |
SQL 问题中索引相关的问题也是最突出的。
show full processlist通过 show full processlist 实时获取交互的 SQL。SQL语句查询计划
explain ${SQL}
索引优化
对于数据量达到一定的表，尽量不要走扫描全表。在使用过滤条件的时候where 及 order by 涉及的列上建立索引。
查询语句无论是使用哪种判断条件 等于、小于、大于，
WHERE左侧的条件查询字段不要使用函数或者表达式使用
EXPLAIN命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分析这条 sql 语句，这样方便我们分析，进行优化。当你的 SELECT 查询语句只需要使用一条记录时，要使用
LIMIT 1不要直接使用
SELECT *，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，SELECT * 使用的是全表扫描，也就是type = all。为每一张表设置一个 ID 属性
避免在
WHERE字句中对字段进行NULL判断避免在
WHERE中使用!=或<>操作符。否则将执行全表扫描。使用
BETWEEN AND替代IN。对于连续的范围查询使用BETWEEN。为搜索字段创建索引,大字段类型不要建立索引。
选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等
使用
LIKE %abc%不会走索引，而使用LIKE abc%会走索引。SELECT * FROM tab_name WHERE index_column LIKE 'abc%' #索引是有效的 SELECT * FROM tab_name WHERE index_column LIKE '%abc' #索引是无效的 SELECT * FROM tab_name WHERE index_column LIKE '%cba' #索引是无效的 SELECT * FROM tab_name WHERE index_column LIKE '%abc%' #索引是无效的1
2
3
4
5
- 如果要提高效率，可以考虑使用全文检索。
- ```sql
select id from t with(index(索引名)) where num = @num
对于枚举类型的字段(即有固定罗列值的字段)，建议使用
ENUM而不是VARCHAR，如性别、星期、类型、类别等拆分大的 DELETE 或 INSERT 语句
选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数。
字段设计尽可能使用
NOT NULL避免在
WHERE子句中使用表达式。select id from t where num/2 = 100 # 错误的案例 select id from t where num = 100*2 # 正确的案例1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
- 进行水平切割或者垂直分割。
#### 表连接优化
##### **Nested Loop Join（NLJ）算法：**
> 基础算法:NLJ，嵌套循环算法。循环外层是驱动表，循坏内层是被驱动表。
- 驱动表会驱动被驱动表进行连接操作。首先驱动表找到第一条记录，然后从头扫描被驱动表，逐一查找与驱动表第一条记录匹配的记录然后连接起来形成结果表中的一条记。
- 被驱动表查找完后，再从驱动表中取出第二个记录，然后从头扫描被驱动表，逐一查找与驱动表第二条记录匹配的记录，连接起来形成结果表中的一条记录。
```sql
foreach row1 from t1
foreach row2 from t2
if row2 match row1 #row2与row1匹配，满足连接条件
join row1 and row2 into result #连接row1和row2加入结果集
# 首先加载t1，然后从t1中取出第一条记录，之后加载t2表，与t2表中的记录逐个匹配，连接匹配的记录。
Block Nested Loop Join(BNLJ)算法：
高级算法：BNLJ，块嵌套循环算法，可以看作对NLJ的优化。
- 建立一个缓存区joinbuffer，一次从驱动表中取多条记录，然后扫描被驱动表，被驱动表的每一条记录都尝试与缓冲区中的多条记录匹配，如果匹配则连接并加入结果集。
- 缓冲区越大，驱动表一次取出的记录就越多。这个算法的优化思路就是减少内循环的次数从而提高表连接效率。
- 其实这就是 hash join 了，MySQL 8.0 已支持。
临时表优化
MySQL在执行SQL查询时可能会用到临时表，一般情况下，用到临时表就意味着性能较低。
- 一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表。
1 | ORDERY BY price GROUP BY name |
大表连表查询
一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
- 1、如果A表TID是自增长,并且是连续的,B表的ID为索引
1 | select * from a,b where a.tid = b.id and a.tid>500000 limit 200; |
- 2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
1 | select * from b , (select tid from a limit 50000,200) a where b.id = a .tid; |
数据库的三范式是什么
- 第一范式：列不可再分，字段具备单一职责
- 第二范式：行可以唯一区分，主键约束
- 第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束
- 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
- 第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
- 第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在”A → B → C”的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y
- 且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。
Mysql 大小写问题
- 表名、表别名、字段名、字段别名都小写。SQL保留字段、函数名、绑定变量名都大写。
DDL设计数据表的原则
- 数据表越少越好
- 数据表中的字段越少越好
- 数据表中联合主键的字段越少越好
- 使用主键和外键越多越好
varchar与char的区别以及varchar(50)中的50代表的涵义
- varchar与char的区别
- CHAR和VARCHAR类型在存储和检索方面有所不同，CHAR是一种固定长度的类型，VARCHAR则是一种可变长度的类型。
- CHAR列长度固定为创建表时声明的长度，长度值范围是1到255当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。
- varchar(50)中50的涵义
- 最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)
- int（20）中20的涵义
- 是指显示字符的长度但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0
- 20表示最大显示宽度为20，但仍占4字节存储，存储范围不变。
- mysql为什么这么设计
- 对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样。
表中有大字段X(例如：text类型)，且字段X不会经常更新，以读为为主。如何拆分
拆带来的问题：连接消耗 + 存储拆分空间；不拆可能带来的问题：查询性能；
- 1、如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
- 2、如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择。
什么是临时表，何时删除临时表
1 | 在 MySQL 中，有三种类型的表 |
- MySQL 会在下面这几种情况产生临时表
- 使用 UNION 查询：UNION 有两种，一种是
UNION，一种是UNION ALL，它们都用于联合查询；区别是 使用 UNION 会去掉两个表中的重复数据，相当于对结果集做了一下去重(distinct)。使用 UNION ALL，则不会排重，返回所有的行。使用 UNION 查询会产生临时表。 - 使用
TEMPTABLE 算法或者是 UNION 查询中的视图。TEMPTABLE 算法是一种创建临时表的算法，它是将结果放置到临时表中，意味这要 MySQL 要先创建好一个临时表，然后将结果放到临时表中去，然后再使用这个临时表进行相应的查询。 - ORDER BY 和 GROUP BY 的子句不一样时也会产生临时表。
- DISTINCT 查询并且加上 ORDER BY 时；
- SQL 用到 SQL_SMALL_RESULT 选项时；如果查询结果比较小的时候，可以加上 SQL_SMALL_RESULT 来优化，产生临时表
- FROM 中的子查询；
- EXPLAIN 查看执行计划结果的 Extra 列中，如果使用
Using Temporary就表示会用到临时表。
- 使用 UNION 查询：UNION 有两种，一种是
Exists与in
- 将设A、B两表，走索引的情况下。根据A、B表的大小比较，如果A表大于B表那么IN的查询效率比EXISTS高。
1 | SELECT * FROM A WHERE cc IN (SELECT cc FROM B) |
测试环境打开profiling
- 首先我们需要看下 profiling 是否开启，开启它可以让 MySQL 收集在 SQL 执行时所使用的资源情况，命令如下：
1 | mysql> select @@profiling; |
去除重复行
- DISTINCT 需要放到所有列名的前面
1 | # 这么写会报错 |
- DISTINCT 其实是对后面所有列名的组合进行去重。
drop、delete与truncate
SQL中的drop、delete、truncate都表示删除，但是三者有一些差别
delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger。
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate
in, not in , exists , not exists 它们有什么区别，效率如何？
你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？
- 主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等。
你们数据库是否支持emoji表情，如果不支持，如何操作？
- 如果是utf8字符集的话，需要升级至utf8_mb4方可支持。
Too many connections
连接数超出 Mysql 的最大连接限制
解决方法：
- 1、在 my.cnf 配置文件里面增加连接数，然后重启 MySQL 服务。max_connections = 10000
- 2、临时修改最大连接数，重启后不生效。需要在 my.cnf 里面修改配置文件，下次重启生效。
MySQL数据库cpu飙升到100%的话怎么处理？
- 列出所有进程 show processlist,观察所有进程 ,多秒没有状态变化的(干掉)
- 查看超时日志或者错误日志 (一般会是查询以及大批量的插入会导致cpu与i/o上涨,当然不排除网络状态突然断了,导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送,,当然的一次被坑经历)。