Linux运维学习笔记之MySQL存储引擎介绍，学习笔记mysql

Linux运维学习笔记之MySQL存储引擎介绍，学习笔记mysql

第三十五章 MySQL存储引擎

一、MySQL常用引擎

MyISAM、InnoDB、HEAP(In-Memory)和NDB(clustered)

二、MySQL插件式存储引擎的体系结构

三、MySQL最常用的两种引擎：MyISAM和InnoDB

1、MyISAM：表级锁，不支持事务。强调的是性能，其执行数度比InnoDB类型更快。保存表的具体行数

2、InnoDB：行级锁，InnoDB支持事务，以及外部键等高级数据库功能。不保存表的具体行数

3、MySQL5.5.1(含)之前为默认使用的是MyISAM引擎，MySQL5.1之后默认使用的是InnoDB引擎。

四、MyISAM引擎

1、简介

MyISAM是MySQL5.1(含)之前默认存储引擎。它基于更老的ISAM代码，但有很多有用的扩展。

每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。MySQL的系统库表基本上都是MyISAM引擎的文件：

ls -l /data/3306/data/mysql

-rw-rw---- 1mysql mysql 8820 Mar 28 13:36columns_priv.frm

-rw-rw---- 1mysql mysql 0 Mar 28 13:36 columns_priv.MYD

-rw-rw---- 1mysql mysql 4096 Mar 28 13:36columns_priv.MYI

...

-rw-rw---- 1mysql mysql 10630 Mar 28 13:36 user.frm

-rw-rw---- 1mysql mysql 456 Apr 17 13:22 user.MYD

-rw-rw---- 1mysql mysql 2048 Apr 17 13:24 user.MYI

.frm文件存储表定义。

数据文件的扩展名为.MYD (MYData)。

索引文件的扩展名是.MYI (MYIndex)。

要明确表示你想要用一个MyISAM表格，请用ENGINE表选项指出来：CREATE TABLE t (i INT) ENGINE =MYISAM;一般地，ENGINE选项是不必要的;除非默认已经被改变了，MyISAM是默认存储引擎。

2、通过命令查看MyISAM引擎文件类型

file user.frm

user.frm: MySQLtable definition file Version 9

file user.MYD

user.MYD:Hitachi SH big-endian COFF executable, not stripped

file user.MYI

user.MYI: MySQLMISAM compressed data file Version 1

3、MyISAM引擎特点

(1)不支持事务

(2)表级锁定(更新时锁整个表)

(3)读写互相阻塞，但读读不会阻塞

(4)只会缓存索引，不会缓存数据

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

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

4、MyISAM引擎适用的生产场景

(1)不需要事务支持，对数据一致性不高的业务

(2)一般为读多的应用，读写都频繁的不适合，但读多或写多的都适合

(3)读写并发访问相对较低的业务(纯读纯写高并发也可以)(锁定机制问题)

(4)数据修改相对较少的业务(阻塞问题)

(5)中小型网站的部分业务会用

5、MyISAM引擎调优精要

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

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

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

(4)尽量顺序操作让Insert数据都写在尾部，减少阻塞

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

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

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

grep query my.cnf

query_cache_size = 256M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

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

(9)可以把主从同步的主库使用InnoDB引擎，从库使用MyISAM引擎(不推荐)

五、InnoDB引擎

1、简介

InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

InnoDB是为处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。被InnoDB存储引擎管理的两个重要的基于磁盘的资源是InnoDB表空间数据文件和它的日志文件。InnoDB存储它的表&索引在一个表空间中，如果你指定无InnoDB配置选项，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

2、InnoDB引擎特点

(1)支持事务

(2)行级锁定(更新时锁当前行)，但全表扫描时，仍会是表锁，要注意间隙锁的影响

(3)读写阻塞与事务隔离级别相关

(4)缓存索引和数据

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

(6)支持外键约束，5.5以前不支付全文索引，5.5以后就支持了

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

(8)支持分区、表空间，类似Oracle

(9)对硬件资源要求比较高。

3、InnoDB引擎适用的生产场景

(1)需要事务支持，对数据一致性较高的业务

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

(3)读写及更新较为频繁的场景：BBS、SNS、微博、微信等

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

grep innodb my.cnf

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size= 2048M #缓存，官方建议为总内存的50-80%

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= 16M

innodb_log_file_size= 128M

innodb_log_files_in_group= 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0 #为1时，每个表一个表空间文件

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：每个事务提交时，每隔一秒，把事务日志从缓存区写到日志文件中，并把日志文件的数据刷新到磁盘上。即使服务器没有宕机，只是MySQL服务挂了，也可能会丢失数据。

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

innodb_flush_log_at_trx_commit= 2：每个事务提交时，把事务日志从缓存区写到日志文件中，每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上。只有当系统宕机时，才有可能丢失的数据。(推荐使用)

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

六、InnoDB和MyISAM的差别

1、InnoDB不支持FULLTEXT全文类型的索引。

2、InnoDB 中不保存表的具体行数，也就是说，执行select count(*) fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。

3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

4、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

5、LOAD TABLE FROMMASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

6、另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如updatetable set num=1 where name like “%aaa%”

7、小结

两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁。而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。

七、MyISAM的优势(网上文章，不一定)

作为使用MySQL的用户角度出发，Innodb和MyISAM都是比较喜欢的，如果数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是首选。原因如下：

1、平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。

2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

3、经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为最小的一个数据库实例的数据量基本都是几十G大小。

4、从接触的应用逻辑来说，selectcount(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的都会锁全表的。

5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。

7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些selectcount(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。

当然Innodb也不是绝对不用，用事务的项目就用Innodb的。另外，可能有人会说你MyISAM无法抗太多写操作，但是可以通过架构来弥补。

八、批量修改MySQL服务引擎

1、单个修改：SQL语句修改

(1)修改命令

alter table table_name Engine = InnoDB;

alter table table_name Engine = MyISAM;

(2)示例

a、查看student表的存储引擎

show create table student;

+---------+--------------------------------------------------------

|Table |Create T able |

+---------+---------------------------------------------------------

| student |CREATE TABLE `student` (

`id` int(2) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL

) ENGINE=InnoDBDEFAULT CHARSET=latin1 |

+---------+--------------------------------------------------------+

b、修改引擎

alter table student engine = MyISAM;

c、查看student表的存储引擎

show create table student;

+---------+--------------------------------------------------------

|Table |Create T able |

+---------+---------------------------------------------------------

| student |CREATE TABLE `student` (

`id` int(2) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL

) ENGINE=MyISAMDEFAULT CHARSET=latin1 |

+---------+--------------------------------------------------------+

2、批量修改

(1)方法一：使用for循环

(2)方法二：使用sed对备份内容进行引擎转换(数据量大的时候效果不好)

sed -e 's#InnoDB#MyISAM#g' bak_InnoDB.sql > bak_MyISAM.sql

mysql -uroot -p

(3)方法三：使用mysql_convert_table_format进行引擎转换

a、查看命令

which mysql_convert_table_format

/application/mysql/bin/mysql_convert_table_format

b、改指定表

mysql_convert_table_format --user=root--password='123456' --socket=/data/3306/mysql.sock --engine=InnoDB test student

c、批量改库中所有表

mysql_convert_table_format--user=root --password='123456' --socket=/data/3306/mysql.sock --engine=InnoDBtest