MYSQL引擎之MyISAM

321231255

mysql常用的引擎主要有一下几种，MRG_MYISAM 、CSV 、MyISAM、InnoDB、MEMORY ，NDB，其中MyISAM、InnoDB是mysql最常用的存储引擎，今天主要讨论MyISAM， InnoDB后续再继续...
一、MyISAM引擎的特点
1、不支持事物（事物是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败）
2、表级锁定，数据更新时锁定整个表，其锁定级别是表级锁定，这虽然可以染锁定的实现成本很小但是也同时大大降低了其并发性能
3、读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读
4、只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高读取的时候阻塞写入，但读本身并不会阻塞另外的读。（key_buffer_size = 16M）
5、读取速度轻快，占用资源相对较少
6、不支持外键约束、但支持全文索引

二、MyISAM引擎使用的生产业务场景
1、不需要事物支持的业务
2、一般为读数据比较多的应用，读写都频繁场景不适合，读多或者写多的都适合
3、读写并发访问相对较低的业务。(纯读纯写高并发也可以)
4、数据修改相对较少的业务(阻塞问题)
5、以读为主的业务,例如www、blog、图片信息数据库，用户数据库，商品库等业务
PS：单一对数据库的操作都可以使用MyISAM，所谓单一就是尽量纯读，纯写等(insert update deleter)

三、MyISAM还支持3种不同的存储格式
静态[固定长度]表  （默认格式,表字段的长度是固定不变的,优点: 存储快，容易缓存，出现故障容易恢复；确定: 占用磁盘空间相对动态表大，PS：程序在取数据的时候，内容后面的空格会自动去掉，而内容前面的空格不会）
动态表 （表字段是可变的，优点：占用内存较小，确定：频繁的更新删除记录会产生碎片；需要执行OPTIMEZE TABLE语句或myisamchk -r来改善性能，若出现故障恢复相对较困难）
压缩表 （压缩表由myisampack工具创建，占据非常小的磁盘空间，每条记录都是单独压缩的，访问开支小）

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

optimize table tbl_name;
SHOW INDEX FROM `tbl_name`;

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周一次或每月一次即可，只对特定的表运行。

四、MyISAM引擎存储文件结构
该引擎对应的表有3个文件
myisam.frm [表结构定义]、
    myisam.MYD[数据文件]、
    myisam.MYI[索引]
  其中数据文件和索引问价可以放置在不同的目录，平均分配IO,可获得更快的速度.
  通过建表的时候，指定文件存放的目录,路径必须是绝对路径，且具有访问权限.
DATA DIRECTORY = 'data_dir'
INDEX DIRECTORY = 'index_dir'
实例:
  CREATE TABLE `myisamdata` (
  `Host` char(60) DEFAULT NULL,
  `Db` char(64) DEFAULT NULL,
  `User` char(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 DATA DIRECTORY = '/home/myisamdata/' INDEX DIRECTORY = '/home/myisamdata/';

五、MyISAM引擎调优精要

1、设置适合的索引(缓存机制)
2、调整读写优先级，根据实际需求确保重要操作更优先执行
3、启用延迟插入改善大批量写入性能(降低写入频率，尽可能多条数据一次性写入)
4、尽量顺序操作让Insert数据都写入到尾部，减少阻塞
5、分解大的操作，降低单个操作的阻塞时间
6、降低并发数(减少MYSQL的访问)，某些高并发场景通过应用进行排队队列机制
7、对于相对静态(更改不频繁)的数据库数据，充分利用Query Cache 或者memcached  缓存服务可以极大的提高访问效率

my.cnf

query_cache_size =256M

query_cache_limit =256M

query_cache_size =2M

query_cache_min_res_unit =256M

8、MyISAM的Count只有在全表扫描的时候特别搞笑，带有其他条件的count都需要进行实际的数据访问
9、把主从同步的主库使用innodb,从库使用MyISAM引擎 (不推荐)