MySQL performance optimization

1 selection of storage engines

InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides advanced database functionality such as transactional support and external keys.

1.1 InnoDB and the MyISAM the Difference

1. InnoDB does not support indexes of type Fulltext.

2. The exact number of rows in a table is not saved in 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.

5. The 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, if MySQL cannot determine the scope 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%"

1.2 InnoDB and the MyISAM The Choice

　　The main difference between the two types is Innodb transactional and foreign key and row level locks are supported. and MyISAM does not support it. So MyISAM tend to be considered only suitable for use in small projects.

As a user of MySQL, InnoDB and MyISAM are more like, if the database platform to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely the first choice.

The reasons are as follows:

1, most of the projects on the platform are read and write less projects, and MyISAM reading performance is stronger than InnoDB.

2, MyISAM index and data are separate, and the index is compressed, the memory usage of the corresponding improved a lot. Can load more indexes, and InnoDB is the index and the data is tightly bound, do not use compression which will cause innodb than MyISAM volume is large.

3, often 1, 2 months will occur application developers accidentally update a table where the scope of the wrong, resulting in this table can not be normal use, this time the superiority of the MyISAM is reflected, casually from the day of the copy of the compressed package out of the corresponding table file, casually put into a database directory, Then dump into SQL and then back to the main library, and the corresponding binlog complement. If it's InnoDB, I'm afraid it can't be so fast, don't tell me to let InnoDB regularly back up with an export xxx.sql mechanism, because the minimum amount of data for a database instance is roughly dozens of g in size.

4, from the application logic of contact, select COUNT (*) and order BY is the most frequent, probably can account for the entire SQL total statement of more than 60% of the operation, and this operation InnoDB actually will lock the table, many people think InnoDB is a row-level lock, That's just where the primary key is valid, and the non-primary key will lock the full table.

5, there is often a lot of application departments need me to give them regular data on some tables, MyISAM words are very convenient, as long as they correspond to the list of the frm. myd,myi files, let them in the corresponding version of the database to start the line, and InnoDB need to export xxx.sql, because the light to other people's files, by the dictionary data file, the other side is not available.

6, if and myisam than insert write operation, InnoDB also not up to MyISAM write performance, if is for index-based update operation, although MyISAM may be inferior innodb, but so high concurrency of write, from the library can chase is also a problem, It might as well be solved by a multi-instance sub-Library table architecture.

7, if it is used MyISAM, the merge engine can greatly speed up the development of the application department, they just do some select count (*) operation on this merge table, it is very suitable for a large project total of about hundreds of millions of rows of a type (such as log, survey statistics) business table.

Of course InnoDB is not absolutely not used, the project with the business is used InnoDB. In addition, someone might say that you myisam not be able to write too much, but you can make up for it through architecture.

2 parameter Configuration

2.1 Connection

Connections typically come from a Web server, and the following lists some of the connection-related parameters and how to set them up.

1, Max_connections

This is the maximum number of connections allowed by the Web server, remembering that each connection uses session memory.

2, max_packet_allowed

The maximum packet size, usually equal to the size of the largest data set you need to return in a chunk, if you are using remote mysqldump, then its value needs to be larger.

3, Aborted_connects

Check the system Status counter to determine that it has not grown if the number of increases indicates that the client has encountered an error while connecting.

4, Thread_cache_size

An inbound connection creates a new thread in MySQL because it's cheap and fast to open and close connections in MySQL, and it doesn't have as many persistent connections as other databases, such as Oracle, but thread pre-creation doesn't save time, which is why MySQL threads are cached.

If you are growing, pay close attention to the threads that are created, and let your thread cache larger, and for 2550 or 100 of thread_cache_size, memory consumption is not much.

2.2 Query Cache

