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

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

在數(shù)據(jù)量不是很大時,，大多慢查詢可以用索引解決,，大多慢查詢也因為索引不合理而產(chǎn)生。
MySQL 索引基于 B+ 樹,，這句話相信面試都背爛了,，接著就可以問最左前綴索引、 B+ 樹和各種樹了,。
說到最左前綴，實際就是組合索引的使用規(guī)則,，使用合理組合索引可以有效的提高查詢速度,，為什么呢？
因為索引下推,。如果查詢條件包含在了組合索引中,，比如存在組合索引（a,b)，查詢到滿足 a 的記錄后會直接在索引內部判斷 b 是否滿足，減少回表次數(shù),。同時,，如果查詢的列恰好包含在組合索引中，即為覆蓋索引,，無需回表,。索引規(guī)則估計都知道，實際開發(fā)中也會創(chuàng)建和使用,。問題可能更多的是：為什么建了索引還慢,？
1.1.1 什么原因導致索引失效

建了索引還慢，多半是索引失效（未使用）,，可用 explain 分析,。索引失效常見原因有 :
where 中使用 != 或 <> 或 or 或表達式或函數(shù)（左側）
like 語句 % 開頭
字符串未加’’
索引字段區(qū)分度過低，如性別
未匹配最左前綴
(一張嘴就知道老面試題了) 為什么這些做法會導致失效,，成熟的 MySQL 也有自己的想法,。
1.1.2 這些原因為什么導致索引失效

如果要 MySQL 給一個理由，還是那棵 B+ 樹,。
函數(shù)操作

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

隱式類型轉換和隱式字符編碼轉換也會導致這個問題,。
隱式類型轉換對于 JOOQ 這種框架來說一般倒不會出現(xiàn)。
隱式字符編碼轉換在連表查詢時倒可能出現(xiàn),，即連表字段的類型相同但字符編碼不同。
破壞了有序性

至于 Like 語句 % 開頭,、字符串未加 ’’ 原因基本一致,，MySQL 認為對索引字段的操作可能會破壞索引有序性就機智的優(yōu)化掉了,。
不過,，對于如性別這種區(qū)分度過低的字段,，索引失效就不是因為這個原因,。
1.1.3 性別字段為什么不要加索引

為什么索引區(qū)分度低的字段不要加索引,。盲猜效率低,，效率的確低，有時甚至會等于沒加。
對于非聚簇索引,，是要回表的,。假如有 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 位添加索引,，避免不必要的開支。假如的確需要如關鍵字查詢,，那交給更合適的如 ES 或許更好,。
不要對索引字段做函數(shù)操作
對于確定的、寫多讀少的表或者頻繁更新的字段都應該考慮索引的維護成本,。
1.1.5 如何評價 MySQL 選錯了索引

有時,，建立了猛一看挺正確的索引，但事情卻沒按計劃發(fā)展,。就像“為啥 XXX 有索引,，根據(jù)它查詢還是慢查詢”。
此刻沒準要自信點：我的代碼不可能有 BUG,，肯定是 MySQL 出了問題,。MySQL 的確可能有點問題。
這種情況常見于建了一大堆索引，查詢條件一大堆,。沒使用你想讓它用的那一個,，而是選了個區(qū)分度低的，導致過多的掃描,。造成的原因基本有兩個：
信息統(tǒng)計不準確：可以使用 analyze table x重新分析,。
優(yōu)化器誤判：可以 force index強制指定?；蛐薷恼Z句引導優(yōu)化器,，增加或刪除索引繞過。
但根據(jù)我淺薄的經(jīng)驗來看,，更可能是因為你建了些沒必要的索引導致的,。不會真有人以為 MySQL 沒自己機靈吧？
除了上面這些索引原因外,，還有下面這些不常見或者說不好判斷的原因存在,。
1.2 等MDL鎖

