有點麻煩的感覺，如列數(shù)據(jù)為 123 按你的意思是要得到 11這樣的數(shù)據(jù)？這列的方差又是什么意思，讀書少呀，看不懂
創(chuàng)新互聯(lián)主要從事網(wǎng)站建設(shè)、網(wǎng)站制作、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)文成,10余年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):13518219792
很多應(yīng)用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導(dǎo)航欄。然而，如何通過MySQL更好的實現(xiàn)分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解數(shù)據(jù)庫的底層或多或少有助于優(yōu)化分頁查詢。
我們先從一個常用但性能很差的查詢來看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
這個查詢耗時0.00sec。So，這個查詢有什么問題呢？實際上，這個查詢語句和參數(shù)都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的問題在于offset(分頁偏移量)很大的時候，像下面這樣：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查詢在有2M行記錄時需要0.22sec，通過EXPLAIN查看SQL的執(zhí)行計劃可以發(fā)現(xiàn)該SQL檢索了100015行，但最后只需要15行。大的分頁偏移量會增加使用的數(shù)據(jù)，MySQL會將大量最終不會使用的數(shù)據(jù)加載到內(nèi)存中。就算我們假設(shè)大部分網(wǎng)站的用戶只訪問前幾頁數(shù)據(jù)，但少量的大的分頁偏移量的請求也會對整個系統(tǒng)造成危害。Facebook意識到了這一點，但Facebook并沒有為了每秒可以處理更多的請求而去優(yōu)化數(shù)據(jù)庫，而是將重心放在將請求響應(yīng)時間的方差變小。
對于分頁請求，還有一個信息也很重要，就是總共的記錄數(shù)。我們可以通過下面的查詢很容易的獲取總的記錄數(shù)。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB為存儲引擎時需要耗費9.28sec。一個不正確的優(yōu)化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先準(zhǔn)備好符合條件的記錄數(shù)，隨后只要執(zhí)行一句 select FOUND_ROWS(); 就能獲得總記錄數(shù)。但是在大多數(shù)情況下，查詢語句簡短并不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
這個語句耗時20.02sec，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。
下面來看看到底如何優(yōu)化。文章分為兩部分，第一部分是如何獲取記錄的總數(shù)目，第二部分是獲取真正的記錄。
高效的計算行數(shù)
如果采用的引擎是MyISAM，可以直接執(zhí)行COUNT(*)去獲取行數(shù)即可。相似的，在堆表中也會將行數(shù)存儲到表的元信息中。但如果引擎是InnoDB情況就會復(fù)雜一些，因為InnoDB不保存表的具體行數(shù)。
我們可以將行數(shù)緩存起來，然后可以通過一個守護進程定期更新或者用戶的某些操作導(dǎo)致緩存失效時，執(zhí)行下面的語句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
獲取記錄
下面進入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經(jīng)說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創(chuàng)建一個新的表“news”，按照時事性排序(最新發(fā)布的在最前面)，實現(xiàn)一個高性能的分頁。為了簡單，我們就假設(shè)最新發(fā)布的新聞的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一個比較高效的方式是基于用戶展示的最后一個新聞Id。查詢下一頁的語句如下，需要傳入當(dāng)前頁面展示的最后一個Id。
SELECT *
FROM news WHERE id $last_id
ORDER BY id DESC
LIMIT $perpage
查詢上一頁的語句類似，只不過需要傳入當(dāng)前頁的第一個Id，并且要逆序。
SELECT *
FROM news WHERE id $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查詢方式適合實現(xiàn)簡易的分頁，即不顯示具體的頁數(shù)導(dǎo)航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實現(xiàn)真正的頁面導(dǎo)航還是很難的,下面看看另一種方式。
SELECT id
FROM (
SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
FROM news
JOIN (SELECT @cnt:= 0)T
WHERE id $last_id
ORDER BY id DESC
LIMIT $perpage * $buttons
)C
WHERE cnt = 0;
通過上面的語句可以為每一個分頁的按鈕計算出一個offset對應(yīng)的id。這種方法還有一個好處。假設(shè)，網(wǎng)站上正在發(fā)布一片新的文章，那么所有文章的位置都會往后移一位，所以如果用戶在發(fā)布文章時換頁，那么他會看見一篇文章兩次。如果固定了每個按鈕的offset Id，這個問題就迎刃而解了。Mark Callaghan發(fā)表過一篇類似的博客，利用了組合索引和兩個位置變量，但是基本思想是一致的。
如果表中的記錄很少被刪除、修改，還可以將記錄對應(yīng)的頁碼存儲到表中，并在該列上創(chuàng)建合適的索引。采用這種方式，當(dāng)新增一個記錄的時候，需要執(zhí)行下面的查詢重新生成對應(yīng)的頁號。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
當(dāng)然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護。
UPDATE pagination T
JOIN (
SELECT id, CEIL((p:= p + 1) / $perpage) page
FROM news
ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;
現(xiàn)在想獲取任意一頁的元素就很簡單了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;
還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用于數(shù)據(jù)集相對小，并且沒有可用的索引的情況下—比如處理搜索結(jié)果時。在一個普通的服務(wù)器上執(zhí)行下面的查詢，當(dāng)有2M條記錄時，要耗費2sec左右。這種方式比較簡單，創(chuàng)建一個用來存儲所有Id的臨時表即可(這也是最耗費性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;
ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;
接下來就可以向下面一樣執(zhí)行分頁查詢了。
SELECT *
FROM _tmp
WHERE OFFSET = $offset
ORDER BY OFFSET
LIMIT $perpage;
簡單來說，對于分頁的優(yōu)化就是。。。避免數(shù)據(jù)量大時掃描過多的記錄。
1.Trim（A1）
清除掉字符串前后空格。
MySQL有同名函數(shù)，Python有近似函數(shù)strip。
如果要清除所有空格，用substitute（A1，“ ”，“”）
2.Concatenate(A1，A2……)
合并單元格中的內(nèi)容
或"我""很""好" ＝ 我很好
MySQL有近似函數(shù)concat
3.Replace（指定字符串，哪個位置開始替換，替換幾個字符，替換成什么）
MySQL中有同名函數(shù)，Python中有同名函數(shù)
4.Left／Right（指定字符串，截取長度）
Mid(指定字符串，開始位置，截取長度)
MySQL中有同名函數(shù)
5.Len／Lenb
返回字符串的長度，在len中，中文計算為一個，在lenb中，中文計算為兩個。
MySQL中有同名函數(shù)，Python中有同名函數(shù)。
6.Find（要查找字符，指定字符串，第幾個字符）
查找某字符串出現(xiàn)的位置，可以指定為第幾次出現(xiàn)
MySQL中有近似函數(shù) find_in_set，Python中有同名函數(shù)
7.Text
將數(shù)值轉(zhuǎn)化為指定的文本格式
1.Lookup（查找的值，值所在的位置，返回相應(yīng)位置的值）
2.Vlookup(查找的值，哪里找，找哪個位置的值，是否精準(zhǔn)匹配)
3.Index（查找的區(qū)域，區(qū)域內(nèi)第幾行，區(qū)域內(nèi)第幾列）
和Match組合，功能強大
4.Match（查找指定的值，查找所在區(qū)域，查找方式的參數(shù)）
5.Row
返回單元格所在的行
Column
返回單元格所在的列
6.Offset（指定點，偏移多少行，偏移多少列，返回多少行，返回多少列）
if，and，or，is系列
MySQL中有同名函數(shù)，Python中有同名函數(shù)
1.Sum／Sumif／Sumifs、Count／Countif／Countifs、max、min
MySQL中有同名函數(shù)，Python中有同名函數(shù)
2.Sumproduct
統(tǒng)計總和相關(guān)
MySQL中有同名函數(shù)
3.Rank（A2，A2:A23）
排序，返回指定值在引用區(qū)域的排名，重復(fù)值同一排名。
SQL中有近似函數(shù)row_number() 。
4.Rand／Randbetween
常用隨機抽樣，前者返回0~1之間的隨機值，后者可以指定范圍。
MySQL中有同名函數(shù)。
5.Averagea
求平均值，也有Averageaif，Averageaifs
MySQL中有同名函數(shù)，python有近似函數(shù)mean。
6.Substotal（引用區(qū)域，參數(shù)）
匯總型函數(shù)，將平均值、計數(shù)、最大最小、相乘、標(biāo)準(zhǔn)差、求和、方差等參數(shù)化，換言之，只要會了這個函數(shù)，上面的都可以拋棄掉了。
7.Int／Round
取整函數(shù)，int向下取整，round按小數(shù)位取數(shù)。
round(3.1415,2) =3.14 ;
round(3.1415,1)=3.1
1.Year、Month、Day、Date（年，月，日）、Now、Today
2.Weekday(指定時間，參數(shù))
參數(shù)為1代表從星期日開始算作第一天
3.Datedif（開始日期，結(jié)束日期，參數(shù)）
日期計算函數(shù)，計算兩日期的差。參數(shù)決定返回的是年還是月等。
MySQL中有近似函數(shù) DateDiff。
4.同時顯示日期和時間：自定義格式為yyyy-m-d AAAA
快捷鍵，格式轉(zhuǎn)換（數(shù)值、文本、時間），數(shù)組，分列（SQL和Python中有類似的spilt ( )函數(shù)），合并單元格，數(shù)據(jù)透視表（聚合功能有一點類似SQL中的gorup by，python中則有更為強大的pandas.pivot_table( )），刪除重復(fù)項（功能類似SQL中的distinct ,python中的set），單元格條件格式，凍結(jié)首行首列（在Python和R中，read_csv函數(shù)，會有一個專門的參數(shù)header=true，來判斷是否讀取表頭作為columns的名字），自定義下拉菜單（數(shù)據(jù)有效性），查找公式錯誤，分組和分級顯示，分析工具庫，第三方應(yīng)用
快速合并多列數(shù)據(jù)：把列拉寬-選取數(shù)據(jù)-填充-兩端對齊
方差是有函數(shù)的,
例如:
SQL SELECT
2 sale_item,
3 VARIANCE(sale_money)
4 FROM
5 sale_report
6 GROUP BY
7 sale_item;
SALE VARIANCE(SALE_MONEY)
---- --------------------
A 0
B 11.9203372
C 77.5865272
注： Oracle 里面是 VARIANCE 函數(shù)， SQL Server 是 VAR 函數(shù)， MySQL 是 VAR_SAMP 函數(shù)。
但是要求你的數(shù)據(jù)是 一行一行的
而你的數(shù)據(jù)， 是一列一列的。
也就是你需要先作 列行轉(zhuǎn)換
我這里假設(shè)你的數(shù)據(jù)庫是 SQL Server
先
SELECT 序號, '6' AS 月份, [6月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '7' AS 月份, [7月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '8' AS 月份, [8月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '9' AS 月份, [9月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '10' AS 月份, [10月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '11' AS 月份, [11月] AS 數(shù)據(jù) FROM 表 ;
上面的查詢， 應(yīng)該就會形成一個這樣格式的結(jié)果：
序號 月份 數(shù)據(jù)
1 6 134.8
1 7 134.4
1 8 133.4
1 9 134.5
1 10 133.7
1 11 133.7
其他數(shù)據(jù)略...
這樣就可以使用那個函數(shù)來處理了
SELECT
序號,
VAR ( 數(shù)據(jù) ) AS 方差
FROM
(
SELECT 序號, '6' AS 月份, [6月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '7' AS 月份, [7月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '8' AS 月份, [8月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '9' AS 月份, [9月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '10' AS 月份, [10月] AS 數(shù)據(jù) FROM 表 UNION ALL
SELECT 序號, '11' AS 月份, [11月] AS 數(shù)據(jù) FROM 表
) subQuery
GROUP BY
序號
沒有rando函數(shù)。只有rand和randn
1．rand()
生成（0,1）區(qū)間上均勻分布的隨機變量?；菊Z法：
rand([M,N,P ...])
生成排列成M*N*P... 多維向量的隨機數(shù)。如果只寫M，則生成M*M矩陣；如果參數(shù)為[M,N]可以省略掉方括號。一些例子：
rand(5,1) %生成5個隨機數(shù)排列的列向量，一般用這種格式
rand(5) %生成5行5列的隨機數(shù)矩陣
rand([5,4]) %生成一個5行4列的隨機數(shù)矩陣
生成的隨機數(shù)大致的分布。
x=rand(100000,1);
hist(x,30);
由此可以看到生成的隨機數(shù)很符合均勻分布。(視頻教程會略提及hist()函數(shù)的作用)
2．randn()
生成服從標(biāo)準(zhǔn)正態(tài)分布（均值為0，方差為1）的隨機數(shù)?；菊Z法和rand()類似。
randn([M,N,P ...])
生成排列成M*N*P... 多維向量的隨機數(shù)。如果只寫M，則生成M*M矩陣；如果參數(shù)為[M,N]可以省略掉方括號。一些例子：
randn(5,1) %生成5個隨機數(shù)排列的列向量，一般用這種格式
randn(5) %生成5行5列的隨機數(shù)矩陣
randn([5,4]) %生成一個5行4列的隨機數(shù)矩陣
3、matlab中random函數(shù)——通用函數(shù)，求各分布的隨機數(shù)據(jù)，其用法：
y = random('norm',A1,A2,A3,m,n)
式中：A1，A2，A3為分布的參數(shù)，m，n用來指定隨機數(shù)的行和列，name的取值有相關(guān)的表格來參照。
例：產(chǎn)生一個3行4列均值為2、標(biāo)準(zhǔn)差為0.3的正態(tài)分布隨機數(shù)：
y =random('norm',2,0.3,3,4)
y =
2.1613 2.2587 1.8699 2.8308
2.5502 2.0956 2.1028 1.5950
1.3223 1.6077 3.0735 2.9105
重慶分公司
重慶分公司
