mysql資料庫表設計優化（mysql大數據表優化）

• 1、8，mysql資料庫，怎麼優化
• 2、MySQL資料庫性能優化之分區分表分庫
• 3、MySQL 數據表優化設計（三）：CHAR 和 VARCHAR 怎麼選？
• 4、北大青鳥設計培訓：mysql資料庫的優化方法？
• 5、北大青鳥設計培訓：Mysql資料庫的設計和優化？
• 6、昆明java培訓學校告訴你Mysql資料庫的設計和優化？

優化Mysql資料庫的8個方法

1、創建索引

對於查詢佔主要的應用來說，索引顯得尤為重要。很多時候性能問題很簡單的就是因為我們忘了添加索引而造成的，或者說沒有添加更為有效的索引導致。如果不加索引的話，那麼查找任何哪怕只是一條特定的數據都會進行一次全表掃描，如果一張表的數據量很大而符合條件的結果又很少，那麼不加索引會引起致命的性能下降。但是也不是什麼情況都非得建索引不可，比如性別可能就只有兩個值，建索引不僅沒什麼優勢，還會影響到更新速度，這被稱為過度索引。

2、複合索引

比如有一條語句是這樣的：select

* from users where area=’beijing’ and

age=22;

如果我們是在area和age上分別創建單個索引的話，由於mysql查詢每次只能使用一個索引，所以雖然這樣已經相對不做索引時全表掃描提高了很多效率，但是如果在area、age兩列上創建複合索引的話將帶來更高的效率。如果我們創建了(area,

age,

salary)的複合索引，那麼其實相當於創建了(area,age,salary)、(area,age)、(area)三個索引，這被稱為最佳左前綴特性。因此我們在創建複合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。

3、索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的默認值為NULL。

4、使用短索引

對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的

列，如果在前10 個或20

個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。

5、排序的索引問題

mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order

by中的列是不會使用索引的。因此資料庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建複合索引。

6、like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like

「%aaa%」 不會使用索引而like 「aaa%」可以使用索引。

7、不要在列上進行運算

select *

from users where

YEAR(adddate)2007;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成

select * from

users where adddate『2007-01-01′;

8、不使用NOT

IN和操作

NOT IN和操作都不會使用索引將進行全表掃描。NOT IN可以NOT

EXISTS代替，id3則可使用id3 or id3來代替。

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

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

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

分表的分類

**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多。

VARCHAR 和 CHAR 是兩種主要的字元串類型，用於存儲字元。不幸的是，由於實現的方式依賴於存儲引擎，因此很難解釋這些字元串在磁碟和內存中如何存儲，除了除了常用的 InnoDB 和 MyISAM 外，假設你使用了其他存儲引擎，應當仔細閱讀存儲引擎的文檔。

VARCHAR 存儲可變長度的字元串，也是最常用的字元數據類型。相比固定長度的類型，VARCHAR 所需的存儲空間更小，它會儘可能少地使用存儲空間（例如，短的字元串佔據的空間）。對於 MyISAM 來說，如果創建表的時候指定了 ROW_FORMAT=FIXED 的話，那麼會使用固定的空間存儲欄位而導致空間浪費。VARCHAR 使用1-2個額外的位元組存儲字元串的長度：當最大長度低於255位元組的時候使用1個位元組，如果更多的話就使用2個位元組。因此，拉丁字符集的 VARCHAR(10)會使用11個位元組的存儲空間，而 VARCHAR(1000)則會使用1002個位元組的存儲空間。

VARCHAR 由於能夠節省空間，因此可以改善性能。但是，由於長度可變，當更新數據表的時候數據行的存儲空間會變化，這一定程度上會帶來額外的開銷。如果數據行的長度導致原有的存儲位置無法存放，那麼不同的存儲引擎會做不同的處理。例如 MyISAM 可能產生數據行的碎片，而 InnoDB 需要進行磁碟分頁來存放更新後的數據行。

