MySQL optimization: how to find the cause of low SQL Efficiency

After a low-efficiency SQL statement is queried, you can use the EXPLAIN or DESC command to obtain information about how MySQL executes the SELECT statement, including how the table connects to and connects to the table during the execution of the SELECT statement, for example, to calculate the sales volume of all companies in 2006, we need to associate the sales table with the company table, and perform sum operations on the profit field. The execution plan of the corresponding SQL statement is as follows:
Mysql> explain select sum (profit) from sales a, company B where a. company_id = B. id and a. year = 2006 \ G;
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table:
Type: ALL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 12
Extra: Using where
* *************************** 2. row ***************************
Id: 1
Select_type: SIMPLE
Table: B
Type: ALL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 12
Extra: Using where
2 rows in set (0.00 sec)

Each column is interpreted as follows:

• Select_type: SELECT type. common values include SIMPLE (SIMPLE table, that is, table join or subquery is not used) and PRIMARY (PRIMARY query, that is, outer query), UNION (the second or subsequent query statement in UNION), SUBQUERY (the first select in the subquery), and so on.

• Table: The table of the output result set.

• Type: indicates the connection type of the table. The connection type with good performance to poor performance is system (only one row in the table, that is, the constant table) and const (a single table can have at most one matching row, for example, primary key or unique index) and eq_ref (for each row above, only one record is queried in this table. Simply put, the primary key or unique index is used in multi-table join), ref (similar to eq_ref, the difference is that instead of using primary key or unique index, but using a normal index), ref_or_null (similar to ref, the difference is that the condition contains a query for NULL) index_merge (index merge optimization), unique_subquery (in is followed by a subquery for the primary key field), index_subquery (similar to unique_subquery, the difference is that In is followed by a subquery that queries non-unique index fields), range (range query in a single table), and index (for each row above, data is obtained through the query index) and all (for each row above, data is obtained through full table scan ).

• Possible_keys: indicates the index that may be used during query.
• Key: indicates the actually used index.
• Key_len: the length of the index field.
• Rows: number of rows scanned.
• Extra: Description and description of execution.

In the preceding example, it can be confirmed that the full table scan of Table a results in unsatisfactory efficiency. Create an index for the year field of Table a as follows:

Mysql> create index idx_sales_year on sales (year );
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
After an index is created, the execution plan of this statement is as follows:
Mysql> explain select sum (profit) from sales a, company B where a. company_id = B. id and a. year = 2006 \ G;
* *************************** 1. row ***************************
Id: 1
Select_type: SIMPLE
Table:
Type: ref
Possible_keys: idx_sales_year
Key: idx_sales_year
Key_len: 4
Ref: const
Rows: 3
Extra:
* *************************** 2. row ***************************
Id: 1
Select_type: SIMPLE
Table: B
Type: ALL
Possible_keys: NULL
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 12
Extra: Using where
2 rows in set (0.00 sec)

We can find that the number of rows to be scanned in Table a is significantly reduced after the index is created (from full table scan to three rows). It can be seen that the index can greatly improve the database access speed, this advantage is even more obvious when tables are huge. Using index optimization SQL is a common basic method for optimizing problematic SQL, in the subsequent sections, we will introduce how to optimize the SQL statements by using indexes.