sql整合成功案例

㈠ 慢sql治理經典案例分享

作者 | 如期
來源 | 阿里技術公眾號

菜鳥供應鏈金融慢sql治理已經有一段時間，自己負責的應用持續很長時間沒有慢sql告警，現階段在推進組內其他成員治理應用慢sql。這里把治理過程中的一些實踐拿出來分享下。

在分頁查詢治理的文章里已經介紹過我們系統舊的分頁查詢邏輯，上面的查詢sql明顯就是分頁查詢獲取總記錄數，通過XXX_rules表的分頁查詢介面溯源，找到發起調用的頁面是我們小二後台的一個操作商家准入的頁面，頁面打開後直接調用分頁查詢介面，除了分頁參數，不傳入其他任何查詢參數，導致掃描全表。

靈魂拷問：為什麼要掃描全表？全表數據展示到頁面，花里胡哨的數據有用嗎？

調研：和經常使用這個頁面的運營聊後了解到，打開頁面查詢出的全表數據對運營是沒有用的，他們根本不看這些數據。運營的操作習慣是拿到商家id，在頁面查詢框中輸入商家id，查到商家數據後進行操作。

由此優化方案就很明朗了：打開頁面時不直接查詢全量數據，等運營輸入商家id後，將商家id作為參數進行查詢。XXX_rules表中，商家id這一常用查詢條件設置為索引，再結合分頁查詢優化，全表掃描慢sql得以解決。

優化後的小二後台頁面如下：

打開頁面時未查詢任何數據，查詢條件商家賬戶為必填項。

優化後的sql為:

執行EXPLAIN得到結果如下：

可以看到命中了索引，掃描行數為3，查詢速度明顯提高。

掃描全表治理簡單來說就是加入查詢條件，命中索引，去除全表掃描查詢，雖然有些粗暴，但並不是沒有道理。實際業務場景中，很少有要掃描全表獲取全部數據的情況，限制調用上游必須傳入查詢條件，且該查詢條件能命中索引，能很大程度上避免慢sql。

另外，再引申下，XXX_rules初始的用意是准入表，記錄金融貨主維度的准入情況，最多也就幾千條數據，但是很多同事將這張表理解為規則表，寫入很多業務相關規則，導致這個表膨脹到一百多萬條數據，表不clean了。這就涉及到數據表的設計使用，明確表的使用規范，不亂寫入數據，能給後期維護帶來很大的便利。

除了時間、操作人欄位，XXX_rules表就rule_name、rule_value、status、proct_code四個欄位，表的索引對這四個欄位做各種排列組合。存在如下問題：

1、rule_name離散度不高，放在索引首位不合適；

2、前三個索引重合度很高；

顯然是對索引的命中規則不夠了解。XXX_rules表很多業務有定時任務對其寫入刪除，索引多、混亂，對性能有很大的影響。

高性能的索引有哪些，再來回顧下：

1、獨立的列：索引列不能是表達式的一部分；

2、選擇區分度高的列作為索引；

3、選擇合適的索引列順序：將選擇性高的索引列放在最前列；

4、覆蓋索引：查詢的列均在索引中，不需要回查聚簇索引；

5、使用索引掃描來做排序;

6、在遵守最左前綴的原則下，盡量擴展索引，而不是創建索引。

但凡記得第3和6規則，也不至於把索引建成這樣。

對索引進行整合如下：

系統中有很多任務拉取整個產品下的准入記錄，然後進行處理，所以將區分度較高的proct_code放在索引首位，然後添加rule_name、status欄位到索引里，進一步過濾數據，減少掃描行數，避免慢sql。針對常用的rule_value查詢條件，可以命中UK，因此不用單獨建立索引。

很多業務邏輯中，需要拉取滿足某個條件的記錄列表，查詢的sql語句帶有order by，記錄比較多的情況，排序代價往往很大，但是查詢出來的記錄是否有序對業務邏輯沒有影響，比如分頁治理里討論的count語句，只需要統計條數，order by對條數沒有影響，再比如查出記錄列表後，不依賴記錄的順序遍歷列表處理數據，這時候order by多此一舉。

查詢sql無limit語句，且業務處理邏輯不依賴於order by後列表記錄的順序，則去除查詢sql中的order by語句。

