一個優(yōu)化MySQL查詢操作的具體案例分析
問題描述
一個用戶反映先線一個SQL語句執(zhí)行時間慢得無法接受。SQL語句看上去很簡單（本文描述中修改了表名和字段名):
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īng)該是先用a作為驅(qū)動表，先后使用 a.L和b.S這兩個索引。而實際上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ū)動表。
上一篇文章我們介紹到，MySQL選擇jion順序是分別分析各種join順序的代價后，選擇最小代價的方法。
這個join只涉及到兩個表，自然也與optimizer_search_depth無關(guān)。于是我們的問題就是，我們預(yù)期的那個join順序的為什么沒有被選中？
MySQL Tips: MySQL提供straight_join語法，強(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的查詢代價可以用依次連乘rows估算。
?join順序?qū)α?，簡單的分析查詢代價：普通join是1038165*1, straight_join是 63*1038165. 貌似MySQL沒有錯。但一定哪里不對！
發(fā)現(xiàn)異常
回到我們最初的設(shè)想。我們預(yù)計表a作為驅(qū)動表，是因為認(rèn)為表b能夠用上IX_S索引，而實際上staight_join的時候確實用上了，但這個結(jié)果與我們預(yù)期的又不同。
我們知道，索引的過濾性是決定了一個索引在查詢中是否會被選中的重要因素，那么是不是b.S的過濾性不好呢？
MySQL Tips: show index from tbname返回結(jié)果中Cardinality的值可以表明一個索引的過濾性。
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:
可以這個索引的CARDINALITY: 1038165，已經(jīng)很大了。那這個表的估算行是多少呢。
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這個索引的區(qū)分度被認(rèn)為非常好，已經(jīng)近似于唯一索引。
MySQL Tips: 在show table status結(jié)果中看到的Rows用于表示表的當(dāng)前行數(shù)。對于MyISAM表這是一個精確值，但對InnoDB這是個估算值。
雖然是估算值，但優(yōu)化器是以此為指導(dǎo)的，也就是說，上面的某個explain里面的數(shù)據(jù)完全不符合期望：staight_join結(jié)果中第二行的rows。
階段結(jié)論
我們發(fā)現(xiàn)整個錯誤的邏輯是這樣的：以a為驅(qū)動表的執(zhí)行計劃，由于索引b.S的rows估計為1038165導(dǎo)致優(yōu)化器認(rèn)為代價大于以b為驅(qū)動表。而實際上這個索引的區(qū)分度為1.（當(dāng)然對explan結(jié)果比較熟悉的同學(xué)會發(fā)現(xiàn)，第二行的type字段和Extra字段一起詭異了)
也就是說，straight_join得到的每一行去b中查詢的時候，都走了全表掃描。在MySQL里面出現(xiàn)這種情況的最常見的是類型轉(zhuǎn)換。比如一個字符串字段，雖然包含的是全數(shù)字，但查詢的時候傳入的不是字符串格式。
在這個case里面，兩個都是字符串。因此，就是字符集相關(guān)了。
回到兩個表結(jié)構(gòu)，發(fā)現(xiàn)S字段的聲明差別在于 COLLATE utf8_bin -- 這個就是本case的根本原因了：a表得到的S值是utf8_bin，優(yōu)化器認(rèn)為類型不同，無法直接用上索引b.IX_S過濾。
至于為什么還會用上索引，這個是因為覆蓋索引帶來“誤解”。
MySQL Tips：若查詢的所有結(jié)果能夠從某個索引完全得到，則會優(yōu)先用遍歷索引替代遍歷數(shù)據(jù)。
作為驗證，
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 *， 無法使用覆蓋索引，因此第二行的key就顯示為NULL. (筆者淚：要是早出這個結(jié)果查起來可方便多了）
優(yōu)化
當(dāng)然最直接的想法就是修改兩個表的S字段的定義，改成相同即可。這個方法可以避免修改業(yè)務(wù)代碼，但DDL代價略大。這里提供兩種在SQL語句方面的優(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;
這個寫法比較直觀，需要注意最后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)為小寫，就是將臨時結(jié)果集轉(zhuǎn)成utf8_bin，之后使用b.S匹配時就能夠直接利用索引。
其實兩個改寫方法的本質(zhì)相同，區(qū)別是寫法1是隱式轉(zhuǎn)換。理論上說寫法2速度更快些。
小結(jié)
做join的字段盡量設(shè)計為類型完全相同。
相關(guān)文章
MySQL數(shù)據(jù)庫主從同步實戰(zhàn)過程詳解
這篇文章主要介紹了MySQL數(shù)據(jù)庫主從同步,結(jié)合實例形式詳細(xì)分析了MySQL數(shù)據(jù)庫主從同步基本配置方法與操作注意事項,需要的朋友可以參考下2020-05-05
Mysql update多表聯(lián)合更新的方法小結(jié)
這篇文章主要介紹了Mysql update多表聯(lián)合更新的方法小結(jié)，通過實例代碼給大家介紹了mysql多表關(guān)聯(lián)update的語句，感興趣的朋友跟隨小編一起看看吧2020-02-02
mysql中查詢字段為null的數(shù)據(jù)navicat問題
這篇文章主要介紹了mysql中查詢字段為null的數(shù)據(jù)navicat問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12
MySQL查詢學(xué)習(xí)之基礎(chǔ)查詢操作
這篇文章主要給大家介紹了關(guān)于MySQL查詢學(xué)習(xí)之基礎(chǔ)查詢操作的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-05-05
這篇文章主要給大家介紹了關(guān)于MySQL?1130錯誤原因分析以及解決方案的相關(guān)資料,MySQL 1130錯誤通常是由于連接MySQL時使用的用戶名或密碼不正確所導(dǎo)致的,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-10-10
MySQL學(xué)習(xí)記錄之KEY分區(qū)引發(fā)的血案
這篇文章主要給大家介紹了關(guān)于MySQL學(xué)習(xí)記錄之KEY分區(qū)引發(fā)的血案的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
CentOS6.9下mysql 5.7.17安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了CentOS6.9下mysql 5.7.17安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-10-10
教你使用VS?Code的MySQL擴(kuò)展管理數(shù)據(jù)庫的方法
這篇文章主要介紹了使用VS?Code的MySQL擴(kuò)展管理數(shù)據(jù)庫,在本文告訴你如何用VS?Code的擴(kuò)展程序管理MySQL數(shù)據(jù)庫，包括連接到MySQL、新建數(shù)據(jù)庫和表、修改字段定義、簡單的查詢方法以及導(dǎo)入導(dǎo)出，需要的朋友可以參考下2022-01-01
NOT?IN?的作用和?IN?恰好相反，NOT?IN?用來判斷表達(dá)式的值是否不存在于給出的列表中；如果不是，返回值為?1，否則返回值為?0,下面這篇文章主要給大家介紹了關(guān)于mysql中not?in隱含陷阱的相關(guān)資料,需要的朋友可以參考下2022-04-04
SQL實現(xiàn)LeetCode(180.連續(xù)的數(shù)字)
這篇文章主要介紹了SQL實現(xiàn)LeetCode(180.連續(xù)的數(shù)字),本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08