SQL 表类型(存储引擎)的选择_lisa_modify

101第第22篇篇开开发发篇篇第第77章章表表类类型型（（存存储储引引擎擎））的的选选择择和大多数数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。本章将详细介绍存储引擎的概念、分类以及实际应用中的选择原则。7.1MySQL存储引擎概述插件式存储引擎是MySQL数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL默认支持多种存储引擎，以适用于不同领域的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储，用户甚至可以按照自己的需要定制和使用自己的存储引擎，以实现最大程度的可定制性。MySQL5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。默认情况下，创建新表不指定表的存储引擎，则新表是默认存储引擎的，如果需要修改默认的存储引擎，则可以在参数文件中设置default-table-type。查看当前的默认存储引擎，可以使用以下命令：mysqlshowvariableslike'table_type';+---------------+--------+|Variable_name|Value|+---------------+--------+|table_type|MyISAM|+---------------+--------+1rowinset(0.00sec)可以通过下面两种方法查询当前数据库支持的存储引擎，第一种方法为：mysqlSHOWENGINES\G***************************1.row***************************Engine:MyISAMSupport:DEFAULTComment:DefaultengineasofMySQL3.23withgreatperformanceTransactions:NOXA:NOSavepoints:NO***************************2.row***************************Engine:MEMORYSupport:YESComment:Hashbased,storedinmemory,usefulfortemporarytables102Transactions:NOXA:NOSavepoints:NO***************************3.row***************************Engine:MRG_MYISAMSupport:YESComment:CollectionofidenticalMyISAMtablesTransactions:NOXA:NOSavepoints:NO***************************4.row***************************Engine:InnoDBSupport:YESComment:Supportstransactions,row-levellocking,andforeignkeysTransactions:YESXA:YESSavepoints:YES***************************5.row***************************Engine:CSVSupport:YESComment:CSVstorageengineTransactions:NOXA:NOSavepoints:NO5rowsinset(0.00sec)或者采用第二种方法：mysqlSHOWVARIABLESLIKE'have%';+----------------------------+-------+|Variable_name|Value|+----------------------------+-------+|have_archive|NO||have_bdb|NO||have_blackhole_engine|NO||have_compress|YES||have_crypt|YES||have_csv|YES||have_dlopen|YES||have_example_engine|NO||have_federated_engine|NO||have_geometry|YES||have_innodb|YES||have_ndbcluster|NO||have_openssl|NO||have_partitioning|YES|103|have_query_cache|YES||have_row_based_replication|YES||have_rtree_keys|YES||have_symlink|YES|+----------------------------+-------+18rowsinset(0.00sec)以上两种方法都可以用来查看当前支持哪些存储引擎，其中Value显示为“DISABLED”的记录表示支持该存储引擎，但是数据库启动的时候被禁用。在创建新表的时候，可以通过增加ENGINE关键字设置新建表的存储引擎，例如，在下面的例子中，表ai就是MyISAM存储引擎的，而country表就是InnoDB存储引擎的：CREATETABLEai(ibigint(20)NOTNULLAUTO_INCREMENT,PRIMARYKEY(i))ENGINE=MyISAMDEFAULTCHARSET=gbk;CREATETABLEcountry(country_idSMALLINTUNSIGNEDNOTNULLAUTO_INCREMENT,countryVARCHAR(50)NOTNULL,last_updateTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(country_id))ENGINE=InnoDBDEFAULTCHARSET=gbk;也可以使用ALTERTABLE语句，将一个已经存在的表修改成其他的存储引擎。下面的例子介绍了如何将表ai从MyISAM存储引擎修改成InnoDB存储引擎：mysqlaltertableaiengine=innodb;QueryOK,0rowsaffected(0.13sec)Records:0Duplicates:0Warnings:0mysqlshowcreatetableai\G***************************1.row***************************Table:aiCreateTable:CREATETABLE`ai`(`i`bigint(20)NOTNULLAUTO_INCREMENT,PRIMARYKEY(`i`))ENGINE=InnoDBDEFAULTCHARSET=gbk1rowinset(0.00sec)这样修改后，ai表成为InnoDB存储引擎，可以使用InnoDB存储引擎的相关特性。7.2各种存储引擎的特性下面重点介绍几种常用的存储引擎,并对比各个存储引擎之间的区别，以帮助读者理解不同存储引擎的使用方式。表7-1常用存储引擎的对比104特点MyISAMInnoDBMEMORYMERGENDB存储限制有64TB有没有有事务安全支持锁机制表锁行锁表锁表锁行锁B树索引支持支持支持支持支持哈希索引支持支持全文索引支持集群索引支持数据缓存支持支持支持索引缓存支持支持支持支持支持数据可压缩支持空间使用低高N/A低低内存使用低高中等低高批量插入的速度高低高高高支持外键支持下面将重点介绍最常使用的4种存储引擎：MyISAM、InnoDB、MEMORY和MERGE。NDB存储引擎会在第33章MySQLCLUSTER中详细介绍，这里不再赘述。7.2.1MyISAMMyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是：.frm（存储表定义）；.MYD（MYData，存储数据）；.MYI（MYIndex，存储索引）。数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。要指定索引文件和数据文件的路径，需要在创建表的时候通过DATADIRECTORY和INDEXDIRECTORY语句指定，也就是说不同MyISAM表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径，并且具有访问权限。MyISAM类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能访问，会提示需要修复或者访问后返回错误的结果。MyISAM类型的表提供修复的工具，可以用CHECKTABLE语句来检查MyISAM表的健康，并用REPAIRTABLE语句修复一个损坏的MyISAM表。表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。具体的操作步骤可以参见第35章应急处理。MyISAM的表又支持3种不同的存储格式，分别是：静态（固定长度）表；动态表；压缩表。其中，静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是105占用的空间通常比动态表多。静态表的数据在存储的时候会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。但是也有些需要特别注意的问题，如果需要保存的内容后面本来就带有空格，那么在返回结果的时候也会被去掉，开发人员在编写程序的时候需要特别注意，因为静态表是默认的存储格式，开发人员可能并没有意识到这一点，从而丢失了尾部的空格。以下例子演示了插入的记录包含空格时处理的情况：mysqlcreatetableMyisam_char(namechar(10))engine=myisam;QueryOK,0rowsaffected(0.04sec)mysqlinsertintoMyisam_charvalues('abcde'),('abcde'),('abcde'),('abcde');QueryOK,4rowsaffected(0.00sec)Records:4Duplicates:0Warnings:0mysqlselectname,length(name)fromMyisam_char;+---------+--------------+|name|length(name)|+---------+--------------+|abcde|5||abcde|5||abcde|7||abcde|7|+---------+--------------+4rowsinset(0.00sec)从上面的例子可以看出，插入记录后面的空格都被去掉了，前面的空格保留。动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期执行OPTIMIZETABLE语句或myisamchk-r命令来改善性能，并且出现故障的时候恢复相对比较困难。压缩表由myisampack工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。7.2.2InnoDBInnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。下面将重点介绍Inn