1. MySQL查詢慢是什么體驗？

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

1.1 索引

在數(shù)據(jù)量不是很大時，大多慢查詢可以用索引解決，大多慢查詢也因為索引不合理而產(chǎn)生。

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

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

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

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

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

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

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

like 語句 % 開頭

字符串未加’’

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

未匹配最左前綴

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

1.1.2 這些原因為什么導(dǎo)致索引失效

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

函數(shù)操作

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

隱式轉(zhuǎn)換

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

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

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

破壞了有序性

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

不過，對于如性別這種區(qū)分度過低的字段，索引失效就不是因為這個原因。

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

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

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

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

1.1.4 有什么好用且簡單的索引方法

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

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

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

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

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

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

1.1.5 如何評價 MySQL 選錯了索引

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

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

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

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

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

但根據(jù)我淺薄的經(jīng)驗來看，更可能是因為你建了些沒必要的索引導(dǎo)致的。不會真有人以為 MySQL 沒自己機靈吧？

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

1.2 等MDL鎖

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

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

1.3 等 flush

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

1.4 等行鎖

某事物持有寫鎖未提交。

1.5 當(dāng)前讀

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

1.6 大表場景

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

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

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

1.6.1 分庫分表

方案

是分庫還是分表呢？這要具體分析。

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

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

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

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

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

問題

實際運行中，寫問題不大，主要問題在于唯一 ID 生成、非 partition key 查詢、擴容。

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

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

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

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

1.6.2 讀寫分離

為什么要讀寫分離

分表針對大表解決 CPU 瓶頸，分庫解決 IO 瓶頸，二者將存儲壓力解決了。但查詢還不一定。

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

問題

主要問題有過期讀和分配機制。

過期讀，也就是主從延時問題，這個對于。

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

1.7 小結(jié)

以上列舉了 MySQL 常見慢查詢原因和處理方法，介紹了應(yīng)對較大數(shù)據(jù)場景的常用方法。

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

2. 如何評價 ElasticSearch

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

2.1 可以干什么

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

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

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

該操作可以在 Dev Tools 的控制臺替換為：

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

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

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

在 ES 7.0 之前存儲結(jié)構(gòu)是 Index -》 Type -》 Document，按 MySQL 對比就是 database - table - id（實際這種對比不那么合理）。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é)點所有索引信息

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

以下是截取了某日志 index 下的部分 mapping 結(jié)構(gòu)，ES 對字符串類型會默認(rèn)定義成 text ，同時為它定義一個叫做 keyword 的子字段。這兩的區(qū)別是：text 類型會進(jìn)行分詞， keyword 類型不會進(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，其實就是分詞后的詞了。如果把圖中的 Term Index 去掉，是不是有點像 MySQL 了？Term Dictionary 就像二級索引，但 MySQL 是保存在磁盤上的，檢索一個 term 需要若干次的 random access 磁盤操作。

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

到這就知道為啥快了，就是因為有了內(nèi)存中的 Term Index ， 它為 term 的索引 Term Dictionary 又做了一層索引。

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

2.3.1 分詞后檢索

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

2.3.2 精確檢索

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

2.4 什么時候用 ES

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

2.4.1 全文檢索

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

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

什么意思呢？

分詞

開頭對日志的查詢，鍵入 “我可真是個機靈鬼” 時，只會得到完全匹配的信息。

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

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

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

POST yourindex/_analyze { “field”：“yourfield”， “text”：“我可真是個機靈鬼” }

2.4.2 組合查詢

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

ES + MySQL

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

ES + HBASE

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

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

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

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

2.5 小結(jié)

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

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

3. HBASE

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

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

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

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

3.2 OLTP 和 OLAP

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

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

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

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

3.3 RowKey

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

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

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

3.4 使用場景

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

4. 總結(jié)

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

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

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

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

編輯：jq