MySQL alter table storage engine (myisam & lt ;=& gt; innodb)

MySQL alter table storage engine (myisam <=> innodb)

View the supported database engines and default database engines of the current MySQL database

123456789101112131415

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine            | Support | Comment                                                        | Transactions | XA  | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| PERFORMANCE_SCHEMA | YES    | Performance Schema                                            | NO          | NO  | NO        || MRG_MYISAM        | YES    | Collection of identical MyISAM tables                          | NO          | NO  | NO        || CSV                | YES    | CSV storage engine                                            | NO          | NO  | NO        || BLACKHOLE          | YES    | /dev/null storage engine (anything you write to it disappears) | NO          | NO  | NO        || MEMORY            | YES    | Hash based, stored in memory, useful for temporary tables      | NO          | NO  | NO        || InnoDB            | DEFAULT | Supports transactions, row-level locking, and foreign keys    | YES          | YES  | YES        || ARCHIVE            | YES    | Archive storage engine                                        | NO          | NO  | NO        || MyISAM            | YES    | MyISAM storage engine                                          | NO          | NO  | NO        || FEDERATED          | NO      | Federated MySQL storage engine                                | NULL        | NULL | NULL      |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+9 rows in set (0.00 sec)

View the storage engine of a table

1234567891011121314151617181920212223242526272829303132333435

mysql> show tables;+----------------------------------------------+| Tables_in_performance_schema                |+----------------------------------------------+| cond_instances                              || events_waits_current                        || events_waits_history                        || events_waits_history_long                    || events_waits_summary_by_instance            || events_waits_summary_by_thread_by_event_name || events_waits_summary_global_by_event_name    || file_instances                              || file_summary_by_event_name                  || file_summary_by_instance                    || mutex_instances                              || performance_timers                          || rwlock_instances                            || setup_consumers                              || setup_instruments                            || setup_timers                                || threads                                      |+----------------------------------------------+17 rows in set (0.00 sec) mysql> show create table threads;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                                                                                                      |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| threads | CREATE TABLE `threads` (  `THREAD_ID` int(11) NOT NULL,  `PROCESSLIST_ID` int(11) DEFAULT NULL,  `NAME` varchar(128) NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Directly change the storage engine

12345678910111213141516

mysql> show create table wholesale;+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                      |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| jindong_wholesale | CREATE TABLE `wholesale` (  `act_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `goods_id` mediumint(8) unsigned NOT NULL,  `goods_name` varchar(255) NOT NULL,  `rank_ids` varchar(255) NOT NULL,  `prices` text NOT NULL,  `enabled` tinyint(3) unsigned NOT NULL,  PRIMARY KEY (`act_id`),  KEY `goods_id` (`goods_id`) USING BTREE) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Run the following command:

123	mysql> alter table wholesale engine=innodb;Query OK, 1 row affected (0.32 sec)Records: 1  Duplicates: 0  Warnings: 0

 

12345678910111213141516

mysql> show create table wholesale;+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                      |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| jindong_wholesale | CREATE TABLE `wholesale` (  `act_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,  `goods_id` mediumint(8) unsigned NOT NULL,  `goods_name` varchar(255) NOT NULL,  `rank_ids` varchar(255) NOT NULL,  `prices` text NOT NULL,  `enabled` tinyint(3) unsigned NOT NULL,  PRIMARY KEY (`act_id`),  KEY `goods_id` (`goods_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Multi-Table change engine:

123	# mysqldump -uroot -p123456 sx >sx_bak.sqlsed -i "s/MyISAM/InnoDB/g" sx_bak.sql mysql -uroot -p123456 sx <sx_bak.sql

First, most of the projects on my current platform are read-write-less projects, while MyISAM has a much better read performance than Innodb.

The indexes and data of MyISAM are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is closely bound with indexes and data. Without compression, Innodb is much larger than MyISAM.

In terms of application logic I have come into contact with, select count (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements, in fact, this operation of Innodb locks the table. Many people think that Innodb is a row-Level Lock, that is, where is only valid for its primary key, and non-primary keys will lock the entire table.

There is also a lot of application departments often need to give them regular data for some tables, MyISAM is very convenient, as long as they send the corresponding table frm. MYD and MYI files, so that they can start the database in the corresponding version, and Innodb needs to export xxx. SQL, because only files are provided to others, the dictionary data files cannot be used by the other party. If the insert write operation is different from MyISAM, Innodb cannot achieve the Write Performance of MyISAM, if it is an index-based update operation, although MyISAM may be inferior to Innodb, it is also a problem whether the slave database can catch up with highly concurrent writes, it is better to solve MyISAM through the Multi-instance database/table sharding architecture. It is more suitable for the application ring WAL (write ahead logging) with less frequent inserts and more queries) innoDB is suitable for environments with large concurrent writes and queries: supports transactions (ACID space (not limited by large files)

This article permanently updates the link address: