简介
类似于 Linux 系统中的文件系统, 比文件系统高级
功能
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面
存储引擎种类
介绍 (Oracle MySQL)
- InnoDB (5.5 以后默认的存储引擎)
- MyISAM (5.5 以前的默认存储引擎)
- MEMORY
- CSV
- BLACKHOLE
说明: 存储引擎是作用在表上的, 也就是意味着, 不同的表可以有不同的存储引擎类型.
其他 MySQL 的引擎
PerconaDB: 默认是 XtarDB
Mariadb: 默认是 InnoDB
其他的存储引擎支持:
- TokuDB
- RocksDB
- MyRocks
以上三种存储引擎的共同点: 压缩比较高, 数据插入性能极高
现在很多的 NewSQL, 使用比较多的功能特性
InnoDB 与 MyISAM 的区别
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 )
7,INnoDB 支持外键
相关项目
项目一
项目:
环境: zabbix 3.2 , mariaDB 5.5 , CentOS 7.3
现象 : zabbix 卡的要死 , 每隔 3-4 个月, 都要重新搭建一遍 zabbix, 存储空间经常爆满.
问题 :
1. zabbix 版本
2. 数据库版本
3. zabbix 数据库 500G, 存在一个文件里
优化建议:
* 1. 数据库版本升级到 mariaDB 10.x 版本, zabbix 升级更高版本
* 2. 存储引擎改为 tokudb
3. 监控数据按月份进行切割 (二次开发: zabbix 数据保留机制功能重写, 数据库分表)
* 4. 关闭 binlog 和双 1
* 5. 参数调整....
优化结果:
监控状态良好
为什么?
1. mariaDB 10.x 原生态支持 TokuDB, 另版本性能 高 2-3 倍, 表空间管理更加合理
2. TokuDB:insert 数据比 Innodb 快的多, 数据压缩比要 Innodb 高
3. 监控数据按月份进行切割, 为了能够 truncate 每个分区表, 立即释放空间
4. 关闭 binlog -----> 减少无关日志的记录.
5. 参数调整...-----> 安全性参数关闭, 提高性能.
项目二
环境: CentOS 5.8 ,MySQL 5.0 版本, MyISAM 存储引擎, 网站业务 (LNMP), 数据量 50G 左右
现象问题: 业务压力大的时候, 非常卡; 经历过宕机, 会有部分数据丢失.
问题分析:
1.MyISAM 存储引擎表级锁, 在高并发时, 会有很高锁等待
2.MyISAM 存储引擎不支持事务, 在断电时, 会有可能丢失数据
职责
1. 监控锁的情况: 有很多的表锁等待
2. 存储引擎查看: 所有表默认是 MyISAM
解决方案:
1. 升级 MySQL 5.6.10 版本
2. 迁移所有表到新环境
3. 开启双 1 安全参数
存储引擎操作
-- 查看所有存储引擎
show engines;
-- 查看默认的存储引擎
- select @@default_storage_engine;
- show variables like '%engine%';
-- 查看表的存储引擎
- show create table city\G
- SHOW TABLE STATUS LIKE 'CountryLanguage'\G
- show table status\G
-- INFORMATION_SCHEMA 确认每个表的存储引擎
- 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 表碎片, 会锁表
-- 修改存储引擎 (不代表生产操作)
会话级别:
set default_storage_engine=myisam;
全局级别 (仅影响新会话):
set global default_storage_engine=myisam;
重启之后, 所有参数均失效.
写入配置文件 (永久生效)
- VIM /etc/my.cnf
- [mysqld]
- default_storage_engine=myisam
存储引擎是表级别的, 每个表创建时可以指定不同的存储引擎, 但是我们建议统一为 innodb.
平常处理过的 MySQL 问题 -- 碎片处理
平常处理过的 MySQL 问题 -- 碎片处理
环境: centos7.4,MySQL 5.7.20,InnoDB 存储引擎
业务特点: 数据量级较大, 经常需要按月删除历史数据.
问题: 磁盘空间占用很大, 不释放
处理方法:
以前: 将数据逻辑导出, 手工 drop 表, 然后导入进去
现在:
对表进行按月进行分表 (partition, 中间件)
业务替换为 truncate 方式
批量修改存储引擎
需求: 将 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 最直观的存储方式
文件 | 说明 |
---|---|
xx.frm | 表的列定义 |
xx.ibd | 表的数据和索引 |
ibdata1 | 共享表空间文件 (UNDO 回滚数据, 系统数据字典,) -- MySQL5.7 版本 |
ib_logfile0 ~ ib_logfileN | redo log 文件 |
ibtmp1 | 存放临时表 |
ib_buffer_pool | 缓冲区池的映射文件 |
InnoDB 的表空间管理模式
共享表空间模式 (5.5 默认)
ibdata1: 目前遗留下来了, 用来存储系统数据
需要将所有数据存储到同一个表空间中 , 管理比较混乱
5.5 版本出现的管理模式, 也是默认的管理模式.
5.6 版本以, 共享表空间保留, 只用来存储: 数据字典信息, undo, 临时表.
5.7 版本, 临时表被独立出来了
8.0 版本, undo 也被独立出去了
独立表空间模式 (5.6 以后默认)
一个表一个 ibd 文件, 存储数据行和索引信息
共享表空间的设置
一般在初始化之前设定
- VIM /etc/my.cnf
- innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
- innodb_autoextend_increment=64
-- 默认表空间大小
- MariaDB [(none)]> select @@innodb_data_file_path;
- +-------------------------+
- | @@innodb_data_file_path |
- +-------------------------+
- | ibdata1:12M:autoextend |
- +-------------------------+
- 1 row in set (0.000 sec)
-- 查看自增长大小
- MariaDB [(none)]> show variables like '%extend%';
- +-----------------------------+-------+
- | Variable_name | Value |
- +-----------------------------+-------+
- | innodb_autoextend_increment | 64 |
- +-----------------------------+-------+
独立表空间设置
- MariaDB [(none)]> select @@innodb_file_per_table;
- +-------------------------+
- | @@innodb_file_per_table |
- +-------------------------+
- | 1 |
- +-------------------------+
-- 1 代表打开了独立表空间
-- 0 代表独立表空间处于关闭状态
独立表空间迁移
InnoDB 表: ibdata1 + frm + ibd
- alter table t1 discard tablespace;
- alter table t1 import tablespace;
企业案例
案例背景
硬件及软件环境:
联想服务器 (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)
InnoDB 核心特性 -- 事务 (Transaction)
简介
事务: 保证在一个完整业务逻辑中, 所有涉及到的语句, 要么全成功, 要么全失败
事务的 ACID 特性
#Atomic(原子性)
所有语句作为一个单元全部成功执行或全部取消. 不能出现中间状态.
#Consistent(一致性)
如果数据库在事务开始时处于一致状态, 则在执行该事务期间将保留一致状态.
#Isolated(隔离性)
事务之间不相互影响.
#Durable(持久性)
事务成功完成后, 所做的所有更改都会准确地记录在数据库中. 所做的更改不会丢失.
事务的生命周期管理 (事务控制语句)
开启事务
begin;
或者
start transaction;
标准的事务语句 (DML: insert update delete)
- delete from city where id>1000;
- delete from city where id<500;
事务的结束
commit; -- 提交事务
rollback; -- 回滚事务
自动提交功能
```MySQL
-- 查看自动提交策略
select @@autocommit;
1 为开启自动提交, 0 为禁止自动提交.(默认为 1)
-- 修改自动提交策略 (临时)
set autocommit=0;
-- 修改自动提交策略 (全局, 重启失效)
set global autocommit=0;
-- 修改自动提交策略 (永久)
- VIM /etc/my.cnf
- autocommit=0
隐式提交语句
有些语句在没有使用 commit 的时候, 事务也会提交, 这种情况称为隐式提交
用于隐式提交的 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
自动提交功能应用 举例说明
- MySQL[(none)]>use world;
- MySQL[world]>show tables;
- +-----------------+
- | Tables_in_world |
- +-----------------+
- | city |
- | country |
- | countrylanguage |
- +-----------------+
-- 统计 city 表有多少行数据
- MySQL[world]>select count(*) from city;
- +----------+
- | count(*) |
- +----------+
- | 4079 |
- +----------+
- 1 row in set (0.00 sec)
-- 删除 ID 大于 3000 的数据行
MySQL[world]>delete from city where id>3000;
-- 真实删除
- MySQL[world]>select count(*) from city;
- +----------+
- | count(*) |
- +----------+
- | 3000 |
- +----------+
-- 开启事务模式
- MySQL[world]>begin;
- MySQL[world]>delete from city where id>2000;
-- 回滚事务
MySQL[world]>rollback;
-- 未执行删除命令
- MySQL[world]>select count(*) from city;
- +----------+
- | count(*) |
- +----------+
- | 3000 |
- +----------+
- 1 row in set (0.00 sec)
-- 关闭自动提交策略, 回滚事务
- MySQL[world]>set @@autocommit=0;
- MySQL[world]>delete from city where id>2000;
- MySQL[world]>rollback;
- MySQL[world]>select count(*) from city;
- +----------+
- | count(*) |
- +----------+
- | 3000 |
- +----------+
-- 关闭自动提交策略, 提交事务
- MySQL[world]>delete from city where id>2000;
- MySQL[world]>commit;
- MySQL[world]>select count(*) from city;
- +----------+
- | count(*) |
- +----------+
- | 2000 |
- +----------+
Innodb 事务的 ACID 如何保证?
一些概念
redo log -- 重做日志, 记录数据页的变化.
redo log buffer -- redo log 的缓冲区 (内存)
ibd -- 表空间的数据文件, 以段区页方式规划存储数据行和索引
buffer pool -- 数据页缓冲区
LSN -- log seq no; 日志序列号, redolog/log buffer/ibd/buffer pool
WAL -- write ahead log; 日志优先写磁盘
脏页 -- dirty page; 在内存中被修改的数据页
CKPT -- Checkpoint(检查点), 将内存脏页回写到磁盘的动作
TXID -- transaction id; 事务 ID, 伴随着事务的整个生命周期的
undo log -- 回滚日志, ibdata1
redo log
redo 简介
重做日志, ib_logfile0,ib_logfile1
记录的内容
记录数据页的变化
作用
主要保证 ACID 中 的'D' , 持久化功能, 对 AC 也有相应的保证.
加快了 commit 命令的速度, 提高了事务的并发.
并且实现了, 在 MySQL Crash 时, ACSR 中前滚的功能.
undo 功能
undo 简介
回滚日志, 5.7 存储在 ibdata1 , 8.0 undo 单独存储.
undo 记录的内容
记录 逆操作
作用
主要保证了 A 的特性, CI 也有相应的功能
隔离级别和锁机制
功能
主要提供了 ACID 当中的 I 隔离性的功能, C 的功能受到一定影响
隔离级别介绍
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
RU 读未提交 | 是 | 是 | 是 |
RC 读已提交 | 否 | 是 | 是 |
RR 可重复读(默认) | 否 | 否 | 是 |
SR 可串行化 | 否 | 否 | 否 |
RU : 读未提交. 会出现脏读
RC : 读已提交. 防止脏读, 会出现不可重复读, 可能出现幻读
RR : 可重复读. 防止脏读 - 防止不可重复 ( MVCC , 一致性快照读)- 可能会出现幻读 (GAP next-lock)
SR : 可串行化
隔离级别查看与设定
-- 查看隔离级别
- select @@transaction_isolation;
- select @@tx_isolation;
-- 修改隔离级别 (永久)
- VIM /etc/my.cnf
- # 以下是常用的俩种 (RR,RC)
- transaction_isolation=REPEATABLE-READ
- transaction_isolation=READ-COMMITTED
MySQL 的锁 (主要解决 I)
record lock -- 记录锁, 行级锁
gap -- 间隙锁
next-lock -- 下一键锁
锁表
lock tables city read;
解锁
unlock tables;
InnoDB 核心参数介绍
默认存储引擎
select @@default_storage_engine;
Percona:XtraDB 默认
MariaDB:InnoDB 默认 myrocks tokudb
缓冲区池 innodb_buffer_pool_size
查看
- select @@innodb_buffer_pool_size;
- show engine innodb status\G
修改
- VIM /etc/my.cnf
- innodb_buffer_pool_size=2G
-- 官方建议: 最多 95%
-- 生产建议: 不超过 80%,50%-70%
事务日志: innodb_log_buffer_size
select @@innodb_log_buffer_size;
redo 的参数
- MySQL[(none)]>SHOW VARIABLES LIKE '%INNODB_LOG_FILE%';
- +---------------------------+----------+
- | Variable_name | Value |
- +---------------------------+----------+
| innodb_log_file_size | 50331648 | --- 控制大小
| innodb_log_files_in_group | 2 | --- 控制个数
+---------------------------+----------+
relog 的刷写策略 ("双一")
select @@innodb_flush_log_at_trx_commit;
-- 5.7 版本模式为 1
1, 在事务提交时, 立即刷写 redo buffer 中日志到 OS buffer, 立即 FSYNC 到磁盘
0, 每秒刷写 redo buffer 中日志到 OS buffer, 每秒 FSYNC 到磁盘
2, 在事务提交时, 立即刷写 redo buffer 中日志到 OS buffer, 每秒 FSYNC 到磁盘
innodb_flush_method
作用: 控制 buffer pool 和 redo buffer
select @@innodb_flush_method;
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=fsync
来源: http://www.bubuko.com/infodetail-3343054.html