MySQL slow query optimization best practices (i)

Slow query

We know that the general application system, MySQL read and write ratio of about 10:1, and the general insert and update operations rarely appear performance problems, encountered the most problems, but also the most prone to problems, or some complex query operations, so the optimization of query statements has become the development, operations and maintenance engineers must is a major priority in most operations.

Indexing principle

The purpose of the database indexing is to improve the efficiency of the query, the index as in the life of the dictionary, train timetable, book directory, the principle is the same, all by shrinking the desired data range to filter out the final desired results, the original random query into an orderly event, such as we look in the dictionary " MySQL "word, if not indexed, you have to turn from head to tail, think is not very scary! And we will first select the beginning of the query m and then check the Y-letter range of words, the last to navigate to MySQL.

The deep-seated principle of indexing involves disk IO and pre-reading, indexed data structures B + trees, B + Tree properties and search processes, and so on, so long as there is a perceptual understanding, it does not need to be understood very thoroughly and deeply.

Several principles of index building

1) leftmost prefix matching principle: MySQL will always match right until it encounters a range query (>, <, between, like) to stop the match, such as a = 1 and B = 2 and C > 3 and D = 4 If the index of the established (A,B,C,D) order, D is not indexed, if the establishment (A,B,D,C) of the index can be used, the order of a,b,d can be arbitrarily adjusted;

2) The number of indexes is not the more the better, the index also takes up space, and no increase in data to maintain the index, as far as possible to expand the index, do not create new index;

3) Indexes on large tables with unique values, the formula for unique values is count (distinct col)/count (*), which indicates the scale of the field is not duplicated, the larger the scale, the less the number of records we scan, such as SELECT COUNT (distinct user) from Mysql.user;

Query optimization Artifact--explain

Explain simply means being able to simulate the query process, analyze the indexed columns used and possible to use, and the rows of the final query, by reducing the core indicator rows to the result of optimizing the MySQL query statement, and of course remember to Sql_no_cache.

This article comes from the "change from every day" blog, so be sure to keep this source http://lilongzi.blog.51cto.com/5519072/1828781

MySQL slow query optimization best practices (i)