关于mysql:Mysql-实战笔记-一-基础

一、mysql 基础架构

大体来说，MySQL 能够分为 Server 层和存储引擎层两局部。Server 层包含连接器、查问缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数外围服务性能，以及所有的内置函数（如日期、工夫、数学和加密函数等），所有跨存储引擎的性能都在这一层实现，比方存储过程、触发器、视图等。

连接器

一个用户胜利建设连贯后，即便你用管理员账号对这个用户的权限做了批改，也不会影响曾经存在连贯的权限 。批改实现后，只有再新建的连贯才会应用新的权限设置。
建设连贯的过程通常是比较复杂的，所以我倡议你在应用中要尽量减少建设连贯的动作，也就是尽量应用长连贯。然而全副应用长连贯后，你可能会发现，有些时候 MySQL 占用内存涨得特地快，这是因为 MySQL 在执行过程中长期应用的内存是治理在连贯对象外面的 。这些资源会在连贯断开的时候才开释。所以如果长连贯累积下来，可能导致内存占用太大，被零碎强行杀掉（OOM），从景象看就是 MySQL 异样重启了。
怎么解决这个问题呢？你能够思考以下两种计划。

1. 定期断开长连贯。应用一段时间，或者程序外面判断执行过一个占用内存的大查问后，断开连接，之后要查问再重连。
2. 如果你用的是 MySQL 5.7 或更新版本，能够在每次执行一个比拟大的操作后，通过执行 mysql_reset_connection 来从新初始化连贯资源。这个过程不须要重连和从新做权限验证，然而会将连贯复原到刚刚创立完时的状态。

查问缓存

大多数状况下不要应用查问缓存

分析器

词法剖析，谬误时会收到 “You have an error in your SQL syntax”，也会在这个阶段判断查问条件是否蕴含在这个表。在分析阶段判断语句是否正确，表是否存在，列是否存在等。

优化器

优化器是在表外面有多个索引的时候，决定应用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连贯程序。

执行器

开始执行的时候，要先判断一下你对这个表有没有 执行查问的权限 ，如果没有，就会返回没有权限的谬误。
如果有权限，就关上表继续执行。关上表的时候，执行器就会依据表的引擎定义，去应用这个引擎提供的接口。
为什么不在执行器之前判断权限？ 有些时候，SQL 语句要操作的表不只是 SQL 字面上那些。比方如果有个触发器，得在执行器阶段（过程中）能力确定。优化器阶段前是无能为力的

二、日志零碎

redo log (InnoDB 引擎特有的日志)

WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是 先写日志，再写磁盘 。当有一条记录须要更新的时候，InnoDB 引擎就会先把记录写到 redo log 外面，并更新内存，这个时候更新就算实现了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘外面，而这个更新往往是在零碎比拟闲暇的时候做。
redo log 用于保障 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，示意每次事务的 redo log 都间接长久化到磁盘。
工夫轮

binlog(server 层的日志)

这两种日志有以下三点不同。

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都能够应用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么批改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比方“给 ID=2 这一行的 c 字段加 1”。
3. redo log 是循环写的，空间固定会用完；binlog 是能够追加写入的。“追加写”是指 binlog 文件写到肯定大小后会切换到下一个，并不会笼罩以前的日志。
4. redo log 记录数据页“做了什么改变”，Binlog 有两种模式，statement 格局的话是记 sql 语句，row 格局会记录行的内容，记两条，更新前和更新后都有。

浅色框示意是在 InnoDB 外部执行的，深色框是在执行器执行

两阶段提交

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是 ” 两阶段提交 ”。
个别备份都是全量备份加上 binlog
sync_binlog 这个参数设置成 1 的时候，示意每次事务的 binlog 都长久化到磁盘。

三、事务隔离

隔离性与隔离级别

当数据库上有多个事务同时执行的时候，就可能呈现脏读（dirty read）、不可反复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其余事务看到。
可反复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是统一的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当呈现读写锁抵触的时候，后拜访的事务必须等前一个事务执行实现，能力继续执行。

