MySql存储引擎

前言

在数据库中存的就是一张张有着千丝万缕关系的表,所以表设计的好坏,将直接影响着整个数据库。而在设计表的时候,我们都会关注一个问题,使用什么存储引擎。等一下,存储引擎?什么是存储引擎?

什么是存储引擎?

关系数据库表是用于存储和组织信息的数据结构,可以将表理解为由行和列组成的表格,类似于Excel的电子表格的形式。有的表简单,有的表复杂,有的表根本不用来存储任何长期的数据,有的表读取时非常快,但是插入数据时去很差;而我们在实际开发过程中,就可能需要各种各样的表,不同的表,就意味着存储不同类型的数据,数据的处理上也会存在着差异,那么。对于MySQL来说,它提供了很多种类型的存储引擎,我们可以根据对数据处理的需求,选择不同的存储引擎,从而最大限度的利用MySQL强大的功能。

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)。

在Oracle 和SQL Server等数据库中只有一种存储引擎,所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎。

InnoDB是MySql默认的存储引擎

MySql的操作

  • 显示mysql支持的操作引擎
show engines;

这里写图片描述

  • 显示表的结构
DESC sina_oauthuser

这里写图片描述

  • 创建表时,指定存储引擎

如图:

这里写图片描述

或:

Create table tableName(
columnName(列名1)  type(数据类型)  attri(属性设置),
columnName(列名2)  type(数据类型)  attri(属性设置),
……..) engine = engineName
  • 查看表的存储引擎
SHOW CREATE TABLE sina_cookie

这里写图片描述

  • 查看表的当前状态
show table status like 'sina_cookie'

这里写图片描述

  • 修改表的存储引擎
Alter table tableName engine =engineName

MyISAM

在mysql客户端中,使用以下命令可以查看MySQL支持的引擎。

MyISAM表是独立于操作系统的,这说明可以轻松地将其从Windows服务器移植到Linux服务器;每当我们建立一个MyISAM引擎的表时,就会在本地磁盘上建立三个文件,文件名就是表名。例如,我建立了一个MyISAM引擎的tb_Demo表,那么就会生成以下三个文件:

tb_demo.frm,存储表定义;
tb_demo.MYD,存储数据;
tb_demo.MYI,存储索引。

MyISAM表无法处理事务,这就意味着有事务处理需求的表,不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用:

  • 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速,这是它最突出的优点。
  • 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如:MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

InnoDB

InnoDB是一个健壮的事务型存储引擎,这种存储引擎已经被很多互联网公司使用,为用户操作非常大的数据存储提供了一个强大的解决方案。我的电脑上安装的MySQL 5.6.13版,InnoDB就是作为默认的存储引擎。InnoDB还引入了行级锁定和外键约束,在以下场合下,使用InnoDB是最理想的选择:

  • 更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
  • 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
  • 自动灾难恢复。与其它存储引擎不同,InnoDB表能够自动从灾难中恢复。
  • 外键约束。MySQL支持外键的存储引擎只有InnoDB。
  • 支持自动增加列AUTO_INCREMENT属性。

一般来说,如果需要事务支持,并且有较高的并发读取频率,InnoDB是不错的选择。

MEMORY

使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间,采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能,但当mysqld守护进程崩溃时,所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式,这意味着不能使用BLOB和TEXT这样的长度可变的数据类型,VARCHAR是一种长度可变的类型,但因为它在MySQL内部当做长度固定不变的CHAR类型,所以可以使用。

一般在以下几种情况下使用Memory存储引擎:

  • 目标数据较小,而且被非常频繁地访问。在内存中存放数据,所以会造成内存的使用,可以通过参数max_heap_table_size控制Memory表的大小,设置此参数,就可以限制Memory表的最大大小。
  • 如果数据是临时的,而且要求必须立即可用,那么就可以存放在内存表中。
  • 存储在Memory表中的数据如果突然丢失,不会对应用服务产生实质的负面影响。

Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是,可以使用部分查询和通配查询,也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快,但是对“范围比较”的速度就慢多了,因此散列索引值适合使用在=和<>的操作符中,不适合在<或>操作符中,也同样不适合用在order by子句中。

可以在表创建时利用USING子句指定要使用的版本。例如:

create table users
(
    id smallint unsigned not null auto_increment,
    username varchar(15) not null,
    pwd varchar(15) not null,
    index using hash (username),
    primary key (id)
)engine=memory;

上述代码创建了一个表,在username字段上使用了HASH散列索引。下面的代码就创建一个表,使用BTREE索引。

