前言

當我們操作MySQL的時候，如果數據量很小，那么我們如何處理都沒有問題。但是當一張表非常大的時候，我們一個大查詢，一個堆大插入，一個count(*)，一個limit都是非常恐怖的，因此，我在下面說幾種常用的優化方式。

當表數據非常多的時候，我們不能一次把查詢結果load進內存中，那會以下就OOM的，需要采用流式讀取，也就是Hibernate中的ScrollableResult的方式，它的底層實現就是jdbc的流式讀取。

1. JDBC流式讀取 （Hibernate ScrollableResult）

讀取操作開始遇到的問題是當sql查詢數據量比較大時候程序直接拋錯，或是讀不出來ResultSet的next方法阻塞。

Root Casue: mysql driver 默認的行為是需要把整個結果全部讀取到內存(ResultSet)中，才允許讀取結果。當遇到大數據的時候，這顯然會導致OOM。這顯然與期望的行為不一致，期望的行為是jdbc流的方式讀取，當結果從mysql服務端返回后立即開始讀取處理。這樣應用就不需要大量內存來存儲這個結果集。

正確的jdbc流式讀取代碼：

PreparedStatement ps = connection.prepareStatement("select .. from ..", 
 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 
//forward only read only也是mysql 驅動的默認值，所以不指定也是可以的 比如： PreparedStatement ps = connection.prepareStatement("select .. from .."); 
ps.setFetchSize(Integer.MIN_VALUE); //（也可以修改jdbc url通過defaultFetchSize參數來設置，這樣默認所以的返回結果都是通過流方式讀取.）
ResultSet rs = ps.executeQuery(); 
while (rs.next()) { 
 System.out.println(rs.getString("fieldName")); 
}

代碼分析：下面是mysql判斷是否開啟流式讀取結果的方法，有三個條件forward-only，read-only，fatch size是Integer.MIN_VALUE

/**
 * We only stream result sets when they are forward-only, read-only, and the
 * fetch size has been set to Integer.MIN_VALUE
 *
 * @return true if this result set should be streamed row at-a-time, rather
 * than read all at once.
 */
protected boolean createStreamingResultSet() {
 try {
 synchronized(checkClosed().getConnectionMutex()) {
 return ((this.resultSetType == JAVA.sql.ResultSet.TYPE_FORWARD_ONLY)
 && (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY) && (this.fetchSize == Integer.MIN_VALUE));
 }
 } catch (SQLException e) {
 // we can't break the interface, having this be no-op in case of error is ok
 return false;
 }
}

2. JDBC批量寫入

當需要很多的數據一次性寫入表中。如果是一條一條的執行insert來寫入，非常慢。

Root Cause: 第一，單條寫入需要大量的Database請求響應交互。每個insert請求都是一個獨立的Transaction commit。這樣網絡延遲大的情況下多次請求會有大量的時間消耗的網絡延遲上。第二，是由于每個Transaction，Database都會有刷新磁盤操作寫事務日志，保證事務的持久性。由于每個事務只是寫入一條數據，所以磁盤io利用率不高，因為對于磁盤io是按塊來的，所以連續寫入大量數據效率更好。

所以，必須改成批量插入的方式，減少請求數與Transaction。

下面是批量插入的例子：還有jdbc連接串必須加下rewriteBatchedStatements=true

int batchSize = 1000;
PreparedStatement ps = connection.prepareStatement("insert into tb1 (c1,c2,c3...) values (?,?,?...)");
for (int i = 0; i < list.size(); i++) {
 ps.setXXX(list.get(i).getC1());
 ps.setYYY(list.get(i).getC2());
 ps.setZZZ(list.get(i).getC3());
 ps.addBatch();
 if ((i + 1) % batchSize == 0) {
 ps.executeBatch();
 }
}
if (list.size() % batchSize != 0) {
 ps.executeBatch();
}

上面代碼示例是每1000條數據發送一次請求。mysql驅動內部在應用端會把多次addBatch()的參數合并成一條multi value的insert語句發送給db去執行

比如insert into tb1(c1,c2,c3) values (v1,v2,v3),(v4,v5,v6),(v7,v8,v9)...

這樣可以比每條一個insert 明顯少很多請求。減少了網絡延遲消耗時間與磁盤io時間,從而提高了tps。

。

代碼分析: 從代碼可以看出，

1 rewriteBatchedStatements=true，insert是參數化語句且不是insert ... select 或者 insert... on duplicate key update with an id=last_insert_id(...)的話會執行

executeBatchedInserts，也就是muti value的方式

2 rewriteBatchedStatements=true 語句是都是參數化(沒有addbatch(sql)方式加入的)的而且mysql server版本在4.1以上 語句超過三條，則執行executePreparedBatchAsMultiStatement

就是將多個語句通過;分隔一次提交多條sql。比如 "insert into tb1(c1,c2,c3) values (v1,v2,v3);insert into tb1(c1,c2,c3) values (v1,v2,v3)..."

3 其余的執行executeBatchSerially，也就是還是一條條處理

public void addBatch(String sql)throws SQLException {
 synchronized(checkClosed().getConnectionMutex()) {
 this.batchHasPlainStatements = true;
 super.addBatch(sql);
 }
}
public int[] executeBatch()throws SQLException {
 //...
 if (!this.batchHasPlainStatements
 && this.connection.getRewriteBatchedStatements()) {
 if (canRewriteAsMultiValueInsertAtSqlLevel()) {
 return executeBatchedInserts(batchTimeout);
 }
 if (this.connection.versionMeetsMinimum(4, 1, 0)
 && !this.batchHasPlainStatements
 && this.batchedArgs != null
 && this.batchedArgs.size() > 3 /* cost of option setting rt-wise */
 )
 {
 return executePreparedBatchAsMultiStatement(batchTimeout);
 }
 }
 return executeBatchSerially(batchTimeout);
 //.....
}

executeBatchedInserts相比executePreparedBatchAsMultiStatement的方式傳輸效率更好，因為一次請求只重復一次前面的insert table (c1,c2,c3)

mysql server 對請求報文的最大長度有限制,如果batch size 太大造成請求報文超過最大限制，mysql 驅動會內部按最大報文限制查分成多個報文。所以要真正減少提交次數

還要檢查下mysql server的max_allowed_packet 否則batch size 再大也沒用.

mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 167772160 |
+--------------------+-----------+
1 row in set (0.00 sec)

要想驗證mysql 發送了正確的sql 有兩種方式

1 抓包，下圖是wireshark在 應用端抓包mysql的報文

2 另一個辦法是在mysql server端開啟general log 可以查看mysql收到的所有sql

3 在jdbc url上加上參數traceProtocol=true 或者profileSQL=true or autoGenerateTestcaseScript=true

性能測試對比

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.alibaba.druid.pool.DruidDataSource;
public class BatchInsert {
 
 public static void main(String[] args) throws SQLException {
 
 int batchSize = 1000;
 int insertCount = 1000;
 
 testDefault(batchSize, insertCount);
 
 testRewriteBatchedStatements(batchSize,insertCount);
 
 }
 
 private static void testDefault(int batchSize, int insertCount) throws SQLException { 
 
 long start = System.currentTimeMillis();
 
 doBatchedInsert(batchSize, insertCount,"");
 
 long end = System.currentTimeMillis();
 
 System.out.println("default:" + (end -start) + "ms");
 }
 
 
 private static void testRewriteBatchedStatements(int batchSize, int insertCount) throws SQLException {
 long start = System.currentTimeMillis();
 
 doBatchedInsert(batchSize, insertCount, "rewriteBatchedStatements=true");
 
 long end = System.currentTimeMillis();
 
 System.out.println("rewriteBatchedStatements:" + (end -start) + "ms");
 }
 
 
 private static void doBatchedInsert(int batchSize, int insertCount, String mysqlProperties) throws SQLException {
 DruidDataSource dataSource = new DruidDataSource();
 dataSource.setUrl("jdbc:mysql://ip:3306/test?" + mysqlProperties);
 dataSource.setUsername("name");
 dataSource.setPassword("password");
 
 dataSource.init();
 
 Connection connection = dataSource.getConnection();
 
 PreparedStatement preparedStatement = connection.prepareStatement("insert into Test (name,gmt_created,gmt_modified) values (?,now(),now())");
 
 for (int i = 0; i < insertCount; i++) {
 preparedStatement.setString(1, i+" ");
 preparedStatement.addBatch();
 if((i+1) % batchSize == 0) {
 preparedStatement.executeBatch();
 }
 }
 preparedStatement.executeBatch();
 
 connection.close(); 
 
 dataSource.close();
 }
}

網絡環境ping測試延遲是35ms ,測試結果：

default:75525ms
rewriteBatchedStatements:914ms

3. 批量更新

//Session是持久層操作的基礎，相當于JDBC中的Connection。

Session session = sessionFactory.openSession();

try{ //為保持事務的原子性，必須捕捉異常。所有事務都放在這一代碼塊里。

//操作事務時(增、刪、改)必須顯式的調用Transaction,如果不啟動Transaction，數據庫不會有變化(默認：session.autoCommit=false)。

Transaction tx = session.beginTransaction();

for(int i=0; i<=1000; i++){

Student stu = new Student(...);

session.save(stu);//set value to stu

//批量更新：為防止內存不足，分成每20個一批發送過去。 如果不是大批量更新，則不需要這樣

if(i%20==0){

//強制內存中數據同步到mysql，sql打印出并執行，只是事務沒有commit，其他的線程看不到

session.flush();

session.clear();

}

}

//transaction commit默認會自動flush（查詢之前、事務提交時都會自動flush,之前手動flush只是為了內存考慮）。

tx.commit();//提交事務，Hibernate不喜歡拋異常，如有需要，自己捕捉。

//查詢方法。如果有必要，也可以用事務(調用Transaction)

String hql = "from Student s where s.stuNo like ? and s.Sal > ?";//Student是類而不是表

List list = session.createQuery(hql)

.setString(0, "a00_").setDouble(1, 3000.0)//設置HQL的第一二個問號取值

.list();//Hibernate里面，沒有返回值的都默認返回List

StringBuffer sb = new StringBuffer();

for(Student st :(List<Student>)list){//(List<Student>)強制類型轉換

sb.Append(st.getOid()+" "+st.getName()+"n");//拿到Student類里的屬性

}

System.out.print(sb.toString());//直接打印sb也可以，它也是調用toString，但這樣寫效率更高

} catch (HibernateException e) {

e.printStackTrace();

session.getTransaction().rollback();//如果事務不成功，則rollback

} finally {

// 如果是openSession()方法創建的session，必須手動關閉

session.close();//注意關閉順序，session先關，Factory最后關(因為它可以啟動多個session)

sessionFactory.close();//關閉SessionFactory，雖然這里沒看到它，但在HbnUtil里開啟了。

}

 

4.%20表中大數據分頁

我們先從一個常用但性能很差的查詢來看一看。

SELECT%20*
FROM%20city
ORDER%20BY%20id%20DESC
LIMIT%200,%2015

這個查詢耗時0.00sec。So，這個查詢有什么問題呢？實際上，這個查詢語句和參數都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。

CREATE%20TABLE%20city%20(
%20id%20int(10)%20unsigned%20NOT%20NULL%20AUTO_INCREMENT,
%20city%20varchar(128)%20NOT%20NULL,
%20PRIMARY%20KEY%20(id)
)%20ENGINE=InnoDB;

真正的問題在于offset(分頁偏移量)很大的時候，像下面這樣：

SELECT%20*
FROM%20city
ORDER%20BY%20id%20DESC
LIMIT%20100000,%2015;

上面的查詢在有2M行記錄時需要0.22sec，通過EXPLAIN查看SQL的執行計劃可以發現該SQL檢索了100015行，但最后只需要15行。大的分頁偏移量會增加使用的數據，MySQL會將大量最終不會使用的數據加載到內存中。就算我們假設大部分網站的用戶只訪問前幾頁數據，但少量的大的分頁偏移量的請求也會對整個系統造成危害。Facebook意識到了這一點，但Facebook并沒有為了每秒可以處理更多的請求而去優化數據庫，而是將重心放在將請求響應時間的方差變小。

對于分頁請求，還有一個信息也很重要，就是總共的記錄數。我們可以通過下面的查詢很容易的獲取總的記錄數。

SELECT%20COUNT(*)
FROM%20city;

然而，上面的SQL在采用InnoDB為存儲引擎時需要耗費9.28sec。一個不正確的優化是采用SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS可以在能夠在分頁查詢時事先準備好符合條件的記錄數，隨后只要執行一句select%20FOUND_ROWS();%20就能獲得總記錄數。但是在大多數情況下，查詢語句簡短并不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢性能。

SELECT%20SQL_CALC_FOUND_ROWS%20*
FROM%20city
ORDER%20BY%20id%20DESC
LIMIT%20100000,%2015;

這個語句耗時20.02sec，是上一個的兩倍。事實證明使用SQL_CALC_FOUND_ROWS做分頁是很糟糕的想法。

下面來看看到底如何優化。文章分為兩部分，第一部分是如何獲取記錄的總數目，第二部分是獲取真正的記錄。

高效的計算行數

如果采用的引擎是MyISAM，可以直接執行COUNT(*)去獲取行數即可。相似的，在堆表中也會將行數存儲到表的元信息中。但如果引擎是InnoDB情況就會復雜一些，因為InnoDB不保存表的具體行數。

我們可以將行數緩存起來，然后可以通過一個守護進程定期更新或者用戶的某些操作導致緩存失效時，執行下面的語句：

SELECT%20COUNT(*)
FROM%20city
USE%20INDEX(PRIMARY);

獲取記錄

下面進入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創建一個新的表“news”，按照時事性排序(最新發布的在最前面)，實現一個高性能的分頁。為了簡單，我們就假設最新發布的新聞的Id也是最大的。

CREATE%20TABLE%20news(
%20id%20INT%20UNSIGNED%20PRIMARY%20KEY%20AUTO_INCREMENT,
%20title%20VARCHAR(128)%20NOT%20NULL
)%20ENGINE=InnoDB;

一個比較高效的方式是基于用戶展示的最后一個新聞Id。查詢下一頁的語句如下，需要傳入當前頁面展示的最后一個Id。

SELECT%20*
FROM%20news%20WHERE%20id%20<%20$last_id
ORDER%20BY%20id%20DESC
LIMIT%20$perpage

查詢上一頁的語句類似，只不過需要傳入當前頁的第一個Id，并且要逆序。

SELECT%20*
FROM%20news%20WHERE%20id%20>%20$last_id
ORDER%20BY%20id%20ASC
LIMIT%20$perpage

上面的查詢方式適合實現簡易的分頁，即不顯示具體的頁數導航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實現真正的頁面導航還是很難的,下面看看另一種方式。

SELECT%20id
FROM%20(
%20SELECT%20id,%20((@cnt:=%20@cnt%20+%201)%20+%20$perpage%20-%201)%20%%20$perpage%20cnt
%20FROM%20news
%20JOIN%20(SELECT%20@cnt:=%200)T
%20WHERE%20id%20<%20$last_id
%20ORDER%20BY%20id%20DESC
%20LIMIT%20$perpage%20*%20$buttons
)C
WHERE%20cnt%20=%200;

通過上面的語句可以為每一個分頁的按鈕計算出一個offset對應的id。這種方法還有一個好處。假設，網站上正在發布一片新的文章，那么所有文章的位置都會往后移一位，所以如果用戶在發布文章時換頁，那么他會看見一篇文章兩次。如果固定了每個按鈕的offset%20Id，這個問題就迎刃而解了。Mark%20Callaghan發表過一篇類似的博客，利用了組合索引和兩個位置變量，但是基本思想是一致的。

如果表中的記錄很少被刪除、修改，還可以將記錄對應的頁碼存儲到表中，并在該列上創建合適的索引。采用這種方式，當新增一個記錄的時候，需要執行下面的查詢重新生成對應的頁號。

SET%20p:=%200;
UPDATE%20news%20SET%20page=CEIL((p:=%20p%20+%201)%20/%20$perpage)%20ORDER%20BY%20id%20DESC;

當然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護。

UPDATE%20pagination%20T
JOIN%20(
%20SELECT%20id,%20CEIL((p:=%20p%20+%201)%20/%20$perpage)%20page
%20FROM%20news
%20ORDER%20BY%20id
)C
ON%20C.id%20=%20T.id
SET%20T.page%20=%20C.page;

現在想獲取任意一頁的元素就很簡單了：

SELECT%20*
FROM%20news%20A
JOIN%20pagination%20B%20ON%20A.id=B.ID
WHERE%20page=$offset;

還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用于數據集相對小，并且沒有可用的索引的情況下—比如處理搜索結果時。在一個普通的服務器上執行下面的查詢，當有2M條記錄時，要耗費2sec左右。這種方式比較簡單，創建一個用來存儲所有Id的臨時表即可(這也是最耗費性能的地方)。

CREATE%20TEMPORARY%20TABLE%20_tmp%20(KEY%20SORT(random))
SELECT%20id,%20FLOOR(RAND()%20*%200x8000000)%20random
FROM%20city;
%20
ALTER%20TABLE%20_tmp%20ADD%20OFFSET%20INT%20UNSIGNED%20PRIMARY%20KEY%20AUTO_INCREMENT,%20DROP%20INDEX%20SORT,%20ORDER%20BY%20random;

接下來就可以向下面一樣執行分頁查詢了。

SELECT%20*
FROM%20_tmp
WHERE%20OFFSET%20>=%20$offset
ORDER%20BY%20OFFSET
LIMIT%20$perpage;

簡單來說，對于分頁的優化就是。。。避免數據量大時掃描過多的記錄。

軟件開發中，常用要用到分頁、計算總數，數據量超過千萬、上億的時候，往往count%20的需要超過%201s%20的執行時間，甚至%203-5s，對于一個追求性能的前沿團隊來說，這個不能忍啊！

那么我們再從頭分析以下為什么會慢？

mysql%20會對所有符合的條件做一次掃描。

select%20count(*)%20from%20table_a%20where%20a%20=%20'%d'%20...

如果%20a=%d%20的數據有%201000W%20條，那么數據庫就會掃描一次%201000W%20條數據庫。如果不帶查詢條件，那這種全表掃描將更可怕。

count(*)%20和%20count(1)、count(0)

count(expr)%20為統計%20expr%20不為空的記錄

count(*)%20它會計算總行數，不管你字段是否有值都會列入計算范圍。

coount(0),count(1)%20沒有差別，它會計算總行數

Example%201:

mysql>%20explain%20extended%20select%20count(*)%20from%20user;
...
1%20row%20in%20set,%201%20warning%20(0.34%20sec)
%20
mysql>%20show%20warnings;
+-------+------+--------------------------------------------------+
|%20Level%20|%20Code%20|%20Message%20|
+-------+------+--------------------------------------------------+
|%20Note%20|%201003%20|%20select%20count(0)%20AS%20`count(*)`%20from%20`user`%20|

Example%202:

mysql>%20select%20count(*)%20from%20login_log
%20->%20;
+----------+
|%20count(*)%20|
+----------+
|%202513%20|
+----------+
1%20rows%20in%20set%20(0.00%20sec)
%20
mysql>%20select%20count(logoutTime)%20from%20login_log;
+-------------------+
|%20count(logoutTime)%20|
+-------------------+
|%20308%20|
+-------------------+
1%20rows%20in%20set%20(0.00%20sec)

怎么解決？

MyISAM%20DB

MyISAM%20引擎很容易獲得總行數的統計，查詢速度變得更快。因為%20MyISAM%20存儲引擎已經存儲了表的總行數。

MyISAM%20會為每張表維護一個%20row%20count%20的計數器，每次新增加一行，這個計數器就加%201。但是如果有查詢條件，那么%20MyISAM%20也%20game%20over%20了，MyISAM%20引擎不支持條件緩存。

On%20MyISAM,%20doing%20a%20query%20that%20does%20SELECT%20COUNT(*)%20FROM%20{some_table},%20is%20very%20fast,%20since%20MyISAM%20keeps%20the%20information%20in%20the%20index

其他%20DB%20引擎

受到%20MySIAM%20DB%20的啟發，我們可以手動維護總數緩存在表的索引中了。

1、如果%20ID%20連續，且基本不會斷開。直接取最大值%20ID

2、如果表中存在連續的數字列并設為索引，那么通過頁碼即可計算出此字段的范圍，直接作范圍查詢即可：

start%20=%20(page-1)*pagesize+1%20
end%20=%20page*pagesize%20
select%20*%20from%20table%20where%20id%20>start%20and%20id%20<=end

1、涉及到總數操作，專門維護一個總數。新增一個用戶，總數值加%201,%20需要總數的時候直接拿這個總數,%20比如分頁時。如果有多個條件，那么就需要維護多個總數列。該方案的擴展性更好，隨著用戶表數量增大,%20水平切分用戶表，要獲取用戶總數，直接查詢這個總數表即可。

分頁正反偏移

數據庫自帶的%20skip%20和%20limit%20的限制條件為我們創建了分頁的查詢方式，但是如果利用不對，性能會出現千倍萬倍差異。

簡單一點描述：limit%20100000,20%20的意思掃描滿足條件的%20100020%20行，扔掉前面的%20100000%20行，返回最后的%2020%20行，問題就在這里。如果我反向查詢%20oder%20by%20xx%20desc%20limit%200,20，那么我只要索引%2020%20條數據。

Example%203

mysql>%20select%20count(*)%20from%20elastic_task_log_copy;
+----------+
|%20count(*)%20|
+----------+
|%201705162%20|
+----------+
1%20rows%20in%20set%20(2.31%20sec)

正向偏移查詢。超級浪費的查詢，需要先%20skip%20大量的符合條件的查詢。

mysql>%20select%20id%20from%20elastic_task_log_copy%20order%20by%20id%20asc%20limit%201705152,10;
+---------+
|%20id%20|
+---------+
|%201705157%20|
|%201705158%20|
|%201705159%20|
|%201705160%20|
|%201705161%20|
|%201705162%20|
|%201705163%20|
|%201705164%20|
|%201705165%20|
|%201705166%20|
+---------+
10%20rows%20in%20set%20(2.97%20sec)

反向偏移查詢。同樣的查詢結果，千差萬別的結果。

mysql>%20select%20id%20from%20elastic_task_log_copy%20order%20by%20id%20desc%20limit%200,10;
+---------+
|%20id%20|
+---------+
|%201705166%20|
|%201705165%20|
|%201705164%20|
|%201705163%20|
|%201705162%20|
|%201705161%20|
|%201705160%20|
|%201705159%20|
|%201705158%20|
|%201705157%20|
+---------+
10%20rows%20in%20set%20(0.01%20sec)

這兩條%20sql%20是為查詢最后一頁的翻頁%20sql%20查詢用的。由于一次翻頁往往只需要查詢較小的數據，如%2010%20條，但需要向后掃描大量的數據，也就是越往后的翻頁查詢，掃描的數據量會越多，查詢的速度也就越來越慢。

由于查詢的數據量大小是固定的，如果查詢速度不受翻頁的頁數影響，或者影響最低，那么這樣是最佳的效果了（查詢最后最幾頁的速度和開始幾頁的速度一致）。

在翻頁的時候，往往需要對其中的某個字段做排序（這個字段在索引中），升序排序。那么可不可以利用索引的有序性%20來解決上面遇到的問題。

比如有%2010000%20條數據需要做分頁，那么前%205000%20條做%20asc%20排序，后%205000%20條%20desc%20排序，在%20limit%20startnum，pagesize%20參數中作出相應的調整。

但是這無疑給應用程序帶來復雜，這條%20sql%20是用于論壇回復帖子的%20sql，往往用戶在看帖子的時候，一般都是查看前幾頁和最后幾頁，那么在翻頁的時候最后幾頁的翻頁查詢采用%20desc%20的方式來實現翻頁，這樣就可以較好的提高性能。

游標：上一頁的最大值或者最小值

如果你知道上一頁和下一頁的臨界值，那么翻頁查詢也是信手拈來了，直接就告訴了數據庫我的起始查詢在哪，也就沒有什么性能問題了。我更愿意稱這個東西為游標%20(Cursor)。

如果做下拉刷新，那么就直接避免掉分頁的問題了。根據上一頁的最后一個值去請求新數據。

mysql>%20select%20id%20from%20elastic_task_log_copy%20where%20id%20>=%201699999%20limit%2010;
+---------+
|%20id%20|
+---------+
|%201699999%20|
|%201700000%20|
|%201700001%20|
|%201700002%20|
|%201700003%20|
|%201700004%20|
|%201700005%20|
|%201700006%20|
|%201700007%20|
|%201700008%20|
+---------+
10%20rows%20in%20set%20(0.01%20sec)

緩存和不精準

數據量達到一定程度的時候，用戶根本就不關心精準的總數,%20沒人關心差幾個。看看知乎、微博、微信訂閱號，不精準的統計到處都是。

如果每次點擊分頁的時候都進行一次%20count%20操作，那速度肯定不會快到哪里去。他們一般也是采用計數器的辦法。每次新增加一個粉絲，就把值加%201，直接在用戶信息存儲一個總數，一段時間后重新查詢一次，更新該緩存。這樣分頁的時候直接拿這個總數進行分頁，顯示的時候直接顯示模糊之就行。

那為什么微信公眾號的閱讀量只有%2010W+%20這個量級呢？100W+%20級去哪了！

5.%20其他的建議

1、mysql%20的數據查詢,%20大小字段要分開,%20這個還是有必要的,%20除非一點就是你查詢的都是索引內容而不是表內容,%20比如只查詢%20id%20等等

2、查詢速度和索引有很大關系也就是索引的大小直接影響你的查詢效果,%20但是查詢條件一定要建立索引,%20這點上注意的是索引字段不能太多，太多索引文件就會很大那樣搜索只能變慢,

3、查詢指定的記錄最好通過%20Id%20進行%20in%20查詢來獲得真實的數據.%20其實不是最好而是必須，也就是你應該先查詢出復合的%20ID%20列表,%20通過%20in%20查詢來獲得數據

4、mysql%20千萬級別數據肯定是沒問題的,%20畢竟現在的流向%20web2.0%20網站大部分是%20mysql%20的

5、合理分表也是必須的,%20主要涉及橫向分表與縱向分表,%20如把大小字段分開,%20或者每%20100%20萬條記錄在一張表中等等,%20像上面的這個表可以考慮通過%20uid%20的范圍分表,%20或者通過只建立索引表,%20去掉相對大的字段來處理.

6、count()%20時間比較長,%20但是本身是可以緩存在數據庫中或者緩存在程序中的,%20因為我們當時使用在后臺所以第一頁比較慢但是后面比較理想

7、SELECT%20id%20相對%20SELECT%20差距還是比較大的,%20可以通過上面的方法來使用%20SELECT%20id%20+%20SELECT%20…%20IN%20查詢來提高性能

8、必要的索引是必須的,%20還是要盡量返回%205%-20%%20的結果級別其中小于%205%%20最理想;

9、mysql%20分頁的前面幾頁速度很快,%20越向后性能越差,%20可以考慮只帶上一頁,%20下一頁不帶頁面跳轉的方法,%20呵呵這個比較垃圾但是也算是個方案,%20只要在前后多查一條就能解決了.%20比如%20100,10%20你就差%2099,12%20呵呵，這樣看看前后是否有結果.

10、前臺還是要通過其他手段來處理,%20比如%20lucene/Solr+mysql%20結合返回翻頁結果集,%20或者上面的分表

11、總數可能是存在內存中,%20這樣分頁計算的時候速度很快。累加操作的時候將內存中的值加%201。總數這個值要持久化，還是要存到磁盤上的，也就是數據庫中%20(可以是關系型數據庫，也可以是%20mongdb%20這樣的數據庫很適合存儲計數)。把總數放在內存中，只是避免頻繁的磁盤%20i/0%20操作%20(操作數據庫就要涉及到磁盤讀寫)。

如果對此有什么疑問歡迎留言！！！