Differences and optimizations between MyISAM and InnoDB _ MySQL

InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its own advantages and disadvantages, depending on the specific application. Let's discuss it in detail. Basic differences between MyISAM and InnoDB

1. InnoDB does not support FULLTEXT indexes.

2. innoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.

3. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

4. when deleting FROM table, InnoDB does not create a new table, but deletes a row. MyISAM is the table innodb is the row Lock

5. the load table from master (FROM the master load table) operation does not work for InnoDB. the solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then import the data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. if MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "" 2%"

6. InnoDB supports transactions

Select a storage engine based on the actual situation.

In general, myIsam is recommended for queries.

If you need transaction processing or foreign keys, InnoDB may be a good method.

Optimization of MyISAM and InnoDB:

Key_buffer_size-this is very important for the MyISAM table. If you only use the MyISAM table, you can set it to 30-40% of the available memory. A reasonable value depends on the index size, data volume, and load. Remember, the MyISAM table uses the operating system cache to cache data. Therefore, you need to leave some memory for them, in many cases, the data is much larger than the index. However, it is always necessary to check whether all key_buffer files are used -- the. MYI file only has 1 GB, while the key_buffer is set to 4 GB. This is a waste. If you seldom use the MyISAM table, keep the key_buffer_size smaller than 16-32 MB to meet the requirements for temporary table indexes on the disk.

Innodb_buffer_pool_size-this is very important for Innodb tables. Compared with MyISAM tables, Innodb is more sensitive to Buffering. MyISAM can run in the default key_buffer_size setting. However, Innodb is similar to snail bait in the default innodb_buffer_pool_size setting. Because Innodb caches data and indexes, there is no need to leave too much memory for the operating system. Therefore, if you only need Innodb, you can set it to up to 70-80% of available memory. Some rules apply to key_buffer: If your data volume is small and does not increase rapidly, innodb_additional_pool_size does not have much impact on performance, at least on an operating system with almost enough memory to allocate. However, if you still want to set it to 20 MB (or larger), you need to check the other Innodb memory to be allocated.

Innodb_log_file_size is very important for high write loads, especially for large datasets. The larger the value, the higher the performance, but note that the recovery time may increase. I often set it to 64-512 MB, which is different from the server size.

Innodb_log_buffer_size is set by default to enable server performance when the write load is moderate and the transaction is short. If there is a peak update operation or a large load, you should consider increasing the value. If its value is set too high, memory may be wasted-it will refresh once every second, so you do not need to set the memory space more than 1 second. Usually 8-16 MB is enough. The smaller the system, the smaller its value.

Is innodb_flush_logs_at_trx_commit 1000 times slower than MyISAM than Innodb? Maybe you forgot to modify this parameter. The default value is 1, which means that each commit of the update transaction (or a statement other than each transaction) will be refreshed to the disk, which is quite resource-consuming, especially when there is no battery backup cache. Many applications, especially those transformed from MyISAM, set the value to 2, that is, do not refresh the log to the disk, instead, it is only refreshed to the operating system cache. Logs are still refreshed to the disk every second, so the consumption of 1-2 updates per second is usually not lost. If it is set to 0, it will be much faster, but it is relatively insecure-some transactions will be lost when the MySQL server crashes. Set to 2 to direct the part of the transaction that is lost and refreshed to the operating system cache.

Table_cache -- the overhead of opening a table may be high. For example, MyISAM marks the MYI file header that the table is in use. You certainly do not want this operation to be too frequent. Therefore, you usually need to increase the number of caches so that the opened tables can be cached to the maximum extent. It requires the resources and memory of the operating system, which is of course not a problem for the current hardware configuration. If you have more than 200 tables, it may be appropriate to set it to 1024 (each thread needs to open the table). If the number of connections is large, it will increase its value. I have seen a 100,000 error.

Thread_cache -- the overhead of thread creation and destruction may be high, because connection/disconnection of each thread is required. I usually set at least 16. If the application has a large number of skip concurrent connections and the value of Threads_Created is large, I will increase the value. It does not need to create a new thread in common operations.

Query_cache -- this is useful if your application has a large number of reads without application-level caches. Don't set it too large, because it also requires a lot of overhead to maintain it, which causes MySQL to slow down. Usually set to 32-512 Mb. After setting, it is best to track for a period of time to check whether the operation is good. If the cache hit rate is too low under a certain load, enable it.

Sort_buffer_size -- if you only have some simple queries, you don't need to increase its value, even though you have 64 GB memory. Performance may be reduced.

How to optimize the MYSQL database:

1. select the most suitable field attribute, minimize the length of the defined field, and set the field not null, for example, 'Province, gender'. it is best to set it to ENUM.

2. use join instead of subquery

3. use UNION instead of creating a temporary table manually

4. transaction processing (ensure data integrity. for example, if both are added and modified, both operations are performed, and both operations fail)

5. how to create an index? Advantages and disadvantages of indexes ?)

6. optimize SQL statements

7. the explain command shows the mysql execution plan.

8. table sharding (vertical table sharding, horizontal table sharding ?)

The above is all the content of this article. I hope you will like it.