1. MySQL查詢慢是什么體驗(yàn)？

大多數(shù)互聯(lián)網(wǎng)應(yīng)用場(chǎng)景都是讀多寫(xiě)少，業(yè)務(wù)邏輯更多分布在寫(xiě)上。對(duì)讀的要求大概就是要快。那么都有什么原因會(huì)導(dǎo)致我們完成一次出色的慢查詢呢？

1.1 索引

在數(shù)據(jù)量不是很大時(shí)，大多慢查詢可以用索引解決，大多慢查詢也因?yàn)樗饕缓侠矶a(chǎn)生。

MySQL 索引基于 B+ 樹(shù)，這句話相信面試都背爛了，接著就可以問(wèn)最左前綴索引、 B+ 樹(shù)和各種樹(shù)了。

說(shuō)到最左前綴，實(shí)際就是組合索引的使用規(guī)則，使用合理組合索引可以有效的提高查詢速度，為什么呢？

因?yàn)樗饕峦?。如果查詢條件包含在了組合索引中，比如存在組合索引（a，b），查詢到滿足 a 的記錄后會(huì)直接在索引內(nèi)部判斷 b 是否滿足，減少回表次數(shù)。

同時(shí)，如果查詢的列恰好包含在組合索引中，即為覆蓋索引，無(wú)需回表。索引規(guī)則估計(jì)都知道，實(shí)際開(kāi)發(fā)中也會(huì)創(chuàng)建和使用。問(wèn)題可能更多的是：為什么建了索引還慢？

1.1.1 什么原因?qū)е滤饕?/p>

建了索引還慢，多半是索引失效（未使用），可用 explain 分析。索引失效常見(jiàn)原因有 ：

where 中使用 ！= 或 《》 或 or 或表達(dá)式或函數(shù)（左側(cè)）

like 語(yǔ)句 % 開(kāi)頭

字符串未加’’

索引字段區(qū)分度過(guò)低，如性別

未匹配最左前綴

（一張嘴就知道老面試題了） 為什么這些做法會(huì)導(dǎo)致失效，成熟的 MySQL 也有自己的想法。

1.1.2 這些原因?yàn)槭裁磳?dǎo)致索引失效

如果要 MySQL 給一個(gè)理由，還是那棵 B+ 樹(shù)。

函數(shù)操作

當(dāng)在 查詢 where = 左側(cè)使用表達(dá)式或函數(shù)時(shí)，如字段 A 為字符串型且有索引， 有 where length（a） = 6查詢，這時(shí)傳遞一個(gè) 6 到 A 的索引樹(shù)，不難想象在樹(shù)的第一層就迷路了。

隱式轉(zhuǎn)換

隱式類(lèi)型轉(zhuǎn)換和隱式字符編碼轉(zhuǎn)換也會(huì)導(dǎo)致這個(gè)問(wèn)題。

隱式類(lèi)型轉(zhuǎn)換對(duì)于 JOOQ 這種框架來(lái)說(shuō)一般倒不會(huì)出現(xiàn)。

隱式字符編碼轉(zhuǎn)換在連表查詢時(shí)倒可能出現(xiàn)，即連表字段的類(lèi)型相同但字符編碼不同。

破壞了有序性

至于 Like 語(yǔ)句 % 開(kāi)頭、字符串未加 ’’ 原因基本一致，MySQL 認(rèn)為對(duì)索引字段的操作可能會(huì)破壞索引有序性就機(jī)智的優(yōu)化掉了。

不過(guò)，對(duì)于如性別這種區(qū)分度過(guò)低的字段，索引失效就不是因?yàn)檫@個(gè)原因。

1.1.3 性別字段為什么不要加索引

為什么索引區(qū)分度低的字段不要加索引。盲猜效率低，效率的確低，有時(shí)甚至?xí)扔跊](méi)加。

對(duì)于非聚簇索引，是要回表的。假如有 100 條數(shù)據(jù)，在 sex 字段建立索引，掃描到 51 個(gè) male，需要再回表掃描 51 行。還不如直接來(lái)一次全表掃描呢。

