MySQL原理介绍,你学会了吗?

一、Mysql中有哪几种锁?

1)表级锁

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

成都创新互联专业为企业提供旌德网站建设、旌德做网站、旌德网站设计、旌德网站制作等企业网站建设、网页设计与制作、旌德企业网站模板建站服务,10余年旌德做网站经验,不只是建网站,更提供有价值的思路和整体网络服务。

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语句实现数据库数据的重现。

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

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

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。

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

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

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

死锁解决方案

【原因】

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

【解决】

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

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

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

分享题目:MySQL原理介绍,你学会了吗?
转载注明:http://www.stwzsj.com/qtweb/news20/16770.html

网站建设、网络推广公司-创新互联,是专注品牌与效果的网站制作,网络营销seo公司;服务项目有等

广告

声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联