1 存储引擎概念介绍
存储引擎其实就是如何实现存储数据, 如何为存储的数据建立索引以及如何更新, 查询数据等技术实现的方法. 数据库中的存储引擎其实是对使用了该引擎的表进行某种设置, 数据库中的表设定了什么存储引擎, 那么该表在数据存储方式, 数据更新方式, 数据查询性能以及是否支持索引等方面就会有不同的 "效果".
通过选择不同的存储引擎, 获得额外的速度或者功能, 从而改善服务器的应用的整体功能. 例如, 如果你在研究大量的临时数据, 你也许需要使用内存 MySQL 存储引擎. 内存存储引擎能够在内存中存储所有的表格数据. 又或者, 你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力). 这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也称作表类型). MySQL 默认配置了许多不同的存储引擎, 可以预先设置或者在 MySQL 服务器中启用. 你可以选择适用于服务器, 数据库和表格的存储引擎, 以便在选择如何存储你的信息, 如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性.
2 Mysql 概念介绍
插件式存储引擎是 MySql 数据库最重要的特性之一, 用户可以根据应用的需要选择如何存储和索引数据, 是否使用事务等.
MySQL 中的数据用各种不同的技术存储在文件 (或内存) 中, 这些技术中的每一种技术都使用不同的存储机制, 索引技巧, 锁定水平并且最终提供广泛的不同功能和能力. 在 MySQL 中将这些不同的技术及配套的相关功能称为存储引擎.
用命令 SHOW ENGINES 可以查看 mysql 中支持的存储引擎. MySql5.0 支持的存储引擎包括 MyISAM,InnoDB,BDB,MEMORY,MERGE,EXAMPLE,NDB Cluster,ARCHIVE,CVS,BLACKHOLE,FEDERATED 等, 其中只有 InnoDB 和 BDB 提供事务安全表, 其他存储引擎都是非事务安全表.
创建新表时如果不指定存储引擎, 系统将使用默认的存储引擎, MySql5.5 之前默认的存储引擎是 MyISAM,5.5 之后改为了 InnoDB. 如果要修改默认的存储引擎, 可以在参数文件中设置 default-table-type.
mysql 分支主流有以下三种:
mysql-->oracle 公司
mariadb: 真正的开源 mysql, 吸取社区研发的结果, 存储引擎为 Aria, 是 MyISAM 的增强版, 可以做奔溃后安全恢复, mariadb 的引擎有以下三个:
MyISAM-->Aria
InnoDB-->Xtra(Percona 公司做补丁到 InnoDB, 所以性能更好)
tokudb: 支持海量存储
Percona-server(Percona 公司发行的 mysql 数据库另一个分支)
3 Mysql 配置文件介绍
查看配置文件的顺序, 同一配置项出现在多个配置文件中, 后出现的配置生效, 同时, 可以用选项 defaults-extra-file 或者 --defaults-file 来定义额外加载的配置文件的路径
- [root@CentOS7A script]#my_print_defaults --verbose
- Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
例子
常用配置
vim /etc/my.cnf.d/server.cnf
- [server]
- skip_name_resolve = ON #跳过名称解析
- innodb_file_per_table = ON #每表使用独立的表空间
- max_connections = 20000 #最大并发连接数
每一个数据库相当于是路径 /var/lib/mysql / 下的子目录, 如创建一个数据库为 sunny, 那么在路径 / var/lib/mysql / 下就会生成一个目录为 sunny, 但是不能直接在目录 / var/lib/mysql / 创建子目录, 否则会出现问题, 不能生成新的数据库
4 InnoDB 引擎介绍
InnoDB, 是 MySQL 的数据库引擎之一, 为 MySQL AB 发布 binary 的标准之一. InnoDB 由 Innobase Oy 公司所开发, 2006 年五月时由甲骨文公司并购. 与传统的 ISAM 与 MyISAM 相比, InnoDB 的最大特色就是支持了 ACID 兼容的事务 (Transaction) 功能, 类似于 PostgreSQL.
innodb 是事务型存储引擎, 适合对事务要求较高的场景中; 但较适用于处理大量短期事务(即小事务, 即少量语句, 如十几个语句的处理);
基于 MVCC(Mutli Version Concurrency Control)支持高并发; 支持四个隔离级别, 默认级别为 REPEATABLE-READ; 间隙锁以防止幻读;
使用聚集索引(主键索引); 索引和内容放在一起. 一般是有序的, 聚集索引只能一个, 其他索引都是非聚集的, MyISAM 没有聚集索引
支持 "自适应 Hash 索引"; 键值数据, 即 kv
锁粒度: 行级锁; 间隙锁; 关系型数据库写锁是独占的;
行级锁: innodb 改一行只锁一行, 行级别的力度. myISAM 修改一行需要把整张表锁掉
间隙锁: 锁住行和行直接的间隙, 如查询的是两行的信息时, 有操作需要在这两行间插入新的记录, 这里的间隙锁, 就是锁住两行间的间隙, 使得不能在两行间插入间隙
例子: 事务的解释
事务型如 A 账号里减去 80,B 账号里加 80, 但是万一系统此时异常, A 减去后 B 还没加, 这个是不允许的, 要么系统正常后, B 加上 80, 要么 A 减去的 80 被还原, 这个是基于事务完成的
事务是可交叉并行的, 但是要考虑到隔离性
查看所有表的状态信息(表属性), 如下语句, G 将竖排显示内容, 且每行显示一次, 如果有多行信息, 将会分别显示多次
MariaDB [sunny]> show table statusG;
从物理意义上来讲, InnoDB 表由共享表空间, 日志文件组(redo 文件组), 表结构定义文件组成. 若将 innodb_file_per_table 设置为 on, 则系统将为每一个表单独的生成一个 table_name.ibd 的文件, 在此文件中, 存储与该表相关的数据, 索引, 表的内部数据字典信息. 表结构文件则以. frm 结尾, 这与存储引擎无关.
数据存储于 "表空间(table space)" 中, 有两种方式:
(1) 所有数据库中的所有类型为 InnoDB 的表的数据和索引存储于同一个表空间中;
表空间文件: datadir 定义的目录中, 如默认 / var/lib/mysql / 路径下, 表现为 ibdata1, ibdata2, ... 等文件, 文件数量和表数量并不一定是对应的
(2) innodb_file_per_table=ON, 意味着每表使用单独的表空间文件;
每表的数据文件 (数据和索引, 存储于数据库目录) 存储于自己专用的表空间文件中, 并存储于数据库目录下: tbl_name.ibd(表名. ibd)如数据库 sunny 下的表 classlist, 那么表空间文件为 / var/lib/mysql/sunny/classlist.ibd.
表结构的定义: 在数据库目录, tbl_name.frm(表名. frm) , 如 / var/lib/mysql/sunny/classlist.frm, 即存储表格式
ibdata1 介绍
innodb 系统表空间文件 ibdata1 存放三种数据:
a)回滚段
b)所有 innodb 表元数据信息 (这就是为什么 innodb 无法像 myisam 表一样, 直接将表定义文件(表名. frm) 和表数据文件(表名. ibd) 拷贝到另一个库中, 因为还有部分元数据信息在 ibdata1 文件中)
c)double write,insert buffer dump 等等
innodb_file_per_table 介绍
innodb_file_per_table 开启后, 会产生表定义文件表名. frm, 和表数据文件 表名. idb, 这样每个表的数据都会存在自己的. idb 文件中; 如果 关闭, 那么所有的数据都会 存在系统表空间文件 ibdata1 文件中, 这会 ibdata1 非常繁忙并且臃肿 庞大, 而且 ibdata1 无法 收缩的, 比如线上将一个 大的表 drop 掉, 此时 ibdata1 是无法自动缩小的(需要使用 optimiza table 来优化); 而如果开启, 数据存在 .idb 文件中, 则可以随时缩小;
共享表空间和独立表空间优缺点
共享表空间与独占表空间可以通过参数 innodb_file_per_table 来转换, 若为 1, 则开启独占表空间, 否则, 开启共享表存储.
在服务器资源有限, 单表数据不是特别多的情况下, 独立表空间明显比共享方式效率更高 . 但是 MySQL 默认是共享表空间 .
具体的共享表空间和独立表空间优缺点如下:
共享表空间:
优点:
可以放表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制, 如一个表可以分布在不同步的文件上). 数据和文件放在一起方便管理.
缺点:
所有的数据和索引存放到一个文件中以为着将有一个很常大的文件, 虽然可以把一个大文件分成多个小文件, 但是多个表及索引在表空间中混合存储, 这样对于一个表做了大量删除操作后表空间中将会有大量的空隙, 特别是对于统计分析, 日值系统这类应用最不适合用共享表空间.
独立表空间:
优点:
1.每个表都有自已独立的表空间.
2.每个表的数据和索引都会存在自已的表空间中.
3.可以实现单表在不同的数据库中移动.
4.空间可以回收(除 drop table 操作处, 表空不能自已回收)
a) Drop table 操作自动回收表空间, 如果对于统计分析或是日值表, 删除大量数据后可以通过: alter table TableName engine=innodb; 回缩不用的空间.
b) 对于使 innodb-plugin 的 Innodb 使用 truncate table 也会使空间收缩.
c) 对于使用独立表空间的表, 不管怎么删除, 表空间的碎片不会太严重的影响性能, 而且还有机会处理.
缺点:
单表增加过大, 如超过 100 个 G.
注意事项:
对于启用了 innodb_file_per_table 的参数选项之后, 在每个表对应的. idb 文件内只是存放了数据, 索引和插入缓冲, 而撤销 (undo) 信息, 系统事务信息, 二次写缓冲等还是存放在了原来的共享表空间内.
数据段即 B + 树的叶节点, 索引段即为 B + 树的非索引节点.
InnoDB 存储引擎的管理是由引擎本身完成的, 表空间是由分散的页和段组成.
区由 64 个连续的页组成, 每个页大小为 16K, 即每个区大小为 1MB, 创建新表时, 先有 32 页大小的碎片页存放数据, 使用完后才是区的申请,(InnoDB 最多每次申请 4 个区, 保证数据的顺序性能)
页类型有: 数据页, Undo 页, 系统页, 事务数据页, 插入缓冲位图页, 以及插入缓冲空闲列表页.
innodb 小结: innodb 的特点
数据存储: 表空间;
并发: MVCC, 间隙锁, 行级锁;
索引: 聚集索引, 辅助索引;
性能: 预读操作, 内存数据缓冲, 内存索引缓存, 自适应 Hash 索引, 插入操作缓存区;
备份: 支持热备; 备份是重点知识.
查看存储引擎的状态信息, 如 InnoDB 的状态
SHOW ENGINE INNODB STATUS;
5 MyISAM 引擎介绍
MyISAM 的特点:
支持全文索引(FULLTEXT index), 压缩, 空间函数(GIS); 这些是 innodb 不支持的特性
不支持事务
锁粒度: 表级锁
崩溃无法保证表安全恢复
适用场景: 只读 (数据仓库) 或读多写少的场景, 较小的表(以保证崩溃后恢复的时间较短);
文件: 每个表有三个文件, 存储于数据库目录中
tbl_name.frm: 表格式定义; 约束, 数据结构
tbl_name.MYD: 数据文件;
tbl_name.MYI: 索引文件;
例子
创建表 tbl1, 指定引擎为 MyISAM, 那么在路径 / var/lib/mysql/sunny 下就会生成三张表 tbl1.frm tbl1.MYD tbl1.MYI
create table tbl1 (ID int,name char(4)) engine=MyISAM;
特性:
加锁和并发: 表级锁;
修复: 手动或自动修复, 但可能会丢失数据;
索引: 非聚集索引;
延迟索引更新;
表压缩;
innodb 支持在线事务, myISAM 支持只读的数据仓库或者读多写少的场景
6 其它的存储引擎
CSV: 将 CSV 文件 (以逗号分隔字段的文本文件) 作为 MySQL 表文件;
MRG_MYISAM: 将多个 MyISAM 表合并成的虚拟表;
BLACKHOLE: 黑洞, 类似于 / dev/null, 不真正存储数据; 存进来的数据全部丢弃
MEMORY: 内存存储引擎, 支持 hash 索引, 表级锁, 常用于临时表; 不能持久存储
FEDERATED: 用于访问其它远程 MySQL 服务器上表的存储引擎接口;
MariaDB 额外支持很多种存储引擎:
OQGraph,SphinxSE,TokuDB,Cassandra,CONNECT,SQUENCE,...
来源: https://www.thinksaas.cn/group/topic/839742/