下述場景，均來自實際產品線上經驗，出於保密考量，所有需求場景都是仿造的，模擬遇到過的真實場景。
在實際業務場景中，我們經常遇到統計分析，比如現在有一張學生表student，現統計姓名為xxx的總共有多少學生。
| id | name |
|---|---|
| 1 | 張三 |
| 2 | 張三 |
| 3 | 李四 |
| 4 | 武器 |
| 5 | 大炮 |
| 6 | 大炮 |
| 7 | 李四 |
| 8 | 無用 |
| 9 | 劉可 |
| 10 | 狐狸 |
| 11 | 無話 |
| 12 | 敗給 |
| 13 | 事變 |
| 14 | 狐狸 |
| 15 | 何必 |
| 16 | 無話 |
| 17 | 無用 |
| 18 | 無話 |
| 19 | 李四 |
常規思路一般用groub by ,然後再求和，再分頁。
查第一頁
SELECT
t.name,
COUNT(1) as num
FROM
test t
WHERE
1 = 1
GROUP BY t.`name`
ORDER BY
num DESC
LIMIT 0,
5
查詢結果是這樣的：
| name | num |
|---|---|
| 李四 | 3 |
| 無話 | 3 |
| 張三 | 2 |
| 大炮 | 2 |
| 狐狸 | 2 |
查第二頁
SELECT
t.name,
COUNT(1) as num
FROM
test t
WHERE
1 = 1
GROUP BY t.`name`
ORDER BY
num DESC
LIMIT 5,
5
查詢結果是這樣的：
| name | num |
|---|---|
| 狐狸 | 2 |
| 武器 | 1 |
| 劉可 | 1 |
| 敗給 | 1 |
| 事變 | 1 |
結果分析
顯然第二頁的'狐狸'不應該出現，他是第一頁的最後一條資料。這個問題在mysql官方是給予了答案的，其實只要是order by 的排序欄位在結果集中不唯一，排序欄位一致的行他返回的結果都是無序的，這一點不容易被重視，也不容易被測試所發現（單表一般需要較多重複資料和分頁才容易被發現），算是一個小坑。
方案一
網上一般提供的思路： 既然排序欄位不是唯一的，我們一般期望唯一排序，只需要在order by 中跟上唯一標識的欄位即可，像下面這樣：
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
但是這種方式有個致命問題，ORDER BY 後面接了兩個欄位會讓索引失效，巨量資料場景下是不推薦這種方式的。
方案二
使用 ROW_NUMBER() OVER ( ORDER BY t.id) AS serial_number讓他按照指定方式排序，這基本也是萬機油解決方案，對程式碼侵入程度很低。但是我們這個場景下兩種方式效率一樣，因為本來num欄位就沒有索引，但是當order by 存在一個欄位可以用索引的話就不一樣了。
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
假設有這樣一個場景，要求查某公司的商品出售情況的資料，資料庫設計如下：
| 表名 | 備註 |
|---|---|
| order | 訂單表，有create_by 欄位 |
| goods | 商品表 |
| logistics | 物流表 |
| order_goods_mapping | 商品與訂單關聯表 |
| order_logistics_mapping | 物流與訂單關聯表 |
先不考慮資料庫設計是否合理，現在要分頁查詢商品銷售情況，在不考慮資料量的情況下一般這樣寫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欄位索引都有，當資料庫較小的時候看上去沒有任務問題。但是假設商品有1億種商品，這個sql可以預見性的劇卡。因為join操作匹配本來就是nnn這樣的操作，由於只限制了logistics 的company_id，所以查詢出來的資料量依舊是巨大的。（親身經歷的一次因為慢查詢，導致上線失敗的根本原因）
要限制每張表的資料儘可能少，一般多租戶場景下，每張表要有租戶id, 這樣就可以按租戶維度進行資料隔離。由於很多時候我們沒有遇到過大表的情況，所以基本租戶隔離技術在sql聯表查詢沒有體現出來，往往只是限制了聯表的某一張表的租戶id等於登入的租戶id,這是不可取的（有意思的是：難怪現在流行的多租戶方案要求每張表都要有租戶id,除了分庫分表有用，查詢優化也體現出了資料隔離的優勢，一個小小的欄位竟然有這麼大的作用）。優化後的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
mysql作為關係型資料庫，他對行內關係的描述較弱，比如有這樣2個表，主表interface記錄介面表，子表itf_param記錄介面參數列。
itf_param假設構造如下：
| 欄位名 | 描述 |
|---|---|
| id | 主鍵 |
| itf_id | 介面id |
| param_name | 引數名稱 |
| param_value | 引數值 |
現在要查所有(引數名='code',引數值='12')和(引數名='route',引數值='gw')的interface記錄。
通常我們會用如下sql實現：
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
在資料量少的情況下，這個sql是沒有任何問題的，但是在巨量資料量場景下，此sql就難堪大任了，因為一般來講子查詢效率都會較低（這裡即便分頁了也是如此，具體原因要問DB工程師了，估摸著limit是最後被執行，所以逐條過濾大量資料導致效率較低）。
通常連表查詢效率高於子查詢，這裡採用縱錶轉橫表的方式對sql進行優化，如下所示(偽sql)：
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