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