MYSQL TABLE CHANGE STORAGE ENGINE

MYSQL TABLE CHANGE STORAGE ENGINE


1.create table and set storage engine
CREATE TABLE `doudou01` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
## default storage engine is innodb
## check table status
mysql> show table status like 'doudou01';
+----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation      | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
| doudou01 | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 2533274790395903 |         1024 |         0 |              1 | 2016-03-18 11:10:59 | 2016-03-18 11:10:59 | NULL       | gbk_chinese_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+
1 row in set (0.00 sec)


CREATE TABLE `doudou02` (
  `country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(50) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;


2.change table engine


2.1 change from myisam table to innodb table
## add innodb_buffer_pool have up to 3 times
## Why add innodb_buffer_poor ?
## Solution : The reason for the increase in buffer pool memory is that InnoDB often has up to 3 times larger storage 
## requirements than MyISAM as InnoDB stores both data and indexes in memory, but MYISAM only stores indexes.
alter table doudou01 engine=innodb;
## check table status
mysql> show table status like 'doudou01';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation      | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
| doudou01 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2016-03-18 11:19:44 | NULL        | NULL       | gbk_chinese_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
1 row in set (0.01 sec)


2.2 change from innodb table to myisam table
alter table doudou02 engine=myisam;


3.Why does converting tables from MyISAM to Innodb cause performance problems? (文件 ID 1023535.1)


Discussion


 All storage engines have different performance properties and even though MySQL provides the same basic functionality for all storage engines, changing storage engines should not be taken lightly from a performance standpoint. The list below includes the most common causes of performance regressions while converting to Innodb and includes some tips related to them:
 Innodb tables typically have larger memory and disk footprint, so disk performance requirements and size of memory grows to maintain the same level of performance.
 Innodb tables are much more sensible to server settings. Therefore, make sure innodb_buffer_pool and innodb_log_file_size are adjusted to reflect your load and hardware configuraton.
 Innodb tables are clustered by the primary key. If you're performing operations with random primary key values, they may be slower than MyISAM tables. This especially applies to inserts.
LOAD DATA and Index Build operations are not optimized for Innodb and always performed row by row, instead of by sorting the data.
 Innodb uses the primary key as row pointers for Secondary indexes, so Index long primary key may decrease performance significantly, especially for key accesses.
 Innodb uses synchronous disk I/O requiring the disk to report that data is actually written to the plate before it can consider operation completed. This increases data security, but reduces performance, especially on lower end disk sub-systems.
 Innodb is a transactional and multi-versioning storage engine. This means it has to deal with the appropriate overhead such as maintaining a transaction log and undo tablespace with previous row versions.


4.Comparing Innodb And MyISAM Storage Engines (文件 ID 1588042.1)


The general recommendations would be:


InnoDB: Any transactions, finer tuning and larger throughput
MyISAM: transactions are not required, mainly inserts and/or reads, GIS data and indexing


########################################################################################
版權所有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文連結： http://blog.itpub.net/26442936/viewspace-2059382/
********* 6年DBA工作經驗，尋求新工作機會 *********
########################################################################################