所以，InnoDB 引擎對(duì)于這種場(chǎng)景就會(huì)放棄使用索引，至于區(qū)分度多低多少會(huì)放棄，大致是某類(lèi)型的數(shù)據(jù)占到總的 30% 左右時(shí)，就會(huì)放棄使用該字段的索引，有興趣可以試一下。

1.1.4 有什么好用且簡(jiǎn)單的索引方法

前面說(shuō)到大多慢查詢都源于索引，怎么建立并用好索引。這里有一些簡(jiǎn)單的規(guī)則。

索引下推：性別字段不適合建索引，但確實(shí)存在查詢場(chǎng)景怎么辦？如果是多條件查詢，可以建立聯(lián)合索引利用該特性優(yōu)化。

覆蓋索引：也是聯(lián)合索引，查詢需要的信息在索引里已經(jīng)包含了，就不會(huì)再回表了。

前綴索引：對(duì)于字符串，可以只在前 N 位添加索引，避免不必要的開(kāi)支。假如的確需要如關(guān)鍵字查詢，那交給更合適的如 ES 或許更好。

不要對(duì)索引字段做函數(shù)操作

對(duì)于確定的、寫(xiě)多讀少的表或者頻繁更新的字段都應(yīng)該考慮索引的維護(hù)成本。

1.1.5 如何評(píng)價(jià) MySQL 選錯(cuò)了索引

有時(shí)，建立了猛一看挺正確的索引，但事情卻沒(méi)按計(jì)劃發(fā)展。就像“為啥 XXX 有索引，根據(jù)它查詢還是慢查詢”。

此刻沒(méi)準(zhǔn)要自信點(diǎn)：我的代碼不可能有 BUG，肯定是 MySQL 出了問(wèn)題。MySQL 的確可能有點(diǎn)問(wèn)題。

這種情況常見(jiàn)于建了一大堆索引，查詢條件一大堆。沒(méi)使用你想讓它用的那一個(gè)，而是選了個(gè)區(qū)分度低的，導(dǎo)致過(guò)多的掃描。造成的原因基本有兩個(gè)：

信息統(tǒng)計(jì)不準(zhǔn)確：可以使用 analyze table x重新分析。

優(yōu)化器誤判：可以 force index強(qiáng)制指定?；蛐薷恼Z(yǔ)句引導(dǎo)優(yōu)化器，增加或刪除索引繞過(guò)。

但根據(jù)我淺薄的經(jīng)驗(yàn)來(lái)看，更可能是因?yàn)槟憬诵](méi)必要的索引導(dǎo)致的。不會(huì)真有人以為 MySQL 沒(méi)自己機(jī)靈吧？

除了上面這些索引原因外，還有下面這些不常見(jiàn)或者說(shuō)不好判斷的原因存在。

1.2 等MDL鎖

在 MySQL 5.5 版本中引入了 MDL，對(duì)一個(gè)表做 CRUD 操作時(shí)，自動(dòng)加 MDL 讀鎖；對(duì)表結(jié)構(gòu)做變更時(shí)，加 MDL 寫(xiě)鎖。讀寫(xiě)鎖、寫(xiě)鎖間互斥。

當(dāng)某語(yǔ)句拿 MDL 寫(xiě)鎖就會(huì)阻塞 MDL 讀鎖，可以使用show processlist命令查看處于Waiting for table metadata lock狀態(tài)的語(yǔ)句。

1.3 等 flush

flush 很快，大多是因?yàn)?flush 命令被別的語(yǔ)句堵住，它又堵住了 select 。通過(guò)show processlist命令查看時(shí)會(huì)發(fā)現(xiàn)處于Waiting for table flush狀態(tài)。

1.4 等行鎖

某事物持有寫(xiě)鎖未提交。

1.5 當(dāng)前讀

InnoDB 默認(rèn)級(jí)別是可重復(fù)讀。設(shè)想一個(gè)場(chǎng)景：事物 A 開(kāi)始事務(wù)，事務(wù) B 也開(kāi)始執(zhí)行大量更新。B 率先提交， A 是當(dāng)前讀，就要依次執(zhí)行 undo log ，直到找到事務(wù) B 開(kāi)始前的值。

1.6 大表場(chǎng)景

