如何提高M(jìn)ySQL Limit查詢的性能?
十多年專注成都網(wǎng)站制作，企業(yè)網(wǎng)站制作，個(gè)人網(wǎng)站制作服務(wù)，為大家分享網(wǎng)站制作知識(shí)、方案，網(wǎng)站設(shè)計(jì)流程、步驟,成功服務(wù)上千家企業(yè)。為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁(yè)設(shè)計(jì)及定制高端網(wǎng)站建設(shè)服務(wù),專注于企業(yè)網(wǎng)站制作,高端網(wǎng)頁(yè)制作,對(duì)iso認(rèn)證等多個(gè)行業(yè)，擁有多年的網(wǎng)站推廣經(jīng)驗(yàn)。
在MySQL數(shù)據(jù)庫(kù)操作中，我們?cè)谧鲆恍┎樵兊臅r(shí)候總希望能避免數(shù)據(jù)庫(kù)引擎做全表掃描，因?yàn)槿頀呙钑r(shí)間長(zhǎng)，而且其中大部分掃描對(duì)客戶端而言是沒(méi)有意義的。其實(shí)我們可以使用Limit關(guān)鍵字來(lái)避免全表掃描的情況，從而提高效率。
有個(gè)幾千萬(wàn)條記錄的表 on MySQL 5.0.x，現(xiàn)在要讀出其中幾十萬(wàn)萬(wàn)條左右的記錄。常用方法，依次循環(huán)：
select * from mytable where index_col = xxx limit offset, limit;
經(jīng)驗(yàn)：如果沒(méi)有blob/text字段，單行記錄比較小，可以把 limit 設(shè)大點(diǎn)，會(huì)加快速度。
問(wèn)題：頭幾萬(wàn)條讀取很快，但是速度呈線性下降，同時(shí) mysql server cpu 99% ，速度不可接受。
調(diào)用 explain select * from mytable where index_col = xxx limit offset, limit;
顯示 type = ALL
在 MySQL optimization 的文檔寫到"All"的解釋
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.
看樣子對(duì)于 all, mysql 就使用比較笨的方法，那就改用 range 方式？ 因?yàn)?id 是遞增的，也很好修改 sql 。
select * from mytable where id offset and id offset + limit and index_col = xxx
explain 顯示 type = range，結(jié)果速度非常理想，返回結(jié)果快了幾十倍。
Limit語(yǔ)法：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT子句可以被用于強(qiáng)制 SELECT 語(yǔ)句返回指定的記錄數(shù)。LIMIT接受一個(gè)或兩個(gè)數(shù)字參數(shù)。參數(shù)必須是一個(gè)整數(shù)常量。
如果給定兩個(gè)參數(shù)，第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量，第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目。初始記錄行的偏移量是 0(而不是 1)。
為了與 PostgreSQL 兼容，MySQL 也支持句法：LIMIT # OFFSET #。
mysql SELECT * FROM table LIMIT 5,10; //檢索記錄行6-15
//為了檢索從某一個(gè)偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個(gè)參數(shù)為-1
mysql SELECT * FROM table LIMIT 95,-1; //檢索記錄行96-last
//如果只給定一個(gè)參數(shù)，它表示返回最大的記錄行數(shù)目，換句話說(shuō)，LIMIT n 等價(jià)于 LIMIT 0,n
mysql SELECT * FROM table LIMIT 5; //檢索前5個(gè)記錄行
MySQL的limit給分頁(yè)帶來(lái)了極大的方便，但數(shù)據(jù)量一大的時(shí)候，limit的性能就急劇下降。同樣是取10條數(shù)據(jù)，下面兩句就不是一個(gè)數(shù)量級(jí)別的。
select * from table limit 10000,10
select * from table limit 0,10
文中不是直接使用limit，而是首先獲取到offset的id然后直接使用limit size來(lái)獲取數(shù)據(jù)。根據(jù)他的數(shù)據(jù)，明顯要好于直接使用limit。
這里我具體使用數(shù)據(jù)分兩種情況進(jìn)行測(cè)試。
1、offset比較小的時(shí)候：
select * from table limit 10,10
//多次運(yùn)行，時(shí)間保持在0.0004-0.0005之間
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10
//多次運(yùn)行，時(shí)間保持在0.0005-0.0006之間，主要是0.0006
結(jié)論：偏移offset較小的時(shí)候，直接使用limit較優(yōu)。這個(gè)顯然是子查詢的原因。
2、offset大的時(shí)候：
select * from table limit 10000,10
//多次運(yùn)行，時(shí)間保持在0.0187左右
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10
//多次運(yùn)行，時(shí)間保持在0.0061左右，只有前者的1/3?？梢灶A(yù)計(jì)offset越大，后者越優(yōu)。
限流算法目前程序開發(fā)過(guò)程常用的限流算法有兩個(gè)：漏桶算法和令牌桶算法。
漏桶算法
漏桶算法的原理比較簡(jiǎn)單，請(qǐng)求進(jìn)入到漏桶中，漏桶以一定的速率漏水。當(dāng)請(qǐng)求過(guò)多時(shí)，水直接溢出?？梢钥闯?，漏桶算法可以強(qiáng)制限制數(shù)據(jù)的傳輸速度。如圖所示，把請(qǐng)求比作是水滴，水先滴到桶里，通過(guò)漏洞并以限定的速度出水，當(dāng)水來(lái)得過(guò)猛而出水不夠快時(shí)就會(huì)導(dǎo)致水直接溢出，即拒絕服務(wù)。
圖片來(lái)自網(wǎng)絡(luò)
漏桶的出水速度是恒定的，那么意味著如果瞬時(shí)大流量的話，將有大部分請(qǐng)求被丟棄掉（也就是所謂的溢出）。
令牌桶算法
令牌桶算法的原理是系統(tǒng)以一定速率向桶中放入令牌，如果有請(qǐng)求時(shí)，請(qǐng)求會(huì)從桶中取出令牌，如果能取到令牌，則可以繼續(xù)完成請(qǐng)求，否則等待或者拒絕服務(wù)。這種算法可以應(yīng)對(duì)突發(fā)程度的請(qǐng)求，因此比漏桶算法好。
圖片來(lái)自網(wǎng)絡(luò)
漏桶算法和令牌桶算法的選擇
兩者的主要區(qū)別漏桶算法能夠強(qiáng)行限制處理數(shù)據(jù)的速率，不論系統(tǒng)是否空閑。而令牌桶算法能夠在限制數(shù)據(jù)的平均處理速率的同時(shí)還允許某種程度的突發(fā)流量。如何理解上面的含義呢？漏桶算法，比如系統(tǒng)吞吐量是 120/s，業(yè)務(wù)請(qǐng)求 130/s，使用漏斗限流 100/s，起到限流的作用，多余的請(qǐng)求將產(chǎn)生等待或者丟棄。對(duì)于令牌桶算法，每秒產(chǎn)生 100 個(gè)令牌，系統(tǒng)容量 200 個(gè)令牌。正常情況下，業(yè)務(wù)請(qǐng)求 100/s 時(shí)，請(qǐng)求能被正常被處理。當(dāng)有突發(fā)流量過(guò)來(lái)比如 200 個(gè)請(qǐng)求時(shí)，因?yàn)橄到y(tǒng)容量有 200 個(gè)令牌可以同一時(shí)刻處理掉這 200 個(gè)請(qǐng)求。如果是漏桶算法，則只能處理 100 個(gè)請(qǐng)求，其他的請(qǐng)求等待或者被丟棄。
一，獲取mysql用戶下的進(jìn)程總數(shù)
ps -ef | awk '{print $1}' | grep "mysql" | grep -v "grep" | wc-1
二，主機(jī)性能狀態(tài)
# uptime
[root@ ~]# uptime
13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00
三，CPU使用率
# top
或
# vmstat
四，磁盤IO量
# vmstat 或 # iostat
五，swap進(jìn)出量[內(nèi)存]
# free
六，數(shù)據(jù)庫(kù)性能狀態(tài)
(1)QPS(每秒Query量)
QPS = Questions(or Queries) / seconds
mysql show /*50000 global */ status like 'Question';
(2)TPS(每秒事務(wù)量)
TPS = (Com_commit + Com_rollback) / seconds
mysql show status like 'Com_commit';
mysql show status like 'Com_rollback';
(3)key Buffer 命中率
key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%
mysql show status like 'Key%';
(4)InnoDB Buffer命中率
innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
mysql show status like 'innodb_buffer_pool_read%';
(5)Query Cache命中率
Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;
mysql show status like 'Qcache%';
(6)Table Cache狀態(tài)量
mysql show status like 'open%';
(7)Thread Cache 命中率
Thread_cache_hits = (1 - Threads_created / connections ) * 100%
mysql show status like 'Thread%';
mysql show status like 'Connections';
(8)鎖定狀態(tài)
mysql show status like '%lock%';
(9)復(fù)制延時(shí)量
mysql show slave status
(10) Tmp Table 狀況(臨時(shí)表狀況)
mysql show status like 'Create_tmp%';
(11) Binlog Cache 使用狀況
mysql show status like 'Binlog_cache%';
(12) Innodb_log_waits 量
mysql show status like 'innodb_log_waits';
當(dāng)然你也可以使用一下開源監(jiān)控軟件進(jìn)行監(jiān)控
一，RRDTool
二，Nagios
三，MRTG
四，Cacti
通過(guò)sysbench的oltp_read_write測(cè)試來(lái)模擬業(yè)務(wù)壓力、以此來(lái)給指定的硬件環(huán)境配置一份比較合理的MySQL配置文件。
環(huán)境介紹
硬件配置
請(qǐng)點(diǎn)擊輸入圖片描述
軟件環(huán)境
請(qǐng)點(diǎn)擊輸入圖片描述
優(yōu)化層級(jí)與指導(dǎo)思想
優(yōu)化層級(jí)
MySQL數(shù)據(jù)庫(kù)優(yōu)化可以在多個(gè)不同的層級(jí)進(jìn)行，常見的有：
SQL優(yōu)化
參數(shù)優(yōu)化
架構(gòu)優(yōu)化
本文重點(diǎn)關(guān)注：參數(shù)優(yōu)化
指導(dǎo)思想
日志先行 -- 一個(gè)事務(wù)能否成功提交的關(guān)鍵是日志是否成功落盤，與數(shù)據(jù)沒(méi)有太大的關(guān)系；也就是說(shuō)對(duì)寫的優(yōu)化可以表述為各方面的資源向?qū)懖僮鲀A斜。
瓶頸分析 -- 通過(guò)show global status 的各個(gè)計(jì)數(shù)器的值基本上就能分析出當(dāng)前瓶頸所在，再結(jié)合一些簡(jiǎn)單的系統(tǒng)層面的監(jiān)控工具如top iostat 就能明確瓶頸。
整體性能是“讀”“寫”之間的再平衡。
可以使用EXPLAIN語(yǔ)句查看SQL腳本性能：
EXPLAIN SELECT 語(yǔ)句；
例如學(xué)生表，其中電話字段建立了索引
學(xué)生表有6條記錄
查詢SQL語(yǔ)句的運(yùn)行效率
explain select * from students where phone like '%34%'\G
下圖rows為6，顯示該查詢掃描了6行記錄，沒(méi)有利用到索引
explain select * from students where phone like '45%'\G
下圖rows為1，顯示該查詢掃描了1行記錄，利用到了索引
兩圖比較like匹配查詢
第一種寫法 '%34%'?掃描了6行，索引未被利用；第二種寫法?'45%' 掃描1行，索引發(fā)揮作用，效率更高。
重慶分公司
重慶分公司
