MySQL-存储引擎
2019-08-14 本文已影响0人
文娟_狼剩
1、简介
类型Linux中的文件系统，比文件系统要高级
2、mysql中的存储引擎类型
InnoDB（5.5以后的默认存储引擎）
MyISAM（5.5以前的默认存储引擎）
CSV
MEMORY
BLACKHOLE
FEDERATED（Oracle(dblink----->MySQL)）
2.1 第三方工具：
（1）TokuDB
优势：
压缩比高
插入性能很高
应用范围：监控
（2）MyRocks
（3）RocksDB
3、InnoDB与MyISAM的区别？（面试题）
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 )
7>索引 B+tree B*TREE
4、存储引擎查看及简单修改
4.1 查询存储引擎
4.1.1 查看所有支持的存储引擎：
show engines;
4.1.1 使用 SELECT 确认会话存储引擎：
select @@default_storage_engine;
4.1.1 show查询每个表的存储引擎：
SHOW CREATE TABLE city\G;
SHOW TABLE STATUS LIKE 'city'\G
show table status;
show create table city;
4.1.1 INFORMATION_SCHEMA 确认每个表的存储引擎：
select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
4.2 修改默认存储引擎（不代表生产操作）
1> 会话级别修改：
set default_storage_engine=myisam;
2> 全局级别(仅影响新会话):
set global default_storage_engine=myisam;
注意：以上修改为临时修改，重启之后,所有参数均失效。
如果想要永久生效：写入配置文件 vim /etc/my.cnf [mysqld] default_storage_engine=myisam然后重启mysql服务
注：存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.
4.3 修改一个表的存储引擎
alter table t engine=innodb;
扩展：批量修改表的存储引擎
-- 将world库下所有表的引擎替换为innoDB
select concat("alter table ",table_name," engine=innodb") from information_schema.tables where table_schema='world';
注意：此命令我们经常使用他，进行innodb表的碎片整理
4.4 平常处理过的MySQL问题--碎片处理
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:对表进行按月进行分表(partition,中间件)
业务替换为truncate方式
4.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';
5、InnoDB存储引擎物理存储结构
5.1 InnoDB最直观的存储方式
*.frm：表的列定义
*.ibd：表的数据和索引 *****
ibdata1（5.7版本）：共享表空间文件（回滚数据--UNDO（8.0版本）、系统数据字典） *****
ib_logfile0 ~ ib_logfileN：redo log文件 *****
ibtmp1（5.7版本）：存放临时表
ib_buffer_pool：缓冲区池的映射文件
5.2 InnoDB的表空间管理模式介绍
5.2.1 共享表空间模式(5.5 默认)
ibdata1:目前遗留下来了,用来存储系统数据.
=============================================
5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以，共享表空间保留，只用来存储:系统数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了
=============================================
5.2.1 独立表空间模式(5.6以后默认)
一个表一个ibd文件
5.3、共享表空间的设置
1>查看控制表空间的设置：
wenjuan[world]>select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend |
+-------------------------+
1 row in set (0.01 sec)
wenjuan[world]>
------------------说明：-----------------
ibdata1：应用表空间的名字
12M：默认大小
autoextend：自动扩展，就是12M用完了，会在文件的基础上自动每次扩展64M
默认每次增长的大小：
wenjuan[world]>show variables like '%extend%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64 |
+-----------------------------+-------+
1 row in set (0.00 sec)
wenjuan[world]>
不需要经常设置，一般在mysql初始化之前设定好就行：
mysql初始化之前，在
vim /etc/my.cnf中加入innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend
5.4 独立表空间（5.6以后，默认就支持）设置
1> 查询独立表空间模式
wenjuan[world]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
wenjuan[world]>
------------------说明：---------
值只有两种情况：
1-----on----打开独立表空间模式
0-----off---关闭独立表空间模式，就是共享表空间模式
2> 修改表空间模式-------共享表空间体验(不代表生产操作):
wenjuan[(none)]>set global innodb_file_per_table=0;
5.5 独立表空间迁移
alter table t1 discard tablespace; ----清掉ibd文件
alter table t1 import tablespace; ----重新导入ibd文件
DDL DCL DML
innoDB表 : ibdata1 + frm + ibd
6、InnoDB核心特性--事务（Transaction）
6.1 简介
事务：保证在一个完整的业务逻辑中，所有涉及到的语句，要么全成功，要么全失败.
6.2 ACID特性
Atomic（原子性）：所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。
Consistent（一致性）：如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。
Isolated（隔离性）：事务之间不相互影响。
Durable（持久性）：事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
6.3 事务的生命周期（事务控制语句）
6.3.1 开启事务
begin;
或
start transaction;
6.3.2 标准的事务语句(DML: insert update delete)
wenjuan[world]>delete from city where id>1000;
wenjuan[world]>delete from city where id<500;
6.3.3 事务的结束
（1）rollback; 回滚事务
oldguo[world]>begin;
oldguo[world]>delete from city where id>1000;
oldguo[world]>delete from city where id<500;
oldguo[world]>rollback;
（2）commit ; 提交事务
oldguo[world]>begin;
oldguo[world]>delete from city where id>1000;
oldguo[world]>delete from city where id<500;
oldguo[world]>commit;
6.3.4 自动提交功能
查询事务开启状态：select @@autocommit;（默认开启事务）
（1）关闭会话级别事务：----------临时关闭，重启mysql之后失效
set autocommit=0;
（2）全局关闭事务：-----------临时关闭，重启mysql之后失效
set global autocommit=0;
（3）永久生效：
在 /etc/my.cnf中加入autocommit=0，然后重启mysql
6.3.5 隐式提交的语句
用于隐式提交的 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
7、InnoDB 事务的ACID如何保证？
7.1概念
redo log : 重做日志, 记录数据页的变化. ib_logfile0-n
redo log buffer : redo log 的缓冲区(内存)
ibd : 表空间的数据文件,以段区页方式规划存储数据行和索引
buffer pool : 数据页缓冲区
LSN : Log seq no 日志序列号,redo log\log buffer\ibd\buffer pool
WAL : write ahead log 日志优先写磁盘
脏页 : dirty page 在内存中被修改的数据，并还没有写入磁盘
CKPT : checkpoint,将内存脏页回写到磁盘的动作
TXID : transaction_id,事务ID,伴随着事务的整个生命周期.
undo log : 回滚日志,ibdata1
未完……
