MySQL之存储引擎

1. 存储引擎种类

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | 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         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

-- 默认引擎:
MySQL：InnoDB
MariaDB：InnoDB
PerconaDB：XtraDB

-- 其他存储引擎支持：
TokuDB
RocksDB
MyRocks
以上三种存储引擎的共同点：压缩比较高、数据插入性能极高

2. InnoDB核心特性

• Clustered index
• Chang buffer
• AHI：自适应哈希索引
• MVCC：多版本并发控制
• 多缓冲区池
• 支持事务
• 行级锁粒度
• 支持外键
• 更多复制特性
• 支持热备份
• 自动故障恢复

3. 存储引擎的管理命令

3.1 查看存储引擎

-- 查看当前会话默认存储引擎
mysql>  select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

-- 通过show查看每个表的存储引擎
mysql> show create table city\G;
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
--------------------------------------------------------------
mysql> show table status like 'city'\G
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4188
 Avg_row_length: 97
    Data_length: 409600
Max_data_length: 0
   Index_length: 131072
      Data_free: 0
 Auto_increment: 4080
    Create_time: 2020-06-16 19:19:34
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: 
1 row in set (0.00 sec)

-- 通过information_schema确认每个表的存储引擎
mysql> select table_schema,table_name,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
+--------------+-----------------+--------+
| table_schema | table_name      | engine |
+--------------+-----------------+--------+
| awei         | user            | InnoDB |
| school       | course          | InnoDB |
| school       | sc              | InnoDB |
| school       | student         | InnoDB |
| school       | t1              | InnoDB |
| school       | teacher         | InnoDB |
| test         | t100w           | InnoDB |
| world        | city            | InnoDB |
| world        | country         | InnoDB |
| world        | countrylanguage | InnoDB |
| world        | t1              | InnoDB |
+--------------+-----------------+--------+
11 rows in set (0.02 sec)

3.2 更改默认存储引擎

-- 会话级别(仅影响当前会话)
set default_storage_engine=innodb;

-- 全局级别(仅影响新会话，不会修改当前会话)
set global default_storage_engine=innodb;

-- 以上两种方法重启mysql服务后会失效
-- 永久生效：写入peizhiwenj
[mysqld]
default_storage_engine=innodb

3.3 修改表的存储引擎

-- 此命令我们经常使用它，进行innodb表的碎片整理
alter table x engine=innodb;

-- 碎片处理处理问题
-- 业务特点: 数据量级较大，经常需要按月删除历史数据
-- 处理方法：
---- 以前: 将数据逻辑导出，手工drop表，然后重新导入
---- 现在：对表进行按月分表(partition、中间件)或者归档表(pt-archive)；业务替换为truncate方式

-- 案例：2亿行的表，想删除其中1000w条
	1. 如果还未生成，建议在设计表时采用分区表的方式(按月range)，然后删除时truncate
	2. 如果已经存在，建议使用pt-archive工具进行归档表，并且删除无用数据

3.4 批量修改案例

-- 将zabbix库中所有表的存储引擎替换为tokudb
select concat('alter table zabbix.',table_name,' engine=tokudb;')
from information_schema.tables
where table_schema='zabbix'
into outfile '/tmp/tokudb.sql';

-- 将所有非InnoDB业务表查询出来，并修改为InnoDB
select concat('alter table ',table_schema,'.',table_name,' engine=innodb')
from infomation_schema.tables
where engine!='InnoDB'
and table_schema not in ('sys','performance_schema','infomation_schema','mysql')
into outfile '/tmp/alter.sql';

4. 存储引擎体系结构

4.1 宏观结构

4.1.1 MyISam

• myt.frm：存放数据字典信息（列的定义和属性）
• myt.MYD：存放数据行信息
• myt.MYI：存放索引信息

4.1.2 InnoDB

• myt.frm：存放数据字典信息（列的定义和属性）

• myt.ibd：独立表空间文件，存放数据行和索引信息