業務中有很多定時任務，掃描某個表中某個產品下所有數據，對數據進行處理，比如：

三個查詢條件都是區分度不高的列，查出的數據有27W條，加索引意義也不大。

實際業務量沒那麼大，頂多幾千條數據，表裡的數據是從上游同步過來的，最好的辦法是讓上游精簡數據，但是由於業務太久遠，找上游的人維護難度太大，因此只能想其他的辦法。

這個定時任務目的是拉出XXX_rules表的某些產品下的數據，和另一張表數據對比，更新有差異的數據。每天凌晨處理，對時效性沒有很高的要求，因此，能不能轉移任務處理的地方，不在本應用機器上實時處理那麼多條數據？

數據是離線任務odps同步過來的，首先想到的就是dataWork數據處理平台。

建立數據對比任務，將定時任務做的數據對比邏輯放到dataWork上用sql實現，每天差異數據最多幾百條，且結果集含有區分度很高的列，將差異數據寫入odps表，再將數據迴流到idb。

新建定時任務，通過迴流回來的差異數據中區分度高的列作為查詢條件查詢XXX_rules，更新XXX_rules，解決了慢sql問題。

這個方法的前提是對數據實效性要求不高，且離線產出的結果集很小。

explain上述查詢語句，得到結果如下：

XXX_white_list表有將biz_id作為索引，這里查詢XXX_white_list表有傳入biz_id作為查詢條件，為啥explain結果里type為ALL，即掃描全表？索引失效了？索引失效有哪些情況？

索引失效場景

1、OR查詢左右有未命中索引的；

2、復合索引不滿足最左匹配原則；

3、Like以%開頭；

4、需要類型轉換；

5、where中索引列有運算；

6、where中索引列使用了函數；

7、如果mysql覺得全表掃描更快時（數據少時）

上述查詢語句第8行，customer_id為XXX_level_report表欄位，未命中XXX_white_list表索引，導致索引失效。

這個語句用condition、枚舉、join花里胡哨的代碼拼接起來的，改起來好麻煩，而且看起來「OR customer_id LIKE CONCAT(t.biz_id, '@%')」這句不能直接刪掉。最後重構了該部分的查詢語句，去除or查詢，解決了慢sql。

㈡ SQL怎麼把兩個表的數據整合起來

先將兩張表聯合在一起，然後再對聯合後的表進行查詢。例如:
select sum(t.quantity) as totalQty from
(select * from t1
union all
select * from t2)t;

㈢ SQL兩張表聯合查詢

sql 兩表聯合查詢。, listview怎麼實現載入兩張表聯合查詢。sql語句我知道。後面後面怎麼這整

select a.欄位1,b.欄位2 from 表a,表b where 表a.相同欄位=表b.相同欄位

sql聯合查詢語句（兩張表）是什麼?

sql聯合查詢語句（兩張表）是：

select A.ID,A.VALUE,A.TYPE,A.NAME,B.KEY,B.ID,B.VALUE,B.NAME
min(VALUE),max(VALUE) from A left join B on A.ID = B.ID
where B.NAME="你輸入的名字"
and B.VALUE > (select min(VALUE) from B where NAME="你輸入的名字"))
and B.VALUE < (select min(VALUE) from B where NAME="你輸入的名字"))；

延展閱讀：

• A表欄位stuid,stuname。

• B表欄位bid,stuid,score,coursename，status。

• 要用一條sql查出A表中所有記錄的對應的stuid，max(score),coursename，status，並且status=1，sql語句要求跨資料庫，不能使用rownum，，limit等方言。

• 比如資料：

A

stuid stuname

11 zhangshan

22 lisi

B

bid sutid coursename scoure status

a 11 yuwen 66 1

b 11 shuxue 78 1

c 11 huaxue 95 0
最後要得到的資料是

stuid couresname scoure status

11 shuxue 78 1

22 null null null

如何用QT實現兩張表的查詢及聯合查詢？

SELECT* FROM 表1 JOIN 表2 ON 表1.列3 = 表2.列4
註：表1的列3必須與表2的列4相同。

sql三表聯合查詢

select * from tb1,tb2,tb3 where (tb1.id=tb2.id) and (tb2.id=tb3.id) and tb1.xx=xx

