概述
下述場(chǎng)景，均來(lái)自實(shí)際產(chǎn)品線(xiàn)上經(jīng)驗(yàn)，出于保密考量，所有需求場(chǎng)景都是仿造的，模擬遇到過(guò)的真實(shí)場(chǎng)景。
場(chǎng)景一： 統(tǒng)計(jì)數(shù)據(jù)（Order by 不具備唯一性導(dǎo)致的分頁(yè)數(shù)據(jù)混亂）
需求
在實(shí)際業(yè)務(wù)場(chǎng)景中，我們經(jīng)常遇到統(tǒng)計(jì)分析，比如現(xiàn)在有一張學(xué)生表student，現(xiàn)統(tǒng)計(jì)姓名為xxx的總共有多少學(xué)生。
| id | name |
|---|---|
| 1 | 張三 |
| 2 | 張三 |
| 3 | 李四 |
| 4 | 武器 |
| 5 | 大炮 |
| 6 | 大炮 |
| 7 | 李四 |
| 8 | 無(wú)用 |
| 9 | 劉可 |
| 10 | 狐貍 |
| 11 | 無(wú)話(huà) |
| 12 | 敗給 |
| 13 | 事變 |
| 14 | 狐貍 |
| 15 | 何必 |
| 16 | 無(wú)話(huà) |
| 17 | 無(wú)用 |
| 18 | 無(wú)話(huà) |
| 19 | 李四 |
實(shí)現(xiàn)
常規(guī)思路一般用groub by ,然后再求和，再分頁(yè)。
查第一頁(yè)
SELECT
t.name,
COUNT(1) as num
FROM
test t
WHERE
1 = 1
GROUP BY t.`name`
ORDER BY
num DESC
LIMIT 0,
5
查詢(xún)結(jié)果是這樣的：
| name | num |
|---|---|
| 李四 | 3 |
| 無(wú)話(huà) | 3 |
| 張三 | 2 |
| 大炮 | 2 |
| 狐貍 | 2 |
查第二頁(yè)
SELECT
t.name,
COUNT(1) as num
FROM
test t
WHERE
1 = 1
GROUP BY t.`name`
ORDER BY
num DESC
LIMIT 5,
5
查詢(xún)結(jié)果是這樣的：
| name | num |
|---|---|
| 狐貍 | 2 |
| 武器 | 1 |
| 劉可 | 1 |
| 敗給 | 1 |
| 事變 | 1 |
結(jié)果分析
顯然第二頁(yè)的'狐貍'不應(yīng)該出現(xiàn)，他是第一頁(yè)的最后一條數(shù)據(jù)。這個(gè)問(wèn)題在mysql官方是給予了答案的，其實(shí)只要是order by 的排序字段在結(jié)果集中不唯一，排序字段一致的行他返回的結(jié)果都是無(wú)序的（可能無(wú)索引的話(huà)走堆排序），這一點(diǎn)不容易被重視，也不容易被測(cè)試所發(fā)現(xiàn)（單表一般需要較多重復(fù)數(shù)據(jù)和分頁(yè)才容易被發(fā)現(xiàn)），算是一個(gè)小坑。
優(yōu)化
方案一
網(wǎng)上一般提供的思路： 既然排序字段不是唯一的，我們一般期望唯一排序，只需要在order by 中跟上唯一標(biāo)識(shí)的字段即可（或者加索引走索引排序），像下面這樣：
SELECT
t.name,
COUNT(1) as num
FROM
test t
WHERE
1 = 1
GROUP BY t.`name`
ORDER BY
num DESC,t.id desc
LIMIT 5,
5
但是這種方式有個(gè)致命問(wèn)題，ORDER BY 后面接了兩個(gè)字段會(huì)讓索引失效，大數(shù)據(jù)場(chǎng)景下是不推薦這種方式的。
方案二
使用 ROW_NUMBER() OVER ( ORDER BY t.id) AS serial_number讓他按照指定方式排序，這基本也是萬(wàn)機(jī)油解決方案，對(duì)代碼侵入程度很低。但是我們這個(gè)場(chǎng)景下兩種方式效率一樣，因?yàn)楸緛?lái)num字段就沒(méi)有索引，但是當(dāng)order by 存在一個(gè)字段可以用索引的話(huà)就不一樣了。
SELECT
t.name,
COUNT(1) as num ,
ROW_NUMBER() OVER ( ORDER BY t.name) AS serial_number
FROM
test t
WHERE
1 = 1
GROUP BY t.`name`
ORDER BY
num DESC
LIMIT 5,
5
場(chǎng)景二： 大表查詢(xún)優(yōu)化問(wèn)題（多租戶(hù)情景下的連表查詢(xún)規(guī)范）
需求
假設(shè)有這樣一個(gè)場(chǎng)景，要求查某公司的商品出售情況的數(shù)據(jù)，數(shù)據(jù)庫(kù)設(shè)計(jì)如下：
| 表名 | 備注 |
|---|---|
| order | 訂單表 |
| goods | 商品表 |
| logistics | 物流表 |
| order_goods_mapping | 商品與訂單關(guān)聯(lián)表 |
| order_logistics_mapping | 物流與訂單關(guān)聯(lián)表 |
實(shí)現(xiàn)
先不考慮數(shù)據(jù)庫(kù)設(shè)計(jì)是否合理，現(xiàn)在要分頁(yè)查詢(xún)商品銷(xiāo)售情況，在不考慮數(shù)據(jù)量的情況下一般這樣寫(xiě)sql(偽sql):
select g.*,o.*,l.* from goods g
join order_goods_mapping ogm on(ogm.goods_id= g.goods_id)
join order o on(o.order_id= ogm.order_id)
join order_logistics_mapping olg on(olg.order_id = o.order_id)
join logistics l on(l.logistics_id = olg.logistics_id)
where l.company_id = #{companyId} limit 0,10
這些xxxid字段索引都有，當(dāng)數(shù)據(jù)庫(kù)較小的時(shí)候看上去沒(méi)有任務(wù)問(wèn)題。但是假設(shè)商品有1億種商品，這個(gè)sql可以預(yù)見(jiàn)性的劇卡。因?yàn)閖oin操作匹配本來(lái)就是nnn這樣的操作，由于只限制了logistics 的company_id，所以查詢(xún)出來(lái)的數(shù)據(jù)量依舊是巨大的。（親身經(jīng)歷的一次因?yàn)槁樵?xún)，導(dǎo)致上線(xiàn)失敗的根本原因）
優(yōu)化
要限制每張表的數(shù)據(jù)盡可能少，一般多租戶(hù)場(chǎng)景下，每張表要有租戶(hù)id, 這樣就可以按租戶(hù)維度進(jìn)行數(shù)據(jù)隔離。由于很多時(shí)候我們沒(méi)有遇到過(guò)大表的情況，所以基本租戶(hù)隔離技術(shù)在sql聯(lián)表查詢(xún)沒(méi)有體現(xiàn)出來(lái)，往往只是限制了聯(lián)表的某一張表的租戶(hù)id等于登錄的租戶(hù)id,這是不可取的（有意思的是：難怪現(xiàn)在流行的多租戶(hù)方案要求每張表都要有租戶(hù)id,除了分庫(kù)分表有用，查詢(xún)優(yōu)化也體現(xiàn)出了數(shù)據(jù)隔離的優(yōu)勢(shì)，一個(gè)小小的字段竟然有這么大的作用）。優(yōu)化后的sql如下：
select g.*,o.*,l.* from goods g
join order_goods_mapping ogm on(ogm.goods_id= g.goods_id)
join order o on(o.order_id= ogm.order_id)
join order_logistics_mapping olg on(olg.order_id = o.order_id)
join logistics l on(l.logistics_id = olg.logistics_id)
where l.company_id = #{companyId} and g.company_id = #{companyId} and ogm..company_id = #{companyId} and o.company_id = #{companyId} and olg.company_id = #{companyId}limit 0,10
場(chǎng)景三： 子查詢(xún)導(dǎo)致的效率低下的問(wèn)題（縱表轉(zhuǎn)橫表的查詢(xún)，本質(zhì)上是連表取交集問(wèn)題的解決思路）
需求
mysql作為關(guān)系型數(shù)據(jù)庫(kù)，他對(duì)行內(nèi)關(guān)系的描述較弱，比如有這樣2個(gè)表，主表interface記錄接口表，子表itf_param記錄接口參數(shù)表。
itf_param假設(shè)構(gòu)造如下：
| 字段名 | 描述 |
|---|---|
| id | 主鍵 |
| itf_id | 接口id |
| param_name | 參數(shù)名稱(chēng) |
| param_value | 參數(shù)值 |
現(xiàn)在要查所有(參數(shù)名='code',參數(shù)值='12')和(參數(shù)名='route',參數(shù)值='gw')的interface記錄。
實(shí)現(xiàn)
通常我們會(huì)用如下sql實(shí)現(xiàn)：
select it.* from interface it where 1=1
and exists(
select 1 from itf_param p where p.param_name= 'code' and p.param_value='12'
)
and exists(
select 1 from itf_param p where p.param_name= 'route' and p.param_value='gw'
)
where 1=1 limit 0,10
在數(shù)據(jù)量少的情況下，這個(gè)sql是沒(méi)有任何問(wèn)題的，但是在大數(shù)據(jù)量場(chǎng)景下，此sql就難堪大任了，因?yàn)橐话銇?lái)講子查詢(xún)效率都會(huì)較低（這里即便分頁(yè)了也是如此，具體原因要問(wèn)DB工程師了，估摸著limit是最后被執(zhí)行，所以逐條過(guò)濾大量數(shù)據(jù)導(dǎo)致效率較低）。
優(yōu)化
通常連表查詢(xún)效率高于子查詢(xún)，這里采用縱表轉(zhuǎn)橫表的方式對(duì)sql進(jìn)行優(yōu)化，如下所示(偽sql)：文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-424692.html
select it.* ,
MAX(CASE WHEN p.param_name= 'code' THEN p.param_value ELSE NULL END) AS codeParamValue,
MAX(CASE WHEN p.param_name= 'route' THEN p.param_value ELSE NULL END) AS routeParamValue,
from interface it join itf_param p on(it.itf_id = p.itf_id)
where 1=1
group by it.*
having codeParamValue = '12' and routeParamValue='gw'
limit 0,10
場(chǎng)景四： mybatis二級(jí)緩存導(dǎo)致的問(wèn)題
在同一個(gè)事務(wù)里面：
po = xxxMapper.select();
po.setFile(xxx);
po = xxxMapper.select();
其中po的field字段還是xxx。雖然是調(diào)用的mapper.select()從數(shù)據(jù)庫(kù)查詢(xún)的po對(duì)象，但是以為緩存原故，field字段還是xxx,并不是數(shù)據(jù)庫(kù)中的字段。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-424692.html
到了這里，關(guān)于真實(shí)場(chǎng)景sql優(yōu)化持續(xù)更新（老司機(jī)必備）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
