Mysql怎么做分頁優(yōu)化的方法_Mysql分頁優(yōu)化有什么方法
自己的一個網(wǎng)站,由于單表的數(shù)據(jù)記錄高達了一百萬條,造成數(shù)據(jù)訪問很慢,尤其是頁碼大的頁面更是慢的不行。下面由學(xué)習(xí)啦小編為大家整理的Mysql分頁優(yōu)化的方法,希望大家喜歡!
Mysql分頁優(yōu)化的方法
1.普通分頁
數(shù)據(jù)分頁在網(wǎng)頁中十分多見,分頁一般都是limit start,offset,然后根據(jù)頁碼page計算start
select * from user limit 1,20
這種分頁在幾十萬的時候分頁效率就會比較低了,MySQL需要從頭開始一直往后計算,這樣大大影響效率
SELECT * from user limit 100001,20; //time 0.151sexplain SELECT * from user limit 100001,20;
我們可以用explain分析下語句,沒有用到任何索引,MySQL執(zhí)行的行數(shù)是16W+,于是我們可以想用到索引去實現(xiàn)分頁。
2.使用主鍵索引來優(yōu)化數(shù)據(jù)分頁
select * from user where id>(select id from user where id>=100000 limit 1) limit 20; //time 0.003s
使用explain分析語句,MySQL這次掃描的行數(shù)是8W+,時間也大大縮短。
explain select * from user where id>(select id from user where id>=100000 limit 1) limit 20;
Mysql如何分頁
一、基本分頁技巧
通常情況下,為了實現(xiàn)高效分頁,需要在查詢中WHERE條件列和排序列應(yīng)用組合索引。
例如,建立索引(a,b,c)使得以下查詢可以使用索引,提高查詢效率:
1、字段排序
ORDER BY a ORDER BY a,bORDER BY a, b, c ORDER BY a DESC, b DESC, c DESC
2、篩選和排序
WHERE a = const ORDER BY b, c WHERE a = const AND b = const ORDER BY c WHERE a = const ORDER BY b, c WHERE a = const AND b > const ORDER BY b, c
3、下面查詢是無法使用以上索引的
ORDER BY a ASC, b DESC, c DESC//排序方向不一致WHERE g = const ORDER BY b, c // 字段g不是索引一部分WHERE a = const ORDER BY c //沒有使用字段b WHERE a = const ORDER BY a, d // 字段d不是索引的一部分
二、解決大數(shù)據(jù)量翻頁問題
1、將LIMIT M,N的查詢改為LIMIT N
例如,使用LIMIT 10000,20,Mysql將需要讀取前10000行,然后獲取后面的20行 ,這是非常低效的,使用LIMIT N的方式,通過每頁第一條或最后一條記錄的id來做條件篩選,再配合降序和升序獲得上/下一頁的結(jié)果集 。
2、限制用戶翻頁數(shù)量
產(chǎn)品實際使用過程中用戶很少關(guān)心搜索結(jié)果的第1萬條數(shù)據(jù)。
3、使用延遲關(guān)聯(lián)
通過使用覆蓋索引來查詢返回需要的主鍵,再根據(jù)返回的主鍵關(guān)聯(lián)原表獲得需要的行,這樣可以減少Mysql掃描那些需要丟棄的行數(shù)。
如何解決MySQL遠程連接不上
1.排除網(wǎng)絡(luò)或防火墻問題
先看是否能ping通遠程服務(wù)器,ping 192.168.1.211,如果不可以就是網(wǎng)絡(luò)問題。然后,檢查端口是否被防火墻擋住了,telnet 192.168.1.211 3306,如果連接失敗,配置防火墻。
配置防火墻,開啟3306端口
vi /etc/sysconfig/iptables-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT(允許3306端口通過防火墻)/etc/init.d/iptables restart(重啟防火墻使配置生效)
2.檢查MySQL配置
如果開啟了防火墻,telnet還是失敗,通過netstat查看3306的端口狀態(tài):
netstat -apn|grep 3306tcp6 0 0 127.0.0.1:3306 :::* LISTEN 13524/mysqld
注意地方,這說明3306被綁定到了本地。檢查一下my.cnf的配置,這里可以配置綁定ip地址。
bind-address=addr
不配置或者IP配置為0.0.0.0,表示監(jiān)聽所有客戶端連接。
ps:我開啟3306端口,并且檢查MySQL配置之后,telent仍然失敗,但是在本機telnet是ok的,再三確認(rèn)了配置沒有問題。后來跟我們的ucloud賬號管理員提了一下,才知道ucloud管理后臺也需要開啟3306端口,用云服務(wù)器的留意這個。
3.檢查用戶訪問權(quán)限
MySQL建用戶的時候會指定一個host,默認(rèn)是127.0.0.1/localhost,那么這個用戶就只能本機訪問,其它機器用這個用戶帳號訪問會提示沒有權(quán)限,host改為%,表示允許所有機器訪問。