MyISAM和InnoDB
MySQL最常用存储引擎为：MyISAM和InnoDB。目前5.5版本。MyISAM和InnoDB都已经支持。
MySQL的每种存储引擎在MySQL里是通过插件的方式使用，MySQL可以同时支持多种存储引擎。
其中MyISAM
[root@db01-51 ~]# mysql -S /data/3306/mysql.sock -e "show create table mysql.user\G"
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
......
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
MyISAM引擎有三个文件
[root@db01-51 ~]# ll /data/3306/data/mysql/ -rw-rw---- 1 mysql mysql 8700 Mar 15 22:54 help_category.frm #表的定义 -rw-rw---- 1 mysql mysql 1120 Mar 15 22:54 help_category.MYD #数据 -rw-rw---- 1 mysql mysql 3072 Mar 15 22:54 help_category.MYI #索引
MyISAM的特点：
1、不支持事务
（事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全失败。）
2、表级锁定，数据更新时锁定整个表：其锁定机制是表级锁定，虽然可以锁定的实现成本很小但是也同时大大降低其并发性能。
3、读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAN还会再读取的时候阻塞写入，但读本身不会阻塞另外的读。
4、只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，所以大大提高访问性能减少IO，但是这个缓存区只会缓存索引，而不会缓存数据
[root@db01-51 ~]# grep key_buffer_size /data/3306/my.cnf key_buffer_size = 16M
5、读取数据较快。占用资源少
6、不支持外间约束，但支持全文索引
7、MyISAM引擎是MySQL5.5.5前缺省的存储引擎（is the default storage engine prior to MySQL5.5.5）
MyISAM引擎适用的生产业务场景
1、不需要事务支持的业务（转账不行）
2、一般为读数据比较多的应用，读写都频繁场景不适合，读多或写多都适合。
3、读写并发访问相对较低的业务（纯读写高并发也可以）（锁定机制问题）
4、数据修改相对少的业务（阻塞问题）
5、以读为主的业务。如：数据库关系表、www，blong，图片信息数据库，用户数据库，商品库等业务
6、对数据一致性要求不高的业务（不支持事务）
7、硬件资源比较差的机器可用MyISAM
8使用读写分离的MySQL从库可使用。99.9%都不用MyISAM.
调优精要：
对于相对静态（更改不频繁）的数据看数据充分利用query cache或memcached/redis缓存服务可以极大提高访问效率
[root@db01-51 ~]# grep query /data/3306/my.cnf query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k #long_query_time = 1
MyISAM特点:面试必答
1、Table-level locking
2、Full-text search indexes
3、Index caches
4、No Transactions
5、占用资源少
6、读写阻塞，读读不阻塞
7、不支持外键
什么是InnoDB引擎
InnoDB引擎是MySQL数据库的另外一个重要的存储引擎，被包含在所有二进制安装包里。和其他存储引擎相比，InnoDB引擎优点支持ACID的事务（类似PostgreSQL），以及参数完整性（对外键的支持）。MySQL5.5.5后默认引擎为InnoDB
mysql> show create table bbs.test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; #都是默认ENGINE=InnoDB
1 row in set (0.00 sec)
InnoDB引擎特点：
1、支持事务：支持4个事务隔离级别，支持多版本
2、行级锁定（更新时一般都是锁定当前行）：通过索引实现，全表扫描任然会是表锁，注意间隙所的影响
3、读写阻塞与事务隔离级别相关。
4、具有非常高效的缓存特性：能缓存索引，也能缓存数据
5、整个表和主键以Cluster方式存储，组成一颗平衡树
6、所有Secondary Index都会保存主键信息
7、支持分区，表空间，类似oracle数据库
8、支持外键约束，5.5以前不支持全文索引，后面支持
小结：Supports transactions,row-level locking,and foreign keys
9、和MyIASAM引擎比，InnoDB对硬件资源要求高
InnoDB特点：面试必答项
1、Row-level locking
2、Full-text search indexes
3、Data caches
4、Index caches
5、Transactions
6、占用资源多
7读写阻塞与事务隔离级别相关
8、支持外键
InnoDB引擎适用的生产业务场景
1、需要事务支持的业务（具有较好的事务特性）
2、行级锁定对高并发有很好的适应能力，但需要确保查询时通过索引完成
3、数据读写及更新都较为频繁的场景。如：bbs，SNS，微博，威信等
4、数据库一致性要求较高的业务，如：充值，转账
5、硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO
[root@db01-51 ~]# grep innodb /data/3307/my.cnf innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M #####工作中调到内存的30%比较适合 innodb_data_file_path = ibdata1:128M:autoextend ##开机占1G
黑盒
[root@db01-51 ~]# ll /data/3306/data/ -rw-rw---- 1 mysql mysql 134217728 Mar 21 01:17 ibdata1
工作中需要用到的120个参数，配置10个差不多
[root@db01-51 ~]# mysql -S /data/3306/mysql.sock -e show variables like "'innodb_%';"
5.5之后默认为1之前为0（=0共享表空间）
mysql> show variables like '%innodb_file_per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.00 sec)
[root@db01-51 data]# ll xusx total 16 -rw-rw---- 1 mysql mysql 61 Mar 21 01:10 db.opt -rw-rw---- 1 mysql mysql 8586 Mar 21 01:12 test.frm #表的定义 -rw-rw---- 1 mysql mysql 98304 Mar 21 01:12 test.frm #表的说明
InnoDB引擎调优精要
1、主键尽可能小，避免给Secondary index带来过大的空间负担
2、建立有效所以避免全表扫描，因为会使用表锁
3、尽可能缓存所有的索引和数据，提高响应速度，减少IO消耗
4、在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交。有开关可以控制
5、合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件带磁盘，提交事务的时候不能做任何操作。
6、避免主键更新，因为这会带来大量的数据移动。
工作中修改引擎
alter table 表名 engine = INNODB; alter table 表名 engine = MyISAM; 查看修改后结果 show create table student\G
建表时加上引擎：
mysql> CREATE TABLE test (
-> id int(4) NOT NULL AUTO_INCREMENT,
-> name char(20) NOT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
常见面试题：
生产环境中如何选用MySQL引擎：
一般的既有读又有写的业务中，建议使用InnoDB引擎，一句话尽可能多使用InnoDB引擎。
纯读，纯写用myisam。如系统的mysql库
不用引擎怎么备份：
MyISAM或混合引擎：
mysqldump -S /data/3306/mysql.sock -A -x -B --triggers -R --master-data=2|gzip >/data/bak$(date +%F).sql.gz
InnoDB引擎：
mysqldump -S /data/3306/mysql.sock -A -B --triggers -R --master-data=2 --single-transaction|gzip >/data/bak$(date +%F).sql.gz
MySQL有哪些引擎，各自特点和区别：
MySQL5.5：MyISAM、Memory、Innod、NDB
MySQL5.5支持事务的引擎：InnoDB/ndb
事务的四大特性（ACID）
1、原子性（Atomicity）
事务是一个不可分割的单位，事务中的所有SQL等操作要么都发生，要么都不发生。
2、一致性（Consistency）
事务发生前和发生后，数据的完整性必须保持一致。
3、隔离性（Ioslation）
当并发访问数据库时，一个在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的，备份参数
--data-master=2 --single-transaction
4、持久性（Durability）
一个事务一旦被提交，他对数据库中的数据改变就是永久性的。如果出了错误，事务也不允许撤销，只能通过"补偿性事务"
事务的实现
像其他数据库一样，MySQL在做事务的时候常使用的日志先行的方式保证事务的快速和持久。
当开始一个事务时，会记录该事务的一个LSN日志序列号：当执行事务时，会往innodb_log_buffer日志缓冲区里插入事务日志；当事务提交时，将日志缓冲。。。
innodb_flush_log_at_trx_commit=0，每个事务提交的时候，每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；
innodb_flush_log_at_trx_commit=1，每个事务提交的时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；
innodb_flush_log_at_trx_commit=2，每个事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上。。。
innodb_flush_log_at_trx_commit=0，性能是最好的，同样安全性也是最差的。当系统宕机时，会丢失1秒的数据。
参考手册：
浙公网安备 33010602011771号