浅谈MySql的存储引擎(表类型)

什么是MySql数据库

通常意义上,数据库也就是数据的集合,具体到计算机上数据库可以是存储器上一些文件的集合或者一些内存数据的集合。
    我们通常说的MySql数据库,sql server数据库等等其实是数据库管理系统,它们可以存储数据,并提供查询和更新数据库中的数据的功能等等。根据数据库如何存储数据和如何操作数据的实现机制不同,这些数据库之间即有区别又有共同点。
    MySql数据库是开放源代码的关系型数据库。目前,它可以提供的功能有:支持sql语言、子查询、存储过程、触发器、视图、索引、事务、锁、外键约束和影像复制等。在后期,我们会详细讲解这些功能。
    同Oracle 和SQL Server等大型数据库系统一样,MySql也是客户/服务器系统并且是单进程多线程架构的数据库。
    MySql区别于其它数据库系统的一个重要特点是支持插入式存储引擎。

那么什么是存储引擎呢?

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)。
    在Oracle 和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎。
MySql中有哪些存储引擎?
     1 MyISAM:这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种:
    静态MyISAM如果数据表中的各数据列的长度都是预先固定好的,服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的,所以这种表存取和更新的效率非常高。当数据受损时,恢复工作也比较容易做。
    动态MyISAM如果数据表中出现varchar、xxxtext或xxxBLOB字段时,服务器将自动选择这种表类型。相对于静态MyISAM,这种表存储空间比较小,但由于每条记录的长度不一,所以多次修改数据后,数据表中的数据就可能离散的存储在内存中,进而导致执行效率下降。同时,内存中也可能会出现很多碎片。因此,这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
    压缩MyISAM以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储,但是这种表压缩之后不能再被修改。另外,因为是压缩数据,所以这种表在读取的时候要先时行解压缩。
    但是,不管是何种MyISAM表,目前它都不支持事务,行级锁和外键约束的功能。
    2 MyISAM Merge引擎:这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
    3 InnoDB:InnoDB表类型可以看作是对MyISAM的进一步更新产品,它提供了事务、行级锁机制和外键约束的功能。
    4 memory(heap):这种类型的数据表只存在于内存中。它使用散列索引,所以数据的存取速度非常快。因为是存在于内存中,所以这种类型常应用于临时表中。
    5 archive:这种类型只支持select 和 insert语句,而且不支持索引。常应用于日志记录和聚合分析方面。
    当然MySql支持的表类型不止上面几种。

下面我们介绍一下如何查看和设置数据表类型。

MySql中关于存储引擎的操作

    1 查看数据库可以支持的存储引擎
    用show engines; 命令可以显示当前数据库支持的存储引擎情况,如图1所示:

图1 数据库的存储引擎


    由上图可见当前系统的默认数据表类型是MyISAM。当然,我们可以通过修改数据库配置文件中的选项,设定默认表类型。
    2 查看表的结构等信息的若干命令
    要查看表的定义结构等信息可以使用以下几种命令:
    2.1Desc[ribe] tablename; //查看数据表的结构
    例如,查看表t1的结构,可得下图。

图2:查看表t1的结构
 
    2.2 Show create table tablename; //显示表的创建语句
    同上查询表t1,得下图:

图3 显示创建表t1的语句


    2.3 show table status like ‘tablename'\G显示表的当前状态值

同上查询表t1,得下图:

图4 显示表t1 的当前状态值


    综上可见,后两种方式都可以帮助我们查看某一表的存储引擎类型(图中已用红色方框标出)。
    3  设置或修改表的存储引擎
    3.1创建数据库表时设置存储存储引擎的基本语法是:
Create table tableName(
columnName(列名1)  type(数据类型)  attri(属性设置),
columnName(列名2)  type(数据类型)  attri(属性设置),
……..) engine = engineName

例如,假设要创建一个名为user的表,此表包括id,用户名username和性别sex三个字段,并且要设置表类型为merge。则可用如下的方式创建此数据表,

create table user(

id int not null auto_increment,

username char(20) not null,

sex char(2),

primary key(id)

) engine=merge

具体执行结果见下图:

图5 创建表user

查看创建后表user的信息,可见表的当前存储引擎是merge,如图所示:

图6 显示表t1 的当前状态值

3.2修改存储引擎,可以用命令Alter table tableName engine =engineName

假如,若需要将表user的存储引擎修改为archive类型,则可使用命令alter table user engine=archive。如下图所示:

图7 修改表user的存储引擎


查看修改后的表类型,可见表类型已经变为archive类型。

图8 显示表user修改后的状态值
 
小结
     在本文中主要介绍了什么是MySql数据库,并进一步引出了它的一个重要特性, 即插入式的多存储引擎机制。然后,简单介绍了什么是存储引擎和MySql中几种主要的存储引擎。最后,介绍了如何查看数据库支持的所有存储引擎,如何查看数据库表的存储引擎类型及如何设置或修改表的存储引擎类型。刚刚入门学习MySql,文中有错误之处,还请大家多多指导!

(0)

