mysql資料庫優化教程_資料庫如何優化

A. 資料庫如何優化

body{
line-height:200%;
}
如何優化Mysql資料庫
當MySQL資料庫邂逅優化，它有好幾個意思，今天我們所指的是性能優化。
我們究竟該如何對MySQL資料庫進行優化呢？下面我就從MySQL對硬體的選擇、Mysql的安裝、my.cnf的優化、MySQL如何進行架構設計及數據切分等方面來說明這個問題。
1.伺服器物理硬體的優化
1)磁碟(I/O)，MySQL每一秒鍾都在進行大量、復雜的查詢操作，對磁碟的讀寫量可想而知，所以推薦使用RAID1+0磁碟陣列，如果資金允許，可以選擇固態硬碟做RAID1+0；
2)cpu對Mysql的影響也是不容忽視的，建議選擇運算能力強悍的CPU。
2.MySQL應該採用編譯安裝的方式
MySQL資料庫的線上環境安裝，我建議採取編譯安裝，這樣性能會較大的提升。
3.MySQL配置文件的優化
1)skip
-name
-resolve，禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間；
2)back_log
=
384，back_log指出在MySQL暫時停止響應新請求之前，短時間內的多少個請求可以被存在堆棧中，對於Linux系統而言，推薦設置小於512的整數。
3)如果key_reads太大，則應該把my.cnf中key_buffer_size變大，保持key_reads/key_read_requests至少在1/100以上，越小越好。
4.MySQL上線後根據status狀態進行適當優化
1)打開慢查詢日誌可能會對系統性能有一點點影響，如果你的MySQL是主-從結構，可以考慮打開其中一台從伺服器的慢查詢日誌，這樣既可以監控慢查詢，對系統性能影響也會很小。
2)MySQL伺服器過去的最大連接數是245，沒有達到伺服器連接數的上限256，應該不會出現1040錯誤。比較理想的設置是：Max_used_connections/max_connections
*
100%
=85%
5.MySQL資料庫的可擴展架構方案
1)MySQL
cluster，其特點為可用性非常高，性能非常好，但它的維護非常復雜，存在部分Bug;
2)DRBD磁碟網路鏡像方案，其特點為軟體功能強大，數據可在底層塊設備級別跨物理主機鏡像，且可根據性能和可靠性要求配置不同級別的同步。

B. mysql資料庫有100萬+數據，查詢起來很慢了，如何優化

你好，你可以根據條件去添加索引，例如：

一、
所有mysql索引列類型都可以被索引，對來相關類使用索引可以提高select查詢性能，根據mysql索引數，可以是最大索引與最小索引，每種存儲引擎對每個表的至少支持16的索引。總索引長度為256位元組。
mysim和innodb存儲引擎的表默認創建索引都是btree索引，目前mysql還不支持函數索引，但支持前緣索引，對欄位前N個字元創建索引
二、mysql創建索引語法
Create[unioun|fulltext|spatial]indexindexname[usingindextype]ontablename(tablenamecol)
index_col_name:
col_name[(length)][asc|desc]
如果你創建索引時搞錯了，需要修改mysql索引我們可以用alert來修改索引，語法與createindex創建索引差不多，我們就不說了，可以查看相關手冊。
下面我們來看一個關於mysql創建索引實例教程。
mysql>createindexcitynameoncity(city(2));
QueryOk,600rowsaffected(0.26sec)
Records:600Duplicates:0Warings0:
我們現在來以city為條件進行查詢，如下面。
->explainselect*fromcitywherecity='www.111cn.net'G
id:1
......
possible_keys:cityname
key:cityname
好了，現在我們來看看mysql刪除索引等實例
Dropindexnameontablename
實例，我現在要刪除剛才創建city索引
>dropindexcitynameoncity;
Queryok,.....

不過通常對百萬級數據的查詢或者其他操作，都改換其他的大型的資料庫了，希望能幫到你，望採納。

C. MySQL資料庫性能優化之分區分表分庫

分表是分散資料庫壓力的好方法。

分表，最直白的意思，就是將一個表結構分為多個表，然後，可以再同一個庫里，也可以放到不同的庫。

當然，首先要知道什麼情況下，才需要分表。個人覺得單表記錄條數達到百萬到千萬級別時就要使用分表了。

分表的分類

**1、縱向分表**

將本來可以在同一個表的內容，人為劃分為多個表。（所謂的本來，是指按照關系型資料庫的第三範式要求，是應該在同一個表的。）

分表理由：根據數據的活躍度進行分離，（因為不同活躍的數據，處理方式是不同的）

案例：

