MySQL数据库

1、MyIsam和InnoDB的区别
1、MyIsam不支持行级锁，只支持表级锁。InnoDB支持行级锁和表级锁
2、是否支持事务和奔溃后的安全恢复：MyIsam是原子读的操作，速度快。Innode支持事务，外部键等高级功能。InnoDB支持事务具有事务、回滚奔溃后安全恢复能力
3、是否支持外键：MyISAM不支持外键，Innode支持外键

字符集及校对规则

字符集：指从二进制编码到某类字符符号的映射
校对规则：指的是字符的排序规则

索引

MySQL索引引用的数据结构主要有BTree索引和哈希索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数数需求为单条记录查询的时候，可以选择哈希索引，查询性能较快；其余大部分场景，建议选择BTree索引

B树与B+树常用于数据库和操作系统的文件系统中。NTFS、ReiserFS等文件系统都在使用B+树作为元数据索引。B+树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。

MySQL的BTree索引使用的是B树中的B+Tree。但是对于主要的两种存储引擎的实现方式是不同的；

• MyISAM: B+Tree叶节点的data域存放的数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”
• InnoDB：其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的。其表数据本身就是按照B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这被称为“聚簇索引”，而其余索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不需要使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

聚簇索引
InnoDB的表都拥有一个索引，称之为聚簇索引，此索引中存储着行记录，一般来说，聚簇索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作，理解InnoDB聚簇索引是根据主键生成的。

聚簇索引整体是一个b+树，非叶子节点存放的是键值，叶子节点存放的是行数据，称之为数据页，这就决定了表中数据也是聚簇索引中的一部分，数据页通过双向链表来链接的。

辅助索引
除了聚簇索引之外的索引可以称之为辅助索引，与聚簇索引的区别在于辅助索引的叶子节点中存放的是主键的键值。

MySQL可分为server层和引擎层

server层

连接器
负责与客户端建立链接、获取权限、维持和管理连接
长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接
短连接：每次执行完很少的几次查询就断开连接，下次查询再连接一个

长连接占用大内存，可用的方法：①定期断开长连接   ②执行大操作后重新初始化

查询缓存
查询：之前执行过的语句和其结果都会以key-value的形式，被直接存在内存中。
不实用，因为数据库更新快，缓存内也要频繁删除
更新：删除查询缓存

分析器
语句解析。解析SQL语句的正确性和操作

优化器
在表内有多个索引的时候，决定要实用哪个索引；或者一个语句有多表关联的时候，决定各个表的连接顺序。
选择方案

执行器
查询：执行顺序。①查询权限 ②调用存储引擎 ③遍历表选择所有满足条件的作为结果集返回客户端
更新：

存储引擎

数据的存储和提取，架构模式是插件式的，支持InnoDB、MyISAM、Memery等多个存储引擎。

日志系统

如果每一次更新都写入磁盘，然后磁盘也要找到对应的那条记录，然后更新，整个IO成本，查找成本都很高。
WAL技术：Write-ahead loggin：它的关键点就是先写日志，再写磁盘。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

重要的日志模块
为什么会有两份日志呢？
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

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

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

redolog和binlog的二阶段提交：

事务隔离

事务的特性：ACID
原子性：一个事务要么内部的操作全部成功，要么全部失败
一致性：事务执行前后，需要从一个一致性状态转向另一个一致性状态。
隔离性：一个事务对于另一个事物的执行没有干扰，互相隔离。
持久性：只要事务进行了提交，及时还没有提交到磁盘，也能够进行恢复。

多事务执行的时候可能会出现：脏读、不可重复读、幻读的问题

SQL 标准的事务隔离级别包括：读未提交（readuncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。
读未提交：一个事务未提交的更改可以被另一个事务读取
读已提交：事务只能读取另一个事务已提交的结果。解决脏读
可重复读：事务执行期间读取到的数据和执行开始读到的数据是一样的，解决不可重复读
串行化：使用锁机制，在我这个事务对这个数据没处理完之前，别的事务都不能用。解决所有的问题

事务隔离的实现—可重复读
每条记录在更新的时候都会同时记录一条回滚。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

当前值是4，但是不同时刻启动的事务会有不同的read_view。如上视图A\B\C记录的是1,2，4；
每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）