在实现上，数据库外面会创立一个 视图 ，拜访的时候以视图的逻辑后果为准。
在“可反复读”隔离级别下，这个视图是在事务启动时创立的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创立的。
这里须要留神的是，“读未提交”隔离级别下间接返回记录上的最新值，没有视图概念
而“串行化”隔离级别下间接用加锁的形式来防止并行拜访。

事务隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都能够失去前一个状态的值。

同一条记录在零碎中能够存在多个版本，就是数据库的多版本并发管制（MVCC）
回滚日志的删除，当零碎里没有比这个回滚日志更早的 read-view 的时候。
长事务意味着零碎外面会存在很老的事务视图

四、索引

哈希表这种构造实用于只有等值查问的场景。
有序数组在等值查问和范畴查问场景中的性能就都十分优良。有序数组索引只实用于动态存储引擎。

基于主键索引和一般索引的查问有什么区别？
如果语句是 select * from T where ID=500，即主键查问形式，则只须要搜寻 ID 这棵 B + 树；
如果语句是 select * from T where k=5，即一般索引查问形式，则须要先搜寻 k 索引树，失去 ID 的值为 500，再到 ID 索引树搜寻一次。这个过程称为回表。
基于非主键索引的查问须要多扫描一棵索引树。
drop 主键索引会导致其余索引生效，但 drop 一般索引不会

索引保护

插入数据的时候，依据 B+ 树的算法，这时候须要申请一个新的数据页，而后移动局部数据过来。这个过程称为页决裂。在这种状况下，性能天然会受影响。除了性能外，页决裂操作还影响数据页的利用率。本来放在一个页的数据，当初分到两个页中，整体空间利用率升高大概 50%。
自增主键的插入数据模式，正合乎了咱们后面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不波及到移动其余记录，也不会触发叶子节点的决裂。
主键长度越小，一般索引的叶子节点就越小，一般索引占用的空间也就越小。

笼罩索引

如果当初有一个高频申请，要依据市民的身份证号查问他的姓名，建设联结索引就有意义了(利用笼罩索引减速高频查问)。它能够在这个高频申请上用到笼罩索引，不再须要回表查整行记录，缩小语句的执行工夫。当然，索引字段的保护总是有代价的。因而，在建设冗余索引来反对笼罩索引时就须要衡量思考了。

最左前缀准则

为什么会有最左前缀？
索引项是依照索引定义外面呈现的字段程序排序的
因为能够反对最左前缀，所以当曾经有了 (a,b)这个联结索引后，个别就不须要独自在 a 上建设索引了。
在建设联结索引的时候，如何安顿索引内的字段程序?
第一准则是，如果通过调整程序，能够少保护一个索引，那么这个程序往往就是须要优先思考采纳的。
如果有 b 也必须要建索引，那么思考的准则就是空间了，比方，name 字段是比 age 字段大的，那我就倡议你创立一个（name,age) 的联结索引和一个 (age) 的单字段索引

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)，能够在索引遍历过程中，对索引中蕴含的字段先做判断，间接过滤掉不满足条件的记录，缩小回表次数。
select * from tuser where name like '张 %' and age=10

无索引下推

有索引下推
它们的区别是，InnoDB 在 (name,age) 索引外部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。

重建索引合不合理？
索引可能因为删除，或者页决裂等起因，导致数据页有空洞，重建索引的过程会创立一个新的索引，把数据按程序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。然而，重建主键的过程不合理。不论是删除主键还是创立主键，都会将整个表重建。

五、锁

全局锁