用SQL對兩個表的聯合查詢

是分別查前20條？selectcol1，col2，col3,sTimefrom(selectcol1，col2，col3,sTimefromtb1orderbysTimedesc)rownum<21unionallselectcol1，col2，col3,sTimefrom(selectcol1，col2，col3,sTimefromtb2orderbysTimedesc)rownum<21

SQL查詢兩個表聯合查詢怎麼寫？

如下方法：

select 1 a.姓名 , b.minnum , b.maxnum from a INNER JOIN b ON a.id = b.id where b.minnum > 40 or b.maxnum < 40。

1、SQL的解釋：結構化查詢語言（英文簡稱：SQL）是一種特殊目的的程式語言，是一種資料庫查詢和程式設計語言，用於存取資料以及查詢、更新和管理關系資料庫系統；同時也是資料庫指令碼檔案的副檔名。它不要求使用者指定對資料的存放方法，也不需要使用者了解具體的資料存放方式，所以具有完全不同底層結構的不同 資料庫系統,，可以使用相同的結構化查詢語言作為資料輸入與管理的介面。

2、SQL的語句結構：其語句，也稱為「資料檢索 語句」，用以從表中獲得資料，確定資料怎樣在應用程式給出。保留字SELECT是DQL（也是所有SQL）用得最多的動詞，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。這些DQL保留字常與其他型別的SQL語句一起使用。

兩張表聯合查詢,需要整合兩張表不同的欄位~表1 ID ,name,gae 表2 ID yangr date 要得到 ID name gae yangr

select 表1.id,表1.name,表1.gae,表2.ygngr
from 表1 ，表2
where 表1.id=表2.id

mysql兩表聯合查詢

兩個表需要有共同的欄位用來做對應關系，假定表a 的ab欄位和表b 的bc欄位意義是一樣的：
新建兩張表：
表a：ab ac ad
1 1 2
2 3 4
3 5 6
表b: bc bd be
1 7 8
3 9 10
4 11 12
（此時這樣建表只是為了演示連線SQL語句，當然實際開發中我們不會這樣建表，實際開發中這兩個表會有自己不同的主鍵。）
一、外連線
外連線可分為：左連線、右連線、完全外連線。
1、左連線 left join 或 left outer join
SQL語句：select 表a.ab,表a.ac,表a.ad,表b.bc,表b.bd,表b.be from 表a left join 表b on 表a.ab=表b.bd執行結果以ab行為准：
表：ab ac ad bc bd be
1 1 2 1 7 8
2 3 4 0 0 0
3 5 6 3 9 10
其中b表bc第二行的值在ab中沒有對應的所以左連無法找出來，同理右連會變成這樣：
SQL語句：select 表a.ab,表a.ac,表a.ad,表b.bc,表b.bd,表b.be from 表a left join 表b on 表a.ab=表b.bd執行結果以bd行為准：
表：ab ac ad bc bd be
1 1 2 1 7 8
3 5 6 3 9 10
0 0 0 4 11 12
左外連線包含left join左表所有行，如果左表中某行在右表沒有匹配，則結果中對應行右表的部分全部為0.
註：此時我們不能說結果的行數等於左表資料的行數。當然此處查詢結果的行數等於左表資料的行數，因為左右兩表此時為一對一關系。
右外連線包含right join右表所有行，如果左表中某行在右表沒有匹配，則結果中對應左表的部分全部為0。
註：同樣此時我們不能說結果的行數等於右表的行數。當然此處查詢結果的行數等於左表資料的行數，因為左右兩表此時為一對一關系。
3、完全外連線 full join 或 full outer join
SQL語句：select 表a.ab,表a.ac,表a.ad,表b.bc,表b.bd,表b.be from 表a full join 表b on 表a.ab=表b.bd執行結果：
表：ab ac ad bc bd be
1 1 2 1 7 8
2 3 4 0 0 0
3 5 6 3 9 10
0 0 0 4 11 12
完全外連線包含full join左右兩表中所有的行，如果右表中某行在左表中沒有匹配，則結果中對應行右表的部分全部為0，如果左表中某行在右表中沒有匹配，則結果中對應行左表的部分全部為0。
二、內連線 join 或 inner join
SQL語句：select 表a.ab,表a.ac,表a.ad,表b.bc,表b.bd,表b.be from 表a inner join 表b on 表a.ab=表b.bdinner join 是比較運運算元，只返回符合條件的行。
表：ab ac ad bc bd be
1 1 2 1 7 8
3 5 6 3 9 10
三、交叉連線 cross join
1.概念：沒有 WHERE 子句的交叉聯接將產生連線所涉及的表的笛卡爾積。第一個表的行數乘以第二個表的行數等於笛卡爾積結果集的大小。
表a: ab
星期一
星期二
星期三
表b: cd
張三
李四
王五
SELECT a.ab,b.cd FROM 表a CROSS JOIN 表b
ab cd
星期一 張三
星期一 李四
星期一 王五
星期二 張三
星期二 李四
星期二 王五
星期三 張三
星期三 李四
星期三 王五

