MySQL存储引擎笔记day07

最新推荐文章于 2021-11-19 17:07:30 发布

追梦娃

最新推荐文章于 2021-11-19 17:07:30 发布

阅读量355

点赞数

本文链接：https://blog.csdn.net/weixin_44922815/article/details/103035448

版权

存储引擎

1.简介

类似Linux的文件系统，比文件系统要高级。

2.MySQL存储引擎类型(笔试：3-4种)

InnoDB 5.5以后默认的存储引擎。

select @@default_
+------------------------------+
| @@default_tmp_storage_engine |
+------------------------------+
| InnoDB |
+------------------------------+

查看MySQL内的存储引擎

show engines;

查看默认存储引擎
如果忘记了可以查询

show variables like "%default%";

select @@default_tmp_storage

MyISAM （5.1以前默认存储引擎）
CSV
MEMORY
BLACKHOLE**

第三方:工具
TokuDB
MyRocks
ROcksDB

TokuDB优势
1.压缩比高
2.插入性能很高

简历案例:zabbix监控系统架构整改

环境: zabbix 3.2 mariaDB 5.5 centos 7.3

现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.

问题 :

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

优化建议:

1.数据库版本升级到5.7版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1
5.参数调整....
优化结果:
监控状态良好

为什么?

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

3.InnoDB与MyISAM的区别
1、事务(�Transaction)
2、MVCC（Multi-Versos Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR(Auto Crash Safety Recovery) 自动的故障安全恢复
5、支持热备份（Host Backup）
6、Replication:Group Commint, GTID(Global Transaction ID ),多线程（Multi-Threads-SQL）
7.索引 B+tree B*TREE

热备
ACSR
MVCC

外键
复制
B+tree B*TREE

4. 小项目:某公司

环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMPT),数据量50G左右

现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据

职责

1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM

解决方案:

1.升级MySQL 5.6.10版本

迁移所有表到新环境
开启双1安全参数

image.png

备份:InnoDB支持热备MyISAM支持温备

MVCC

5.存储引擎查看简单修改

show engines;
show table status like 'city\G';
select @@default_storage_engine;
selectr table_name,engine from information_schema.tables where  
table_schema='world';
+-----------------+--------+
| table_name      | engine |
+-----------------+--------+
| city            | InnoDB |
| country         | InnoDB |
| countrylanguage | InnoDB |
+-----------------+--------+

创建一个引擎为Myisam表然后修改引擎为innodb

create table t (id int) engine=myisam;

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> alter table t engine=innodb;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

单独修改某一张表的存储引擎

alter table world.t1 engine=innodb;

替换world库下所有表的引擎为innodb

mysql> select concat("alter table" ,table_name,"engine=innodb") from information_schema.tables where table_schema='world';
+---------------------------------------------------+
| concat("alter table" ,table_name,"engine=innodb") |
+---------------------------------------------------+
| alter tablecityengine=innodb                      |
| alter tablecountryengine=innodb                   |
| alter tablecountrylanguageengine=innodb           |
| alter tabletengine=innodb                         |
+---------------------------------------------------+

alter table t engine=innodb;可以整理碎片

在线修改存储引擎重启后都会失效

会话级别

set default_storage_engine=myisam；

全局级别（影响新会话）

set global default_storage_engine=myisam；

如果想永久生效就写入配置文件中。

innodb frm ibd .frm 用于存储表的定义

> 存储结构:数据文件(.MYD),索引文件(.MYI)和结构文件(.frm)

特点:可以在不同服务器上拷贝数据文件和索引文件。

5.6 平常处理过的MySQL问题--碎片处理

环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式

5.6 扩展:如何批量修改

需求:将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';

6 InnoDB存储引擎物理结构

6.1 InnoDB 最直观的存储方式

