MySQL修改表存储引擎方法总结

爱互踩 爱互踩流量交换~

在mysql中最常用的表存储引擎有myisam与innodb了其它更高级的我们几乎不用也用不,对于myisam<=>innodb的特点我想各位都知道一点了,下面我来介绍myisam<=>innodb相互转换吧.

MySQL作为最常用的数据库,经常遇到各种各样的问题。,今天要说的就是表存储引擎的修改,有三种方式,列表如下.

1.真接修改,在数据多的时候比较慢,而且在修改时会影响读取性能,my_table是操作的表,innoDB是新的存储引擎,代码如下:

ALTER TABLE my_table ENGINE=InnoDB

2.导出,导入,这个比较容易操作,直接把导出来的sql文件给改了,然后再导回去,用mysqldump,枫哥常用的是navicate那样更容易上手,友情提醒风险较大.

3.创建,插入,这个比第一种速度快,安全性比第二种高,推荐,分2步操作.

.创建表,先创建一个和要操作表一样的表,然后更改存储引擎为目标引擎,代码如下:

CREATE TABLE my_tmp_table LIKE my_table;

ALTER TABLE my_tmp_table ENGINE=InnoDB;

b.插入,为了安全和速度,最好加上事务,并限制id(主键)范围,代码如下:

INSERT INTO my_tmp_table SELECT * FROM my_table;

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

查看表的存储引擎,代码如下:

  1. mysql>showcreatetablett7;
  2. +-------+-------------------------------------------------------------------------------------------------------------------------+
  3. |Table|CreateTable|
  4. +-------+-------------------------------------------------------------------------------------------------------------------------+
  5. |tt7|CREATETABLE`tt7`(
  6. `id`int(10)defaultNULL,
  7. `name`char(10)defaultNULL
  8. )ENGINE=MyISAMDEFAULTCHARSET=latin1|
  9. +-------+-------------------------------------------------------------------------------------------------------------------------+
  10. 1rowinset(0.00sec)

查看表的数据量,代码如下:

  1. mysql>selectcount(1)fromtt7;
  2. +----------+
  3. |count(1)|
  4. +----------+
  5. |16777216|
  6. +----------+
  7. 1rowinset(0.00sec)

方法一,直接更改存储引擎,代码如下:

  1. mysql>altertablett7engine=innodb;
  2. QueryOK,16777216rowsaffected(2min39.80sec)
  3. Records:16777216Duplicates:0Warnings:0

方法二,把方法一中的存储引擎改回myisam,代码如下:

  1. mysql>altertablett7engine=myisam;
  2. QueryOK,16777216rowsaffected(27.09sec)
  3. Records:16777216Duplicates:0Warnings:0

从这里也可以看出myisam表要比innodb表快很多,创建个和tt7同样表结构的表,代码如下:

  1. mysql>createtablett7_tmplikett7;
  2. QueryOK,0rowsaffected(0.02sec)

tt7_tmp作为中间结果集,代码如下:

  1. mysql>insertintott7_tmpselect*fromtt7;
  2. QueryOK,16777216rowsaffected(27.20sec)
  3. Records:16777216Duplicates:0Warnings:0

删除原表的数据,代码如下:

  1. mysql>truncatetablett7;
  2. QueryOK,16777725rowsaffected(0.18sec)

这回更改原表的存储引擎,代码如下:

  1. mysql>altertablett7engine=innodb;
  2. QueryOK,0rowsaffected(0.06sec)
  3. Records:0Duplicates:0Warnings:0

速度很快就完成了,再把中间结果集的数据导回原表中,代码如下:

  1. mysql>insertintott7select*fromtt7_tmp;
  2. QueryOK,16777216rowsaffected(2min0.95sec)
  3. Records:16777216Duplicates:0Warnings:0

删除中间表,代码如下:

mysql> drop table tt7_tmp;

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

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

如果是批量更改表的存储引擎,用于生成变更的SQL语句:

SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;') FROM information_schema.tables WHERE table_schema='db_name' AND ENGINE='myisam';

用于生成检查表的SQL语句,代码如下:

SELECT CONCAT('CHECK TABLE ',table_name) FROM information_schema.tables WHERE table_schema='db_name';

根据自己系统配置修改如下参数,以加快变更速度,记得以前的值,一会还得改回来,代码如下:

  1. SETGLOBALsort_buffer_size=64*1024*1024;
  2. SETGLOBALtmp_table_size=64*1024*1024; //phpfensi.com
  3. SETGLOBALread_buffer_size=32*1024*1024;
  4. SETGLOBALread_rnd_buffer_size=32*1024*1024;

补充一下:MySql中有哪些存储引擎?

1 MyISAM:这种引擎是mysql最早提供的,这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种.

静态MyISAM:如果数据表中的各数据列的长度都是预先固定好的,服务器将自动选择这种表类型,因为数据表中每一条记录所占用的空间都是一样的,所以这种表存取和更新的效率非常高,当数据受损时,恢复工作也比较容易做.

动态MyISAM:如果数据表中出现varchar、xxxtext或xxxBLOB字段时,服务器将自动选择这种表类型,相对于静态MyISAM,这种表存储空间比较小,但由于每条记录的长度不一,所以多次修改数据后,数据表中的数据就可能离散的存储在内存中,进而导致执行效率下降,同时,内存中也可能会出现很多碎片,因此,这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理.

压缩MyISAM:以上说到的两种类型的表都可以用myisamchk工具压缩,这种类型的表进一步减小了占用的存储,但是这种表压缩之后不能再被修改,另外,因为是压缩数据,所以这种表在读取的时候要先时行解压缩.

但是,不管是何种MyISAM表,目前它都不支持事务,行级锁和外键约束的功能.

2 MyISAM Merge引擎:这种类型是MyISAM类型的一种变种,合并表是将几个相同的MyISAM表合并为一个虚表,常应用于日志和数据仓库.

3 InnoDB:InnoDB表类型可以看作是对MyISAM的进一步更新产品,它提供了事务、行级锁机制和外键约束的功能.

4 memory(heap):这种类型的数据表只存在于内存中,它使用散列索引,所以数据的存取速度非常快,因为是存在于内存中,所以这种类型常应用于临时表中.

5 archive:这种类型只支持select 和 insert语句,而且不支持索引,常应用于日志记录和聚合分析方面.

相关广告
  • MySQL修改表存储引擎方法总结 MySQL修改表存储引擎方法总结 MySQL修改表存储引擎方法总结
相关阅读

MySQL修改表存储引擎方法总结

2019/10/10 17:36:52 | 谷歌SEO算法 | 友情链接作弊