在上一篇MySQL數據庫優化技巧中，主要是從表設計的角度來優化，這篇將探討一下其他的優化技巧。

1. 使用索引

索引是提高數據庫性能的常用方法，它可以令數據庫服務器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(), MIN()和ORDERBY這些命令的時候，性能提高更為明顯。
那該對哪些字段建立索引呢？一般說來，索引應建立在那些將用于JOIN, WHERE判斷和ORDER BY排序的字段上。盡量不要對數據庫中某個含有大量重復的值的字段建立索引，在這樣的字段上建立索引將不會有什么幫助；相反，還有可能降低數據庫的性能。我們在創建表的時候可以同時創建合適的索引，也可以使用ALTER TABLE或CREATE INDEX在以后創建索引。
索引有4種：
1）普通索引：最基本的索引類型，沒有唯一性限制。
2）唯一性索引
3）主鍵：也是一種唯一性索引，必須指定為primary key。
4）全文索引
MySQL從版本3.23.23開始支持全文索引和搜索。全文索引在MySQL 中是一個FULLTEXT類型索引，可在varchar或者text類型的列上創建，但僅能用于MyISAM 類型的表。對于一個大的數據庫，將數據裝載到一個沒有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX創建索引，將是非常快的。但如果將數據裝載到一個已經有FULLTEXT索引的表中，執行過程將會非常慢。

2. 利用LIMIT 1取得唯一行

有時，當你要查詢一張表是，你知道自己只需要看一行。你可能會去的一條十分獨特的記錄，或者只是剛好檢查了任何存在的記錄數，他們都滿足了你的WHERE子句。
在這種情況下，增加一個LIMIT 1會令你的查詢更加有效。這樣數據庫引擎發現只有1后將停止掃描，而不是去掃描整個表或索引。‘

3. 選取合適的存儲引擎

1)MyISAM:默認的MySQL存儲引擎。如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性要求不是很高。其優勢是訪問的速度快。(尤其適合論壇的帖子表)
2)InnoDB：提供了具有提交、回滾和崩潰恢復能力的事務安全。但是對比MyISAM，寫的處理效率差一些并且會占用更多的磁盤空間(如果對安全要求高，則使用innodb)。[賬戶，積分]
3)Memory/heap [一些訪問頻繁，變化頻繁，又沒有必要入庫的數據：比如用戶在線狀態]
說明: memory表的數據都在內存中，因此操作速度快，但是缺少是當mysql重啟后，數據丟失，但表的結構在.
注：從mysql5.5.x開始，默認的存儲引擎變更為innodb，innodb是為處理巨大數據量時擁有最大性能而設計的。它的 cpu效率可能是任何其他基于磁盤的關系數據庫引擎所不能匹敵的。

4. myisam表的定時維護

對于myisam 存儲引擎而言，需要定時執行optimize table 表名，通過optimize table語句可以消除刪除和更新造成的磁盤碎片，從而減少空間的浪費。
語法格式：optimizetable 表名
清理完成碎片之后會減少空間的浪費！

5. 分表優化

把一個大表分成幾個小表：
1)垂直分割：采用內容主表+附加表的方式
內容主表：存儲各種數據的一些公共信息，比如數據的名稱，添加時間等，
可以使用多個附加表，附加表存儲一些數據的獨特的信息。
主要原因：是內容主表里面的數據訪問比較頻繁。
2)水平分割：采用對id取模的方式

6. 并發處理的鎖機制

比如執行如下操作：
（1）從數據庫中取出id值，
（2）進行加1操作。
（3）修改完成后，再保存到數據庫中。
比如原來 id的值為100,==》101
以上步驟執行100次，最后變成200
有兩個用戶同時執行的話。
a用戶：100->101
b用戶：100->101
通過鎖機制來進行解決
鎖機制：在執行時，只有一個用戶獲得鎖，其他用戶處于阻塞狀態，需要等待解鎖。
mysql 的鎖有以下幾種形式：
表級鎖：開銷小，加鎖快，發生鎖沖突的概率最高，并發度最低。myisam引擎屬于這種類型。
行級鎖：開銷大，加鎖慢，發生鎖沖突的概率最低，并發度也最高。innodb屬于這種類型。
1、表鎖的演示；
對myisam表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放后，才會執行其他進程的操作。
表添加讀鎖后，其他進程對該表只能查詢操作，修改時會被阻塞。
當前進程，能夠執行查詢操作，不能執行修改操作。不能對沒有鎖定的表進行操作。
鎖表的語法：
lock table 表名 read|write
也可以鎖定多個表，語法是：locktable 表1 read|wirte,表2 read|wirte
對myisam表的寫操作（加寫鎖），會阻塞其他進程對鎖定表的任何操作，不能讀寫，
表加寫鎖后，則只有當前進程對鎖定的表，可以執行任何操作。其他進程的操作會被阻塞。
2、行鎖的演示
innodb存儲引擎是通過給索引上的索引項加鎖來實現的，這就意味著：只有通過索引條件檢索數據，innodb才會使用行級鎖，否則，innodb使用表鎖。
行鎖的語法：
begin
sql語句
commit
開啟行鎖后，當前進程在針對某條記錄執行操作時，其他進程不能操作和當前進程相同id的記錄。

7. 大批量插入數據

1）對于MyISAM：
先禁用索引：
alter table table_name disable keys;
loading data//insert語句; 執行插入語句
執行完成插入語句后，開啟索引，統一添加索引。
alter table table_name enable keys;
2）對于Innodb：
1，將要導入的數據按照主鍵排序
2，setunique_checks=0,關閉唯一性校驗。
3，setautocommit=0,關閉自動提交。

8. 開啟慢查詢

對于每一條你認為太慢的查詢使用EXPLAIN。

9. 使用臨時表來加速查詢

把表的一個子集進行排序并創建臨時表，有時能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優化器的工作。
如果一個查詢要被執行多次，可以把部分結果先放在一個臨時文件中。

10. 優化like語句

通配符避免出現在詞首，否則將不能利用索引提高查詢速度。可采用reverse函數來處理。

總結

以上是生活随笔為你收集整理的MySQL数据库优化技巧（二）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。