查看 MySQL 表使用的存储引擎--正确方式 以及修改存储引擎的方式

方法一

2010年 11月 24日 星期三 23:30:17

 

        网上有很多类似于《查看 MySQL 表使用的存储引擎》的文章，不过都不严谨。使用 “SHOW CREATE TABLE 表名”  查看。这种方式查出的结果在某些情况下是不准确的。

 

比如创建表 "test"

1. CREATETABLEtest (

2. idINT(11)defaultNULLauto_increment,

3. schar(60)defaultNULL,

4. PRIMARYKEY(id)

5. ) ENGINE=InnoDB;

 

一般情况这样没任何问题。但是，如果MySQL服务器在配置中，未启用 InnoDB 存储引擎。在创建表 "test" 时，MySQL会自动选择默认的存储引擎 MyISAM 创建。

 

Fifi的博客

实例演示如下：

 

Fifi的博客

MySQL  服务器基本情况：

• MySQL 服务器未启用  InnoDB   存储引擎；
• 测试数据库库名：   mytest  ；
• 测试数据库表名：   test ( mytest.test )  ；
• 测试数据库登录帐号：  root  ；
• 测试数据帐号登录密码：   mypassword  ；

 

Fifi的博客

        列 "Engine" 下显示的值表示表正在使用的 MySQL 存储引擎。在未启用  InnoDB  存储引擎的情况下，我们可以发现正确的方式返回的结果里面，列 "Engine" 为 "MyISAM"，并不是 "InnoDB"  存储引擎。所以，使用 “SHOW CREATE TABLE 表名”  查看表使用的  MySQL  存储引擎是不准确的。

1.     确认 MySQL 服务器 是否启用 InnoDB   存储引擎

 

Fifi的博客

        返回结果是：  "InnoDB"   对应的  "Support"等于 “NO”  ，表示未启用  InnoDB  存储引擎。

01. mysql> SHOW  ENGINES;

02. +------------+---------+----------------------------------------------------------+(省略部分结果)

03. | Engine     | Support | Comment                                                  |(省略部分结果)

04. +------------+---------+----------------------------------------------------------+(省略部分结果)

05. | InnoDB     | NO      | Supports transactions, row-level locking, and foreign keys|(省略部分结果)

06. | MRG_MYISAM | YES     | Collection of identical MyISAM tables                  |(省略部分结果)

07. | BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disa(省略部分结果)

08. | CSV        | YES     | CSV storage engine                                       |(省略部分结果)

09. | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables|(省略部分结果)

10. | FEDERATED  | NO      | Federated MySQL storage engine                           |(省略部分结果)

11. | ARCHIVE    | YES     | Archive storage engine                                   |(省略部分结果)

12. | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance|(省略部分结果)

13. +------------+---------+----------------------------------------------------------+(省略部分结果)

14. 8 rows in set (0.00 sec)

15.  

16. mysql>

17. 

 

2.     创建表 "test"

 

Fifi的博客

01. mysql> create database mytest;

02. Query OK, 1 row affected (0.02 sec)

03. mysql> use mytest;

04. Database changed

05. mysql> CREATE TABLE test (

06. -> id INT(11) default NULL auto_increment,

07. -> s char(60) default NULL,

08. -> PRIMARY KEY (id)

09. -> ) ENGINE=InnoDB;

10. Query OK, 0 rows affected, 2 warnings (0.06 sec)

11. mysql>

12. 

 

Fifi的博客

3.     使用不准确的方式： “SHOW CREATE TABLE 表名”  查看

 

Fifi的博客

01. mysql> SHOW CREATE TABLE test;

02. +-------+----------------------------------------------------------------------------+

03. | Table | Create Table|

04. +-------+----------------------------------------------------------------------------+

