通常我們使用offset+limit的方式進(jìn)行分頁查詢，然而隨著數(shù)據(jù)表數(shù)據(jù)量越來越大，這種分頁查詢的方式性能也會(huì)隨之降低。
本篇主要介紹如何對(duì)分頁查詢進(jìn)行優(yōu)化。
一、起因
offset+limit方式的分頁查詢，當(dāng)數(shù)據(jù)表超過100w條記錄，性能會(huì)很差。
主要原因是offset limit的分頁方式是從頭開始查詢，然后舍棄前offset個(gè)記錄，所以offset偏移量越大，查詢速度越慢。
二、分頁查詢優(yōu)化
1. 基于索引再排序，利用MySQL支持ORDER操作可以利用索引快速定位部分元組,避免全表掃描
比如: 讀第10000到10019行元素(pk是主鍵/唯一鍵).
#大于上一頁最后一個(gè)id
SELECT * FROM table WHERE id>=10000 ORDER BY id ASC LIMIT 0,20
#或
SELECT * FROM table WHERE id >= (SELECT id FROM table order by id limit 10000, 1) LIMIT 20;
使用order by id可以在查詢時(shí)使用主鍵索引。
但是這種方式在id為uuid的時(shí)候就會(huì)出現(xiàn)問題?？梢允褂脀here in的方式解決：
select * from table where id in (select id from table order by id limit 100000,10);
帶條件的查詢：
如果在分頁查詢中添加了where條件例如 type = 'a’這樣的條件，sql變成 ：
select * from table where id in (select id from table where type='a' order by id limit 10000,10)
這種情況因?yàn)閠ype沒有使用索引也會(huì)導(dǎo)致查詢速度變慢。但是只添加type為索引查詢速度還是很慢,是因?yàn)椴樵兊臄?shù)據(jù)量太多了。這個(gè)時(shí)候考慮添加組合索引，組合索引的順序要where條件字段在前，id在后，如 (type,id)，因?yàn)榻M合索引查詢時(shí)用到了type索引，而type跟id是組合索引的關(guān)系，如果只select id ，那么直接就可以按組合索引返回id，而不需要再進(jìn)行一次查詢?nèi)シ祷豬d
2. 利用子查詢/連接+索引快速定位元組的位置,然后再讀取元組.
SELECT a.* FROM table a JOIN (select id from table limit 100000, 20) b ON a.id = b.id
三、mysql推薦使用自增id作為數(shù)據(jù)表的主鍵，不要使用uuid作為數(shù)據(jù)表的主鍵。
使用uuid作為主鍵不僅會(huì)帶來性能上的問題，在查詢時(shí)也會(huì)遇到問題。
因?yàn)樵谑褂胹elect id from table limit 10000,10 查詢id數(shù)據(jù)時(shí)，默認(rèn)是對(duì)id進(jìn)行排序，返回的是排序后的id結(jié)果，如果我們想按插入順序查詢結(jié)果，這樣查詢出來的結(jié)果就與我們的需求不相符。
四、聚簇索引和非聚簇索引
聚集索引跟非聚集索引：聚集索引類似與新華字典的拼音，根據(jù)拼音搜索到的信息都是連續(xù)的，可以很快獲取到它前后的信息。非聚集索引類似于部首查詢，信息存放的位置可能不在一個(gè)區(qū)域。對(duì)經(jīng)常使用范圍查詢的字段考慮使用聚集索引。
InnoDB中索引分為聚簇索引（主鍵索引）和非聚簇索引（非主鍵索引），聚簇索引的葉子節(jié)點(diǎn)中保存的是整行記錄，而非聚簇索引的葉子節(jié)點(diǎn)中保存的是該行記錄的主鍵的值。
如果您的表上定義有主鍵，該主鍵索引是聚集索引。
如果你不定義為您的表的主鍵時(shí)，MySQL取第一個(gè)唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚集索引。
如果沒有這樣的列，InnoDB就自己產(chǎn)生一個(gè)這樣的ID值，
優(yōu)先選index key_len小的索引進(jìn)行count(*),盡量不使用聚簇索引
在沒有where條件的情況下，count(*)和count(常量)，如果有非聚簇索引，mysql會(huì)自動(dòng)選擇非聚簇索引，因?yàn)榉蔷鄞厮饕嫉目臻g小，如果沒有非聚簇索引會(huì)使用聚集索引。count(primary key)主鍵id為聚集索引，使用聚集索引。有where條件的情況下，是否使用索引會(huì)根據(jù)where條件判斷。
五、結(jié)論
- mysql數(shù)據(jù)表記錄數(shù)超過幾十萬時(shí)，使用limit進(jìn)行分頁，性能會(huì)比較差
- mysql官方推薦不要使用uuid作為主鍵，而使用自增id作為主鍵。
- mysql表的索引會(huì)影響查詢的默認(rèn)排序，并不絕對(duì)是按主鍵排序。
- 查詢時(shí)一定要添加order by id
- 分布式情況下推薦使用帶時(shí)間屬性的自增長(zhǎng)id（分布式自增長(zhǎng)id算法）
- 性能：count()≈count(1)>count(id)>count(field)，因?yàn)閙ysql()做過優(yōu)化，會(huì)自動(dòng)選擇成本最小的方式查詢,前提是只有在Mysql5.6之后的版本才有優(yōu)化
- mysql官方關(guān)于count(1)和count()的說明：InnoDB handles SELECT COUNT() and SELECT COUNT(1) operations in the same way. There is no performance difference.
- 主備切換策略 ：可靠性優(yōu)先策略，可用性優(yōu)先策略。通常使用可靠性優(yōu)先策略
- 在對(duì)數(shù)據(jù)庫(kù)建索引，盡量選擇區(qū)分度高的列建索引，使用組合索引代替多個(gè)單列索引，遵循左前綴原則等，建組合做因可以避免非聚簇索引二次查詢（先查到索引，再根據(jù)索引關(guān)聯(lián)的聚集索引也就是主鍵再查詢另一個(gè)字段）
- mysql in在查詢的時(shí)候，如果查詢的數(shù)據(jù)比較多，mysql會(huì)認(rèn)為全部掃描速度更快會(huì)進(jìn)行全表查詢，在數(shù)據(jù)少的情況下會(huì)走索引。
