欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

分析一個(gè)MySQL的異常查詢的案例

 更新時(shí)間:2015年05月07日 09:48:49   投稿:goldensun  
這篇文章主要介紹了分析一個(gè)MySQL的異常查詢的案例,主要是針對索引方面的操作問題,需要的朋友可以參考下

問題

用戶工單疑問:相同的語句,只是最后的limit行數(shù)不同。奇怪的是,limit 10 的性能比limit 100的語句還慢約10倍。

隱藏用戶表信息,語句及結(jié)果如下

SELECT f1 , SUM(`f2`) `CNT` FROM T WHERE f1 IS NOT NULL AND f3 = '2014-05-12' GROUP BY f1 ORDER BY `CNT` DESC LIMIT 10;

執(zhí)行時(shí)間3 min 3.65 sec

SELECT f1 , SUM(`f2`) `CNT` FROM T WHERE f1 IS NOT NULL AND f3 = '2014-05-12' GROUP BY f1 ORDER BY `CNT` DESC LIMIT 100;

執(zhí)行時(shí)間1.24Sec.

性能差距非常大!

分析
MySQL Tips:追查語句執(zhí)行時(shí)最常用的方法,是通過explain來看語句的執(zhí)行計(jì)劃。 ?

更有沖擊性的效果是通過縮小范圍后,在這個(gè)數(shù)據(jù)下,limit 67和limit 68的執(zhí)行計(jì)劃相差很大。

兩個(gè)執(zhí)行計(jì)劃:

LIMIT 67
id: 1
select_type: SIMPLE
table: a
type: range
possible_keys: A,B,C
key: B
key_len: 387
ref: NULL
rows: 2555192
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

LIMIT 68
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: A,B,C
key: A
key_len: 3
ref: const
rows: 67586
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

可以看到,兩個(gè)語句的執(zhí)行計(jì)劃不同:使用的索引不同。

MySQL Tips:explain的結(jié)果中,key表示最終使用的索引,rows表示使用這個(gè)索引需要掃描的行數(shù),這是個(gè)估計(jì)值。

表中 索引A定義為 (f3, f4, f1, f2, f5); 索引B定義為(f1, f2, f3);

一個(gè)確認(rèn)

雖然rows是估計(jì)值,但是指導(dǎo)索引使用的依據(jù)。既然limit 68能達(dá)到rows 67586,說明在第一個(gè)語句優(yōu)化器可選結(jié)果中,也應(yīng)該有此值,為什么不會(huì)選擇索引A?
先確認(rèn)一下我們上面的這個(gè)結(jié)論。

MySQL Tips:MySQL語法中能夠用force index 來強(qiáng)行要求優(yōu)化器使用某一個(gè)索引。

Explain SELECT f1 , SUM(f2) CNT FROM t force index(A) WHERE f1 IS NOT NULL AND f3 = ‘2014-05-12' GROUP BY P ORDER BY CNT DESC LIMIT 67\G

id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys:A
key: A
key_len: 3
ref: const
rows: 67586
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

順便說明,由于我們指定了force index,因此優(yōu)化器不會(huì)考慮其他索引,possible_keys里只會(huì)顯示A。我們關(guān)注的是rows:67586。這說明在limit 67語句里,使用索引A也能夠減少行掃描。

MySQL Tips:MySQL優(yōu)化器會(huì)對possiable_key中的每個(gè)可能索引都計(jì)算查詢代價(jià),選擇最小代價(jià)的查詢計(jì)劃。

至此我們大概可以猜測,這個(gè)應(yīng)該是MySQL實(shí)現(xiàn)上的bug:沒有選擇合適的索引,導(dǎo)致使用了明顯錯(cuò)誤的執(zhí)行計(jì)劃。

MySQL Tips:MySQL的優(yōu)化器執(zhí)行期間需要依賴于表的統(tǒng)計(jì)信息,而統(tǒng)計(jì)信息是估算值,因此有可能導(dǎo)致得到的執(zhí)行計(jì)劃非最優(yōu)。