對於一個博客系統，文章標題，作者，分類，創建時間等，是變化頻率慢，查詢次數多，而且最好有很好的實時性的數據，我們把它叫做冷數據。而博客的瀏覽量，回復數等，類似的統計信息，或者別的變化頻率比較高的數據，我們把它叫做活躍數據。所以，在進行資料庫結構設計的時候，就應該考慮分表，首先是縱向分表的處理。

這樣縱向分表後：

首先存儲引擎的使用不同，冷數據使用MyIsam 可以有更好的查詢數據。活躍數據，可以使用Innodb ,可以有更好的更新速度。

其次，對冷數據進行更多的從庫配置，因為更多的操作時查詢，這樣來加快查詢速度。對熱數據，可以相對有更多的主庫的橫向分表處理。

其實，對於一些特殊的活躍數據，也可以考慮使用memcache ,redis之類的緩存，等累計到一定量再去更新資料庫。或者mongodb 一類的nosql 資料庫，這里只是舉例，就先不說這個。

**2、橫向分表**

字面意思，就可以看出來，是把大的表結構，橫向切割為同樣結構的不同表，如，用戶信息表，user_1,user_2等。表結構是完全一樣，但是，根據某些特定的規則來劃分的表，如根據用戶ID來取模劃分。

分表理由：根據數據量的規模來劃分，保證單表的容量不會太大，從而來保證單表的查詢等處理能力。

案例：同上面的例子，博客系統。當博客的量達到很大時候，就應該採取橫向分割來降低每個單表的壓力，來提升性能。例如博客的冷數據表，假如分為100個表，當同時有100萬個用戶在瀏覽時，如果是單表的話，會進行100萬次請求，而現在分表後，就可能是每個表進行1萬個數據的請求（因為，不可能絕對的平均，只是假設），這樣壓力就降低了很多很多。

延伸：為什麼要分表和分區？

日常開發中我們經常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過於龐大，導致資料庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯合查詢的情況，性能會更加糟糕。分表和表分區的目的就是減少資料庫的負擔，提高資料庫的效率，通常點來講就是提高表的增刪改查效率。

什麼是分表？

分表是將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個文件，MYD數據文件，.MYI索引文件，.frm表結構文件。這些子表可以分布在同一塊磁碟上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名，然後去操作它。

什麼是分區？

分區和分表相似，都是按照規則分解表。不同在於分表將大表分解為若干個獨立的實體表，而分區是將數據分段劃分在多個位置存放，可以是同一塊磁碟也可以在不同的機器。分區後，表面上還是一張表，但數據散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的數據。

**MySQL分表和分區有什麼聯系呢？**

1、都能提高mysql的性高，在高並發狀態下都有一個良好的表現。

2、分表和分區不矛盾，可以相互配合的，對於那些大訪問量，並且表數據比較多的表，我們可以採取分表和分區結合的方式（如果merge這種分表方式，不能和分區配合的話，可以用其他的分表試），訪問量不大，但是表數據很多的表，我們可以採取分區的方式等。

3、分表技術是比較麻煩的，需要手動去創建子表，app服務端讀寫時候需要計運算元表名。採用merge好一些，但也要創建子表和配置子表間的union關系。

4、表分區相對於分表，操作方便，不需要創建子表。

我們知道對於大型的互聯網應用，資料庫單表的數據量可能達到千萬甚至上億級別，同時面臨這高並發的壓力。Master-Slave結構只能對資料庫的讀能力進行擴展，寫操作還是集中在Master中，Master並不能無限制的掛接Slave庫，如果需要對資料庫的吞吐能力進行進一步的擴展，可以考慮採用分庫分表的策略。

**1、分表**

在分表之前，首先要選中合適的分表策略（以哪個字典為分表欄位，需要將數據分為多少張表），使數據能夠均衡的分布在多張表中，並且不影響正常的查詢。在企業級應用中，往往使用org_id(組織主鍵)做為分表欄位，在互聯網應用中往往是userid。在確定分表策略後，當數據進行存儲及查詢時，需要確定到哪張表裡去查找數據，

數據存放的數據表 = 分表欄位的內容 % 分表數量

**2、分庫**

分表能夠解決單表數據量過大帶來的查詢效率下降的問題，但是不能給資料庫的並發訪問帶來質的提升，面對高並發的寫訪問，當Master無法承擔高並發的寫入請求時，不管如何擴展Slave伺服器，都沒有意義了。我們通過對資料庫進行拆分，來提高資料庫的寫入能力，即所謂的分庫。分庫採用對關鍵字取模的方式，對資料庫進行路由。

數據存放的資料庫=分庫欄位的內容%資料庫的數量

