文档章节

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

qinwei
 qinwei
发布于 2017/09/09 15:53
字数 2417
阅读 332
收藏 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
292
2
史上最简单的 MySQL 教程(三十五)「数据备份与还原(上)」

数据备份与还原 基础概念: 备份,将当前已有的数据或记录另存一份; 还原,将数据恢复到备份时的状态。 为什么要进行数据的备份与还原? 防止数据丢失; 保护数据记录。 数据备份与还原的方...

qq_35246620
2017/10/01
0
0
数据表的类型及储存位置

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

全栈coder
2017/02/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Confluence 6 修改空间名字和标识

希望修改空间名称: 在空间名称的边上,选择 图标。 输入新的空间名称,然后单击 保存(Save)。 标识(Logo )- 修改空间的名称和标识。 重新组织(Reorder) - 拖动来从新组织快捷链接。 ...

honeymose
15分钟前
0
0
Ubuntu18.04 安装MySQL

1.安装MySQL sudo apt-get install mysql-server 2.配置MySQL sudo mysql_secure_installation 3.设置MySQL非root用户 设置原因:配置过程为系统root权限,在构建MySQL连接时出现错误:ERROR...

AI_SKI
今天
3
0
3.6 rc脚本(start方法) 3.7 rc脚本(stop和status方法) 3.8 rc脚本(以daemon方式启动)

3.6-3.7 rc脚本(start、stop和status方法) #!/usr/bin/env python# -*- coding: utf-8 -*-# [@Version](https://my.oschina.net/u/931210) : python 2.7# [@Time](https://my.oschina.......

隐匿的蚂蚁
今天
3
0
Cnn学习相关博客

CNN卷积神经网络原理讲解+图片识别应用(附源码) 笨方法学习CNN图像识别系列 深度学习图像识别项目(中):Keras和卷积神经网络(CNN) 卷积神经网络模型部署到移动设备 使用CNN神经网络进行...

-九天-
昨天
5
0
flutter 底部输入框 聊天输入框 Flexible

想在页面底部放个输入框,结果键盘一直遮住了,原来是布局问题 Widget build(BuildContext context) { return Scaffold( appBar: AppBar( title: Text("评论"), ...

大灰狼wow
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部