005：存储引擎介绍

一.存储引擎

1.MySQL上支持的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

2.存储引擎的概念

用来处理数据库的相关CRUD操作
每个数据库都有存储引擎，只是MySQL比较强调存储引擎的概念。

3. MySQL存储引擎

官方存储引擎
- MyISAM
- InnoDB -- 推荐；其他引擎已经体停止维护和开发
- Memory
- Federated
- CSV
- Archive
第三方存储引擎
- TokuDB -- 开源，适合插入密集型
- InfoBright -- 商业，开源版本有数据量限制。属于列存储，面向OLAP场景
- Spider

第三方存储引擎在特定场合下比较适合，除此之外，都应该使用InnoDB

3.存储引擎之MyISAM

3.1MyISAM特点

MySQL5.1版本之前的默认存储引擎
堆表数据结构
表锁设计
支持数据静态压缩
不支持事物
数据容易丢失
索引容易损坏
唯一优点
- 数据文件可以直接拷贝到另一台服务器使用
现在MySQL中还有用MyISAM的表，
- 主要是历史原因。数据库文件以MY开头的基本都是MyISAM的表
- 部分如User，DB等系统表(MyISAM引擎)，可以直接拷贝，比较方便
- 性能好，或者存储小不是MyISAM的优点，也不是存在的原因

3.2 MyISAM文件组成

frm 表结构文件
MYI 索引文件
MYD 数据文件
- 数据文件是堆表数据结构，堆是无序数据的集合
- MYI中的叶子节点，指向MYD中的数据页
- 当数据移动到页外时，需要修改对应指针

3.3 myisamchk

myisamchk 最好是关闭数据库，然后修复
不停机需要锁住表 lock table sysbench.test_log_copy read 再修复

mysql> show create table test_log_copyG;
*************************** 1. row ***************************
       Table: test_log_copy
