MySQL原理介绍

一、Mysql中有哪几种锁？

1）表级锁

开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

2）行级锁

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

3）页面锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

二、MySQL索引类型

1）存储方式区分

根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。

1、B-Tree索引

它使用B-Tree数据结构来存储数据，实际上很多存储引擎使用的是B+Tree。B+Tree和B-Tree的不同点在于：

非叶子节点只存储键值信息
所有叶子节点之间都有链指针
数据记录都存放在叶子节点中
B-Tree是为磁盘等外存储设备设计的一种平衡多路查找树。

B-Tree模型（InnoDB）：

B+Tree模型（InnoDB）：

B-Tree索引与B+Tree索引的区别

B-树索引的特点：

所有键值分布在整个树中
任何关键字出现且只出现在一个节点中
搜索有可能在非叶子节点结束
在关键字全集内做一次查找，性能逼近二分查找算法

B+树索引与B-树索引的不同在于：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。

B+Tree对比BTree的优点：

磁盘读写代价更低

那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

查询速度更稳定

由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

2、HASH 索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。
哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。
HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：
1. MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
2. 不能使用 HASH 索引排序。
3. HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
4. HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

2）逻辑区分

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

1、普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
普通索引允许在定义索引的列中插入重复值和空值。
创建普通索引时，使用的关键字是 INDEX。

【示例】

CREATE INDEX index_id ON tb_student(id);

2、唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引使用 UNIQUE INDEX 关键字。

【示例】

CREATE UNIQUE INDEX index_id ON tb_student(id);

3、主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

4、全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和空值。
不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT INDEX 关键字。

【示例】

CREATE FULLTEXT INDEX index_info ON tb_student(info);

其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

5、空间索引

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。
创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。
空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。
创建普通索引时，使用的关键字是 SPATIAL INDEX。

【示例】

CREATE SPATIAL INDEX index_line ON tb_student(line);

3）实际使用区分

1、单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

示例

CREATE INDEX index_addr ON tb_student(address(4));

2、多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

示例

CREATE INDEX index_na ON tb_student(name,address);

三、MySQL数据库中MyISAM和InnoDB的区别

1）MyISAM

不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作是对整个表加锁；
存储表的总行数，查询总数很快；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
可被压缩，存储空间较小；支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
不支持外键；
支持 FULLTEXT类型的全文索引；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

2）InnoDB

支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
备份不方便，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了。
不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。叶节点data域保存了完整的数据记录。

四、事务的四大特性（ACID）

1）原子性（Atomicity）

原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个 SQL 语句执行失败，已经执行成功的 SQL 语句也必须撤销，数据库状态应该退回到执行事务前的状态。

2）一致性（consistency）

一致性指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

3）隔离性（isolation）

一个事务的影响在该事务提交前对其他事务都不可见——这通过锁来实现。

四种隔离级别

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容，脏读，不可重复读）

一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

4）持久性（durability）

事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

五、主从同步机制

1）主从同步过程

主从同步过程中主服务器有一个工作线程I/O dump thread，从服务器有两个工作线程I/O thread和SQL thread。

主库把外界接收的SQL请求记录到自己的binlog日志中（查询操作不记录）；
从库的I/O thread去请求主库的binlog日志，并将binlog日志写到中继日志中；
最后从库SQL thread重做中继日志的SQL语句。

2）复制原理

1、异步复制

异步复制是MySQL默认方式，主库写入binlog日志后即可成功返回客户端，无须等待binlog日志传递给从库的过程，但是一旦主库宕机，就有可能出现丢失数据的情况。

2、半同步复制

MySQL默认的复制方式是异步复制，但是当主库宕机，在高可用架构做准备切换，就会造成新的主库丢失数据的现象。
MySQL5.5版本之后引入了半同步复制，但是主从服务器必须同时安装半同步复制插件。在该功能下，确保从库接收完成主库传递过来的binlog内容已经写入到自己的relay log后才会通知主库上面的等待线程。如果等待超时(超时参数：rpl_semi_sync_master_timeout)，则关闭半同步复制，并自动转换为异步复制模式，直到至少有一台从库通知主库已经接收到binlog信息为止。
半同步复制提升了主从之间数据的一致性，让复制更加安全可靠，在5.7 版本中又增加了rpl_semi_sync_master_wait_point参数，用来控制半同步模式下主库返回给session事务成功之前的事务提交方式。

六、数据备份

1）备份方式

物理备份：一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果
1. 冷备份：冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
逻辑备份：逻辑备份是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。
1. 热备份：热备份指的是当数据库进行备份时, 数据库的读写操作均不受影响
2. 温备份：温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作

2）备份工具

mysqldump：逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
cp, tar 等归档复制工具 ：物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份
lvm2 snapshot：几乎热备, 借助文件系统管理工具进行备份
mysqlhotcopy：名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎
xtrabackup：一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

3）备份策略

针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下几种：

直接cp,tar复制数据库文件（物理备份，冷备）：适合数据量小。
lvm2快照+复制BIN LOGS（逻辑备份，热备）：适合数据量一般，使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果。
mysqldump+复制BIN LOGS（逻辑备份，热备）：适合数据量中等，先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果。
xtrabackup（逻辑备份，热备）：适合数据量很大，使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份。

七、MySQL死锁及解决方案

MySQL死锁产生原因

所谓死锁：是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

产生死锁的四个必要条件：

互斥条件：一个资源每次只能被一个进程使用。
请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。

死锁解决方案

【原因】

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

【解决】

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

最大限度的降低死锁方法：

按同一顺序访问对象。
避免事务中的用户交互。
保持事务简短并在一个批处理中。
使用低隔离级别。
使用绑定连接。