㈠ sql 如何進行字元串反轉 語句怎麼寫
首先，反轉函數reverse的字面意思就是反向輸出輸入的字元串
例子如下：
select reverse('abcd')
----
dcba
其次，資料庫索引中的反向索引（reverse）在某些特定情況下可以極大的優化執行效率。
比如對於哈希分布的數據列 100001,100004,100010,100008,103009
如果用常規索引，其數據可能分布在同一個塊上，查詢效率低
如果用反向索引能很好的均勻分布在各個數據塊上，能更好的運用資料庫的並行度
總體而言，該函數不常用，但是能在需要優化的情況下多一個方法也是不錯的
㈡ 影響數據檢索效率的幾個因素
影響數據檢索效率的幾個因素
數據檢索有兩種主要形態。第一種是純資料庫型的。典型的結構是一個關系型數據，比如 mysql。用戶通過 SQL 表達出所需要的數據，mysql 把 SQL 翻譯成物理的數據檢索動作返回結果。第二種形態是現在越來越流行的大數據玩家的玩法。典型的結構是有一個分區的數據存儲，最初這種存儲就是原始的 HDFS，後來開逐步有人在 HDFS 上加上索引的支持，或者乾脆用 Elasticsearc 這樣的數據存儲。然後在存儲之上有一個分布式的實時計算層，比如 Hive 或者 Spark SQL。用戶用 Hive SQL 提交給計算層，計算層從存儲里拉取出數據，進行計算之後返回給用戶。這種大數據的玩法起初是因為 SQL 有很多 ad-hoc 查詢是滿足不了的，乾脆讓用戶自己寫 map/rece 想怎麼算都可以了。但是後來玩大了之後，越來越多的人覺得這些 Hive 之類的方案查詢效率怎麼那麼低下啊。於是一個又一個項目開始去優化這些大數據計算框架的查詢性能。這些優化手段和經典的資料庫優化到今天的手段是沒有什麼兩樣的，很多公司打著搞計算引擎的旗號干著重新發明資料庫的活。所以，回歸本質，影響數據檢索效率的就那麼幾個因素。我們不妨來看一看。
數據檢索乾的是什麼事情
定位 => 載入 => 變換
找到所需要的數據，把數據從遠程或者磁碟載入到內存中。按照規則進行變換，比如按某個欄位group by，取另外一個欄位的sum之類的計算。
影響效率的四個因素
讀取更少的數據
數據本地化，充分遵循底層硬體的限制設計架構
更多的機器
更高效率的計算和計算的物理實現
原則上的四點描述是非常抽象的。我們具體來看這些點映射到實際的資料庫中都是一些什麼樣的優化措施。
讀取更少的數據
數據越少，檢索需要的時間當然越少了。在考慮所有技術手段之前，最有效果的恐怕是從業務的角度審視一下我們是否需要從那麼多的數據中檢索出結果來。有沒有可能用更少的數據達到同樣的效果。減少的數據量的兩個手段，聚合和抽樣。如果在入庫之前把數據就做了聚合或者抽樣，是不是可以極大地減少查詢所需要的時間，同時效果上並無多少差異呢？極端情況下，如果需要的是一天的總訪問量，比如有1個億。查詢的時候去數1億行肯定快不了。但是如果統計好了一天的總訪問量，查詢的時候只需要取得一條記錄就可以知道今天有1個億的人訪問了。
索引是一種非常常見的減少數據讀取量的策略了。一般的按行存儲的關系型資料庫都會有一個主鍵。用這個主鍵可以非常快速的查找到對應的行。KV存儲也是這樣，按照Key可以快速地找到對應的Value。可以理解為一個Hashmap。但是一旦查詢的時候不是用主鍵，而是另外一個欄位。那麼最糟糕的情況就是進行一次全表的掃描了，也就是把所有的數據都讀取出來，然後看要的數據到底在哪裡，這就不可能快了。減少數據讀取量的最佳方案就是，建立一個類似字典一樣的查找表，當我們找 username=wentao 的時候，可以列舉出所有有 wentao 作為用戶名的行的主鍵。然後拿這些主鍵去行存儲（就是那個hashmap）里撈數據，就一撈一個准了。
談到索引就不得不談一下一個查詢使用了兩個欄位，如何使用兩個索引的問題。mysql的行為可以代表大部分主流資料庫的處理方式：
基本上來說，經驗表明有多個單欄位的索引，最後資料庫會選一最優的來使用。其餘欄位的過濾仍然是通過數據讀取到內存之後，用predicate去判斷的。也就是無法減少數據的讀取量。
在這個方面基於inverted index的數據就非常有特點。一個是Elasticsearch為代表的lucene系的資料庫。另外一個是新銳的druid資料庫。
效果就是，這些資料庫可以把單欄位的filter結果緩存起來。多個欄位的查詢可以把之前緩存的結果直接拿過來做 AND 或者 OR 操作。
索引存在的必要是因為主存儲沒有提供直接的快速定位的能力。如果訪問的就是資料庫的主鍵，那麼需要讀取的數據也就非常少了。另外一個變種就是支持遍歷的主鍵，比如hbase的rowkey。如果查詢的是一個基於rowkey的范圍，那麼像hbase這樣的資料庫就可以支持只讀取到這個范圍內的數據，而不用讀取不再這個范圍內的額外數據，從而提高速度。這種加速的方式就是利用了主存儲自身的物理分布的特性。另外一個更常見的場景就是 partition。比如 mysql 或者 postgresql 都支持分區表的概念。當我們建立了分區表之後，查找的條件如果可以過濾出分區，那麼可以大幅減少需要讀取的數據量。比 partition 更細粒度一些的是 clustered index。它其實不是一個索引（二級索引），它是改變了數據在主存儲內的排列方式，讓相同clustered key的數據彼此緊挨著放在一起，從而在查詢的時候避免掃描到無關的數據。比 partition 更粗一些的是分庫分表分文件。比如我們可以一天建立一張表，查詢的時候先定位到表，再執行 SQL。比如 graphite 給每個 metric 創建一個文件存放採集來的 data point，查詢的時候給定metric 就可以定位到一個文件，然後只讀取這個文件的數據。
另外還有一點就是按行存儲和按列存儲的區別。按列存儲的時候，每個列是一個獨立的文件。查詢用到了哪幾個列就打開哪幾個列的文件，沒有用到的列的數據碰都不會碰到。反觀按行存儲，一張中的所有欄位是彼此緊挨在磁碟上的。一個表如果有100個欄位，哪怕只選取其中的一個欄位，在掃描磁碟的時候其餘99個欄位的數據仍然會被掃描到的。
考慮一個具體的案例，時間序列數據。如何使用讀取更少的數據的策略來提高檢索的效率呢？首先，我們可以保證入庫的時間粒度，維度粒度是正好是查詢所需要的。如果查詢需要的是5分鍾數據，但是入庫的是1分鍾的，那麼就可以先聚合成5分鍾的再存入資料庫。對於主存儲的物理布局選擇，如果查詢總是針對一個時間范圍的。那麼把 timestamp 做為 hbase 的 rowkey，或者 mysql 的 clustered index 是合適。這樣我們按時間過濾的時候，選擇到的是一堆連續的數據，不用讀取之後再過濾掉不符合條件的數據。但是如果在一個時間范圍內有很多中數據，比如1萬個IP，那麼即便是查1個IP的數據也需要把1萬個IP的數據都讀取出來。所以可以把 IP 維度也編碼到 rowkey 或者 clustered index 中。但是假如另外還有一個維度是 OS，那麼查詢的時候 IP 維度的 rowkey 是沒有幫助的，仍然是要把所有的數據都查出來。這就是僅依靠主存儲是無法滿足各種查詢條件下都能夠讀取更少的數據的原因。所以，二級索引是必要的。我們可以把時間序列中的所有維度都拿出來建立索引，然後查詢的時候如果指定了維度，就可以用二級索引把真正需要讀取的數據過濾出來。但是實踐中，很多資料庫並不因為使用了索引使得查詢變快了，有的時候反而變得更慢了。對於 mysql 來說，存儲時間序列的最佳方式是按時間做 partition，不對維度建立任何索引。查詢的時候只過濾出對應的 partition，然後進行全 partition 掃描，這樣會快過於使用二級索引定位到行之後再去讀取主存儲的查詢方式。究其原因，就是數據本地化的問題了。
[page]
數據本地化
數據本地化的實質是軟體工程師們要充分尊重和理解底層硬體的限制，並且用各種手段規避問題最大化利用手裡的硬體資源。本地化有很多種形態
最常見的最好理解的本地化問題是網路問題。我們都知道網路帶寬不是無限的，比本地磁碟慢多了。如果可能盡量不要通過網路去訪問數據。即便要訪問，也應該一次抓取多一些數據，而不是一次搞一點，然後搞很多次。因為網路連接和來回的開銷是非常高的。這就是 data locality 的問題。我們要把計算盡可能的靠近數據，減少網路上傳輸的數據量。
這種帶寬引起的本地化問題，還有很多。網路比硬碟慢，硬碟比內存慢，內存比L2緩存慢。做到極致的資料庫可以讓計算完全發生在 L2 緩存內，盡可能地避免頻繁地在內存和L2之間倒騰數據。
另外一種形態的問題化問題是磁碟的順序讀和隨機讀的問題。當數據彼此靠近地物理存放在磁碟上的時候，順序讀取一批是非常快的。如果需要隨機讀取多個不連續的硬碟位置，磁頭就要來回移動從而使得讀取速度快速下降。即便是 SSD 硬碟，順序讀也是要比隨機讀快的。
基於盡可能讓數據讀取本地化的原則，檢索應該盡可能地使用順序讀而不是隨機讀。如果可以的話，把主存儲的row key或者clustered index設計為和查詢提交一樣的。時間序列如果都是按時間查，那麼按時間做的row key可以非常高效地以順序讀的方式把數據拉取出來。類似地，按列存儲的數據如果要把一個列的數據都取出來加和的話，可以非常快地用順序讀的方式載入出來。
二級索引的訪問方式典型的隨機讀。當查詢條件經過了二級索引查找之後得到一堆的主存儲的 key，那麼就需要對每個 key 進行一次隨機讀。即便彼此僅靠的key可以用順序讀做一些優化，總體上來說仍然是隨機讀的模式。這也就是為什麼時間序列數據在 mysql 里建立了索引反而比沒有建索引還要慢的原因。
為了盡可能的利用順序讀，人們就開始想各種辦法了。前面提到了 mysql 里的一行數據的多個列是彼此緊靠地物理存放的。那麼如果我們把所需要的數據建成多個列，那麼一次查詢就可以批量獲得更多的數據，減少隨機讀取的次數。也就是把之前的一些行變為列的方式來存放，減少行的數量。這種做法的經典案例就是時間序列數據，比如可以一分鍾存一行數據，每一秒的值變成一個列。那麼行的數量可以變成之前的1/60。
但是這種行變列的做法在按列存儲的資料庫里就不能直接照搬了，有些列式資料庫有column family的概念，不同的設置在物理上存放可能是在一起的也可能是分開的。對於 Elasticsearch 來說，要想減少行的數量，讓一行多pack一些數據進去，一種做法就是利用 nested document。內部 Elasticsearch 可以保證一個 document 下的所有的 nested document是物理上靠在一起放在同一個 lucene 的 segment 內。
網路的data locality就比較為人熟知了。map rece的大數據計算模式就是利用map在數據節點的本地把數據先做一次計算，往往計算的結果可以比原數據小很多。然後再通過網路傳輸匯總後做 rece 計算。這樣就節省了大量網路傳輸數據的時間浪費和資源消耗。現在 Elasticsearch 就支持在每個 data node 上部署 spark。由 spark 在每個 data node 上做計算。而不用把數據都查詢出來，用網路傳輸到 spark 集群里再去計算。這種資料庫和計算集群的混合部署是高性能的關鍵。類似的還有 storm 和 kafka 之間的關系。
網路的data locality還有一個老大難問題就是分布式大數據下的多表join問題。如果只是查詢一個分布式表，那麼把計算用 map rece 表達就沒有多大問題了。但是如果需要同時查詢兩個表，就意味著兩個表可能不是在物理上同樣均勻分布的。一種最簡單的策略就是找出兩張表中最小的那張，然後把表的內容廣播到每個節點上，再做join。復雜一些的是對兩個單表做 map rece，然後按照相同的 key 把部分計算的結果匯集在一起。第三種策略是保證數據分布的方式，讓兩張表查詢的時候需要用到的數據總在一起。沒有完美的方案，也不大可能有完美的方案。除非有一天網路帶寬可以大到忽略不計的地步。
更多的機器
這個就沒有什麼好說的了。多一倍的機器就多一倍的 CPU，可以同時計算更多的數據。多一倍的機器就多一倍的磁頭，可以同時掃描更多的位元組數。很多大數據框架的故事就是講如何如何通過 scale out解決無限大的問題。但是值得注意的是，集群可以無限大，數據可以無限多，但是口袋裡的銀子不會無限多的。堆機器解決問題比升級大型機是要便宜，但是機器堆多了也是非常昂貴的。特別是 Hive 這些從一開始就是分布式多機的檢索方案，剛開始的時候效率並不高。堆機器是一個乘數，當資料庫本來單機性能不高的時候，乘數大並不能起到決定性的作用。
更高效的計算和計算實現
檢索的過程不僅僅是磁碟掃描，它還包括一個可簡單可復雜的變換過程。使用 hyperloglog，count min-sketch等有損演算法可以極大地提高統計計算的性能。資料庫的join也是一個經常有演算法創新的地方。
計算實現就是演算法是用C++實現的還是用java，還是python實現的。用java是用大Integer實現的，還是小int實現的。不同的語言的實現方式會有一些固定的開銷。不是說快就一定要C++，但是 python 寫 for 循環是顯然沒有指望的。任何數據檢索的環節只要包含 python/ruby 這些語言的逐條 for 循環就一定快不起來了。
結論
希望這四點可以被記住，成為一種指導性的優化數據檢索效率的思維框架。無論你是設計一個mysql表結構，還是優化一個spark sql的應用。從這四個角度想想，都有哪些環節是在拖後腿的，手上的工具有什麼樣的參數可以調整，讓隨機讀變成順序讀，表結構怎麼樣設計可以最小化數據讀取的量。要做到這一點，你必須非常非常了解工具的底層實現。而不是盲目的相信，xx資料庫是最好的資料庫，所以它一定很快之類的。如果你不了解你手上的資料庫或者計算引擎，當它快的時候你不知道為何快，當它慢的時候你就更加無從優化了。
㈢ SQL資料庫中都是以表的形式存儲數據的嗎
SQLSERVER資料庫中數據存儲：
一：存儲文件類型
SQLSERVER有兩種數據存儲文件，分別是數據文件和日誌文件。
其中：數據文件是以8K(＝8192Byte)的頁面(Page)作為存儲單元的。
而日誌文件是以日誌記錄作為存儲單元。本文只討論數據文件的存儲方式，不涉及到日誌文件存儲方式。
數據文件以頁面做為存儲單元存儲數據，要理解數據文件的存儲方式，必須了解SQLSERVER中定義的頁面類型種類。
二：頁面類型
SQLSERVER中頁面類型有8種,具體每種類型的詳細說明，見下圖：
用戶的數據一般存放在數據頁面中,由上圖可以看出,數據頁包含數據行中除 text、ntext 和 image 數據外的所有數據，text、ntext 和 image 數據存儲在單獨的頁中。那麼在一個數據頁面中,數據是如何存放,SQLSERVER又是根據什麼來定位頁面與頁面上的數據呢。要回答這個問題,有必要先了解數據頁面的具體結構。
三：數據頁面結構
在數據頁上，數據行緊接著頁首按順序放置。在頁尾有一個行偏移表。在行偏移表中，頁上的每一行都有一個條目，每個條目記錄那一行的第一個位元組與頁首的距離。行偏移表中的條目序列與頁中行的序列相反。數據頁面結構如下圖所示，下面將詳細解釋
其中：數據頁面頁首：96個位元組，保存著頁面的系統信息，如頁的類型、頁的可用空間量、擁有頁的對象的對象 ID 以及該頁面所屬於哪個物理文件。
數據區：對應於上圖中所有數據行的總區域，存放真正的數據，是以Slot為單位。一個Slot就是對應於一條數據記錄行，從0開始編號,以16進制反序保存,Slot0,Slot1....。
行偏移數組：用於記錄該數據頁面中每個Slot在數據頁面所處的相對位置，便於定位和檢索每個Slot在數據頁面中的位置，數組中每個記錄占兩個位元組。
四：存儲分配單位：盤區(擴展 Extend)
雖然SQLSERVER中數據文件存儲單位是頁面(Page),但實際SQLSERVE並不是為頁面為單位給數據分配空間，SQLSERVER默認的存儲分配單位是盤區。這樣做的主要原因是為了提高性能。為了避免頻繁的讀寫IO，在表或其它對象分配存儲空間，不是直接分配一個8K的頁面，而是以一個盤區(Extend)為存儲分配單位，一個盤區為8個頁面(=8＊8K＝64K)。
但是這樣做雖然減少了頻繁的IO讀寫，提高的資料庫性能，但卻導致出一個新問題，那就是在存儲那些只有少量數據，不足8K的對象，如果也是分配給一個盤區，就會存在存儲空間上的浪費，降低了空間分配效率。
為解決上述問題，SQLSERVER提供了一種解決方案，定義了兩種盤區類型,統一盤區和混合盤區。
其中:統一盤區只能存放同一個對象，該對象擁有這個盤區的所有頁面
混合盤區：由多個對象共同擁有該盤區。
在實際為對象分配存儲盤區時，為了提高空間利用率，默認的情況下，如果一個對象一開始大小小於8個頁面，就盡量放在混合盤區中，如果該對象大小增加到8個頁面後，SQLSERVER會為這個對象重新分配一個統一盤區。
為了能夠通過上述策略來實現為對象分配存儲盤區，SQLSERVER提供了GAM/SGAM機制來管理和維護數據文件的盤區信息。
㈣ Spark SQL CBO 基於代價的優化
Spark CBO 背景
本文將介紹 CBO，它充分考慮了數據本身的特點（如大小、分布）以及操作運算元的特點（中間結果集的分布及大小）及代價，從而更好的選擇執行代價最小的物理執行計劃，即 SparkPlan。
Spark CBO 原理
CBO 原理是計算所有可能的物理計劃的代價，並挑選出代價最小的物理執行計劃。其核心在於評估一個給定的物理執行計劃的代價。
物理執行計劃是一個樹狀結構，其代價等於每個執行節點的代價總合，如下圖所示。
而每個執行節點的代價，分為兩個部分
每個操作運算元的代價相對固定，可用規則來描述。而執行節點輸出數據集的大小與分布，分為兩個部分：1) 初始數據集，也即原始表，其數據集的大小與分布可直接通過統計得到；2)中間節點輸出數據集的大小與分布可由其輸入數據集的信息與操作本身的特點推算。
所以，最終主要需要解決兩個問題
Statistics 收集
通過如下 SQL 語句，可計算出整個表的記錄總數以及總大小
從如下示例中，Statistics 一行可見， customer 表數據總大小為 37026233 位元組，即 35.3MB，總記錄數為 28萬，與事實相符。
通過如下 SQL 語句，可計算出指定列的統計信息
從如下示例可見，customer 表的 c_customer_sk 列最小值為 1， 最大值為 280000，null 值個數為 0，不同值個數為 274368，平均列長度為 8，最大列長度為 8。
除上述示例中的統計信息外，Spark CBO 還直接等高直方圖。在上例中，histogram 為 NULL。其原因是，spark.sql.statistics.histogram.enabled 默認值為 false，也即 ANALYZE 時默認不計算及存儲 histogram。
下例中，通過 SET spark.sql.statistics.histogram.enabled=true; 啟用 histogram 後，完整的統計信息如下。
從上圖可見，生成的 histogram 為 equal-height histogram，且高度為 1102.36，bin 數為 254。其中 bin 個數可由 spark.sql.statistics.histogram.numBins 配置。對於每個 bin，勻記錄其最小值，最大值，以及 distinct count。
值得注意的是，這里的 distinct count 並不是精確值，而是通過 HyperLogLog 計算出來的近似值。使用 HyperLogLog 的原因有二
運算元對數據集影響估計
對於中間運算元，可以根據輸入數據集的統計信息以及運算元的特性，可以估算出輸出數據集的統計結果。
本節以 Filter 為例說明運算元對數據集的影響。
對於常見的 Column A < value B Filter，可通過如下方式估算輸出中間結果的統計信息
上述估算的前提是，欄位 A 數據均勻分布。但很多時候，數據分布並不均勻，且當數據傾斜嚴重是，上述估算誤差較大。此時，可充分利用 histogram 進行更精確的估算
啟用 Historgram 後，Filter Column A < value B 的估算方法為
在上圖中，B.value = 15，A.min = 0，A.max = 32，bin 個數為 10。Filter 後 A.ndv = ndv(<B.value) = ndv(<15)。該值可根據 A < 15 的 5 個 bin 的 ndv 通過 HyperLogLog 合並而得，無須重新計算所有 A < 15 的數據。
運算元代價估計
SQL 中常見的操作有 Selection（由 select 語句表示），Filter（由 where 語句表示）以及笛卡爾乘積（由 join 語句表示）。其中代價最高的是 join。
Spark SQL 的 CBO 通過如下方法估算 join 的代價
其中 rows 即記錄行數代表了 CPU 代價，size 代表了 IO 代價。weight 由 *spark.sql.cbo.joinReorder.card.weight *決定，其默認值為 0.7。
Build側選擇
對於兩表Hash Join，一般選擇小表作為build size，構建哈希表，另一邊作為 probe side。未開啟 CBO 時，根據表原始數據大小選擇 t2 作為build side
而開啟 CBO 後，基於估計的代價選擇 t1 作為 build side。更適合本例
優化 Join 類型
Spark SQL 中，Join 可分為 Shuffle based Join 和 BroadcastJoin。Shuffle based Join 需要引入 Shuffle，代價相對較高。BroadcastJoin 無須 Join，但要求至少有一張表足夠小，能通過 Spark 的 Broadcast 機制廣播到每個 Executor 中。
在不開啟 CBO 中，Spark SQL 通過 spark.sql.autoBroadcastJoinThreshold 判斷是否啟用 BroadcastJoin。其默認值為 10485760 即 10 MB。
並且該判斷基於參與 Join 的表的原始大小。
在下圖示例中，Table 1 大小為 1 TB，Table 2 大小為 20 GB，因此在對二者進行 join 時，由於二者都遠大於自動 BroatcastJoin 的閾值，因此 Spark SQL 在未開啟 CBO 時選用 SortMergeJoin 對二者進行 Join。
而開啟 CBO 後，由於 Table 1 經過 Filter 1 後結果集大小為 500 GB，Table 2 經過 Filter 2 後結果集大小為 10 MB 低於自動 BroatcastJoin 閾值，因此 Spark SQL 選用 BroadcastJoin。
優化多表 Join 順序
未開啟 CBO 時，Spark SQL 按 SQL 中 join 順序進行 Join。極端情況下，整個 Join 可能是 left-deep tree。在下圖所示 TPC-DS Q25 中，多路 Join 存在如下問題，因此耗時 241 秒。
開啟 CBO 後， Spark SQL 將執行計劃優化如下
優化後的 Join 有如下優勢，因此執行時間降至 71 秒
總結
5萬人關注的大數據成神之路，不來了解一下嗎？
5萬人關注的大數據成神之路，真的不來了解一下嗎？
5萬人關注的大數據成神之路，確定真的不來了解一下嗎？