mysql查詢優化相關技巧

　　使用EXPLAIN語句檢查優化器操作 +----+-------------+----------+-------+---------------+------+---------+------+------+----------------- | id | select_type | table | type | possible_keys | key | key_len| ref | rows | Extra +----+-------------+----------+-------+---------------+------+---------+------+------+----------------- | 1 |SIMPLE | car_info | range | name | name | 768 | NULL | 9 | Using where; Using index | +----+-------------+----------+-------+---------------+------+---------+------+------+----------------

　　EXPLAIN輸出解釋

　　select_type 有如下幾種類型： SIMPLE：未使用連接查詢或者子查詢的簡單select語句 explain select * from car_info;

　　PRIMARY：最外層的select語句 explain select * from (select name from car_info where name like '凱迪拉克%') as a;

　　+----+-------------+------------+-------+---------------+------+---------+------+------+-------------

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+------------+-------+---------------+------+---------+------+------+---------------

　　| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 9 | |

　　| 2 | DERIVED | car_info | range | name | name | 768 | NULL | 9 | Using where; Using index |

　　+----+-------------+------------+-------+---------------+------+---------+------+------+---------------

　　UNION：union中的第二個，或後面的select語句 explain select name from car_info where id =100 union select name from web_car_brands where id =5; +------+--------------+----------------+-------+---------------+---------+---------+-------+------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+--- | 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 | | | 2 | UNION | web_car_brands | const | PRIMARY,id | PRIMARY | 4 | const | 1 | | | NULL | UNION RESULT |

　　DEPENDENT UNION：union中的第二個或後面的色了傳統語句，取決於外面的查詢

　　mysql> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3) ;

　　+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+------

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--

　　| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where

　　| 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |

　　| 3 | DEPENDENT UNION | t3 | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index |

　　|NULL | UNION RESULT |

　　+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+-

　　UNION RESULT：union的結果 explain select name from car_info where id =100 union select name from web_car_brands where id =5; +------+--------------+----------------+-------+---------------+---------+---------+-------+------+----- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+-- | 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 | | | 2 | UNION | web_car_brands | const | PRIMARY,id | PRIMARY | 4 | const | 1 | | | NULL | UNION RESULT |

　　SUBQUERY：子查詢中的第一個SELECT語句 explain select name from car_info where id = (select id from web_car_series where id = 5); +----+-------------+----------------+-------+---------------+---------+---------+-------+------+----- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+------ | 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 | | | 2 | SUBQUERY | web_car_series | const | PRIMARY | PRIMARY | 4 | | 1 | Using index | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+-----

　　DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決於外面的查詢

　　explain select name from car_info where id in (select id from web_car_series where id = 5); +----+--------------------+----------------+-------+---------------+---------+---------+-------+------+- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+----------------+-------+---------------+---------+---------+-------+------+ | 1 | PRIMARY | car_info | index | NULL | name | 768 | NULL | 145 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | web_car_series | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | +----+--------------------+----------------+-------+---------------+---------+---------+-------+-----

　　DERIVED：在from列表中包含子查詢，mysql會遞歸的執行該子查詢，並把結果放在臨時表中

　　explain select * from (select name from car_info where id = 100) a; +----+-------------+------------+--------+---------------+---------+---------+------+------+- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+------+------+- | 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | car_info | const | PRIMARY | PRIMARY | 8 | | 1 | | +----+-------------+------------+--------+---------------+---------+---------+------+------+-

　　type列： MySQL 在表裡找到所需行的方式包括如下幾張(由左至右，由最差到最好)： All-->index-->range-->ref -->eq_ref-->const,system -->null

　　ALL：進行全數據表掃描 index：按照索引的次序掃描表，先讀索引，然後讀取具體的數據行，其實還是全表掃描，好處在於不用排序，按照索引的順序 range：按照某個范圍讀取數據行 ref：非唯一性索引訪問 eq_ref：使用唯一性索引訪問(主鍵或者唯一性索引) const：最多只有一個匹配行，const常用於數值比較如 primary key

　　null：在優化過程中已經得到結果，不需要訪問表或者索引 如：explain select min(id) from car_info;

　　possible_keys列： possible_keys列指出MySQL能使用哪個索引在該表中找到行。注意，該列完全獨立於EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。

　　如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣，創造一個適當的索引並且再次用EXPLAIN檢查查詢

　　key列 key列顯示MySQL實際決定使用的鍵(索引)。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

　　key_len列 key_len列顯示MySQL決定使用的鍵長度。使用的索引的長度，在不損失精確性的情況下，長度越短越好

　　rows列 rows列顯示MySQL認為它執行查詢時必須檢查的行數

　　mysql相關優化技巧

　　盡量使用數據類型相同的數據列進行比較

　　使帶索引的數據列在比較表達式中單獨出現

　　不要在like模式的開始位置使用通配符，此時索引無效

　　盡量使用數值操作，少使用字符串操作

　　數據類型合理選用，盡量"小"，選擇適用於存儲引擎的數據格式

　　盡量將數據列聲明為NOT NULL ，因為MYSQL不需要在查詢處理期間檢查數據列值是否為NULL

　　考慮使用ENUM數據列，ENUM在MYSQL內部被表示為一系列數值，處理速度快

　　利用Procedure analyse()語句 該語句可以將數據列中可以采用ENUM方式字段列出，procedure analyse(16,256)語句表示數據列中不同取值超過16個的或者長度超過256個字節的，不提出ENUM類型的建議

　　對容易產生碎片化的數據表進行整理，對於可變長度的數據列，隨著數據的大量修改或者刪除極易