Mysql DBA 高级运维学习之路-MySQL数据库存储引擎知识

原创

海风掠过 2018-02-17 09:45:28 ©著作权

©著作权归作者所有：来自51CTO博客作者海风掠过的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.MySQL 引擎概述

1.1 什么是存储引擎

我们在录制一个视频文件的时候，可以转换成不同的格式如mp4，avi，wmv等，而且在电脑的磁盘上也会存在于不同类型的文件系统例如windows里常见的文件系统有ntfs,fat32；存在于linux操作系统里常见的文件系统有ext3、ext4、xfs等等。但是跟我们呈现的内容都是一样的，直观的区别是占用系统空间的大小与清晰程度不一样。那么数据库存储引擎也有很多种存储方式。无论用什么存储引擎来存储，用户看到的数据都是一样的。不同的引擎存储，引擎功能，占用的空间的大小，读取性能可能有区别。

1.2 MySQL存储引擎的架构

MySQL的存储引擎是MySQL数据库的重要组成部分，MySQL的每种存储引擎在MySQL里是通过插件的方式使用的，MySQL可以同时支持多种引擎，MySQL常用的存储引擎为MyISAM和Innodb两种。下面是MySQL存储引擎的体系结构简图。

1.3 有关MySQL引擎的特别说明

以上MyISAM、InnoDB和NDB三个存储引擎是目前比较常用的存储引擎，特别是前两种。

1.4 如何查看mysql服务器支持哪些存储引擎

可以在mysql中使用显示引擎的命令得到一个可用引擎的列表

 mysql> show engines\G
 *************************** 1. row ***************************
  Engine: MRG_MYISAM
 Support: YES
 Comment: Collection of identical MyISAM tables
Transactions: NO
  XA: NO
  Savepoints: NO
 *************************** 2. row ***************************
  Engine: InnoDB
 Support: DEFAULT
 Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
  XA: YES
  Savepoints: YES
 *************************** 3. row ***************************
  Engine: FEDERATED
 Support: NO
 Comment: Federated MySQL storage engine
Transactions: NULL
  XA: NULL
  Savepoints: NULL
 *************************** 4. row ***************************
  Engine: BLACKHOLE
 Support: YES
 Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
  XA: NO
  Savepoints: NO
 *************************** 5. row ***************************
  Engine: MyISAM
 Support: YES
 Comment: MyISAM storage engine
Transactions: NO
  XA: NO
  Savepoints: NO
 *************************** 6. row ***************************
  Engine: MEMORY
 Support: YES
 Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
  XA: NO
  Savepoints: NO
 *************************** 7. row ***************************
  Engine: PERFORMANCE_SCHEMA
 Support: YES
 Comment: Performance Schema
Transactions: NO
  XA: NO
  Savepoints: NO
 *************************** 8. row ***************************
  Engine: CSV
 Support: YES
 Comment: CSV storage engine
Transactions: NO
  XA: NO
  Savepoints: NO
8 rows in set (0.00 sec)

1.5 有关MySQL引擎的特别说明

以上MyISAM、InnoDB和NDB三个存储引擎是目前比较常用的存储引擎，特别是前两种。

2.MyISAM引擎介绍

2.1 什么是MyISAM引擎

MyISAM引擎是MySQL关系数据库管理系统的默认存储引擎（MySQL5.5.5以前）。这种MySQL表存储结构从旧的ISAM代码扩展出很多有用的功能，在新版的MySQL中InnoDB引擎由于其对事物参照完整性，以及更高的并发性等有点开始逐步的取代MyISAM引擎。

mysql> use linzhongniao;
Database changed
mysql> show create table student\G
 *************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

每个MyISAM在磁盘上存储成三个文件。第一个文件的以表名开头扩展名为.frm；数据文件的扩展名为.MYD (MYData)；索引文件的扩展名是.MYI (MYIndex)。MySQL系统的表多数都使用了MyISAM引擎。

[root@linzhongniao ~]# ll /data/3306/data/mysql/user.*
 -rw-rw----. 1 mysql mysql 10630 Oct 30 23:09 /data/3306/data/mysql/user.frm
 -rw-rw----. 1 mysql mysql  3520 Oct 30 23:09 /data/3306/data/mysql/user.MYD
 -rw-rw----. 1 mysql mysql  2048 Oct 30 23:09 /data/3306/data/mysql/user.MYI

2.2 MyISAM引擎的特点

(1)不支持事务（事务是指逻辑上的一组操作。组成这组数据的各个单元，要么全成功要么全失败）

(2)表级锁定（数据库更新时锁整个表）：其锁定机制是表级索引，这虽然可以让锁定的实现成本很少但是也同时大大降低了其并发性能。

(3)读写互相堵塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。

(4)只会缓存索引：MyISAM可以通过key_buffer_size缓存索引来大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。