在未二次開(kāi)發(fā)的 MYSQL 中，上億的表肯定算大表，這種情況即使在索引、查詢層面做到了較好實(shí)現(xiàn)，面對(duì)頻繁聚合操作也可能會(huì)出現(xiàn) IO 或 CPU 瓶頸，即使是單純查詢，效率也會(huì)下降。

且 Innodb 每個(gè) B+ 樹(shù)節(jié)點(diǎn)存儲(chǔ)容量是 16 KB，理論上可存儲(chǔ) 2kw 行左右，這時(shí)樹(shù)高為3層。我們知道，innodb_buffer_pool 用來(lái)緩存表及索引，如果索引數(shù)據(jù)較大，緩存命中率就堪憂，同時(shí) innodb_buffer_pool 采用 LRU 算法進(jìn)行頁(yè)面淘汰，如果數(shù)據(jù)量過(guò)大，對(duì)老或非熱點(diǎn)數(shù)據(jù)的查詢可能就會(huì)把熱點(diǎn)數(shù)據(jù)給擠出去。

所以對(duì)于大表常見(jiàn)優(yōu)化即是分庫(kù)分表和讀寫(xiě)分離了。

1.6.1 分庫(kù)分表

方案

是分庫(kù)還是分表呢？這要具體分析。

如果磁盤(pán)或網(wǎng)絡(luò)有 IO 瓶頸，那就要分庫(kù)和垂直分表。

如果是 CPU 瓶頸，即查詢效率偏低，水平分表。

水平即切分?jǐn)?shù)據(jù)，分散原有數(shù)據(jù)到更多的庫(kù)表中。

垂直即按照業(yè)務(wù)對(duì)庫(kù)，按字段對(duì)表切分。

工具方面有 sharding-sphere、TDDL、Mycat。動(dòng)起手來(lái)需要先評(píng)估分庫(kù)、表數(shù)，制定分片規(guī)則選 key，再開(kāi)發(fā)和數(shù)據(jù)遷移，還要考慮擴(kuò)容問(wèn)題。

問(wèn)題

實(shí)際運(yùn)行中，寫(xiě)問(wèn)題不大，主要問(wèn)題在于唯一 ID 生成、非 partition key 查詢、擴(kuò)容。

唯一 ID 方法很多，DB 自增、Snowflake、號(hào)段、一大波GUID算法等。

非 partition key 查詢常用映射法解決，映射表用到覆蓋索引的話還是很快的。或者可以和其他 DB 組合。

擴(kuò)容要根據(jù)分片時(shí)的策略確定，范圍分片的話就很簡(jiǎn)單，而隨機(jī)取模分片就要遷移數(shù)據(jù)了。也可以用范圍 + 取模的模式分片，先取模再范圍，可以避免一定程度的數(shù)據(jù)遷移。

當(dāng)然，如果分庫(kù)還會(huì)面臨事務(wù)一致性和跨庫(kù) join 等問(wèn)題。

1.6.2 讀寫(xiě)分離

為什么要讀寫(xiě)分離

分表針對(duì)大表解決 CPU 瓶頸，分庫(kù)解決 IO 瓶頸，二者將存儲(chǔ)壓力解決了。但查詢還不一定。

如果落到 DB 的 QPS 還是很高，且讀遠(yuǎn)大于寫(xiě)，就可以考慮讀寫(xiě)分離，基于主從模式將讀的壓力分?jǐn)?，避免單機(jī)負(fù)載過(guò)高，同時(shí)也保證了高可用，實(shí)現(xiàn)了負(fù)載均衡。

問(wèn)題

主要問(wèn)題有過(guò)期讀和分配機(jī)制。

過(guò)期讀，也就是主從延時(shí)問(wèn)題，這個(gè)對(duì)于。

分配機(jī)制，是走主還是從庫(kù)?？梢灾苯哟a中根據(jù)語(yǔ)句類(lèi)型切換或者使用中間件。

1.7 小結(jié)

以上列舉了 MySQL 常見(jiàn)慢查詢?cè)蚝吞幚矸椒?，介紹了應(yīng)對(duì)較大數(shù)據(jù)場(chǎng)景的常用方法。

