問答專欄Q & A COLUMN
使用合理的分頁方式以提高分頁的效率
正如樓主所說，分頁查詢在我們的實際應用中非常普遍，也是最容易出問題的查詢場景。比如對于下面簡單的語句，一般想到的辦法是在name，age，register_time字段上創(chuàng)建復合索引。這樣條件排序都能有效的利用到索引，性能迅速提升。
如上例子，當 LIMIT 子句變成 “LIMIT 100000, 50” 時，此時我們會發(fā)現，只取50條語句為何會變慢?
原因很簡單，MySQL并不知道第 100000條記錄從什么地方開始，即使有索引也需要從頭計算一次，因此會感覺非常的慢。
通常，我們在做分頁查詢時，是可以獲取上一頁中的某個數據標志來縮小查詢范圍的，比如時間，可以將上一頁的最大值時間作為查詢條件的一部分，SQL可以優(yōu)化為這樣：
若對你有所幫助，歡迎點贊、關注支持哦。
評論0
加載中...
題主給的這個sql其實想要的數據也就20條吧(你那個300020應該是打錯了，不可能是實際業(yè)務一頁顯示30多萬條記錄)，單純查三十多萬數據其實很快，為什么分頁后就很慢？
變慢的原因，一方面是select *，另一方面是數據量較大，還有一個是帶有排序操作。本質是分頁查詢時，會先查詢出limit + offset條記錄，然后截取后面的offset記錄。
Mysql數據庫作為一款比較主流的開源關系型數據庫，市場上我覺得貌似開發(fā)者沒有一個沒用過吧。
影響MySQL查詢性能的因素有很多，比如sql，表結構設計，磁盤io，網卡io，高并發(fā)，數據庫相關參數配置，還有服務器硬等。
這里面涉及最多也是面試中最常問的就是有關sql的優(yōu)化。
因為很多性能上的問題來自sql的比較多，mysql數據庫在數據量級達到百萬以上性能是逐漸下降的。
關于sql優(yōu)化又有很多優(yōu)化的方向和手段。比如對表結構的字段類型，默認值，索引等最基礎的做一些優(yōu)化，然后編寫的sql最好要能完全命中索引。
當然并不是說建索引就一定命中，不走索引就一定慢。這取決于mysql的執(zhí)行計劃。
還有建索引也并不是越多越好，單表索引最好不要超過6個，畢竟索引也占空間，數據更新的同時，還牽扯到索引文件的維護。
OK說了這么多，到底該怎么對這個分頁又排序做優(yōu)化呢？
我的做法就是合理利用主鍵索引來處理
select a.* from table a inner join (select id from table
limit 300000,20）
b on a.id=b.id;
然后排序最好放到代碼層面上去。
希望我的回答能幫到你
評論0
加載中...
問題
我們有一個 SQL，用于找到沒有主鍵 / 唯一鍵的表，但是在 MySQL 5.7 上運行特別慢，怎么辦?
實驗
我們搭建一個 MySQL 5.7 的環(huán)境，此處省略搭建步驟。
寫個簡單的腳本，制造一批帶主鍵和不帶主鍵的表：
執(zhí)行一下腳本：
現在執(zhí)行以下 SQL 看看效果：
...
執(zhí)行了 16.80s，感覺是非常慢了。
現在用一下 DBA 三板斧，看看執(zhí)行計劃：
感覺有點慘，由于 information_schema.columns 是元數據表，沒有必要的統(tǒng)計信息。
那我們來 show warnings 看看 MySQL 改寫后的 SQL：
我們格式化一下 SQL：
可以看到 MySQL 將
select from A where A.x not in (select x from B) //非關聯(lián)子查詢
轉換成了
select from A where not exists (select 1 from B where B.x = a.x) //關聯(lián)子查詢
如果我們自己是 MySQL，在執(zhí)行非關聯(lián)子查詢時，可以使用很簡單的策略：
select from A where A.x not in (select x from B where ...) //非關聯(lián)子查詢:1. 掃描 B 表中的所有記錄，找到滿足條件的記錄，存放在臨時表 C 中，建好索引2. 掃描 A 表中的記錄，與臨時表 C 中的記錄進行比對，直接在索引里比對，
而關聯(lián)子查詢就需要循環(huán)迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //關聯(lián)子查詢掃描 A 表的每一條記錄 rA： 掃描 B 表，找到其中的第一條滿足 rA 條件的記錄。
顯然，關聯(lián)子查詢的掃描成本會高于非關聯(lián)子查詢。
我們希望 MySQL 能先"緩存"子查詢的結果（緩存這一步叫物化，MATERIALIZATION），但MySQL 認為不緩存更快，我們就需要給予 MySQL 一定指導。
...
可以看到執(zhí)行時間變成了 0.67s。
整理
我們診斷的關鍵點如下：
1. 對于 information_schema 中的元數據表，執(zhí)行計劃不能提供有效信息。
2. 通過查看 MySQL 改寫后的 SQL，我們猜測了優(yōu)化器發(fā)生了誤判。
3. 我們增加了 hint，指導 MySQL 正確進行優(yōu)化判斷。
但目前我們的實驗僅限于猜測，猜中了萬事大吉，猜不中就無法做出好的診斷。
評論0
加載中...
個人實戰(zhàn)經驗分享一下，商品表，數據量還是相對較大的，有好幾百萬。當時最初架構也是遇到這種問題，因為最初設計的時候沒想到會有這么大數據量，也就應對10萬以內的架構設計。那后來也是通過不斷找尋方案，最終采用了一種橋連接表的方案。主表是商品表，幾百萬或者上千萬商品。
第一步，建立橋數據表，一個自增ID，一個商品ID，主要這倆字段，額外排序條件也可以加進去，均int類型，不宜過大。這個表就是存商品ID用的。
第二步，查詢分頁的時候，先在這個橋表做分頁查詢，表小，都是索引，速度非?？?，然后取出商品ID后，再用已知商品ID對商品表做in查詢。查出具體信息。這樣速度提升巨大。簡單實用，僅需對分頁部分做查詢修改即可完成。
目前三五百萬商品表，都是毫秒級查詢，沒改造之前需要半分鐘。
再說一句是MySQL數據庫，有的說什么分表分庫，那都沒必要，用in條件查詢，那是最快的，直接告訴在哪，取數就行了。
評論0
加載中...
這個得明白mysql的存儲原理 講個例子 一個小區(qū)有很多棟 一棟有很多層 一層有很多室 你哪個小區(qū)哪一棟那一層都不告訴它 它能一下找到嗎 雖然它也想快點幫你找到
評論0
加載中...0
回答0
回答0
回答0
回答0
回答0
回答0
回答0
回答0
回答0
回答
