logo头像

待到风起时,扬帆济沧海

mysql总结

本文于545天之前发表,文中内容可能已经过时。

MySQL复制原理及流程

主从复制

  1. mysql启动以后会存在2个进程,一个是sqlThred进程一个IOThred进程
  2. 在Slave 服务器上执行sart slave命令开启主从复制开关,开始进行主从复制
  3. Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器,并请求从执行binlog日志文件的指定位置之后开始发送binlog日志内容
  4. Master服务器接收到来自Slave服务器的IO线程的请求后,二进制转储IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外,还有在master服务器端记录的新的binlog文件名称,以及在新的binlog中的下一个指定更新位置。
  5. 当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后,会将binlog日志内容依次写到Slave端自身的Relay Log(即中继日志)文件(MySQL-relay-bin.xxx)的最末端,并将新的binlog文件名和位置记录到master-info文件中,以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
  6. Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容,然后及时把Relay LOG 文件中的内容解析成sql语句,并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句,并在relay-log.info中记录当前应用中继日志的文件名和位置点

MySQL多少种日志

  • 错误日志:记录出错信息,也记录一些警告信息或者正确的信息。
  • 查询日志:记录所有对数据库请求的信息,不论这些请求是否得到了正确的执行。
  • 慢查询日志:设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
  • 二进制日志:记录对数据库执行更改的所有操作。
  • 中继日志:
  • 事务日志:

MySQL中myisam与innodb的区别

  • InnoDB支持事物,而MyISAM不支持事物
  • InnoDB支持行级锁,而MyISAM支持表级锁
  • InnoDB支持MVCC, 而MyISAM不支持
  • InnoDB支持外键,而MyISAM不支持
  • InnoDB不支持全文索引,而MyISAM支持(5.6版本已经支持了)
  • MYSQL引擎的文件包含(.frm-表结构文件、.myd-表数据文件,.myi-表索引文件)
  • InnoDB引擎包含文件(*.frm-表结构文件,ibd-数据和索引文件)

MVCC解释

  • MVCC (Multiversion Concurrency Control),即多版本并发控制技术,它使得大部分支持行锁的事务引擎,不再单纯的使用行锁来进行数据库的并发控制,取而代之的是把数据库的行锁与行的多个版本结合起来,只需要很小的开销,就可以实现非锁定读,从而大大提高数据库系统的并发性能

  • 读锁:也叫共享锁、S锁,若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S 锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

  • 写锁:又称排他锁、X锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

  • 表锁:操作对象是数据表。Mysql大多数锁策略都支持(常见mysql innodb),是系统开销最低但并发性最低的一个锁策略。事务t对整个表加读锁,则其他事务可读不可写,若加写锁,则其他事务增删改都不行。

  • 行级锁:操作对象是数据表中的一行。是MVCC技术用的比较多的,但在MYISAM用不了,行级锁用mysql的储存引擎实现而不是mysql服务器。但行级锁对系统开销较大,处理高并发较好。

MYSQL事物

数据库事务特性

  1. 原子性
  2. 一致性
  3. 隔离性
  4. 持久性

    数据库的隔离级别

  5. 读未提交(S锁)
  6. 读已提交(U锁)
  7. 可重复读(X锁)
  8. 序列化(X锁)

事物是如何通过日志实现的

  1. 事务日志是通过redo(重做日志)和innodb的存储引擎日志缓冲(Innodb log buffer)来实现的,
  2. 当开始一个事务的时候,会记录该事务的lsn(log sequence number)号;
  3. 当事务执行时,会往InnoDB存储引擎的日志的日志缓存里面插入事务日志;
  4. 当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”

MySQL binlog的几种日志录入格式以及区别

Statement:每一条会修改数据的sql都会记录在binlog中

  • 优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。
  • 缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的 一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题(如sleep()函数, last_insert_id(),以及user-defined functions(udf)会出现问题).

Row:不记录sql语句上下文相关信息,仅保存哪条记录被修改。

  • 优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下 每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题
  • 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比 如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。

Mixedlevel:是以上两种level的混合使用

一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则 采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择 一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的 变更。

1
2
3
4
5
6
7
8
9
...............................................................................
# at 552
#131128 17:50:46 server id 1 end_log_pos 665 Query thread_id=11 exec_time=0 error_code=0 ---->执行时间:17:50:46;pos点:665
SET TIMESTAMP=1385632246/*!*/;
update zyyshop.stu set name='李四' where id=4 ---->执行的SQL
/*!*/;
# at 665
#131128 17:50:46 server id 1 end_log_pos 692 Xid = 1454 ---->执行时间:17:50:46;pos点:692
...............................................................................

MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的?

InnoDB是基于索引来完成行锁
select * from tab_with_index where id = 1 for update;
or update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

坑:InnoDB的行锁是实现在索引上的,而不是锁在物理行记录上。潜台词是,如果访问没有命中索引,也无法使用行锁,将要退化为表锁。InnoDB务必建好索引,否则锁粒度较大,会影响并发。

悲观锁和乐观锁

  • 乐观锁(Optimistic Concurrency Control,OCC):假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。
    典型的例子就是表锁

  • 悲观锁(Pessimistic Concurrency Control,PCC):假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。
    典型的例子就是行锁 select * from LostUpdate where id =1 for update

mysql的索引方法btree和hash的区别

hash

  1. memory引擎才显示的支持Hash索引,innoDb默认是B+树
  2. Hash 索引仅仅能满足”=”,”IN”和”<= & >=”查询,不能使用范围查询。
  3. Hash 索引无法被用来避免数据的排序操作。
  4. Hash 索引不能利用部分索引键查询。
  5. Hash 索引在任何时候都不能避免表扫描。
  6. Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

    btree

与hash的缺点就是btree的特点