分庫(kù)分表和讀寫(xiě)分離是針對(duì)大數(shù)據(jù)或并發(fā)場(chǎng)景的，同時(shí)也為了提高系統(tǒng)的穩(wěn)定和拓展性。但也不是所有的問(wèn)題都最適合這么解決。

2. 如何評(píng)價(jià) ElasticSearch

前文有提到對(duì)于關(guān)鍵字查詢可以使用 ES。那接著聊聊 ES 。

2.1 可以干什么

ES 是基于 Lucene 的近實(shí)時(shí)分布式搜索引擎。使用場(chǎng)景有全文搜索、NoSQL Json 文檔數(shù)據(jù)庫(kù)、監(jiān)控日志、數(shù)據(jù)采集分析等。

對(duì)非數(shù)據(jù)開(kāi)發(fā)來(lái)說(shuō)，常用的應(yīng)該就是全文檢索和日志了。ES 的使用中，常和 Logstash， Kibana 結(jié)合，也成為 ELK 。先來(lái)瞧瞧日志怎么用的。

下面是我司日志系統(tǒng)某檢索操作：打開(kāi) Kibana 在 Discover 頁(yè)面輸入格式如 “xxx” 查詢。

該操作可以在 Dev Tools 的控制臺(tái)替換為：

GET yourIndex/_search { “from” ： 0， “size” ： 10， “query” ： { “match_phrase” ： { “l(fā)og” ： “xxx” } } }

什么意思？Discover 中加上 “” 和 console 中的 match_phrase 都代表這是一個(gè)短語(yǔ)匹配，意味著只保留那些包含全部搜索詞項(xiàng)，且位置與搜索詞項(xiàng)相同的文檔。

2.2 ES 的結(jié)構(gòu)

在 ES 7.0 之前存儲(chǔ)結(jié)構(gòu)是 Index -》 Type -》 Document，按 MySQL 對(duì)比就是 database - table - id（實(shí)際這種對(duì)比不那么合理）。7.0 之后 Type 被廢棄了，就暫把 index 當(dāng)做 table 吧。

在 Dev Tools 的 Console 可以通過(guò)以下命令查看一些基本信息。也可以替換為 crul 命令。

GET /_cat/health？v&pretty：查看集群健康狀態(tài)GET /_cat/shards？v ：查看分片狀態(tài)GET yourindex/_mapping ：index mapping結(jié)構(gòu)GET yourindex/_settings ：index setting結(jié)構(gòu)GET /_cat/indices？v ：查看當(dāng)前節(jié)點(diǎn)所有索引信息

重點(diǎn)是 mapping 和 setting ，mapping 可以理解為 MySQL 中表的結(jié)構(gòu)定義，setting 負(fù)責(zé)控制如分片數(shù)量、副本數(shù)量。

以下是截取了某日志 index 下的部分 mapping 結(jié)構(gòu)，ES 對(duì)字符串類(lèi)型會(huì)默認(rèn)定義成 text ，同時(shí)為它定義一個(gè)叫做 keyword 的子字段。這兩的區(qū)別是：text 類(lèi)型會(huì)進(jìn)行分詞， keyword 類(lèi)型不會(huì)進(jìn)行分詞。

