建站
咨詢
售后
建站咨詢
新聞中心
如何設(shè)計一個能夠高效查詢的千萬級MySQL數(shù)據(jù)庫？
我們先探討非高并發(fā)量的實(shí)現(xiàn)。
江北ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)建站的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18980820575（備注：SSL證書合作）期待與您的合作！
對于查詢頻次較高的字段，加上索引。
加索引注意事項(xiàng)：1.對那些字符內(nèi)容較長的最好不要加索引2.按照官方文檔，單表加的索引不要超過16個，索引的長度不要超過256個字節(jié)。隨意加索引，會給數(shù)據(jù)維護(hù)增加負(fù)擔(dān)
其實(shí)，可以引入分區(qū)。
分區(qū)注意事項(xiàng)：1.常見的分區(qū)類型有range，list，hash，key等。用的比較多的就是range分區(qū)。2.對于初始建立索引的時候，我們往往會忽視一個前提條件，導(dǎo)致添加失敗報錯。這里的前提是，如果表是有主鍵的，分區(qū)的鍵和主鍵不是同一個，那么分區(qū)的鍵也必須是主鍵。
引入分區(qū)后，數(shù)據(jù)寫入時，數(shù)據(jù)庫會自動判斷寫入哪個分區(qū)
對于并發(fā)量較高的，我們除了做上面的操作外，就要考慮分庫分表或者采用一主多從的方式。
未來我相信這類問題需要采用NewSQl這類數(shù)據(jù)庫來解決，如TiDb等，此時，我們將不必考慮數(shù)據(jù)分區(qū)的問題，而且可以做到數(shù)據(jù)水平無限擴(kuò)展，和熱點(diǎn)數(shù)據(jù)的動態(tài)分布。
Mysql某個表有近千萬數(shù)據(jù)，CRUD比較慢，如何優(yōu)化？
數(shù)據(jù)千萬級別之多，占用的存儲空間也比較大，可想而知它不會存儲在一塊連續(xù)的物理空間上，而是鏈?zhǔn)酱鎯υ诙鄠€碎片的物理空間上?？赡軐τ陂L字符串的比較，就用更多的時間查找與比較，這就導(dǎo)致用更多的時間。
可以做表拆分，減少單表字段數(shù)量，優(yōu)化表結(jié)構(gòu)。
在保證主鍵有效的情況下，檢查主鍵索引的字段順序，使得查詢語句中條件的字段順序和主鍵索引的字段順序保持一致。
主要兩種拆分 垂直拆分，水平拆分。
垂直分表
也就是“大表拆小表”，基于列字段進(jìn)行的。一般是表中的字段較多，將不常用的， 數(shù)據(jù)較大，長度較長（比如text類型字段）的拆分到“擴(kuò)展表“。 一般是針對 那種 幾百列的大表，也避免查詢時，數(shù)據(jù)量太大造成的“跨頁”問題。
垂直分庫針對的是一個系統(tǒng)中的不同業(yè)務(wù)進(jìn)行拆分，比如用戶User一個庫，商品Product一個庫，訂單Order一個庫。 切分后，要放在多個服務(wù)器上，而不是一個服務(wù)器上。為什么？ 我們想象一下，一個購物網(wǎng)站對外提供服務(wù)，會有用戶，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫上的，這會讓數(shù)據(jù)庫的單庫處理能力成為瓶頸。按垂直分庫后，如果還是放在一個數(shù)據(jù)庫服務(wù)器上， 隨著用戶量增大，這會讓單個數(shù)據(jù)庫的處理能力成為瓶頸，還有單個服務(wù)器的磁盤空間，內(nèi)存，tps等非常吃緊。 所以我們要拆分到多個服務(wù)器上，這樣上面的問題都解決了，以后也不會面對單機(jī)資源問題。
數(shù)據(jù)庫業(yè)務(wù)層面的拆分，和服務(wù)的“治理”，“降級”機(jī)制類似，也能對不同業(yè)務(wù)的數(shù)據(jù)分別的進(jìn)行管理，維護(hù)，監(jiān)控，擴(kuò)展等。 數(shù)據(jù)庫往往最容易成為應(yīng)用系統(tǒng)的瓶頸，而數(shù)據(jù)庫本身屬于“有狀態(tài)”的，相對于Web和應(yīng)用服務(wù)器來講，是比較難實(shí)現(xiàn)“橫向擴(kuò)展”的。 數(shù)據(jù)庫的連接資源比較寶貴且單機(jī)處理能力也有限，在高并發(fā)場景下，垂直分庫一定程度上能夠突破IO、連接數(shù)及單機(jī)硬件資源的瓶頸。
水平分表
針對數(shù)據(jù)量巨大的單張表（比如訂單表），按照某種規(guī)則（RANGE,HASH取模等），切分到多張表里面去。 但是這些表還是在同一個庫中，所以庫級別的數(shù)據(jù)庫操作還是有IO瓶頸。不建議采用。
水平分庫分表
將單張表的數(shù)據(jù)切分到多個服務(wù)器上去，每個服務(wù)器具有相應(yīng)的庫與表，只是表中數(shù)據(jù)集合不同。 水平分庫分表能夠有效的緩解單機(jī)和單庫的性能瓶頸和壓力，突破IO、連接數(shù)、硬件資源等的瓶頸。
水平分庫分表切分規(guī)則
1. RANGE
從0到10000一個表，10001到20000一個表；
2. HASH取模
一個商場系統(tǒng)，一般都是將用戶，訂單作為主表，然后將和它們相關(guān)的作為附表，這樣不會造成跨庫事務(wù)之類的問題。 取用戶id，然后hash取模，分配到不同的數(shù)據(jù)庫上。
3. 地理區(qū)域
比如按照華東，華南，華北這樣來區(qū)分業(yè)務(wù)，七牛云應(yīng)該就是如此。
4. 時間
按照時間切分，就是將6個月前，甚至一年前的數(shù)據(jù)切出去放到另外的一張表，因?yàn)殡S著時間流逝，這些表的數(shù)據(jù) 被查詢的概率變小，所以沒必要和“熱數(shù)據(jù)”放在一起，這個也是“冷熱數(shù)據(jù)分離”。
分庫分表后面臨的問題
事務(wù)支持
分庫分表后，就成了分布式事務(wù)了。如果依賴數(shù)據(jù)庫本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)，將付出高昂的性能代價； 如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)，又會造成編程方面的負(fù)擔(dān)。
跨庫join
只要是進(jìn)行切分，跨節(jié)點(diǎn)Join的問題是不可避免的。但是良好的設(shè)計和切分卻可以減少此類情況的發(fā)生。解決這一問題的普遍做法是分兩次查詢實(shí)現(xiàn)。在第一次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)的id,根據(jù)這些id發(fā)起第二次請求得到關(guān)聯(lián)數(shù)據(jù)。
跨節(jié)點(diǎn)的count,order by,group by以及聚合函數(shù)問題
這些是一類問題，因?yàn)樗鼈兌夹枰谌繑?shù)據(jù)集合進(jìn)行計算。多數(shù)的代理都不會自動處理合并工作。解決方案：與解決跨節(jié)點(diǎn)join問題的類似，分別在各個節(jié)點(diǎn)上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并。和join不同的是每個結(jié)點(diǎn)的查詢可以并行執(zhí)行，因此很多時候它的速度要比單一大表快很多。但如果結(jié)果集很大，對應(yīng)用程序內(nèi)存的消耗是一個問題。
數(shù)據(jù)遷移，容量規(guī)劃，擴(kuò)容等問題
來自淘寶綜合業(yè)務(wù)平臺團(tuán)隊，它利用對2的倍數(shù)取余具有向前兼容的特性（如對4取余得1的數(shù)對2取余也是1）來分配數(shù)據(jù)，避免了行級別的數(shù)據(jù)遷移，但是依然需要進(jìn)行表級別的遷移，同時對擴(kuò)容規(guī)模和分表數(shù)量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點(diǎn)，這也從一個側(cè)面反映出了Sharding擴(kuò)容的難度。
ID問題
一旦數(shù)據(jù)庫被切分到多個物理結(jié)點(diǎn)上，我們將不能再依賴數(shù)據(jù)庫自身的主鍵生成機(jī)制。一方面，某個分區(qū)數(shù)據(jù)庫自生成的ID無法保證在全局上是唯一的；另一方面，應(yīng)用程序在插入數(shù)據(jù)之前需要先獲得ID,以便進(jìn)行SQL路由.
一些常見的主鍵生成策略
UUID
使用UUID作主鍵是最簡單的方案，但是缺點(diǎn)也是非常明顯的。由于UUID非常的長，除占用大量存儲空間外，最主要的問題是在索引上，在建立索引和基于索引進(jìn)行查詢時都存在性能問題。
Twitter的分布式自增ID算法Snowflake
在分布式系統(tǒng)中，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求，實(shí)現(xiàn)也還是很簡單的，除去配置信息，核心代碼就是毫秒級時間41位 機(jī)器ID 10位 毫秒內(nèi)序列12位。
跨分片的排序分頁
一般來講，分頁時需要按照指定字段進(jìn)行排序。當(dāng)排序字段就是分片字段的時候，我們通過分片規(guī)則可以比較容易定位到指定的分片，而當(dāng)排序字段非分片字段的時候，情況就會變得比較復(fù)雜了。為了最終結(jié)果的準(zhǔn)確性，我們需要在不同的分片節(jié)點(diǎn)中將數(shù)據(jù)進(jìn)行排序并返回，并將不同分片返回的結(jié)果集進(jìn)行匯總和再次排序，最后再返回給用戶。
如何優(yōu)化Mysql千萬級快速分頁
很多應(yīng)用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導(dǎo)航欄。然而，如何通過MySQL更好的實(shí)現(xiàn)分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解數(shù)據(jù)庫的底層或多或少有助于優(yōu)化分頁查詢。
我們先從一個常用但性能很差的查詢來看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
這個查詢耗時0.00sec。So，這個查詢有什么問題呢？實(shí)際上，這個查詢語句和參數(shù)都沒有問題，因?yàn)樗玫搅讼旅姹淼闹麈I，而且只讀取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意識到了這一點(diǎn)，但Facebook并沒有為了每秒可以處理更多的請求而去優(yōu)化數(shù)據(jù)庫，而是將重心放在將請求響應(yīng)時間的方差變小。
對于分頁請求，還有一個信息也很重要，就是總共的記錄數(shù)。我們可以通過下面的查詢很容易的獲取總的記錄數(shù)。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB為存儲引擎時需要耗費(fèi)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，是上一個的兩倍。事實(shí)證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。
下面來看看到底如何優(yōu)化。文章分為兩部分，第一部分是如何獲取記錄的總數(shù)目，第二部分是獲取真正的記錄。
高效的計算行數(shù)
如果采用的引擎是MyISAM，可以直接執(zhí)行COUNT(*)去獲取行數(shù)即可。相似的，在堆表中也會將行數(shù)存儲到表的元信息中。但如果引擎是InnoDB情況就會復(fù)雜一些，因?yàn)镮nnoDB不保存表的具體行數(shù)。
我們可以將行數(shù)緩存起來，然后可以通過一個守護(hù)進(jìn)程定期更新或者用戶的某些操作導(dǎo)致緩存失效時，執(zhí)行下面的語句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
獲取記錄
下面進(jìn)入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經(jīng)說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創(chuàng)建一個新的表“news”，按照時事性排序(最新發(fā)布的在最前面)，實(shí)現(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
上面的查詢方式適合實(shí)現(xiàn)簡易的分頁，即不顯示具體的頁數(shù)導(dǎo)航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實(shí)現(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)然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護(hù)。
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條記錄時，要耗費(fèi)2sec左右。這種方式比較簡單，創(chuàng)建一個用來存儲所有Id的臨時表即可(這也是最耗費(fèi)性能的地方)。
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ù)量大時掃描過多的記錄。
文章題目：mysql怎么對千萬級別 mysql千萬級數(shù)據(jù)查詢
標(biāo)題網(wǎng)址：http://fisionsoft.com.cn/article/ddidsig.html
