Relearning Mysql Database 5: analysis and Optimization according to MySQL Index principle

The official account of Wechat [Java technology jianghu] is a small technical station of an Ali Java engineer. Author Huang Xiaoxie, focusing on Java-related technologies: SSM, SpringBoot, MySQL, distributed, middleware, cluster, Linux, network, multithreading, occasionally talking about Docker, ELK, but also sharing technical information and learning experience, committed to Java full-stack development!

One: Mysql principle and slow query

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.

Thinking caused by a slow query selectcount (*) fromtaskwherestatus=2 and operator_id=20839 and operate_time > 1371169729 and operate_time,