MySQL Query optimization method (note)

1.COUNT ()

The optimization of count can be implemented by the following SQL

　　

Mysql> Select COUNT (gnp<10000 or null)  as ' <<<< ', count (gnp>=10000 or null) as ' >>> > ' from country;+------+------+| <<<< | >>>> |+------+------+|  |   |+------+------+1 row in Set (0.00 sec)

The value returned by COUNT (*) to COUNT (Cloumn) may be different because count (*) is also counted if there is an empty condition

2.SUM ()

Optimization of sum () needs to be achieved by establishing an index

　　

Mysql> desc city;+-------------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+----------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | Name | char (35) | NO |         MUL |                | || CountryCode | CHAR (3) |     NO |         |                | || District | char (20) |     NO |         |                | || Population | Int (11) |     NO | |                0 | |+-------------+----------+------+-----+---------+----------------+5 rows in Set (0.03 sec) mysql> CREATE index Popu _index on city (population); Query OK, 4079 rows affected (0.05 sec) records:4079 duplicates:0 warnings:0mysql> Explain select SUM (population) A s sum from city;+----+-------------+-------+-------+---------------+------------+---------+------+------+-------- -----+| ID | Select_type | Table | Type | Possible_keys | Key |Key_len | Ref | Rows |  Extra |+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+| 1 | Simple | City | Index | NULL | Popu_index | 4 | NULL | 4079 | Using index |+----+-------------+-------+-------+---------------+------------+---------+------+------+---------- ---+1 row in Set (0.00 sec) mysql> Select SUM (population) as sum from city;+------------+| Sum |+------------+| 1429559884 |+------------+1 row in Set (0.00 sec)

3. Optimization of sub-queries

Optimizations for subqueries can be implemented by on

　

Mysql> desc city;+-------------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+----------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | Name | char (35) | NO |         MUL |                | || CountryCode | CHAR (3) |     NO |         |                | || District | char (20) |     NO |         |                | || Population | Int (11) | NO | MUL |                0 | |+-------------+----------+------+-----+---------+----------------+5 rows in Set (0.01 sec) mysql> desc country; +----------------+---------------------------------------------------------------------------------------+----- -+-----+---------+--| Field | Type | Null | Key | Default | E+----------------+---------------------------------------------------------------------------------------+------+-----+---------+--| Code | CHAR (3) | NO |         PRI | || Name | CHAR (52) |     NO |         | || Continent | Enum (' Asia ', ' Europe ', ' North America ', ' Africa ', ' Oceania ', ' Antarctica ', ' South ' America ') |     NO | | Asia | | Region | char (26) |     NO |         | || Surfacearea | Float (10,2) |     NO | | 0.00 | | Indepyear | smallint (6) |     YES | | NULL | | Population | Int (11) |     NO | | 0 | | lifeexpectancy |             Float (3,1)                                                               |     YES | | NULL | | GNP | Float (10,2) |     YES | | NULL | | Gnpold | Float (10,2) |     YES | | NULL | | LocalName | CHAR (45) |     NO |         | || Governmentform | CHAR (45) |     NO |         | || Headofstate | char (60) |     YES | | NULL | | Capital | Int (11) |     YES | | NULL | | Code2 | char (2) |     NO |         | |+----------------+---------------------------------------------------------------------------------------+------+-----+-- -------+--15 rows in Set (0.01 sec) error:no Query specifiedmysql> explain select t.* from City t where T.countrycode in (select code from country), +----+-------------+---------+--------+---------------+---------+---------+----------- ----------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+---------+--------+---------------+---------+---------+---------------------+------+-  ------------+| 1 | Simple | T | All | NULL | NULL | NULL | NULL | 4079 |  NULL | | 1 | Simple | Country | Eq_ref | PRIMARY | PRIMARY | 3 |    World.t.countrycode | 1 | Using index |+----+-------------+---------+--------+---------------+---------+---------+---------------------+-- ----+-------------+2 rows in Set (0.04 sec) MYSQL&GT Explain select t.* from City t joins country T1 on t.countrycode = t1.code;+----+-------------+-------+--------+----------- ----+---------+---------+---------------------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+---------------------+------+---  ----------+| 1 | Simple | T | All | NULL | NULL | NULL | NULL | 4079 |  NULL | | 1 | Simple | T1 | Eq_ref | PRIMARY | PRIMARY | 3 |    World.t.countrycode | 1 | Using index |+----+-------------+-------+--------+---------------+---------+---------+---------------------+---- --+-------------+2 rows in Set (0.00 sec)

Such optimizations may return duplicate rows, which require the use of keywords distinct

4.Group by optimization

The following are normal execution cases

　　

mysql> explain select t.* from City t joins country T1 on t.countrycode = T1.code gr OUP by t.id;+----+-------------+-------+--------+---------------+---------+---------+---------------------+----- -+---------------------------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+----------------  -----+------+---------------------------------+| 1 | Simple | T | All | NULL | NULL | NULL | NULL | 4079 | Using temporary;  Using Filesort | | 1 | Simple | T1 | Eq_ref | PRIMARY | PRIMARY | 3 |    World.t.countrycode | 1 | Using index |+----+-------------+-------+--------+---------------+---------+---------+---------------- -----+------+---------------------------------+2 rows in Set (0.02 sec) 

The

can see how to use temporary tables and file sorting, so how can we reduce the cost of the IO already consumed by the resource, as shown in the following way

Mysql> explain select t.* from City t joins (select code from country) T1 on t.countrycode = t1.code;+----+------------- +------------+-------+---------------+-------------+---------+---------------------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+------------+-------+---------------+-------------+---------+---------------------+--  ----+-------------+| 1 | PRIMARY | T | All | NULL | NULL | NULL | NULL | 4079 |  NULL | | 1 | PRIMARY | <derived2> | Ref | <auto_key0> | <auto_key0> | 3 |   World.t.countrycode | 10 |  Using Index | | 2 | DERIVED | Country | Index | NULL | PRIMARY | 3 |  NULL | 239 | Using index |+----+-------------+------------+-------+---------------+-------------+---------+------------------ ---+------+-------------+3 rows in sET (0.00 sec) 

This avoids the sorting of files and the use of the temporary table.

Optimization of 5.limit operation mode

Before use:

Mysql> Explain select * from City order by name limit  100,10;+----+-------------+-------+------+---------------+ ------+---------+------+------+----------------+| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref  | rows | Extra          |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+|  1 | Simple      |  All  | NULL          | NULL | NULL    | NULL | 4079 | Using filesort |+----+-------------+-------+------+---------------+------+---------+------+------+-------------- --+1 row in Set (0.00 sec)

You can see the use of file sorting, which results in poor performance.

Optimization method: The order by operation can be done by the primary key or the column containing the index;

　　

Mysql> CREATE index Name_index on city  (name);mysql> Explain select name from city order by name limit  100,10 ;+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+| ID | Select_type | Table | Type  | possible_keys | key        | key_len | ref  | rows | Extra       |+----+-------------+-------+-------+---------------+------------+---------+------+------+----------- --+|  1 | Simple      | City  | index | NULL          | name_index |      NULL |  110 | Using index |+----+-------------+-------+-------+---------------+------------+---------+------+------+---------- ---+1 row in Set (0.00 sec)

You can see that only 35 rows were scanned and the index was used but the efficiency was increased a lot.

MySQL Query optimization method (note)