Linux操作文档——MySQL存储引擎（5.7.26）

科技2022-07-21 100

文章目录

一、存储引擎简介1、MySQL支持的存储引擎2、常用存储引擎对比二、查看存储引擎1、查看存储引擎设置2、查看表存储引擎状态1、查看指定表2、查看全局表三、修改存储引擎1、修改存储引擎2、整理碎片（仅限innodb）3、批量替换四、InnoDB存储引擎物理存储结构1、共享表空间1、查看共享表空间参数设置2、共享表空间自定义设置（MySQL初始化） 2、独立表空间1、独立表空间设置2、独立表空间迁移五、事务（InnoDB引擎）1、事务的ACID特性2、事务的生命周期1、开启事务2、标准事务语句3、事务结束 3、自动提交机制(autocommit)1、查看参数2、修改参数（关闭自动提交）4、隐式提交 4、事务的ACID1、redo 重做日志2、undo 回滚日志3、锁4、隔离级别1、查看数据库隔离级别2、更改隔离级别方法 5、InnoDB核心参数

一、存储引擎简介

1、MySQL支持的存储引擎

mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)

2、常用存储引擎对比

存储引擎优点缺点innoDB提供事务的支持，回滚，崩溃修复恢复能力，多版本事务并发控制（默认存储引擎）读写效率较差，占用的数据库空间较大Memory内存中对数据创建表，数据全部存储在内存，读写速度非常快，对数据的安全性要求比较低生命周期短MYISAM支持三种存储方式：静态型，动态型，压缩型，占用的空间小，存储的速度快不支持事务和并发TokuDB注重insert性能，压缩比高，数据的插入性能高限制记录不能太大，不注重update的性能

二、查看存储引擎

1、查看存储引擎设置

mysql> SELECT @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)

2、查看表存储引擎状态

1、查看指定表

mysql> show create table school.test; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号', `sname` varchar(255) NOT NULL COMMENT '姓名', `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄', `gender` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别', `intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

2、查看全局表

三、修改存储引擎

1、修改存储引擎

mysql> alter table school.test engine=MyISAM; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table school.test; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号', `sname` varchar(255) NOT NULL COMMENT '姓名', `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄', `gender` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别', `intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

2、整理碎片（仅限innodb）

mysql> alter table school.test engine=innodb; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

3、批量替换

批量替换zabbix表存储引擎innodb为tokudb

mysql> select concat("alter table ",table_schema,".",table_name," engine=tokudb;") from information_schema.tables where table_schema='zabbix';

四、InnoDB存储引擎物理存储结构

[root@mysql-1 ~]# cd /data/mysql/data/ [root@mysql-1 data]# ll 总用量 122928 -rw-r----- 1 mysql mysql 56 8月 27 19:00 auto.cnf drwxr-x--- 2 mysql mysql 20 8月 28 22:16 blog -rw-r----- 1 mysql mysql 307 8月 29 23:39 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 10月 5 13:54 ibdata1 -rw-r----- 1 mysql mysql 50331648 10月 5 13:54 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 10月 4 16:33 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 10月 4 18:01 ibtmp1 drwxr-x--- 2 mysql mysql 4096 8月 27 19:00 mysql -rw-r----- 1 mysql mysql 7637 8月 29 23:39 mysql-1.err -rw-r----- 1 mysql mysql 6 8月 30 00:05 mysql-1.pid drwxr-x--- 2 mysql mysql 8192 8月 27 19:00 performance_schema drwxr-x--- 2 mysql mysql 82 10月 5 13:54 school drwxr-x--- 2 mysql mysql 8192 8月 27 19:00 sys drwxr-x--- 2 mysql mysql 56 10月 4 17:43 test drwxr-x--- 2 mysql mysql 144 9月 7 09:53 world 组件说明ibdata1系统数据字典信息(统计信息)，UNDO表空间等数据ib_logfile0 ~ ib_logfile1REDO日志文件，事务日志文件ibtmp1临时表空间磁盘位置，存储临时表frm存储表的列信息ibd表的数据行和索引ib_logfile0 ib_logfile1Redo Log，重做日志ibdata1存储在共享表空间中，回滚日志ibtmp1临时表，在做join union操作产生临时数据，用完自动清理

1、共享表空间

1、查看共享表空间参数设置

初始生成默认大小为12M的共享表空间，当空间不足时自动扩展，每次增加64M

2、共享表空间自定义设置（MySQL初始化）

初始生成默认大小为512M的共享表空间，当空间不足时自动扩展，每次增加64M，作用是预留足够共享表空间。

[root@mysql-1 ~]# mysqld --initialize-insecure --user=mysql --basedir=xxxxxx..... innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

2、独立表空间

作用：存储用户数据特点：一个表一个ibd文件，存储数据行和索引信息总结：一张InnoDB表= frm+idb+ibdata1

1、独立表空间设置

mysql> select @@innodb_file_per_table; +-------------------------+ | @@innodb_file_per_table | +-------------------------+ | 1 | +-------------------------+ mysql> set global innodb_file_per_table=0; //1为开启，0为关闭，默认开启

2、独立表空间迁移

