簡介： MySQL數據庫優化技巧大全

MySQL優化三大方向

① 優化MySQL所在服務器內核(此優化一般由運維人員完成)。

② 對MySQL配置參數進行優化(my.cnf)此優化需要進行壓力測試來進行參數調整。

③ 對SQL語句以及表優化。

MySQL參數優化

1:MySQL 默認的最大連接數為 100，可以在 mysql 客戶端使用以下命令查看

mysql> show variables like 'max_connections';

2:查看當前訪問Mysql的線程

mysql> show processlist;

3:設置最大連接數

mysql>set globle max_connections = 5000;

最大可設置16384,超過沒用

4:查看當前被使用的connections

mysql>show globle status like 'max_user_connections'

申請阿里云服務時，可以使用2000元阿里云代金券，阿里云官網領取網址：https://dashi.aliyun.com/site/yun/youhui (長期有效)

對MySQL語句性能優化的16條經驗

① 為查詢緩存優化查詢

② EXPLAIN 我們的SELECT查詢(可以查看執行的行數)

③ 當只要一行數據時使用LIMIT 1

④ 為搜索字段建立索引

⑤ 在Join表的時候使用相當類型的列，并將其索引

⑥ 千萬不要 ORDER BY RAND ()

⑦ 避免SELECT *

⑧ 永遠為每張表設置一個ID

⑨ 可以使用ENUM 而不要VARCHAR

⑩ 盡可能的使用NOT NULL

? 固定長度的表會更快

? 垂直分割

? 拆分打的DELETE或INSERT語句

? 越小的列會越快

? 選擇正確的存儲引擎

? 小心 "永久鏈接"

具體描述如下：

(一) 使用查詢緩存優化查詢

大多數的MySQL服務器都開啟了查詢緩存。這是提高性能最有效的方法之一，而且這是被MySQL引擎處理的。當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放入一個緩存中，這樣后續的相同查詢就不用操作而直接訪問緩存結果了。

這里最主要的問題是，對于我們程序員來說，這個事情是很容易被忽略的。因為我們某些查詢語句會讓MySQL不使用緩存，示例如下：

1：SELECT username FROM user WHERE signup_date >= CURDATE()

2：SELECT username FROM user WHERE signup_date >= '2014-06-24‘

上面兩條SQL語句的差別就是 CURDATE() ，MySQL的查詢緩存對這個函數不起作用。所以，像 NOW() 和 RAND() 或是其它的諸如此類的SQL函數都不會開啟查詢緩存，因為這些函數的返回是會不定的易變的。所以，你所需要的就是用一個變量來代替MySQL的函數，從而開啟緩存。

(二) 使用EXPLAIN關鍵字檢測查詢

使用EXPLAIN關鍵字可以使我們知道MySQL是如何處理SQL語句的，這樣可以幫助我們分析我們的查詢語句或是表結構的性能瓶頸；EXPLAIN的查詢結果還會告訴我們索引主鍵是如何被利用的，數據表是如何被被搜索或排序的....等等。語法格式是：EXPLAIN +SELECT語句;

我們可以看到，前一個結果顯示搜索了 7883 行，而后一個只是搜索了兩個表的 9 和 16 行。查看rows列可以讓我們找到潛在的性能問題。

(三)當只要一行數據時使用LIMIT 1

加上LIMIT 1可以增加性能。MySQL數據庫引擎會在查找到一條數據后停止搜索，而不是繼續往后查詢下一條符合條件的數據記錄。

(四)為搜索字段建立索引

索引不一定就是給主鍵或者是唯一的字段，如果在表中，有某個字段經常用來做搜索，需要將其建立索引。

索引的有關操作如下：

1.創建索引

在執行CREATE TABLE語句時可以創建索引，也可以單獨用CREATE INDEX或ALTER TABLE來為表增加索引。

1.1> ALTER TABLE

ALTER TABLE 用來創建普通索引、唯一索引、主鍵索引和全文索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE table_name ADD UNIQUE (column_list);

ALTER TABLE table_name ADD PRIMARY KEY (column_list);

ALTER TABLE table_name ADD FULLTEXT (column_list);

其中table_name是要增加索引名的表名，column_list指出對哪些列列進行索引，多列時各列之間使用半角逗號隔開。索引名index_name是可選的，如果不指定索引名稱，MySQL將根據第一個索引列自動指定索引名稱，另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時創建多個索引。

1.2> CREATE INDEX

CREATE INDEX可對表增加普通索引或UNIQUE索引以及全文索引，但是不可以對表增加主鍵索引

CREATE INDEX index_name ON table_name (column_list);

CREATE UNIQUE index_name ON table_name (column_list);

