个人学习使用

cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core)
----------------------------------------------------------------------
getenforce 
Enforcing
----------------------------------------------------------------------
systemctl status firewalld.service 
   Active: active (running)
----------------------------------------------------------------------
systemctl status NetworkManager
   Active: active (running)
----------------------------------------------------------------------
MySQL
Server version: 5.7.26

一）介绍

• 存储引擎类似于Linux操作系统中的文件系统
• 查看所有存储引擎

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       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

• InnoDB核心特性
  1.支持事务
  2.支持聚簇索引
  3.支持MVCC
  4.支持行级锁
  5.支持外键
  6.支持各种缓冲区
  7.支持热备
  8.支持自动故障恢复(ACSR)
  9.支持主从复制(GTID,GR)

二）查看、修改存储引擎

• 查看

show engines;
----------------------------------------------------------------------
select @@default_storage_engine;
----------------------------------------------------------------------
show create table t1;
----------------------------------------------------------------------
#查询非InnoDB存储引擎的业务表
select 
table_schema,table_name,engine
from information_schema.tables
where table_schema not in ('mysql','sys','information_schema','performance_schema')
and engine!='innodb';

• 修改
  将多张非InnoDB存储引擎的业务表，替换成InnoDB存储引擎

1.创建表x1,x2,x3,x4,x5，存储引擎为MyISAM;

create table x1(id int) engine=myisam;
create table x2(id int) engine=myisam;
create table x3(id int) engine=myisam;
create table x4(id int) engine=myisam;
create table x5(id int) engine=myisam;

2.设置安全路径

secure-file-priv=/tmp
(1)修改配置文件

vim /etc/my.cnf
----------------------------------------------------------------------
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
port=3306
secure-file-priv=/tmp

(2)重启MySQL

/etc/init.d/mysqld restart
----------------------------------------------------------------------
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

3.查询非InnoDB存储引擎的业务表

select 
table_schema,table_name,engine
from information_schema.tables
where table_schema not in ('mysql','sys','information_schema','performance_schema')
and engine!='innodb';

4.拼接语句导出修改语句

select  concat("alter table ",table_schema,".",table_name," engine=innodb;")
from information_schema.tables
where table_schema not in ('mysql','sys','information_schema','performance_schema')
and engine!='innodb'
into outfile '/tmp/alter.sql';

5.查看导出的文件

ll /tmp/alter.sql
-rw-rw-rw-. 1 mysql mysql 175 Feb 21 20:11 /tmp/alter.sql
----------------------------------------------------------------------
cat /tmp/alter.sql 
alter table test.x1 engine=innodb;
alter table test.x2 engine=innodb;
alter table test.x3 engine=innodb;
alter table test.x4 engine=innodb;
alter table test.x5 engine=innodb;

6.修改存储引擎

mysql -uroot -p
----------------------------------------------------------------------
source /tmp/alter.sql

三）InnoDB物理存储结构

每张表都由.frm与.ibd文件组成

------------------------数据库文件--------------------------------------------
performance_schema
sys
test
world
xuexiao

四）InnoDB事务

4.1）事务的ACID特性

• 原子性（Atomicity，或称不可分割性）
• 一致性（Consistency）
• 隔离性（Isolation，又称独立性）
• 持久性（Durability）

4.2）隔离级别

select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

• RU：读未提交
  READ-UNCOMMITTED
• RC：读已提交
  READ-COMMITTED
• RR：可重复读
  REPEATABLE-READ
• SR ：串行化