• ibdata1：共享表空间文件，存放数字字典信息，undo logs、double write、change buffer磁盘区域

  • 不同版本ibdata1中存储的数据不一样

  • 5.5：ibdata1 中还会存储临时表数据 + 用户数据(数据行+索引)

  • 5.6：ibdata1 中还会存储临时表数据

    

  • 8.0：ibdata1 取消存储数据字典信息，将 undo logs 独立出去

    

  • ibdata1 在慢慢瘦身，将比较关键的数据独立出来了

• ib_logfile0、ib_logfile1：InnoDB 事务重做日志（redo logs）

• ibtmp1：临时表空间文件（排序、分组、多表连接、子查询、逻辑备份等）

• ib_buffer_pool：正常关库时，存储缓冲区的热数据

• 结论：仅仅拷贝ibd、frm文件到新的数据库是无法正常使用的

4.2 InnoDB微观结构

4.2.1 磁盘

1. 表空间

(1) 什么是表空间
	表空间的概念是引入与Oracle数据库
	起初是为了解决存储空间扩展的问题，MySQL5.5引入了共享表空间模式

(2) MySQL表空间类型
	共享表空间：在5.5版本引入了共享表空间(ibdata1)作为默认存储方式，用于存储系统数据、日志、undo、临时表、用户数据和索引
	独立表空间：5.6版本默认独立表空间模式，单表单表空间
	普通表空间：完全和Oracle一致的表空间管理模式，更加自定义化
	undo表空间：存储undo logs(回滚日志)
	临时表空间：存储临时表，5.7默认独立

(3) 表空间管理
	用户数据默认的存储方式：独立表空间模式(5.6后)，独立表空间和共享表空间是可以相互切换的
	-- 查看默认表空间模式
	-- 1代表独立表空间模式；0代表共享表空间模式
	select @@innodb_file_per_table;
	
	-- 切换表空间模式
	-- 说明：修改完成后只影响新创建的表
	set global innodb_file_per_table=0;
	
	-- 查看共享表空间大小和个数
	mysql> select @@innodb_data_file_path;
	+-------------------------+
	| @@innodb_data_file_path |
	+-------------------------+
	| ibdata1:12M:autoextend  |
	+-------------------------+
	1 row in set (0.00 sec)
	
	-- 扩展共享表空间大小和个数
	-- 说明：通常是在初始化数据时就设定号参数
	-- 方法1：初始化之前，在my.cnf加入以下配置
	innodb_data_file_path=idata1:1G;idata2:1G:autoextend
	-- 方法2：在已运行中的数据库上扩展多个ibdata文件
	-- 在设置innodb_data_file_path参数时，已有的ibdata1大小应该和磁盘上真实大小一致，再进行添加
	innodb_data_file_path=idata1:xx;idata2:1G:idata3:1G:autoextend

2. 事务日志

redo logs：重做日志
(1) 文件位置
	/data/3306/ib_logfile0~ib_logfileN
(2)	控制参数
	-- 设置文件大小
	innodb_log_file_size=50331648
	-- 设置文件个数
	innodb_log_files_in_group=2
	-- 设置存储位置
	innodb_log_group_home_dir=./
(3) 功能：
	用来存储MySQL在做修改类(DML)操作时数据页变化过程及版本号(LSN),属于物理日志
	默认两个文件存储redo，是循环覆盖使用的

undo logs：回滚日志
(1) 文件位置
	/data/3306/ibdata1
	/data/3306/ibtmp1
(2) 控制参数
	-- 回滚段的个数
	innodb_rollback_segments=128
(3) 功能:
	用来存储回滚日志，相当于快照，提供InnoDB多版本并发读写，属于逻辑日志
	通过记录的每次操作的反操作，提供回滚功能

4.2.2 内存

1. 数据内存区域

共享内存缓冲区
buffer pool：缓冲区池
(1) 控制参数
    mysql> select @@innodb_buffer_pool_size;
    +---------------------------+
    | @@innodb_buffer_pool_size |
    +---------------------------+
    |                 134217728 |
    +---------------------------+
    1 row in set (0.00 sec)
(2) 功能：
	缓冲数据页和索引页

------------------------------------------------------------------
会话内存缓冲区
join_buffer_size
key_buffer_size
read_buffer_size
read_rnd_buffer_size
sort_buffer_size

2. 日志

innodb_log_buffer_size=16777216
功能：负责redo日志的缓冲