《高性能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 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

数据库的读写分离

读写分离,基本的原理是让主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE),而从数据库处理SELECT查询操作。数据库复制被用来把事务...

733
来自专栏数据和云

YH4:Oracle Flex Clusters

从Oracle数据库12c开始,可以将Oracle Clusterware和Oracle RAC配置在大型集群中,称为Oracle Flex集群。 这些集群包含...

2585
来自专栏狐狸

SQLServer CDC数据迁移和数据抽取功能介绍

为了满足数据迁移和数据抽取的业务需要,使得有机会在数据库层面上直接实现增量抽取功能,ORACLE综合性能和场景需要,在数据库引擎层面直接集成了CDC功能,由于提...

622
来自专栏idba

MySQL常见slave延迟原因以及解决方法

一 序言 在运维线上M-M 架构的MySQL数据库时,接收的比较多关于主备延时的报警:

843
来自专栏Java技术

ELK Stack之Beats简介

Beats 是ELK Stack技术栈中负责单一用途数据采集并推送给Logstash或Elasticsearch的轻量级产品。

825
来自专栏乐沙弥的世界

快速体验mongoDB分片

1、mongodb分片的实质是将数据分散到不同的物理机器,以分散IO,提供并发与吞吐量 2、mongodb分片依赖于片键,即任意一个需要开启的集合都需要创建...

682
来自专栏IT大咖说

618大促,苏宁如何通过citus打造分布式数据库抗住DB高负载

内容来源:2017 年 10 月 20 日,苏宁云商IT总部资深技术经理陈华军在“PostgreSQL 2017中国技术大会”进行《苏宁citus分布式数据库应...

972
来自专栏刘迪的专栏

MongoDB第二期:压缩与索引

MongoDB-3.2中 WiredTiger 存储引擎引入了压缩功能,出色的压缩了海量数据的存储空间。MongoDB很好的优化了建立索引的机制,对于海量数据,...

8410
来自专栏狐狸

SQL Server 多表数据增量获取和发布 2.1

为了满足数据迁移和数据抽取的业务需要,使得有机会在数据库层面上直接实现增量抽取功能,ORACLE综合性能和场景需要,在数据库引擎层面直接集成了CDC功能,由于提...

722
来自专栏应兆康的专栏

数据库的内模式、概念模式、外模式三级模式

外模式(external schema) 也称用户模式 用户与数据库系统之间的接口,对用户使用的数据的描述 数据操纵语言DML ,比如select看到一个表的...

33610

扫码关注云+社区