命令记不住

登陆

存储引擎

  |   129 浏览

存储引擎

1.存储引擎简介

1、简介

相当于Linux文件系统，只不过比文件系统强大

2、功能：

数据读写
数据安全
提高性能
热备份
自动故障恢复
高可用方面支持

3、存储引擎种类

InnoDB
MyISAM
MEMORY
BLACKHOLE
NDBCLUSTER
ARCHIVE
FEDERATED
EXAMPLE
MERGE
CSV

3.1MySQL支持的存储引擎种类查看：

show engines;

存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。

perconaDB:XtraDB
mariaDB:
TokuDB ------>zabbix

扩展:

TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
Myrocks

2.zabbix服务数据库优化案例

环境: zabbix 3.2 mariaDB 5.5 centos 7.3

1现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix
问题 :

    1. zabbix 版本
    1. 数据库版本
    1. zabbix数据库500G,存在一个文件里

2优化建议:

  • 1.版本升级到5.7版本
  • 2.存储引擎改为tokudb
  • 3.监控数据按月份进行切割
  • 4.关闭binlog
  • 5.参数调整....

3为什么?

    1. 原生态支持tokudb,另外经过测试环境,5.7要比5.5 版本性能 高 2-3倍
    1. TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
  • 3.监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
  • 4.关闭binlog ----->减少无关日志的记录
  • 5.参数调整...----->安全性参数关闭,提高性能

3.InnoDB存储引擎介绍

在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
优点：

事务安全（遵从 ACID）
MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
	InnoDB 行级别锁定
	Oracle 样式一致非锁定读取
表数据进行整理来优化基于主键的查询
支持外键引用完整性约束
大型数据卷上的最大性能
将对表的查询与不同存储引擎混合
出现故障后快速自动恢复
用于在内存中缓存数据和索引的缓冲区池

请你列举MySQL InnoDB存储优点？
请你列举 InooDB和MyIsam的区别？

InnoDB：

  • 1、事务（Transaction）
  • 2、MVCC（Multi-Version Concurrency Control多版本并发控制）
  • 3、行级锁(Row-level Lock)
  • 4、ACSR（Auto Crash Safe Recovery）自动的故障安全恢复
  • 5、支持热备份(Hot backup)
  • 6、Replication: Group Commit , GTID (Global Transaction ID) ,多线程(SQL Multi-Threads)

4.存储引擎查看

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.

2.使用 SHOW 确认每个表的存储引擎：

SHOW CREATE TABLE City\G;
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

3.使用 INFORMATION_SCHEMA 确认每个表的存储引擎：

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'city' AND TABLE_SCHEMA = 'world'\G

SELECT TABLE_NAME, ENGINE FROM  INFORMATION_SCHEMA.TABLES WHERE  TABLE_SCHEMA = 'oss';

4.如何修改一个表的存储引擎

db01 [oldboy]>alter table t1 engine innodb;

5.扩展:如何批量修改
需求:将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表的随便,避开业务繁忙期.

alter table t1 engine innodb;

6.怎么判断有大量的碎片?
提示:表真实的空间占用和磁盘的ibd文件比较

(1) 真正占用空间大小

SELECT table_schema,SUM(AVG_ROW_LENGTH * TABLE_ROWS+ INDEX_LENGTH)/1024 AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;

(2) 磁盘方面查看IBD文件占用的磁盘空间

 du -sh
 ll -h

5.Innodb存储引擎物理存储结构

1.最直观的存储方式(/data/mysql)

表的列的信息 :frm 
数据行和索引 :ibd
元数据:"基表" ibdata1 
日志:ib_logfile0 
临时表:ibtmp1

2.表空间(Tablespace)
2.1共享表空间

需要将所有数据存储到同一个表空间中	，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以后，共享表空间保留，只用来存储，系统表相关数据,undo,临时表。

2.2共享表空间设置

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)

innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

2.3独立表空间

从5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行以及索引信息

基本表结构元数据存储：

xxx.frm

最终结论：

			       元数据        数据行+索引
mysql表数据     =（ibdataX+frm）+ibd(段、区、页)
			        DDL           DML+DQL

2.4MySQL的存储引擎日志：

Redo Log: ib_logfile0  ib_logfile1，重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
临时表:ibtmp1，在做join union操作产生临时数据，用完就自动

2.5独立表空间设置问题

db01 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+


alter table city dicard tablespace;
alter table city import tablespace;

6.数据库断电丢失数据恢复案例

开发用户

jira(bug追踪) 、 confluence(内部知识库)    ------>LNMT 

-------------------
联想服务器（IBM）   
磁盘500G 没有raid
centos 6.8
mysql 5.6.33  innodb引擎  独立表空间

备份没有，日志也没开

------------------

编译→制作rpm

/usr/bin/mysql

/var/lib/mysql

confulence   jira

所有软件和数据都在"/"

------------
断电了，启动完成后“/” 只读

fsck   重启
启动mysql启动不了。
结果：confulence库在  ， jira库不见了


我想出一个办法来：
create table xxx
alter table  confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;


虚拟机测试可行。

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`),
  KEY `idx_popu` (`Population`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

面临的问题，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、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态

5.故障案例模拟
获得原数据库表的结构（创建语句）
1.通过历史备份
2.和开发一起来完成

模拟——我们直接 show create table t1;
CREATE TABLE `t1` (
  `stuid` int(11) NOT NULL,
  `stuname` varchar(20) NOT NULL,
  `stusex` char(1) NOT NULL,
  `cardid` varchar(20) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `entertime` datetime DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`stuid`),
  KEY `idx_stuname` (`stuname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.搭建一个1:1的测试数据库
mysql -S /data/3307/mysql.sock

4.在测试库中，恢复表结构
mysql -S /data/3307/mysql.sock
create database oldboy;
CREATE TABLE `t1` (
  `stuid` int(11) NOT NULL,
  `stuname` varchar(20) NOT NULL,
  `stusex` char(1) NOT NULL,
  `cardid` varchar(20) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `entertime` datetime DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`stuid`),
  KEY `idx_stuname` (`stuname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.删除空的ibd文件
alter table t1  discard tablespace;

6.使用源库的ibd导入
cp /usr/local/mysql/mydata/oldboy/t1.ibd  /data/3307/data/oldboy/
chown -R mysql.mysql /data/*
alter table t1 import  tablespace;

文件句柄

rm -rf 
inode  位图索引 标记改为可覆盖状态
推荐阅读