Mysql資料庫優化的方法總結（必看）

這篇文章帶給大家的內容是關於Mysql資料庫優化的方法總結，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

學無止境，資料庫最佳化分為方方面面，在這裡，我進行了比較全的總結，分享給正在工作或學習的同儕。

資料庫的最佳化分為以下七大面向：

1 、表格的設計要符合三範式（適當的反三範式也可以）；

2、增加適當的索引，索引對查詢速度影響很大，必須新增索引（主鍵索引，唯一索引，普通索引，全文索引）；

#3、新增適當儲存過程，觸發器，事務等；

4 、讀寫分離（主從資料庫）；

5、對sql語句的一些最佳化，（查詢執行速度比較慢的sql語句）；

##6、分錶分區（

分錶：把一張大表分成多張表。分區：把一張表裡面的分配到不同的區域存儲）；

7、對mysql伺服器硬體的升級操作。

接下來我將具體講解優化的方式。

一、三範式

第一個範式：

原子性：表裡面的欄位不能再分割，只要是關聯式資料庫，就天然的自動滿足第一範式

關係型資料庫

（有行和列的概念）：mysql 、sql server、oracle、db2、infomix、sybase、postgresql，在設計時，先有函式庫->表->欄位->具體記錄（內容）：儲存資料時，要設計欄位。

非關聯式資料庫（泛指nosql資料庫）：memcache、redis、momgodb等。

第二範式：

一個表格中沒有完全相同的記錄，透過一個主鍵即能解決

第三範式：

表中無法儲存冗餘資料

反三範式設計：

相簿表1生活100#2

ID	相簿名稱	#相簿瀏覽量
	照片
##工作照	100

照片表#ID##照片名稱相簿ID瀏覽量#1我的小狗1492我的小貓1#513我的同事2100

如果要算一個相簿的瀏覽量，我們可以在相簿表中新增相簿瀏覽量字段，瀏覽照片的時候同時更新相簿瀏覽量。

二、開啟慢查詢

#Mysql慢查詢預設是關閉的，預設記錄超過10秒的sql語句。

1.檢視慢查詢記錄時間：

#

show variables like ‘long_query_time’;

2.修改慢式查詢時間：

set long_query_time=2;

3.透過如下的一個函數來進行測試：

benchmark(count,expr)   函数可以测试执行count次expr操作需要的时间

#三、建立索引

1、主鍵索引的特點：

(1)一個表格中最多只有一個主鍵索引

(2)一個主鍵索引可以指向多個欄位

( 3)主鍵索引的列，不能有重複的值，也不能有null

(4)主鍵索引的效率高。

2、唯一索引的特點：

#(1)一個表格中可以有多個唯一索引

#(2)一個唯一索引可以指向多個列，

(3)如果在唯一索引上，沒有指定not null，則該列可以為空，同時可以有多個null,

(4)唯一索引的效率較高。

3、普通索引：

使用普通索引主要是提高查詢效率

4 、全文索引

mysql自帶的全文索引mysql5.5不支援中文，支援英文，同時要求表的儲存引擎是myisam。如果希望支援中文，有兩個方案，

(1)使用aphinx中文版coreseek (來取代全文索引)

(2)外掛程式mysqlcft。

新增索引主要的問題：

(1)較頻繁的作為查詢條件欄位應該建立索引，唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件，更新非常頻繁的欄位不適合建立索引

(2)不會出現在WHERE子句中欄位不該建立索，索引是由代價的，雖然是查詢速度提高了，但是，會影響增該刪的效率。而且索引檔會佔用空間。

四、分錶、分割區

#垂直分表（內容主表附加表）：

#內容主表：儲存各種資料的一些公共訊息，例如資料的名稱，新增時間等，

可以使用多個附加表，附加表儲存一些資料的獨特的資訊。

主要原因：是內容主表裡面的資料存取比較頻繁。

特點：表格結構不同

#水平分錶：

將表數據存在不同的表中。

特點：表格結構相同

分割區：

就是把一個表格儲存到磁碟不同區域，仍然是一張表。

基本的概念：

#(1)Range（範圍）–這個模式允許將資料分割不同範圍。例如可以將一個表格透過年份劃分成若干個分區。

(2)List（預先定義清單）–此模式允許系統透過預先定義的清單的值來分割資料。

(3)Hash（雜湊）–這中模式允許透過對錶的一個或多個列的Hash Key進行計算，最後透過這個Hash碼不同數值對應的數據區域進行分區。例如可以建立一個對錶主鍵進行分區的表。

(4)Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

分区表的限制：

(1)只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列。

(2)最大分区数目不能超过1024。

(3)如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内。

(4)按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多。

五、并发处理的锁机制

锁机制：在执行时，只有一个用户获得锁，其他用户处于阻塞状态，需要等待解锁。

mysql 的锁有以下几种形式：

表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam引擎属于这种类型。

行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb属于这种类型。

表锁的演示：

1.对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的操作。

2.表添加读锁后，其他进程对该表只能查询操作，修改时会被阻塞。

3.当前进程，能够执行查询操作，不能执行修改操作。不能对没有锁定的表进行操作。

4.锁表的语法：

lock table 表名 read|write

5.也可以锁定多个表

6.对myisam表的写操作（加写锁），会阻塞其他进程对锁定表的任何操作，不能读写，

7.表加写锁后，则只有当前进程对锁定的表，可以执行任何操作。其他进程的操作会被阻塞。

 行锁的演示：

1.innodb存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，innodb才会使用行级锁，否则，innodb使用表锁。

2.开启行锁后，当前进程在针对某条记录执行操作时，其他进程不能操作和当前进程相同id的记录。

php里面有文件锁，在实际的项目中多数使用文件锁，因为表锁，会阻塞，当对一些表添加写锁后，其他进程就不能操作了。这样会阻塞整个网站，会拖慢网站的速度。

相关推荐：

以上是Mysql資料庫優化的方法總結（必看）的詳細內容。更多資訊請關注PHP中文網其他相關文章！