mysql存储引擎

Innodb引擎用表空间进行数据存储：

innodb_file_per_table

ON：独立表空间：tablename.ibd （mysql5.6后版本为默认配置） .frm文件是记录数据表结构的文件，.ibd文件是innodb实际存储数据的文件

OFF：系统表空间：ibddataX .frm ibddataX

注意：frm文件是mysql服务器层产生的，mysql服务器层保存的东西与存储引擎是无关的

innodb数据字典：是innodb内部产生的，可以保证事务的安全性，是本身封装了一些自定义

查询：

mysql> show variables like '%innodb_file_per%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)

创建表，指定引擎：engine

mysql> create table myinnodb(id int,name varchar(10)) engine='innodb';

比较：

系统表空间无法简单的收缩文件文件大小
独立表空间可以通过optimize table 命令收缩系统文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据（对于频繁写入的业务特别适合）

数据转移：系统表空间数据------------>独立表空间数据

步骤：

（1）mysqldump导出所有的数据库表数据，记得数据存储过程，触发器要一起导出

（2）停止mysql服务器，修改参数innodb_file_per_table=ON，并删除innodb相关文件

（3）重启mysql服务，重建innodb系统表空间

（4）重新导入数据

innodb是事务性的存储引擎
完全支持事务的ACID特性（原子性，一致性，隔离性，持久性）
日志类型： Redo log（重做日志。实现事务的持久性）和 Undo log (回滚日志)
innodb支持行级锁，可以最大程度的支持并发注意：行级锁是在存储引擎层实现的

mysql> show variables like 'innodb_log_buffer_size'; （redo log 默认配置缓存区的大小）

+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 | 数字以字节为单位的，每隔一秒都会将缓存区刷新到磁盘上，并不需要配置太大
+------------------------+----------+

锁：

锁主要作用是管理共享资源的并发访问
锁用于实现事务的隔离性

锁的类型：

共享锁（读锁）读锁直接是相互不会阻塞的
独占锁（写锁）

锁的粒度：

表级锁
行级锁是在存储引擎中实现的

表级独占锁演示：

终端一：加一个表级独占锁：

mysql> lock table myinnodb write;

终端二：查看这个表：结果是显示不出数据

终端一：解锁表

mysql> unlock tables;

终端二：刚刚查看的表：结果显示出了数据

阻塞和死锁

什么是阻塞因为不同锁之间兼容性关系，有些时刻一个事务中的锁需要等待另一个事务中的锁释放，所占用的资源 eg:上边的表级锁演示
- 阻塞是为了确保事务可以并发且正常的运行，如果出现大量阻塞，可能是有些表出现了慢查询等一些问题，大量阻塞会导致系统的整体资源性能下降
什么是死锁指俩个或俩个以上的事务在执行过程中，相互占用了对方等待的资源而产生的异常

innodb状态检查：

mysql> pager more; 相当于less的作用，可以对下边输出信息翻页查看

mysql> show engine innodb status\G 所显示信息中可以看到读跟写的进程，默认为4读4写，可配置

Innodb适用场景：

mysql5.7之前全文索引跟空间函数只能用myisam
mysql5.7之后的版本，innodb也支持全文索引、空间函数，innodb适合于大多数的OLTP应用

mysql常用存储引擎 Myisam： mysql5.5之前默认存储引擎

文件系统存储特点：

MYD为数据文件，MYI为索引文件，frm为数据结构文件
并发性和锁级别表级锁，对于读写混合并发性并不好
表损坏修复可能会造成数据丢失命令行工具：

mysql> check table myarz; 检查表
+---------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| thetest.myarz | check | status | OK |
+---------------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> repair table myarz; 修复表
+---------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------+----------+----------+
| thetest.myarz | repair | status | OK |
+---------------+--------+----------+----------+
1 row in set (0.01 sec)

特性：

myisam表支持数据压缩命令行：myisampack

限制：

MYSQL5.0之前版本默认支持表大小为4G，如果要存储大表需要修改：MAX_Rows 和 AVG_ROW_LENGTH 这俩个参数数值相乘的大小为准
mysql5.0版本以后默认支持单表大小为256TB

适用场景：

非事务型应用
只读类报表应用只读之间共享锁不会堵塞
空间类应用

mysql常用存储引擎 CSV：

文件系统存储特点：

数据CSV文件以文本形式在文件中，可以直接less、more、vim去查看
.csv文件存储表内容
.csm文件存储标的元数据如表状态和数据量
.frm文件存储表结构信息

特点：

以csv格式进行数据存储
所有列必须都不能是NULL的
不支持索引（对于表的查询效率特别低）
支持对数据文件直接编辑，保存文本文件内容

不支持表的列为NULL的演示：

mysql> create table mycsv(id int,name varchar(10)) engine=csv; 以下报错就说明了csv索引是不支持表的列为NULL

ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

mysql> create table mycsv(id int not null,name varchar(10) not null) engine=csv;
Query OK, 0 rows affected (0.02 sec)

不支持索引的演示：

mysql> create index idx_id on mycsv(id);
ERROR 1069 (42000): Too many keys specified; max 0 keys allowed 0的关键键表示不支持索引

适用场景：

适合作为数据交换的中间件

mysql常用存储引擎 Archive：

文件系统存储特点：

以zlib对表数据进行压缩，磁盘I/O更少比如10T的数据表，如果用Archive可能只需要几百M就够了
数据存储在以ARZ为后缀的文件中

