MyISAM engine
- The characteristics of
Table level lock: lock the entire table when updating. 3. Read and write block each other: block not only read but also write, but read itself does not block other read. Only indexes are cached: Using key_buffer_size to cache indexes greatly improves access performance with less disk IO, but the cache only caches indexes, not data 5. High read speed and low resource consumption 6. Foreign key constraint is not supported, but full-text index is supported. 7
InnoDB engine
- The characteristics of
Support transaction: support 4 transaction isolation levels, support multi-version read 2. Row-level lock: through the index implementation, full table scan will still be a table lock 3. Read/write blocking is dependent on transaction isolation level. 4. Very efficient caching features: can cache indexes as well as data 5. The entire table and primary keys are stored in cluster mode, forming a balanced tree 6. All Secondary indexes store primary key information 7. Support partition, table space 8. Support foreign key constraints 9. Compared with MyISAM: InnoDB requires higher hardware resources
How do I batch change the MySQL engine in a production environment
-
MySQL > modify MySQL command statement
ALTER TABLE ENGINE= INNODB; ALTER TABLE table_name ENGINE= MyISAM;Copy the code
-
Method 2: Run the sed command to convert the backup data
mysqldump > bak1.sql nohup sed -e 's/MyISAM/InnoDB/g' bak2.sql > bak2.sql & mysql < bak1.sql Copy the code
-
Method 3: mysql_convert_table_format command change
Mysql_convert_table_format --u= user name --p= password -e --engine= storage engine name table nameCopy the code