這篇文章主要介紹MySQL中慢查詢優(yōu)化的示例分析，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

為海淀等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及海淀網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為做網(wǎng)站、網(wǎng)站制作、海淀網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！

一個(gè)用戶反映線上一個(gè)SQL語(yǔ)句執(zhí)行時(shí)間慢得無(wú)法接受。SQL語(yǔ)句看上去很簡(jiǎn)單（本文描述中修改了表名和字段名):
SELECT count(*)  FROM  a  JOIN  b ON  a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ; 

且查詢需要的字段都建了索引，表結(jié)構(gòu)如下：
CREATE TABLE `a` (
  `L` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
  `I` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `A` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `S` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `F` tinyint(4) DEFAULT NULL,
  `V` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '',
  `N` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  KEY `IX_L` (`L`),
  KEY `IX_I` (`I`),
  KEY `IX_S` (`S`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
  `R` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',
  `V` varchar(32) DEFAULT NULL,
  `U` varchar(32) DEFAULT NULL,
  `C` varchar(16) DEFAULT NULL,
  `S` varchar(64) DEFAULT NULL,
  `I` varchar(64) DEFAULT NULL,
  `E` bigint(32) DEFAULT NULL,
  `ES` varchar(128) DEFAULT NULL,
  KEY `IX_R` (`R`),
  KEY `IX_C` (`C`),
  KEY `IX_S` (`S`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

從語(yǔ)句看，這個(gè)查詢計(jì)劃很自然的，就應(yīng)該是先用a作為驅(qū)動(dòng)表，先后使用 a.L和b.S這兩個(gè)索引。而實(shí)際上explain的結(jié)果卻是：
    +----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref      | rows    | Extra       |
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
|  1 | SIMPLE      | b     | index | IX_S          | IX_S | 195     | NULL     | 1038165 | Using index |
|  1 | SIMPLE      | a     | ref   | IX_L,IX_S     | IX_S | 195     | test.b.S |       1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+

分析

從explain的結(jié)果看，查詢用了b作為驅(qū)動(dòng)表。
上一篇文章我們介紹到，MySQL選擇jion順序是分別分析各種join順序的代價(jià)后，選擇最小代價(jià)的方法。
這個(gè)join只涉及到兩個(gè)表，自然也與optimizer_search_depth無(wú)關(guān)。于是我們的問題就是，我們預(yù)期的那個(gè)join順序的為什么沒有被選中？

MySQL Tips: MySQL提供straight_join語(yǔ)法，強(qiáng)制設(shè)定連接順序。 explain SELECT count(*)  FROM  a  straight_join  b ON  a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;            
+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra                                       |
+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+
|  1 | SIMPLE      | a     | range | IX_L,IX_S     | IX_L | 4       | NULL |      63 | Using where                                 |
|  1 | SIMPLE      | b     | index | IX_S          | IX_S | 195     | NULL | 1038165 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+------+---------+------+---------+---------------------------------------------+

MySQL Tips: explain結(jié)果中，join的查詢代價(jià)可以用依次連乘rows估算。

join順序?qū)α耍?jiǎn)單的分析查詢代價(jià)：普通join是1038165*1, straight_join是 63*1038165. 貌似MySQL沒有錯(cuò)。但一定哪里不對(duì)！

發(fā)現(xiàn)異常

回到我們最初的設(shè)想。我們預(yù)計(jì)表a作為驅(qū)動(dòng)表，是因?yàn)檎J(rèn)為表b能夠用上IX_S索引，而實(shí)際上staight_join的時(shí)候確實(shí)用上了，但這個(gè)結(jié)果與我們預(yù)期的又不同。
我們知道，索引的過濾性是決定了一個(gè)索引在查詢中是否會(huì)被選中的重要因素，那么是不是b.S的過濾性不好呢？

MySQL Tips: show index from tbname返回結(jié)果中Cardinality的值可以表明一個(gè)索引的過濾性。

show index的結(jié)果太多，也可以從information_schema表中取。
mysql> select * from information_schema.STATISTICS where table_name='b' and index_name='IX_S'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: test
   TABLE_NAME: b
   NON_UNIQUE: 1
 INDEX_SCHEMA: test
   INDEX_NAME: IX_S
 SEQ_IN_INDEX: 1
  COLUMN_NAME: S
    COLLATION: A
  CARDINALITY: 1038165 SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT: 

可以這個(gè)索引的CARDINALITY: 1038165，已經(jīng)很大了。那這個(gè)表的估算行是多少呢。
show table status like 'b'\G
*************************** 1. row ***************************
           Name: b
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1038165 Avg_row_length: 114
    Data_length: 119160832
Max_data_length: 0
   Index_length: 109953024
      Data_free: 5242880
 Auto_increment: NULL
    Create_time: 2014-05-23 00:24:25
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
從Rows: 1038165看出，IX_S這個(gè)索引的區(qū)分度被認(rèn)為非常好，已經(jīng)近似于唯一索引。