但要說明的是,上述Tip是客觀情況造成(可接受),但本例卻是例外,因此優(yōu)化器實(shí)際上可以拿到能夠作出選擇正確結(jié)果的數(shù)據(jù)(rows值),但是最終選擇錯(cuò)誤。

原因分析

MySQL優(yōu)化器是按照查詢代價(jià)的估算值,來確定要使用的索引。計(jì)算這個(gè)估算值的過程,基本是按照“估計(jì)需要掃描的行數(shù)”來確定的。

MySQL Tips:MySQL在目前集團(tuán)主流使用的5.1和5.5版本中只能使用前綴索引。

因此,使用索引A只能用上字段f3,使用索引B只能用上字段f1。Rows即為使用了索引查到上下界,之后需要掃描的數(shù)據(jù)行數(shù)(估算值)。

上述的語句需要用到group和order by,因此執(zhí)行計(jì)劃中都有Using temporary; Using filesort。
流程上按順序先計(jì)算使用索引A的查詢代價(jià)。

之后依次計(jì)算其他possitabe_key的查詢代價(jià)。由于過程中需要排序,在得到一個(gè)暫定結(jié)果后,需要判斷是否有代價(jià)更低的排序方式(test_if_cheaper_ordering)。
與之前的大同小異,也是依靠估計(jì)掃描行數(shù)來計(jì)算代價(jià)。

在這個(gè)邏輯的實(shí)現(xiàn)過程中,存在一個(gè)bug:在估計(jì)當(dāng)前索引的區(qū)分度的時(shí)候,沒有考慮到前綴索引。

即:假設(shè)表中有50w行數(shù)據(jù),索引B(f1,f2,f3),則計(jì)算索引區(qū)分度時(shí),需要根據(jù)能夠用上的前綴部分來確定。比如f1有1000個(gè)不同的值,則平均每個(gè)key值上的記錄數(shù)為500.如(f1,f2)有10000個(gè)同的值,則平均每個(gè)組合key上的記錄數(shù)為50,若(f1,f2,f3)有50w個(gè)不同的值,則平均每個(gè)組合key上的記錄數(shù)為1。

MySQL Tips:每個(gè)key上的記錄數(shù)越少,說明使用該索引查詢時(shí)效率最高。對應(yīng)于show index from tbl 輸出結(jié)果中的Cardinality值越大。

在這個(gè)case下,索引B只能使用f1做前綴索引,但是在計(jì)算單key上的行平均值時(shí)用的是(f1,f2,f3),這就導(dǎo)致估算用索引B估算的時(shí)候,得到的代價(jià)偏小。導(dǎo)致誤選。

回到問題本身

1、 為什么limit值大的時(shí)候反而選對了呢?
這是因?yàn)樵谟?jì)算B的查詢代價(jià)時(shí),查詢需要返回的行數(shù)limit_rows也參與乘積,若limit值較大,則計(jì)算出來的B的代價(jià)就會(huì)更大,反而會(huì)由于代價(jià)。值超過A,而導(dǎo)致優(yōu)化器最終選擇A。

2、 這個(gè)表有50w行數(shù)就,為什么limit相差為就差別這么大?
這與語句本身有關(guān)。這個(gè)語句中有g(shù)roup by,這就意味著每多l(xiāng)imit一個(gè)值,實(shí)際上需要掃描更多的行N。 這里N為“表的總行數(shù)”/“表中不同的f2值”。
也就是說這個(gè)語句使得這個(gè)bug有放大作用。

解決方案

分析清楚后解決方法就比較簡單了,修改代碼邏輯,在執(zhí)行test_if_cheaper_ordering過程中,改用字段f1的區(qū)分度來計(jì)算即可。

