存储引擎
2020-11-18 本文已影响0人
挑战_bae7
存储引擎
1.查询支持的存储引擎
3306 [(none)]>show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
例如：
select table_schema,table_name,engine from information_schema.tables where engine='CSV';
查询引擎csv的表名
PerconaDB:默认是XtraDB
MariaDB:默认是InnoDB
其他的存储引擎支持:
TokuDB
RocksDB
MyRocks
以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
现在很多的NewSQL,使用比较多的功能特性.
例如
环境: 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.参数调整...----->安全性参数关闭,提高性能.
环境: centos 5.8 ,MySQL 5.0版本,MyISAM存储引擎,网站业务(LNMP),数据量50G左右
现象问题: 业务压力大的时候,非常卡;经历过宕机,会有部分数据丢失.
问题分析:
1.MyISAM存储引擎表级锁,在高并发时,会有很高锁等待
2.MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据
职责
1.监控锁的情况:有很多的表锁等待
2.存储引擎查看:所有表默认是MyISAM
解决方案:
1.升级MySQL 5.6.10版本
2. 迁移所有表到新环境
3. 开启双1安全参数
innodb myisam 区别
innodb 支持 事务 myisam不支持
锁的粒度 是行锁 myisam 是表锁
innodb 支持热备
innodb CSR（自动故障恢复）
innodb MVCC(多版本并发控制)
Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )
mysql 5.5 以后 默认引擎是innodb
MariaDB [(none)]> select @@default_storage_engine; 查询默认的数据库引擎
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
set default_storage_engine=MYISAM; 临时修改 只在当前会话有效 关闭会话失效
set global default_storage_engine=MYISAM; 新开会话生效 重启服务失效
查看数据库中的引擎 有两种:
show table status from world; 只可以查询一个库
select table_schema,table_name,engine from information_schema.tables where table_schema='world';
select table_schema,table_name,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
查询除了 系统库外的所有数据库文件
alter table school.sc engine=innodb; 将myisam转成innodb命令
select concat('alter table ',table_schema,'.',table_name,' engine=innodb') from information_schema.tables where table_schema='world';
整合命令 这种会锁表 对数据类型数据不影响
这个命令可以整理删除 修改 产生的碎片整理
alter table school.sc engine=innodb;
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式
2.物理存储结构
ibdata1 系统数据字典信息(统计信息)，UNDO表空间等数据
ib_logfile0
ib_logfile1 redlog 文件 （两个文件） 事务日志
ibtmp1： 临时表空间磁盘位置，存储临时表
city.frm 列信息 数据类型
city.ibd 行信息 索引
共享表空间
需要将所有数据存储到同一个表空间中 ，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了
共享表空间这样导致ibdata1 文件会越来越大 通过下面改进
MariaDB [(none)]> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:10M:autoextend | 默认10M 自动扩展
+-------------------------+
MariaDB [(none)]> show variables like '%extend%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 8 | 默认以8M增长扩展
+-----------------------------+-------+
共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend （ibdata1 512M ibdata2 512M可以自动扩展）
innodb_autoextend_increment=64
独立表空间
从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操作产生临时数据，用完就自动删除
参数控制：
3306 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+
set global innodb_file_per_table=0; 设置共享表空间
alter table city dicard tablespace; 删除ibd文件 表空间文件 数据 索引 文件类型之类的
alter table city import tablespace; 导入表空间文件
案例
confulence库中一共有107张表。 mysql版本要一样
1、创建107和和原来一模一样的表。
他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库
mysqldump -uroot -ppasswrd -B vpopmail --no-data >vpopmail.sql
拿到你的测试库，进行恢复
到这步为止，表结构有了。
2、表空间删除。
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='vpopmail' into outfile '/tmp/discad.sql';
source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功。主外键关系
很绝望，一个表一个表分析表结构，很痛苦。
set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。
3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
cp -a vpopmail/*.ibd /app/mysql/vpopmail/
chown mysql.mysql /app/mysql/vpopmail/
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='vpopmail' into outfile '/tmp/import.sql';
soruce /tmp/import.sql 就OK
4、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态
dd if=/dev/vda of=/dev/vdb 将vda盘的数据复制到vdb中
3.事务
特性：
Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）
事务之间不相互影响。
Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
事务的生命周期
事务开始 begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令
事务提交 commit 完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了
回滚事务 rollback 将内存中，已执行过的操作，回滚回去
自动提交功能
3306 [vpopmail]>select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
3306 [(none)]>set autocommit=0;
3306 [(none)]>set global autocommit=0;
注：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
(1)
set autocommit=0;
set global autocommit=0;
(2)
vim /etc/my.cnf
autocommit=0
隐式提交语句
用于隐式提交的 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
例如:
set autocommit=0;
begin;
delete from city where id>1000;
select * from city where id>1000;
rollback; 回滚
select * from city where id>1000;
理解概念
redo log ---> 重做日志 ib_logfile0~1 50M , 轮询使用
redo log buffer ---> redo内存区域
ibd ----> 存储 数据行和索引
buffer pool --->缓冲区池,数据和索引的缓冲
LSN : 日志序列号
磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.
图片.png
mysql csr 前滚
MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
情况一:
我们做了一个事务,begin开始事务;update修改数据;commit提交事务.
1.在begin ,会立即分配一个TXID=tx_01.
2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到内存中data buffer中
3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到内存中redobuffer
5. 执行commit时,LGWR日志写线程会将内存中redobuffer信息写入磁盘中redolog日志文件中,基于WAL原则,
在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102
MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动
以上的工作过程,我们把它称之为基于REDO的"前滚操作"
undo 回滚日志
在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备
锁
“锁”顾名思义就是锁定的意思。
“锁”的作用是什么？
在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).
悲观锁:行级锁定(行锁)
谁先操作某个数据行,就会持有<这行>的(X)锁.
乐观锁: 没有锁
例如
行级锁
3306 [world]>select @@autocommit; 两边都关闭自动提交
begin；
update city set name='yyyy' where id=999;
另外一边
update city set name='yyyyi' where id=999; 就会hold住 默认等待50秒超时 提示如下
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update city set name='yyyyi' where id=10000; 如果是其他行就可以正常运行
隔离级别
3306 [world]>select @@tx_isolation; 查询默认隔离级别
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
影响到数据的读取,默认的级别是 RR模式.
3306 [world]>set global transaction_isolation='read-committed'; 修改级别
例如：
同时打开两个mysql会话
设置 set autocommit=0;
同时开启两个事务
一个mysql会话 录入以下
begin；
update city set name='yyywwwwy' where id=999;
一个mysql会话 录入
begin；
select * from city where id=999; 看值的变化
RR : 必须两个会话 commit 后 才能看到 变化的值
RC： 一个会话 commit后 另一个会话立马可以看到变化的值
RU：一个会话修改后 不提交事务 另一个会话立马可以看到变化的值
总结：
transaction_isolation 隔离级别(参数)
负责的是,MVCC,读一致性问题
RU : 读未提交,可脏读,一般不出现
RC : 读已提交,可能出现幻读,可以防止脏读（脏读 修改数据是在内存中 还没有写入磁盘中）.
RR : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.
例如:
[world]>select * from city where id=999 for update; 这种就锁定了这行数据
[world]>commit;
核心参数：
查看默认存储引擎
show engines;
show variables like 'default_storage_engine';
select @@default_storage_engine;
alter table city engine='innodb';
删除外键
show create table city;
| city | CREATE TABLE `city` (
`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`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 |
alter table city drop FOREIGN KEY city_ibfk_1; 删除外键
独立表空间
show variables like 'innodb_file_per_table';
缓存区池
3306 [world]>select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728 | 128M默认值 官网建议物理内存的70-80% 最多80%
+---------------------------+
set global innodb_buffer_pool_size=268435456; 修改缓存区池 256*1024*1024
show engine innodb status\G
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 274857984
Dictionary memory allocated 165130
Buffer pool size 16384 16384*16/1024=256M 等于设置的缓存区池值
Free buffers 9230 9230/16384 不超过70%就说明数据库 够用
Database pages 7154
Old database pages 2620
innodb_flush_log_at_trx_commit (双一标准之一)
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
1，每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush 到操作系统的文件系统缓存 fsync到物理磁盘.
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次；
2，每次事务提交引起写入文件系统缓存,但每秒钟完成一次fsync磁盘操作
图片.png
Innodb_flush_method=(O_DIRECT, fdatasync)
3306 [(none)]>show variables like '%innodb_flush%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | | 默认没有设置
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
+--------------------------------+-------+
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
redo设置
innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3