[root@linzhongniao ~]# grep "key_buffer" /data/3306/my.cnf  
key_buffer_size = 32M

(5)读取速度较快，占用资源相对较少

(6)不支持外键约束，但支持全文索引

2.3 MyISAM引擎使用的生产场景

(1)不需要事务支持的业务（例如转账和充值付款就不行）。

(2)一般为读数据比较多的网站应用。读写都频繁不适合。

(3)读写并发访问相对较少的业务。

(4)以读为主的业务，例如：图片信息数据库，用户数据库，商品库等业务。

(5)数据修改相对较少的业务（防止堵塞）。

(6)对数据一致性要求不是非常高的业务。

(7)硬件资源比较差的机器可以用MyISAM。

小结：单一对数据库的操作都可以用MyISAM，所谓单一就是尽量纯读，或者纯写（insert,update,delete）等。

2.4 MyISAM引擎调优精要

(1)设置合适的索引（缓存机制）。

(2)调整读写优先级，根据实际需求确保重要操作更优先执行。

(3)启用延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次性写入）。

(4)尽量顺序操作让insert数据都写入到尾部，减少堵塞。

(5)分解大的操作，降低单个操作的堵塞时间。

(6)降低并发数（减少对MySQL访问），某些高并发场景通过应用进行排队队列机制。

(7)对于相对静态（更改不频繁）的数据库数据，充分利用Query Cache或memcached缓存服务可以极大的提高访问效率。

[root@linzhongniao ~]# grep "query_cache" /data/3306/my.cnf 
 query_cache_size = 2M  缓存的空间的大小
 query_cache_limit = 1M  缓存的限制
 query_cache_min_res_unit = 2k 最小的缓存对象不要搞太大

(8)MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的访问。

(9)把主从复制的主库使用Innodb从库使用MyISAM引擎(不推荐)。

3.InnoDB引擎介绍

3.1 什么是InnoDB引擎？

InnoDB引擎是MySQL数据库的另一个重要的存储引擎。InnoDB引擎的优点是它支持兼容ACID的事务（类似于PostgreSQL）,以及参数完整性（即对外键的支持）。Oracle公司于2005年10月收购了Innobase，Innobase采用双认证授权。它使用GNU发行，也允许将InnoDB结合到商业软件的团体获得授权。

更多参考 refman-5.5-en.html-chapter/storage-engines.html

MySQL5.5.5以后数据库的默认存储引擎为InnoDB。

mysql> select version();
 +------------+
| version()  |
 +------------+
| 5.5.32-log |
 +------------+
1 row in set (0.00 sec)

mysql> use linzhongniao;
Database changed
mysql> show create table student\G
 *************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

InnoDB的文件格式只有ibdata1一种，它共享表空间，所有数据都存放在这里面然后再进行分类。

[root@linzhongniao ~]# ll /data/3306/data/ib*
 -rw-rw----. 1 mysql mysql 134217728 Nov  2 12:05 /data/3306/data/ibdata1
 -rw-rw----. 1 mysql mysql   4194304 Nov  2 12:05 /data/3306/data/ib_logfile0
 -rw-rw----. 1 mysql mysql   4194304 Oct 12 22:52 /data/3306/data/ib_logfile1
 -rw-rw----. 1 mysql mysql   4194304 Oct 12 22:52 /data/3306/data/ib_logfile2

独立表空间对应的物理数据文件

#innodb_file_per_table
#innodb_data_home_dir = /data/3306/data/xxx
innodb_data_file_path = ibdata1:128M:autoextend

3.2 InnoDB引擎的特点

(1)支持事务：支持4个事务隔离级别，读写的堵塞与事务的隔离级别有关。

(2)行级锁定（更新时一般都是锁定当前行）：通过索引实现，全表扫描仍然会是表锁，要注意间歇锁的影响。

(3)具有非常高效的缓存特性：能缓存索引，也能缓存数据。

(4)整个表和主键以Cluster方式存储，组成一棵平衡树。

(5)所有Secondary Index都会保存主键信息。

(6)支持分区，表空间，类似oracle数据库。

(7)支持外键约束，MySQL5.5以前不支持全文索引，以后支持了。

(8)和MyISAM引擎比，对硬件资源要求比较高。

小结：支持事务，行级锁，支持外键。

3.3 InnoDB引擎使用的生产业务场景

(1)需要事务支持的业务（具有良好的事务特性）。

(2)行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的。

(3)数据读写及更新较为频繁的场景，如BBS、SNS、微博等。

(4)数据一致性要求较高的业务例如：充值转账，银行卡转账。

(5)硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO。

(6)相比MyISAM引擎，innodb引擎更消耗资源，速度没有MyiSAM引擎快。

3.4 InnoDB引擎调优