CREATE FULLTEXT index_name ON table_name (column_list);

table_name、index_name和column_list具有與ALTER TABLE語句中相同的含義，索引名必須指定。另外，不能用CREATE INDEX語句創建PRIMARY KEY索引。

2.索引類型

普通索引INDEX：適用于name、email等一般屬性

唯一索引UNIQUE：與普通索引類似，不同的是唯一索引要求索引字段值在表中是唯一的，這一點和主鍵索引類似，但是不同的是，唯一索引允許有空值。唯一索引一般適用于身份證號碼、用戶賬號等不允許有重復的屬性字段上。

主鍵索引：其實就是主鍵，一般在建表時就指定了，不需要額外添加。

全文檢索：只適用于VARCHAR和Text類型的字段。

注意：全文索引和普通索引是有很大區別的，如果建立的是普通索引，一般會使用like進行模糊查詢，只會對查詢內容前一部分有效，即只對前面不使用通配符的查詢有效，如果前后都有通配符，普通索引將不會起作用。對于全文索引而言在查詢時有自己獨特的匹配方式，例如我們在對一篇文章的標題和內容進行全文索引時：

ALTER TABLE article ADD FULLTEXT ('title', 'content'); 在進行檢索時就需要使用如下的語法進行檢索：

SELECT * FROM article WHERE MATCH('title', 'content') AGAINST ('查詢字符串');

在使用全文檢索時的注意事項：

MySql自帶的全文索引只能用于數據庫引擎為MYISAM的數據表，如果是其他數據引擎，則全文索引不會生效。此外，MySql自帶的全文索引只能對英文進行全文檢索，目前無法對中文進行全文檢索。如果需要對包含中文在內的文本數據進行全文檢索，我們需要采用Sphinx(斯芬克斯)/Coreseek技術來處理中文。另外使用MySql自帶的全文索引時，如果查詢字符串的長度過短將無法得到期望的搜索結果。MySql全文索引所能找到的詞默認最小長度為4個字符。另外，如果查詢的字符串包含停止詞，那么該停止詞將會被忽略。

3.組合索引

組合索引又稱多列索引，就是建立索引時指定多個字段屬性。有點類似于字典目錄，比如查詢 'guo' 這個拼音的字時，首先查找g字母，然后在g的檢索范圍內查詢第二個字母為u的列表，最后在u的范圍內查找最后一個字母為o的字。比如組合索引(a,b,c)，abc都是排好序的，在任意一段a的下面b都是排好序的，任何一段b下面c都是排好序的

組合索引的生效原則是 從前往后依次使用生效，如果中間某個索引沒有使用，那么斷點前面的索引部分起作用，斷點后面的索引沒有起作用；

造成斷點的原因：

前邊的任意一個索引沒有參與查詢，后邊的全部不生效。

前邊的任意一個索引字段參與的是范圍查詢，后面的不會生效。

斷點跟索引字字段在SQL語句中的位置前后無關，只與是否存在有關。在網上找到了很好的示例：

比如：

where a=3 and b=45 and c=5 .... #這種三個索引順序使用中間沒有斷點，全部發揮作用；

where a=3 and c=5... #這種情況下b就是斷點，a發揮了效果，c沒有效果

where b=3 and c=4... #這種情況下a就是斷點，在a后面的索引都沒有發揮作用，這種寫法聯合索引沒有發揮任何效果；

where b=45 and a=3 and c=5 .... #這個跟第一個一樣，全部發揮作用，abc只要用上了就行，跟寫的順序無關

(a,b,c) 三個列上加了聯合索引(是聯合索引 不是在每個列上單獨加索引)而是建立了a,(a,b),(a,b,c)三個索引，另外(a,b,c)多列索引和 (a,c,b)是不一樣的。

具體實例可以說明：

(0) select * from mytable where a=3 and b=5 and c=4;

abc三個索引都在where條件里面用到了，而且都發揮了作用

(1) select * from mytable where c=4 and b=6 and a=3;

這條語句為了說明 組合索引與在SQL中的位置先后無關，where里面的條件順序在查詢之前會被mysql自動優化，效果跟上一句一樣

(2) select * from mytable where a=3 and c=7;

a用到索引，b沒有用，所以c是沒有用到索引效果的

(3) select * from mytable where a=3 and b>7 and c=3;

a用到了，b也用到了，c沒有用到，這個地方b是范圍值，也算斷點，只不過自身用到了索引

(4) select * from mytable where b=3 and c=4;

因為a索引沒有使用，所以這里 bc都沒有用上索引效果

(5) select * from mytable where a>4 and b=7 and c=9;

a用到了 b沒有使用，c沒有使用

(6) select * from mytable where a=3 order by b;

