存储引擎介绍
什么是存储引擎
MySQL引擎:可以理解为MySQL的"文件系统",只不过功能更强大。
MySQL自带存储引擎类型
1)InnoDB2)MyISAM 3)MEMORY HASH索引 4)ARCHIVE 5)FEDERATED 6)EXAMPLE 7)BLACKHOLE 8)MERGE 9)NDBCLUSTER 10)CSV
查看存储引擎表
只要能看到存储引擎engine就行
-#方法一:
mysql> show create table student;
+---------+----------------------------------------------+
| Table | Create Table | |
+---------+----------------------------------------------+
| | CREATE TABLE `student` ( |
| | `id` int(11) NOT NULL DEFAULT '0', |
| | `name` varchar(10) DEFAULT NULL, |
| student | `age` tinyint(4) DEFAULT NULL, |
| | `gender` enum('m','f') DEFAULT NULL, |
| | `phone` char(11) DEFAULT NULL, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------+
-#方法二:
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';
+--------------------+---------------------------+--------+
| table_schema | table_name | engine |
+--------------------+---------------------------+--------+
| mysql | db | MyISAM |
| mysql | event | MyISAM |
| mysql | func | MyISAM |
| mysql | plugin | MyISAM |
| mysql | proc | MyISAM |
| mysql | procs_priv | MyISAM |
| mysql | proxies_priv | MyISAM |
| mysql | servers | MyISAM |
| mysql | tables_priv | MyISAM |
+--------------------+---------------------------+--------+
-#方法三:
mysql> show table status like 'city';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| city | InnoDB | 10 | Compact | 4188 | 97 | 409600 | 0 | 212992 | 0 | 4080 | 2023-07-27 15:20:13 | NULL | NULL | latin1_swedish_ci | NULL | | |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
-#方法四:
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
存储引擎设置
-#方法一:在启动配置文件中设置服务器存储引擎
在/etc/my.cnf配置文件的[mysqld]标签下添加:
default_storage_engine=<Storage Engine>
-#方法二:使用SET(select)命令为当前客户机设置
在MySQL命令行中临时设置
select @@storage_engine=<Storage Engine>
-#方法三:建表指定存储引擎
create table zls.t2_myisam(id int) engine myisam;
InnoDB和MyISAM的区别
物理区别
-# data目录下(/app/mysql/data/)
[root@db01 data]# ll /app/mysql/data/world/city.*
-rw-rw---- 1 mysql mysql 8710 Jul 27 15:20 /app/mysql/data/world/city.frm
-rw-rw---- 1 mysql mysql 671744 Jul 27 15:20 /app/mysql/data/world/city.ibd
[root@db01 data]# ll /app/mysql/data/mysql/user.*
-rw-rw---- 1 mysql mysql 10684 Jul 20 19:03 /app/mysql/data/mysql/user.frm
-rw-rw---- 1 mysql mysql 656 Jul 25 11:52 /app/mysql/data/mysql/user.MYD
-rw-rw---- 1 mysql mysql 2048 Jul 26 18:12 /app/mysql/data/mysql/user.MYI
-----------------.frm(format)是表结构、.ibd是表空间(独立表空间)-------------------
-#查看表结构
mysql> desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | MUL | 0 | |
+-------------+----------+------+-----+---------+----------------+
逻辑区别
InnoDB的核心特性
重点:MVCC 多版本并发控制 事务 行级锁 热备份 Crash Safe Recovery(自动故障恢复)
修改表存储引擎
root@localhost [zls] >alter table t1_myisam engine=innodb;
InnoDB特性——表空间
共享表空间
5.5版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展
共享表空间存储
1)undo事务日志2)系统数据 3)临时表
共享表空间切割
mysql> show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name | Value |
+----------------------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+----------------------------------+------------------------+
[root@db01 data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend
独立表空间
存储:表中的真实数据
查看独立表空间是否开启
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
[root@db03 ~]# ll /app/mysql/data/world/city.*
/app/mysql/data/world/city.frm
/app/mysql/data/world/city.ibd
InnoDB核心特性——事务
事务 伴随着交易事务主要针对DML语句(insert、update、delete)
事务特性:
A:原子性:将一个事务视为一个单元,要么全部成功,有一条失败,就全部回滚
C:一致性:事务执行之前和事务执行之后,状态保持一致
I:隔离性:事务与事务之间互相隔离
D:持久性:当事务被提交后,永久写入磁盘
事务的生命周期
# 成功的事务:
begin;
DML
DML
DML
commit;
# 失败的事务
begin;
DML
DML
DML
rollback;
事务控制语句
begin; start transaction; // 开启一个事务
savepoint; // 将事务保存在某一状态
mysql> savepoint zls_4000;
rollback to savepoint; // 回到指定的位置点
mysql> rollback to savepoint zls_4000;
release savepoint; // 删除位置点
mysql> release savepoint zls_4000;
commit; // 提交事务
rollback; // 回滚事务
## 设置
SET AUTOCOMMIT:为当前连接禁用或启用默认 autocommit 模式
vim /etc/my.cnf
[msyqld]
autocommit=0
MySQL在默认情况下,只要执行一个DML语句,就会自动开启一个事务
事务的隐式提交
1)在事务执行期间,如果执行了begin,开启下一个事务,自动提交上一个事务。
2)在事务执行期间,如果执行了DDL、DCL也会自动提交事务
3)在事务执行期间,如果执行锁表语句 lock,unlock也会自动提交上一个事务
4)load data in file,做备份
5)select for update 自动提交事务
6)在autocommit开启的时候,自动提交事务
事务日志(CSR 自动故障恢复)
redo:重做
MySQL数据写入特性:WAL(Write Ahead Log日志优先写)
MySQL数据,包括事务提交后,不是真的把所有数据都写入磁盘持久化,而是优先记录日志
MySQL数据写入磁盘,就是将内存中的数据,写入到独立表空间中(.ibd文件)
undo:不做
如果事务commit提交了,然后断电了,如何CSR?
如果事务没commit,然后断电了,如何CSR?(没有记录redo)
如果事务没commit,并记录到redo,断电了,如何CSR?
## undo log
-rw-rw---- 1 mysql mysql 79691776 Jul 31 08:13 ibdata1
-rw-rw---- 1 mysql mysql 52428800 Jul 28 15:52 ibdata2
## redo log
-rw-rw---- 1 mysql mysql 50331648 Jul 31 08:13 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jul 20 19:03 ib_logfile1
innodb核心特性——锁
锁粒度:行级锁
共享锁:在一个事务执行期间,不阻塞查询操作
排他锁:在一个事务执行期间,阻塞其他修改操作
乐观锁:谁先提交,以谁为准
悲观锁:以事务开启为准,谁先执行修改操作,其他人查询会被阻塞
innodb核心特性—MVCC多版本并发控制
共享锁:在一个事务执行期间,不阻塞查询操作
排他锁:在一个事务执行期间,阻塞其他修改操作
乐观锁:谁先提交,以谁为准
1)只阻塞修改类操作,不阻塞查询类操作
2)乐观锁的机制(谁先提交谁为准)
InnoDB核心特性—隔离级别
mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
# MySQL默认隔离级别:
RR级别 REPEATABLE-READ 可重复读
# 修改隔离级别:
在/etc/my.cnf里加入tx_isolation=想改的级别(例tx_isolation=REPEATABLE-READ)
# MySQL隔离级别(由低到高)
1)RU:read uncommit 未提交读
2)RC:read commit 提交读
3)RR:repeatable read 可重复读
4)SERIALIZABLE:串行化级别