MySQL中InnoDB与MyISAM区别

show tablestatus;
 

    查看表采用何种引擎。

      MySQLAdministrator建数据库的时候，表缺省是InnoDB类型。原来是MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
        MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。这样就可以根据数据表不同的用处是用不同的存储类型。

      另外，MyISAM类型的二进制数据文件可以在不同操作系统中迁移。也就是可以直接从Windows系统拷贝到linux系统中使用。
--------------------------------------------------------------
      mysql数据库中最常使用的两种表类型为 innodb和myisam。这两种类型各有优缺点，视具体应用而定。
      InnoDB 给MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recoverycapabilities)的事务安全(transaction-safe (ACID compliant))型表。
      InnoDB提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking readinSELECTs)。这些特性均提高了多用户并发操作的性能表现。   
      在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row levellocks)适宜非常小的空间。
      InnoDB 是MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。
      InnoDB的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。
      在技术上，InnoDB是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
        InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM中，表被存放在单独的文件中。
        InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。
        InnoDB所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，或者是独立的表空间文件）,相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

二、MyISAM

    MyISAM 是MySQL缺省存贮引擎 . 每张MyISAM表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸。
      因为MyISAM相对简单所以在效率上要优于InnoDB..小型应用使用MyISAM是不错的选择.MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。

MyIASM是IASM表的新版本，有如下扩展：
·二进制层次的可移植性。
·NULL列索引。
·对变长行比ISAM表有更少的碎片。
·支持大文件。
·更好的索引压缩。
·更好的键吗统计分布。
·更好和更快的auto_increment处理。

以下是一些细节和具体实现的差别：

    1.InnoDB不支持FULLTEXT类型的索引。

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

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

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

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

    MyISAM类型的二进制数据文件可以在不同操作系统中迁移。
    另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如updatetable set num=1 where name like “�a%” 再另外，使用两种的选择：
      如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。
      如果执行大量的SELECT，MyISAM是更好的选择。
      若需要使用事务处理，但是原来的数据表使用的是myisam，就需要改为bdb或者innodb，这样基于myisam的程序，将类型改为innodb后，其程序不用改动……综上所述，任何一种表都不是万能的，只有恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。

三、MyISAM和InnoDB优化

    1、key_buffer_size-这个参数，key_buffer_size表示索引缓冲区的大小，严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。

      根据网络一些高手写的文章表示可以检查状态值Key_read_requests和Key_reads，即可知道key_buffer_size设置是否合理。比例key_reads /key_read_requests应该尽可能的低，至少是1:100，1:1000更好，虽然我还没有找到理论的依据，但是，我在自己维护的几台实际运行良好的库做过的测试后表明，这个比值接近1：20000，这从结果证明了他们说这话的正确性，我们不妨用之。

        这对MyISAM表来说非常重要。如果只是使用MyISAM表，可以把它设置为可用内存的 30-40%。
        合理的值取决于索引大小、数据量以及负载 --
        记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的key_buffer 都被利用了 -- .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB的情况是非常少的。这么做太浪费了。
        如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size以适应给予磁盘的临时表索引所需。

2、innodb_buffer_pool_size - 这对Innodb表来说非常重要。
      Innodb相比MyISAM表对缓冲更为敏感。
      MyISAM可以在默认的key_buffer_size 设置下运行的可以，
      然而Innodb在默认的innodb_buffer_pool_size 设置下却跟蜗牛似的。
      0由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80%的可用内存。一些应用于 key_buffer 的规则有 -- 如果你的数据量不大，并且不会暴增，那么无需把

3、innodb_additional_pool_size -这个选项对性能影响并不太多，至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为20MB(或者更大)，因此就需要看一下Innodb其他需要分配的内存有多少。

      innodb_log_file_size在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我经常设置为64-512MB，跟据服务器大小而异。

4、innodb_log_buffer_size默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 -- 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB就足够了。越小的系统它的值越小。

5、innodb_flush_logs_at_trx_commit
 
    是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。
    默认值是1，这意味着每次提交的更新事务（或者每个事务之外的语句）都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。
      很多应用程序，尤其是从MyISAM转变过来的那些，把它的值设置为 2就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。
      如果设置 为 0就快很多了，不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2只会丢失刷新到操作系统缓存的那部分事务。

6、table_cache -- 打开一个表的开销可能很大。
    例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。
    如果你有200多个表的话，那么设置为 1024也许比较合适（每个线程都需要打开表），如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。

    table_cache指示表高速缓存的大小。当Mysql访问一个表时，如果在Mysql表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区，这样做的好处是可以更快速地访问表中的内容。
    一般来说，可以通过查看数据库运行峰值时间的状态值Open_tables和Opened_tables，用以判断是否需要增加table_cache的值，即如果open_tables接近table_cache的时候，并且Opened_tables这个值在逐步增加，那就要考虑增加这个值的大小了。

    在mysql默认安装情况下，table_cache的值在2G内存以下的机器中的值默认时256到512，如果机器有4G内存,则默认这个值是2048，但这决不意味着机器内存越大，这个值应该越大，因为table_cache加大后，使得mysql对SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。
    所以平时维护中还是要根据库的实际情况去作出判断，找到最适合你维护的库的table_cache值，有人说：“性能优化是一门艺术”，这话一点没错。大凡艺术品，大都是经过千锤百炼，精雕细琢而成。

      这里还要说明一个问题，就是table_cache加大后碰到文件描述符不够用的问题，在mysql的配置文件中有这么一段提示引用“Thenumber of open tables for all threads. Increasing this valueincreases the number of file descriptors that mysqldrequires.Therefore you have to make sure to set the amount of openfiles allowed to at least 4096 in the variable "open-files-limit"in” section [mysqld_safe]”

      说的就是要注意这个问题，一想到这里，部分兄弟可能会用ulimit -n作出调整，但是这个调整实际是不对的，换个终端后，这个值又会回到原始值，所以最好用sysctl或者修改/etc/sysctl.conf文件，同时还要在配置文件中把open_files_limit这个参数增大，对于4G内存服务器，相信现在购买的服务器都差不多用4G的了，那这个这个open_files_limit至少要增大到4096，如果没有什么特殊情况，设置成8192就可以了。

7、thread_cache -- 线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程
。
8、query_cache --如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通常设置为32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。

9、sort_buffer_size --如果你只有一些简单的查询，那么就无需增加它的值了，尽管你有 64GB的内存。搞不好也许会降低性能。