存储引擎+InnoDB核心特性(ACID)-事务介绍

作者: 新_WX | 来源:发表于2019-08-13 12:24 被阅读0次

1. 简介

类似于Linux的文件系统，比文件系统要高级

2. MySQL 存储引擎类型

>>查看支持的存储引擎
mysql[(none)]>show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

>>查看默认存储引擎
--- 方法一：
mysql[(none)]>show variables like '%engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

--- 方法二：
mysql[(none)]>select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)
<InnoDB        (MySQL 5.5以后默认存储引擎)>
<MyISAM        (5.5版本以前的默认引擎)>

--- 方法三：
mysql[(none)]>select table_name ,engine from information_schema.tables where table_schema='world';
+-----------------+--------+
| table_name      | engine |
+-----------------+--------+
| city            | InnoDB |
| country         | InnoDB |
| countrylanguage | InnoDB |
| t1              | InnoDB |
+-----------------+--------+
4 rows in set (0.00 sec)

第三方：
TokuDB优势：

压缩比极高

插入性能很高
MyRocks
RocksDB

新新

修改表中的引擎
select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';

3. InnoDB与MyISAM

3.1 InnoDB存储引擎介绍

image.png

3.2 InnoDB与MyISAM的区别

1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safety Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、复制 Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )

索引 B+tree B*TREE

项目：x送

image.png

4. 如何查看存储引擎

查看支持的存储引擎命令(在文档首部)

>>查看某张表的存储引擎
mysql[world]>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: 2019-08-06 18:30:29
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

5. 修改存储引擎示例

mysql[wangxin]>create table t (id int) engine=myisam;
Query OK, 0 rows affected (0.34 sec)

mysql[wangxin]>show create table   t;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql[wangxin]>alter table t engine=innodb;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql[wangxin]>show create table   t;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

---小扩展: 将world库下所有表的引擎替换为innoDB---
select concat("alter table ",table_name," engine=innodb") 
from information_schema.tables where table_schema='world';

修改会话级别的默认引擎，只对当前会话有效

image.png

set global default_storage_engine=myisam;
全局级别的命令(仅影响新会话)<重启之后，所有参数均失效>

6. InnoDB存储引擎物理存储结构

6.1 InnoDB最直观的存储方式

存储文件	文件内容
city.frm	表的列定义
city.idb	表的数据和索引
ibdata1(5.7版本)	共享表空间文件(UNDO回滚数据(8.0独立)，系统数据字典)
ib_logfile0 ~ ib_logfileN	redo log 文件，默认大小50M
ibtmp1(5.7版本独立)	存放临时表
ib_buffer_pool	缓冲区池的映射文件

点击访问InnoDB存储结构官方文档

6.2 InnoDB 的表空空管理模式

city表 -----> 独立表空间 -----> 表空间数据文件：city.idb(IBD) -----> 段区页
共享表空间模式(5.5 默认)
ibdata1：目前遗留，用来存储系统数据。
独立表空间模式(5.6以后默认)
一个表一个IBD文件

6.3 共享表空间设置

查看共享表空间
select @@innodb_data_file_path;

一般是在安装MySQL初始化数据之前设置

mysql配置文件添加以下内容：
vim /etc/my.cnf
innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend

6.4 独立表空间设置

共享表空间设置：
select @@innodb_file_per_tablle;
共享表空间体验:
oldguo[(none)]>set global innodb_file_per_table=0;

6.5 独立表空间迁移

删除ibd文件
alter table t1 discard tablespace;
导入历史文件
alter table t2 import tablespace;
InnoDB表组成：ibdata1 + frm + ibd

真实案例，数据库无法启动，异常断电导致的数据丢失

案例背景一：

案例:  
环境: 贵州X交管系统,违章信息,MySQL5.7 , 70多张表.
备份策略: 每周六 mysqldump全备,每天binlog备份
问题描述: 
        在网上找了个参数调整ibtmp1大小,直接再接在生产中修改,由于手误,删除了ibdata1文件.
        数据库无法启动. 
解决思路: 
        1. mysqldump全备(周六)+日志(7,1,2,3,4)恢复
中间出的问题:
        1.全备有问题,经过调整,好用了.
        2. binlog日志只有2,3,4三天日志
换解决方案:  表空间迁移
        我们有什么? 
            1. 上周六的完整数据(完整表结构)
            2. 周四宕机时的ibd

