自己的一個(gè)網(wǎng)站，由于單表的數(shù)據(jù)記錄高達(dá)了一百萬(wàn)條，造成數(shù)據(jù)訪問(wèn)很慢，尤其是頁(yè)碼大的頁(yè)面更是慢的不行。下面由學(xué)習(xí)啦小編為大家整理的Mysql分頁(yè)優(yōu)化的方法，希望大家喜歡!

　　Mysql分頁(yè)優(yōu)化的方法

　　1.普通分頁(yè)

　　數(shù)據(jù)分頁(yè)在網(wǎng)頁(yè)中十分多見(jiàn)，分頁(yè)一般都是limit start,offset,然后根據(jù)頁(yè)碼page計(jì)算start

　　select * from user limit 1,20

　　這種分頁(yè)在幾十萬(wàn)的時(shí)候分頁(yè)效率就會(huì)比較低了，MySQL需要從頭開(kāi)始一直往后計(jì)算，這樣大大影響效率

　　SELECT * from user limit 100001,20; //time 0.151sexplain SELECT * from user limit 100001,20;

　　我們可以用explain分析下語(yǔ)句，沒(méi)有用到任何索引，MySQL執(zhí)行的行數(shù)是16W+，于是我們可以想用到索引去實(shí)現(xiàn)分頁(yè)。

　　2.使用主鍵索引來(lái)優(yōu)化數(shù)據(jù)分頁(yè)

　　select * from user where id>(select id from user where id>=100000 limit 1) limit 20; //time 0.003s

　　使用explain分析語(yǔ)句，MySQL這次掃描的行數(shù)是8W+，時(shí)間也大大縮短。

　　explain select * from user where id>(select id from user where id>=100000 limit 1) limit 20;

　　Mysql如何分頁(yè)

　　一、基本分頁(yè)技巧

　　通常情況下，為了實(shí)現(xiàn)高效分頁(yè)，需要在查詢(xún)中WHERE條件列和排序列應(yīng)用組合索引。

　　例如，建立索引(a,b,c)使得以下查詢(xún)可以使用索引，提高查詢(xún)效率：

　　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、下面查詢(xún)是無(wú)法使用以上索引的

　　ORDER BY a ASC, b DESC, c DESC//排序方向不一致WHERE g = const ORDER BY b, c // 字段g不是索引一部分WHERE a = const ORDER BY c //沒(méi)有使用字段b WHERE a = const ORDER BY a, d // 字段d不是索引的一部分

　　二、解決大數(shù)據(jù)量翻頁(yè)問(wèn)題

　　1、將LIMIT M,N的查詢(xún)改為L(zhǎng)IMIT N

　　例如，使用LIMIT 10000,20，Mysql將需要讀取前10000行，然后獲取后面的20行 ，這是非常低效的，使用LIMIT N的方式，通過(guò)每頁(yè)第一條或最后一條記錄的id來(lái)做條件篩選，再配合降序和升序獲得上/下一頁(yè)的結(jié)果集 。

　　2、限制用戶(hù)翻頁(yè)數(shù)量

　　產(chǎn)品實(shí)際使用過(guò)程中用戶(hù)很少關(guān)心搜索結(jié)果的第1萬(wàn)條數(shù)據(jù)。

　　3、使用延遲關(guān)聯(lián)

　　通過(guò)使用覆蓋索引來(lái)查詢(xún)返回需要的主鍵，再根據(jù)返回的主鍵關(guān)聯(lián)原表獲得需要的行，這樣可以減少M(fèi)ysql掃描那些需要丟棄的行數(shù)。

　　如何解決MySQL遠(yuǎn)程連接不上

　　1.排除網(wǎng)絡(luò)或防火墻問(wèn)題

　　先看是否能ping通遠(yuǎn)程服務(wù)器，ping 192.168.1.211，如果不可以就是網(wǎng)絡(luò)問(wèn)題。然后，檢查端口是否被防火墻擋住了，telnet 192.168.1.211 3306，如果連接失敗，配置防火墻。

　　配置防火墻，開(kāi)啟3306端口

　　vi /etc/sysconfig/iptables-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT(允許3306端口通過(guò)防火墻)/etc/init.d/iptables restart(重啟防火墻使配置生效)

　　2.檢查MySQL配置

　　如果開(kāi)啟了防火墻，telnet還是失敗，通過(guò)netstat查看3306的端口狀態(tài)：

　　netstat -apn|grep 3306tcp6 0 0 127.0.0.1:3306 :::* LISTEN 13524/mysqld

　　注意地方，這說(shuō)明3306被綁定到了本地。檢查一下my.cnf的配置，這里可以配置綁定ip地址。

　　bind-address=addr

　　不配置或者IP配置為0.0.0.0，表示監(jiān)聽(tīng)所有客戶(hù)端連接。

　　ps:我開(kāi)啟3306端口，并且檢查MySQL配置之后，telent仍然失敗，但是在本機(jī)telnet是ok的，再三確認(rèn)了配置沒(méi)有問(wèn)題。后來(lái)跟我們的ucloud賬號(hào)管理員提了一下，才知道ucloud管理后臺(tái)也需要開(kāi)啟3306端口，用云服務(wù)器的留意這個(gè)。

　　3.檢查用戶(hù)訪問(wèn)權(quán)限

　　MySQL建用戶(hù)的時(shí)候會(huì)指定一個(gè)host，默認(rèn)是127.0.0.1/localhost，那么這個(gè)用戶(hù)就只能本機(jī)訪問(wèn)，其它機(jī)器用這個(gè)用戶(hù)帳號(hào)訪問(wèn)會(huì)提示沒(méi)有權(quán)限，host改為%，表示允許所有機(jī)器訪問(wèn)。