一、MySQL存储引擎种类
查看命令:show engines;
MRG_MYISAM
CSV
MyISAM
BLACKHOLE
PERFORMANCE_SCHEMA
InnoDB
ARCHIVE
MEMORY
FEDERATED
其他产品存储引擎(percona、mariadb)
TokuDB
MyRocks
二、InnoDB 核心特性介绍
1、聚簇索引(Clustered index )
用来组织存储数据和优化查询
2、修改缓冲区(Change buffer)
3、自适应hash索引:AHI
4、MVCC
多版本并发控制
5、多缓冲区池
减少磁盘IO
6、事务
数据安全保证
7、行级锁粒度
控制并发
8、外键
9、支持更多复制特性
10、支持热备
11、自动故障恢复(CSR)
12、双写机制(Double Write)
三、存储引擎管理
(一)、默认存储引擎修改
1、会话级别
set default_storage_engine=myisam;
2、全局级别(仅影响新会话)
set global default_storage_engine=myisam;
3、永久生效
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
systemctl restart mysql
(二)、查看所有生产表的存储引擎
select table_schema,table_name ,engine
from information_schema.tables
where table_schema not in ('sys','mysql','information_schema','performance_schema');
(三)、修改表的存储引擎
alter table 表名称 engine=引擎名称;
例:alter table x engine=innodb;
(四)、批量修改存储引擎
将zabbix库中的所有表,innodb替换为tokudb
select concat("alter table zabbix.",table_name," engine tokudb;")
from information_schema.tables
where table_schema='zabbix'
into outfile '/tmp/tokudb.sql';
source /tmp/tokudb.sql
将所有非InnoDB业务表查询出来,并修改为InnoDB
select concat("alter table ",table_schema,".",table_name," engine=innodb;" )
from information_schema.tables
where engine != 'InnoDB'
and table_schema not in ('sys','performance_schema','information_schema','mysql')
into outfile '/tmp/alter.sql' ;
source /tmp/alter.sql
四、MySQL 存储引擎体系结构
(一)、宏观结构
1、MyISAM
myt.frm : 数据字典信息(列的定义和属性)
myt.MYD : 数据行
myt.MYI : 索引
2、InnoDB
city.frm : 数据字典信息(列的定义和属性)
city.ibd(独立表空间文件) : 数据行和索引
ibdata1 (共享表空间文件) : 数据字典信息,UNDO(事务回滚日志),double write 磁盘区域,change buffer磁盘区域
(二)、InnoDB 微观结构(体系结构)
1、磁盘
①、数据存储
a,表空间
表空间概念
表空间概念是引入于Oracle数据库。
起初为了解决存储空间扩展的问题。MySQL5.5 版本引入了共享表空间模式。
表空间结构
表 ----> 表空间 ---> 段 ---> 多个区 ---> 连续的page ---> 连续的block ----> 连续的扇区
MySQL 表空间类型
1、共享表空间
用来存储:系统数据,日志,undo,临时表,用户数据和索引
2、独立表空间
5.6 版本默认独立表空间模式。单表单表空间。
3、普通表空间
完全和Oracle一致的表空间管理模式
4、undo表空间
存储undo logs(回滚日志)
5、临时表空间
6、存储临时表。5.7 默认独立
b.共享表空间
共享表空间演变
5.5版本:
系统相关:(全局)数据字典信息、UNDO回滚信息、Double Write信息、临时表信息、change buffer
用户数据: 表数据行、表的索引数据
5.6版本:共享表空间只存储于系统数据,把用户数据独立了。
系统相关:(全局)数据字典信息、UNDO回滚信息、Double Write信息、临时表信息、change buffer
5.7版本:在5.6基础上,把临时表独立出来,UNDO也可以设定为独立
系统相关:(全局)数据字典信息、UNDO回滚信息、Double Write信息、change buffer
8.0.19版本:在5.7的基础上将UNDO回滚信息默认独立
系统相关:Double Write信息、change buffer
8.0.20版本:在之前版本基础上,独立 Double Write信息
系统相关:change buffer
生产中设置建议
5.7 中建议:设置共享表空间2-3个,大小建议512M或者1G,最后一个定制为自动扩展。
8.0 中建议:设置1个就ok,大小建议512M或者1G
c.共享表空间管理
查看共享表空间
select @@innodb_data_file_path;
ibdata1文件,默认初始大小12M,不够用会自动扩展,默认每次扩展64M
查看共享表空间每次扩展大小
select @@innodb_autoextend_increment;
设置方法
方法1:初