MySQL 工作原理 存储引擎配置和特点

[TOC]

1547907234411

MySQL 工作原理

MySQL体系结构

1547907266525

MySQL存储引擎

1. 作为可插拔式的组件提供

MySQL 服务软件自带的功能程序,处理表的处理器.

不同存储引擎具有不同的功能和数据存储方式

2. 默认的存储引擎

   MySQL 5.0/5.1 MyISAM

   MySQL 5.5+ InnDB

存储引擎的配置

查看

列出可用的存储引擎类型

SHOW ENGINES;

查看当前表使用存储引擎

SHOW CREATE TABLE 表名;

修改

建表时手动指定存储引擎

未指定时,使用默认存储引擎

create table 表名(字段名 类型 约束条件) engine=存储引擎;

mysql> create table t1(id int);  #使用默认引擎
mysql> create table t2(name char(15)) engine=memory;
mysql> create table t3(id int,age int)engine=innodb;

修改已存在表存储引擎

alter table 表名 engine=存储引擎;

mysql> alter table t4 engine=myisam;

修改设置默认存储引擎

mysql配置文件中添加如下,重启mysql.

[mysqld]
default-storage-engine=innodb

存储引擎特点

各存储引擎特点

存储引擎	主要特点	相关的表文件
myisam	1. 支持表级锁 2. 不支持事务,事务回滚,外键	表名.frm 表名.MYI 表名.MYD
innodb	1.支持行级锁 2.支持事务,事务回滚,外键	表名.frm 表名.ibd ibdata1 ib_logfile0 ib_logfile1
memory	数据放在内存中,数据库重启数据丢失	表名.frm

表文件功能

表文件	功能
表名.frm	存放表结构(即字段信息)
表名.MYI	存放MyISAM引擎表的索引
表名.MYD	存放MyISAM引擎表的数据
表名.ibd	存放InnoDB引擎表的索引和数据
ibdata1	事务未提交的SQL命令
ib_logfile0 ib_logfile1	事务已经提交的SQL命令

MySQL锁机制

锁粒度

粒度	说明	对应存储引擎
表级锁	一次直接对整张表进行加锁	MyISAM
行级锁	只锁定某一行	InnoDB
页级锁	对整个页面进行加锁 ( MySQL 管理数据的基本存储单位,内存中1M就是一页 )

锁类型

类型	说明	对应命令
读锁( 共享锁 )	支持并发读	select
写锁( 互斥锁,排它锁 )	独占锁,上锁期间其他线程不能读表写表.	insert update delete

查看当前的锁状态

检查Table_lock开头的变量,%作通配符

SHOW STATUS LIKE 'Table_lock%';

mysql> SHOW STATUS LIKE 'Table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 99    |
| Table_locks_waited    | 0     |
+-----------------------+-------+

执行查询操作多的表,适合使用myisam存储引擎,这样可以节省系统资源.

执行写操作多的表,适合使用innodb存储引擎,这样可以加大并发访问.

事务

一次sql操作,从开始到结束的过程.

事务回滚

在事务执行过程中,任意一步操作失败,恢复所有的操作.

事务日志:

记录所有sql操作,回滚时即参考事务日志回滚

ibdata1 未提交的SQL命令

​ LSN 日志序列号

ib_logfile0 已经提交的SQL命令

ib_logfile1 已经提交的SQL命令

事务特性(ACID)

名称	说明
Atomic 原子性	事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败
Consistency 一致性	事务操作的前后,表中的记录没有变化
Isolation 隔离性	事务操作是相互隔离不受影响的
Durability 持久性	数据一旦提交,不可改变,永久改变表数据

示例

需要用的几个命令	说明
show variables like "autocommit";	查看提交状态
set autocommit=off;	关闭自动提交
rollback;	数据会滚
commit;	提交数据

#未提交,事务没有执行完,回滚则恢复到最初.
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    2 | leo  |
|    1 | bob  |
+------+------+

mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    2 | leo  |
|    1 | bob  |
+------+------+
2 rows in set (0.00 sec)

#commit提交后,无法回滚
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    2 | leo  |
|    1 | bob  |
+------+------+
2 rows in set (0.00 sec)

mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | bob  |
+------+------+
1 row in set (0.00 sec)