回滚日志什么时候删除？
再不需要的时候才删除回滚日志，系统会判断，当没有事务再需要这个回滚日志的时候，回滚日志就会被删除。

什么时候不需要了呢？
当系统里没有比这个回滚日志更早的read-view的时候

不建议使用长事务？
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间

事务的启动方式
1、显示启动，begin或者start transaction。配套语句是commit，回滚语句是rollback
2、set autocommit=0 这个命令会将线程的自动提交关闭，意味着如果你只执行一个select语句，这个事务就启动，但是会持续到你主动执行commit或者rollback语句，或者断开连接

因此，我会建议你总是使用 set autocommit=1, 通过显式语句的方式来启动事务

使用commit work and chain来解决显示启动需要多一句begin的交互，即在提交事务的时候启动下一个事务。

事务的配置方式 ： transaction-isolation

如何避免长事务对业务的影响？
这个问题，我们可以用应用开发端和数据库端来看。
首先从应用开发端来看：
1. 确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
2. **确认是否有不必要的只读事务。**有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）
从数据库端来看：
1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
2. Percona 的 pt-kill 这个工具不错，推荐使用；
3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成
2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方
便。

深入浅出索引（上）

为什么要应用索引？
索引的出现就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型
**哈希表：**采用key-value的数据存储结构，只需要输入待查找的key的值，就可以找到其对应的value的值。哈希的思路简单，把值放到数组里，用一个哈希函数将key值映射成数组下标索引，然后再把value放到数组的这个位置。
**好处：**增加新数据和查找数据很快
**缺点：**因为存储不是有序的，所以做区间索引会很慢

有序数组：
优点：适用于静态存储引擎，比如要保存的是2017年某个城市所有人口的信息，不会进行修改。
缺点：缺点在中间插入的时候，就需要更改后面的所有数据。

二叉搜索树
为了维持O（logN）的查询复杂度，就需要这颗平衡二叉树
二叉树的效率是最高的，但是实际上大多数使用的数据库存储却不使用二叉树，其原因是，索引不知在内存中，还在磁盘中。
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

B+树和B树还有二叉树的对比：

• B树和B+树都是多路查找树，目的都是为了解决多次访问磁盘，IO耗时太长的问题；
• 如果采用二叉树，虽然查找的时间复杂度没变，但是由于每一个节点可能在不同的页上，因此访问磁盘的次数增多。
• B+树进一步优化了磁盘访问次数，将索引和数据分开存储，因此每一个页中存储的索引更多。
• B+树的叶节点增加了指针链接，可以查询效率更高；
• 但同样的，B+树对查询的优化操作会降低了一定写入性能。

InnoDB索引模型
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+
树中的。

假设，有一个主键为ID的表，表中有字段k，并且在k中有索引

根据叶子节点的内容，索引类型分为主键索引和非主键索引；
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引
（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引
（secondary index）。

基于主键索引和普通索引的查询有什么区别？
主键索引可以直接找到所在行的数据。而非主键索引需要先在辅助索引树中找到主键索引，再查找主键索引树种查找，这个过程称为回表。

索引维护
B+树为了维护索引的有序性，在插入新值的时候做有必要的维护。
如果直接在一个叶子节点后追加，就只需要在后面追加一格记录。
但是如果在叶子节点中间插入值，就需要逻辑上挪动后面的数据，空出位置。更糟糕的是，如果后面数据页已经满了，根据B+树的算法，就需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。 分裂页会影响性能，页分裂操作也会影响数据页的利用率。
有分裂也就有合并，当两个页由于删除了数据，利用率很低之后，也会将数据页做合并。

为什么有些建议表语句中一定要有自增主键？
自增主键是指自增列上定义的主键，插入新记录的时候可以不指定ID值，系统会获取当前的ID最大值加1作为下一条记录的ID值。
自增主键的每次插入都是追加操作，不涉及其他记录的挪动，也不会出发叶子节点的分裂。

从存储角度看，如果一个业务的字段主键值很大，则会导致辅助索引叶子节点占用大量的内存。显然，主键长度越小，普通索引的叶子结点就越小，普通索引占用的空间也就越小。

有没有什么场景适合用业务字段直接做主键的呢？
1、只有一个索引 2、该索引必须是唯一的索引