㈣ SQL優化萬能公式：5 大步驟 + 10 個案例

在應用開發的早期，數據量少，開發人員開發功能時更重視功能上的實現，隨著生產數據的增長，很多SQL語句開始暴露出性能問題，對生產的影響也越來越大，有時可能這些有問題的SQL就是整個系統性能的瓶頸。

1、通過慢查日誌等定位那些執行效率較低的SQL語句

2、explain 分析SQL的執行計劃

type由上至下，效率越來越高

Extra

3、show profile 分析

了解SQL執行的線程的狀態及消耗的時間。默認是關閉的，開啟語句「set profiling = 1;」

4、trace

trace分析優化器如何選擇執行計劃，通過trace文件能夠進一步了解為什麼優惠券選擇A執行計劃而不選擇B執行計劃。

5、確定問題並採用相應的措施

案例1、最左匹配

索引

SQL語句

查詢匹配從左往右匹配，要使用order_no走索引，必須查詢條件攜帶shop_id或者索引( shop_id , order_no )調換前後順序

案例2、隱式轉換

索引

SQL語句

隱式轉換相當於在索引上做運算，會讓索引失效。mobile是字元類型，使用了數字，應該使用字元串匹配，否則MySQL會用到隱式替換，導致索引失效。

案例3、大分頁

索引

SQL語句

對於大分頁的場景，可以優先讓產品優化需求，如果沒有優化的，有如下兩種優化方式， 一種是把上一次的最後一條數據，也即上面的c傳過來，然後做「c < xxx」處理，但是這種一般需要改介面協議，並不一定可行。另一種是採用延遲關聯的方式進行處理，減少SQL回表，但是要記得索引需要完全覆蓋才有效果，SQL改動如下

案例4、in + order by

索引

SQL語句

in查詢在MySQL底層是通過n*m的方式去搜索，類似union，但是效率比union高。in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過將in包含的數值，一條條去查詢獲取元組數的，因此這個計算過程會比較的慢，所以MySQL設置了個臨界值(eq_range_index_pe_limit)，5.6之後超過這個臨界值後該列的cost就不參與計算了。因此會導致執行計劃選擇不準確。默認是200，即in條件超過了200個數據，會導致in的代價計算存在問題，可能會導致Mysql選擇的索引不準確。

處理方式，可以( order_status , created_at )互換前後順序，並且調整SQL為延遲關聯。

案例5、范圍查詢阻斷，後續欄位不能走索引

索引

SQL語句

范圍查詢還有「IN、between」

案例6、不等於、不包含不能用到索引的快速搜索。（可以用到ICP）

在索引上，避免使用NOT、!=、>、!、NOT EXISTS、NOT IN、NOT LIKE等

案例7、優化器選擇不使用索引的情況

如果要求訪問的數據量很小，則優化器還是會選擇輔助索引，但是當訪問的數據占整個表中數據的蠻大一部分時（一般是20%左右），優化器會選擇通過聚集索引來查找數據。

查詢出所有未支付的訂單，一般這種訂單是很少的，即使建了索引，也沒法使用索引。

案例8、復雜查詢

如果是統計某些數據，可能改用數倉進行解決；如果是業務上就有那麼復雜的查詢，可能就不建議繼續走SQL了，而是採用其他的方式進行解決，比如使用ES等進行解決。

案例9、asc和desc混用

desc 和asc混用時會導致索引失效

案例10、大數據

