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;