在 MySQL 5.5 版本中引入了 MDL，對一個表做 CRUD 操作時,，自動加 MDL 讀鎖,；對表結構做變更時，加 MDL 寫鎖,。讀寫鎖,、寫鎖間互斥。
當某語句拿 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 當前讀

InnoDB 默認級別是可重復讀,。設想一個場景：事物 A 開始事務，事務 B 也開始執(zhí)行大量更新,。B 率先提交,， A 是當前讀，就要依次執(zhí)行 undo log ,，直到找到事務 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 算法進行頁面淘汰,，如果數(shù)據(jù)量過大，對老或非熱點數(shù)據(jù)的查詢可能就會把熱點數(shù)據(jù)給擠出去,。
所以對于大表常見優(yōu)化即是分庫分表和讀寫分離了,。
1.6.1 分庫分表

方案

是分庫還是分表呢？這要具體分析,。
如果磁盤或網(wǎng)絡有 IO 瓶頸,，那就要分庫和垂直分表。
如果是 CPU 瓶頸,，即查詢效率偏低,，水平分表。
水平即切分數(shù)據(jù),，分散原有數(shù)據(jù)到更多的庫表中,。
垂直即按照業(yè)務對庫，按字段對表切分,。
工具方面有 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ù)遷移,。
當然,，如果分庫還會面臨事務一致性和跨庫 join 等問題。
1.6.2 讀寫分離

為什么要讀寫分離

分表針對大表解決 CPU 瓶頸,，分庫解決 IO 瓶頸,，二者將存儲壓力解決了。但查詢還不一定,。
如果落到 DB 的 QPS 還是很高,，且讀遠大于寫，就可以考慮讀寫分離,，基于主從模式將讀的壓力分攤,，避免單機負載過高，同時也保證了高可用,，實現(xiàn)了負載均衡,。
問題

主要問題有過期讀和分配機制。
過期讀,，也就是主從延時問題,，這個對于。
分配機制,，是走主還是從庫,。可以直接代碼中根據(jù)語句類型切換或者使用中間件,。
1.7 小結

以上列舉了 MySQL 常見慢查詢原因和處理方法,，介紹了應對較大數(shù)據(jù)場景的常用方法。
分庫分表和讀寫分離是針對大數(shù)據(jù)或并發(fā)場景的,，同時也為了提高系統(tǒng)的穩(wěn)定和拓展性,。但也不是所有的問題都最適合這么解決,。
2. 如何評價 ElasticSearch

前文有提到對于關鍵字查詢可以使用 ES。那接著聊聊 ES ,。
2.1 可以干什么

ES 是基于 Lucene 的近實時分布式搜索引擎,。使用場景有全文搜索、NoSQL Json 文檔數(shù)據(jù)庫,、監(jiān)控日志,、數(shù)據(jù)采集分析等。
對非數(shù)據(jù)開發(fā)來說,，常用的應該就是全文檢索和日志了,。ES 的使用中,，常和 Logstash, Kibana 結合,，也成為 ELK 。先來瞧瞧日志怎么用的,。
下面是我司日志系統(tǒng)某檢索操作：打開 Kibana 在 Discover 頁面輸入格式如 “xxx” 查詢,。
該操作可以在 Dev Tools 的控制臺替換為：
GET yourIndex/_search
{
"from" : 0, "size" : 10,
"query" : {
"match_phrase" : {
"log" : "xxx"
}
}
}
什么意思？Discover 中加上 “” 和 console 中的 match_phrase 都代表這是一個短語匹配,，意味著只保留那些包含全部搜索詞項,，且位置與搜索詞項相同的文檔。
2.2 ES 的結構

