1、简介
相当于Linux文件系统,只不过比文件系统强大
2、功能了解
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持等
3、存储引擎种类(笔试)
3.1 介绍(Oracle MySQL)
InnoDB
MyISAM
MEMORY
ARCHIVE
FEDERATED
EXAMPLE
BLACKHOLE
MERGE
NDBCLUSTER
CSV
3.2 引擎种类查看
show engines;
存储引擎是作用在表上的,也就意味着,不同的表可以有不同的存储引擎类型。
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.
3.3 简历案例---zabbix监控系统架构整改
环境: zabbix 3.2 mariaDB 5.5 centos 7.3
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
1. zabbix 版本
2. 数据库版本
3. zabbix数据库500G,存在一个文件里
优化建议:
1.数据库版本升级到5.7版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....
优化结果:
监控状态良好
为什么?
1.原生态支持TokuDB,另外经过测试环境,5.7要比5.5 版本性能 高 2-3倍
2.TokuDB:insert数据比Innodb快的多,数据压缩比要Innodb高
3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
4.关闭binlog ----->减少无关日志的记录.
5.参数调整...----->安全性参数关闭,提高性能.
3.4 InnoDB和MyISAM存储引擎的替换
环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责:
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2.迁移所有表到新环境
3.开启双1安全参数
4、InnoDB存储引擎介绍
在MySQL5.5版本之后,默认的存储引擎,提供高可靠性和高性能。
4.1 优点
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 )
4.2 笔试题
1.请你列举MySQL InnoDB存储优点?
回答:
MVCC : 多版本并发控制
聚簇索引 : 用来组织存储数据和优化查询,IOT。
支持事务 : 数据安全保证
支持行级锁 : 控制并发
外键
多缓冲区支持
自适应Hash索引: AHI
复制中支持高级特性。
备份恢复: 支持热备。
自动故障恢复:CR Crash Recovery
双写机制:DWB Double Write Buffer
2.请你列举 InooDB和MyIsam的区别?
回答:
InnoDB 支持: 事务、MVCC、聚簇索引、外键、缓冲区、AHI、CR、DWB,MyISAM不支持。
InnoDB 支持: 行级锁,MyISAM支持表级锁.
InnoDB 支持热备(业务正常运行,影响低),MyISAM支持温备份(锁表备份)。
InnoDB 支持CR(自动故障恢复),宕机自动故障恢复,数据安全和一致性可以得到保证。MyISAM不支持,宕机可能丢失当前修改。
5. 存储引擎查看
5.1 使用 SELECT 确认会话存储引擎
查询默认存储引擎:SELECT @@default_storage_engine;
会话级别: set default_storage_engine=myisam;
全局级别(仅影响新会话): set global default_storage_engine=myisam;
重启之后,所有参数均失效.
如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.
5.2 SHOW 确认每个表的存储引擎
SHOW CREATE TABLE City\G;
SHOW TABLE STATUS LIKE 'CountryLanguage'\G
5.3 INFORMATION_SCHEMA 确认每个表的存储引擎
[world]>select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
Master [world]>show table status;
Master [world]>show create table city;
5.4 修改一个表的存储引擎
db01 [oldboy]>alter table t1 engine innodb;
注意:此命令我们经常使用他,进行innodb表的碎片整理
5.5 平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:
将数据逻辑导出,手工drop表,然后导入进去
现在:
1.对表进行按月进行分表(partition,中间件)或者归档表
2.业务替换为truncate方式
5.6 扩展:如何批量修改
项目:将所有的非InnoDB引擎的表查询出来,批量修改为InnoDB
mysql> select table_schema,table_name ,engine
from information_schema.tables
where
table_schema not in ('sys','mysql','information_schema','performance_schema')
and engine !='innodb';
----------------------------------------------------------
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema') and engine !='innodb' into outfile '/tmp/a.sql';
mysql> source /tmp/a.sql
5.7 面试题:2亿行的表,删除1千万行
1.如果2亿行的数据表,还没有生成,建议在设计表结构的时候,采用分区表的方式(按月range),然后使用truncate删除无用数据
2.如果2亿上的数据表,已经存在,建议使用pt-archive,工具进行归档表,并且删除无用数据。
6、InnoDB存储引擎物理存储结构
6.0 最直观的存储方式(/data/mysql/data)
ibdata1:系统数据字典信息(统计信息),UNDO表空间等数据
ib_logfile0 ~ ib_logfile1: REDO日志文件,事务日志文件。
ibtmp1: 临时表空间磁盘位置,存储临时表
frm:存储表的列信息
ibd:表的数据行和索引
6.1 表空间(Tablespace)
6.1.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
6.1.2 共享表空间设置
共享表空间设置(在搭建MySQL时,初始化数据之前设置到参数文件中)
扩容共享表空间
[(none)]>select @@innodb_data_file_path;
[(none)]>show variables like '%extend%';
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64
# 模拟在初始化时设置共享表空间(生产建议)
5.7 中建议:设置共享表空间2-3个,大小建议1G或者4G,最后一个定制为自动扩展。
8.0 中建议:设置1-2个就ok,大小建议1-4G
# 清理数据
[root@db01 data]# /etc/init.d/mysqld stop
[root@db01 data]# rm -rf /data/3306/data/*
[root@db01 data]# vim /etc/my.cnf
# 修改
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend
# 重新初始化
[root@db01 data]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 重启数据库生效
[root@db01 data]# /etc/init.d/mysqld start
6.1.3 独立表空间
从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操作产生临时数据,用完就自动
6.1.4 独立表空间设置问题
db01 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+
alter table city dicard tablespace;
alter table city import tablespace;
# 利用独立表空间进行快速数据迁移
源端:3306/test/t100w -----> 目标端:3307/test/t100w
1. 锁定源端t100w表
mysql> flush tables test.t100w with read lock ;
mysql> show create table test.t100w;
CREATE TABLE `t100w` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2. 目标端创建test库和t100w空表
mysql> create database test charset=utf8mb4;
CREATE TABLE `t100w` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3. 单独删除空的表空间文件
mysql> alter table test.t100w discard tablespace;
4. 拷贝源端ibd文件到目标端目录,并设置权限
[root@db01 test]# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/
[root@db01 test]# chown -R mysql.mysql /data/*
5. 导入表空间
mysql> alter table test.t100w import tablespace;
mysql> select count(*) from test.t100w;
+----------+
| count(*) |
+----------+
| 1000000 |
6. 解锁源端数据表
mysql> unlock tables;