On the difference and optimization of MyISAM and InnoDB _mysql

The basic difference between MyISAM and InnoDB

The fulltext type index is not supported for 1.InnoDB.

The number of rows in the table is not saved in 2.InnoDB, that is, when the select count (*) from table is executed, InnoDB scans the entire table to calculate the number of rows, but MyISAM simply reads out the saved rows. Note that when the COUNT (*) statement contains the Where condition, the operations of the two tables are the same.

3. For Auto_increment type fields, the InnoDB must contain only the index of the field, but in the MyISAM table, you can establish a federated index with the other fields.

4.DELETE from table, InnoDB does not re-establish the table, but deletes one row at a time. MyISAM is the table InnoDB is the row lock

5.LOAD table from master (the Master load table) 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, tables that use additional InnoDB attributes, such as foreign keys, do not apply.

In addition, row locks on innodb tables are not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the entire table, such as the Update table set num=1 where name like "2%"

6.InnoDB Support Things

Select the storage engine according to the actual situation.

In general, if the query is more recommended to use MyISAM.

If you need a transaction or a foreign key, then InnoDB may be a better way.

MyISAM and InnoDB optimization:

Key_buffer_size-This is very important for the MyISAM table. If you are using only the MyISAM table, you can set it to the 30-40% of available memory. A reasonable value depends on the size of the index, the amount of data, and the load--remember, the MyISAM table uses the operating system's cache to cache the data, so you need to set aside some of the memory for them, and in many cases the data peso is much more. However, it is always necessary to check whether all the Key_buffer are exploited--. Myi files are only 1GB, and Key_buffer is set to 4GB is very rare. It's too wasteful to do so. If you rarely use MyISAM tables, then keep key_buffer_size below 16-32MB to accommodate the temporary table indexes that are given to the disk.

Innodb_buffer_pool_size-This is very important for the InnoDB table. InnoDB is more sensitive to buffering than MyISAM tables. MyISAM can be run under the default Key_buffer_size settings, however InnoDB is like a snail in the default innodb_buffer_pool_size settings. Because InnoDB caches data and indexes without leaving the operating system with too much memory, you can set it up to 70-80% usable memory if you only need to use InnoDB. Some of the rules that apply to Key_buffer are--if your data is small and not exploding, you don't need to innodb_additional_pool_size--this option does not affect performance too much, at least on an operating system with almost enough memory to allocate. But if you still want to set it to 20MB (or more), you need to look at the amount of memory other InnoDB need to allocate.

Innodb_log_file_size is important in the case of high write loads, especially large data sets. The larger the value, the higher the performance, but note that recovery time may be increased. I often set it to 64-512MB, which varies according to the size of the server.

Innodb_log_buffer_size default settings for medium-intensity write load and shorter transactions, server performance is also available. If there is a peak update operation or a large load, you should consider increasing its value. If its value is set too high, it may waste memory-it refreshes every second, so there is no need to set the required memory space for more than 1 seconds. Usually the 8-16MB is enough. The smaller the system, the smaller its value.

Innodb_flush_logs_at_trx_commit is InnoDB 1000 times times slower than MyISAM and head is big? It seems that you have forgotten to revise this parameter. The default value is 1, which means that each submitted update transaction (or statements outside of each transaction) is flushed to disk, which is quite resource-intensive, especially when there is no battery-standby cache. Many applications, especially those from MyISAM, set the value to 2, which means that the log is not flushed to disk, but only to the operating system's cache. Logs are still flushed to disk per second, so there is usually no loss of 1-2 updates per second. If set to 0 is much faster, but also relatively insecure-the MySQL server crashes will lose some transactions. Set to 2 command to lose that part of the transaction that is flushed to the operating system cache.

Table_cache-the overhead of opening a table can be significant. For example MyISAM the Myi file header flag that the table is in use. You certainly don't want this to be too frequent, so it's usually a lot more cache, so it's enough to cache open tables. It needs the operating system's resources and memory, which is certainly not a problem for the current hardware configuration. If you have more than 200 tables, it might be appropriate to set it to 1024 (each thread needs to open the table), and if the number of connections is larger then increase its value. I have seen the case set to 100,000.

Thread_cache-The cost of creating and destroying threads can be significant because each thread needs to be connected/disconnected. I'm usually at least set to 16. If there is a large number of jumps and concurrent connections in the application and the value of threads_created is larger, then I will increase its value. It is designed to not create a new thread in the usual operation.

Query_cache-This is useful if your application has a large number of reads and no application-level caching. Don't set it too large, because it costs a lot to maintain it, which can cause MySQL to slow down. usually set to 32-512MB. After setting up, it's best to track for a while to see if it's working well. Under certain load pressure, if the cache hit rate is too low, enable it.

Sort_buffer_size-If you have only a few simple queries, you don't need to increase the value, although you have 64GB of memory. Well, maybe it'll degrade performance.

Ways to optimize the MySQL database:

1, select the most applicable field properties, as much as possible to reduce the definition of field length, as far as possible set the field not NULL, such as ' Province, sex ', preferably set to enum

2, use join instead of subquery

3, use Union (union) instead of manually created temporary tables

4, transaction processing (to ensure data integrity, such as add and modify at the same time, both are implemented, one failed)

5, indexing is appropriate (how to create an index?) The pros and cons of indexing? ）

6, Optimizing SQL statements

7,explain can see the MySQL execution plan

8, the table (vertical table, horizontal table?) ）

The above mentioned is the entire content of this article, I hope you can enjoy.