“******”： { “mappings”： { “doc”： { “properties”： { “appname”： { “type”： “text”， “fields”： { “keyword”： { “type”： “keyword”， “ignore_above”： 256 } }

2.3 ES 查詢?yōu)槭裁纯欤?/p>

分詞是什么意思？看完 ES 的索引原理你就 get 了。

ES 基于倒排索引。嘛意思？傳統(tǒng)索引一般是以文檔 ID 作索引，以內(nèi)容作為記錄。倒排索引相反，根據(jù)已有屬性值，去找到相應(yīng)的行所在的位置，也就是將單詞或內(nèi)容作為索引，將文檔 ID 作為記錄。

圖中的 Ada、Sara 被稱作 term，其實(shí)就是分詞后的詞了。如果把圖中的 Term Index 去掉，是不是有點(diǎn)像 MySQL 了？Term Dictionary 就像二級(jí)索引，但 MySQL 是保存在磁盤(pán)上的，檢索一個(gè) term 需要若干次的 random access 磁盤(pán)操作。

而 ES 在 Term Dictionary 基礎(chǔ)上多了層 Term Index ，它以 FST 形式保存在內(nèi)存中，保存著 term 的前綴，借此可以快速的定位到 Term dictionary 的本 term 的 offset 。而且 FST 形式和 Term dictionary 的 block 存儲(chǔ)方式都很節(jié)省內(nèi)存和磁盤(pán)空間。

到這就知道為啥快了，就是因?yàn)橛辛藘?nèi)存中的 Term Index ， 它為 term 的索引 Term Dictionary 又做了一層索引。

不過(guò)，也不是說(shuō) ES 什么查詢都比 MySQL 快。檢索大致分為兩類(lèi)。

2.3.1 分詞后檢索

ES 的索引存儲(chǔ)的就是分詞排序后的結(jié)果。比如圖中的 Ada，在 MySQL 中 %da% 就掃全表了，但對(duì) ES 來(lái)說(shuō)可以快速定位

2.3.2 精確檢索

該情況其實(shí)相差是不大的，因?yàn)?Term Index 的優(yōu)勢(shì)沒(méi)了，卻還要借此找到在 term dictionary 中的位置。也許由于 MySQL 覆蓋索引無(wú)需回表會(huì)更快一點(diǎn)。

2.4 什么時(shí)候用 ES

如前所述，對(duì)于業(yè)務(wù)中的查詢場(chǎng)景什么時(shí)候適合使用 ES ？我覺(jué)得有兩種。

2.4.1 全文檢索

在 MySQL 中字符串類(lèi)型根據(jù)關(guān)鍵字模糊查詢就是一場(chǎng)災(zāi)難，對(duì) ES 來(lái)說(shuō)卻是小菜一碟。具體場(chǎng)景，比如消息表對(duì)消息內(nèi)容的模糊查詢，即聊天記錄查詢。

但要注意，如果需要的是類(lèi)似廣大搜索引擎的關(guān)鍵字查詢而非日志的短語(yǔ)匹配查詢，就需要對(duì)中文進(jìn)行分詞處理，最廣泛使用的是 ik 。Ik 分詞器的安裝這里不再細(xì)說(shuō)。

什么意思呢？

分詞

開(kāi)頭對(duì)日志的查詢，鍵入 “我可真是個(gè)機(jī)靈鬼” 時(shí)，只會(huì)得到完全匹配的信息。

而倘若去掉 “”，又會(huì)得到按照 “我”、“可”，“真”…。分詞匹配到的所有信息，這明顯會(huì)返回很多信息，也是不符合中文語(yǔ)義的。實(shí)際期望的分詞效果大概是“我”、“可”、“真是”，“機(jī)靈鬼”，之后再按照這種分詞結(jié)果去匹配查詢。

這是 ES 默認(rèn)的分詞策略對(duì)中文的支持不友善導(dǎo)致的，按照英語(yǔ)單詞字母來(lái)了，可英語(yǔ)單詞間是帶有空格的。這也是不少國(guó)外軟件中文搜索效果不 nice 的原因之一。

對(duì)于該問(wèn)題，你可以在 console 使用下方命令，測(cè)試當(dāng)前 index 的分詞效果。

POST yourindex/_analyze { “field”：“yourfield”， “text”：“我可真是個(gè)機(jī)靈鬼” }

2.4.2 組合查詢

如果數(shù)據(jù)量夠大，表字段又夠多。把所有字段信息丟到 ES 里創(chuàng)建索引是不合理的。使用 MySQL 的話那就只能按前文提到的分庫(kù)分表、讀寫(xiě)分離來(lái)了。何不組合下。

ES + MySQL

將要參與查詢的字段信息加上 id，放入 ES，做好分詞。將全量信息放入 MySQL，通過(guò) id 快速檢索。

ES + HBASE

如果要省去分庫(kù)分表什么的，或許可以拋棄 MySQL ，選擇分布式數(shù)據(jù)庫(kù)，比如 HBASE ， 對(duì)于這種 NOSQL 來(lái)說(shuō)，存儲(chǔ)能力海量，擴(kuò)容 easy ，根據(jù) rowkey 查詢也很快。

以上思路都是經(jīng)典的索引與數(shù)據(jù)存儲(chǔ)隔離的方案了。

當(dāng)然，攤子越大越容易出事，也會(huì)面臨更多的問(wèn)題。使用 ES 作索引層，數(shù)據(jù)同步、時(shí)序性、mapping 設(shè)計(jì)、高可用等都需要考慮。

畢竟和單純做日志系統(tǒng)對(duì)比，日志可以等待，用戶不能。

2.5 小結(jié)

本節(jié)簡(jiǎn)單介紹了 ES 為啥快，和這個(gè)快能用在哪?，F(xiàn)在你可以打開(kāi) Kibana 的控制臺(tái)試一試了。

如果想在 Java 項(xiàng)目中接入的話，有 SpringBoot 加持，在 ES 環(huán)境 OK 的前提下，完全是開(kāi)箱即用，就差一個(gè)依賴了?；镜?CRUD 支持都是完全 OK 的。

3. HBASE

前面有提到 HBASE，什么是 HBASE ，鑒于篇幅這里簡(jiǎn)單說(shuō)說(shuō)。

3.1 存儲(chǔ)結(jié)構(gòu)

關(guān)系型數(shù)據(jù)庫(kù)如 MySQL 是按行來(lái)的。

Row key 是主鍵，按照字典序排序。TimeStamp 是版本號(hào)。info 和 area 都是列簇（column Family），列簇將表進(jìn)行橫向切割。name、age 叫做列，屬于某一個(gè)列簇，可進(jìn)行動(dòng)態(tài)添加。Cell 是具體的 Value 。

3.2 OLTP 和 OLAP

數(shù)據(jù)處理大致可分成兩大類(lèi)：聯(lián)機(jī)事務(wù)處理OLTP（on-line transaction processing）、聯(lián)機(jī)分析處理OLAP（On-Line Analytical Processing）。

OLTP是傳統(tǒng)的關(guān)系型數(shù)據(jù)庫(kù)的主要應(yīng)用，主要是基本的、日常的事務(wù)處理。

OLAP是數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)的主要應(yīng)用，支持復(fù)雜分析，側(cè)重決策支持，提供直觀易懂的查詢結(jié)果。

