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

I do not know if you have any understanding of similar articles on the principles of MySQL indexing and the basic steps of optimization. Today, I am here to give you a brief introduction. If you are interested, let's take a look at the text. I believe you will gain something after reading what is the principle of MySQL index and the basic steps of optimization.

Preface

This article is written by a big shot of Meituan, it's not bad to take it out and share it with you, the sql statement embedded in html in the code is written by the java framework, and you can understand the statement to be executed by its sql.

Background

With its excellent performance, low cost and rich resources, MySQL has become the first choice of relational database for most Internet companies. Although the performance is excellent, the so-called "good horse and saddle", how to make better use of it, has become a required course for development engineers. We often see requirements such as "proficient in MySQL", "SQL statement optimization", "understanding database principles" and so on from the job description. We know that in general application systems, the read-write ratio is about 10:1, and insert operations and general update operations rarely have performance problems, and those that encounter the most and are most prone to problems are some complex query operations. so the optimization of query statements is obviously the top priority.

Since July 13, I have been working in Meituan's core business system department to optimize slow queries, totaling more than ten systems, and accumulating hundreds of slow query cases. As the complexity of the business increases, the problems encountered are bizarre, varied and unimaginable. The purpose of this paper is to explain the principle of database index and how to optimize slow query from the point of view of development engineer.

Select count (*) from task where status=2 and operator_id=20839 and operate_time > 1371169729 and operate_time, 3 and d = 4 if you build an index in the order of (arecalbrech), d does not need an index, but if you build an index of (aformab), it can all be used, and the order of aformab can be adjusted at will. = and in can be out of order, for example, a = 1 and b = 2 and c = 3 indexes can be built in any order, and mysql's query optimizer will help you optimize it into a form that the index can recognize. Try to select a highly differentiated column as the index. The formula for distinguishing degree is count (distinct col) / count (*), indicating the proportion of non-repetitive fields. The larger the proportion, the less the number of records we scan, and the differentiation degree of the only key is 1. While some status and gender fields may be 0 in front of big data, then some people may ask, is there any empirical value for this ratio? It is difficult to determine this value for different scenarios. Generally, we need more than 0.1 for the fields that require join, that is, an average of 10 records are scanned. Index columns can not participate in the calculation, keep the column "clean", for example, from_unixtime (create_time) = '2014-05-29' can not use the index, the reason is very simple, the b + tree is stored in the data table field values, but for retrieval, all elements need to be compared with the application function, obviously the cost is too high. So the statement should be written as create_time = unix_timestamp ('2014-05-29'). Expand the index as much as possible, do not create a new index. For example, if you already have an index of an in the table, and now you want to add the index of (a), you only need to modify the original index. Go back to the initial slow query

According to the leftmost matching principle, the index of the initial sql statement should be the joint index of status, operator_id, type and operate_time; the order of status, operator_id and type can be reversed, which is why I would say that all relevant queries for this table will be found and analyzed comprehensively; for example, the following query:

Select * from task where status = 0 and type = 12 limit 10 * select count (*) from task where status = 0

Then the index status,type,operator_id,operate_time is very correct, because it can cover all cases. This makes use of the leftmost matching principle of the index.

Query optimization artifact-explain command

I believe you are no stranger to the explain command. Please refer to the official website explain-output for specific usage and field meaning. It needs to be emphasized that rows is the core indicator, and most statements with small rows must be executed quickly (there are exceptions, which will be discussed below). So optimization statements are basically optimizing rows.

The basic steps of slow query optimization first run to see if it is really slow. Pay attention to setting the SQL_NO_CACHEwhere conditional single table lookup and locking the minimum return record table. This sentence means that the where of the query statement is applied to the table with the smallest number of records returned in the table. Start to query each field in a single table to see which field has the highest degree of differentiation explain to view the execution plan. Whether it is consistent with the expectation of 1 (starting with locking tables with fewer records) the sql statement in the form of order by limit gives priority to the sorted table to understand the business side's use of scenarios and indexes with reference to several major principles of indexing to observe the results, which is not in line with the expectation to continue to analyze several slow query cases from 0.

The following examples explain in detail how to analyze and optimize slow queries.

Complex sentence writing

In many cases, we write SQL only to achieve function, which is only the first step. Different sentence writing methods often have essential differences in efficiency, which requires us to have a very clear understanding of the execution plan and indexing principles of mysql. Please see the following sentence:

Select distinct cert.emp_id from cm_log cl inner join (select emp.id as emp_id Emp_cert.id as cert_id from employee emp left join emp_certificate emp_cert on emp.id = emp_cert.emp_id where emp.is_deleted=0) cert on (cl.ref_table='Employee' and cl.ref_oid= cert.emp_id) or (cl. Ref_table='EmpCertificate' and cl.ref_oid= cert.cert_id) where cl.last_upd_date > = '2013-11-07 15 and cl.last_upd_date='2013 03and cl.last_upd_date='2013-11-07 15 and cl.last_upd_date='2013 03and cl.last_upd_date='2013-11-07 15and cl.last_upd_date='2013 03and cl.last_upd_date='2013-11-07 15purl 03and cl.last_upd_date= 2875 and&llt / span > oei.node_right = 2875and oei.node_right

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.