MySQL performance optimization tips

MySQL Performance Optimization

MySQL is widely used in Internet companies, and people often have needs for MySQL performance optimization. I have compiled some practical tips for MySQL optimization.

Schema and data type optimization

1. Integers are usually the best choice for identity columns because they are fast and can use AUTO_INCREMENT
2. Completely "random" strings (such as those generated by MD5() , SHA1() , or UUID() ) are randomly distributed in a large space, causing INSERT and some SELECT statements to become very slow.
3. If you want the query to be executed quickly and with good concurrency, it is best not to do too many associated queries in a single query (Internet companies are very averse to associated queries), and use programs to complete the associated operations.
4. If you need to change the table structure of a relatively large table (add a column in the ALTER TABLE operation), it is recommended that you first copy a table with the same structure as the original table, then copy the data into it, and finally rename the new table to the name of the original table. Because when changing the table structure, it is very likely that the entire table will be locked and may be unavailable for a long time.
5. To avoid multi-table associations, you can consider some anti-paradigm table building solutions and add some redundant fields.

InnoDB Index Optimization

1. If you do not start searching from the leftmost column of the index, you cannot use the index.
2. All non-clustered indexes need to locate the corresponding primary key through the index first, and then find the data in the clustered index, so you must be careful when defining the primary key index.
3. MySQL can use the index to sort the results only if the order of the index columns is exactly the same as the order of ORDER BY clause and the sorting direction (reverse or forward) of all columns is the same. There is one case where ORDER BY clause does not need to satisfy the leftmost prefix requirement of the index, that is, when the leading column is a constant.
4. When using like to match the value of a string field, try to use prefix matching like 'XX%' and avoid using like '%XX'
5. Hash indexes are implemented based on hash tables. Only queries that exactly match all columns of the index are valid, and they do not follow the leftmost matching principle of the index.
6. When the server needs to perform a joint operation on multiple indexes (usually with multiple OR conditions), it is recommended to change to UNION mode, which makes it easier to hit the index.
7. There is a rule of thumb for how to choose the order of columns in the index: put the most selective columns first in the index.
8. Use covering indexes as much as possible (if an index contains or covers the values ​​of all the fields to be queried, we call it a covering index). You can see the "Using index" information in the Extra column of EXPLAIN
9. When ID is the primary key, creating index (A) is equivalent to creating two indexes (A) and (A, ID)
10. The more indexes a table has, the slower SELECT , UPDATE , and DELETE operations will be, and the more memory will be used.
11. InnoDB uses shared locks on secondary indexes, but accessing the primary key index requires an exclusive lock
12. Use WHERE IN and WHERE BETWEEN AND to perform range queries whenever possible.
13. The larger the LIMIT offset, the slower the performance.
14. When writing queries, avoid single-row searches, use the data's native order as much as possible to avoid additional sorting operations, and use index coverage queries as much as possible.

Query performance optimization

1. For inefficient queries, we usually analyze them from two aspects:

1. Determine whether the application is retrieving larger amounts of data than necessary. This usually means that too many rows were accessed, but sometimes it could be that too many columns were accessed.
2. Check if the MySQL server is parsing more rows than necessary
   

2. Generally, MySQL can use the following three methods to apply WHERE conditions, from best to worst:

• Use the WHERE condition in the index to filter out non-matching records
• Use an index covering scan to return records
• Return data from the data table and then filter out records that do not meet the conditions
  

3.MySQL is designed to make connections and disconnections lightweight and efficient in returning small query results. On a general-purpose server, it is possible to run more than 100,000 queries per second. A gigabit network card can easily handle more than 2,000 queries per second. MySQL can scan millions of rows of data in memory per second.

4. When deleting a large amount of data, it is recommended to pause for a while after deleting a small batch of data before deleting the next batch.

5. Sorting is a very costly operation anyway, so from a performance perspective, you should avoid sorting as much as possible or avoid sorting large amounts of data as much as possible.

6. The COUNT() function has two different functions: it can count the number of values ​​in a column or the number of rows. The simplest way is to count the number of rows through COUNT(*)

7. When doing a related query, make sure there is an index on the related field

8. If the amount of data is large and historical data needs to be deleted regularly, you can consider using a partition table

9. If the specified index column and partition column do not match, the query will fail to perform partition filtering

10. Avoid foreign key constraints as much as possible, usually implemented through programming, and keep foreign keys in mind

11. It is best not to use triggers, stored procedures, custom functions, etc.

12. Use query cache as much as possible. If there is some uncertain data (such as NOW() or CURRENT_DATE()) when writing query statements, it will not be cached.

13. Using multiple small tables instead of one large table is good for query caching

14. When writing in batches, only one cache invalidation is required, so it is more efficient than single writes (the cache is invalidated every time a write is made). For write-intensive applications, directly disable the query cache.

15. If the cache space is too large, the server may freeze during the expiration operation

The above is a summary of my personal experience at work. If there are any errors in the description, I hope you can help point them out so that we can communicate and learn together!

This is the end of this article about sharing MySQL performance optimization tips. For more relevant MySQL performance optimization content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!