在 ES 7.0 之前存儲結構是 Index -> Type -> Document,，按 MySQL 對比就是 database - table - id(實際這種對比不那么合理),。7.0 之后 Type 被廢棄了，就暫把 index 當做 table 吧,。
在 Dev Tools 的 Console 可以通過以下命令查看一些基本信息,。也可以替換為 crul 命令。
GET /_cat/health?v&pretty：查看集群健康狀態(tài)
GET /_cat/shards?v ：查看分片狀態(tài)
GET yourindex/_mapping :index mapping結構
GET yourindex/_settings :index setting結構
GET /_cat/indices?v :查看當前節(jié)點所有索引信息
重點是 mapping 和 setting ,，mapping 可以理解為 MySQL 中表的結構定義,，setting 負責控制如分片數(shù)量、副本數(shù)量,。
以下是截取了某日志 index 下的部分 mapping 結構,，ES 對字符串類型會默認定義成 text ，同時為它定義一個叫做 keyword 的子字段,。這兩的區(qū)別是：text 類型會進行分詞,， keyword 類型不會進行分詞。
"******": {
"mappings": {
"doc": {
"properties": {
"appname": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
2.3 ES 查詢?yōu)槭裁纯欤?/strong>

分詞是什么意思,？看完 ES 的索引原理你就 get 了,。
ES 基于倒排索引。嘛意思,？傳統(tǒng)索引一般是以文檔 ID 作索引,，以內容作為記錄,。倒排索引相反，根據(jù)已有屬性值,，去找到相應的行所在的位置,，也就是將單詞或內容作為索引，將文檔 ID 作為記錄,。
下圖是 ES 倒排索引的示意圖,，由 Term index，Team Dictionary 和 Posting List 組成,。
圖片

圖中的 Ada,、Sara 被稱作 term，其實就是分詞后的詞了,。如果把圖中的 Term Index 去掉,，是不是有點像 MySQL 了？Term Dictionary 就像二級索引,，但 MySQL 是保存在磁盤上的,，檢索一個 term 需要若干次的 random access 磁盤操作。
而 ES 在 Term Dictionary 基礎上多了層 Term Index ,，它以 FST 形式保存在內存中,，保存著 term 的前綴，借此可以快速的定位到 Term dictionary 的本 term 的 offset ,。而且 FST 形式和 Term dictionary 的 block 存儲方式都很節(jié)省內存和磁盤空間,。
到這就知道為啥快了，就是因為有了內存中的 Term Index , 它為 term 的索引 Term Dictionary 又做了一層索引,。
不過,，也不是說 ES 什么查詢都比 MySQL 快。檢索大致分為兩類,。
2.3.1 分詞后檢索

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

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

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

在 MySQL 中字符串類型根據(jù)關鍵字模糊查詢就是一場災難，對 ES 來說卻是小菜一碟。具體場景,，比如消息表對消息內容的模糊查詢,，即聊天記錄查詢。
但要注意,，如果需要的是類似廣大搜索引擎的關鍵字查詢而非日志的短語匹配查詢,，就需要對中文進行分詞處理，最廣泛使用的是 ik ,。Ik 分詞器的安裝這里不再細說,。
什么意思呢？
分詞

開頭對日志的查詢,，鍵入 “我可真是個機靈鬼” 時,，只會得到完全匹配的信息。
而倘若去掉 “”,，又會得到按照 “我”,、“可”，“真”….分詞匹配到的所有信息,，這明顯會返回很多信息,，也是不符合中文語義的,。實際期望的分詞效果大概是“我”,、“可”、“真是”,，“機靈鬼”,，之后再按照這種分詞結果去匹配查詢。
這是 ES 默認的分詞策略對中文的支持不友善導致的,，按照英語單詞字母來了,，可英語單詞間是帶有空格的。這也是不少國外軟件中文搜索效果不 nice 的原因之一,。
對于該問題,，你可以在 console 使用下方命令，測試當前 index 的分詞效果,。
POST yourindex/_analyze
{
"field":"yourfield",
"text":"我可真是個機靈鬼"
}
2.4.2 組合查詢

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

1. ES + MySQL

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

如果要省去分庫分表什么的，或許可以拋棄 MySQL ,，選擇分布式數(shù)據(jù)庫,，比如 HBASE , 對于這種 NOSQL 來說，存儲能力海量,，擴容 easy ,，根據(jù) rowkey 查詢也很快。
以上思路都是經(jīng)典的索引與數(shù)據(jù)存儲隔離的方案了,。
當然,，攤子越大越容易出事，也會面臨更多的問題,。使用 ES 作索引層,，數(shù)據(jù)同步、時序性,、mapping 設計,、高可用等都需要考慮。
畢竟和單純做日志系統(tǒng)對比,，日志可以等待,，用戶不能。
2.5 小結

本節(jié)簡單介紹了 ES 為啥快,，和這個快能用在哪?，F(xiàn)在你可以打開 Kibana 的控制臺試一試了。
如果想在 Java 項目中接入的話,，有 SpringBoot 加持,，在 ES 環(huán)境 OK 的前提下，完全是開箱即用,，就差一個依賴了,。基本的 CRUD 支持都是完全 OK 的,。
3. HBASE

前面有提到 HBASE , 什么是 HBASE ,，鑒于篇幅這里簡單說說。
3.1 存儲結構

關系型數(shù)據(jù)庫如 MySQL 是按行來的,。
姓名小學中學大學
李某XX小學YY中學NULL
HBASE 是按列的（實際是列族）,。列式存儲上表就會變成：
姓名學校名稱
李某XX小學
李某YY中學

下圖是一個 HBASE 實際的表模型結構。圖片
Row key 是主鍵,，按照字典序排序,。TimeStamp 是版本號。info 和 area 都是列簇（column Family），列簇將表進行橫向切割,。name,、age 叫做列，屬于某一個列簇,，可進行動態(tài)添加,。Cell 是具體的 Value 。
3.2 OLTP 和 OLAP

數(shù)據(jù)處理大致可分成兩大類：聯(lián)機事務處理OLTP（on-line transaction processing）,、聯(lián)機分析處理OLAP（On-Line Analytical Processing）,。
OLTP是傳統(tǒng)的關系型數(shù)據(jù)庫的主要應用，主要是基本的,、日常的事務處理,。
OLAP是數(shù)據(jù)倉庫系統(tǒng)的主要應用，支持復雜分析,，側重決策支持,，提供直觀易懂的查詢結果。
面向列的適合做 OLAP,，面向行的適用于聯(lián)機事務處理(OLTP),。不過 HBASE 并不是 OLAP ，他沒有 transaction,，實際上也是面向 CF 的,。一般也沒多少人用 HBASE 做 OLAP 。
3.3 RowKey

HBASE 表設計的好不好,，就看 RowKey 設計,。這是因為 HBASE 只支持三種查詢方式
1,、基于 Rowkey 的單行查詢 2,、基于 Rowkey 的范圍掃描 3、全表掃描
可見 HBASE 并不支持復雜查詢,。
3.4 使用場景

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

個人覺得軟件開發(fā)是循序漸進的，技術服務于項目,，合適比新穎復雜更重要,。
如何完成一次快速的查詢？最該做的還是先找找自己的 Bug，解決了當前問題再創(chuàng)造新問題,。
本文列舉到的部分方案對于具體實現(xiàn)大多一筆帶過,，實際無論是 MySQL 的分表還是 ES 的業(yè)務融合都會面臨很多細節(jié)和困難的問題，搞工程的總要絕知此事要躬行,。
參考

億級流量系統(tǒng)架構之如何設計每秒十萬查詢的高并發(fā)架構 https:///post/5bfe771251882509a7681b3a
使用 ELK 搭建日志集中分析平臺 https://wsgzao./post/elk/)https://wsgzao./post/elk/
MySQL和Lucene索引對比分析https://www.cnblogs.com/luxiaoxun/p/5452502.html
HBASE 深入淺出 https://www.ibm.com/developerworks/cn/analytics/library/ba-cn-bigdata-hbase/index.html
作者：llc687
https:///post/如何完成一次快速的查詢