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