通常，如果最大的列長度遠遠高於平均長度的話（例如可選的備註欄位），使用 VARCHAR 是划算的，同時如果更新的頻次很低，那麼碎片化也不會是一個問題。需要注意的是，如果使用的是 UTF-8字符集，則實際存儲的位元組長度是根據字元定的。對於中文，推薦的存儲字符集是 utf8mb4。

CHAR 類型的長度是固定的，MySQL 會對每個欄位分配足夠的存儲空間。 存儲CHAR 類型值的時候，MySQL 會移除後面多出來的空字元 。值是使用空字元進行對齊以便進行比較。對於短的字元串來說，使用 CHAR 更有優勢，而如果所有的值的長度幾乎一致的話，就可以使用 CHAR。例如存儲用戶密碼的MD5值時使用 CHAR 就更合適，這是因為 MD5的長度總是固定的。同時，對於欄位值經常改變的數據類型來說，CHAR 相比 VARCHAR 也更有優勢，因為 CHAR 不會產生碎片。對於很短的數據列，使用 CHAR 比 VARCHAR更高效，例如使用CHAR(1)存儲邏輯值的 Y 和 N，這種情況下只需要1個位元組，而 VARCHAR 需要2個位元組。

對於移除空字元這個特性會感覺奇怪，我們舉個例子：

按上面的結果插入數據表後，string2中的前置空格不會移除，但使用 CHAR 類型存儲時，string3尾隨空格會被移除，使用 SQL 查詢結果來檢驗一下：

得出來的結果如下，可以看到 CHAR 類型的 string3後面的空格被移除了，而 VARCHAR類型的沒有。這種情況大多數時候不會有什麼問題，實際在應用中也經常會使用 trim 函數移除兩端的空字元，但是如果確實需要存儲空格的時候，那就需要注意不要選擇使用 CHAR 類型：

數據如何存儲是由存儲引擎決定的，而且存儲引擎處理固定長度和可變長度的數據的方式並不相同。Memory 引擎使用固定大小的行，因此它需要分配最大可能的存儲空間——即便數據長度是可變的。但是，對於字元串的對齊和空字元截斷是由 MySQL 服務端完成的，因此所有存儲引擎都是一樣的。

與 CHAR 和 VARCHAR 相似的是 BINARY和 VARBINARY，用於存儲二進位位元組字元，BINARY 的對齊使用字元0的位元組值來對齊，並且再獲取值的時候不會截斷。如果需要使用字元的位元組值而不是字元的話，使用 BINARY 會更高效，這是因為比較時，一方面不需要考慮大小寫，另一方面是MySQL一次只比較一個位元組。

我們都知道，伺服器資料庫的開發一般都是通過java或者是PHP語言來編程實現的，而為了提高我們資料庫的運行速度和效率，資料庫優化也成為了我們每日的工作重點，今天，昌平IT培訓就一起來了解一下mysql伺服器資料庫的優化方法。

為什麼要了解索引真實案例案例一：大學有段時間學習爬蟲，爬取了知乎300w用戶答題數據，存儲到mysql數據中。

那時不了解索引，一條簡單的「根據用戶名搜索全部回答的sql「需要執行半分鐘左右，完全滿足不了正常的使用。

案例二：近線上應用的資料庫頻頻出現多條慢sql風險提示，而工作以來，對資料庫優化方面所知甚少。

例如一個用戶數據頁面需要執行很多次資料庫查詢，性能很慢，通過增加超時時間勉強可以訪問，但是性能上需要優化。

索引的優點合適的索引，可以大大減小mysql伺服器掃描的數據量，避免內存排序和臨時表，提高應用程序的查詢性能。

索引的類型mysql數據中有多種索引類型，primarykey，unique，normal，但底層存儲的數據結構都是BTREE;有些存儲引擎還提供hash索引，全文索引。

BTREE是常見的優化要面對的索引結構，都是基於BTREE的討論。

B-TREE查詢數據簡單暴力的方式是遍歷所有記錄;如果數據不重複，就可以通過組織成一顆排序二叉樹，通過二分查找演算法來查詢，大大提高查詢性能。

