To find the storage engine for a database:
Show engines
Show variables like '%storage_engine% '
Change the engine of the database to change the configuration file/etc/my.cnf
Modify Default-storage-engine=innodb (the storage engine that needs to be changed), and then restart the database
Service mysqld Restart
ALTER TABLE engine=innodb
The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table)
The following storage engines are most commonly used:
MyISAM: The default mysql plug-in storage engine, which is one of the most commonly used MySQL storage engines in the Web, data warehousing, and other application environments. Note that it is easy to change the default storage engine of the MySQL server by changing the storage_engine configuration variable.
InnoDB: For transactional applications, with many features, including ACID transaction support.
BDB: An alternative to the InnoDB transaction engine that supports COMMIT,ROLLBACK , and other transactional features.
Memory: Keep all your data in RAM and provide extremely fast access in environments where you need to quickly find references and other similar data.
Merge: Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing.
Archive: Provides the perfect solution for storing and retrieving large amounts of historically, archived, or security audit information that is rarely referenced.
Federated: The ability to link multiple separate MySQL servers to create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.
cluster/ndb:MySQL 's clustered database engine, especially for applications with high performance lookup requirements, also requires the highest uptime and availability.
Other: The other storage engines include CSV(referencing a comma-delimited file used as a database table),blackhole(for temporary suppression of application input to the database), and Example engine (can help to quickly create a custom plug-in storage engine).
Keep in mind that you do not have to use the same storage engine for the entire server or scenario, and you can use it for each table in the scenario
different MySQL storage engines, this is important.
The following major Apple told MyISAM and InnoDB
MyISAM Storage Engine
MyISAM is the default storage engine. It is based on older ISAM code, but there are many useful extensions. Some features of the MyISAM storage engine:
· All data values are stored in low bytes first. This separates the data machine from the operating system. The only requirement for binary portability is that the machine uses the complement (as the machine has in the last 20 years) and the IEEE floating-point format (which is completely dominant in the mainstream machine). The only machines that do not support binary compatibility are embedded systems. These systems sometimes use a special processor.
· Storing data in low bytes does not seriously affect speed; The bytes in the data row are generally not federated, and reading the unbound bytes from One Direction is no more resource-intensive than reading from reverse. The code that gets the column values on the server does not appear to be time-tight compared to other code.
· Large files (up to 63-bit file lengths) are supported on file systems and operating systems that support large files.
· When deleting and updating and inserting a mix, the rows of dynamic dimensions are less fragmented. This is done automatically by merging adjacent deleted blocks, and if the next block is deleted, it expands to the next one.
· The maximum number of indexes per MyISAM table is 64. This can be changed by recompiling. The maximum number of columns per index is 16.
· The maximum key length is 1000 bytes. This can also be changed by compiling. For cases where the key length exceeds 250 bytes, a key block of more than 1024 bytes is used.
· The Blob and text columns can be indexed.
· A null value is allowed in the column of the index. This accounts for 0-1 bytes per key.
· All numeric key values are stored first in high bytes to allow a higher index compression.
· When the records are inserted in a sequential order (as if you were using a auto_increment column), the index tree is split so that the high node contains only one key. This improves the spatial utilization of the index tree.
· The internal processing of one auto_incremen column per table. MyISAM automatically updates this column for insert and update operations. This makes the Auto_increment column faster (at least 10%). The value at the top of the sequence cannot be exploited after it has been deleted. (When the Auto_increment column is defined as the last column of a multicolumn index, the use of values removed from the top of the sequence can occur). The auto_increment value can be reset using ALTER TABLE or MYISAMCH.
· If the table in the middle of the data file has no free blocks, you can insert new rows into the table while other threads are reading from the table. (This is known as concurrent operations). The appearance of a free block is the result of deleting rows, or updating a dynamic length row with more data than the current content. When all free blocks are exhausted (filled), future insertions become concurrent.
· You can put data files and index files in different directories, create table for a higher speed with Data directory and index directory options, see 13.1.5, "CREATE table Syntax".
· Each character column can have a different character set.
· Another flag in the MyISAM index file indicates whether the table is closed correctly. If you start the Mysqld,myisam table with the--myisam-recover option, it is automatically checked when it is opened, and the table is repaired if the table is improperly closed.
· If you run Myisamchk with the--update-state option, it labels the table as checked. Myisamchk--fast Check only those tables that do not have this flag.
· Myisamchk--analyze stores statistics for some keys, and also stores statistics for the entire key.
· Myisampack can package blobs and varchar columns.
MyISAM also supports the following features:
· True varchar types are supported, and varchar columns begin with a length stored in 2 bytes.
· A table with varchar can have a fixed or dynamic record length.
· varchar and char columns can be up to 64KB.
· A messed up computed index pair can be used for unique. This allows you to have unique on the merge of any column within the table. (However, you cannot search on a unique computed index).
InnoDB Storage Engine
InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides an Oracle-style, non-locking read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.
The InnoDB is designed for maximum performance when dealing with large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.
The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.
The InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB a default table for MySQL on Windows.
InnoDB is used to generate large database sites that require high performance. The famous Internet news site slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and some other sites handle an average of 800 insertions/updates per second on InnoDB.
The difference between InnoDB and MyISAM
Overview of differences:
MyISAM is the default storage engine in MySQL, and generally not too many people are concerned about this thing. Deciding what kind of storage engine to use is a very tricky thing to do, but it's worth it to study, the article here only considers the two MyISAM and InnoDB, because these two are the most common.
Let's answer some questions first:
Do you have a foreign key in your database?
Do you need business support?
Do you need full-text indexing?
What query patterns do you often use?
How big is your data?
Thinking about these questions can help you find the right direction, but that's not absolute. If you need transaction processing or foreign keys, then InnoDB may be a good way. If you need full-text indexing, then generally speaking, MyISAM is a good choice because it is built in the system, however, we do not actually test 2 million rows of records in a regular manner. So, even slower, we can get full-text indexing from InnoDB by using Sphinx.
The size of the data is an important factor in what kind of storage engine you choose, and large datasets tend to choose the INNODB approach because they support transactional processing and failback. The small database determines the length of time to recover, and InnoDB can use the transaction log for data recovery, which is faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes.
Your habit of manipulating database tables can also be a factor that has a significant impact on performance. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. While the primary key query will be quite fast under InnoDB, it is important to be careful that if our primary key is too long it can cause performance problems. A large number of inserts statements will be faster under MyISAM, but updates will be faster under innodb-especially when concurrency is large.
So, which one do you use to check? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, the use of MyISAM in large-scale environments can be a great success, but it's not always the case. If you are planning to use a project with a large amount of data and require transactional or foreign key support, then you should really use the InnoDB method directly. But it is important to remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables may make you have a very bad experience.
Summary of differences:
1.InnoDB does not support indexes of type Fulltext.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.
4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.
The 5.LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.
In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "%aaa%"
Remap
Ways to improve InnoDB performance:
MyISAM and InnoDB Storage engine performance difference is not very large, for InnoDB, the main impact of performance is innodb_flush_log_at_trx_commit this option, if set to 1, then each time you insert data is automatically submitted, resulting in a sharp decline in performance, should be related to the refresh log, set to 0 efficiency can see a significant increase, of course, you can also submit "Set autocommit = 0" in SQL to set up to achieve good performance. In addition, I have heard that setting up Innodb_buffer_pool_size can improve the performance of InnoDB, but I have found no particularly significant improvement in my test.
Basically we can consider using InnoDB to replace our MyISAM engine, because InnoDB itself a lot of good features, such as transaction support, stored procedures, views, row-level locking and so on, in the case of a lot of concurrency, I believe InnoDB performance is certainly much stronger than MyISAM, of course , the corresponding configuration in the MY.CNF is also more critical, good configuration, can effectively accelerate your application.
Any kind of table is not omnipotent, only appropriate for the business type to select the appropriate table type, in order to maximize the performance advantage of MySQL
Types and differences of MySQL storage engines (InnoDB and MyISAM)
