目录
MySQL储存引擎
什么是储存引擎
MySQL引擎:
可以理解为,MySQL的“文件系统”,只不过功能更加强大。
存储引擎的类型
MySQL 提供以下存储引擎:
- 01)InnoDB
- 02)MyISAM
- 03)MEMORY HASH索引
- 04)ARCHIVE
- 05)FEDERATED
- 06)EXAMPLE
- 07)BLACKHOLE
- 08)MERGE
- 09)NDBCLUSTER
- 10)CSV
查看表的存储引擎(查全部的表)
root@localhost [(none)] >show create table world.city;
root@localhost [(none)] >select table_schema,table_name,engine from information_schema.tables where engine='innodb';
root@localhost [(none)] >select table_schema,table_name,engine from information_schema.tables where engine='myisam';
+--------------+------------------+--------+
| table_schema | table_name | engine |
+--------------+------------------+--------+
| mysql | columns_priv | MyISAM |
| mysql | db | MyISAM |
| mysql | event | MyISAM |
| mysql | func | MyISAM |
| mysql | ndb_binlog_index | MyISAM |
| mysql | proc | MyISAM |
| mysql | procs_priv | MyISAM |
| mysql | proxies_priv | MyISAM |
| mysql | tables_priv | MyISAM |
| mysql | user | MyISAM |
+--------------+------------------+--------+
innodb和myisam的区别
物理区别
[root@db04 ~]# ll /app/mysql/data/mysql/user.*
/app/mysql/data/mysql/user.frm
/app/mysql/data/mysql/user.MYD
/app/mysql/data/mysql/user.MYI
[root@db04 ~]# ll /app/mysql/data/world/city.*
/app/mysql/data/world/city.frm // 表结构
/app/mysql/data/world/city.ibd // 表空间(独立表空间)
format:格式,结构
.frm都是表结构信息
# 表结构
root@localhost [zls] >desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | MUL | 0 | |
+-------------+----------+------+-----+---------+----------------+
逻辑区别
innodb核心特性
重点:
- MVCC 多版本并发控制
- 独立表空间
- 事务
- 行级锁
- 热备份
- Crash Safe Recovery(自动故障恢复)
- ETREE
存储引擎如何查看
# 查看当前目录的存储引擎
SELECT @@default_storage_engine;
root@localhost [world] >show table status like 'city';
存储引擎设置
1)在启动配置文件中设置服务器存储引擎
#在配置文件的[mysqld]标签下添加
[mysqld]
default-storage-engine=<Storage Engine>
2)使用 SET 命令为当前客户机会话设置
#在MySQL命令行中临时设置
SET @@storage_engine=<Storage Engine>
3)建表指定存储引擎
root@localhost [world] >create table zls.t2_myisam(id int)engine myisam;
修改已经创建的表的存储引擎
## 修改单个表的
root@localhost [zls] >alter table t1_myisam engine=innodb;
## 修改库下所有的
1.把库用mysqldump导出数据库
2.通过vim修改,把ENGINE=MyISAM改为ENGINE=InnoDB
3.在导回去mysql -uroot -p123 < xx.sql
企业项目案例
项目背景:
公司原有的架构:一个展示型的网站,LAMT,MySQL5.1.77版本(MYISAM),50M数据量。
小问题不断:
1、表级锁:对表中任意一行数据修改类操作时,整个表都会锁定,对其他行的操作都不能同时进行。
2、不支持故障自动恢复(CSR):当断电时有可能会出现数据损坏或丢失的问题。 如何解决:
1、提建议将现有的MYISAM引擎替换为Innodb,将版本替换为5.6.38
1)如果使用MYISAM会产生”小问题”,性能安全不能得到保证,使用innodb可以解决这个问题。
2)5.1.77版本对于innodb引擎支持不够完善,5.6.38版本对innodb支持非常完善了。
2、实施过程和注意要素
InnoDB特性——表空间
共享表空间
5.5版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展
共享表空间存储:
5.7版本中默认会将undo和临时表独立出来,5.6版本也可以独立,只不过需要在初始化的时候进行配置
1)undo事务日志
2)系统数据
3)临时表
5.7共享表,临时表
-rw-r----- 1 mysql mysql 12582912 Jul 30 14:42 ibtmp1
共享表空间切割
mysql> show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name | Value |
+----------------------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+----------------------------------+------------------------+
# autoextend 自动扩容
## 文件配置(4864*16/1024=76)
[root@db01 data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend
## 切割完成后会出来一个新的,
-rw-r----- 1 mysql mysql 79691776 Jul 30 15:16 ibdata1
-rw-r----- 1 mysql mysql 52428800 Jul 28 17:19 ibdata2
## 查看
root@localhost [(none)] >show variables like '%path%';
+----------------------------------+------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------+
| innodb_data_file_path | ibdata1:76M;ibdata2:50M:autoextend |
+----------------------------------+------------------------------------+
企业案例
独立表空间
在没有备份数据的情况下,突然断电导致表损坏,打不开数据库。
故障复现
# 在数据库内导出数据
[root@db04 tmp]# mysqldump -uroot -p123 -B world > /tmp/city.sql
# 使用scp传送到03机器上
[root@db04 tmp]# scp city.sql 10.0.0.53:/root
# 03机器导入数据库
[root@db03 ~]# mysql -uroot -p123 < city.sql
# 表结构损坏
# 查看表
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
# 查看表内容(无法查看)
mysql> select * from fity;
ERROR 1146 (42S02): Table 'world.fity' doesn't exist
恢复步骤(思路)
# 1.准备环境
cd /app/mysql/scripts
./mysql_install_db --basedir=/app/mysql --datadir=/app/mysql/data
# 2.建库
mysql> create database world;
# 3.管开发要建表语句
CREATE TABLE `city_new` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
# 4.使用语句创建表
# 5.删除新表的表空间
mysql> alter table city_new discard tablespace;
# 6.将旧表的表空间拷贝到新表下
cd /app/mysql/data/world
rm -f city_new.ibd
[root@db03 world]# cp -a city.ibd city_new.ibd
# 7.导入新表的表空间
mysql> alter table city_new import tablespace;
# 8.删除旧表
[root@db02 world]# rm -f city.frm city.ibd
或--------------
use world;
drop table city;
# 9.修改表名
mysql> alter table city_new rename city;
# 9.9挂维护页
# 10.应用割接
- 开发改代码,修改连接数据库的IP地址
- 将新环境做一个全备,mysqldump -B world > /tmp/wd.sql
# 11.binlog截取新增数据,恢复到旧环境
# 12.取消维护页
InnoDB核心特性——事务
事务 伴随着交易
事务主要针对DML语句(insert、update、delete)
事务特性:ACID
A:原子性:将一个事务视为一个单元,要么全部成功,有一条失败,就全部回滚
C:一致性:事务执行之前和事务执行之后,状态保持一致
I:隔离性:事务与事务之间互相隔离 (隔离级别,锁)
D:持久性:当事务被提交后,永久写入磁盘
事务的生命周期
# 成功的事务:
begin;
DML
DML
DML
commit;
# 失败的事务
begin;
DML
DML
DML
rollback;
事务控制语句
begin; start transaction; // 开启一个事务
savepoint; // 将事务保存在某一状态
mysql> savepoint zls_4000;
rollback to savepoint; // 回到指定的位置点
mysql> rollback to savepoint zls_4000;
release savepoint; // 删除位置点
mysql> release savepoint zls_4000;
commit; // 提交事务
rollback; // 回滚事务
## 设置
SET AUTOCOMMIT:为当前连接禁用或启用默认 autocommit 模式
vim /etc/my.cnf
[msyqld]
autocommit=0
MySQL在默认情况下,只要执行一个DML语句,就会自动开启一个事务
自动提交
#查看自动提交
mysql> show variables like 'autocommit';
#临时关闭
mysql> set autocommit=0;
#永久关闭
[root@db01 world]# vim /etc/my.cnf
[mysqld]
autocommit=0
事务的隐式提交
1)在事务执行期间,如果执行了begin,开启下一个事务,自动提交上一个事务。
2)在事务执行期间,如果执行了DDL、DCL也会自动提交事务
3)在事务执行期间,如果执行锁表语句 lock,unlock也会自动提交上一个事务
4)load data in file,做备份
5)select for update 自动提交事务
6)在autocommit开启的时候,自动提交事务
事务日志(CSR 自动故障恢复)
redo
redo,"重做日志",是事务日志的一种
特性:WAL(Write Ahead Log)日志优先写
REDO:记录的是,内存数据页的变化过程
redo工作过程
#执行步骤 update t1 set num=2 where num=1;
1)首先将t1表中num=1的行所在数据页加载到内存中buffer page
2)MySQL实例在内存中将num=1的数据页改成num=2
3)num=1变成num=2的变化过程会记录到,redo内存区域,也就是redo buffer page中
#提交事务执行步骤 commit;
1)当敲下commit命令的瞬间,MySQL会将redo buffer page写入磁盘区域redo log
2)当写入成功之后,commit返回ok
undo
1)undo是什么?
undo,顾名思义“回滚日志”,是事务日志的一种。
Undo Log的原理:为了满足事务的原子性,在操作任何数据之前,首先将数据备份到一个地方(这个存储数据备份的地方称为Undo Log)。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句,系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。除了可以保证事务的原子性,Undo Log也可以用来辅助完成事务的持久化。
undo工作过程
A.事务开始.
B.记录zls=200到undo log.
C.修改zls=100
D.将undo log写到磁盘。
E.将数据写到磁盘。
F.事务提交
redo和undo的储存位置
#redo位置
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 50331648 Aug 15 06:34 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Mar 6 2017 ib_logfile1
#undo位置
[root@db01 data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata1
-rw-rw---- 1 mysql mysql 79691776 Aug 15 06:34 ibdata2
### 在MySQL5.6版本中undo是在ibdata文件中,在MySQL5.7版本会独立出来。
事务中的锁
锁的粒度
-
MyIsam:低并发锁(表级锁)
-
Innodb:高并发锁(行级锁)
行级锁要以主键(索引)为搜索,要不然不识别行
多版本并发控制(MBVCC)
共享锁:在一个事务执行期间,不阻塞查询操作
排他锁:在一个事务执行期间,阻塞其他修改操作
乐观锁:谁先提交,以谁为准
悲观锁:以事务开启为准,谁先执行修改操作,其他人查询会被阻塞
事务的隔离级别
- RC:read commit 提交读
- RU:read uncommit 未提交读
- RR:repeatable read 可重复读
- SERTALIZABLE: 串行化级别
-
READ UNCOMMITTED(独立提交) 允许事务查看其他事务所进行的未提交更改
-
READ COMMITTED 允许事务查看其他事务所进行的已提交更改
-
REPEATABLE READ 确保每个事务的 SELECT 输出一致
InnoDB 的默认级别
-
SERIALIZABLE 将一个事务的结果与其他事务完全隔离
#查看隔离级别
mysql> show variables like '%iso%';
#修改隔离级别为RU
[mysqld]
transaction_isolation=read-uncommit
mysql> use oldboy
mysql> select * from stu;
mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123);
#修改隔离级别为RC
[mysqld]
transaction_isolation=read-commit
标签:city,储存,data,MySQL,事务,引擎,mysql,world,root
From: https://www.cnblogs.com/xiutai/p/17749306.html