《高性能MySQL》读书笔记(二) ——MySQL存储引擎概述

《高性能MySQL》读书笔记(二)——MySQL存储引擎概述

(原创内容,转载请注明来源,谢谢)

一、基础信息

mysql将数据库保存在数据目录下的一个子目录,创建表时,会在此目录下,创建与表明相同的文件名,后缀是.frm,大小写根据系统决定,windows大小写不敏感,linux大小写敏感。

可以采用showtable status命令查看表格信息,例如:

上述结果中,name是名字,engine是引擎,row_format是数据的长度是否可变,rows是当前有几行,avg_row_length是平均每行字节数,data_length是表数据字节数,max_data_length是该表最大可以使用的字节数,index_length是当前索引占字节数,data_free是已经分配的空间还未使用的(主要是被删除的行,该行原先的空间可以给新的数据使用),auto_increment是自增步长,三个时间分别是创建、最后修改、最后检查表的时间,collation是编码方式,checksum是整个表的校验和,create_options是其他选项,comment创建表格时候的备注。

二、innodb引擎

innodb是mysql默认的事务性引擎,被设计用来处理大量短期事务,这类事务大多数情况下不会发生需要回滚的事情。innodb是mysql最广泛的引擎,很多公司直接规定绝大部分建表要求采用innodb引擎。

innodb特性如下:

1)innodb数据存储在表空间中,表空间是由一系列文件组成的,由innodb管理的“黑盒子”。

2)innodb采用mvcc的高并发策略,实现四个隔离级别,默认是repeatable read(可重复读),并通过间隙锁避免幻读。间隙锁是指不仅锁定涉及的行,还会将索引中的间隙进行锁定。

3)innodb采用聚簇索引的方式,索引结构和其他存储引擎很大不同,对主键查询有很高的性能。但是由于其要求二级索引都必须要包含主键,所以主键大会导致索引文件大。因此建表应该设计索引尽量小。

4)innodb内部有许多优化,包括从磁盘读取数据时的可预测性预读,自动在内存中创建hash索引,以加速读操作,以及能够加速插入操作的插入缓冲区等。

5)innodb支持热备份,而大多数mysql的其他引擎都不支持。

6)innodb引擎中,索引和表数据是在同一个文件中,这个和mysiam有很大不同。

三、myisam引擎

在mysql5.1之前,mysql默认的引擎是myisam,其提供大量特性,包括全文索引、压缩、空间函数等,但是其不支持事务和行级锁,因此数据库奔溃后很难恢复。

1、存储

myisam将数据文件和索引文件分开存,分别是后缀.MYD和.MYI,其行支持动态或静态,会根据表定义来决定行的格式。

2、特性

1)加锁与并发

myisam会对整个表加锁,读时加共享锁,写时加排他锁,但是在读的时候也可以插入数据,称为并发插入。

2)修复

myisam修复可能导致数据丢失,而且非常慢,通过命令repair table要求修复表。

3)延迟更新索引键

如果设置了delay_key_write,则每次修改完数据,不会立即写入磁盘,而是会写到内存的缓冲区,只有清理缓冲区或关闭表才会写入数据。这样做提升了性能,但是如果数据库奔溃也会导致数据丢失。

3、myisam压缩表

如果创建数据后,不会在修改,则使用myisam压缩表比较合适,可以使用myisampack对表进行压缩。压缩的数据,如果没有解压,是无法修改的。由于每一行的数据是单独压缩的,因此读取数据不需要解压,这样一来,一次性读入一个压缩表,而不用每次通过I/O从磁盘取数据,极大加快了读取速度。

四、其他mysql内建索引

1、archive

只支持insert和select,会缓存所有的写并利用zlib进行压缩,所以磁盘操作比myisam更少。但是查询需要全表扫描,适用于日志和数据采集类系统。

支持行级锁和专用缓冲区,对快速写具有高并发性,在批量插入完成之前,读操作不可见,这个模仿mvcc。

archive是一个针对高速插入和压缩做优化的引擎。

2、csv引擎

该引擎可以将csv文件作为数据库表处理,不支持索引。可以在数据库运行期间拷入、拷出文件,也可以将excel文件转成csv并放在mysql数据目录,这样就可以在mysql直接打开使用。

3、federated引擎

这个引擎是一个访问mysql服务器的代理,可以创建远程访问服务器的客户端连接,并将查询传输到远程执行。

4、memory引擎

如果要快速访问数据,而且数据丢失也没问题,并且不修改数据,则采用memory引擎最佳,这个表保存在内存中,不需要I/O,比myisam快了一个数量级,重启服务器后,表的结构会在,但是里面的数据会全部丢失。

使用场景:

1)用于查找表或映射表。

2)用于缓存周期性聚合数据,或数据分析期间的临时数据。

该引擎支持hash索引,速度非常快,但是是表锁,并发写入性能低,不支持blob或text,并且每行长度固定,即使是varchar,也会转成char。

mysql保存临时结果,内部实现就是用memory引擎,如果太大超出内存允许范围,则会转成myisam引擎。

另外,临时表和memory引擎的数据表不一样。临时表是用create temporary table命令创建的表,适用于任何引擎,只在单个连接有效,连接中断则消失;而memory引擎的表数据,在服务器重启之前,会一直存在。

5、ndb集群引擎

这个引擎是sql和ndb之间的接口,mysql集群,是由mysql服务器、ndb集群存储引擎,以及分布式、share-something、容灾、高可用的ndb数据库集合,共同组成的。

6、第三方引擎

1)OLTP类引擎