MySQL Tips: 在show table status結(jié)果中看到的Rows用于表示表的當(dāng)前行數(shù)。對(duì)于MyISAM表這是一個(gè)精確值，但對(duì)InnoDB這是個(gè)估算值。 雖然是估算值，但優(yōu)化器是以此為指導(dǎo)的，也就是說，上面的某個(gè)explain里面的數(shù)據(jù)完全不符合期望：staight_join結(jié)果中第二行的rows。

目前為止

我們發(fā)現(xiàn)整個(gè)錯(cuò)誤的邏輯是這樣的：以a為驅(qū)動(dòng)表的執(zhí)行計(jì)劃，由于索引b.S的rows估計(jì)為1038165導(dǎo)致優(yōu)化器認(rèn)為代價(jià)大于以b為驅(qū)動(dòng)表。
而實(shí)際上這個(gè)索引的區(qū)分度為1.
（當(dāng)然對(duì)explan結(jié)果比較熟悉的同學(xué)會(huì)發(fā)現(xiàn)，第二行的type字段和Extra字段一起詭異了)

也就是說，straight_join得到的每一行去b中查詢的時(shí)候，都走了全表掃描。在MySQL里面出現(xiàn)這種情況的最常見的是類型轉(zhuǎn)換。比如一個(gè)字符串字段，雖然包含的是全數(shù)字，但查詢的時(shí)候傳入的不是字符串格式。

在這個(gè)case里面，兩個(gè)都是字符串。因此，就是字符集相關(guān)了。
回到兩個(gè)表結(jié)構(gòu)，發(fā)現(xiàn)S字段的聲明差別在于 COLLATE utf8_bin -- 這個(gè)就是本case的根本原因了：a表得到的S值是utf8_bin，優(yōu)化器認(rèn)為類型不同，無(wú)法直接用上索引b.IX_S過濾。

至于為什么還會(huì)用上索引，這個(gè)是因?yàn)楦采w索引帶來(lái)“誤解”。

MySQL Tips：若查詢的所有結(jié)果能夠從某個(gè)索引完全得到，則會(huì)優(yōu)先用遍歷索引替代遍歷數(shù)據(jù)。

作為驗(yàn)證，
mysql> explain SELECT *  FROM  a  straight_JOIN  b ON  binary a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;

+—-+————-+——-+——-+—————+——+———+——+———+————————————————+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+—-+————-+——-+——-+—————+——+———+——+———+————————————————+ 
| 1 | SIMPLE | a | range | IX_L | IX_L | 4 | NULL | 63 | Using where | 
| 1 | SIMPLE | b | ALL | IX_S | NULL | NULL | NULL | 1038165 | Range checked for each record (index map: 0x4) | 
+—-+————-+——-+——-+—————+——+———+——+———+————————————————+ 
由于結(jié)果是select *， 無(wú)法使用覆蓋索引，因此第二行的key就顯示為NULL. (筆者淚：要是早出這個(gè)結(jié)果查起來(lái)可方便多了）。

優(yōu)化

當(dāng)然最直接的想法就是修改兩個(gè)表的S字段的定義，改成相同即可。這個(gè)方法可以避免修改業(yè)務(wù)代碼，但DDL代價(jià)略大。這里提供兩種在SQL語(yǔ)句方面的優(yōu)化。

1、select count(*) from  b join (select s from  a  WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00') ta on b.S=ta.s;
這個(gè)寫法比較直觀，需要注意最后b.S和ta.S的順序

2、SELECT count(*)  FROM  a  JOIN  b ON  binary a.`S` = b.`S` WHERE a.`L` > '2014-03-30 00:55:00' AND a.`L` < '2014-03-30 01:00:00' ;
從前面的分析知道是由于b.S定義為utf8_bin.
MySQL Tips: MySQL中字符集命名規(guī)則中, XXX_bin與XXX的區(qū)別為大小寫是否敏感。
這里我們將A.s全部增加binary限定，先轉(zhuǎn)為小寫，就是將臨時(shí)結(jié)果集轉(zhuǎn)成utf8_bin，之后使用b.S匹配時(shí)就能夠直接利用索引。
其實(shí)兩個(gè)改寫方法的本質(zhì)相同，區(qū)別是寫法1是隱式轉(zhuǎn)換。理論上說寫法2速度更快些。

以上是“mysql中慢查詢優(yōu)化的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

新聞名稱：mysql中慢查詢優(yōu)化的示例分析
轉(zhuǎn)載注明：http://jinyejixie.com/article40/pgggeo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站建設(shè)、網(wǎng)站收錄、標(biāo)簽優(yōu)化、響應(yīng)式網(wǎng)站、做網(wǎng)站、手機(jī)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)