一種提升SQL改寫效率的方法

本文分享自天翼雲開發者社群《 一種提升SQL改寫效率的方法》 , 作者:唐****律

一、 背景

SQL改寫是資料庫產品中使用比較頻繁的一個技術，在大多數產品中的呼叫頻率也非常高，通常對效能的需求需要接近對應資料庫產品的上限。例如在天翼雲關係型資料庫中的Mysql語法相容元件，其效能測試標準需要達到接近30萬TPS，也意味著SQL改寫環節的效能標準需要支援至少每秒30萬次以上，否則會成為系統的效能瓶頸。

SQL改寫的基礎是抽象語法樹，而抽象語法樹則是由SQL字串經過詞法分析和語法分析之後得到的。詞法分析器和語法分析器在市面上有非常多的種類可供挑選，例如Lexer、YACC、Antlr、Druid等，一般資料庫產品都只在其基礎上進行SQL改寫,例如基於C語言開發的PG的分散式資料庫外掛Citus。對於一些Java語言開發的資料庫產品，SQL解析的效能則會有所下降，有的資料庫會在此基礎上再進行最佳化，例如分散式資料庫Mycat，則是在Druid的基礎上再加入了一個SQL快取，用以減輕SQL解析和改寫的代價。但是這對於一些對於SQL改寫需求特別大或者請求語句特別複雜的資料庫產品來說還是不夠的，例如在天翼雲關係型資料庫中的Mysql語法相容元件中，SQL改寫過程中需要進行後設資料收集、型別推斷、萬用字元分析、子查詢和巢狀查詢處理、別名分析和修正、型別適配、隱式型別轉換、系統引數計算、以及近50條語法相容規則，這樣一來，每次進行SQL改寫就是一個非常大的開銷，因此需要對SQL改寫環節進行效能最佳化。

對於此類問題，業界也有一些的解決方案，例如分散式資料庫 Mycat，它使用快取對SQL改寫進行效能最佳化，以SQL為key對抽象語法樹進行快取，減輕了部分SQL解析的負擔，特點是快取命中率低，效能提升有限，消耗記憶體大。

二、 方案

本方案以提高解析能力為目標，從快取方向出發，考慮如何提高快取命中率，以減少不必要的效能消耗。結合應用在使用 SQL的過程中的主體結構不輕易改變的特性，使用引數化SQL作為快取key，處理過程中預先對SQL進行詞法分析，分解為引數化SQL和引數列表，並以引數化SQL為key對抽象語法樹進行快取。如果快取未命中，則對引數化SQL依次進行詞法分析、語法分析、改寫處理，最後在改寫完畢之後，再結合先前記錄的引數生成目標SQL，即完成完整的SQL改寫過程。

這個方案減少了大部分的效能消耗，快取命中率高，記憶體消耗小，大幅提升了效能，其核心邏輯是以額外的效能消耗極小的詞法分析和引數化環節為代價，大幅縮短了效能消耗極高的抽象語法樹改寫過程。

需要注意的是，該方案的應用對改寫環節提出了更高的要求，開發者需要預見引數在整個改寫過程中的作用並進行正確的處理。舉例來說，在分散式資料庫中有一些 SQL改寫，需要依據過濾條件的值的hash值，來決定將哪些SQL分發到哪些資料節點，這個時候由於過濾條件的值已被引數化，所以SQL改寫過程中就不能直接決定其需要分發的節點了，而是要改為在最後結合引數生成目標SQL的時候計算分發的節點。

三、 優點

本方案提出一種提升 SQL改寫效率的方法，透過預先對SQL進行詞法分析，分解為引數化SQL和引數列表，並以引數化SQL為key對抽象語法樹進行快取，然後進行抽象語法樹改寫，最後再結合引數列表生成目標SQL，大幅提升了快取命中率和SQL改寫效率。

經過相同環境下的測試對比，可知本方案在提高 SQL改寫效率方面產生了巨大的提升，並且由於測試樣本較少，快取命中率更高的方案顯然會在實際應用場景中獲得更大的優勢。表1為3種方案對於SQL改寫的效能對比：從天翼云云電腦生產環境中隨機摘取100萬條資料對其進行Mysql語法到PostgreSQL語法的改寫，在Intel Core i7-6700 CPU 和24GB記憶體的測試環境下，各使用10個執行緒分別按上述3個方案進行測試。