a用到了索引，b在結果排序中也用到了索引的效果，前面說了，a下面任意一段的b是排好序的

(7) select * from mytable where a=3 order by c;

a用到了索引，但是這個地方c沒有發揮排序效果，因為中間斷點了，使用 explain 可以看到 filesort

(8) select * from mytable where b=3 order by a;

b沒有用到索引，排序中a也沒有發揮索引效果

注意：在查詢時，MYSQL只能使用一個索引，如果建立的是多個單列的普通索引，在查詢時會根據查詢的索引字段，從中選擇一個限制最嚴格的單例索引進行查詢。別的索引都不會生效。

4.查看索引

mysql> show index from tblname;

mysql> show keys from tblname;

5.刪除索引

刪除索引的mysql格式 :DORP INDEX IndexName ON tab_name；

注意：不能使用索引的情況

對于普通索引而言 在使用like進行通配符模糊查詢時,如果首尾之間都使用了通配符，索引時無效的。

假設查詢內容的關鍵詞為'abc'

SELECT * FROM tab_name WHERE index_column LIKE 'abc%'; #索引是有效的

SELECT * FROM tab_name WHERE index_column LIKE '%abc'; #索引是無效的

SELECT * FROM tab_name WHERE index_column LIKE '%cba'; #索引是有效的

SELECT * FROM tab_name WHERE index_column LIKE '%abc%'; #索引是無效的

當檢索的字段內容比較大而且檢索內容前后部分都不確定的情況下，可以改為全文索引，并使用特定的檢索方式。

(五)在join表的時候使用相當類型的列，并將其索引

如果在程序中有很多JOIN查詢，應該保證兩個表中join的字段時被建立過索引的。這樣MySQL顳部會啟動優化JOIN的SQL語句的機制。注意：這些被用來JOIN的字段，應該是相同類型的。例如：如果要把 DECIMAL 字段和一個 INT 字段Join在一起，MySQL就無法使用它們的索引。對于那些STRING類型，還需要有相同的字符集才行。(兩個表的字符集有可能不一樣)

例如：

SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = “user_id”

兩個 state 字段應該是被建過索引的，而且應該是相當的類型，相同的字符集。

(六)切記不要使用ORDER BY RAND()

如果你真的想把返回的數據行打亂了，你有N種方法可以達到這個目的。這樣使用只讓你的數據庫的性能呈指數級的下降。這里的問題是：MySQL會不得不去執行RAND()函數(很耗CPU時間)，而且這是為了每一行記錄去記行，然后再對其排序。就算是你用了Limit 1也無濟于事(因為要排序)

(七)避免使用SELECT *

從數據庫里讀出越多的數據，那么查詢就會變得越慢。并且，如果我們的數據庫服務器和WEB服務器是兩臺獨立的服務器的話，這還會增加網絡傳輸的負載。 所以，我們應該養成一個需要什么就取什么的好的習慣。

Hibernate性能方面就會差，它不用*，但它將整個表的所有字段全查出來

優點：開發速度快

(八)永遠為每張表設置一個ID主鍵

我們應該為數據庫里的每張表都設置一個ID做為其主鍵，而且最好的是一個INT型的(推薦使用UNSIGNED)，并設置上自動增加的 AUTO_INCREMENT標志。 就算是我們 users 表有一個主鍵叫 “email”的字段，我們也別讓它成為主鍵。使用 VARCHAR 類型來當主鍵會使用得性能下降。另外，在我們的程序中，我們應該使用表的ID來構造我們的數據結構。 而且，在MySQL數據引擎下，還有一些操作需要使用主鍵，在這些情況下，主鍵的性能和設置變得非常重要，比如，集群，分區…… 在這里，只有一個情況是例外，那就是“關聯表”的“外鍵”，也就是說，這個表的主鍵，通過若干個別的表的主鍵構成。我們把這個情況叫做“外鍵”。比如：有一個“學生表”有學生的ID，有一個“課程表”有課程ID，那么，“成績表”就是“關聯表”了，其關聯了學生表和課程表，在成績表中，學生ID和課程ID叫“外鍵”其共同組成主鍵。

(九)使用ENUM而不是VARCHAR

ENUM 類型是非常快和緊湊的。在實際上，其保存的是 TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項列表變得相當的完美。 如果我們有一個字段，比如“性別”，“國家”，“民族”，“狀態”或“部門”，我們知道這些字段的取值是有限而且固定的，那么，我們應該使用 ENUM 而不是 VARCHAR。

(十)盡可能的不要賦值為NULL