create table users
(
    id smallint unsigned not null auto_increment,
    username varchar(15) not null,
    pwd varchar(15) not null,
    index using btree (username),
    primary key (id)
)engine=memory;

MERGE

MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,尽管其使用不如其它引擎突出,但是在某些情况下非常有用。说白了,Merge表就是几个相同MyISAM表的聚合器;Merge表中并没有数据,对Merge类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。

对于服务器日志这种信息,一般常用的存储策略是将数据分成很多表,每个名称与特定的时间端相关。例如:可以用12个相同的表来存储服务器日志数据,每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表,这意味着需要编写并更新多表查询,以反映这些表中的信息。与其编写这些可能出现错误的查询,不如将这些表合并起来使用一条查询,之后再删除Merge表,而不影响原来的数据,删除Merge表只是删除Merge表的定义,对内部的表没有任何影响。

下面就通过一个简单的例子来说说如何建立引擎为merge类型的表。

create table tb_log1(
    id int unsigned not null auto_increment, 
    log varchar(45),
    primary key(id)) engine=myisam;
insert into tb_log1(log) values('tb_log1_1');
insert into tb_log1(log) values('tb_log1_2');
insert into tb_log1(log) values('tb_log1_3');
insert into tb_log1(log) values('tb_log1_4');
insert into tb_log1(log) values('tb_log1_5');
create table tb_log2(
    id int unsigned not null auto_increment,
    log varchar(45),
    primary key(id)) engine=myisam;
insert into tb_log2(log) values('tb_log2_1');
insert into tb_log2(log) values('tb_log2_2');
insert into tb_log2(log) values('tb_log2_3');
insert into tb_log2(log) values('tb_log2_4');

先创建两个引擎为myisam(必须为myisam引擎)的表。插入上述数据,然后创建merge表,进行merge操作。

create table tb_merge(
    id int unsigned not null auto_increment, 
    log varchar(45), 
    primary key(id))engine=merge 
    union(tb_log1,tb_log2) insert_method=last;

这样就得到了一个引擎为merge的表,并且合并了tb_log1和tb_log2两个表。查询tb_merge表,可以得到以下数据:

+----+-----------+
| id | log       |
+----+-----------+
|  1 | tb_log1_1 |
|  2 | tb_log1_2 |
|  3 | tb_log1_3 |
|  4 | tb_log1_4 |
|  5 | tb_log1_5 |
|  1 | tb_log2_1 |
|  2 | tb_log2_2 |
|  3 | tb_log2_3 |
|  4 | tb_log2_4 |
+----+-----------+

现在我们主要来解释一下上面MERGE表的建表语句。

  • ENGINE=MERGE

指明使用MERGE引擎,有些同学可能见ENGINE=MRG_MyISAM的例子,也是对的,它们是一回事。

  • UNION=(t1, t2)

指明了MERGE表中挂接了些哪表,可以通过alter table的方式修改UNION的值,以实现增删MERGE表子表的功能。比如:

 alter table tb_merge engine=merge union(tb_log1) insert_method=last;
  • INSERT_METHOD=LAST

INSERT_METHOD指明插入方式,取值可以是:0 不允许插入;FIRST 插入到UNION中的第一个表; LAST插入到UNION中的最后一个表。

  • MERGE表及构成MERGE数据表结构的各成员数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型,索引也必须按照同样的顺序和同样的方式定义。

ARCHIVE

Archive是归档的意思,在归档之后很多的高级功能就不再支持了,仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前,Archive是不支持索引,但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制,它使用zlib压缩库,在记录被请求时会实时压缩,所以它经常被用来当做仓库使用。

存储引擎的一些问题

  • 如何查看服务器有哪些存储引擎可以使用?

为确定你的MySQL服务器可以用哪些存储引擎,执行如下命令: show engines;这个命令就能搞定了。

  • 如何选择合适的存储引擎?

选择标准可以分为:

(1)是否需要支持事务;
(2)是否需要使用热备;
(3)崩溃恢复:能否接受崩溃;
(4)是否需要外键支持;

然后按照标准,选择对应的存储引擎即可。

  • InnoDB与MyISAM

https://www.pureweber.com/article/myisam-vs-innodb/

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

Oracle存储过程和自定义函数 - 2016-06-05 14:06:48

