前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >《高性能MySQL》读书笔记(二) ——MySQL存储引擎概述

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

作者头像
用户1327360
发布2018-03-07 16:25:11
1.4K0
发布2018-03-07 16:25:11
举报
文章被收录于专栏:决胜机器学习

《高性能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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2017-09-12,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 决胜机器学习 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档