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) 什么是表空间
表空间的概念是引入与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
- 事务日志
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 内存
- 数据内存区域
共享内存缓冲区
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
- 日志
innodb_log_buffer_size=16777216
功能:负责redo日志的缓冲