MySQL Default_storage_engine: Choosing the right MySQL storage engine

Now let's put aside the problem with the MySQL storage engine. If your MySQL table is InnoDB and you don't need to worry about how InnoDB works, you've set it up, but you're not sure if it's going to work. These questions will be mentioned below.

About the storage engine

MySQL has been supporting pluggable storage engines since its inception more than 20 years ago, but for a long time MyISAM has been the default storage engine, and many people run MySQL without even knowing the underlying storage engine at all. After all, MySQL was initially designed for small databases of small web sites, and many applications have become accustomed to using MyISAM storage engines.

No problem at first, everything is fine, but now the problem is: MyISAM does not take into account the scenario of high concurrent high load, multi-core CPU and RAID array, nor does it extend flexibly. So the Web site traffic is more and more, they can not expand, because the MySQL query will be locked at the table level for a few seconds (MyISAM only support this lock mechanism). They don't want to damage their business data every time MySQL crashes.

InnoDB Storage Engine

Many people do not know that the MyISAM storage engine has a brother named InnoDB since MySQL existed. and high concurrent load, performance and elasticity (also including atomicity, consistency and isolation) is its specialty.
Of course, there have been some problems in the development of INNODB (especially the performance issues before the 2006 5.0.30), but in the next 10 years, InnoDB has been proven in areas you can think of (or not), and MyISAM has rarely been noticed.
So, starting with MySQL 5.5.5, InnoDB becomes the default storage engine, and now you can hardly find the installation of a large MySQL database using MyISAM instead of InnoDB.
Let me show you how to quickly count and list all the MyISAM tables in your system to facilitate your start planning migrations.

The storage engine you use

The following query shows you the storage engine used and some of their statistics, including the number of tables, size, and so on.

Mysql> SELECT engine,
COUNT (*) as TABLES,
Concat (Round (sum (table_rows)/1000000,2), ' M ') rows,
Concat (SUM (data_length)/(1024*1024*1024), 2, ' G ') data,
Concat (Round) (SUM (index_length)/(1024*1024*1024), 2), ' G ') idx,
Concat (Round) (SUM (data_length+index_length)/(1024*1024*1024), 2), ' G ') total_size,
Round (sum (index_length)/sum (Data_length), 2) Idxfrac
From INFORMATION_SCHEMA. TABLES
WHERE Table_schema not in (' MySQL ', ' performance_schema ', ' information_schema ')
GROUP by engine
ORDER by sum (data_length+index_length) DESC LIMIT 10;
+--------+--------+---------+--------+--------+------------+---------+
| Engine | TABLES | Rows | DATA | IDX | Total_size | Idxfrac |
+--------+--------+---------+--------+--------+------------+---------+
|    InnoDB | 181 | 457.58M | 92.34G | 54.58G |    146.92G | 0.59 |
|     MyISAM | 13 | 22.91M | 7.85G | 2.12G |    9.97G | 0.27 |
+--------+--------+---------+--------+--------+------------+---------+
2 rows in Set (0.22 sec)

Gets a list of MyISAM tables sorted by size, executing the following query:

SELECT
Concat (Table_schema, '. ', table_name) TBL,
Engine
Concat (Round (table_rows/1000000,2), ' M ') rows,
Concat (Round (data_length/(1024*1024*1024), 2), ' G ') data,
Concat (Round (index_length/(1024*1024*1024), 2), ' G ') idx,
Concat (Round (data_length+index_length)/(1024*1024*1024), 2), ' G ') total_size,
Round (index_length/data_length,2) Idxfrac
From INFORMATION_SCHEMA. TABLES
WHERE Table_schema not in (' MySQL ', ' performance_schema ', ' information_schema ')
and engine = ' MyISAM '
Order by Data_length+index_length DESC;

Keep in mind that changing the default storage engine to InnoDB or upgrading MySQL does not automatically convert your table to InnoDB. So far, you need a table to convert tables, or use scripts.
Note that small MyISAM tables also need to be converted, because as long as one myisam is used in a join statement, the entire query is locked with a table-level, so this will have a significant effect on concurrency. So make sure you turn all the MyISAM tables into InnoDB tables.

Convert to InnoDB

It is advisable to familiarize yourself with the InnoDB configuration before you start converting the engine to InnoDB. When you are ready, perform the following query to convert:

SET @DB_NAME = ' your_database ';

SELECT CONCAT (' ALTER TABLE ', table_name, ' engine=innodb; ') As Sql_statements
From Information_schema.tables as TB
WHERE Table_schema = @DB_NAME
and ' ENGINE ' = ' MyISAM '
and ' table_type ' = ' BASE TABLE '
ORDER BY table_name DESC;