文档章节

mysql数据表存储引擎类型及特性

qinwei
 qinwei
发布于 2017/09/09 15:53
字数 2417
阅读 325
收藏 34

数据表类型(存储引擎)

数据库引擎用于存储、处理和保护数据的核心服务,利用数据库引擎可控制访问权限并快速处理事务,利用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库,包括创建用于存储数据的表和用于查看、管理、保护数据安全的数据库对象(索引、视图、存储过程)。

常见引擎比对

特性 Myisam InnoDB Memory BDB Archive
存储限制 无限制 64TB 没有 没有
事务安全 - 支持 - 支持 -
锁机制 表锁 行锁 表锁 页锁 行锁
B树索引 支持 支持 支持 支持 -
哈希索引 - 支持 支持 - -
全文索引 支持 - - - -
集群索引 - 支持 - - -
数据缓存 - 支持 支持 - -
索引缓存 支持 支持 支持 - -
数据压缩 支持 - - - 支持
空间使用 N/A 非常低
内存使用
批量插入速度 非常高
外键支持 - 支持 - - -

各引擎特点

  • Myisam
    mysql默认存储引擎,在磁盘上存储成三个文件.frm(存储表定义).MYD(MYData存储数据)。MYI(MYIndex存储索引);
    没有事务支持,不支持行锁外键,因此当insert、update会锁定整个表,效率会低一些,MyIASM中存储了行数,如果表的读操作远大于写且不需要事务,MyISAM优选。

    索引
    Myisam index

    1. MyISAM引擎索引结构为B+Tree,其中B+Tree的数据域存储的为实际数据地址即索引和实际数据分开即非聚集索引。
    2. 如图主键索引和辅助索引结构一直只不过主键索引要求key唯一。
    3. MyISAM中索引检索算法首先安装B+Tree搜索算法搜索索引,如果key存在,则取出data域的值,然后以data域的值为地址,读取相应数据记录。
  • Innodb
    提供了对数据库ACID事务支持并实现SQL标准的四种隔离级别,提供行级锁和外键约束。Mysql运行时Innodb会在内存中建立缓冲池用于缓冲数据和索引,该引擎不支持fulltext类型索引且没有保存表的行数,select count(*) from table 血药扫全表。
    需要事务操作时Innodb首选,锁力度小,写操作不会锁定权标,所以并发高时Innodb引擎效率更高,
    相比Myisam写处理效率差一些会占用更多的磁盘空间保存数据和索引。

    索引
    Myisam index

    1. Innodb索引采用B+Tree且Innodb索引文件本身就是数据文件即B+Tree的数据域存储的就是实际的数据如图Primary Key即聚集索引。这个索引的key就是数据表主键,Innodb表本身就是主索引。
    2. Innodb辅助索引数据域存储的是相应的主键的值而不是地址,通过辅助索引查找时先找到主键再通过主键查找数据。所以主键不建议过长否则辅助索引会变得很大。
    3. Innodb必须有主键如果没有显示指定Mysql会自动选择一个唯一标识的数据记录为主键。
    4. 聚集索引按主键搜索效率十分高效,辅助索引必须检索两遍。
    5. 基于Innodb索引结构可以解释为什么不建议使用过长的主键,为什么不建议使用非单调(非递增)的记录做主键,B+Tree索引结构导致使用非单调做主键会相当低效。

常用命令

  • show engines; 查看当前支持的引擎和默认引擎
  • show table status from mytest; show create table tablename;查看数据表引擎
  • 修改默认引擎 my.ini [mysqld]下增加 default-storage-engine=InnoDB