演练:
        1. 搭建一个临时库
        [root@db01 /data/3306/data]# mysql -S /data/3307/mysql.sock
        2. 恢复上周六的备份到临时库
        oldguo[world]>source /root/world.sql
        3. 将测试库中ibd清掉
        [root@db01 /data/3306/data]# vim /data/3307/my.cnf 
        secure-file-priv=/tmp
        [root@db01 /data/3306/data]# systemctl restart mysqld3307.service 
        oldguo[(none)]>select concat("alter table world.",table_name," discard tablespace;") from information_schema.tables where table_schema='world' into outfile '/tmp/discard.sql';
        oldguo[(none)]>set foreign_key_checks=0;
        oldguo[(none)]>source /tmp/discard.sql
        4. 将故障库ibd文件,拷贝指定位置
        [root@db01 /data/3306/data/world]# cp *.ibd /data/3307/data/world/
        [root@db01 /data/3306/data/world]# chown -R mysql.mysql /data/
        5. 将ibd进行import 
        oldguo[(none)]>select concat("alter table world.",table_name," import tablespace;") from information_schema.tables where tablee_schema='world' into outfile '/tmp/import.sql';
        oldguo[world]>source /tmp/import.sql

案例背景二:

硬件及软件环境:
联想服务器（IBM） 
磁盘500G 没有raid
centos 6.8
mysql 5.6.33  innodb引擎  独立表空间
备份没有，日志也没开

开发用户专用库:
jira(bug追踪) 、 confluence(内部知识库)    ------>LNMT

故障描述:
断电了，启动完成后“/” 只读
fsck  重启,系统成功启动,mysql启动不了。
结果：confulence库在  ， jira库不见了

学员求助内容:
求助：
这种情况怎么恢复？
我问：
有备份没

求助：
连二进制日志都没有，没有备份，没有主从
我说：
没招了，jira需要硬盘恢复了。
求助：
1、jira问题拉倒中关村了

2、能不能暂时把confulence库先打开用着
将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的

问：有没有工具能直接读取ibd
我说：我查查，最后发现没有

我想出一个办法来：
表空间迁移:
create table xxx
alter table  confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;
虚拟机测试可行。

处理问题思路:
confulence库中一共有107张表。

1、创建107和和原来一模一样的表。
他有2016年的历史库，我让他去他同事电脑上 mysqldump备份confulence库
mysqldump -uroot -ppassw0rd -B  confulence --no-data >test.sql

拿到你的测试库，进行恢复
到这步为止，表结构有了。

2、表空间删除。
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql'

source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功。主外键关系
很绝望，一个表一个表分析表结构，很痛苦。

set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。

3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';

4、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）

7. InnoDB 核心特性--事务(Transaction)

7.0 简介

事务：保证在一个完整业务逻辑中，所有涉及到的语句，要么全成功，要么全失败。

7.1 ACID 特性

A：原子性(Atomic)
所有语句作为一个单元全部执行或全部取消。不能出现中间状态
C: 一致性(Consistent)
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态
I ：隔离性(Isolated)
事务之间不相互影响
D：持久性(Dyrable)
事务成功完成后，所做的所有更改都会准确的记录在数据库中。所做的更改不会丢失。

7.2 事务的生命周期管理(事务的控制语句)

7.2.1 开启事务

>>开启事务：
begin;
或者：
start transaction;

7.2.2 标准的事务语句(DML: insert update delete)

image.png

7.2.3 事务的结束

rollback       撤销事务，回滚
commit           提交(只要提交的数据无法回滚)

image.png

7.2.4 自动提交功能

select @@autocommit;
set autocommit=0;                    临时关闭自动提交
set global aotocommit=0              其他会话生效关闭自动提交

>>永久生效:
vim /etc/my.cnf
autocommit=0

7.2.5 隐式提交的的语句

begin
a
b
c
commitl                               操作下面的操作的时候会自动执行这条命令
>>用于隐式提交的 SQL 语句：
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

存储引擎+InnoDB核心特性(ACID)-事务介绍

1. 简介

2. MySQL 存储引擎类型

3. InnoDB与MyISAM

3.1 InnoDB存储引擎介绍

3.2 InnoDB与MyISAM的区别

4. 如何查看存储引擎

5. 修改存储引擎示例

6. InnoDB存储引擎物理存储结构

6.1 InnoDB最直观的存储方式

6.2 InnoDB 的表空空管理模式

6.3 共享表空间设置

6.4 独立表空间设置

6.5 独立表空间迁移

真实案例，数据库无法启动，异常断电导致的数据丢失

7. InnoDB 核心特性--事务(Transaction)

7.0 简介

7.1 ACID 特性

7.2 事务的生命周期管理(事务的控制语句)

7.2.1 开启事务

7.2.2 标准的事务语句(DML: insert update delete)

7.2.3 事务的结束

7.2.4 自动提交功能

7.2.5 隐式提交的的语句

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读