The MySQL query cache can skip the phases of SQL parsing optimization queries and return the cached results directly to the user. The cache exists in a hash table, by querying SQL, querying the database, the client protocol, and so on as key. Before deciding whether to hit, MySQL will not parse SQL, but directly use SQL to query the cache, any character on the SQL, such as spaces, comments, will cause cache misses.

If there are indeterminate data in the query, such as the Current_date () and now () functions, then the query is not cached after it is complete. Therefore, queries that contain indeterminate data will certainly not find the available caches.

Work flow

1. server receives SQL, with SQL and some other criteria for key Lookup cache table (additional performance consumption)

2. If the cache is found, return the cache directly (performance boost)

3. If the cache is not found, execute the SQL query, including the original SQL parsing, optimization, and so on.

4. After executing SQL query results, put SQL query results into the Cache table (additional performance consumption)

Cache invalidation

When a table is writing data, the cache of the table (hit check, cache write, and so on) will be in the failed state. In InnoDB, if a transaction modifies a table, the table's cache is invalidated before the transaction commits, and the related query for that table cannot be cached until the transaction commits.

Memory management of the cache

The cache creates a chunk of memory (query_cache_size) in memory to maintain the cached data, where approximately 40K of space is used to maintain cached metadata, such as spatial memory, mapping of data tables and query results, mapping of SQL and query results, and so on.

MySQL divides this large memory block into small chunks of memory (query_cache_min_res_unit), each of which stores its own type, size, and query result data, as well as pointers to front and back memory blocks.

MySQL needs to set the size of a single small storage block, and when the SQL query starts (not getting the results) to request a piece of space, so even if your cache data does not reach this size, you also need to use this size of data block to save (this is the same as the Linux file system block). If the result exceeds the size of this block of memory, then you need to request a memory block. When the query finishes discovering that the requested memory block is redundant, the excess space is freed, which can cause memory fragmentation issues.

Cache configuration Parameters

1. Query_cache_type: Whether to open the cache

1) off: Off

2) on: Always Open

3) DEMAND: Only queries that explicitly write Sql_cache will inhale the cache

2. Query_cache_size: The total amount of memory space used by the cache, in bytes, this value must be an integer multiple of 1024, or the actual MySQL allocation may be different from this value (feel this should be related to the file system Blcok size)

3. Query_cache_min_res_unit: Minimum unit size when allocating memory blocks

4. Query_cache_limit:mysql can cache the maximum results, if exceeded, increase the value of qcache_not_cached, and delete the query results

5. Query_cache_wlock_invalidate: If a data table is locked, the data is still returned from the cache, the default is off, which means that you can still return

2.3 Temp Table

The memory speed is pretty fast, so we want all the sort operations to be done in memory, and we can adjust the query to make the result set smaller to sort the memory, or set the variable to a larger size.

Tmp_table_size

Max_heap_table_size

Whenever you create a temporary table in MySQL, it uses the minimum value of both variables as the threshold value, and in addition to building temporary tables on disk, many sessions are created that preempt restricted resources, so it's best to tune the query instead of setting these parameters higher, and Note that tables with a BLOB or text field type are written directly to disk.

2.4 Session Memory

Each session in MySQL has its own memory, which is the memory allocated to the SQL query, so you want to make it as large as possible to meet your needs. But you have to balance the number of consistent sessions in the database at the same time. The black art here is that MySQL is cached on demand, so you can't just add them and multiply the number of sessions, which is a much larger estimate than MySQL typically uses.

The best practice is to start MySQL, connect all the sessions, and then continue to focus on the Virt column of the top-level session, the number of mysqld rows is usually relatively stable, this is the actual total memory usage, minus all the static MySQL memory area, get the actual all session memory, Then divide by the number of sessions to get an average.

1, Read_buffer_size

Cache a block of contiguous scans, which is a cross-storage engine, not just a myisam table.

2, Sort_buffer_size

The size of the sort buffer is performed, preferably set to 1m-2m, and then set in the session, setting a higher value for a particular query.

3, Join_buffer_size