Create Table: CREATE TABLE `test_log_copy` (
  `scenario` varchar(30) NOT NULL DEFAULT '' COMMENT '测试场景',
  `server_name` varchar(15) NOT NULL COMMENT '主机名',
  `test_type` varchar(15) NOT NULL COMMENT 'read-only,read-write,insert等',
  `sb_threads` int(11) NOT NULL DEFAULT '0' COMMENT 'sysbench 测试线程',
  `create_time` datetime DEFAULT NULL COMMENT '开始时间',
  `done_time` datetime DEFAULT NULL COMMENT '完成时间',
  `server_load` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '以当前线程测试完后立刻记录15分钟负载值',
  `request_read` int(11) NOT NULL DEFAULT '0',
  `request_write` int(11) NOT NULL DEFAULT '0',
  `transactions_per_second` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'QPS',
  `request_per_second` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'TPS',
  `95_pct_time` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '单位毫秒',
  KEY `createtime` (`create_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

[root@localhost-m(252) /r2/soft/dbtest/mysql-5.7.18/mysqldata]# myisamchk  sysbench/test_log_copy.MYI
Checking MyISAM file: sysbench/test_log_copy.MYI
Data records:      60   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links

myisamchk通过扫描MYD文件来重建MYI文件；如果MYD文件中某条记录有问题，将跳过该记录

4. Memory存储引擎

4.1 Memory介绍

全内存存储的引擎
数据库重启后数据丢失
支持哈希索引
不支持事物

4.2 Memory特性

千万不要用Memory存储引擎去做缓存(Cache), 性能上不及Redis和Memcahced
Memory不能禁用，当涉及内部排序操作的临时表时，使用该存储引擎
- max_heap_table_size决定使用内存的大小，默认时16M
  - 无论该表使用的什么引擎，只要使用到临时表，或者指定Memory，都受参数影响
- 当上面设置的内存放不下数据时，(>=5.6)转为MyISAM,(>=5.7)转为InnoDB
  - 注意磁盘上临时路径空间的大小(tmpdir)
- 内存使用为会话(SESSION)级别，当心内核OOM
支持哈希索引，且仅支持等值查询

mysql> show global status like "%tmp%tables";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 673   |      -- 内存放不下，转成磁盘存储的数量,如果过大，考虑增大内存参数
| Created_tmp_tables      | 6455  |      -- 创建临时表的数量
+-------------------------+-------+
2 rows in set (0.00 sec)


mysql> show variables like "tmp%";
+----------------+----------------------------------------+
| Variable_name  | Value                                  |
+----------------+----------------------------------------+
| tmp_table_size | 33554432                               |
| tmpdir         | /r2/soft/dbtest/mysql-5.7.18/mysqldata | -- memory转成磁盘存储的路径
+----------------+----------------------------------------+
2 rows in set (0.00 sec)

mysql> show create table UserG
*************************** 1. row ***************************
Table: User
Create Table: CREATE TABLE `User` (
`id` int(11) NOT NULL,
`name` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`) USING HASH          -- 对这个字段使用USING HASH,创建hash索引
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.3 Memory的物理特性

内存不会一次性分配最大空间，而是随着使用逐步增到到最大值
通过链表管理空闲空间
使用固定长度存储数据
不支持BLOB和TEXT类型
可以创建自增主键

5. CSV存储引擎

5.1 CSV介绍

CSV - Comma-Separated Values，使用逗号分隔
不支持特殊字符
CSV是一种标准文件格式
文件以纯文本形式存储表格数据
使用广泛

5.2 CSV文件组成

frm 表结构
CSV 数据文件
CSM 元数据信息

5.3 CSV特性

MySQL CSV存储引擎运行时，即创建CSV文件
通过MySQL标准接口来查看和修改CSV文件
无需将CSV文件导入到数据库，只需创建相同字段的表结构，拷贝CSV文件即可
CSV存储引擎表每个字段必须是NOT NULL属性

6.Federated存储引擎

6.1. Federated介绍

允许本地访问远程MySQL数据库中表的数据
本地不存储任何数据文件
类似Oracle中的DBLink
Federated存储引擎默认不开启, 需要在my.cnf的[mysqld]标签下添加 federated
MySQL的Federated不支持异构数据库访问，MariaDB中的FederatedX支持

Federated

6.2 Federated 语法

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

CONNECTION='mysql://username:password@hostname:port/database/tablename'

例句如下：

CREATE TABLE `test001` (
  `scenario` varchar(30) NOT NULL DEFAULT '' COMMENT '测试场景',
  `server_name` varchar(15) NOT NULL COMMENT '主机名',
  `test_type` varchar(15) NOT NULL COMMENT 'read-only,read-write,insert等',
  `sb_threads` int(11) NOT NULL DEFAULT '0' COMMENT 'sysbench 测试线程',
  `create_time` datetime DEFAULT NULL COMMENT '开始时间',
  `done_time` datetime DEFAULT NULL COMMENT '完成时间',
  `server_load` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '以当前线程测试完后立刻记录15分钟负载值',
  `request_read` int(11) NOT NULL DEFAULT '0',
  `request_write` int(11) NOT NULL DEFAULT '0',
  `transactions_per_second` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'QPS',
  `request_per_second` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'TPS',
  `95_pct_time` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '单位毫秒',
  KEY `createtime` (`create_time`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://federated:123456@192.168.48.168:3306/sysbench/test_log';

6.3 Federated测试

终端A(root@252)-192.168.48.168


mysql> create user federated@'192.168.24.180' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on sysbench.* to federated@'192.168.24.180';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for federated@'192.168.24.180';
+--------------------------------------------------------------+
| Grants for federated@192.168.24.180                          |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'federated'@'192.168.24.180'           |
| GRANT SELECT ON `sysbench`.* TO 'federated'@'192.168.24.180' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

--终端A数据表为sysbench.test_log表

mysql> select scenario ,server_name, test_type,sb_threads,create_time,done_time ,server_load from sysbench.test_log limit 1;
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+
| scenario          | server_name | test_type  | sb_threads | create_time         | done_time           | server_load |
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+
| oltp_update_index | localhost   | read-write |          2 | 2017-11-15 11:55:07 | 2017-11-15 12:45:18 |        1.56 |
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+
1 row in set (0.00 sec)

终端B(proxy.gczheng.com)-192.168.24.180

--测试账号可用
[root@proxy ~]# mysql -h192.168.48.168 -ufederated -p123456 -e "select scenario ,server_name, test_type,sb_threads,create_time,done_time ,server_load from sysbench.test_log limit 1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+
| scenario          | server_name | test_type  | sb_threads | create_time         | done_time           | server_load |
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+
| oltp_update_index | localhost   | read-write |          2 | 2017-11-15 11:55:07 | 2017-11-15 12:45:18 |        1.56 |
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+


(root@localhost) 13:21:28 [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |   -- FEDERATED引擎没有启动
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

[root@proxy ~]# cat /etc/my.cnf
# ... 省略 ...
[mysqld]
federated           -- 新增的配置项，表示打开Federated引擎
# ... 省略 ...

--重启MySQL
[root@proxy ~]# /usr/local/mysql/support-files/mysql.server stop
Shutting down MySQL..... SUCCESS!
[root@proxy ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL... SUCCESS!


(root@localhost) 13:23:28 [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |  -- 显示YES,表示federated引擎已经启用
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)


(gcdb@localhost) 13:47:40 [(none)]>  create database federated;   --创建federated库
Query OK, 1 row affected (0.00 sec)

(gcdb@localhost) 13:47:53 [(none)]> use federated;
Database changed

--创建test001库映射（终端A sysbench.test_log表）
(gcdb@localhost) 13:48:02 [federated]> CREATE TABLE `test001` (
    ->   `scenario` varchar(30) NOT NULL DEFAULT '' COMMENT '测试场景',
    ->   `server_name` varchar(15) NOT NULL COMMENT '主机名',
    ->   `test_type` varchar(15) NOT NULL COMMENT 'read-only,read-write,insert等',
    ->   `sb_threads` int(11) NOT NULL DEFAULT '0' COMMENT 'sysbench 测试线程',
    ->   `create_time` datetime DEFAULT NULL COMMENT '开始时间',
    ->   `done_time` datetime DEFAULT NULL COMMENT '完成时间',
    ->   `server_load` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '以当前线程测试完后立刻记录15分钟负载值',
    ->   `request_read` int(11) NOT NULL DEFAULT '0',
    ->   `request_write` int(11) NOT NULL DEFAULT '0',
    ->   `transactions_per_second` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'QPS',
    ->   `request_per_second` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT 'TPS',
    ->   `95_pct_time` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '单位毫秒',
    ->   KEY `createtime` (`create_time`)
    -> ) ENGINE=federated  connection='mysql://federated:123456@192.168.48.168:3306/sysbench/test_log';
Query OK, 0 rows affected (0.01 sec)


(gcdb@localhost) 15:26:15 [federated]> select scenario ,server_name, test_type,sb_threads,create_time,done_time ,server_load from test001 limit 1,10;  --测试可以查到数据
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+
| scenario          | server_name | test_type  | sb_threads | create_time         | done_time           | server_load |
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+
| oltp_update_index | localhost   | read-write |          4 | 2017-11-15 12:45:18 | 2017-11-15 13:35:33 |        1.03 |
| oltp_update_index | localhost   | read-write |          8 | 2017-11-15 13:35:33 | 2017-11-15 14:25:40 |        0.93 |
| oltp_update_index | localhost   | read-write |         16 | 2017-11-15 14:25:40 | 2017-11-15 15:15:40 |        1.30 |
| oltp_update_index | localhost   | read-write |         24 | 2017-11-15 15:15:40 | 2017-11-15 16:04:40 |        1.66 |
| oltp_update_index | localhost   | read-write |         32 | 2017-11-15 16:04:40 | 2017-11-15 16:46:26 |        2.09 |
| oltp_update_index | localhost   | read-write |         64 | 2017-11-15 16:46:26 | 2017-11-15 17:26:17 |        2.64 |
| oltp_update_index | localhost   | read-write |         96 | 2017-11-15 17:26:17 | 2017-11-15 18:06:47 |        1.90 |
| oltp_update_index | localhost   | read-write |        128 | 2017-11-15 18:06:48 | 2017-11-15 18:47:24 |        2.52 |
| oltp_update_index | localhost   | read-write |        196 | 2017-11-15 18:47:24 | 2017-11-15 19:27:57 |        2.44 |
| oltp_update_index | localhost   | read-write |          2 | 2017-11-16 03:48:32 | 2017-11-16 04:38:32 |        5.49 |
+-------------------+-------------+------------+------------+---------------------+---------------------+-------------+
10 rows in set (0.01 sec)

-- 由于只有select权限，无法对该`federated`.`test001`表进行insert操作
(gcdb@localhost) 15:27:09 [federated]> INSERT INTO `federated`.`test001` (
    -> `scenario`,
    -> `server_name`,
    -> `test_type`,
    -> `sb_threads`,
    -> `create_time`,
    -> `done_time`,
    -> `server_load`,
    -> `request_read`,
    -> `request_write`,
    -> `transactions_per_second`,
    -> `request_per_second`,
    -> `95_pct_time`
    -> )
    -> VALUES
    -> (
    -> '扯淡计划',
    -> '拯救小黑羊',
    -> 'read-write',
    -> '2',
    -> '2017-11-24 11:55:07',
    -> '2017-11-24 12:45:18',
    -> '1.00',
    -> '0',
    -> '699408',
    -> '290.10',
    -> '290.10',
    -> '0.78'
    -> );
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1142: INSERT command denied to user 'federated'@'192.168.24.180' for table '' from FEDERATED

005： 存储引擎介绍