**3、即分表又分庫**

資料庫分表可以解決單表海量數據的查詢性能問題，分庫可以解決單台資料庫的並發訪問壓力問題。

當資料庫同時面臨海量數據存儲和高並發訪問的時候，需要同時採取分表和分庫策略。一般分表分庫策略如下：

中間變數 = 關鍵字%（資料庫數量*單庫數據表數量）

庫 = 取整（中間變數/單庫數據表數量）

表 = （中間變數%單庫數據表數量）

實例：

1、分庫分表

很明顯，一個主表（也就是很重要的表，例如用戶表）無限制的增長勢必嚴重影響性能，分庫與分表是一個很不錯的解決途徑，也就是性能優化途徑，現在的案例是我們有一個1000多萬條記錄的用戶表members,查詢起來非常之慢，同事的做法是將其散列到100個表中，分別從members0到members99，然後根據mid分發記錄到這些表中，牛逼的代碼大概是這樣子：

復制代碼代碼如下:

<?php

for($i=0;$i< 100; $i++ ){

//echo "CREATE TABLE db2.members{$i} LIKE db1.members
";

echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}
";

}

?>

2、不停機修改mysql表結構

同樣還是members表，前期設計的表結構不盡合理，隨著資料庫不斷運行，其冗餘數據也是增長巨大，同事使用了下面的方法來處理：

先創建一個臨時表：

/*創建臨時表*/

CREATE TABLE members_tmp LIKE members

然後修改members_tmp的表結構為新結構，接著使用上面那個for循環來導出數據，因為1000萬的數據一次性導出是不對的，mid是主鍵，一個區間一個區間的導，基本是一次導出5萬條吧，這里略去了

接著重命名將新表替換上去：

/*這是個頗為經典的語句哈*/

RENAME TABLE members TO members_bak,members_tmp TO members;

就是這樣，基本可以做到無損失，無需停機更新表結構，但實際上RENAME期間表是被鎖死的，所以選擇在線少的時候操作是一個技巧。經過這個操作，使得原先8G多的表，一下子變成了2G多。

D. mysql中怎樣對大批量級的數據查詢進行優化

在我們使用MySQL資料庫時，比較常用也是查詢，包括基本查詢，關聯查詢，條件查詢等等，對於同一個操作，SQL語句的實現有很多種寫法，但是不同的寫法查詢的性能可能會有很大的差異。這里主要介紹下select查詢優化的要點。

1. 使用慢查詢日誌去發現慢查詢。
2. 使用執行計劃去判斷查詢是否正常運行。
3. 總是去測試你的查詢看看是否他們運行在最佳狀態下 –久而久之性能總會變化。
4. 避免在整個表上使用count(*),它可能鎖住整張表。
5. 使查詢保持一致以便後續相似的查詢可以使用查詢緩存。
6. 在適當的情形下使用GROUP BY而不是DISTINCT。
7. 在WHERE, GROUP BY和ORDER BY子句中使用有索引的列。
8. 保持索引簡單,不在多個索引中包含同一個列。
9. 有時候MySQL會使用錯誤的索引,對於這種情況使用USE INDEX。
10. 檢查使用SQL_MODE=STRICT的問題。
11.對於記錄數小於5的索引欄位，在UNION的時候使用LIMIT不是是用OR.
12. 為了避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去實現。
3. 不要使用 MAX,使用索引欄位和ORDER BY子句。
14. 避免使用ORDER BY RAND().
15. LIMIT M，N實際上可以減緩查詢在某些情況下，有節制地使用。
16. 在WHERE子句中使用UNION代替子查詢。
17. 對於UPDATES（更新），使用 SHARE MODE（共享模式），以防止獨占鎖。
18. 在重新啟動的MySQL，記得來溫暖你的資料庫，以確保您的數據在內存和查詢速度快。
19. 使用DROP TABLE，CREATE TABLE DELETE FROM從表中刪除所有數據。
20. 最小化的數據在查詢你需要的數據，使用*消耗大量的時間。
21. 考慮持久連接，而不是多個連接，以減少開銷。
22. 基準查詢，包括使用伺服器上的負載，有時一個簡單的查詢可以影響其他查詢。
23. 當負載增加您的伺服器上，使用SHOW PROCESSLIST查看慢的和有問題的查詢。
24. 在開發環境中產生的鏡像數據中測試的所有可疑的查詢。
來源：PHP程序員雷雪松的博客

E. mysql資料庫怎麼優化，有幾方面的優化

在鍵租謹開始演示之前，我們先介紹下兩個概念。

概念一，數據的可選擇性基數，也就是常說的cardinality值。