(1)主键尽可能小，避免给Secondary index带来过大的空间负担。

(2)避免全表扫描，因为会使用表锁。

(3)尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗。

(4)在大批量小插入的时候，尽量自己控制事务而不是使用autocommit自动提交。有开关可以控制提交的方式。

(5)合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性,推荐设置为2。

如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。

innodb_flush_log_at_trx_commit=0每个事物提交的时候，每个一秒把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘中，等于0是性能最好的，同样安全性也是最差的，当系统宕机是，会丢失一秒的数据。

innodb_flush_log_at_trx_commit = 1 每个事务提交的时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上。

innodb_flush_log_at_trx_commit = 2 每个事务提交的时候，把事务日志数据从缓存区写到日志文件中，每隔一秒刷新一次日志文件。

(6)避免主键更新，因为这会带来大量的数据移动。

3.5 Innode引擎的的重要参数说明

InnoDB引擎重要参数

[root@linzhongniao ~]# grep -i innodb /data/3306/my.cnf
#default_table_type = InnoDB
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0

这些参数里面最重要的是innodb_buffer_pool_size，缓存会把数据放到这里面。更多的内容我们可以看官方文档，也可以看innodb-heavy这个里面都有，在mysql的安装路径里面，我的mysql安装路径是/usr/local/mysql/。

[root@linzhongniao ~]# ll /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf   
 -rw-r--r--. 1 root root 19791 Oct 12 14:31 /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf
[root@linzhongniao ~]# grep "innodb_buffer" /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf 
innodb_buffer_pool_size = 2G

4.生产环境中如何批量更改MySQL引擎

推荐使用sed对备份内容进行引擎转换的方式，不要忘记修改my.cnf使之支持并能高效的使用对应的引擎。

实战演示：改一个表的存储引擎，我们以student表为例。批量修改表的存储引擎可以把表列出来，然后登录mysql数据库批量的修改。我的mysql引擎是InnoDB的，现在将InnoDB引擎改成MyISAM引擎。

（1）方法一：MySQL命令语句修改

命令如下所示：

alter table student ENGINE = MyISAM;
alter table student ENGINE = InnoDB;

InnoDB引擎改成MyISAM引擎

mysql> use linzhongniao;
Database changed
mysql> show create table student\G
 *************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table student ENGINE = MyISAM;
Query OK, 10 rows affected (0.31 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> show create table student\G
*************************** 1. row ***************************
   Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

（2）方法二：使用sed对备份的内容进行引擎装换

然后用sed命令替换，完了再将数据导进去。适合数据量比较小的，如果数据量太大会有问题。

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -A -B --events --single-transaction --master-data=2 >/server/backup/mysql_backup_$(date +%F).sql
[root@linzhongniao ~]# sed -i "s#InnoDB#MyISAM#g" /server/backup/mysql_backup_2018-11-02.sql 
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock </server/backup/mysql_backup_2018-11-02.sql

提示：注意如果做了主从复制要用--master-data=2做备份，否则在导入数据的时候回报错如下所示：

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock </server/backup/mysql_backup_2018-11-02.sql 
ERROR 1198 (HY000) at line 22: This operation cannot be performed with a running slave; run STOP SLAVE first

（3）方法三：用mysql_convert_table_format命令进行修改

[root@linzhongniao ~]# mysql_convert_table_format --user=root --password=123456 --socket=/data/3306/mysql.sock --engine=MyISAM linzhongniao student
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use linzhongniao;show create table student\G"
 *************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

提示：如果执行mysql_convert_table_format命令时出现下面错误

Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/mysql/bin/mysql_convert_table_format line 20.
BEGIN failed--compilation aborted at /usr/local/mysql/bin/mysql_convert_table_format line 20.

这是因为系统没有按安装DBI组件,DBI(Database Interface)是perl连接数据库的接口。其是perl连接数据库的最优秀方法，他支持包括Orcal、Sybase、mysql、db2等绝大多数的数据库。yum安装perl-DBD-MySQL。

5.MySQL服务的事务介绍

事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，要么全成功要么全失败。

例如：zhangsan给lisi转账5块钱，流程如下：

(1)从zhangsan银行卡取出5块，计算试money-5。

(2)把上面的5块钱打入lisi的账户上，lisi收到5块，money+5。

上述的转账过程，对应的sql语句为:

update zhangsan_account set money=money-5 where name=’zhangsan’;
update lisi_account set money=money+5 where name=’lisi’;

上面的两条SQL操作，在事务中的操作是一起的要么都执行，要么都不执行。这就是事务的原子性。

MySQL5.5支持事务的引擎：innodb/ndb

ndb是mysql集群的引擎

5.1 事务的四大特性（ACID）

(1)原子性（Atomicity）

事务是一个不可分割的单位，事务中的所有SQL等操作要么都发生，要么都不发生。

(2)一致性（Consistency）

事务发生前和发生后，数据的完整性必须保持一致。

(3)隔离性（Isolation）

当并发访问数据时。一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的。还记得备份的参数--single-transaction么？

[root@mysql ~]# mysqldump --help|grep single
  turn --lock-all-tables on, unless --single-transaction is
  forget to read about --single-transaction below). In all
  whole dump. Automatically turns --single-transaction and
  --lock-all-tables on, unless --single-transaction is
  forget to read about --single-transaction below). In all
  ensures that only a single newline is used.
  --single-transaction 
  single transaction. Works ONLY for tables stored in
  --single-transaction dump is in process, to ensure a