Execute the buffer size allocated by the federated query, set it to 1m-2m size, and then set it individually on demand in each session.

4, Read_rnd_buffer_size

For sorting and order by operations, it is best to set it to 1M and then set it as a session variable to a larger value in the session.

2.5 Slow Query Log

A slow query log is a useful feature of MySQL.

1, Log_slow_queries

MySQL parameter in the log_slow_queries parameter set it in the My.cnf file, set it to on, by default, MySQL will put the file into the data directory, the file is named "Hostname-slow.log", But you can also specify a name for this option when you set it.

2, Long_query_time

The default value is 10 seconds, you can set it dynamically, the value from 1 to set it to ON, if the database is started, by default, the log will be closed. As of 5.1.21 and the version of Google patch installed, this option can be set in microseconds, which is an amazing feature, because once you have eliminated all queries that have been queried for more than 1 seconds, the adjustment is very successful, which helps you to eliminate the problem SQL before the problem gets bigger.

3, Log_queries_not_using_indexes

It's a good idea to turn on this option, which really records the query that returns all rows.

2.6 Summary

We introduced the MySQL parameters of the five major categories of settings, usually we rarely touch them, in the MySQL performance tuning and troubleshooting when these parameters are very useful.

Cached queries in MySQL include two resolution query plans, as well as the returned datasets, which will invalidate items in the query cache if the underlying table data or structure changes.

1, Query_cache_min_res_unit

MySQL parameters in the query_cache_min_res_unit query cache block is allocated at this size, using the following formula to calculate the average size of the query cache, based on the calculation results set this variable, MySQL will be more efficient use of query cache, cache more queries, Reduce the waste of memory.

2, Query_cache_size

This parameter sets the total size of the query cache.

3, Query_cache_limit

This parameter tells MySQL to drop a query larger than this size, generally large queries are relatively rare, such as running a batch to perform a large report statistics, so those large result sets should not be filled with the query cache.

3 Query Statement Optimization  

Explain is used to show the execution of the current SQL statement, including the call index, so we can add the required index to the table based on this statement.

Explain the most important thing when displaying the content is the Keys field, which indicates that the query uses that index, and if it does not use the index, it displays NULL, which is when we add the appropriate index as required, and if we use the index, we display the index.

Here are just two explain listed three important parameters:

Type table connection types are in turn from best to worst

System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All

in general, the query must be guaranteed to reach at least Range level, it is best to reach ref .

Const This type is used in a table with a record that matches at most one row, and it is read at the start of the query. and used the primary or the unique time. (That is, if you use a primary or a unique index and the matching record has only one, the type used is the const Here is an example of the Ecs_goods table in Ecshop

Explain SELECT * from ecs_goods where goods_id = 10 The type of this case is const because where goods_id is the primary key primary key

Explain SELECT * from Ecs_goods where goods_id < 10 This is not a const.

Explain SELECT * from ecs_goods where add_time = 123445324 This type of case is also const, because Add_time is a unique index

The eq_ref applies to SQL statements with table joins, and only one statement is read from the table, and the index used must be primary_key or unique

Ref all matching records in the table will be read, and the index used cannot be primary or unique, which is the most common and common type

All this is very bad because there is no index in the table, so you have to do a full scan of the table

Possible_keys refers to which index is used in the pit when the SQL statement searches the table, but may not be used. If the argument is empty, it means that no index is used. In this case, you can check that those fields in the WHERE clause are suitable for Gazzo to improve query performance.

The key represents the index that is actually used in the query, and if no index is currently used, the parameter is null, as in this case, look for those fields in the WHERE clause to be indexed to improve query performance.

Good indexes are important, good indexes let queries have good access types and only check the rows that are needed. But adding an index does not mean that MySQL will access and return the same rows.

Extra contains additional information that is not appropriate for display in other columns but is important. If it is using temporary or using Filesort, then there is a need for improvement.

MySQL performance optimization