• notice
  • Congratulations on the launch of the Sought Tech site

How to find the reason for the low SQL efficiency in mysql optimization

After querying the inefficient SQL statement, you can use the EXPLAIN or DESC command to obtain information on how MySQL executes the SELECT statement, including how the tables are connected and the order of the connections during the execution of the SELECT statement.For example, we want to calculate all companies in 2006 You need to associate the sales table with the company table, and perform a sum operation on the profit field.The execution plan of the corresponding SQL 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: a
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)

The explanation of each column is as follows:

•select_type: Represents the type of SELECT.Common values ​​are SIMPLE (simple table, that is, no table join or subquery), PRIMARY (main query, that is, the outer query), UNION (the second in UNION) Or the following query statement), SUBQUERY (the first SELECT in the subquery), etc.

•table: The table of the output result set.

•type: Indicates the connection type of the table.The connection types with performance from good to poor are system (there is only one row in the table, that is, the constant table), const (there is at most one matching row in a single table, such as primary key or unique) index), eq_ref (for each row in front, only one record is queried in this table, in simple terms, it is the primary key or unique index used in the multi-table join), ref (similar to eq_ref, the difference is that the primary key or unique index, but use ordinary 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 (after in is a subquery to query the primary key field), index_subquery (similar to unique_subquery, the difference is that after in is a subquery that queries non-unique index fields), range (a range query in a single table), index (for each row in front, the data is obtained by querying the index), all (For each of the previous rows, the data is obtained through a full table scan).

•possible_keys: indicates the indexes that may be used when querying.
•key: indicates the index actually used.
•key_len: The length of the index field.
•rows: the number of scan rows.
•Extra: Explanation and description of the implementation.

In the above example, it can be confirmed that the efficiency of the full table scan of table a is not ideal, so create an index on 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 creating the index, 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: a
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)

    It can be found that the number of rows that need to be scanned for table a after indexing is significantly reduced (from a full table scan to 3 rows).It can be seen that the use of indexes can greatly improve the access speed of the database, especially in the table This advantage is more obvious when it is very large.Using index to optimize SQL is a common and basic method for optimizing SQL.In the following chapters, we will specifically introduce how to use indexes to optimize SQL.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+