高性能MySQL-再探


高性能MySQL-再探

前一节我们大致了解了MySQL的工作原理和数据库中的一些基本概念,这一节我们会针对MySQL中对于某些特性简单介绍。

MySQL中的事务

MySQL提供了两种事务型的存储引擎:InnoDBNDB Cluster。还有一些其他第三方的存储引擎也支持事务,后续我们再详细讨论。

自动提交(AUTOCOMMIT)

MySQL默认采用自动提交模式。如果不显式开启事务,每个查询都被当做一个事务对待。我们可以通过设置(SET AUTOCOMMIT = 0(ON)/1(OFF))改变提交模式。如果设置为0(OFF)禁用自动提交,所有的查询都是在一个事务中进行,直到显式的执行COMMIT或ROLLBACK,结束该事务并开启一个新的事务。对于不支持事务的存储引擎或者表,此设置不会有任何影响。

但这也有特殊的时候,MySQL中的一些命令在执行之前会强制COMMIT提交当前事务。典型的例子是在数据定义语句(DDL)。

混合引擎

MySQL服务器层不管理事务,事务是由存储引擎实现的。所以在不同的存储引擎中使用事务是不可靠谱的。

隐式和显式锁定

InnoDB采用的是两阶段锁定协议。隐式锁定:在事务执行过程中,随时都能执行锁定,在COMMIT或ROLLBACK时一次性释放所有的锁。InnoDB会根据隔离级别在需要的时候自动加锁。显式锁定:InnoDB支持特定的语句进行显示锁定,例如:SELECT ... LOCK IN SHARE MODESELECT ... FOR UPDATE

这不是SQL规范的一部分,在事务中使用显示锁很容易造成死锁,需要注意

另外MySQL也支持LOCK TABLESUNLOCK TABLE语句,这些是MySQL服务器层实现的,和存储引擎无关。

如果你不清楚LOCK TABLES的工作原理,除非你禁用了AUTOCOMMIT,任何时候都不应该显式的使用LOCK TABLES

多版本并发控制

为了考虑到并发性能,MySQL的大部分存储引擎都是不是简单的实现了行级锁,一般都同时实现了多版本并发控制(MVCC)。MVCC没有同一的标准,可以理解为行级锁的升级或变种。MVCC在很多情况下可以不加锁完成并发操作,降低了加锁的消耗。

InnoDB中MVCC的实现是通过在每行记录后面保存两个隐藏的列(行的创建时间和行的过期时间)来实现的。两个列中存储的是系统版本号(系统版本号会随着事务的开启而自增,当前事务的版本号实际上就是递增之后的系统版本号)。通过事务版本号的隐藏列的版本号对比实现MVCC。下面我们具体讨论一下在REPEATABLE READ下MVCC是如何操作的:

  • SELECT

    InnoDB会根据一下两个条件检查每行的记录:

    1. InnoDB只查找版本号早于(小于等于)当前事务版本的数据行,确保当前事务读取到的数据都是早于当前事务更改后的数据。
    2. 行的过期版本要么未定义要么大于当前事务版本号,确保读取到的行在事务开始之前未被删除。
  • INSERT

    InnoDB为新插入的每一行保存当前事务版本号作为版本号

  • DELETE

    InnoDB为删除的每一行保存当前系统版本号作为行的过期标识

  • UPDATE

    InnoDB为插入一行新的记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为删除标识

    实际上InnoDB的UPDATE操作是插入新行和修改旧行的版本号

MVCC的实现保证了不管需要执行多长时间,每个事务看到的数据都是一致的。保存两个版本号使得绝大多数的读操作不需要加锁。这使得读数据操作很简单,性能很好,相反这回增加额外的存储空间和做更多的行检查工作以及一些额外的维护工作。

MVCC只在REPEATABLE READREAD COMMITTED两个隔离级别下工作

MySQL的存储引擎

这里我们浅显的了解一下MySQL中存储引擎是如何存储数据的,只作为了解。

在文件系统中,MySQL为每个数据库(schema)在数据目录下创建一个同名的子目录,创建表时,会在改数据库的目录下创建一个同名的.frm文件保存表定义。例如我们的数据库中有一个database1数据库,库中有一个table1的表,那么我们会在MySQL的数据目录下面看到如下文件:

[path/to/mysql/var/database1]:ls
table1.frm ...

大小写敏感依据操作系统不同而不同,Linux是大小写敏感的,Windows是大小写不敏感的。

我们可以通过SHOW TABLE STATUS命令查看表描述:

字段含义
Name表名
Engine表的存储引擎类型
Row_format行的格式
Rows表中的行数,对于MyISAM是精确值,对于InnoDB是估计值
Avg_row_length平均每行包含的字节数
Data_lenght表数据的大小(字节数)
Max_data_length表数据的最大容量,依存储引擎而异
Index_length索引大小
Data_free剩余表大小,对于MyISAM表,表示已分配但目前还没有使用的空间,如之前删除的数据大小。
Auto_increment下一个AUTO_INCREMENT值
Create_time表的创建时间
Update_time表数据的最后修改时间
Check_time最后一次检查表的时间
Collation表的默认字符集和字符排序规则
Checksum如果启用,保存的是整个表的实时校验和
Create_options创建表时指定的其他选项
Commnet额外信息

存储引擎

  • InnoDB

    InnoDB是MySQL默认事务型引擎,也是最重要使用最广泛的引擎。
    >除非有非常特别的原因需要使用其他引擎,否则应该优先考虑InnoDB引擎。
    
  • MyISAM

    MyISAM不支持事务,不支持崩溃后的安全恢复,但对于只读数据或者表比较小可以容忍修复的表还是可以使用的。
    >再次强调不要默认使用MyISAM,而是应当默认使用InnoDB。
    

MySQL中还有很多其他的存储引擎:

  • Archive:不是事务型引擎,只支持SELECT和INSERT。具有较高的写入性能(对插入行进行压缩),但是SELECT是全表扫描。是针对高速插入和压缩做了优化的简单引擎。
  • Blackhole:可以用于复制数据到备库,或者简单地记录日志。不推荐使用。
  • CSV:将csv文件当作表来使用,不支持索引。
  • Federated:用于访问远程服务器的代理,不推荐使用。
  • Memory:将数据存在内存中的一种引擎,读取速度非常快,不支持持久化(重启后数据会丢失)。
  • Merge:是MyISAM的一个变种,已被放弃。
  • NDB集群:一种集群引擎,MySQL集群的相关会在后面博客讲到

如何选择合适的引擎

一句话,大部分时候InnoDB都是第一选择。这里给大家列举几条参考依据:

  • 除非InnoDB不能满足应用的需要,可以选择其他引擎。
  • 除非万不得已,否则不应当使用混合引擎。
  • 应用是否需要事务支持
  • 对于备份的需求,是否需要在线热备份。
  • 崩溃恢复,MyISAM崩溃后发生损坏的概率比InnoDB高很多。
  • 特有的特性,你应当足够了解一个引擎,不然就InnoDB。

声明:Iamzz|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - 高性能MySQL-再探


人生如逆旅,我亦是行人。