名词概念

  • ACID: (Atomicity)原子性,要么全部执行要么不执行;(Consistency)一致性,事务的运行不改变数据库中数据的一致性;(Isolation)独立性,也称隔离性两个以上的食物不会出现交错执行的状态;(Durability)持久性,事务执行成功后数据持久保存。

  • BTree 二叉搜索树

    1. 所有非叶子几点最多有两个子节点(left right)
    2. 所有节点存储一个关键字
    3. 非叶子节点左指针指向小于其关键字的子树,右指针指向大于其关键字的子树
      BTree
      二叉树查找:从跟节点开始查询关键字与节点相等,命中返回。否则查询关键字比节点小,进入左子节点否则进入右节点。如果左或右为空反馈找不到。如果树左右节点保持平衡如图1、3棵树查询性能逼近二分查找。树比二分查找的有点是数据更新时不需要移动大段内存数据如3、4图数据更新。
      经过一系列的更新可能导致图2的BTree树,该树搜索成线性无查询优势,在实际使用中通常使用平衡二叉树如图1、3即“平衡二叉树”,平衡算法是一种在B树种插入和删除节点的策略。
  • B-Tree 多路搜索树(非二叉树)

    1. 任意非叶子节点最多只有M个子节点且M>2
    2. 跟节点的子节点数为[2, M]
    3. 除跟节点外的非叶子节点的子节点树为[M/2, M]
    4. 每个节点存放至少M/2-1(取上整)和至多M-1个关键字(至少2个关键字)
    5. 非叶子节点的关键字个数=指向儿子的指针个数-1
    6. 非叶子节点的关键字:K[1],K[2],…,K[M-1]且K[i]<K[i+1]
    7. 非叶子几点的指针:P[1],P[2],…,P[M],其中P[1]指向关键字小于K[1]的子树,P[M]指向管关键字大于K[M-1]的子树,其他P[i]指向关键字属于(K[i-1], K[i])的子树
    8. 所有叶子节点位于同一层
      B-Tree
      B-Tree查找:从跟节点开始,对节点内的关键字(有序)进行二分查找,命中结束。否则进入查询关键字所属范围的儿子节点;重复直到空或叶子节点。
      由于限制除根节点外的非叶子节点至少含有M/2个儿子,确保了节点的至少利用率所以B-Tree的性能等价于二分查找,也就没有B树平衡的问题。由于M/2的限制,插入或删除节点时需要考虑分裂和合并节点。
      B-Tree特性:关键字集合分布在整科树种;任何一个关键字出现且只出现在一个节点中;搜索有可能在非叶子节点结束;搜索性能等价于在关键字全集内做一次二分查找;自动层次控制;
  • B+Tree B-Tree变体多路搜索树

    1. 基本与B-Tree定义相同除以下外
    2. 非叶子节点的子树指针与关键字个数相同
    3. 非叶子节点的子树指针P[i]指向关键字值属于(K[i], K[i+1])的子树
    4. 为所有叶子节点增加一个链指针
    5. 所有关键字都在叶子节点出现
      B+Tree
      B+Tree查找:与B-Tree相同区别B+树只有达到叶子节点才命中,其性能等价于关键字全集做一次二分查找。
      B+Tree特性:所有关键字都出现在叶子节点链表中,链表中关键字有序;不可能在非叶子节点命中;非叶子节点相当于是叶子节点的索引,叶子节点相当于是存储关键字数据的数据层;更适合文件索引系统;
  • B*Tree B+Tree变体

    1. 在B+Tree的非跟和非叶子节点增加指向兄弟的指针
      B*Tree
      B+Tree分裂:当一个节点满时,分配一个新的节点,将原节点中1/2的数据复制到新节点,最后在父节点中增加新节点指针;B+树分类只影响原节点和父节点不影响兄弟节点。
      B*Tree分裂:一个节点满时,如果下一个兄弟节点未满,将一部分数据移到兄弟几点中,再在源节点插入关键字,最后修改父节点中兄弟节点的关键字;如果兄弟节点也满了,则在源节点与兄弟节点之间增加新节点,并各赋值1/3的数据到新节点,最后在父节点增加新节点的指针。B*Tree分配节点的概率比B+Tree要低,空间使用率高。
  • 各个树比对

类型 特点
BTree 每个节点只存储一个关键字,等于命中,小于左节点,大于右节点
B-Tree 多路搜索树,每个节点存储M/2到M个关键字,非叶子节点存储指向关键字范围的子节点,所有关键字在整棵树中出现,且只出现一次,非叶子节点可以命中
B+Tree B-Tree基础上尉叶子节点增加链表指针,所有关键字都在叶子节点出现,非叶子节点作为叶子节点的索引,B+Tree叶子节点才命中
B*Tree B+Tree基础上为非也自己点也增加链表指针,将节点的最低利用率从1/2提高到2/3