让 mysql 只读，其余线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包含建表、批改表构造等）和更新类事务的提交语句。
全局锁的典型应用场景是，做全库逻辑备份。
Flush tables with read lock (FTWRL)
set global readonly=true
一是，在有些零碎中，readonly 的值会被用来做其余逻辑，比方用来判断一个库是主库还是备库。因而，批改 global 变量的形式影响面更大，我不倡议你应用。
二是，在异样解决机制上有差别。如果执行 FTWRL 命令之后因为客户端产生异样断开，那么 MySQL 会主动开释这个全局锁，整个库回到能够失常更新的状态。而将整个库设置为 readonly 之后，如果客户端产生异样，则数据库就会始终放弃 readonly 状态，这样会导致整个库长时间处于不可写状态，危险较高。

表级锁

MySQL 外面表级别的锁有两种：一种是 表锁 ，一种是 元数据锁（meta data lock，MDL)。
表锁的语法是 lock tables … read/write。与 FTWRL 相似，能够用 unlock tables 被动开释锁，也能够在客户端断开的时候主动开释。须要留神，lock tables 语法除了会限度别的线程的读写外，也限定了本线程接下来的操作对象。
MDL 不须要显式应用 ，在拜访一个表的时候会被主动加上。MDL 的作用是，保障读写的正确性。
当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做构造变更操作的时候，加 MDL 写锁 。

session C 会被 blocked，是因为 session A 的 MDL 读锁还没有开释，而 session C 须要 MDL 写锁，因而只能被阻塞。
如果某个表上的查问语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再申请的话，这个库的线程很快就会爆满。
如何平安地给小表加字段?
首先咱们要解决长事务，事务不提交，就会始终占着 MDL 锁。比拟现实的机制是，在 alter table 语句外面 设定等待时间，如果在这个指定的等待时间外面可能拿到 MDL 写锁最好，拿不到也不要阻塞前面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令反复这个过程。

行锁

在 InnoDB 事务中，行锁是在须要的时候才加上的，但并不是不须要了就立即开释，而是要等到事务完结时才开释。这个就是两阶段锁协定。
如果一个事务中须要锁多个行，要把最可能造成锁抵触、最可能影响并发度的锁尽量往一个事务最初放。因为这样抵触锁的工夫会起码

死锁和死锁检测

一种策略是，间接进入期待，直到超时。这个超时工夫能够通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发动死锁检测，发现死锁后，被动回滚死锁链条中的某一个事务，让其余事务得以继续执行, 而后通过重试死锁业务。 将参数 innodb_deadlock_detect 设置为 on，示意开启这个逻辑。如果要加锁拜访的行上有锁，才会检测
怎么解决由这种热点行更新导致的性能问题呢？ 问题的症结在于，死锁检测要消耗大量的 CPU 资源
1. 管制并发度
2. 也能够思考通过将一行改成逻辑上的多行来缩小锁抵触

深刻事务隔离

consistent read view，即 InnoDB 在 实现 MVCC 时用到的一致性读视图 ，用于反对 RC（Read Committed，读提交）和 RR（Repeatable Read，可反复读）隔离级别的实现。 它没有物理构造 ，作用是事务执行期间用来定义“我能看到什么数据”。
在可反复读隔离级别下，事务在启动的时候就“拍了个快照”。留神，这个快照是基于整库的。
InnoDB 外面每个事务有一个 惟一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务零碎申请的，是按申请程序严格递增的。而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id(row trx_id 就是事务 id)。同时，旧的数据版本要保留，并且在新的数据版本中，可能有信息能够间接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有本人的 rowtrx_id

语句更新会生成 undo log（回滚日志）
图中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上实在存在的，而是每次须要的时候依据以后版本和 undo log 计算出来的。比方，须要 V2 的时候，就是通过 V4 顺次执行 U3、U2 算进去。
InnoDB 的行数据有多个版本，每个数据版本有本人的 row trx_id，每个事务或者语句有本人的一致性视图。一般查问语句是一致性读，一致性读会依据 row trx_id 和一致性视图确定数据版本的可见性。
** 对于可反复读，查问只抵赖在事务启动前就曾经提交实现的数据；
对于读提交，查问只抵赖在语句启动前就曾经提交实现的数据；**
而以后读，总是读取曾经提交实现的最新版本。