概述 PL/SQL中的过程和函数(通常称为子程序)是PL/SQL块的一种特殊的类型,这种类型的子程序可以以编译的形式存放在数据库中,并为后续的程序块调用。 相同点: 完成特定功能的程序 不同点: 是否用return语句返回值。 举个例子: create or replace procedure PrintStudents (p_staffName in xgj_test.username% type ) as cursor c_testData is select t . sal , t . comm f
下面列出一些Mongodb的学习网站,如果有需要可以到这些地方进行深入学习。 http://www.mongoing.com                                                  MongoDB中文社区 http://www.mongodb.org                                                  MongoDB官方网站 https://jira.mongodb.org                       
在这片文章中,简单谈谈关于Mongodb中字段先后顺序的问题。 Mongodb中的文档是一种Key:Value对组成的Json字符串,按理说,字段的先后顺序是不应该影响到查询操作的。 我们简单做个实验,往集合中插入一条记录: db.testcollection.insert({"apple":2, "banana":3}); 这时候,我们通过以下两种方式查询都是一样的: 第一种方式: db.testcollection.find({"apple":2,"banana":3}) 第二种方式: db.test

Stored Outline - 2016-06-04 17:06:58

存储概要(stored outline) 是固定执行计划的一种传统技术。在Oracle 11g以前要固定执行计划只能使用outline,Oracle11g中引入了SPM来固定执行计划,创建stored outline需要有create any outline权限,为了管理还需要有对dba_outlines的查询权限。 创建stored outline 在会话级outline ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE | FALSE | 如果将creat

数据库之乐观锁和悲观锁篇 - 2016-06-04 17:06:58

冲突 在多用户环境中,在同一时间可能会有多个用户更新相同的记录,这会产生冲突。这就是著名的并发性问题。 典型的 冲突 有: 丢失更新: 一个事务的更新覆盖了其它事务的更新结果,就是所谓的更新丢失。例如:用户A把值从6改为2,用户B把值从2改为6,则用户A丢失了他的更新。 脏读: 当一个事务读取其它完成一半事务的记录时,就会发生脏读取。例如:用户A,B看到的值都是6,用户B把值改为2,用户A读到的值仍为6。 为了解决这些并发带来的问题。 我们需要引入并发控制机制。 并发控制 乐观并发控制(乐观锁)和悲观并发

SQL Server优化50法 - 2016-06-04 17:06:10

SQL Server优化50法 虽然查询速度慢的原因很多,但是如果通过一定的优化,也可以使查询问题得到一定程度的解决。 查询速度慢的原因很多,常见如下几种:     1、没有索引或者 没有 用到索引 (这是查询慢最常见的问题,是程序设计的缺陷)     2、I/O吞吐量小,形成了瓶颈效应。     3、没有 创建计算 列导致查询不优化。     4、内存不足     5、网络速度慢     6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)     7、锁或者死锁(这也是查询慢最常见的问题,

回收DBA角色的注意事项 - 2016-06-04 17:06:00

   Oracle对于权限的划分粒度非常精细,为了方便管理条目众多权限,Oracle引入了角色(role)这个逻辑概念,也在系统中预先设置了很多角色。在实际的应用中,应用用户对于权限的需要可能较为复杂,因此为了方便授权,很多应用用户被授予DBA角色,DBA角色拥有较多的系统权限,这对于数据库的管理是非常不利的,违反了权限最小化的安全原则。出于安全的考虑,系统可能需要回收DBA角色,在回收权限的过程中,为了保证应用正常运行,需要注意一些细节,下文将对几个需要注意的点进行讨论。   实验环境说明(本文中的结论
master数据库状态 可以使用ttRepStateGet 得到数据库的复制状态/角色 ACTIVE - active master,主库,可读写 STANDBY - active standby,备库,只读 IDLE - 所有数据库的初始状态,其实subscriber也是这个状态,只读 其它的状态有FAILED 和 RECOVERING 克隆数据库(初始化) 使用ttRepAdmin -duplicate 实现数据库的初始克隆。 需要两个数据库有相同的instance administrator,源库还
Oracle 10g中想要固定执行计划只能使用outline,sql profile不能起固定sql执行的效果,但是在Oracle 10.2.0.5中使用dbms_outln.create_outline通过使用共享池中的游标来创建outline,发现创建的outline与游标中的执行计划并不一致,而在oracle 10.2.0.4与oracle 11.2.0.4中是通过游标来创建的outline与cursor的实际执行计划是一致的。这应该是BUG. Oracle 10.2.0.5中的测试如下: 定义绑定

不要在存储过程中控制事务 - 2016-06-03 17:06:20

MySQL技术内幕 InnoDB存储引擎 344页. 原来看过这段,总而言之,就是MySQL不要在存储过程中控制事务. 当时没有仔细看细节,只是记住了一个结论. 毕竟都21世纪了.还有用存储过程的? 但是..#(×%¥×@…………#@ 以如下过程为例. drop   table  nums;   drop   procedure  pCreateNums;      create   table  nums(id  int   not   null   primary   key );      deli