千萬級資料庫優化

Ⅰ Mysql 對於千萬級的大表要怎麼優化

對大數據的資料庫管理優化的總結：

常用的優化sql----突出快字，使完成操作的時間最短

1、用索引提高效率：

2、選擇有效率的表名順序，及數據結構及欄位；
3、使用DECODE函數可以避免重復掃描相同記錄或重復連接相同的表；
4、刪除重復記；
5、過內部函數提高SQL效率；
......

讀寫分離-----操作不在一個表裡完成

1、主資料庫A，進行事務性增、改、刪操作（INSERT、UPDATE、DELETE）；
2、從資料庫B，進行SELECT查詢操作；

3、A復制到B，使數據保持一致性；

垂直劃分 ------數據不存儲在一個伺服器里

按照功能劃分，把數據分別放到不同的資料庫和伺服器。如博客功能的放到伺服器A，儲存文件放到伺服器B；

水平劃分------相同數據結構的數據不放在一張表裡

把一個表的數據根據一定的規則劃分到不同的資料庫，兩個資料庫的表結構一樣。

數據歸檔處理-----時間優先原則存儲讀取

將資料庫中不經常使用的數據遷移至近線設備，將長期不使用的數據遷移至文件形式歸檔。這樣，隨著應用的需要，數據會在在線、近線和文件文檔之間移動，如當應用需要訪問很久以前的某些數據，它們的物理位置在近線設備，則會自動移動到在線設備。對用戶的應用而言，這些都是透明的，就像所有數據都存放在在線設備一樣，不會對資料庫應用產生任何影響。

Ⅱ 千萬數量級的資料庫，全文索引很慢，怎麼進行優化

千萬數量級的資料庫，全文索引很慢，怎麼進行優化
條件少掃描的時間少,但數據集可能大,條件多反之,不能一概而論。 另外,不要用SELECT * 這樣的方式,性能低下。

Ⅲ 千萬級的數據，統計分析，非常慢，怎麼優化

首先這個日誌不適合放在資料庫，其次mysql就是慢，這么大的數據量，弄個從庫專門用來統計，慢慢統計吧
1 sql優化
2 分表（垂直、水平）
3 內存存放
4 提高磁碟IO（如使用SSD）
5 負載均衡
6 CDN
7 +CPU/內存

Ⅳ 千萬級別以上的資料庫如何去優化

第一優化你的sql和索引；
第二加緩存，memcached,redis；
第三以上都做了後，還是慢，就做主從復制或主主復制，讀寫分離，可以在應用層做，效率高，也可以用三方工具，第三方工具推薦360的atlas,其它的要麼效率不高，要麼沒人維護；
第四如果以上都做了還是慢，不要想著去做切分，mysql自帶分區表，先試試這個，對你的應用是透明的，無需更改代碼,但是sql語句是需要針對分區表做優化的，sql條件中要帶上分區條件的列，從而使查詢定位到少量的分區上，否則就會掃描全部分區，另外分區表還有一些坑，在這里就不多說了；
第五如果以上都做了，那就先做垂直拆分，其實就是根據你模塊的耦合度，將一個大的系統分為多個小的系統，也就是分布式系統；
第六才是水平切分，針對數據量大的表，這一步最麻煩，最能考驗技術水平，要選擇一個合理的sharding key,為了有好的查詢效率，表結構也要改動，做一定的冗餘，應用也要改，sql中盡量帶sharding key，將數據定位到限定的表上去查，而不是掃描全部的表；
mysql資料庫一般都是按照這個步驟去演化的，成本也是由低到高。

Ⅳ php+mysql 如何優化千萬級數據模糊查詢加快

關於mysql處理百萬級以上的數據時如何提高其查詢速度的方法

最近一段時間由於工作需要，開始關注針對Mysql資料庫的select查詢語句的相關優化方法。

由於在參與的實際項目中發現當mysql表的數據量達到百萬級時，普通SQL查詢效率呈直線下降，而且如果where中的查詢條件較多時，其查詢速度簡直無法容忍。曾經測試對一個包含400多萬條記錄（有索引）的表執行一條條件查詢，其查詢時間竟然高達40幾秒，相信這么高的查詢延時，任何用戶都會抓狂。因此如何提高sql語句查詢效率，顯得十分重要。以下是網上流傳比較廣泛的30種SQL查詢語句優化方法：
1、應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