相关推荐

  • 浅谈MySql的存储引擎(表类型)

    什么是MySql数据库 通常意义上,数据库也就是数据的集合,具体到计算机上数据库可以是存储器上一些文件的集合或者一些内存数据的集合.    我们通常说的MySql数据库,sql server数据库等等其实是数据库管理系统,它们可以存储数据,并提供查询和更新数据库中的数据的功能等等.根据数据库如何存储数据和如何操作数据的实现机制不同,这些数据库之间即有区别又有共同点.    MySql数据库是开放源代码的关系型数据库.目前,它可以提供的功能有:支持sql语言.子查询.存储过程.触发器.视图.索引.

  • 浅谈MYSQL中树形结构表3种设计优劣分析与分享

    目录 简介 问题 设计1:邻接表 表设计 SQL示例 设计2:路径枚举 表设计 SQL示例 设计3:闭包表 表设计 SQL示例 结合使用 表设计 总结 简介 在开发中经常遇到树形结构的场景,本文将以部门表为例对比几种设计的优缺点: 问题 需求背景:根据部门检索人员, 问题:选择一个顶级部门情况下,跨级展示当前部门以及子部门下的所有人员,表怎么设计更合理 ? 递归吗 ?递归可以解决,但是势必消耗性能 设计1:邻接表 注:(常见父Id设计) 表设计 CREATE TABLE `dept_info01

  • 浅谈MySQL临时表与派生表

    关于派生表 当主查询中包含派生表,或者当select 语句中包含union字句,或者当select语句中包含一个字段的order by 子句(对另一个字段的group by 子句)时,MySQL为了完成查询,则需要自动创建临时表存储临时结果集,这种临时表由MySQL自行创建,自行维护,成为自动创建的临时表.对于自动创建的临时表,由于内存临时表的性能更为优越,mysql总是首先使用内存临时表,而当内存临时表变得太大时,达到某个阈值的时候,内存临时表就转存为外存临时表.也就是说,外存临时表是内存临时

  • 浅谈MySQL大表优化方案

    背景 阿里云RDS FOR MySQL(MySQL5.7版本)数据库业务表每月新增数据量超过千万,随着数据量持续增加,我们业务出现大表慢查询,在业务高峰期主业务表的慢查询需要几十秒严重影响业务 方案概述 一.数据库设计及索引优化 MySQL数据库本身高度灵活,造成性能不足,严重依赖开发人员的表设计能力以及索引优化能力,在这里给几点优化建议 时间类型转化为时间戳格式,用int类型储存,建索引增加查询效率 建议字段定义not null,null值很难查询优化且占用额外的索引空间 使用TINYINT类

  • 浅谈MySQL中float、double、decimal三个浮点类型的区别与总结

    下表中规划了每个浮点类型的存储大小和范围: 类型 大小 范围(有符号) 范围(无符号) 用途 ==float== 4 bytes (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度 浮点数值 ==double== 8 bytes (-1.797 693 134 862 315 7 E

  • 浅谈MySQL如何优雅的做大表删除

    随着时间的推移或者业务量的增长,数据库空间使用率也不断的呈稳定上升状态,当数据库空间将要达到瓶颈的时候,可能我们才会发现数据库有那么一两张的超级大表!他们堆积了从业务开始到现在的全部数据,但是90%的数据都是没有业务价值的,这时候该如何处理这些大表? 既然是没有价值的数据,我们通常一般会选择直接删除或者归档后删除两种,对于数据删除的操作方式来说又可分为两大类: 通过truncate直接删除表中全部数据 通过delete删除表中满足条件记录 一.Truncate操作 从逻辑意义上来讲,trunca

  • 浅谈MySQL user权限表

    MySQL 在安装时会自动创建一个名为 mysql 的数据库,mysql 数据库中存储的都是用户权限表.用户登录以后,MySQL 会根据这些权限表的内容为每个用户赋予相应的权限. user 表是 MySQL 中最重要的一个权限表,用来记录允许连接到服务器的账号信息.需要注意的是,在 user 表里启用的所有权限都是全局级的,适用于所有数据库. user 表中的字段大致可以分为 4 类,分别是用户列.权限列.安全列和资源控制列,下面主要介绍这些字段的含义. 用户列 用户列存储了用户连接 MySQL

  • 浅谈MySQL表空间回收的正确姿势

    目录 前置说明 问题重现 删除数据原理 数据的复用 哪些操作会造成数据空洞 如何收缩表空间 小结 不知道大家有没有遇到这样的一种情况,线上业务在MySQL表上做增删改查操作,随着时间的推移,表里面的数据越来越多,表数据文件越来越大,数据库占用的空间自然也逐渐增长 为了缩小磁盘上表数据文件占用的空间,我们在最大的一张业务表中用delete命令删除了一半儿的旧数据,删除之后,磁盘上表数据文件并没有缩小,即使删除整张表的数据,文件依然没有变小,这是为什么呢? 本文将详细的分析上述问题,并给出正确回收表

  • 浅谈Mysql时间的存储 datetime还是时间戳timestamp

    目录 简单对比 占用空间 优缺对比 如何存储毫秒或者更高级别的小数? 时间戳详解 一个方便的用法 显示格式(非存储格式) java可能遇到的坑 简单对比 占用空间 MySQL 常用的日期时间类型常用的是datetime.timestamp.除此之外 还有用的不多的YEAR DATE TIME注意5.6.4的版本 从上表可以看到,DATETIME默认占用5个字节,而TIMESTAMP默认占用4个字节,如果需要更高精度的存储(秒后的小数点个数,比如毫秒)那么需要额外的存储空间. 优缺对比 DATET

  • 浅谈mysql一张表到底能存多少数据

    程序员平时和mysql打交道一定不少,可以说每天都有接触到,但是mysql一张表到底能存多少数据呢?计算根据是什么呢?接下来咱们逐一探讨 知识准备 数据页 在操作系统中,我们知道为了跟磁盘交互,内存也是分页的,一页大小4KB.同样的在MySQL中为了提高吞吐率,数据也是分页的,不过MySQL的数据页大小是16KB.(确切的说是InnoDB数据页大小16KB).详细学习可以参考官网我们可以用如下命令查询到. mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_s

随机推荐