• notice
  • Congratulations on the launch of the Sought Tech site

Talking about the Differences and Optimization of Myisam and InnoDB

Basic differences between MyISAM and InnoDB

1.InnoDB does not support indexes of type FULLTEXT.

2.InnoDB does not save the specific number of rows in the table, that is to say, when executing select count(*) from table, InnoDB needs to scan the entire table to calculate how many rows there are, but MyISAM simply reads and saves it number of rows.Note that when the count(*) statement contains a where condition, the operations for both tables are the same.

3.For fields of type AUTO_INCREMENT, InnoDB must contain an index of only this field, but in MyISAM tables, a joint index can be established with other fields.

4.When DELETE FROM table, InnoDB will not recreate the table, but delete row by row.MyISAM is the table where innodb is the row lock

5.LOAD TABLE FROM MASTER (from the main load table) operation does not work for InnoDB.The solution is to first change the InnoDB table to a MyISAM table, import the data and then change it to an InnoDB table, but for the extra InnoDB features (such as foreign keys) for tables do not apply.

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

6.InnoDB supports things

Select the storage engine according to the actual situation.

In general, it is recommended to use myIsam if there are many queries.

If you need transactions or foreign keys, then InnoDB might be the way to go.

MyISAM and InnoDB optimizations:

key_buffer_size- This is very important for MyISAM tables.If you're just using MyISAM tables, you can set it to 30-40% of available memory.Reasonable values ​​depend on index size, data volume, and load- - keep in mind that MyISAM tables use the operating system's cache to cache data, so some memory needs to be set aside for them, and in many cases the data is larger than the index.Nonetheless, always check that all key_buffers are being used- -.MYI files are only 1GB and the key_buffer is set to 4GB is very rare.It's such a waste.If you rarely use MyISAM tables, also keep key_buffer_size below 16-32MB to accommodate temporary table indexes given to disk.

innodb_buffer_pool_size- This is very important for Innodb tables.Innodb is more sensitive to buffering than MyISAM tables.MyISAM works fine with the default key_buffer_size setting, but Innodb is snail-like with the default innodb_buffer_pool_size setting.Since Innodb caches data and indexes, there is no need to leave too much memory for the operating system, so if you only need to use Innodb, you can set it up to 70-80% of the available memory.Some of the rules that apply to key_buffer are- if your data volume is small and it won't explode, then you don't need to set innodb_additional_pool_size- this option doesn't have much impact on performance, at least on operating systems that have about enough memory to allocate It is so.But if you still want to set it to 20MB (or more), so you need to look at how much other memory Innodb needs to allocate.

       innodb_log_file_size is important in case of high write load especially with large data sets.The higher the value, the higher the performance, but be aware that the recovery time may be increased.I usually set it to 64-512MB, depending on server size.

      The default setting of innodb_log_buffer_size is acceptable for moderate write loads and short transactions.If there is a peak update operation or if the load is heavy, you should consider increasing its value.If its value is set too high, memory may be wasted- - it refreshes every second, so there is no need to set more than 1 second of required memory space.Usually 8-16MB is enough.The smaller the system, the smaller its value.

      innodb_flush_logs_at_trx_commit Is Innodb 1000 times slower than MyISAM and a big head? It seems that maybe you forgot to modify this parameter.The default value is 1, which means that every committed update transaction (or every statement outside of a transaction) is flushed to disk, which can be quite resource-intensive, especially without a battery-backed cache.Many applications, especially those converted from MyISAM, set it to a value of 2, which means that the log is not flushed to disk, but only to the operating system's cache.The log is still flushed to disk every second, so the consumption of 1-2 updates per second is usually not lost.Setting it to 0 is much faster, but also relatively unsafe- - some transactions are lost when the MySQL server crashes.A setting of 2 instructs to miss the portion of the transaction flushed to the OS cache.

      table_cache- - Opening a table can be expensive.For example, MyISAM puts the MYI header to indicate that the table is in use.You definitely don't want to do this too often, so you usually want to increase the number of caches enough to maximize the cache of open tables.It needs to use the resources and memory of the operating system, which is certainly 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, then increase its value.I've seen it set to 100,000.

      thread_cache- - Thread creation and destruction can be expensive as each thread's connect/disconnect is required.I usually set at least 16.If the application has a lot of hopping concurrent connections and the value of Threads_Created is relatively large, then I will increase its value.Its purpose is to eliminate the need to create new threads during normal operations.

      query_cache- - useful if your application is read-heavy and doesn't have application-level caching.Don't make it too big, because maintaining it also requires a lot of overhead, which can make MySQL slow.Typically set to 32-512Mb.After setting it up, it is best to track it for a while to see if it works well.Under certain load pressure, if the cache hit rate is too low, it is enabled.

      sort_buffer_size- - if you only have some simple queries, then there is no need to increase its value, even though you have 64GB of memory.If not, it may degrade performance

How to optimize MYSQL database:

1, select the most applicable field attribute, reduce the length of the defined field as much as possible, and try to set the field to NOT NULL, such as 'province, gender', preferably set to ENUM

2, use join instead of subquery

3, use union (UNION) instead of manually created temporary table

4, transaction processing (guarantee data integrity, such as adding and modifying at the same time, both are executed if both are established, and either fails)

5.Properly build an index (how to build an index? The pros and cons of indexing?)

6, optimize the sql statement

7, explain can see mysql execution plan

8, sub-table (vertical sub-table, horizontal sub-table?)

The above is the whole content of this article, I hope you like it.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+