面向列的適合做 OLAP，面向行的適用于聯(lián)機(jī)事務(wù)處理（OLTP）。不過(guò) HBASE 并不是 OLAP ，他沒(méi)有 transaction，實(shí)際上也是面向 CF 的。一般也沒(méi)多少人用 HBASE 做 OLAP 。

3.3 RowKey

HBASE 表設(shè)計(jì)的好不好，就看 RowKey 設(shè)計(jì)。這是因?yàn)?HBASE 只支持三種查詢方式

1、基于 Rowkey 的單行查詢 2、基于 Rowkey 的范圍掃描 3、全表掃描

可見(jiàn) HBASE 并不支持復(fù)雜查詢。

3.4 使用場(chǎng)景

HBASE 并非適用于實(shí)時(shí)快速查詢。它更適合寫(xiě)密集型場(chǎng)景，它擁用快速寫(xiě)入能力，而查詢對(duì)于單條或小面積查詢是 OK 的，當(dāng)然也只能根據(jù) rowkey。但它的性能和可靠性非常高，不存在單點(diǎn)故障。

4. 總結(jié)

個(gè)人覺(jué)得軟件開(kāi)發(fā)是循序漸進(jìn)的，技術(shù)服務(wù)于項(xiàng)目，合適比新穎復(fù)雜更重要。

如何完成一次快速的查詢？最該做的還是先找找自己的 Bug，解決了當(dāng)前問(wèn)題再創(chuàng)造新問(wèn)題。

本文列舉到的部分方案對(duì)于具體實(shí)現(xiàn)大多一筆帶過(guò)，實(shí)際無(wú)論是 MySQL 的分表還是 ES 的業(yè)務(wù)融合都會(huì)面臨很多細(xì)節(jié)和困難的問(wèn)題，搞工程的總要絕知此事要躬行。

文章轉(zhuǎn)載：llc687.top/post/如何完成一次快速的查詢

編輯：jq