《MYSQL教程優(yōu)化mysql的limit offset的例子》要點(diǎn)：
本文介紹了MYSQL教程優(yōu)化mysql的limit offset的例子，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。
?經(jīng)常碰到的一個(gè)問題是limit的offset太高,如：limit 100000,20,這樣系統(tǒng)會(huì)查詢100020條,然后把前面的100000條都扔掉,這是開銷很大的操作,導(dǎo)致查詢很慢.假設(shè)所有分頁的頁面訪問頻率一樣,這樣的查詢平均掃描表的一半數(shù)據(jù).優(yōu)化的方法,要么限制訪問后面的頁數(shù),要么提升高偏移的查詢效率.MYSQL學(xué)習(xí)
???? 一個(gè)簡單的優(yōu)化辦法是使用覆蓋查詢(covering index)查詢,然后再跟全行的做join操作.如：
MYSQL學(xué)習(xí)
代碼如下:
SQL>select * from user_order_info limit 1000000,5;
這條語句就可以優(yōu)化為：
代碼如下:
select * from user_order_info inner join (select pin from user_order_info limit 1000000,5) as lim using(pin);
SQL>explain select * from user_order_info limit 1000000,5;
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
| 1 | SIMPLE | user_order_info | ALL | NULL | NULL | NULL | NULL | 23131886 | |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)
SQL>explain extended select * from user_order_info inner join (select pin from user_order_info limit 1000000,5) as lim using(pin);
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | |
| 1 | PRIMARY | user_order_info | eq_ref | PRIMARY | PRIMARY | 42 | lim.pin | 1 | 100.00 | |
| 2 | DERIVED | user_order_info | index | NULL | PRIMARY | 42 | NULL | 23131886 | 100.00 | Using index |
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
3 rows in set, 1 warning (0.66 sec)
根據(jù)兩個(gè)explain的對(duì)比,可以清晰發(fā)現(xiàn),第一個(gè)未使用索引,掃描了23131886行,第二個(gè)也掃描了同樣的行數(shù),但是使用了索引,效率提高了.這樣可以直接使用index得到數(shù)據(jù),而不去查詢表,當(dāng)找到需要的數(shù)據(jù)之后,在與全表join,獲得其他的列.
轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/6382.html