2、對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

3、應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：
select id from t where num is null
可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：
select id from t where num=0

4、盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：
select id from t where num=10 or num=20
可以這樣查詢：
select id from t where num=10
union all
select id from t where num=20

5、下面的查詢也將導致全表掃描：(不能前置百分號)
select id from t where name like 『%c%』
若要提高效率，可以考慮全文檢索。

6、in 和 not in 也要慎用，否則會導致全表掃描，如：
select id from t where num in(1,2,3)
對於連續的數值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

7、如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：
select id from t where num=@num
可以改為強制查詢使用索引：
select id from t with(index(索引名)) where num=@num

8、應盡量避免在 where 子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：
select id from t where num/2=100
應改為:
select id from t where num=100*2

9、應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：
select id from t where substring(name,1,3)=』abc』–name以abc開頭的id
select id from t where datediff(day,createdate,』2005-11-30′)=0–』2005-11-30′生成的id
應改為:
select id from t where name like 『abc%』
select id from t where createdate>=』2005-11-30′ and createdate<』2005-12-1′

10、不要在 where 子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

11、在使用索引欄位作為條件時，如果該索引是復合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使 用，並且應盡可能的讓欄位順序與索引順序相一致。

12、不要寫一些沒有意義的查詢，如需要生成一個空表結構：
select col1,col2 into #t from t where 1=0
這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：
create table #t(…)

13、很多時候用 exists 代替 in 是一個好的選擇：
select num from a where num in(select num from b)
用下面的語句替換：
select num from a where exists(select 1 from b where num=a.num)

14、並不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重復時，SQL查詢可能不會去利用索引，如一表中有欄位 sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

15、索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有 必要。

16.應盡可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那麼需要考慮是否應將該索引建為 clustered 索引。

17、盡量使用數字型欄位，若只含數值信息的欄位盡量不要設計為字元型，這會降低查詢和連接的性能，並會增加存儲開銷。這是因為引擎在處理查詢和連接時會 逐個比較字元串中每一個字元，而對於數字型而言只需要比較一次就夠了。

18、盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。

19、任何地方都不要使用 select * from t ，用具體的欄位列表代替「*」，不要返回用不到的任何欄位。

20、盡量使用表變數來代替臨時表。如果表變數包含大量數據，請注意索引非常有限（只有主鍵索引）。

21、避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

22、臨時表並不是不可使用，適當地使用它們可以使某些常式更有效，例如，當需要重復引用大型表或常用表中的某個數據集時。但是，對於一次性事件，最好使 用導出表。

23、在新建臨時表時，如果一次性插入數據量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然後insert。

24、如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

25、盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那麼就應該考慮改寫。

26、使用基於游標的方法或臨時表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。

27、與臨時表一樣，游標並不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括「合計」的常式通常要比使用游標執行的速度快。如果開發時 間允許，基於游標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。

28、在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句後向客戶端發送 DONE_IN_PROC 消息。

29、盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

30、盡量避免大事務操作，提高系統並發能力。

Ⅵ 怎麼樣提高千萬級SQL資料庫查詢速度

1.對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

2.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：

select id from t where num=0

3.應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

4.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5.in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3)

對於連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查詢也將導致全表掃描：

select id from t where name like '%abc%'

若要提高效率，可以考慮全文檢索。

Ⅶ mysql千萬級數據update怎麼優化

提問：何設計或優化千萬級別表外其信息覺題點范簡單說該何做於存儲設計必須考慮業務特點收集信息：
1.數據容量：1-3內概少條數據每條數據概少位元組；
2.數據項：否欄位些欄位值否經更新；
3.數據查詢SQL條件：哪些數據項列名稱經現WHERE、GROUP BY、ORDER BY句等；
4.數據更新類SQL條件：少列經現UPDATE或DELETE WHERE句；
5.SQL量統計比：SELECT：UPDATE+DELETE：INSERT=少
6.預計表及相關聯SQL每總執行量何數量級
7.表數據：更新主業務 查詢主業務
8.打算採用資料庫物理伺服器及資料庫伺服器架構
9.並發何
10.存儲引擎選擇InnoDBMyISAM