[Mysql连载一]mysql数据表存储引擎类型及特性

[Mysql连载二]mysql支持的数据类型及最佳实践

© 著作权归作者所有

共有 人打赏支持
qinwei
粉丝 3
博文 20
码字总数 11168
作品 0
海淀
高级程序员
加载中

评论(3)

clouddyy
clouddyy
很老的资料了,现在默认引擎是innodb
开源中国首席罗纳尔多
开源中国首席罗纳尔多
InnoDB不是理论上支持无线大存储么?
mia0x75
mia0x75
你确定InnoDB不支持FT么?
数据表的创建、删除、索引和变更

创建数据表 删除数据表 索引表 存储引擎的索引特性: 可以对单个列或多个列建立索引,多列索引也被称作复合索引; 索引可以只包含唯一值,也可以包含重复值; 可以为同一个表创建多个索引,帮...

TinyDolphin
01/05
0
0
新安装的Mysql5.5数据库使用mybatis提示插入数据成功并且主键已经自增,但是数据表中没有数

经过查询是因为在4.0版本之后新推出的数据引擎InnoDB与MyIASM之间的差别,改为MyIASM即可具体介绍参照。 插入数据的时候发现一切正常, 但数据并未插入数据库,甚是奇怪,后来发现在数据库中...

boco
2015/10/20
912
0
MySQL 数据库分区管理 细节决定成败

在MySQL数据库中,表的不同部分在不同的位置被存储为单独的表。分区主要就是用来解决表在不同的位置存储的问题。在其他数据库中,也会存在这种情况。他们将这种类型的数据表称之为分区表。分...

华宰
2011/03/02
268
2
数据表的类型及储存位置

MySQL支持MyISAM,InnoDB,GEAP , BOB , ARCHIVE , CSV等多种数据表类型,在创建一个新的MySQL数据表时,可以为它设置一个类型。其中最重要的有MyISAM和InnoDB两种表类型,它们有各自的特性。...

全栈coder
2017/02/26
0
0
mysql中engine=innodb和engine=myisam的区别

最开始用MySQL Administrator建数据库的时候,表缺省是InnoDB类型,也就没有在意。后来用Access2MySQL导数据的时候发现只能导成 MyISAM类型的表,不知道这两种类型有什么区别,就去查了查。原...

晨曦之光
2012/03/01
386
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

异步化,高并发大杀器

今天来聊聊如何让项目异步化的一些事。 在进入正文之前,顺便给大家推荐一个Java架构方面的交流学习群:698581634,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分...

Java大蜗牛
13分钟前
0
0
织梦dedecms安全设置

网站本身做好防范: 1、修改默认后台名。 打开网站根目录,找到[dede],这个文件夹就是后台的路径,可以随意修改,比如修改为[adminbuy],此时后台登陆的路径为:http://www.*****.com/admi...

雨季2014
14分钟前
0
0
完美Python入门基础知识点总结,看完你的Python就完全入门了!

Python标识符 在 Python 里,标识符有字母、数字、下划线组成。 在 Python 中,所有标识符可以包括英文、数字以及下划线(_),但不能以数字开头。 Python 中的标识符是区分大小写的。 以下划线...

糖宝lsh
20分钟前
0
0
Java关于前后台传输中文乱码以及Excel下载乱码解决

针对乱码,一般情况是前后台以及中间件编码格式不同导致.故需要保证编码格式相同即可. 1.前台页面格式指定,这里用的是jsp,故需要将pageEncoding指定为"UTF-8" 2.指定服务器编码格式,比如用的t...

勤奋的蚂蚁
22分钟前
0
0
Flutter组件 - Flexible

Flexible是一个控制Row、Column、Flex等子组件如何布局的组件。 Flexible组件可以使Row、Column、Flex等子组件在主轴方向有填充可用空间的能力(例如,Row在水平方向,Column在垂直方向),但是...

一箭落旄头
25分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部