程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 一個優化MySQL查詢操作的詳細案例剖析

一個優化MySQL查詢操作的詳細案例剖析

編輯:MySQL綜合教程

一個優化MySQL查詢操作的詳細案例剖析。本站提示廣大學習愛好者:(一個優化MySQL查詢操作的詳細案例剖析)文章只能為提供參考,不一定能成為您想要的結果。以下是一個優化MySQL查詢操作的詳細案例剖析正文


成績描寫

一個用戶反應先線一個SQL語句履行時光慢得沒法接收。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' ;

且查詢須要的字段都建了索引,表構造以下:

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;

從語句看,這個查詢籌劃很天然的,就應當是先用a作為驅動表,前後應用 a.L和b.S這兩個索引。而現實上explain的成果倒是:

+----+-------------+-------+-------+---------------+------+---------+----------+---------+-------------+
| 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的成果看,查詢用了b作為驅動表。

上一篇文章我們引見到,MySQL選擇jion次序是分離剖析各類join次序的價值後,選擇最小價值的辦法。

這個join只觸及到兩個表,天然也與optimizer_search_depth有關。因而我們的成績就是,我們預期的誰人join次序的為何沒有被選中?

MySQL Tips: MySQL供給straight_join語法,強迫設定銜接次序。

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成果中,join的查詢價值可以用順次連乘rows預算。

?join次序對了,簡略的剖析查詢價值:通俗join是1038165*1, straight_join是 63*1038165. 貌似MySQL沒有錯。但必定哪裡纰謬!

發明異常

回到我們最後的假想。我們估計表a作為驅動表,是由於以為表b可以或許用上IX_S索引,而現實上staight_join的時刻確切用上了,但這個成果與我們預期的又分歧。

我們曉得,索引的過濾性是決議了一個索引在查詢中能否會被選中的主要身分,那末是否是b.S的過濾性欠好呢?

MySQL Tips: show index from tbname前往成果中Cardinality的值可以注解一個索引的過濾性。

show index的成果太多,也能夠從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,曾經很年夜了。那這個表的預算行是若干呢。

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這個索引的辨別度被以為異常好,曾經近似於獨一索引。

MySQL Tips: 在show table status成果中看到的Rows用於表現表確當前行數。關於MyISAM表這是一個准確值,但對InnoDB這是個預算值。

固然是預算值,但優化器是以此為指點的,也就是說,下面的某個explain外面的數據完整不相符希冀:staight_join成果中第二行的rows。

階段結論

我們發明全部毛病的邏輯是如許的:以a為驅動表的履行籌劃,因為索引b.S的rows估量為1038165招致優化器以為價值年夜於以b為驅動表。而現實上這個索引的辨別度為1.(固然對explan成果比擬熟習的同窗會發明,第二行的type字段和Extra字段一路詭異了)

也就是說,straight_join獲得的每行去b中查詢的時刻,都走了全表掃描。在MySQL外面湧現這類情形的最多見的是類型轉換。好比一個字符串字段,固然包括的是全數字,但查詢的時刻傳入的不是字符串格局。

在這個case外面,兩個都是字符串。是以,就是字符集相干了。

回到兩個表構造,發明S字段的聲明差異在於 COLLATE utf8_bin -- 這個就是本case的基本緣由了:a表獲得的S值是utf8_bin,優化器以為類型分歧,沒法直接用上索引b.IX_S過濾。

至於為何還會用上索引,這個是由於籠罩索引帶來“誤會”。

MySQL Tips:若查詢的一切成果可以或許從某個索引完整獲得,則會優先用遍歷索引替換遍歷數據。

作為驗證,

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) |

+—-+————-+——-+——-+—————+——+———+——+———+————————————————+

因為成果是select *, 沒法應用籠罩索引,是以第二行的key就顯示為NULL. (筆者淚:如果早出這個成果查起來可便利多了)

優化

固然最直接的設法主意就是修正兩個表的S字段的界說,改成雷同便可。這個辦法可以免修正營業代碼,但DDL價值略年夜。這裡供給兩種在SQL語句方面的優化。

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中字符集定名規矩中, XXX_bin與XXX的差別為年夜小寫能否敏感。

這裡我們將A.s全體增長binary限制,先轉為小寫,就是將暫時成果集轉成utf8_bin,以後應用b.S婚配時就可以夠直接應用索引。

其實兩個改寫辦法的實質雷同,差別是寫法1是隱式轉換。實際上說寫法2速度更快些。

小結

做join的字段盡可能設計為類型完整雷同。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved