MySQL原理介绍，你学会了吗？-51CTO.COM

一、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 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
不能使用 HASH 索引排序。
HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
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等命令直接打包复制数据库的数据文件达到备份的效果

冷备份：冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

逻辑备份：逻辑备份是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

热备份：热备份指的是当数据库进行备份时, 数据库的读写操作均不受影响

温备份：温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作

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加锁有次序。

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

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