Mysql中怎么優(yōu)化慢查詢

今天就跟大家聊聊有關Mysql中怎么優(yōu)化慢查詢，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

一、打開Mysql的慢查詢?nèi)罩荆?/h3>

方式一：修改my.ini

//定義多少秒的查詢算是慢查詢，這里定為2秒
long_query_time=2

//5.5及以上版本配置如下
slow-query-log=on
slow_query_log_file="mysql_slow_query.log"

//記錄沒有使用索引的query
log-query-not-using-indexes

方式二：控制臺開啟

set global slow_query_lon=ON
set global long_query_time=3600
set global log_querise_not_using_indexes=ON

二、使用EXPLAIN分析sql慢查詢語句

上圖為使用了索引，下圖沒有。

type從好到壞依次是：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL

• const：使用唯一索引或者主鍵，返回記錄一定是1行記錄的等值where條件時，通常type是const。其他數(shù)據(jù)庫也叫做唯一索引掃描

• eq_ref：出現(xiàn)在要連接過個表的查詢計劃中，驅(qū)動表只返回一行數(shù)據(jù)，且這行數(shù)據(jù)是第二個表的主鍵或者唯一索引，且必須為not null，唯一索引和主鍵是多列時，只有所有的列都用作比較時才會出現(xiàn)eq_ref

• ref：不像eq_ref那樣要求連接順序，也沒有主鍵和唯一索引的要求，只要使用相等條件檢索時就可能出現(xiàn)，常見與輔助索引的等值查找。或者多列主鍵、唯一索引中，使用第一個列之外的列作為等值查找也會出現(xiàn)，總之，返回數(shù)據(jù)不唯一的等值查找就可能出現(xiàn)。

• range：索引范圍掃描，常見于使用>,<,is null,between ,in ,like等運算符的查詢中。

• index，ALL：相當與全表查詢

阿里規(guī)約要求：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts 最好

三、常用慢查詢優(yōu)化

1. 索引失效的情況：

• 不在索引列上做任何操作（計算，函數(shù)，（自動或者手動）類型裝換），會導致索引失效而導致全表掃描；

• 使用不等于(!= 或者<>)的時候，無法使用索引，會導致索引失效

• 使用is not null 或者 is null會導致無法使用索引

• like查詢是以%開頭，索引會失效變成全表掃描，覆蓋索引。

• 如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什么盡量少用or的原因)。要想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引

• 如果mysql使用全表掃描要比使用索引快,則不會使用到索引

2. 優(yōu)化數(shù)據(jù)庫結(jié)構

   分表分庫

3. 優(yōu)化LIMIT分頁

   當limit m,n的m過大時，導致每次查詢都要先從整個表中找到滿足條件的前M記錄，之后舍棄這M條記錄并從第M+1條記錄開始再依次找到N條滿足條件的記錄。如果表非常大，且篩選字段沒有合適的索引，且M特別大那么這樣的代價是非常高的。

• 傳統(tǒng)分頁：

  Select * from table limit 10000,10；

  
  

• 推薦分頁：

  Select * from table WHERE id>=23423 limit 11; (每頁10條)
  
  Select * from table WHERE id>=23434 limit 11;

   

  Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;

   

  Select * from table INNER JOIN (SELECT id from table limit 10000,10) USING(id)

   

  程序取ID: Select id from table limit 10000,10;
  
  Select * from table WHERE ID in(123,456…);

  
  

• 分頁方式四：

• 分頁方式三：

• 分頁方式二：

• 分頁方式一：

SELECT * FROM pre_forum_post ORDER BY pid ASC LIMIT 7332000,1000

select a.* from pre_forum_post a ,(select tid,position from pre_forum_post ORDER BY pid ASC LIMIT 7332000,1000) b where a.tid=b.tid and a.position=b.position

https://blog.csdn.net/qq_35513598/article/details/79813098

https://blog.csdn.net/qq_35571554/article/details/82800463

4. 優(yōu)化in查詢

   一條In查詢：

   select * from a where id in (select id from b );

   他等同于：

   select * from a where exists(select * from b where b.id=a.id );

   而exists相關子查詢的執(zhí)行原理是: 循環(huán)取出a表的每一條記錄與b表進行比較，比較的條件是a.id=b.id . 看a表的每條記錄的id是否在b表存在，如果存在就行返回a表的這條記錄。

   由exists執(zhí)行原理可知，a表(外表)使用不了索引，必須全表掃描，因為是拿a表的數(shù)據(jù)到b表查。而且必須得使用a表的數(shù)據(jù)到b表中查（外表到里表中），順序是固定死的。

   要想優(yōu)化，只能在b上建索引，因為a表上的索引mysql利用不上。

   進一步優(yōu)化，把查詢修改成inner join連接查詢：

   select * from a inner join b on a.id=b.id;

   為什么不用left join 和 right join？這時候表之間的連接的順序就被固定住了，比如左連接就是必須先查左表全表掃描，然后一條一條的到另外表去查詢，右連接同理。仍然不是最好的選擇。而inner join的順序則是由mysql自己決定，最終選擇最優(yōu)的順序執(zhí)行。

看完上述內(nèi)容，你們對Mysql中怎么優(yōu)化慢查詢有進一步的了解嗎？如果還想了解更多知識或者相關內(nèi)容，請關注億速云行業(yè)資訊頻道，感謝大家的支持。