存储引擎 

 

一 存储引擎的介绍

　　存储引擎： 可以理解为 mysql的 文件系统，只是功能更加强大， 　　mysql引擎功能： 除了可以提供基本的存取功能，还有更多功能 事务功能 锁定 备份和恢复 优化以及特殊功能

 

二 MySQL中的存储引擎分类

 

1.分类

InnoDB MyISAM MEMORY ARCHIVE FEDERATED EXAMPLE BLACKHOLE MERGE NDBCLUSTER CSV

 

 

 2. 存储引擎查询

 

-- 查看数据库支持的存储引擎 show engines; -- 查看表的存储引擎 show create table city; show table status like 'city'\G -- 查看index_test 库下的所有表的引擎 select table_schema,table_name,engine from information_schema.tables where table_schema='index_test'; -- 查看数据库下存储引擎是innodb的 所有表 select table_schema,table_name,engine from information_schema.tables where engine='InnoDB';

 

 

三 MySQL存储引擎设置

 1. 在启动配置文件中设置服务器存储引擎：

[mysqld] default-storage-engine=InnoDB

 

 2. 使用 SET 命令为当前客户机会话设置：

SET @@storage_engine=InnoDB;

 

 3. 在 CREATE TABLE 语句指定：

CREATE TABLE t (i INT) ENGINE =InnoDB;

 

四 MySQL Innodb引擎存储结构

 1. MyISAM

Myisam存储引擎是在mysql5.5之前版本默认的存储引擎create table yyy(id int(10))engine=myisam;

.frm： 记录表结构 .MYD： 数据存储.MYI： 索引存储

 2. InnoDB

.frm文件：保存了每个表的元数据，包括表结构的定义等，该文件与数据库引擎无关。.ibd文件：保存每个表的数据文件

 

五 innodb的表空间

 

共享表空间：主要存放系统元数据等独立表空间：主要存放用户数据

1. 共享表空间

 1)查看共享表空间

mysql> show variables like "innodb_data_file_path"; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+

 

2) 停服务器,查看ibdata1大小

[root@db2 data]# du -sh ../data/ibdata1 76M ../data/ibdata1

 

3) 修改my.cnf

[mysqld] innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

 

 

ibdata1这个必须要和 du-sh 查到的大小一致

一般都是刚装好数据库就设置这项innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend创建一个表空间，其中包含一个名为 ibdata1 且大小为 50 MB（固定）的数据文件和一个名为 ibdata2 且大小为 50 MB（自动扩展）的数据文件：

 

2.独立表空间

独立表空间 默认是开启 在 my.cnf 设置 0关闭 1开启

表空间是  每个 InnoDB 表的 .ibd 文件

 

-- 否开启 show variables like "innodb_file_per_table";

 

 

六 MySQL中的事务

一组数据操作执行步骤，这些步骤被视为一个工作单元 用于对多个语句进行分组 可以在多个客户机并发访问同一个表中的数据时使用所有步骤都成功或都失败 如果所有步骤正常，则执行 如果步骤出现错误或不完整，则取消

 

 

1. 事务ACID 4个隔离级别

 

Atomic（原子性）[əˈtɒmɪk] 　　所有语句作为一个单元全部成功执行或全部取消。Consistent（一致性） [kənˈsɪstənt] 　　如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。 Isolated（隔离性） [ˈaɪsəleɪtɪd] 　　事务之间不相互影响。Durable（持久性）[ˈdjʊərəbl] 　　事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

 

2. 事务控制语句

开始事务 begin ... commit 取消事务 begin ... rollback

 

 

3. mysql 5.5 默认是自动commit

在mysql5.5开始， 开启事务时不需要begin 语句。 并且 默认是开启autocommit模式，作为一个事务隐式提交每个语句

 

mysql> show variables like "autocommit"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec)

 

 

4. 关闭autocommit

1） 临时设置

我们可以通过以下命令进行修改关闭（0是关闭，1是开启）： SET GLOBAL AUTOCOMMIT=0; -所有新建会话 SET SESSION AUTOCOMMIT=0; -当前会话 SELECT @@AUTOCOMMIT; -查看设置结果

 

2） 永久设置

[mysqld] AUTOCOMMIT=0

 

 

 

5. 其他触发隐式commit的情况

用于隐式提交的 SQL 语句： 　　START TRANSACTION 　　SET AUTOCOMMIT = 1 导致提交的非事务语句： 　　DDL语句： （ALTER、CREATE 和 DROP） 　　DCL语句： （GRANT、REVOKE 和 SET PASSWORD） 　　锁定语句：（LOCK TABLES 和 UNLOCK TABLES）导致隐式提交的语句示例： 　　TRUNCATE TABLE 　　LOAD DATA INFILE 　　SELECT FOR UPDATE

 

6. redo 日志

　　重做日志，是事务日志的一种

作用： 　　在事务acid过程中，实现的是D 持久化的操作

7. undo日志

　　回滚日志 是事务日志的一种作用： 　　在事务acid过程中，实现的是 a c 原子性和一致性的作用

　　

 

七  MySQL中的锁

在事务ACID过程中， “锁”和“隔离级别” 一起来实现隔离性作用

 

1.锁的粒度 　　

　　1、MyIasm：低并发锁——表级锁 　　2、Innodb：高并发锁——行级锁

 

2. 四种隔离级别

READ UNCOMMITTED 允许事务查看其他事务所进行的未提交更改 READ COMMITTED 允许事务查看其他事务所进行的已提交更改 REPEATABLE READ****** 确保每个事务的 SELECT 输出一致 InnoDB 的默认级别 SERIALIZABLE 将一个事务的结果与其他事务完全隔离

 

 

3. 查看默认隔离级别

-- 查看当前会话隔离级别 select @@tx_isolation; -- 查看系统当前隔离级别 select @@global.tx_isolation;

 

 

默认隔离级别是  REPEATABLE READ

 

---

 

转载于:https://www.cnblogs.com/augustyang/p/11511729.html

相关资源：Microsoft SQL Server 2005技术内幕：存储引擎(中文).pdf