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加锁有次序。
最大限度的降低死锁方法:
- 按同一顺序访问对象。
- 避免事务中的用户交互。
- 保持事务简短并在一个批处理中。
- 使用低隔离级别。
- 使用绑定连接。