如果不是特殊情況，盡可能的不要使用NULL。在MYSQL中對于INT類型而言，EMPTY是0，而NULL是空值。而在Oracle中 NULL和EMPTY的字符串是一樣的。NULL也需要占用存儲空間，并且會使我們的程序判斷時更加復雜。現實情況是很復雜的，依然會有些情況下，我們需要使用NULL值。 下面摘自MySQL自己的文檔： “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

(十一) 固定長度的表會更快

如果表中的所有字段都是“固定長度”的，整個表會被認為是 “static” 或 “fixed-length”。 例如，表中沒有如下類型的字段： VARCHAR，TEXT，BLOB。只要我們包括了其中一個這些字段，那么這個表就不是“固定長度靜態表”了，這樣，MySQL 引擎會用另一種方法來處理。 固定長度的表會提高性能，因為MySQL搜尋得會更快一些，因為這些固定的長度是很容易計算下一個數據的偏移量的，所以讀取的自然也會很快。而如果字段不是定長的，那么，每一次要找下一條的話，需要程序找到主鍵。 并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論我們用不用，他都是要分配那么多的空間。另外在取出值的時候要使用trim去除空格

(十二)垂直分割

“垂直分割”是一種把數據庫中的表按列變成幾張表的方法，這樣可以降低表的復雜度和字段的數目，從而達到優化的目的。

(十三)拆分大的DELETE或INSERT

如果我們需要在一個在線的網站上去執行一個大的 DELETE 或 INSERT 查詢，我們需要非常小心，要避免我們的操作讓我們的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，別的操作都進不來了。Apache 會有很多的子進程或線程。所以，其工作起來相當有效率，而我們的服務器也不希望有太多的子進程，線程和數據庫鏈接，這是極大的占服務器資源的事情，尤其是內存。如果我們把我們的表鎖上一段時間，比如30秒鐘，那么對于一個有很高訪問量的站點來說，這30秒所積累的訪問進程/線程，數據庫鏈接，打開的文件數，可能不僅僅會讓我們的WEB服務Crash，還可能會讓我們的整臺服務器馬上掛了。所以在使用時使用LIMIT 控制數量操作記錄的數量。

(十四)越小的列會越快

對于大多數的數據庫引擎來說，硬盤操作可能是最重大的瓶頸。所以，把我們的數據變得緊湊會對這種情況非常有幫助，因為這減少了對硬盤的訪問。 參看 MySQL 的文檔 Storage Requirements 查看所有的數據類型。 如果一個表只會有幾列罷了(比如說字典表，配置表)，那么，我們就沒有理由使用 INT 來做主鍵，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。如果我們不需要記錄時間，使用 DATE 要比 DATETIME 好得多。

(十五)選擇正確的存儲引擎

在MYSQL中有兩個存儲引擎MyISAM和InnoDB,每個引擎都有利有弊。

MyISAM適合于一些需要大量查詢的應用，但是對于大量寫操作的支持不是很好。甚至一個update語句就會進行鎖表操作，這時讀取這張表的所有進程都無法進行操作直至寫操作完成。另外MyISAM對于SELECT COUNT(*)這類的計算是超快無比的。InnoDB 的趨勢會是一個非常復雜的存儲引擎，對于一些小的應用，它會比 MyISAM 還慢。它支持“行鎖” ，于是在寫操作比較多的時候，會更優秀。并且，他還支持更多的高級應用，比如：事務。

MyISAM是MYSQL5.5版本以前默認的存儲引擎，基于傳統的ISAM類型，支持B-Tree，全文檢索，但是不是事務安全的，而且不支持外鍵。不具有原子性。支持鎖表。

InnoDB是事務型引擎，支持ACID事務(實現4種事務隔離機制)、回滾、崩潰恢復能力、行鎖。以及提供與Oracle一致的不加鎖的讀取方式。InnoDB存儲它的表和索引在一個表空間中，表空間可以包含多個文件。

MyISAM和InnoDB比較，如下圖所示：

在5.5之后默認的存儲引擎是INNODB

可以單獨進行修改也可以在創建表時修改：

ALTER TABLE tab_name ENGINE INNODB；

(十六)小心永久鏈接

“永久鏈接”的目的是用來減少重新創建MySQL鏈接的次數。當一個鏈接被創建了，它會永遠處在連接的狀態，就算是數據庫操作已經結束了。而且，自從我們的Apache開始重用它的子進程后——也就是說，下一次的HTTP請求會重用Apache的子進程，并重用相同的 MySQL 鏈接。

而且，Apache 運行在極端并行的環境中，會創建很多很多的了進程。這就是為什么這種“永久鏈接”的機制工作地不好的原因。在我們決定要使用“永久鏈接”之前，我們需要好好地考慮一下我們的整個系統的架構。

總結

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

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