而BTREE是一種更強大的排序樹，支持多個分支，高度更低，數據的插入、刪除、更新更快。

現代資料庫的索引文件和文件系統的文件塊都被組織成BTREE。

btree的每個節點都包含有key，data和只想子節點指針。

btree有度的概念d=1。

假設btree的度為d，則每個內部節點可以有n=[d+1，2d+1)個key，n+1個子節點指針。

樹的大高度為h=Logb[(N+1)/2]。

索引和文件系統中，B-TREE的節點常設計成接近一個內存頁大小(也是磁碟扇區大小)，且樹的度非常大。

這樣磁碟I/O的次數，就等於樹的高度h。

假設b=100，一百萬個節點的樹，h將只有3層。

即，只有3次磁碟I/O就可以查找完畢，性能非常高。

索引查詢建立索引後，合適的查詢語句才能大發揮索引的優勢。

另外，由於查詢優化器可以解析客戶端的sql語句，會調整sql的查詢語句的條件順序去匹配合適的索引。

在JAVA開發中資料庫的學習也是我們需要了解的，截下來幾篇文章都是關於資料庫的設計和應用，那麼java課程培訓機構廢話不多說開始學習吧！  資料庫的設計  資料庫設計是基礎，資料庫優化是建立在設計基礎之上的。

好的資料庫一定擁有好的設計。

  資料庫設計的目標是為用戶和各種應用系統提供一個信息基礎設施和高效的運行環境。

  資料庫的三大範式  第一範式1NF:所有的域都應該是原子性的，即資料庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。

  第二範式2Nf:第二範式在第一範式的基礎之上更進一層。

第二範式需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關(主要針對聯合主鍵而言)。

也就是說在一個資料庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張資料庫表中。

  第三範式3Nf:所有欄位必須與主鍵直接相關，而不是間接相關。

也可以理解為欄位不要和其他非主鍵欄位相關.  注意:這三個範式儘可能去遵守,不是一定要墨守成規.這只是讓我們設計的表的時候,越靠近這些範式,可以使欄位盡量的減小冗餘.但是有時候也可以根據實際需要小小的違背一下.但是第三範式違反一下還可以接受,但是第一範式別違反.  資料庫設計的步驟  需求分析階段  準確了解與分析用戶需求(包括數據與處理)。

是整個設計過程的基礎，是最困難、最耗費時間的一步。

  概念結構設計階段  是整個資料庫設計的關鍵–設計資料庫的E-R模型圖，確認需求信息的正確和完整  Entity_Relationship—實體之間的關係  一對一  一對多  多對一

在JAVA開發中資料庫的學習也是我們需要了解的，截下來幾篇文章都是關於資料庫的設計和應用，那麼java課程培訓機構廢話不多說開始學習吧！

資料庫的設計

資料庫設計是基礎，資料庫優化是建立在設計基礎之上的。好的資料庫一定擁有好的設計。

資料庫設計的目標是為用戶和各種應用系統提供一個信息基礎設施和高效的運行環境。

資料庫的三大範式

第一範式1NF:所有的域都應該是原子性的，即資料庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。

第二範式2Nf:第二範式在第一範式的基礎之上更進一層。第二範式需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關(主要針對聯合主鍵而言)。也就是說在一個資料庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張資料庫表中。

第三範式3Nf:所有欄位必須與主鍵直接相關，而不是間接相關。也可以理解為欄位不要和其他非主鍵欄位相關.

注意:這三個範式儘可能去遵守,不是一定要墨守成規.這只是讓我們設計的表的時候,越靠近這些範式,可以使欄位盡量的減小冗餘.但是有時候也可以根據實際需要小小的違背一下.但是第三範式違反一下還可以接受,但是第一範式別違反.

資料庫設計的步驟

需求分析階段

準確了解與分析用戶需求(包括數據與處理)。是整個設計過程的基礎，是最困難、最耗費時間的一步。

概念結構設計階段

是整個資料庫設計的關鍵–設計資料庫的E-R模型圖，確認需求信息的正確和完整

Entity_Relationship—實體之間的關係

一對一

一對多

多對一