Why optimize

In the process of application development, due to the small amount of data in the initial stage, we would pay more attention to the realization of functions when writing SQL statements. However, after the application system was officially launched, with the rapid growth of production data, many SQL statements began to show performance problems, and the impact on production became more and more serious. In this case, these problematic SQL statements become the bottleneck of the entire system performance and affect the user experience. Therefore, WE need to optimize THE SQL

How to troubleshoot and locate

1. Use slow query logs to locate SQL statements whose execution efficiency is low. Slow query logs are recorded after the query is complete.

2. Analyze execution plans for inefficient SQL through EXPLAIN

EXPLAIN SELECT * FROM film
Copy the code

The query results are as follows:The meanings of the parameters are as follows:

  • Select_type: Indicates the type of SELECT. Common values include SIMPLE (SIMPLE table, that is, no table join or SUBQUERY is used),PRIMARY (PRIMARY query, that is, the outer query), UNION (the second or subsequent query statement in the UNION), and SUBQUERY (the first SELECT in the SUBQUERY).
  • Type: indicates how MySQL finds the desired rows in the table, from left to right, with the worst performance to the best performance

Type =ALL, full table scan; Ype =index, index full scan; Type =range, index range scan, common in <, <=, >, >=, between operators; Type =ref, scan with a prefix that is not unique index or unique index

  • Key: indicates the actual index.
  • Key_len: the length used in the index field.
  • “Rows” : indicates the number of scanned rows.

Optimize the instance

 explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id= b.customer_id and email = 'test';
Copy the code

The email field is not indexed, triggering a full table scan. The number of rows to be scanned is 599.

create index idx_email on customer(email);
Copy the code

The number of rows scanned by the query is reduced to 1 row, and for large tables, it is necessary to create indexes on the corresponding fields.