相關(guān)文章

  • mysql修改sql_mode報(bào)錯(cuò)的解決

    mysql修改sql_mode報(bào)錯(cuò)的解決

    今天在Navicat中運(yùn)行sql語句創(chuàng)建數(shù)據(jù)表出現(xiàn)了錯(cuò)誤Err 1067。本文主要介紹了mysql修改sql_mode報(bào)錯(cuò)的解決,感興趣的可以了解一下
    2021-09-09
  • 企業(yè)生產(chǎn)MySQL優(yōu)化介紹

    企業(yè)生產(chǎn)MySQL優(yōu)化介紹

    這篇文章主要介紹了企業(yè)生產(chǎn)MySQL優(yōu)化介紹的相關(guān)內(nèi)容,文中敘述了其優(yōu)化內(nèi)容,各個(gè)方面介紹的比較詳細(xì),需要的朋友可以參考。
    2017-10-10
  • Mysql中replace與replace into的用法講解

    Mysql中replace與replace into的用法講解

    今天小編就為大家分享一篇關(guān)于Mysql中replace與replace into的用法講解,小編覺得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來看看吧
    2019-03-03
  • MySQL數(shù)據(jù)庫命令

    MySQL數(shù)據(jù)庫命令

    這篇文章主要介紹了數(shù)據(jù)庫的常用命令,數(shù)據(jù)庫中對表的命令以及一些常用的數(shù)據(jù)庫查詢和常用函數(shù),感興趣的小伙伴可以借鑒一下
    2023-03-03
  • 在MySQL中使用LIMIT進(jìn)行分頁的方法

    在MySQL中使用LIMIT進(jìn)行分頁的方法

    這篇文章主要介紹了在MySQL中使用LIMIT進(jìn)行分頁的方法,作者列舉出了三種方法,并且針對跳頁等常見問題做出了提示,需要的朋友可以參考下
    2015-05-05
  • DBeaver連接mysql數(shù)據(jù)庫錯(cuò)誤圖文解決方案

    DBeaver連接mysql數(shù)據(jù)庫錯(cuò)誤圖文解決方案

    這篇文章主要給大家介紹了關(guān)于DBeaver連接mysql數(shù)據(jù)庫錯(cuò)誤解決方案的相關(guān)資料,DBeaver是免費(fèi)、開源、通用數(shù)據(jù)庫工具,是許多開發(fā)開發(fā)人員和數(shù)據(jù)庫管理員的所選,需要的朋友可以參考下
    2023-11-11
  • Mysql 本地計(jì)算機(jī)無法啟動(dòng) mysql 服務(wù) 錯(cuò)誤 1067:進(jìn)程意外終止。

    Mysql 本地計(jì)算機(jī)無法啟動(dòng) mysql 服務(wù) 錯(cuò)誤 1067:進(jìn)程意外終止。

    初學(xué)php接觸mysql,遇到一些問題,卸載重裝后,無法啟動(dòng)mysql服務(wù),網(wǎng)絡(luò)上有很多種說法,我這里將我解決這個(gè)問題的辦法提出
    2009-12-12
  • 最新MySQL高級SQL語句大全

    最新MySQL高級SQL語句大全

    這篇文章主要介紹了MySQL高級SQL語句,主要包括常用查詢知識order by按關(guān)鍵字排序的sql語句,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2022-06-06
  • CentOS Mysql數(shù)據(jù)庫如何實(shí)現(xiàn)定時(shí)備份

    CentOS Mysql數(shù)據(jù)庫如何實(shí)現(xiàn)定時(shí)備份

    這篇文章主要介紹了CentOS Mysql數(shù)據(jù)庫如何實(shí)現(xiàn)定時(shí)備份,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下
    2020-06-06
  • MYSQL的索引使用注意小結(jié)

    MYSQL的索引使用注意小結(jié)

    這篇文章主要介紹了MYSQL的索引使用注意,本文通過示例代碼給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2023-09-09

最新評論