XtraDB是innodb引擎的改进版本,可以完全取代innodb,在性能、可测量性、操作灵活性都很优秀,并且兼容innodb数据文件和innodb的所有查询。mariadb包含此引擎。

2)面向列的引擎

通常mysql的引擎都是针对行的,但是在大数据量的情况下,如果不需要行的全部数据,采用面向列的方式效率更高。

Infobright是最有名的面向列的引擎,在数据量达到数十TB时运行良好,是为数据分析和数据仓库设计的引擎。该引擎将数据高度压缩,按块排列,每块有一组元数据。在数据处理时,可以通过元数据判断是否要跳过该块,甚至用块也可以满足要求。该引擎不支持索引,块结构是一种类似的准索引。

五、存储引擎选择

1、通用建议

mysql默认的存储引擎是innodb,并且作者的建议是,除非对innodb所不具备的功能非常的有需求,否则都用innodb。例如要全文索引,可以考虑innodb结合sphinx,而不是用myisam。

当然,某些特定情况,如不在意数据是否丢失,没有并发需求,同时觉得innodb太占空间,则可以用myisam。

另外,非常不建议对不同的表采用不同的索引,即混合索引,这样会出现很多意外的问题,使得程序非常复杂。

例如,选择支持事务的索引和不支持事务的索引混合,则事务回退时,不支持索引的表的信息无法回退。

具体应用大体如下。

2、日志型应用

如果需要对用户访问的记录实时记录等,对插入数据有高要求,则使用archive或者myisam比较合适,但是建议采用读写分离。将对主库进行写操作,对从库进行读操作。这样可以避免获取日志报表等的时候,影响插入的性能。

另外,也可以按照日期等分表,这样查询历史就可以直接读取历史表。

3、大部分情况只读的应用

如各语言、框架等的说明文档,大部分情况下是只读的,基本没有写操作,虽然myisam是对读性能比较好,但是仍支持innodb引擎。因为其聚簇索引的速度非常快,且崩溃后的恢复等也比较容易。

4、订单处理

涉及到订单处理,必须使用事务,这也就使得使用innodb是最佳选择。

5、CD-ROM

如果数据不改变了,则采用myisam的压缩表,速度会非常快。

6、大数据量

在3-5TB的时候,使用innodb可以比较好的运行,但是需要良好的系统设计。超过这个数目,建议使用Infobright或TokuDB。

六、引擎转换

共有三种引擎转换方式。

1、alter table

例如,altertable tablename engine = InnoDB;

这个做法可以适用于任何存储引擎,但是比较耗时,mysql会按行将数据从一张表复制到另一张表,在复制期间可能会消耗所有的I/O,同时原表会加上读锁。转换表会失去原有的特性,如将innodb转换成myisam,再转回innodb,原有的外键会丢失。

2、导出和导入

使用mysqldump工具,将表导出到文件,然后手动修改文件中的create table中的存储引擎的选项,同时由于一个数据库不能有两个表名,还要修改表名。修改完毕后再导入即可。

3、创建与查询

这种方式是结合前两种方式,而且不需要整个表导出。实际方案即创建一张结构和原表一样的新表,且引擎设置为新的引擎,然后使用insert into newtable select * from oldtable的方式。如果数据量太大,可以采用分批的方式进行。为了保证数据的完全一致,也可以在进行复制期间,给原来的表加上读锁。

——written by linhxx 2017.09.12

原文发布于微信公众号 - 决胜机器学习(phpthinker)

原文发表时间:2017-09-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏信安之路

轻松理解什么是 SQL 注入

作为长期占据 OWASP Top 10 首位的注入,OWASP 对于注入的解释如下:

640
来自专栏Python入门

使用Python这么多年,才发现Python还有这些实用的功能和特点

在使用Python多年以后,我偶然发现了一些我们过去不知道的功能和特性。一些可以说是非常有用,但却没有充分利用。考虑到这一点,我编辑了一些你应该了解的Pytho...

671
来自专栏王磊的博客

js堆栈溢出的问题

    js是最令程序员头疼的问题了,不是语法也不是使用头疼,而是调试头疼,虽然有很方便的各种各样的调试工具,但经管这样有时候一个疏忽的小问题,会导致各种各样的...

2984
来自专栏FreeBuf

Hongcms 3.0.0后台SQL注入漏洞分析

* 本文作者:BlackWater,本文属FreeBuf原创奖励计划,未经许可禁止转载。

846
来自专栏joycl

c#面试题汇总

下面的参考解答只是帮助大家理解,不用背,面试题、笔试题千变万化,不要梦想着把题覆盖了,下面的题是供大家查漏补缺用的,真正的把这些题搞懂了,才能“以不变应万变”。...

641
来自专栏CDA数据分析师

不能不懂的 Python 7大功能和特点

在使用Python多年以后,我偶然发现了一些我们过去不知道的功能和特性。一些可以说是非常有用,但却没有充分利用。考虑到这一点,我编辑了一些你应该了解的Pytho...

1778
来自专栏晨星先生的自留地

面试中遇到的坑之mysql注入入门

1824
来自专栏任浩强的运维生涯

mongodb基础篇

一、  关于mongodb 两种非关系数据库 Redis:满足极高读写性能的Key-Value数据库       键值式储存,可以通过键快速查询到值。     ...

3228
来自专栏鬼谷君

Mysql慢查询和慢查询日志分析

1141
来自专栏Crossin的编程教室

【我问 Crossin】python程序一闪而过,是我的打开方式不对?

除了在公众号、交流群组或论坛上提问,大家有想问的问题也可以直接在本栏目文章下留言,接受以下提问: 与编程相关的问题 工作咨询、学习方向建议 程序猿的日常 Cro...

2759

扫描关注云+社区