（1）创建和原表结构一致的空表（2）将空表的ibd文件删除

mysql> alter table 表名 dicard tablespace;

（3）将原表的ibd拷贝过来，并且修改权限（4）将原表ibd进行导入

mysql> alter table 表名 import tablespace;

五、事务（InnoDB引擎）

1、事务的ACID特性

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

2、事务的生命周期

1、开启事务

mysql> begin;

2、标准事务语句

DML语句 insert update delete

3、事务结束

1、回滚

mysql> rollback;

2、提交（提交后无法回滚）

mysql> commit;

3、自动提交机制(autocommit)

1、查看参数

mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)

2、修改参数（关闭自动提交）

1、会话级别

mysql> set autocommit=0;

2、全局级别

mysql> set global autocommit=0;

3、永久修改(重启生效)

[root@mysql-1 ~]# vim /etc/my.cnf autocommit=0

4、隐式提交

导致提交的非事务语句： DDL语句：（ALTER、CREATE 和 DROP） DCL语句：（GRANT、REVOKE 和 SET PASSWORD）锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

4、事务的ACID

名词解释redo log重做日志，ib_logfile0~1，默认50M , 轮询使用redo log bufferredo内存区域，包含数据页的变化信息+数据页当时的LSN号ibd存储数据行和索引data buffer pool缓冲区池，数据和索引的缓冲LSN日志序列号，存在于ibd，redolog，data buffer pool，redo buffer中脏页内存脏页，内存中发生了修改，没写入到磁盘之前，我们把内存页称之为脏页CKPTCheckpoint，检查点，就是将脏页刷写到磁盘的动作TXID事务号，InnoDB会为每一个事务生成一个事务号，伴随着整个事务WALwrite ahead log 日志优先写的方式实现持久化，日志是优先于数据写入磁盘的

1、redo 重做日志

功能：（1）记录了内存数据页的变化. （2）提供快速的持久化功能（WAL）（3）CSR过程中实现前滚的操作（磁盘数据页和redo日志LSN一致）

2、undo 回滚日志

作用：（1）记录了数据修改之前的状态（2）rollback 将内存的数据修改恢复到修改之前（3）在CSR中实现未提交数据的回滚操作（4）实现一致性快照,配合隔离级别保证MVCC，读和写的操作不会互相阻塞

3、锁

实现了事务之间的隔离功能，InnoDB中实现的是行级锁

4、隔离级别

级别说明RU读未提交，可脏读，一般部议叙出现RC读已提交，会出现不可重复读，可能出现幻读，可以防止脏读RR可重复读，功能是防止"幻读"现象，利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)，必须索引支持，通过MVCC基础解决了不可重复读（默认）SR可串行化,可以防止死锁，但是并发事务性能较差

注意：在RC级别下可以减轻GAP+NextLock锁的问题，但是会出现幻读现象，一般在为了读一致性会在正常select后添加for update语句，但是执行完一定要commit，否则容易出现所等待比较严重.。

1、查看数据库隔离级别

[root@mysql-1 ~]# mysql -uroot -p1 mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)

2、更改隔离级别方法

[root@mysql-1 ~]# vim /etc/my.cnf #服务端配置 [mysqld] #用户 user=mysql #软件安装目录 basedir=/usr/local/mysql #数据路径 datadir=/data/mysql/data #socket文件位置 socket=/tmp/mysql.sock #服务器ID号 server_id=6 #端口号 port=3306 #客户端配置 #transaction_isolation=read-uncommitted //RU #transaction_isolation=read-committed //RC #transaction_isolation=REPEATABLE-READ //RR [mysql] #socket文件位置 socket=/tmp/mysql.sock [root@mysql-1 ~]# systemctl restart mysqld

5、InnoDB核心参数

1、存储引擎默认设置

default_storage_engine=innodb

2、表空间模式

innodb_file_per_table=1

3、共享表空间文件个数和大小

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

4、“双一” 标准

innodb_flush_log_at_trx_commit=1 ------- The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit. With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash. With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash. -------

翻译：为了完全符合ACID，默认设置为1是必需的。在每个事务提交时，日志被写入并刷新到磁盘。如果设置为0，那么日志将每秒写入一次并刷新到磁盘。未为其刷新日志的事务可能会在崩溃中丢失。如果设置为2，则在每个事务提交后写入日志，并每秒刷新到磁盘一次。未为其刷新日志的事务可能会在崩溃中丢失。控制 Redo buffer 和 buffer pool

Innodb_flush_method=(O_DIRECT,fsync) 参数说明fsync最高性能O_DIRECT注重安全

最高安全模式

innodb_flush_log_at_trx_commit=1 Innodb_flush_method=O_DIRECT

最高性能

innodb_flush_log_at_trx_commit=0 Innodb_flush_method=fsync

5、redo日志设置相关

innodb_log_buffer_size=16777216 //日志缓冲区大小 innodb_log_file_size=50331648 //日志大小（默认50M） innodb_log_files_in_group = 3 //redo个数（默认2个），即ib_logfile

6、脏页刷写策略

innodb_max_dirty_pages_pct=75 //脏页占用内存比达到75％时写入磁盘

Processed: 0.014, SQL: 8