single-transactionFALSE

(4)持久性（Durability）

一个事务一旦被提交，他对数据库中的数据改变就是永久性的。如果出了错误事务也不允许撤销，只能“补偿性事务”。

5.2 事务的开启

数据库的默认事务是自动提交的，也就是发一条sql它就执行一条。如果想多条sql放在一个事务中执行，则需要使用事务进行处理，当我们开启一个事务，并且没有提交，mysql会自动回滚事务，或者我们使用rollback命令手动回滚事务。

(1)数据库提交事务的两种方法：

第一种方法：

begin 开启事务

rollback 回滚事务，提示：执行sql语句如果有不成功的用回滚事务

commit 提交事务，等都执行成功了提交事务。

第二种方法：

set autocommit = ON 开启自动提交

set autocommit = OFF 禁止自动提交

(2)演示验证自动提交的作用：

我们查看一下事务是否开启，如果开启禁止自动提交事务

mysql> show variables like '%auto%';
+-----------------------------+-------+
| Variable_name   | Value |
+-----------------------------+-------+
| auto_increment_increment| 2 |
| auto_increment_offset   | 1 |
| autocommit  | ON|
| automatic_sp_privileges | ON|
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode| 1 |
| sql_auto_is_null| OFF   |
+-----------------------------+-------+
7 rows in set (0.00 sec)
mysql> set global autocommit=OFF;
Query OK, 0 rows affected (0.00 sec)

查看是否关闭，如果没有关闭退出mysql重新登录再进行查看

mysql> show variables like '%autoco%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit| OFF   |
+---------------+-------+
1 row in set (0.00 sec)

演示在linzhongniao库的student表中插入一条数据，不手动提交事务退出登录后查看是否存在这条数据

mysql> use linzhongniao
Database changed
mysql> select * from student;
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | 张三|
|  4 | woshishei   |
|  5 | xiaozhang   |
|  7 | lisi|
|  9 | lisi1   |
| 11 | lisi2   |
| 12 | lisi3   |
| 14 | lisi4   |
| 16 | lisi5   |
| 17 | xiaohong|
| 19 | xiaohong1   |
| 21 | xiaohong2   |
+----+-------------+
14 rows in set (0.00 sec)    
mysql> insert into student(name) values('xianghong3');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student; 
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | 张三|
|  4 | woshishei   |
|  5 | xiaozhang   |
|  7 | lisi|
|  9 | lisi1   |
| 11 | lisi2   |
| 12 | lisi3   |
| 14 | lisi4   |
| 16 | lisi5   |
| 17 | xiaohong|
| 19 | xiaohong1   |
| 21 | xiaohong2   |
| 23 | xianghong3  |
+----+-------------+
15 rows in set (0.00 sec)

退出，重新登录，我们看没有提交事务数据没有被保存

mysql> use linzhongniao
Database changed
mysql> select * from student; 
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | 张三|
|  4 | woshishei   |
|  5 | xiaozhang   |
|  7 | lisi|
|  9 | lisi1   |
| 11 | lisi2   |
| 12 | lisi3   |
| 14 | lisi4   |
| 16 | lisi5   |
| 17 | xiaohong|
| 19 | xiaohong1   |
| 21 | xiaohong2   |
+----+-------------+
14 rows in set (0.00 sec)

commit提交事务，如果自动提交事务没有开启，对库表进行修改后要commit提交事务

mysql> insert into student(name) values('xianghong3');
Query OK, 1 row affected (0.00 sec)

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

退出重新登录mysql查看数据是否插入

mysql> use linzhongniao
Database changed
mysql> select * from student; 
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | 张三|
|  4 | woshishei   |
|  5 | xiaozhang   |
|  7 | lisi|
|  9 | lisi1   |
| 11 | lisi2   |
| 12 | lisi3   |
| 14 | lisi4   |
| 16 | lisi5   |
| 17 | xiaohong|
| 19 | xiaohong1   |
| 21 | xiaohong2   |
| 25 | xianghong3  |
+----+-------------+
15 rows in set (0.00 sec)