重建索引？
索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序从插入，这样页面的利用率最高，也就是索引最紧凑，更省空间。
重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。

深入浅出索引（下）

经过索引优化，避免回表的过程？
覆盖索引：由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。比如语句只需要知道ID的值，就可以使用覆盖索引，这时候只需要查询ID的值，而ID值就已经在K索引树上了。
还可建立**“联合索引”**，联合索引的索引维护总是有代价的。因此，在建立冗余索引来支持索引覆盖就需要权衡考虑了。这正是业务DBA，或者成为业务数据架构师的工作。

最左前缀原则：
B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

在建立联合索引的时候，如何安排索引内的字段顺序?
索引项是按照索引定义里面出现的字段顺序排序的
不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这
个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

在建立联合索引的时候，如何安排索引内的字段顺序？
第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

索引下推
我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

全局锁和表锁：给表加个字段怎么有怎么多障碍？
根据加锁的范围，可以分为全局锁、表锁和行锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法命令是Flush table with read lock(FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的一下语句就会被阻塞：数据更新（增删改），数据定义语句（建表、修改表结构）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整个库每个表都select出来存成文本。

**single-transaction方法用于库备份，但是它适用于所有的表使用事务引擎的库。**如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员
使用 InnoDB 替代 MyISAM 的原因之一。
既然要全库只读，为什么不使用 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（metadata lock)。 MDL 不需要显式使用，在访问一个表的时
候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
当对一个表做增删改查操作的时候，加 MDL 读锁（多个线程可以对表进行增删改查）；当要对表做结构变更操作的时候，加 MDL 写锁。

如何安全地给小表加字段？
首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
**定时，**拿不到表就放弃。在 alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

行锁功过：怎么减少行锁对性能的影响？

MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引

擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。
在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段行锁协议。

这就导致，如果你的事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的锁尽量往后放。

死锁和死锁检测
当并发系统中，不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

解决死锁的两种策略：
一，进入等待，直到超时，这个超时参数可以通过参数innodb_lock_wait_timeout 来设置。
二、**发起死锁检测，**发现死锁后，主动回滚死锁链条中的某个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

怎么解决由这种热点行更新导致的性能问题呢？
一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检
测关掉。
另一个思路是控制并发度。并发控制要做在数据库服务器。可是使用中间件、修改MySQL实现。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样InnoDB内部就不会有大量的死锁检测工作了。
有一个简单的办法是将一行改成逻辑上的多行来减少锁冲突。

事务到底是隔离还是不隔离的？

解说“当前读”、“提交读”和“一致性读（可重复读）”

begin/start stransation命令并不是一个事务的起点，在执行到它们的第一个操作InnoDB表的语句，事务才真正启动。如果要马上启动一个事务，可以使用starttransaction with consistent snashot这个命令。

在MySQL里，有两个“视图”的概念：
一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结
果。创建视图的语法是 create view … ，而它的查询方法与表一样。
另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，
用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔
离级别的实现。

“快照”在 MVCC 里是怎么工作的？
在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整
库的。
InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。
也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row
trx_id。

undo log 在哪呢？
InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交
数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1记为高水位。
这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数
   据是可见的；
2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在黄色部分，那就包括两种情况
   a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
   b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。
   InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

更新逻辑
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）
除了 update 语句外，select 语句如果加 锁，也是当前读。
mysql> select k from t where id=1 lock in share mode; 读锁(S 锁，共享锁)
mysql> select k from t where id=1 for update; 写锁(X 锁，排他锁)

	这里我们提到了一个概念，叫作当前读。其实，除了 **update 语句**外，select 语句如果**加锁**，也是**当前读**。

事务的可重复读的能力是怎么实现的？
可重复读的核心就是一致性读；而事务更新数据的时候，只能用当前读。如果当前记录的行锁被其他可重复读的事务占用的话，就需要进入锁等待。

读提交和可重复读的逻辑类似，它们最主要的区别是：
在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这一个一致性视图；
在读提交隔离级别下，每个语句执行前都会重新计算一个新的视图。

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有
自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性
对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；

而当前读，总是读取已经提交完成的最新版本。并且如果有写锁就会进行等待，等待写锁释放才能够读到最新的提交