DEFAULT_STORAGE_ENGINE:选择正确的MySQL存储引擎

现在让我们把MySQL存储引擎的问题放在一边。如果你的MySQL表都是用的InnoDB而你不需要关心InnoDB是如何运作的，你已经设置了，但不确定是否生效。这些问题将在下面会提到。

关于存储引擎

MySQL自20多年前成立以来一直支持可插拔存储引擎，但在一段相当长的时间里MyISAM一直是默认的存储引擎，许多人运行MySQL甚至对底层存储引擎一点都不了解。毕竟，MySQL刚开始是为小型网站的小型数据库设计的，许多应用已经习惯使用MyISAM存储引擎。

刚开始没什么问题，一切正常，但现在的问题是：MyISAM没有考虑到应用到高并发高负载，多核CPU和RAID阵列的场景，也不能弹性扩展。所以网站流量越来越多后，他们不能扩展，因为MySQL查询会在表级锁上等待数秒(MyISAM只支持这种锁机制)。他们不想每次MySQL崩溃时损坏他们的业务数据。

INNODB存储引擎

许多人并不知道，自MySQL存在以来MyISAM存储引擎就有一个兄弟叫InnoDB。并且高并发负载，性能和弹性(也包括原子性，一致性和隔离)正是它的特长。
当然，在InnoDB发展过程中也有过一些问题(尤其是2006年5.0.30之前的版本的性能问题)，但在这之后的10年时间里，InnoDB已经在你能想到的领域(或者没有)得到了证明，而MyISAM已经很少被关注了。
因此，从MySQL 5.5.5开始，InnoDB成为默认的存储引擎，现在你几乎找不到大型MySQL数据库的安装使用MyISAM而不是InnoDB。
下面让我来告诉你如何快速地统计和列出在你系统的所有MyISAM表，方便你开始计划迁移。

你使用的存储引擎

下面的查询展示你所用的存储引擎以及它们的一些统计信息，包括表数量，大小等。

  1. mysql> SELECT engine,
  2.   count(*) as TABLES,
  3.   concat(round(sum(table_rows)/1000000,2),’M’) rows,
  4.   concat(round(sum(data_length)/(1024*1024*1024),2),’G’) DATA,
  5.   concat(round(sum(index_length)/(1024*1024*1024),2),’G’) idx,
  6.   concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G’) total_size,
  7.   round(sum(index_length)/sum(data_length),2) idxfrac
  8.  FROM information_schema.TABLES
  9. WHERE table_schema not in (‘mysql’, ‘performance_schema’, ‘information_schema’)
  10. GROUP BY engine
  11. ORDER BY sum(data_length+index_length) DESC LIMIT 10;
  12. +——–+——–+———+——–+——–+————+———+
  13. | engine | TABLES | rows    | DATA   | idx    | total_size | idxfrac |
  14. +——–+——–+———+——–+——–+————+———+
  15. | InnoDB |    181 | 457.58M | 92.34G | 54.58G | 146.92G    |    0.59 |
  16. | MyISAM |     13 | 22.91M  | 7.85G  | 2.12G  | 9.97G      |    0.27 |
  17. +——–+——–+———+——–+——–+————+———+
  18. 2 rows in set (0.22 sec)

获取以大小排序的MyISAM表列表，执行如下查询：

  1. SELECT
  2.     concat(table_schema, ‘.’, table_name) tbl,
  3.     engine,
  4.     concat(round(table_rows/1000000,2),’M’) rows,
  5.     concat(round(data_length/(1024*1024*1024),2),’G’) DATA,
  6.     concat(round(index_length/(1024*1024*1024),2),’G’) idx,
  7.     concat(round((data_length+index_length)/(1024*1024*1024),2),’G’) total_size,
  8.     round(index_length/data_length,2) idxfrac
  9.  FROM information_schema.TABLES
  10. WHERE table_schema not in (‘mysql’, ‘performance_schema’, ‘information_schema’)
  11.   AND engine = ‘MyISAM’
  12. ORDER BY data_length+index_length DESC;

需要记住的是，更改默认的存储引擎为InnoDB或者升级MySQL并不会自动把你的表转换为InnoDB。目前为止，你需要一个表一个表地转换，或者使用脚本。
需要注意的是，小的MyISAM表也一样需要转换，因为只要有一个MyISAM用在join语句里，那么整个查询都是用表级锁，所以这将对并发有很大影响。所以确保你把所有的MyISAM表转为InnoDB表。

转换为INNODB

建议在你着手转换引擎为InnoDB之前，最好先熟悉理解一下InnoDB的配置。准备好后，执行如下查询来转换：

  1. SET @DB_NAME = ‘your_database’;
  2.  
  3. SELECT  CONCAT(‘ALTER TABLE `’, table_name, ‘` ENGINE=InnoDB;’) AS sql_statements
  4. FROM    information_schema.tables AS tb
  5. WHERE   table_schema = @DB_NAME
  6. AND     `ENGINE` = ‘MyISAM’
  7. AND     `TABLE_TYPE` = ‘BASE TABLE’
  8. ORDER BY table_name DESC;