哈希(hash)比树(tree)更快,索引结构为什么要设计成树型?

加速查找速度的数据结构,常见的有两类:
(1)哈希,例如HashMap,查询/插入/修改/删除的平均时间复杂度都是O(1);
(2)树,例如平衡二叉搜索树,查询/插入/修改/删除的平均时间复杂度都是O(lg(n));
可以看到,不管是读请求,还是写请求,哈希类型的索引,都要比树型的索引更快一些,那为什么,索引结构要设计成树型呢?

索引设计成树形,和SQL的需求相关。

对于这样一个单行查询的SQL需求:
select * from t where name=”shenjian”;
确实是哈希索引更快,因为每次都只查询一条记录。
画外音:所以,如果业务需求都是单行访问,例如passport,确实可以使用哈希索引

但是对于排序查询的SQL需求
分组:group by 排序:order by 比较:<、>
哈希型的索引,时间复杂度会退化为O(n),而树型的“有序”特性,依然能够保持O(log(n)) 的高效率。

数据库索引为什么使用B+树?

为了保持知识体系的完整性,简单介绍下几种树。

二叉搜索树

二叉搜索树
二叉搜索树,如上图,是最为大家所熟知的一种数据结构,就不展开介绍了,它为什么不适合用作数据库索引?
(1)当数据量大的时候,树的高度会比较高,数据量大的时候,查询会比较慢;
(2)每个节点只存储一个记录,可能导致一次查询有很多次磁盘IO;

B树

B树
B树,如上图,它的特点是:
(1)不再是二叉搜索,而是m叉搜索;
(2)叶子节点,非叶子节点,都存储数据;
(3)中序遍历,可以获得所有节点;

B树被作为实现索引的数据结构被创造出来,是因为它能够完美的利用“局部性原理”。

什么是局部性原理?

局部性原理的逻辑是这样的:

  1. 内存读写块,磁盘读写慢,而且慢很多;
  2. 磁盘预读:磁盘读写并不是按需读取,而是按页预读,一次会读一页的数据,每次加载更多的数据,如果未来要读取的数据就在这一页中,可以避免未来的磁盘IO,提高效率;(通常,一页数据是4K)
  3. 局部性原理:软件设计要尽量遵循“数据读取集中”与“使用到一个数据,大概率会使用其附近的数据”,这样磁盘预读能充分提高磁盘IO;

B树为何适合做索引?

  1. 由于是m分叉的,高度能够大大降低;
  2. 每个节点可以存储j个记录,如果将节点大小设置为页大小,例如4K,能够充分的利用预读的特性,极大减少磁盘IO;

B+树

B+树
B树的插入及平衡化操作和2-3树很相似,这里就不介绍了。下面是往B树中依次插入

6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4
动画演示
B+树,如上图,仍是m叉搜索树,在B树的基础上,做了一些改进:

  1. 非叶子节点不再存储数据,数据只存储在同一层的叶子节点上;(画外音:B+树中根到每一个节点的路径长度一样,而B树不是这样。)
  2. 叶子之间,增加了链表,获取所有节点,不再需要中序遍历;

这些改进让B+树比B树有更优的特性:

  1. 范围查找,定位min与max之后,中间叶子节点,就是结果集,不用中序回溯;(画外音:范围查询在SQL中用得很多,这是B+树比B树最大的优势。)
  2. 叶子节点存储实际记录行,记录行相对比较紧密的存储,适合大数据量磁盘存储;非叶子节点存储记录的PK,用于查询加速,适合内存存储
  3. 非叶子节点,不存储实际记录,而只存储记录的KEY的话,那么在相同内存的情况下,B+树能够存储更多索引;

最后,量化说下,为什么m叉的B+树比二叉搜索树的高度大大大大降低?
大概计算一下:

(1)局部性原理,将一个节点的大小设为一页,一页4K,假设一个KEY有8字节,一个节点可以存储500个KEY,即j=500

(2)m叉树,大概m/2<= j <=m,即可以差不多是1000叉树

(3)那么:

一层树:1个节点,1*500个KEY,大小4K

二层树:1000个节点,1000500=50W个KEY,大小10004K=4M

三层树:10001000个节点,10001000500=5亿个KEY,大小10001000*4K=4G

画外音:额,帮忙看下有没有算错。

可以看到,存储大量的数据(5亿),并不需要太高树的深度(高度3),索引也不是太占内存(4G)。

MYSQL主从延迟的问题和解决办法

主从同步的延迟的原因

我们知道,一个服务器开放N个链接给客户端来连接的, 这样有会有大并发的更新操作, 但是从服务器的里面读取binlog 的线程仅有一个, 当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致,主服务器的SQL大量积压,未被同步到从服务器里。这就导致了主从不一致, 也就是主从延迟。

解决办法

  • 实际上主从同步延迟根本没有什么一招制敌的办法,因为所有的SQL必须都要在从服务器里面执行一遍,但是主服务器如果不断的有更新操作源源不断的写入,那么一旦有延迟产生,那么延迟加重的可能性就会原来越大。当然我们可以做一些缓解的措施。
  • 我们知道因为主服务器要负责更新操作,他对安全性的要求比从服务器高,所有有些设置可以修改,比如sync_binlog=1,innodb_flush_log_at_trx_commit=1之类的设置,而slave则不需要这么高的数据安全,完全可以讲sync_binlog设置为0或者关闭binlog,innodb_flushlog,innodb_flush_log_at_trx_commit也可以设置为0来提高sql的执行效率这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为slave。

  • 就是把,一台从服务器当度作为备份使用,而不提供查询,那边他的负载下来了,执行relaylog里面的SQL效率自然就高了。

  • 增加从服务器喽,这个目的还是分散读的压力,从而降低服务器负载。

评论系统未开启,无法评论!