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

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

1.1 索引

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

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

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

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

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

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

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

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

like 語句 % 開頭

字符串未加’’

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

未匹配最左前綴

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

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

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

函數(shù)操作

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

隱式轉(zhuǎn)換

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

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

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

破壞了有序性

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

優(yōu)化器誤判：可以 force index強(qiáng)制指定。或修改語句引導(dǎo)優(yōu)化器，增加或刪除索引繞過。

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

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

1.2 等MDL鎖

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

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

1.3 等 flush

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

1.4 等行鎖

某事物持有寫鎖未提交。

1.5 當(dāng)前讀

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

1.6 大表場(chǎng)景

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

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

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

1.6.1 分庫(kù)分表

方案

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

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

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

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

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

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

問題

實(shí)際運(yù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 等問題。

1.6.2 讀寫分離

為什么要讀寫分離

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

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

問題

主要問題有過期讀和分配機(jī)制。

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

分配機(jī)制，是走主還是從庫(kù)。可以直接代碼中根據(jù)語句類型切換或者使用中間件。

1.7 小結(jié)

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

分庫(kù)分表和讀寫分離是針對(duì)大數(shù)據(jù)或并發(fā)場(chǎng)景的，同時(shí)也為了提高系統(tǒng)的穩(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ù)開發(fā)來說，常用的應(yīng)該就是全文檢索和日志了。ES 的使用中，常和 Logstash， Kibana 結(jié)合，也成為 ELK 。先來瞧瞧日志怎么用的。

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

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

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

什么意思？Discover 中加上 “” 和 console 中的 match_phrase 都代表這是一個(gè)短語匹配，意味著只保留那些包含全部搜索詞項(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 可以通過以下命令查看一些基本信息。也可以替換為 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ì)字符串類型會(huì)默認(rèn)定義成 text ，同時(shí)為它定義一個(gè)叫做 keyword 的子字段。這兩的區(qū)別是：text 類型會(huì)進(jìn)行分詞， keyword 類型不會(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 是保存在磁盤上的，檢索一個(gè) term 需要若干次的 random access 磁盤操作。

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

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

不過，也不是說 ES 什么查詢都比 MySQL 快。檢索大致分為兩類。

2.3.1 分詞后檢索

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

2.3.2 精確檢索

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

2.4 什么時(shí)候用 ES

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

2.4.1 全文檢索

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

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

什么意思呢？

分詞

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

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

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

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

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

2.4.2 組合查詢

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

ES + MySQL

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

ES + HBASE

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

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

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

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

2.5 小結(jié)

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

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

3. HBASE

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

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

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

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ù)處理大致可分成兩大類：聯(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）。不過 HBASE 并不是 OLAP ，他沒有 transaction，實(shí)際上也是面向 CF 的。一般也沒多少人用 HBASE 做 OLAP 。

3.3 RowKey

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

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

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

3.4 使用場(chǎng)景

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

4. 總結(jié)

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

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

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

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

編輯：jq