MySQL存储引擎、锁类型、并发事务问题、日志文件、数据文件

存储引擎相关查询

# 查看当前mysql支持的存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

#查看当前 mysql 的默认存储引擎
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

#查看某个表用什么引擎
show create table 表名;

#设置存储引擎
## 创建表指定存储引擎
CREATE TABLE `mytest` ( 
	`id` int(11) NOT NULL AUTO_INCREMENT 
) ENGINE=InnoDB(不写默认 InnoDB);

#通过修改配置文件修改默认存储引擎
default-storage-engine = MyISAM
#通过alter命令修改数据表的默认引擎
alter table mytest engine = MyISAM;

行级锁、表级锁和页级锁

• 行级锁：是 MySQL 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁分 为共享锁和排他锁。
  • 特点：加锁开销大，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度最高 页级锁
• 页级锁：是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。采取折衷的页级锁，一次锁定相邻的一组记录。
  • 特点：开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间， 并发度一般。
• 表级锁：是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单， 资源消耗较少，被大部分 MySQL 引擎支持。表级锁定分为表共享读锁（共享锁）与表独占 写锁（排他锁）。
  • 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

锁类型

ＭySQL的表锁有两种模式： 表共享读锁（Table Read Lock） 表独占写锁（Table Write Lock）

• InnoDB两种类型的行锁：
  共享锁（S）：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

上共享锁的写法：lock in share mode
例如： select  math from zje where math>60 lock in share mode；

排他锁（Ｘ）：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

上排它锁的写法：for update
例如：select math from zje where math >60 for update；

• InnoDB两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
  意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

各存储引擎介绍

MyISAM 存储引擎

• 该存储引擎不支持事务、也不支持外键，访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。
• MyISAM 存储引擎索引文件和数据文件是分离的。表相关文件有三个, frm 是存放表结构数 据，MYD 是表数据。MYI 是存放索引，索引树上会存储数据在 MYD 文件里面的位置。
• MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一

MyISAM 主要特性

• 大文件（达到 63 位文件长度）在支持大文件的文件系统和操作系统上被支持
• 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
• 每个 MyISAM 表最大索引数是 64，这可以通过重新编译来改变。每个索引最大的列数是 16
• 最大的键长度是 1000 字节，这也可以通过编译来改变，对于键长度超过 250 字节的情 况，一个超过 1024 字节的键将被用上
• BLOB 和 TEXT 列可以被索引，支持 FULLTEXT 类型的索引，而 InnoDB 不支持这种类 型的索引
• NULL 被允许在索引的列中，这个值占每个键的 0~1 个字节
• 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
• 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE 操作的时候该列被更新，同时 AUTO_INCREMENT 列将被刷新。所以说，MyISAM 类型表 的 AUTO_INCREMENT 列更新比 InnoDB 类型的 AUTO_INCREMENT 更快
• 可以把数据文件和索引文件放在不同目录
• 每个字符列可以有不同的字符集
• 有 VARCHAR 的表可以固定或动态记录长度
• VARCHAR 和 CHAR 列可以多达 64KB

MyISAM存储格式

• 静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容 易缓存，出现故障容易恢复；占用空间通常比动态表多。
• 动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行 optimize table 或 myisamchk -r 命令来改善性能，而且出现故障的时候恢复比较困难。
• 压缩表：使用 myisampack 工具创建，占用非常小的磁盘空间。因为每个记录是被单独 压缩的，所以只有非常小的访问开支。

InnoDB 存储引擎

支持具有提交、回滚及崩溃恢复能力等事务特性，所以比 MyISAM 存储引擎占用更多的磁盘空间

• 表数据文件本身就是按 B+Tree 组织的一个索引结构文件，主键索引叶节点包含了完整的数据记录, 表相关文件只有两个。同样.frm 文件是存放表结构数据，.ibd 存放的数据和索引。

场景

由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的 完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择 InnoDB 有很大 的优势。如果需要频繁的更新、删除操作的数据库，也可以选择 InnoDB，因为支持事务的 提交（commit）和回滚（rollback）。

MERGE 存储引擎

MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge 表就是几个相同 MyISAM 表的聚合器；Merge 表中并没有数据，对 Merge 类型的表可以进行查询、更新、 删除操作，这些操作实际上是对内部的 MyISAM 表进行操作。

场景

对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特 定的时间端相关。例如：可以用 12 个相同的表来存储服务器日志数据，每个表用对应各个 月份的名字来命名。当有必要基于所有 12 个日志表的数据来生成报表，这意味着需要编写 并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些 表合并起来使用一条查询，之后再删除 Merge 表，而不影响原来的数据，删除 Merge 表只 是删除 Merge 表的定义，对内部的表没有任何影响。

MEMORY 存储引擎

MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。
MEMORY 存储引擎默认使用哈希（HASH）索引，其速度比使用 B-+Tree 型要快，但也可 以使用 B 树型索引。由于这种存储引擎所存储的数据保存在内存中，所以其保存的数据具 有不稳定性，比如如果 mysqld 进程发生异常、重启或计算机关机等等都会造成这些数据的 消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。

场景

如果需要该数据库中一个用于查询的临时表

ARCHIVE 存储引擎

Archive 是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入 和查询两种功能。在 MySQL 5.5 版以前，Archive 是不支持索引，但是在 MySQL 5.5 以后的版本中就开始支持索引了。Archive 拥有很好的压缩机制，它使用 zlib 压缩库，在记录被 请求时会实时压缩，所以它经常被用来当做仓库使用。

场景

由于高压缩和快速插入的特点 Archive 非常适合作为日志表的存储引擎，但是前提是 不经常对该表进行查询操作

CSV 存储引擎

使用该引擎的 MySQL 数据库表会在 MySQL 安装目录 data 文件夹中的和该表所在数据库 名相同的目录中生成一个.CSV 文件（所以，它可以将 CSV 类型的文件当做表进行处理）， 这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索 引，即使用该种类型的表没有主键列；另外也不允许表中的字段为 null。csv 的编码转换需 要格外注意。

场景

这种引擎支持从数据库中拷入/拷出 CSV 文件。如果从电子表格软件输出一个 CSV 文件，将其存放在 MySQL 服务器的数据目录中，服务器就能够马上读取相关的 CSV 文件。 同样，如果写数据库到一个 CSV 表，外部程序也可以立刻读取它。在实现某种类型的日志 记录时，CSV 表作为一种数据交换格式，特别有用

BLACKHOLE 存储引擎

黑洞存储引擎，写入的任何数据都会消失，可以应用于主备复制中的分发主库。
该存储引擎支持事务，而且支持 mvcc 的行级锁，写入这种引擎表中的任何数据都会消失， 主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

场景

如果配置一主多从的话，多个从服务器会在主服务器上分别开启自己相对应的线程， 执行 binlogdump 命令而且多个此类进程并不是共享的。为了避免因多个从服务器同时请求 同样的事件而导致主机资源耗尽，可以单独建立一个伪的从服务器或者叫分发服务器。

PERFORMANCE_SCHEMA 存储引擎

该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细 信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供 MySQL 服务器性 能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变 mysql 服务 器的监控周期，例如（CYCLE、MICROSECOND）。 MySQL 用户是不能创建存储引擎为 PERFORMANCE_SCHEMA 的表。

场景

DBA 能够较明细得了解性能降低可能是由于哪些瓶颈。

Federated 存储引擎

该存储引擎可以不同的 Mysql 服务器联合起来，逻辑上组成一个完整的数据库。这种存储 引擎非常适合数据库分布式应用。
Federated 存储引擎可以使你在本地数据库中访问远程数据库中的数据，针对 federated 存 储引擎表的查询会被发送到远程数据库的表上执行，本地是不存储任何数据的。

InnoDB 和 MyISAM 的对比