功能特点：

只支持insert跟select操作
只允许在自增ID列上加索引

mysql> create table myarz(id int auto_increment not null,name varchar(10),key(id)) engine=Archive;

mysql> delete from myarz; 1031报错表示不支持这种操作
ERROR 1031 (HY000): Table storage engine for 'myarz' doesn't have this option

mysql> update myarz set name='bbb' where id=1;
ERROR 1031 (HY000): Table storage engine for 'myarz' doesn't have this option

mysql> create index idx_id on myarz(id);
ERROR 1069 (42000): Too many keys specified; max 1 keys allowed

使用场景：

日志和数据采集类应用日志采集表，数据记录表 注意：只能收集跟记录，不能修改

mysql常用存储引擎 Memory：

文件系统存储特点：

也称HEAP存储引擎，所有的数据保存在内存中
数据保存在内存说明数据是易丢失的，一旦mysql服务器重启，所有memory表中的数据都会丢失，但是表结构会保留

功能特点：

支持HASH索引和BTree索引（默认为HASH索引，等值查询HASH索引特别快，范围查询要用BTree索引）
所有字段都为固定长度varchar(10)=char(10)
不支持BLOG和TEXT等大字段
使用表级锁

默认值为16M，修改以后对已经存在的memory引擎的表是无效的，只能对表重建进行生效

不支持text大表类型

mysql> create table mymem(id int,c1 varchar(10),c2 char(10),c3 text) engine=memory;
ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

建立索引

mysql> create index idx_c1 on mymem(c1);

mysql> create index idx_c2 using btree on mymem(c2);

mysql> show create table mymem;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mymem | CREATE TABLE `mymem` (
`id` int(11) DEFAULT NULL,
`c1` varchar(10) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`) USING BTREE
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |

查看索引

mysql> show index from mymem\G

Column_name: c1
Index_type: HASH

Column_name: c2
Index_type: BTREE

查看表的状态信息：

mysql> show table status like 'mymem'\G

适用场景：

用于查找或者映射表，例如邮编和地区的对应表
用于保存数据分析中产生的中间表
用于缓存周期性聚合数据的结果表
注意：memory数据容易丢失，所以要求数据可以再生

mysql常用存储引擎 Federated：当前版本mysql默认是禁止的

文件系统存储特点：

提供了访问远程mysql服务器上表的方法
本地不存储数据，数据都放在了远程服务器上
本地需要保存表结构和远程服务器连接的信息

如何使用

默认禁止，启动需要在启动时增加federated参数
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name

1.mysql> show engines\G

Engine: FEDERATED
Support: NO 默认是不支持的
Comment: Federated MySQL storage engine

2.在my.cnf文件配置,然后重启mysql

#支持Federated引擎,1表示开启
federated=1

3.mysql> show engines\G

Engine: FEDERATED
Support: YES

演示：

创建俩个库：一个远程，一个本地

mysql> create database local; 本地

mysql> create database remote; 远程

远程数据库创建表，插入数据，创建用户并授权

mysql> use remote;

mysql> create table remote_fed(id int auto_increment not null,c1 varchar(10) not null default '',c2 char(10) not null default '',primary key(id))engine=innodb;

mysql> insert into remote_fed(c1,c2) values('aaa','bbb'),('ccc','ddd'),('eee','fff');

mysql> grant all privileges on remote.* to 'fred'@'%' identified by '111';

使用本地库，创建连接通道

mysql> create table local_fed(id int auto_increment not null,c1 varchar(10) not null default '',c2 char(10) not null default '',primary key(id))engine=federated connection='mysql://fred:111@192.168.122.15:3306/remote/remote_fed';

mysql> select * from local.local_fed; 下面报错是因为：同一个ip在短时间内产生太多（超过mysql数据库max_connection_errors的最大值）中断的数据库连接而导致的阻塞；
ERROR 1429 (HY000): Unable to connect to foreign data source: Host '192.168.122.15' is blocked because of many connection erro

解决方法：mysql> flush hosts;

mysql> select * from local.local_fed;
+----+-----+-----+
| id | c1 | c2 |
+----+-----+-----+
| 1 | aaa | bbb |
| 2 | ccc | ddd |
| 3 | eee | fff |
+----+-----+-----+

适用场景

偶尔的统计分析和手工查询

如何使用存储引擎

参考条件：

事务 ------如果要支持事务的话，innodb是首选
备份 ------可以在线热备的存储引擎，只有innodb拥有免费热备方案
注意：mysqldump不算是热备，他是一种逻辑备份，在备份的时候会对数据加锁
崩溃恢复 -------myisam数据比Innodb存储引擎数据更容易丢失
存储引擎的特有特性 -------建议不要混合使用存储引擎

啊

posted @ 2018-09-26 16:08 天宇星空阅读(539) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

天宇星空

mysql存储引擎

Innodb引擎用表空间进行数据存储：

锁：

阻塞和死锁

Innodb适用场景：

mysql常用存储引擎 Myisam： mysql5.5之前默认存储引擎

mysql常用存储引擎 CSV：

mysql常用存储引擎 Archive：

mysql常用存储引擎 Memory：

mysql常用存储引擎 Federated： 当前版本mysql默认是禁止的

如何使用存储引擎

参考条件：

公告

mysql常用存储引擎 Federated：当前版本mysql默认是禁止的