Mysql optimization How to find the reason for the low efficiency of SQL _mysql

After querying for inefficient SQL statements, you can obtain information about how MySQL executes the SELECT statement through the EXPLAIN or DESC commands, including the order in which tables are connected and connected during the execution of the SELECT statement, for example, we want to calculate sales for all companies in 2006 and need Associates the sales table with the company table, and the Profit field is summed (sum), and the corresponding SQL execution plan 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 explanations for each column are as follows:

Select_type: Represents the type of select, the common values are simple (plain table, that is, no table joins or subqueries), PRIMARY (main query, that is, outer query), union (the second or subsequent query in union), Subque RY (the first SELECT in a subquery), and so on.

table: The table that outputs the result set.

type: Represents the type of connection for a table, the performance from good to bad connection type is system (only one row in the table, that is, the constant table), const (a single table with up to a matching row, such as primary key or unique index), Eq_ref (for each previous row, in the Only one record is queried in this table, in simple terms, the use of primary key or unique index in a multiple table connection, ref (similar to Eq_ref, the difference being to use a normal index instead of primary key or unique index) , Ref_or_null (similar to ref, except that the condition contains a query to null), Index_merge (index merge optimization), Unique_subquery (in followed by a subquery for the primary key field), Index_su Bquery (similar to Unique_subquery, the difference is in the query of a non-unique indexed field), Range (range query in one table), index (for each previous row, by querying the index to get the data), all (for each previous row, Data is obtained through a full table scan.

Possible_keys: An index that may be used when querying.
key: Represents the index that is actually used.
Key_len: The length of the indexed field.
rows: Number of scanned rows.
Extra: Description and description of the implementation.

In the example above, it is already possible to confirm that a full table scan of table A is not efficient, then create an index to the Year field of Table A, as follows:

Mysql> CREATE index Idx_sales_year on sales (year);
Query OK, rows affected (0.01 sec)
Records:12 duplicates:0 warnings:0
After the index is created, the execution plan for 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)

You can see that the number of rows that need to be scanned for table A is significantly reduced (from a full table scan to 3 rows) after an index is established. The use of visible indexes can greatly improve the access speed of the database, especially when the table is large, this advantage is more obvious, using index optimization SQL is a common basic method of optimizing problem SQL, In a later section we will specify how to make the index optimize SQL.