致明白10問題至於何設計類表應該都清楚

至於優化若指創建表能變表結構建議InnoDB引擎利用點內存減輕磁碟IO負載IO往往資料庫伺服器瓶頸

另外優化索引結構解決性能問題建議優先考慮修改類SQL語句使更快些已靠索引組織結構式前提 索引已經創建非若讀主考慮打query_cache 及調整些參數值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_siz
更信息參見：
MySQL資料庫伺服器端核參數詳解推薦配置
紙談兵說我思路及我解決拋磚引玉
我近解決問題
我現公司三張表5億數據每張表每增量100w
每張表概10columns左右
面我做測試比
1.首先看engine,數據量情況沒做區情況
mysiam比innodb讀情況效率要高13％左右
2.做partition讀mysql官文檔其實於partition專門myisam做優化於innodb所數據存ibdata面所即使看schema變其實沒本質變化
區於同physical disk面情況提升概1％
區同physical disk我三同disks提升概3％其實所謂吞吐量由素決定比explain parition候看record區每區都其實本質沒解決讀問題提升寫效率
另外問題於區張表三column都經用於做查詢條件其實件悲慘事情沒辦所sql做針性區mysql官文檔說間做區且用間查詢恭喜
3.表主要用讀寫其實問題充應該問寫入候同並發查詢我問題比較簡單mongodb shredding支持能crushmysql所通情況9am－9pm寫入情況候我做 viewview基於近插入或者經查詢通做view離讀取說寫table讀進行邏輯判斷前view操作
4做些archive table比先些表做已統計析通已析＋增量解決
5用mysiam問題要注意.configure候加max index length參數候record數於制定度候indexdisable

Ⅷ Mysql 千萬級數據量插入和查詢應該怎麼優化

mysql
千萬級數據量插入和查詢應該怎麼優化
大資料庫優化sql,索引,橫向縱向切割!多庫,主從,讀寫分離

Ⅸ 如何優化Mysql千萬級快速分頁，limit優化快

很多應用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導航欄。然而，如何通過MySQL更好的實現分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解資料庫的底層或多或少有助於優化分頁查詢。

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

SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15

這個查詢耗時0.00sec。So，這個查詢有什麼問題呢？實際上，這個查詢語句和參數都沒有問題，因為它用到了下面表的主鍵，而且只讀取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的執行計劃可以發現該SQL檢索了100015行，但最後只需要15行。大的分頁偏移量會增加使用的數據，MySQL會將大量最終不會使用的數據載入到內存中。就算我們假設大部分網站的用戶只訪問前幾頁數據，但少量的大的分頁偏移量的請求也會對整個系統造成危害。Facebook意識到了這一點，但Facebook並沒有為了每秒可以處理更多的請求而去優化資料庫，而是將重心放在將請求響應時間的方差變小。

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

SELECT COUNT(*)
FROM city;

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

SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;

這個語句耗時20.02sec，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。
下面來看看到底如何優化。文章分為兩部分，第一部分是如何獲取記錄的總數目，第二部分是獲取真正的記錄。

高效的計算行數

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

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

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

獲取記錄

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

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

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

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

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

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

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

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對應的id。這種方法還有一個好處。假設，網站上正在發布一片新的文章，那麼所有文章的位置都會往後移一位，所以如果用戶在發布文章時換頁，那麼他會看見一篇文章兩次。如果固定了每個按鈕的offset Id，這個問題就迎刃而解了。Mark Callaghan發表過一篇類似的博客，利用了組合索引和兩個位置變數，但是基本思想是一致的。

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

SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

當然，也可以新增一個專用於分頁的表，可以用個後台程序來維護。

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;

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

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

還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用於數據集相對小，並且沒有可用的索引的情況下—比如處理搜索結果時。在一個普通的伺服器上執行下面的查詢，當有2M條記錄時，要耗費2sec左右。這種方式比較簡單，創建一個用來存儲所有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;

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

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

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