MySQL-11-存储引擎
存储引擎简单介绍
存储引擎:相当于Linux文件系统，只不过比文件系统强大
功能
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
存储引擎种类
InnoDB
MyISAM
MEMORY
CSV
引擎种类查看
mysql> show engines;
存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性
innodb存储引擎简单介绍
在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能
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)
使用 SELECT 确认会话存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
修改存储引擎(不代表生产操作)
会话级别:
set default_storage_engine=myisam;
全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.
如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb
确认每个表的存储引擎
select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
修改一个表的存储引擎
alter table t1 engine innodb;
注意：此命令我们经常使用他，进行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';
innodb存储引擎物理存储结构
innodb存储引擎物理存储结构，最直观的存储方式(/data/mysql/data)
ibdata1：系统数据字典信息(统计信息)，UNDO表空间等数据
ib_logfile0 ~ ib_logfile1：REDO日志文件，事务日志文件
ibtmp1：临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引
表空间
1、共享表空间
需要将所有数据存储到同一个表空间中，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式
5.6版本以后，共享表空间保留，只用来存储:数据字典信息,undo,临时表
5.7版本，临时表被独立出来了
8.0版本，undo也被独立出去了
具体变化参考官方文档:
https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.8/en/innodb-architecture.html
共享表空间设置
共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
mysql> select @@innodb_data_file_path;
mysql> show variables like '%extend%';
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64
2、独立表空间
从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操作产生临时数据，用完就自动清理
独立表空间设置问题
mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+
alter table city dicard tablespace;
alter table city import tablespace;
事务
事务的ACID特性
Atomic （原子性）所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态
Consistent（一致性）如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态
Isolated （隔离性）事务之间不相互影响
Durable （持久性）事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失
事务的生命周期（事务控制语句）
1 事务的开始
begin
说明:在5.5以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令
2 事务的结束
commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了
rollback：回滚事务
将内存中，已执行过的操作，回滚回去
3 自动提交策略（autocommit）
mysql> select @@autocommit;
mysql> set autocommit=0;
mysql> set global autocommit=0;
注：自动提交是否打开?
一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
(1)
set autocommit=0;
set global autocommit=0;
(2)
vim /etc/my.cnf
autocommit=0
4 隐式提交语句
用于隐式提交的SQL语句：
begin
a
b
begin
SET AUTOCOMMIT = 1
导致提交的非事务语句：
DDL语句：（ALTER、CREATE 和 DROP）
DCL语句：（GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
5 开始事务流程：
1、检查autocommit是否为关闭状态
select @@autocommit;
或者：
show variables like 'autocommit';
2、开启事务,并结束事务
begin
delete from student where name='tomsb';
update student set name='Jerry' where name='tom';
rollback; // 回滚
begin
delete from student where name='tomsb';
update student set name='Jerry' where name='tom';
commit; // 提交
转载请注明出处哟~
https://www.cnblogs.com/lichengguo
