mysql递归查询所有上下节点_【转】MySQL之Spider存储引擎原理详解
一、概述
Spider是為MySQL/MariaDB開發的一個特殊引擎，具有內嵌分片功能。MariaDB從10.0.4開始支持Spider。作為MariaDB的一個新的主要特性。Spider的主要功能是將數據分散到多個后端節點，它的作用類似于一個代理。
Spider有三個作用：
MySQL和Spider的架構圖：
Spider是由日本的MySQL開發者Kentoku Shiba創建和開發的存儲引擎，騰訊數據庫團隊貢獻了非常多Patch。據說騰訊游戲、騰訊支付等有大規模使用。
二、表鏈接
Spider的表鏈接的技術參考ISO/IEC 9075-9:2008 SQL/MED標準。利用Spider的這個特性，你可以像操作本地MariaDB實例的表一樣來操作分布在多個MariaDB實例上的表。
Spider表是個虛擬表，本身不存儲數據。當創建一個Spider存儲引擎的表時，該表指向單個或多個后端MariaDB實例上對應的表。后端實例上的表可以是任何存儲引擎的表。Spider的系統表spider_tables記錄了各個數據分片的實例位置、連接、狀態等信息（如下圖中TABLE和PART部分）。該系統表可以便利Spider跨節點的join操作：訪問數據所在的機器，然后把數據拉取到本地進行join操作；如果進行join操作字段不是分片字段，那么需要廣播SQL語句將數據拉取到Spider節點進行join操作。
在執行CREATE TABLE命令創建Spider引擎的表時，通過添加COMMENT或CONNECTION語法來指定后端實例的地址等信息。多個實例時，通過PARTITION語法來指定。
在Spider節點，表字段定義可以忽略。Spider第一次訪問表的時候，如果發現沒有表字段定義，會從后端節點拉取相關元數據，然后緩存在本地。
2.1 水平分表
水平分表應該是最常用的模式，類似于現有MySQL中間件的分庫分表，或者手動分庫分表。可以將一張表拆分為多個分區，每個分區保存一部分數據。如下所示：
Spider支持MySQL/MariaDB所有類型的分區表，有Range、Hash、List、Key分區。下面演示的基于Range分區。
例如，在2個節點上創建如下表：
然后在spider節點上創建如下表來鏈接2個節點的表：
CREATE2.2 垂直分表
垂直分表可以使用在多個不同的場景，包括將多個數據庫實例的表映射到同個數據庫實例上。這既可以實現跨表Join，又可以起到數據垂直拆分的效果。如下圖所示：
業務將用戶的不同類型信息user_info、user_msg、user_detail和user_log分別保存到不同實例上實現垂直擴展，業務可以直接訪問這些表進行DML操作，相互間沒有干擾，這樣可以解除單臺服務器的存儲和計算資源瓶頸。但畢竟它們都屬于用戶信息，有多表查詢的需求，所以可以將這些表映射到獨立的Spider實例上，業務通過Spider實例進行普通查詢操作，包括跨表的各種Join，還可以在Spider實例上進行各種統計和分析操作。當然，業務也可以通過Spider實例統一對這些表進行DML操作，通過部署多個Spider實例實現訪問的負載均衡。
例如，先在兩個后端節點分別創建如下的一個表：
然后在spider節點上鏈表兩個后端節點：
CREATE三、事務
Spider分別針對單機事務與XA事務實現了相應的操作事務的方法。下圖列出了兩者部分實現的方法。
上述方法的主要實現是向后端節點發送消息，有些階段同時需要執行記錄系統表的行為。Spider依賴后端數據節點保證事務的持久性以及隔離性。它只負責開啟事務，以及在適當的時機發送提交或者回滾事務的命令。如果單機事務涉及多個數據節點，Spider需要將相應的連接保存在隊列中。在事務提交或者回滾的時候，逐個發送相應的命令。
3.1 分布式事務DTP/XA模型
Spider分布式XA事務的實現參照了分布式事務DTP/XA模型（如下圖）。在這個模型中，有三個角色：
AP通過RM API來操作和管理資源，通過TM接口開啟/終止/結束事務。RM與TM之間需要實現XA接口。XA接口定義了兩階段提交的必要步驟，以及RM與TM之間需要進行的交互。
其兩階段提交步驟如下：
DTP/XA模型圖示：
3.2 Spider事務的實現
Spider扮演的是TM角色，而后端的數據節點扮演的是RM的角色。
在Spider中，XA事務分別有四種狀態，如下圖，對應于NOT YET，PREPAED，ROLLBACK以及COMMITTED。Spider在開始PREPARE階段之際會在系統表spider_xa中標記該XA事務的狀態為NOT YET。在所有數據節點都接收到PREPARE消息以后，該XA事務的狀態進入到PREPARED階段。假如在PREPARE階段，某一個數據節點發生故障，那么Spider會回滾該事務。相應地，事務的狀態變成ROLLBACK。
最后，如果所有參與事務的節點都返回PREPARE OK，該事務進入提交階段。
執行XA事務，Spider與兩個后端節點的詳細交互步驟：
從上圖可以看到Spider向后端節點發送XA START命令時會設置會話級別的事務特性，同時將XA事務ID發送到后端節點。因為XA事務ID由三部分組成，Spider會將這三個部分的解析出來，然后拼接成對應的字符串發送到后端節點。為了節省網絡開銷，Spider將XA END與XA PREPARE命令合并起來一起發送。也就是在這個階段初始，Spider在系統表里面記錄事務的狀態。如果所有的RM都返回OK，那么Spider進入PREPARED狀態，準備提交事務。否則，事務進入到回滾狀態。
四、讀寫流程
為了更清楚地了解Spider的讀寫流程，我們有必要研究一下數據庫系統的查詢執行模型，以及MySQL的插拔式引擎如何跟這個模型對接的。
數據庫系統基本都采用迭代器模型處理查詢，也叫volcano查詢執行引擎（發明這個詞的學者大概是因為查詢執行計劃樹看起來像一座火山，如下圖）。執行計劃樹的上層節點通過get_next方法驅動子節點獲取一條元組，子節點遞歸調用。在葉子節點也就是基本表將數據返回。
這個模型的一個好處就是實現起來很優雅，同時數據流與控制流結合在一起方便程序的調試。這個模型的缺點是函數的大量調用使得進程/線程上下文切換頻繁，程序的局部性受到損害。因此，后來針對OLAP場景，采用了向量查詢執行模型來減少進程上下文的切換以及保證保證高速緩存的命中率。
再次以下圖為例子，圖中的SQL語句的功能是查詢一個部門的平均薪資。假如在職工表EMP的員工ID字段Dno上存在索引，MySQL在Server層針對該查詢語句生成的查詢計劃如下:順序掃描部門表，通過索引訪問職工表，然后在兩表join操作之后進行投影操作。下一個階段為分組排序操作。上層的操作算子（例如join），驅動子節點調用get_next方法（表掃描方法）獲取一條元組。底層操作算子（表訪問方法，handler接口定義）將數據返回。至此，我們可以總結一下MySQL體系的工作原理：查詢執行計劃由MySQL Server層生成，存儲引擎受執行計劃驅動而訪問表。MySQL的handler已經定義好表的訪問方法，實現了這些訪問方法的存儲引擎就可以作為MySQL的插件式引擎而存在。
查詢計劃樹示例：
下面我們對Spider的讀寫流程結合Server層代碼進行分析。
4.1 SELECT操作
上面提到Spider的作用類似一個proxy，本身并不存儲數據。因此Spider處理SELECT語句（UPDATE與DELETE類似）首先需要根據查詢解析的信息生成一個SELECT語句，發送到查詢涉及的后端節點，將數據從遠端拉到本地，然后進行處理。函數spider_db_append_select_columns根據查詢涉及的讀集以及寫集獲取相應的字段，構造一個SQL語句從后端節點拉取數據到本地。如果涉及多個分片，spider將從不同實例獲取過來的結果集存放在不同的結果集spider_db_result中。類spider_db_fetch提供了fetch_next, current_row等方法供上層方法調用。Server層調用get_next方法驅動引擎層獲取下一條數據。
對于表訪問方法，MySQL實現了索引掃描(ha_index_read)與隨機訪問（ha_rnd_next）的方法。對于切分為多個分片的DB，索引掃描需要借助優先隊列。索引掃描需要區分是否是第一次調用該方法。如果是第一次調用該方法，需要遍歷所有的分片讀取一條記錄，然后插入到優先隊列。對應到Spider，如果第一次調用訪問遠端實例表的方法，需要生成SELECT語句，將遠端實例的數據拉到本地存放。在使用索引掃描的情況，MySQL 為每個分片保留一個key buffer以及record buffer。server 利用隊列頭部的m_top_entry 獲得訪問的分片ID。接著，調用get_next方法獲取相應的元組，將返回的數據存放在record buffer，并插入到優先隊列。函數最后將元組從優先隊列返回。
為緩解內存等資源的壓力，Spider實現全表掃描的方法是逐個分片串行掃描（為了加速，spider也提供了并行掃描數據節點的選項）。如下兩圖給出了Spider對于上述兩種表訪問方法的實現機制。
索引掃描實現：
全表掃描：
4.2 INSERT操作
MySQL的handler類對于INSERT操作提供的接口函數的名字是write_row。存儲引擎想要支持INSERT操作就必須實現write_row方法。Spider對于write_row方法的實現是簡單地根據查詢解析的信息拼接一條INSERT語句，發往后端節點處理。如果是批量插入操作則需要與MySQL Server層配合，將INSERT語句批量發到后端節點。
下圖結合一條批量插入的INSERT語句給出MySQL中INSERT操作的具體實現。
mysql_insert調用write_row執行具體的插入操作（第8行）。這是存儲引擎必須實現的方法。對應于spider，spider根據查詢涉及到的列（field）拼成一條INSERT語句（如果是分片數據庫，VALUSE中的列必須包含分區鍵，分區鍵是自增列的情況除外）。圖9中的QUERY將用戶ID（ID）和用戶名（Name）插入到user表，其中ID是分區鍵。mysql_insert根據VALUES包含的元組數目，判斷是否需要進行批量插入操作。該例子的QUERY的VALUES包含4條元組，所有需要進行批量插入操作。MySQL循環調用write_row方法觸發spider生成INSERT語句。Spider的write_row方法實現中會根據分區鍵將INSERT語句進行分組（第5行~第9行）。圖9給出的實例只有兩個數據分片，所以SQL語句被分成兩組。處理完VALUES以后，Spider的INSERT語句也拼接完成。
ha_end_bulk_insert方法通知Spider完成VALUES處理。此時，Spider將INSERT發送到后端節點進行處理（第11行）。
4.3 DELETE實現
Spider想要支持DELETE操作必須實現MySQL handler類提供的ha_delete_row方法。與INSERT操作不同，DELETE操作需要生成一條SELECT語句將查詢涉及的分區鍵拉到Spider節點。這是因為MySQL Server層的“once-a-tuple”的查詢執行模型（實際上基本所有的關系數據庫系統都采用該模型）會驅動Spider逐個拼接DELETE語句，然后發往后端節點。這時候，Spider需要知道對應的DELETE語句該往哪個后端節點發送。為了減少網絡開銷，Spider提供了批量發送DELETE語句的功能。
Spiderpider中delete的實現如下：
MySQL Server層首先確定表的訪問方法：采用索引掃描或者全部掃描（第5行）？DELETE方法需要執行一次查找操作，調用get_next方法(info.read_record)獲取一條元組（第10行）。Spider需要判斷是否第一次調用get_next方法。如果是的話，則需要生成SELECT語句，將數據節點的數據拉到本地。否則，Spider直接從本地返回數據給上層調用者。接下來，Server層調用ha_delete_row方法將數據刪除。這是存儲引擎需要具體實現的方法。由于Spider本身并不存儲數據的緣故，其實現delete操作主要思想是利用從后端節點拉取過來的數據（分區鍵，過濾條件等），拼接成一條DELETE語句。然后，發送該請求到數據節點。Spider為了優化網絡開銷，提供了批量發送DELETE語句的選項。
UPDATE操作的實現類似DELETE，都需要Spider生成SELECT語句從后端節點拉取數據。只不過，UPDATE在更新區分鍵的時候，可能需要多一次DELETE操作（刪除原來分區的數據，將新的數據插入到不同的分區）。
4.4 其他說明
在Spider表上進行的DML和查詢操作均下發到后端數據節點。在HA（高可用，后面會介紹）模式下，Spider能夠進行負責均衡，將查詢請求分攤到不同的數據幾點上。對于DML操作，不管是HA模式還是分表模式，Spider節點均對數據節點進行并發地數據增刪改。涉及到多個數據節點的DML操作，Spider基于兩階段事務實現數據的一致性。
對于DDL操作情況有些不一樣。對于Spider表進行drop table操作不會影響后端數據節點，也就是說數據節點對應的表不會被刪除。類似地，對Spider節點進行alter table加索引或刪索引等操作也不會影響后端數據。但若執行truncate table，則后端數據節點的表也會被清空。
五、性能優化
為了提高性能，還支持多種功能，包括使用DirectSQL直接操作后端數據節點，各種下推（push down）優化等。
5.1 DirectSQL
Spider作為MySQL的一個可插拔引擎，實現了handler類定義的相應的存取方法。Spider本身并不存放數據，而是類似一個代理的功能將訪問請求路由到后端的數據節點。Spider提供了兩種途徑訪問后端節點存儲的數據。
MySQL體系下的Spider：
如上圖所示，Spider可以遵循MySQL傳統的查詢處理流程來訪問數據，也開發了自有的一套來加速數據訪問。在傳統的查詢處理方式下，SQL查詢請求經過查詢解析、查詢重寫、查詢優化等步驟。按照生成的查詢執行計劃，Spider從后端節點拉取數據，交給MySQL服務器處理。Spider在這種查詢處理框架之下的一個缺點是不能很好地利用后端節點可并行化特性，同時需要對SQL查詢進行兩次解析，帶來的性能損耗問題比較嚴重。
有測試表明，傳統處理方式性能損耗約50%左右。基于這個原因，為了加速聚集、統計等查詢，Spider開發團隊提供了DirectSQL方式執行查詢。DirectSQL的原理類似于Map Reduce方案，將查詢直接下發到后端節點，無需在MySQL服務器層進行解析（Map階段）；后端節點將結果返回給Spider，由Spider合并結果集（Reduce階段）。這個方式很好地利用后端節點可并行處理查詢的特點，消除重復解析SQL語句的行為。
Spider提供了2個DirectSQL，分別是SPIDER_DIRECT_SQL和SPIDER_BG_DIRECT_SQL。其用法如下：
DirectSQL它會直接在parameters參數指定的遠端節點執行sql參數指定的SQL，并將結果保存在tmp_table_list指定的臨時表上，parameters一般指定為在mysql.servers中定義的一到多個后端數據節點server。類似于MapReduce機制，直接將SQL并行發送給指定的server（Map過程），將結果集返回給Spider節點，由其經過處理后返回給客戶端（Reduce階段）。操作如下所示：
SELECT5.2 下推（Push Down）
5.2.1 聚合下推
將min、max、avg、count和sum等常用的聚合操作直接下推到數據節點，再聚合數據節點返回的結果。
5.2.2 Update/Delete下推
如果是批量更新或刪除，Spider將整個Update/Delete操作下推到數據節點，而不是在Spider節點解析Values后逐條下發到數據節點。
5.2.3 Join下推
與聚合操作、DML操作類似，Join操作也可以直接下推到數據節點，Spider僅做結果集處理。除此之外，Spider還支持引擎條件（Engine Condition）下推、索引hints下推等，這些下推操作即減少了Spider節點和數據節點重復的SQL解析優化，又減少了返回給Spider的結果集大小，可以節省系統的資源消耗，并提高實例的性能。
六、高可用
Spider支持分區加HA（High Availability）的部署方式，實現數據水平擴展的同時實現數據高可靠和高可用。部署模式參考如下：
在上圖中，部署3個Spider節點用于服務高可用和負載均衡，分布表TABLE1有3個分區，Spider下面有3個數據節點，每個數據節點保存2個分區的數據。每個分區的數據均放置在2個數據節點上。多個數據節點的同一個分區也是通過兩階段提交來保證數據一致。
下面部署方式示例：
七、其他
7.1 性能
下圖是MariaDB官方文檔中提供的性能指標。
上圖為sysbench只讀場景下，MariaDB（InnoDB）、Spider和其他Proxy套件的性能對比，可以發現在低并發場景下Spider處于弱勢，但并發數達到128以上，除了Spider和HaProxy，MariaDB和MySQL-Proxy性能均有急劇下降。
上圖為讀寫操作在不同Spider表數據冗余下的性能對比，可以發現副本多了對讀幫助較大，對寫影響較小。
7.2 優缺點
Spider最大的優勢是在數據庫進程內部實現了分庫分表和跨節點的表復雜查詢，這樣容易做到跟MySQL協議的100%兼容。依托于成熟而靈活的MySQL插件式存儲引擎框架，可以進一步堆疊/擴展更多高級功能，比如后端節點的探活和自動切換，多個Spider節點的負載均衡等。可以說，基于引擎插件層進行MySQL功能特性擴展是個非常不錯的方向。
Spider的缺點在于用戶數還是太少，功能集未經過大規模驗證，成熟度不高。此外文檔偏少，功能也還不夠豐富。Spider的優勢：
a、對業務透明，業務程序可以不用修改或做非常少的修改。業務連接到Spider節點，具體訪問哪個數據節點由Spider處理，無需放在業務邏輯中；
b、方便水平擴展，能解決單個服務器的計算和存儲資源瓶頸問題。擴展能力可以是無窮的；
c、支持多種后端數據節點類型，比如后端節點可以是MariaDB，也可以是MySQL，甚至是Oracle（MariaDB目前暫不支持）；
d、拆分方式靈活。可以根據需求實現垂直拆分和水平拆分功能，水平拆分支持基于分區表，可支持哈希、范圍、列表等算法；
e、完全兼容MySQL協議，由于Spider位于存儲引擎層，基于分區表實現數據拆分，所以MySQL的所有SQL在Spider均可以進行適配。這是Spider相比傳統的分庫分表中間件非常大的優勢；
f、可透明使用后端數據節點的多種功能特性。比如全文索引，地理位置索引等。Spider的劣勢：
a、Spider的表不支持查詢緩存；
b、Spider實例一致性物理備份困難。需要到每個數據節點單獨進行物理備份，整個實例的一致性物理備份不好做；
c、Spider本身是單點。可通過MySQL復制來實現高可用，但需為業務提供VIP實現Spider節點切換后的服務可用性；
d、性能不如單MariaDB/MySQL實例。這是由于用戶請求多跨一層網絡，性能上會有一些損耗。尤其是在跨分區、跨表查詢時。
e、線上使用案例較少，功能穩定性還未得到全面驗證。比如筆者在某些部署模式下使用XA事務會報錯，在執行DirectSQL時會導致mysqld crash。
八、總結
Spider的最大亮點是為MySQL的使用者提供分庫分表的中間件解決方案，同時在SQL語法上兼容MySQL。這得益于Spider作為MySQL的插拔式引擎而存在。Spider是一個proxy，其本身并沒有存儲數據，因此上層的讀寫表請求需要轉換成SQL語句，重新路由到后端的數據節點。相比其它的中間件解決方案，Spider的查詢解析次數都是兩次，并沒有過多開銷。此外，Spider還針對聚集、排序等操作提供了MAP REDUCE的解決方案。
Spider部署非常簡單，使用方便。可以使用在三類業務場景：一是業務存在多個數據庫實例，某些實例有訪問其他實例上數據的需求（比如做Join）；二是分庫分表場景，比如業務存在超大表，或者業務的數據量一直在增加導致單數據庫實例無法容納，或者業務的并發請求非常多，單實例無法處理；三是作為Proxy，這個場景非常多，比如使用Spider實例作為堡壘機，外部業務均訪問Spider節點，不直接訪問后端數據庫服務，提高網絡安全性。
總之，從兼容性、性能上衡量，Spider是MySQL分庫分表一個不錯的選項。
九、參考
總結
以上是生活随笔為你收集整理的mysql递归查询所有上下节点_【转】MySQL之Spider存储引擎原理详解的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： python一些常用方法_python
- 下一篇： 接口超时后程序还会继续执行嘛_答网友问：