05. | test  | CREATE TABLE `test` (

06. `id` int(11) NOT NULL AUTO_INCREMENT,

07. `s` char(60) DEFAULT NULL,

08. PRIMARY KEY (`id`)

09. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

10. +-------+----------------------------------------------------------------------------+

11. 1 row in set (0.00 sec)

12. mysql>

 

Fifi的博客

4.     正确方式一：  SHOW TABLE STATUS from  数据库库名  where Name='表名';

01. ansen@neusoft:/myhome$ mysql -uroot -p'mypassword'

02. Welcome to the MySQL monitor.  Commands end with ; or \g.

03. Your MySQL connection id is 221

04. Server version: 5.1.41-3ubuntu12.7 (Ubuntu)

05.  

06. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

07.  

08. mysql> SHOW TABLE STATUS from mytest where Name='test';

09. +------------+--------+---------+------------+------+----------------+-------------+(省略部分结果)

10. | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |(省略部分结果)

11. +------------+--------+---------+------------+------+----------------+-------------+(省略部分结果)

12. | test | MyISAM |      10 | Fixed      |    0 |              0 |           0 |(省略部分结果)

13. +------------+--------+---------+------------+------+----------------+-------------+(省略部分结果)

14. 1 row in set (0.02 sec)

15.  

16. mysql>

 

Fifi的博客

5.     正确方式二：  mysqlshow  -u 数据库登录帐号 -p '数据库登录帐号密码'   --status   数据库库名   表名

 

Fifi的博客

1. ansen@neusoft:/myhome$ mysqlshow  -uroot -p'mypassword'   --status mytest test

2. Database:mytest  Wildcard: test

3. +------------+--------+---------+------------+------+----------------+-------------+(省略部分结果)

4. | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |(省略部分结果)

5. +------------+--------+---------+------------+------+----------------+-------------+(省略部分结果)

6. | test | MyISAM |      10 | Fixed      |    0 |              0 |           0 |(省略部分结果)

7. +------------+--------+---------+------------+------+----------------+-------------+(省略部分结果)

8. ansen@neusoft:/myhome$

 

方法二

http://blog.csdn.net/zhongweijian/article/details/7619404

 

mysql> show table status like 'mytable' \G;

*************************** 1. row ***************************

           Name: mytable  表名

         Engine: InnoDB  存储引擎伟InnoDB

        Version: 10   mysql版本

     Row_format: Compact   行格式。有Dynamic，fixed，Compact等格式。Dynamic是动态行，表字段里面宝航varchar，BloB等不定长字段。fixed是定长行。Compact是行压缩。

           Rows: 0  表中的行数

 Avg_row_length: 0  平均每行的字节数

    Data_length: 16384   整个表的数据量（字节）

Max_data_length: 0  表最大的容量。0表示无限

   Index_length: 0    索引数据占用磁盘空间的大小

      Data_free: 10485760  表示已分配但还未被使用的空间大小。

 Auto_increment: NULL  下一个AUto_increment的值

    Create_time: 2011-08-06 22:39:46  创建时间

    Update_time: NULL  更新时间

     Check_time: NULL  使用check table等命令时的检查时间

      Collation: utf8_general_ci  默认字符集和字符列排列顺序

       Checksum: NULL  如果启动，则表示整个表的校验和

 Create_options: max_rows=4294967295 avg_row_length=32  表创建时的选项

        Comment:  

1 row in set (0.00 sec)

修改存储引擎的方法

http://blog.sina.com.cn/s/blog_701271e80101326x.html

修改表的存储引擎myisam<=>innodb

查看表的存储引擎
mysql> showcreate table tt7;
+-------+-------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------+
| tt7 | CREATE TABLE `tt7` (
`id` int(10) default NULL,
`name` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看表的数据量
mysql>select count(1) from tt7;
+----------+
| count(1) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)

方法一：

直接更改存储引擎
mysql> altertable tt7 engine=innodb;
Query OK, 16777216 rows affected (2 min 39.80 sec)
Records: 16777216 Duplicates: 0 Warnings: 0

方法二：

把方法一中的存储引擎改回myisam
mysql>alter table tt7 engine=myisam;
Query OK, 16777216 rows affected (27.09 sec)
Records: 16777216 Duplicates: 0 Warnings: 0

从这里也可以看出myisam表要比innodb表快很多

创建个和tt7同样表结构的表
mysql>create table tt7_tmp like tt7;
Query OK, 0 rows affected (0.02 sec)

tt7_tmp作为中间结果集
mysql>insert into tt7_tmp select * from tt7;
Query OK, 16777216 rows affected (27.20 sec)
Records: 16777216 Duplicates: 0 Warnings: 0

删除原表的数据
mysql> truncatetable tt7;
Query OK, 16777725 rows affected (0.18 sec)

这回更改原表的存储引擎
mysql>alter table tt7 engine=innodb;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

速度很快就完成了

再把中间结果集的数据导回原表中
mysql>insert into tt7 select * from tt7_tmp;
Query OK, 16777216 rows affected (2 min 0.95 sec)
Records: 16777216 Duplicates: 0 Warnings: 0

删除中间表
mysql> drop tablett7_tmp;

测试结果：

方法二比较快一点，但是数据量要是比较大的话，方法二就要采用化整为零的分批操作的方式，否则insert操作将会具耗时，并产生大量的undo日志。

如果是小表的话（500M以内，根据自己系统的硬件环境），采用方法一就可以
如果是大表的话，那就采用方法二+批量的方式

如果是批量更改表的存储引擎

用于生成变更的SQL语句：
SELECTCONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;') FROMinformation_schema.tables WHERE table_schema="db_name" ANDENGINE="myisam";

用于生成检查表的SQL语句：
SELECTCONCAT('CHECK TABLE ',table_name) FROM information_schema.tablesWHERE table_schema="db_name";

根据自己系统配置修改如下参数，以加快变更速度（记得以前的值，一会还得改回来）
SETGLOBAL sort_buffer_size=64*1024*1024;
SET GLOBAL tmp_table_size=64*1024*1024;
SET GLOBAL read_buffer_size=32*1024*1024;
SET GLOBAL read_rnd_buffer_size=32*1024*1024;