Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to optimize mysql". In daily operation, I believe many people have doubts about how to optimize mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to optimize mysql". Next, please follow the editor to study!

Question 10 million table how to optimize?

In order to solve this problem, first of all, we need to consider whether the number of records in the table will increase and what the increment is. Here are three main aspects of optimization in this interview.

1. Index optimization here index optimization mainly refers to whether the table has more queries or more new modifications, which will affect the creation of indexes. For example, if you add or update frequently, you cannot create too many indexes, which will affect the efficiency of inserts and updates. You only need to create indexes for key fields.

2. Partition when the number of tables reaches a certain number, in order to maintain query efficiency, it is necessary to regularly clean up the data in the table, or through partitioning to improve query efficiency. First of all, let's talk about cleaning data. Delete must be very slow. Here, you can improve the cleaning speed through drop partitions. When querying, you can query specified partition data through partition fields to improve query efficiency.

3. The number of records of the sub-table is very large and will grow. Here, the sub-table can be divided according to the situation. The sub-table is similar to the partition principle, but when you encounter a huge amount of data and the increment is huge, you can consider dividing the table according to the main business field. At the same time, the table is partitioned according to the date. Here, for example, to store the business log data of each province, it must be necessary to choose provincial points when querying, and the total amount of query is not very meaningful. This category can be considered according to provincial tables, and then each table is partitioned according to date.

5. Sub-repository: generally, data security is considered to be deployed in different regions, or services are required to split different businesses and deploy them separately. This involves the problem of cross-database query. If it is service-oriented, it can be standardized and interacted through the rest interface.

How do you usually optimize for slow queries

1. Check the execution plan and increase the index to improve efficiency

2. Sql keyword optimization

Using not exists instead of not in,not in does not use indexes

Query conditions avoid using leading fuzzy queries, such as'% xxx', because indexes cannot be used

If the query condition uses or, make sure that the columns on both sides of the or must have an index, otherwise the index will fail.

When the string field is a number, put single quotation marks in the where condition, otherwise the index will fail (because MySQL will convert the string type in the table to a number and then compare it, resulting in index invalidation)

ISNULL judges not to move the index, so use it cautiously.

The page number of the LIMIT page should not be too large. It will query all the results and discard the unwanted ones. Use the primary key to make a join query

Replace in or exists with temporary tables

At this point, the study on "how to optimize mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.