Mysql 存储引擎
1.什么是存储引擎
相当于系统的文件系统
2.存储引擎分类
InnoDB
MyISAM
MEMORY
CSV
等
2.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)存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高 现在很多的NewSQL,使用比较多的功能特性.
3.InnoDB介绍
mysql5.5之后默认引擎为InnoDB
InnoDB优点
1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )
查看默认的存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
@@default_storage_engine
+--------------------------+
InnoDB
+--------------------------+
1 row in set (0.00 sec)修改默认存储引擎
会话级别:
set default_storage_engine=myisam;
全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.
如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.查看所有除系统表的存储引擎
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
+--------------+------------+--------+
school score InnoDB
school student InnoDB
+--------------+------------+--------+
2 rows in set (0.00 sec)修改一个表的存储引擎 比如t1表
alter table t1 engine innodb;批量修改一个库下多个表的存储引擎，比如zabbix
select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';InnoDB的物理储存结构
ibdata1：系统数据字典信息(统计信息)，UNDO表空间等数据
ib_logfile0 ~ ib\_logfile1: REDO日志文件，事务日志文件。
ibtmp1： 临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引
修改共享表空间
共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
[(none)]>select @@innodb_data_file_path;
[(none)]>show variables like '%extend%';
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64独立表空间
从5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
基本表结构元数据存储：
xxx.frm
最终结论：
元数据 数据行+索引
mysql表数据 =（ibdataX+frm）+ibd(段、区、页)
DDL DML+DQL
MySQL的存储引擎日志：
+ Redo Log: ib_logfile0 ib_logfile1，重做日志
+ Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
+ 临时表:ibtmp1，在做join union操作产生临时数据，用完就自动
独立表空间设置
查看 1代表就是使用的独立表空间
mysql> select @@innodb_file_per_table;
+-------------------------+
@@innodb_file_per_table
+-------------------------+
1
+-------------------------+
1 row in set (0.00 sec)删除和导入表空间
alter table table_name dicard tablespace;
alter table table_name import tablespace;上面这两条语句主要用于数据库表空间丢失的处理
比如宕机造成数据库无法启动 ，我们需要新搭建一套数据库环境 创建原来的表 然后执行dicard tablespace删除我们自己创建的表空间 ，把故障机器的表空间文件frm复制过来 重新import tablespace 就可以恢复数据
缓存区池
查询
select @@innodb_buffer_pool_size;
show engine innodb status \G
innodb_buffer_pool_size一般建议最多是物理内存的 75-80%
双一
innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
select @@innodb_flush_log_at_trx_commit;
1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush 到操作系统的文件系统缓存 fsync到物理磁盘.
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次；
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作。
Innodb_flush_method=(O_DIRECT, fdatasync)
控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存
show variables like ‘%innodb_flush%’;
O_DIRECT :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,都走OS buffer
O_DSYNC :日志缓冲区写磁盘,不走 OS buffer
使用建议
最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsyncredo日志有关的参数
innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3