Ways to modify the MySQL data Engine-improve database performance

The classmate Sue me to say that he in order to make the data query become faster, modify the data engine, so query, summed up a bit.

After logging in to MySQL, look at the engine supported by the current database and the default database engine, using the following command:

Mysql>show engines;

+--------------------+---------+------------------------------------------------------------------------------- ------------------------+--------------+------+-------------------+| Engine | Support | Comment | Transactions | XA | Savepoints (save point) |+--------------------+---------+-------------------------------------------------------------- -----------------------------------------+--------------+------+-------------------+| federated | NO | Federated MySQL Storage engine Federated MySQL Storage engines | NULL | NULL | NULL | | Mrg_myisam | YES | Collection of identical MyISAM tables same MyISAM table collection | NO | NO | NO | | MyISAM | YES |                                 MyISAM Storage Engine MyISAM storage engines                  | NO | NO | NO | | Blackhole | YES | /dev/null Storage Engine (anything you write to it disappears) empty storage engines (any content written to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine CSV Storage engines | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables hash-based, stored in RAM for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive Storage Engine Archive Storage engines | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, Row-level locking, and foreign keys support transactions, row-level locking, and foreign keys. | YES | YES | YES | | Performance_schema | YES |                                          Performance Schema Performance mode                | NO | NO | NO |+--------------------+---------+------------------------------------------------------------------------- --------------------------------+--------------+------+-----------------+9 rows in Set

Modify command: (modified for a single table)

ALTER TABLE user ENGINE =myisam;

Modified, okay?

Enquiry Information:

1. What are the differences between MyISAM InnoDB in MySQL?

MyISAM

InnoDB

The difference in composition:

Each myisam is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type.

The. frm file stores the table definition.

The data file has an extension of. MYD (MYData).

The extension of the index file is. MYI (Myindex).

A disk-based resource is a InnoDB tablespace data file and its log file, and the size of the InnoDB table is limited only by the size of the operating system file, typically 2GB
transaction Processing Aspects :

Tables of the MyISAM type emphasize performance, which is performed more quickly than the InnoDB type, but does not provide transactional support

InnoDB provides transaction support transactions, external keys and other advanced database functions

SELECT Update,insert , Delete Operation
If executing a lot of select,myisam is a better choice

1. If your data performs a large number of inserts or UPDATE, for performance reasons, you should use the InnoDB table

2.DELETE from table , InnoDB does not reestablish the table, but deletes one row at a time.

3.LOAD table from MASTER operation does not work for InnoDB, the solution is to first change the InnoDB table to MyISAM table, import data and then change to InnoDB table, However, for tables that use an additional InnoDB attribute (such as a foreign key), an internal processing of one auto_incremen column per table is not used for auto_increment operations .

(auto_increment value can be reset by ALTER TABLE or MYISAMCH)

(For fields 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, better and faster auto_increment processing;)

(If you specify a auto_increment column for a table, the InnoDB table handle in the data dictionary contains a counter called the autogrow counter, which is used to assign a new value to the column.) )

4. InnoDB support transactions, MyISAM is not supported, for INNODB each SQL language is encapsulated as a transaction by default, auto-commit, which affects speed, so it is better to put multiple SQL language between Begin and commit, compose a transaction;

5. InnoDB supports foreign keys, while MyISAM is not supported. The conversion of a InnoDB table containing a foreign key to MyISAM will fail;

6. InnoDB is a clustered index, the data file is tied to the index, must have a primary key, through the primary key index efficiency is very high. However, the secondary index requires two queries, first querying the primary key, and then querying the data through the primary key. Therefore, the primary key should not be too large, because the primary key is too large and the other indexes will be large. While MyISAM is a nonclustered index, the data file is detached and the index holds a pointer to the data file. The primary key index and the secondary index are independent.

7. InnoDB does not save the exact number of rows in the table, and a full table scan is required to execute SELECT COUNT (*) from table. and MyISAM with a variable to save the entire table row number, the execution of the above statement only need to read out the variable, fast;

8. InnoDB does not support full-text indexing, while MyISAM supports full-text indexing, and the query efficiency is MyISAM high;

How to choose:

1. Whether to support transactions, if you want to choose InnoDB, if you do not need to consider myisam;

2. If the majority of the table is just read query, you can consider MyISAM, if both read and write are very frequent, please use InnoDB.

3. After the system MyISAM, it is more difficult to recover, can accept;

4. MySQL5.5 version start InnoDB has become the default engine of MySQL (previously MyISAM), stating that its advantages are obvious, if you do not know what to use, then use InnoDB, at least not bad.

(Reference: https://www.zhihu.com/question/20596402/answer/211492971)

Ways to modify the MySQL data Engine-improve database performance