存储引擎+InnoDB核心特性(ACID)-事务介绍
2019-08-13 本文已影响0人
新_WX
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 | 缓冲区池的映射文件 |
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