city.frm 表的列定义
city.ibd 表的数据和索引
ibdatal5.7 共享表空间文件（undo回滚数据（8.0独立）,系统数据字典）
ib_logfile0 ~ ib_logfileN redo log文件
iptmp1(5.7) 存放临时表
ib_buffer_pool 缓冲区池的映射文件

6.2 InnoDB的表空间管理模式

city表----->独立表空间----->表空间数据文件:city.ibd-----ibd ----->段区页
共享表空间模式（5.5默认）
目前遗留下来了,用来存储系统数据。
独立表空间模式,(5.6以后默认)
一个表一个ibd文件
5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以后共享表空间保留，只用来存储:系统数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了
=============================================

6.3 共享表空间的设置

mysql> select @@innodb_data_file_path; ---一般是在初始化数据之前设置好
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

设置

vim /etc/my.cnf
innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend
show variables like '%extend%';

6.4独立表空间设置

select @@innodb_file_per_table;
共享表空间体验（不代表生产操作):
set global innodb_file_per_table=0; 1是打开0是关闭 #这个是全局。

6.5独立表空间迁移

XXX（无法恢复）alter table t1 discard tablespace;  清楚表空间
alter table t1  import    tablespace;  把之前的ipd文件拷贝回去原位置。所以需要再倒入一下。 把原文件权限修改为mysql

source用法

对于导入数据量比较小的数据我们可以直接使用mysql的图形界面进行导入，但是当数据量比较大时使用使用图形界面就相对于慢很多了，于是我们可以使用mysql数据库的一个命令source命令来进行数据的导入。

mysql>use dbtest;
mysql>set names utf8;
mysql>source D:/www/sql/back.sql;

InnoDB表:ibdatal + fra + ibd
DDL DCL DML

有一案例背景：

硬件及软件环境:

联想服务器（IBM）

磁盘500G 没有raid

centos6.8

mysql 5.6 innodb引擎独立表空间

备份没有,日志也没开。

开发用户专用库：
jira（bug追踪）、confluence（内部知识库） ----->LNMT

故障描述：

断电了，启动完成后“/”只读
fsck 重启,系统成功启动.mysql启动不了。
结果:confluence库在，jira库不见了。

问题：这种情况怎么恢复？

表空间迁移：

create table xxx
alter table confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;

虚拟机测试可行。

1、创建107和和原来一模一样的表。

他有2016年的历史库，我让他去他同事电脑上 mysqldump备份confluence库
mysqldump -uroot -ppassword -B confluence --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）

7.InnoDB核心特性--事务（Transaction）

7.0简介

事务:保证在一个在完整的业务逻辑中，所有涉及到的语句，要么全成功，要么全失败。

7.1 ACID特性

Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）
事务之间不相互影响。
Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

7.2 事务的生命周期管理

7.2.1 开启事务

begin;
或者
start transaction;

7.2.2 标准的事务语句（DML:insert update）

[world]>delete from city where id>1000;
[world]>delete from city where id<500;

7.2.3 事务的结束

rollbackup; 回滚

[world]>begin;
[world]>delete from city where id>1000;
[world]>delete from city where id<500;
[world]>rollback;

commit;如果命令对的话一执行会全部删除，如果错的话那就不会执行。

[world]>begin;
[world]>delete from city where id>1000
[world]>delete from city where id<500;
[world]>commit;

7.2.4自动提交

set  autocommit=0;  ##临时关  会话级别。
set global autocommit=0;##全局关
vim /etc/my.cnf  ##永久关闭
autocommit=0

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

7.2.5 隐式提交的语句

begin;
a
b
c
drop create alter grant ......

用于隐式提交的 SQL 语句：

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

追梦娃

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL存储引擎笔记day07

存储引擎1.简介类似Linux的文件系统，比文件系统要高级。2.MySQL存储引擎类型(笔试：3-4种)InnoDB 5.5以后默认的存储引擎。select @@default_+------------------------------+| @@default_tmp_storage_engine |+------------------------------...
复制链接

扫一扫