對於推送業務的數據存儲，可能數據量會很大，如果在方案的選擇上，最終選擇存儲在MySQL上，並且做7天等有效期的保存。那麼需要注意，頻繁的清理數據，會照成數據碎片，需要聯系DBA進行數據碎片處理。

㈤ sql多表聯查實例

sql多表聯查實例
下面提供四款sql多表關聯查詢的實例，個個效率不一樣。
select
*
from
order_info
as
a
,ivrlog4ivrdlvinst
as
b
where
(a.saleorder=b.ext1_skill
and
b.start_date=@date1
and
se_id='55'
and
b.ext1_skill!='')
and
convert(varchar(10),a.instime,112)=@date2
and
max(a.instime)
方法二
select
*
from
order_info
as
a
where
a.saleorder=(
select
b.ext1_skill
from
ivrlog4ivrdlvinst
as
b
where
b.start_date=@date1
and
se_id='55'
and
b.ext1_skill!='')
and
convert(varchar(10),max(a.instime),112)=@date2
方法三
declare
@date1
varchar(20),
@date2
varchar(20)
set
@date1='20100812'
set
@date2='2010-08-12'
select
*
from
order_info
as
a
where
a.saleorder=
(select
b.ext1_skill
from
ivrlog4ivrdlvinst
as
b
where
b.start_date=@date1
and
se_id='55'
and
b.ext1_skill!='')
and
convert(varchar(10),a.instime,112)=@date2
and
max(a.instime)
方法四
select
b.caller,
b.start_date,
b.start_time,
b.ext1_skill,
c.deliveryno,
c.destroyresult,
c.deliverydate,
c.deliverytime,
c.arrangetime,
c.driverphone,
c.drivermobile,
a.servicedate,
a.servicetime,
a.workertel
from
order_info
as
a
,ivrlog4ivrdlvinst
as
b
,delivery_info
as
c
where
a.saleorder
in
(select
b.ext1_skill
from
ivrlog4ivrdlvinst
where
b.start_date=@date1
and
b.se_id='55'
and
b.ext1_skill!='')
and
convert(varchar(10),a.instime,112)=@date2
order
by
b.start_date
desc,
b.start_time
desc

㈥ 1、sql查詢語句時怎麼把幾個欄位拼接成一個欄位這幾個欄位是整型的。

1、語句如下：

select 欄位A, 欄位B, 欄位A + 欄位B as 欄位C From 表1

註：欄位A加上欄位B的計算列命名為欄位C

2、計算列

計算列由可以使用同一表中的其他列的表達式計算得來。表達式可以是非計算列的列名、常量、函數，也可以是用一個或多個運算符連接的上述元素的任意組合。表達式不能為子查詢。

原題的答案就是「計算列」。

(6)sql整合成功案例擴展閱讀：

計算列應用范圍

計算列可用於選擇列表、WHERE 子句、ORDER BY 子句或任何可使用正則表達式的其他位置，但下列情況除外：

用作 CHECK、FOREIGN KEY 或 NOT NULL 約束的計算列必須標記為 PERSISTED。如果計算列的值由具有確定性的表達式定義，並且索引列中允許使用計算結果的數據類型，則可將該列用作索引中的鍵列，或者用作 PRIMARY KEY 或 UNIQUE 約束的一部分。

例如，如果表中含有整數列a和b，則可以對計算列a+b創建索引，但不能對計算列a+ DATEPART(dd,GETDATE()) 創建索引，因為在後續調用中，其值可能發生改變。

計算列不能作為 INSERT 或 UPDATE 語句的目標。

資料庫引擎基於使用的表達式自動確定計算列的為 Null 性。即使只有非空列，大多數表達式的結果也「認為」可為空值，因為下溢或溢出生成的結果也可能為空。使用帶AllowsNull屬性的 COLUMNPROPERTY 函數可查明表中任何計算列的為 Null 性。

通過指定 ISNULL (check_expression,constant) 可以將可為空值的表達式轉換為不可為空值的表達式，其中,constant是可替換所有空結果的非空值。

網路.計算列

㈦ sql怎麼把兩張表合並成一張

sql把兩張表合並成一張步驟如下：

1、我們首先將以A為基準用leftjoin以B為基準用right，都要就用fulljoin 。