[MySQL Optimizer]--How to find the reason for SQL efficiency underground

[MySQL Optimizer]--How to find the reason for SQL efficiency underground

Source: Chinaunix Blog Date: 2009.07.20 16:12 (Total reviews ) I want to comment

After querying to inefficient SQL statements, you can obtain information about how MySQL executes the SELECT statement through the EXPLAIN or DESC command, including the order in which the 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 Associate 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) Each column is interpreted as follows: Select_type: Represents the type of SELECT, with a common value of simple (i.e. not using table joins or subqueries), PRIMARY (main query, or outer query), union (second or subsequent query statement in union), Subquer Y (the first SELECT in a subquery), and so on. Table: The tables that output the result set. Type: Represents the connection type of the table, performance from good to bad connection type is system (only one row in the table, that is, the constant table), const (a single table has a maximum of a matching row, such as primary key or unique index), Eq_ref (for each of the preceding row, in this Only one record is queried in the table, simply, primary key or unique index is used in a multi-table connection, ref (similar to Eq_ref, except that the primary key or unique index is used instead of the normal Ref_or_null (similar to ref, except that the condition contains a null query), index_merge (index merge optimization), Unique_subquery (in which a subquery that queries the primary key field), Index_sub Query (similar to Unique_subquery, where in is followed by a subquery that queries a non-unique indexed field), range (a range query in a single table), index (for each of the preceding rows by querying the index for data), all (for each preceding row, the Full table Scan to get the data). Possible_keys: Represents the index that may be used when querying. Key: Represents the index that is actually used. Key_len: The length of the indexed field. Rows: The number of rows scanned. Extra: Description and description of the implementation. In the above example, it is already possible to confirm that the full table scan of a table results in an unsatisfactory efficiency, then create an index on 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) It can be found that the number of rows that need to be scanned after indexing is significantly reduced (from full table scanning to 3 rows), and the use of visible indexes can greatly improve the database access speed, especially when the table is very large, this advantage is more obvious, the use of Index optimization SQL is a common basic method of optimization problem SQL In a later chapter, we'll show you how to make an index to optimize SQL. This article comes from Chinaunix blog, if you look at the original point:Http://blog.chinaunix.net/u3/93470/showart_2001531.html

[MySQL Optimizer]--How to find the reason for SQL efficiency underground