• InnoDB 和 MyISAM 实现都是使用 B+树
• 由于锁粒度的不同, InnoDB 比 MyISAM 支持更高的并发;
• InnoDB 为行级锁, MyISAM 为表级锁,所以 InnoDB 相对于 MyISAM 来说,更容易发生死 锁,锁冲中突的概率更大,而且上锁的开销也更大,因为需要为毎一行加锁;
• 在备份容灾上, InnoDB 支持在线热备,有很成熟的在线热备解决方案;
• 查询性能上, MyISAM 的查询效率高于 InnoDB, 因为 InnoDB。DB 在查询过程中,是需要维 护数据缓存,而且查询过程是先定位到行所在的数据块,然后在从数据块中定位到要查找的行; 而 MyISAM 可以直接定位到数据所在的内存地址,可以直接找到数据;
• SELECT COUNT(*)语句,如果行数在千万级别以上, MyISAM 可以快速查出,而 InnoDB 查询的特别慢,因为 MyISAM 将行数单独存储了,而 InnoDB 需要逐行去统计行数;所以如果使用 InnoDB,而且需要查询行数,则需要对行数进行特殊处理,如:离线查询并缓存;
• MyISAM 的表结构文件包括: .frm 表结构定义, .MYD(索引), .MYD(数据); 而 InnoDB 的表 数据文件为: .ibd 和.frm(表结构定义)

MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。 MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则 取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。 MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
InnoDB 中，表数据文件（数据和索引放在一块）本身就是按 B+Tree 组织的一个索引结构。 这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键。因此 InnoDB 表数据文件本身就是主索引。

日志文件

日志类型	名称	用途	备注
error log	错误日志	排错	/var/log/mysqld.log【默认开启】
bin log	二进制日志	备份	增量备份 DDL DML DCL
Relay log	中继日志	复制	接收 replication master
slow log	慢查询日志	调优	查询时间超过指定值

Binlog

1. 使用场景是否需要事务支持

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

2. 如何开启mysql日志？mysqld,cnf 中 [mysqld] 添加如下内容

[mysqld] 
# binlog 配置 
server_id=1 
log_bin = mysql-bin 
binlog_format = ROW 
expire_logs_days = 30 #设置expire_logs_days自动过期清理binlog

重启 sudo /etc/init.d/mysql restart

Binlog操作

1. 登录到mysql查看binlog,只查看第一个binlog文件的内容

mysql> show binlog events;

2. 查看指定binlog文件的内容

mysql> show binlog events in 'mysql-bin.000001';

3. 查看当前正在写入的binlog文件(查看master当前的日志文件及日志位置)

mysql> show master status\G

4. 获取binlog文件列表

mysql> show binary logs;

数据文件

• frm 文件 ：不论是什么存储引擎，每一个表都会有一个以表名命名的.frm 文件，与表相关的元数据（meta) 信息都存放在此文件中，包括表结构的定义信息等。
• MYD 文件：myisam 存储引擎专用，存放 myisam 表的数据（data)。每一个 myisam 表都会有一个.MYD 文件与之呼应，同样存放在所属数据库的目录下
• MYI 文件：也是 myisam 存储引擎专用，存放 myisam 表的索引相关信息。对于 myisam 存储引擎来说， 可以被缓存(cache)的内容主要就是来源于.MYI 文件中。每一个 myisam 表对应一个.MYI 文件，其存放的位置和.frm 及.MYD 一样
• .ibd 文件和 ibdata 文件：存放 innoDB 的数据文件（包括索引）。innoDB 存储引擎有两种表空间方式：独享表空间和 共享表空间。
  • 独享表空间：使用.ibd 文件来存放数据，且每个表一个.ibd 文件，文件存放在和 innodb 数据相同的位置。
  • 共享表空间：使用.ibdata 文件，所有表共同使用一个（或多个，自行配置）ibdata 文件。
• db.opt 文件此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规。

并发事务问题

1. 可以交给应用解决—更新丢失(Lost Update)
2. 需要数据库提供事务间的隔离机制来解决—脏读(Dirty Reads) 、不可重复读(Non-Repeatable Reads) 、幻读(Phantom Reads)
3. 实现隔离机制的方法主要有两种----1）加读写锁； 2) 一致性快照读，即 MVCC, MVCC只在repeatable read和read committed两个隔离级别下工作