查詢優化器在生成各種執行計劃之前，得先從統計信息中取得相關數據，這樣才能估算每步操作所涉及到的記錄數，而這個相關數據就是cardinality。簡單來說，就是每個值在每個欄位中的唯一值分布狀態。

比如表t1有100行記錄，其中一列為f1。f1中唯一值的個數可以是100個，也可以是1個，當然也可以是1到100之間的任何一個數字。這里唯一值越的多少，就是這個列的可選擇基數。

那看到這里我們就明白了，為什麼要在基數高的欄位上建立索引，而基數低的的欄位建立索引反而沒有全表掃描來的快。當然這個只是一方面，至於更深入的探討就不在我這篇探討的范圍了。

概念二，關於HINT的使用。

這里我來說下HINT是什麼，在什麼時候用。

HINT簡單來說就是在某些特定的場景下人工協助MySQL優化器的工作，使她生成最優的執行計劃。一般來說，優化器的執行計劃都是最優化的，不過在某些特定場景下，執行計劃可能不是最優化。

比如：表t1經過大稿基量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經很不準確了，這時候剛好執行了一條SQL，那麼有可能這條SQL的執行計劃就不是最優的。為什麼說有可能呢？

來看下具體演示

譬如，以下兩條SQL，

A：
select * from t1 where f1 = 20;

B：
select * from t1 where f1 = 30;

如果f1的值剛好頻繁更新的值為30，並且沒有達到MySQL自動更新cardinality值的臨界值或者說用戶設置了手動更新又或者用戶減少了sample page等等，那麼對這兩條語句來說，可能不準確的就是B了。

這里順帶說下，MySQL提供了自動更新和手動更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊。

那回到正題上，MySQL 8.0 帶來了幾個HINT，我今天就舉個index_merge的例子。

示例表結構：

mysql> desc t1;+------------+--------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || rank1 | int(11) | YES | MUL | NULL | || rank2 | int(11) | YES | MUL | NULL | || log_time | datetime | YES | MUL | NULL | || prefix_uid | varchar(100) | YES | | NULL | || desc1 | text | YES | | NULL | || rank3 | int(11) | YES | MUL | NULL | 型啟 |+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

表記錄數：

mysql> select count(*) from t1;+----------+| count(*) |+----------+| 32768 |+----------+1 row in set (0.01 sec)

這里我們兩條經典的SQL：

SQL C：
select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D：
select * from t1 where rank1 =100 and rank2 =100 and rank3 =100;

表t1實際上在rank1,rank2,rank3三列上分別有一個二級索引。

那我們來看SQL C的查詢計劃。

顯然，沒有用到任何索引，掃描的行數為32034，cost為3243.65。

mysql> explain format=json select * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3243.65" }, "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "rows_examined_per_scan": 32034, "rows_proced_per_join": 115, "filtered": "0.36", "cost_info": { "read_cost": "3232.07", "eval_cost": "11.58", "prefix_cost": "3243.65", "data_read_per_join": "49K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)

我們加上hint給相同的查詢，再次看看查詢計劃。

這個時候用到了index_merge,union了三個列。掃描的行數為1103，cost為441.09，明顯比之前的快了好幾倍。

mysql> explain format=json select /*+ index_merge(t1) */ * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "441.09" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "union(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1103, "rows_proced_per_join": 1103, "filtered": "100.00", "cost_info": { "read_cost": "330.79", "eval_cost": "110.30", "prefix_cost": "441.09", "data_read_per_join": "473K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)

我們再看下SQL D的計劃：

不加HINT，
mysql> explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "534.34" }, "table": { "table_name": "t1", "access_type": "ref", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "idx_rank1", "used_key_parts": [ "rank1" ], "key_length": "5", "ref": [ "const" ], "rows_examined_per_scan": 555, "rows_proced_per_join": 0, "filtered": "0.07", "cost_info": { "read_cost": "478.84", "eval_cost": "0.04", "prefix_cost": "534.34", "data_read_per_join": "176" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" } }}1 row in set, 1 warning (0.00 sec)

加了HINT，
mysql> explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.23" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "intersect(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1, "rows_proced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "5.13", "eval_cost": "0.10", "prefix_cost": "5.23", "data_read_per_join": "440" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" } }}1 row in set, 1 warning (0.00 sec)

對比下以上兩個，加了HINT的比不加HINT的cost小了100倍。

總結下，就是說表的cardinality值影響這張的查詢計劃，如果這個值沒有正常更新的話，就需要手工加HINT了。相信MySQL未來的版本會帶來更多的HINT。

mysql資料庫優化教程

與mysql資料庫優化教程相關的內容