首頁(yè)技術(shù)文章正文

Java培訓(xùn)實(shí)戰(zhàn)教程之mysql優(yōu)化

更新時(shí)間:2016年01月20日15時(shí)11分來源:傳智播客Java培訓(xùn)學(xué)院瀏覽次數(shù):

Java培訓(xùn)過程中精點(diǎn)、難點(diǎn)知識(shí)解析
1. mysql引擎1.1. 引擎類型

MySQL常用的存儲(chǔ)引擎為MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事務(wù)安全表，其他存儲(chǔ)引擎都是非事務(wù)安全表。

MyISAM是MySQL的默認(rèn)存儲(chǔ)引擎。MyISAM不支持事務(wù)、也不支持外鍵，但其訪問速度快，對(duì)事務(wù)完整性沒有要求。

innoDB存儲(chǔ)引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是比起MyISAM存儲(chǔ)引擎，InnoDB寫的處理效率差一些并且會(huì)占用更多的磁盤空間以保留數(shù)據(jù)和索引

MEMORY存儲(chǔ)引擎使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表。每個(gè)MEMORY表只實(shí)際對(duì)應(yīng)一個(gè)磁盤文件。MEMORY類型的表訪問非常得快，因?yàn)樗臄?shù)據(jù)是放在內(nèi)存中的，并且默認(rèn)使用HASH索引。但是一旦服務(wù)關(guān)閉，表中的數(shù)據(jù)就會(huì)丟失掉。

MERGE存儲(chǔ)引擎是一組MyISAM表的組合，這些MyISAM表必須結(jié)構(gòu)完全相同。MERGE表本身沒有數(shù)據(jù)，對(duì)MERGE類型的表進(jìn)行查詢、更新、刪除的操作，就是對(duì)內(nèi)部的MyISAM表進(jìn)行的。

1.2. 如何選擇合適的存儲(chǔ)引擎

選擇標(biāo)準(zhǔn): 根據(jù)應(yīng)用特點(diǎn)選擇合適的存儲(chǔ)引擎,對(duì)于復(fù)雜的應(yīng)用系統(tǒng)可以根據(jù)實(shí)際情況選擇

多種存儲(chǔ)引擎進(jìn)行組合.

下面是常用存儲(chǔ)引擎的適用環(huán)境:

1.MyISAM：默認(rèn)的 MySQL 插件式存儲(chǔ)引擎，它是在Web、數(shù)據(jù)倉(cāng)儲(chǔ)和其他應(yīng)用環(huán)境下最常使用的存儲(chǔ)引擎之一

2.InnoDB：用于事務(wù)處理應(yīng)用程序，具有眾多特性，包括ACID 事務(wù)支持。

3.Memory：將所有數(shù)據(jù)保存在 RAM 中，在需要快速查找引用和其他類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問。

4.Merge：允許 MySQL DBA 或開發(fā)人員將一系列等同的MyISAM 表以邏輯方式組合在一起，

并作為 1 個(gè)對(duì)象引用它們。對(duì)于諸如數(shù)據(jù)倉(cāng)儲(chǔ)等 VLDB 環(huán)境十分適合

2. 設(shè)置高速緩存

注：可以通過order by語(yǔ)句測(cè)試緩存，order by語(yǔ)句執(zhí)行速度慢！

2.1. 設(shè)置高速緩存2.1.1. 查看高速緩存是否支持

SHOW VARIABLES LIKE 'have_query_cache';

2.1.2. 設(shè)置和查詢高速緩存大小

SET GLOBAL query_cache_size = 41984; #40K

SHOW VARIABLESLIKE 'query_cache_size';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| query_cache_size | 41984 |

+------------------+-------+

2.1.3. 緩存開啟的方式

查看是否開啟

SHOW VARIABLES LIKE'query_cache_type';

開啟

SET SESSION query_cache_type =ON;

如果查詢緩存大小設(shè)置為大于0，query_cache_type變量影響其工作方式。這個(gè)變量可以設(shè)置為下面的值：

0或OFF：將阻止緩存或查詢緩存結(jié)果。

1或ON：將允許緩存，以SELECTSQL_NO_CACHE開始的查詢語(yǔ)句除外。

2或DEMAND：僅對(duì)以SELECTSQL_CACHE開始的那些查詢語(yǔ)句啟用緩存。

出自Java培訓(xùn)

另外：

GLOBAL：設(shè)置所有鏈接的客戶端

session：設(shè)置單個(gè)客戶端

2.1.4. 設(shè)置緩存結(jié)果的最大值最小值

如果不設(shè)置緩存的上線下線，查詢結(jié)果過大將不會(huì)緩存。

查詢上線：

SHOW VARIABLES LIKE 'query_cache_limit';

設(shè)置上下線：

SET GLOBAL query_cache_limit=10485760; #10M

SET GLOBAL query_cache_min_res_unit=41984;

2.1.5. 查詢高速緩沖狀態(tài)和維護(hù)

可以使用下面的語(yǔ)句檢查MySQL服務(wù)器是否提供查詢緩存功能：

SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| have_query_cache | YES |

+------------------+-------+

FLUSH QUERY CACHE：語(yǔ)句來清理查詢緩存碎片以提高內(nèi)存使用性能。該語(yǔ)句不從緩存中移出任何查詢。

RESET QUERY CACHE：語(yǔ)句從查詢緩存中移出所有查詢。FLUSH TABLES語(yǔ)句也執(zhí)行同樣的工作。

SHOW STATUS：為了監(jiān)視查詢緩存性能，使用SHOWSTATUS查看緩存狀態(tài)變量，例如：

mysql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+--------+

| Qcache_free_blocks | 36 |

| Qcache_free_memory | 138488 |

| Qcache_hits | 79570 |

| Qcache_inserts | 27087 |

| Qcache_lowmem_prunes | 3114 |

| Qcache_not_cached | 22989 |

| Qcache_queries_in_cache | 415 |

| Qcache_total_blocks | 912 |

+-------------------------+--------+

QCACHE_free_blocks：空閑內(nèi)存塊的數(shù)量。

QCACHE_free_memory：空閑內(nèi)存的大小。

QCACHE_hits：查詢緩存被訪問的次數(shù)（命中數(shù)）。

QCACHE_inserts：加入到緩存的查詢數(shù)量。

QCACHE_lowmem_prunes：由于內(nèi)存較少?gòu)木彺鎰h除的查詢數(shù)量。

QCACHE_not_cached：非緩存查詢數(shù)(不可緩存，或由于query_cache_type設(shè)定值未緩存)。

Qcache_queries_in_cache：登記到緩存內(nèi)的查詢的數(shù)量。

Qcache_total_blocks：查詢緩存內(nèi)的總塊數(shù)。

2.2. 高速緩存語(yǔ)句要求

下面的兩個(gè)查詢被查詢緩存認(rèn)為是不相同的：

SELECT * FROM tbl_name

Select * from tbl_name

查詢必須是完全相同的(逐字節(jié)相同)才能夠被認(rèn)為是相同的。

2.3. 不緩存的語(yǔ)句

如果一個(gè)查詢包含下面函數(shù)中的任何一個(gè)，它不會(huì)被緩存：

BENCHMARK()

CONNECTION_ID()

CURDATE()

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

CURTIME()

DATABASE()

帶一個(gè)參數(shù)的ENCRYPT()

FOUND_ROWS()

GET_LOCK()

LAST_INSERT_ID()

LOAD_FILE()

MASTER_POS_WAIT()

NOW()

RAND()

RELEASE_LOCK()

SYSDATE()

不帶參數(shù)的UNIX_TIMESTAMP()

USER()

3. EXPLAIN執(zhí)行計(jì)劃3.1. 簡(jiǎn)介

使用 EXPLAIN 關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語(yǔ)句的。這可以幫你分析你的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。

EXPLAIN 的查詢結(jié)果還會(huì)告訴你你的索引主鍵被如何利用的，你的數(shù)據(jù)表是如何被搜索和排序的……等等，等等。

挑一個(gè)你的SELECT語(yǔ)句（推薦挑選那個(gè)最復(fù)雜的，有多表聯(lián)接的），把關(guān)鍵字EXPLAIN加到前面。

EXPLAIN

SELECT * FROM userinfo u INNER JOIN jobinfo j ON u.jobinfoId=j.id;

查看執(zhí)行計(jì)劃：

參數(shù)解釋：

id：查詢的序號(hào)

select_type:select類型，simple表示簡(jiǎn)單的查詢

table：引用的表

type：鏈接類型，all表示全表掃描，沒有使用索引。

possible_keys：查詢時(shí)可以使用的索引

key：查詢時(shí)正在使用的索引

key_len：索引的長(zhǎng)度

rows：查詢的行數(shù)，乘積即為笛卡爾積

Extra：該列包含MySQL解決查詢的詳細(xì)信息。

3.1.1. 參數(shù)詳解

id：這是SELECT的查詢序列號(hào)。

select_type：SELECT類型，可以為以下任何一種：

SIMPLE：簡(jiǎn)單SELECT(不使用UNION或子查詢)

PRIMARY：最外面的SELECT

UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句

DEPENDENT UNION：UNION中的第二個(gè)或后面的SELECT語(yǔ)句，取決于外面的查詢

UNION RESULT：UNION的結(jié)果。

SUBQUERY:子查詢中的第一個(gè)SELECT

DEPENDENT SUBQUERY:子查詢中的第一個(gè)SELECT，取決于外面的查詢

DERIVED:導(dǎo)出表的SELECT(FROM子句的子查詢)

table:輸出的行所引用的表。

type:聯(lián)接類型。下面給出各種聯(lián)接類型，按照從最佳類型到最壞類型進(jìn)行排序：

system表僅有一行(=系統(tǒng)表)。

const表最多有一個(gè)匹配行，它將在查詢開始時(shí)被讀取。

eq_ref比較的時(shí)候，“=”前后的變量都加了索引。

ref:前面的表加了索引。

index：該聯(lián)接類型與ALL相同，只是索引樹被掃描。

ALL：全表掃描。

possible_keys：possible_keys列指出MySQL能使用哪個(gè)索引在該表中找到行。

如果該列是NULL，則沒有相關(guān)的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。

key:顯示MySQL實(shí)際決定使用的索引。如果沒有選擇索引，鍵是NULL。

key_len:顯示MySQL決定使用的索引長(zhǎng)度。如果索引是NULL，則長(zhǎng)度為NULL。

ref：顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。

rows：顯示MySQL認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù)。

Extra：該列包含MySQL解決查詢的詳細(xì)信息。下面解釋了該列可以顯示的不同的文本字符串：

Distinct：MySQL發(fā)現(xiàn)第1個(gè)匹配行后，停止為當(dāng)前的行組合搜索更多的行。

Not exists：MySQL能夠?qū)Σ樵冞M(jìn)行LEFTJOIN優(yōu)化，發(fā)現(xiàn)1個(gè)匹配LEFT JOIN標(biāo)準(zhǔn)的行后，不再為前面的的行組合在該表內(nèi)檢查更多的行。

range checkedfor each record (index map: #)：MySQL沒有發(fā)現(xiàn)好的可以使用的索引，但發(fā)現(xiàn)如果來自前面的表的列值已知，可能部分索引可以使用。對(duì)前面的表的每個(gè)行組合，MySQL檢查是否可以使用range或index_merge訪問方法來索取行。

Using filesort：MySQL需要額外的一次傳遞，以找出如何按排序順序檢索行。通過根據(jù)聯(lián)接類型瀏覽所有行并為所有匹配WHERE子句的行保存排序關(guān)鍵字和行的指針來完成排序。然后關(guān)鍵字被排序，并按排序順序檢索行

Using index：從只使用索引樹中的信息而不需要進(jìn)一步搜索讀取實(shí)際的行來檢索表中的列信息。當(dāng)查詢只使用作為單一索引一部分的列時(shí)，可以使用該策略。

Using temporary：為了解決查詢，MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來容納結(jié)果。典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時(shí)。

Using where：WHERE子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會(huì)有一些錯(cuò)誤。

Using sort_union(...), Using union(...), Using intersect(...)：這些函數(shù)說明如何為index_merge聯(lián)接類型合并索引掃描。

3.2. 優(yōu)化方案3.2.1. 查看匹配的列類型和長(zhǎng)度是否相同

查看兩張表鏈接的列的類型和長(zhǎng)度是否相同，不同改為相同

ALTER TABLE 表名 MODIFY 列名 BIGINT(20);源自java培訓(xùn)

3.3. 為相關(guān)聯(lián)的列設(shè)置索引

查看索引：

SHOW INDEX FROM tbl_name;

創(chuàng)建索引：

ALTER TABLE 表名 ADD INDEX 索引名 (索引列) ;

刪除索引：

drop index 索引名 on 表名;

顯示使用索引：

USE INDEX

在你查詢語(yǔ)句中表名的后面，添加 USE INDEX 來提供你希望 MySQ 去參考的索引列

表，就可以讓 MySQL 不再考慮其他可用的索引。

Eg:SELECT * FROM mytable USE INDEX(mod_time, name) ...

IGNORE INDEX

如果你只是單純的想讓 MySQL 忽略一個(gè)或者多個(gè)索引，可以使用 IGNORE INDEX 作

為 Hint。

Eg:SELECT * FROM mytale IGNORE INDEX(priority) ...

FORCE INDEX

為強(qiáng)制 MySQL 使用一個(gè)特定的索引，可在查詢中使用 FORCE INDEX 作為 Hint。

Eg:SELECT * FROM mytable FORCE INDEX(mod_time) ...

3.4. 不使用索引的情況

下列情況下，Mysql 不會(huì)使用已有的索引：

1.如果 mysql 估計(jì)使用索引比全表掃描更慢，則不使用索引。例如：如果 key_part1

均勻分布在 1 和 100 之間，下列查詢中使用索引就不是很好：

SELECT * FROM table_name where key_part1> 1 and key_part1 < 90

2.如果使用內(nèi)存表并且 where 條件中不用＝索引列，其他>、<、 >=、 <=均不使用

索引；

3.如果 like 是以％開始；

4.對(duì) where 后邊條件為字符串的一定要加引號(hào)，字符串如果為數(shù)字 mysql 會(huì)自動(dòng)轉(zhuǎn)為

字符串，但是不使用索引。

3.5. 查看索引使用情況

語(yǔ)法：

mysql> show status like 'Handler_read%';

如果索引正在工作，Handler_read_key 的值將很高，這個(gè)值代表了一個(gè)行被索引值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高，因?yàn)樗饕⒉唤?jīng)常使用。

Handler_read_rnd_next 的值高則意味著查詢運(yùn)行低效，并且應(yīng)該建立索引補(bǔ)救。這個(gè)值的含義是在數(shù)據(jù)文件中讀下一行的請(qǐng)求數(shù)。如果你正進(jìn)行大量的表掃描，

該值較高。通常說明表索引不正確或?qū)懭氲牟樵儧]有利用索引。

4. 其他優(yōu)化4.1. 當(dāng)只要一行數(shù)據(jù)時(shí)使用 LIMIT 1

當(dāng)你查詢表的有些時(shí)候，你已經(jīng)知道結(jié)果只會(huì)有一條結(jié)果，但因?yàn)槟阋苍S會(huì)去檢查返回的記錄數(shù)。在這種情況下，加上 LIMIT 1 可以增加性能。這樣一來，MySQL數(shù)據(jù)庫(kù)引擎會(huì)在找到一條數(shù)據(jù)后停止搜索，而不是繼續(xù)往后查少下一條符合記錄的數(shù)據(jù)。

例如：

如果你想在登陸時(shí)驗(yàn)證用戶名密碼是否存在，你可以這樣寫

SELECT 1 FROM jobinfo WHERENAME ='zhangsan' AND PASSWORD = '1234' LIMIT1;

而不是

SELECT * FROM jobinfo WHERE NAME='zhangsan' AND PASSWORD = '1234';

4.2. 為搜索字段建索引

索引并不一定就是給主鍵或是唯一的字段。如果在你的表中，有某個(gè)字段你總要會(huì)經(jīng)常用來做搜索，那么，請(qǐng)為其建立索引吧。

4.3. 避免 SELECT *

從數(shù)據(jù)庫(kù)里讀出越多的數(shù)據(jù)，那么查詢就會(huì)變得越慢。并且，如果你的數(shù)據(jù)庫(kù)服務(wù)器和WEB服務(wù)器是兩臺(tái)獨(dú)立的服務(wù)器的話，這還會(huì)增加網(wǎng)絡(luò)傳輸?shù)呢?fù)載。

4.4. 永遠(yuǎn)為每張表設(shè)置一個(gè)ID

我們應(yīng)該為數(shù)據(jù)庫(kù)里的每張表都設(shè)置一個(gè)ID做為其主鍵，而且最好的是一個(gè)INT型的，并設(shè)置上自動(dòng)增加的AUTO_INCREMENT標(biāo)志。

就算是你 users 表有一個(gè)主鍵叫“email”的字段，你也別讓它成為主鍵。使用 VARCHAR 類型來當(dāng)主鍵會(huì)使用得性能下降。另外，在你的程序中，你應(yīng)該使用表的ID來構(gòu)造你的數(shù)據(jù)結(jié)構(gòu)。

而且，在MySQL數(shù)據(jù)引擎下，還有一些操作需要使用主鍵，在這些情況下，主鍵的性能和設(shè)置變得非常重要，比如，集群，分區(qū)……

4.5. 盡可能的使用 NOT NULL

除非你有一個(gè)很特別的原因去使用 NULL 值，你應(yīng)該總是讓你的字段保持 NOT NULL。

首先，問問你自己“Empty”和“NULL”有多大的區(qū)別（如果是INT，那就是0和NULL）？如果你覺得它們之間沒有什么區(qū)別，那么你就不要使用NULL。

不要以為 NULL 不需要空間，其需要額外的空間，并且，在你進(jìn)行比較的時(shí)候，你的程序會(huì)更復(fù)雜。

4.6. Prepared Statements

Prepared Statements很像存儲(chǔ)過程，是一種運(yùn)行在后臺(tái)的SQL語(yǔ)句集合，我們可以從使用 prepared statements 獲得很多好處，無(wú)論是性能問題還是安全問題。

Prepared Statements 可以檢查一些你綁定好的變量，這樣可以保護(hù)你的程序不會(huì)受到“SQL注入式”攻擊。當(dāng)然，你也可以手動(dòng)地檢查你的這些變量，然而，手動(dòng)的檢查容易出問題，而且很經(jīng)常會(huì)被程序員忘了。

在性能方面，當(dāng)一個(gè)相同的查詢被使用多次的時(shí)候，這會(huì)為你帶來可觀的性能優(yōu)勢(shì)。你可以給這些Prepared Statements定義一些參數(shù)，而MySQL只會(huì)解析一次。

4.7. 把IP地址存成 INT

在java培訓(xùn)過程中很多程序員都會(huì)創(chuàng)建一個(gè) VARCHAR(15) 字段來存放字符串形式的IP而不是整形的IP。如果你用整形來存放，只需要4個(gè)字節(jié)，并且你可以有定長(zhǎng)的字段。而且，這會(huì)為你帶來查詢上的優(yōu)勢(shì)，尤其是當(dāng)你需要使用這樣的WHERE條件：IP between ip1 and ip2。

我們必需要使用NT，因?yàn)?/font> IP地址會(huì)使用整個(gè)32位的無(wú)符號(hào)整形。

而你的查詢，你可以使用 INET_ATON() 來把一個(gè)字符串IP轉(zhuǎn)成一個(gè)整形，并使用 INET_NTOA() 把一個(gè)整形轉(zhuǎn)成一個(gè)字符串IP。

SELECT INET_ATON('192.168.0.1') FROMjobinfo;

SELECT INET_NTOA(3232235521) FROM jobinfo;

4.8. 固定長(zhǎng)度的表會(huì)更快

在Java培訓(xùn)中過程中的一個(gè)真實(shí)的案例，如果表中的所有字段都是“固定長(zhǎng)度”的，整個(gè)表會(huì)被認(rèn)為是“static”或 “fixed-length”。例如，表中沒有如下類型的字段：VARCHAR，TEXT，BLOB。

只要你包括了其中一個(gè)這些字段，那么這個(gè)表就不是“固定長(zhǎng)度靜態(tài)表”了，這樣，MySQL 引擎會(huì)用另一種方法來處理。

固定長(zhǎng)度的表會(huì)提高性能，因?yàn)?/font>MySQL搜尋得會(huì)更快一些，因?yàn)檫@些固定的長(zhǎng)度是很容易計(jì)算下一個(gè)數(shù)據(jù)的偏移量的，所以讀取的自然也會(huì)很快。而如果字段不是定長(zhǎng)的，那么，每一次要找下一條的話，需要程序找到主鍵。

并且，固定長(zhǎng)度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長(zhǎng)度的字段會(huì)浪費(fèi)一些空間，因?yàn)槎ㄩL(zhǎng)的字段無(wú)論你用不用，他都是要分配那么多的空間。

4.9. 垂直分割

“垂直分割”是一種把數(shù)據(jù)庫(kù)中的表按列變成幾張表的方法，這樣可以降低表的復(fù)雜度和字段的數(shù)目，從而達(dá)到優(yōu)化的目的。

示例一：在Users表中有一個(gè)字段是家庭地址，這個(gè)字段是可選字段，相比起，而且你在數(shù)據(jù)庫(kù)操作的時(shí)候除了個(gè)人信息外，你并不需要經(jīng)常讀取或是改寫這個(gè)字段。那么，為什么不把他放到另外一張表中呢？這樣會(huì)讓你的表有更好的性能，大多的時(shí)候，我對(duì)于用戶表來說，只有用戶ID，用戶名，口令，用戶角色等會(huì)被經(jīng)常使用。小一點(diǎn)的表總是會(huì)有好的性能。

示例二：你有一個(gè)叫 “last_login” 的字段，它會(huì)在每次用戶登錄時(shí)被更新。但是，每次更新時(shí)會(huì)導(dǎo)致該表的查詢緩存被清空。所以，你可以把這個(gè)字段放到另一個(gè)表中，這樣就不會(huì)影響你對(duì)用戶ID，用戶名，用戶角色的不停地讀取了，因?yàn)椴樵兙彺鏁?huì)幫你增加很多性能。

另外，你需要注意的是，這些被分出去的字段所形成的表，你不會(huì)經(jīng)常性地去Join他們，不然的話，這樣的性能會(huì)比不分割時(shí)還要差，而且，會(huì)是極數(shù)級(jí)的下降。

4.10. 拆分大的 DELETE 或 INSERT 語(yǔ)句

如果你需要在一個(gè)在線的網(wǎng)站上去執(zhí)行一個(gè)大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個(gè)網(wǎng)站停止響應(yīng)。因?yàn)檫@兩個(gè)操作是會(huì)鎖表的，表一鎖住了，別的操作都進(jìn)不來了。

Apache會(huì)有很多的子進(jìn)程或線程。所以，其工作起來相當(dāng)有效率，而我們的服務(wù)器也不希望有太多的子進(jìn)程，線程和數(shù)據(jù)庫(kù)鏈接，這是極大的占服務(wù)器資源的事情，尤其是內(nèi)存。

如果你把你的表鎖上一段時(shí)間，比如30秒鐘，那么對(duì)于一個(gè)有很高訪問量的站點(diǎn)來說，這30秒所積累的訪問進(jìn)程/線程，數(shù)據(jù)庫(kù)鏈接，打開的文件數(shù)，可能不僅僅會(huì)讓你導(dǎo)致WEB服務(wù)Crash，還可能會(huì)讓你的整臺(tái)服務(wù)器馬上掛了。

所以，如果你有一個(gè)大的處理，你定你一定把其拆分，使用 LIMIT 條件是一個(gè)好的方法。下面是一個(gè)示例：

while (1) {

//每次只做1000條

"DELETEFROM logs WHERE log_date <= '2009-11-01' LIMIT 1000";

if (select 1 FROM logs WHERE log_date <= '2009-11-01' LIMIT 1==0) {

// 沒得可刪了，退出！

break;

}

// 每次都要休息一會(huì)兒

usleep(50000);

}

4.11. 選擇正確的存儲(chǔ)引擎

在 MySQL 中有多個(gè)存儲(chǔ)引擎 MyISAM 和 InnoDB等，每個(gè)引擎都有利有弊。

MyISAM 適合于一些需要大量查詢的應(yīng)用，但其對(duì)于有大量寫操作并不是很好。甚至你只是需要update一個(gè)字段，整個(gè)表都會(huì)被鎖起來，而別的進(jìn)程，就算是讀進(jìn)程都無(wú)法操作直到讀操作完成。另外，MyISAM 對(duì)于SELECT COUNT(*) 這類的計(jì)算是超快無(wú)比的。

InnoDB 的趨勢(shì)會(huì)是一個(gè)非常復(fù)雜的存儲(chǔ)引擎，對(duì)于一些小的應(yīng)用，它會(huì)比 MyISAM 還慢。他是它支持“行鎖”，于是在寫操作比較多的時(shí)候，會(huì)更優(yōu)秀。并且，他還支持更多的高級(jí)應(yīng)用，比如：事務(wù)。

4.12. 越小的列會(huì)越快

對(duì)于大多數(shù)的數(shù)據(jù)庫(kù)引擎來說，硬盤操作可能是最重大的瓶頸。所以，把你的數(shù)據(jù)變得緊湊會(huì)對(duì)這種情況非常有幫助，因?yàn)檫@減少了對(duì)硬盤的訪問。

如果一個(gè)表只會(huì)有幾列罷了（比如說字典表，配置表），那么，我們就沒有理由使用 INT 來做主鍵，使用SMALLINT 或是更小的 TINYINT 會(huì)更經(jīng)濟(jì)一些。

4.13. 使用 ENUM 而不是 VARCHAR

ENUM 類型是非?？旌途o湊的。在實(shí)際上，其保存的是TINYINT，但其外表上顯示為字符串。這樣一來，用這個(gè)字段來做一些選項(xiàng)列表變得相當(dāng)?shù)耐昝馈?/font>

如果你有一個(gè)字段，比如“性別”，“國(guó)家”，“民族”，“狀態(tài)”或“部門”，你知道這些字段的取值是有限而且固定的，那么，你應(yīng)該使用 ENUM 而不是 VARCHAR。

MySQL也有一個(gè)“建議”告訴你怎么去重新組織你的表結(jié)構(gòu)。當(dāng)你有一個(gè) VARCHAR 字段時(shí)，這個(gè)建議會(huì)告訴你把其改成 ENUM 類型。使用 PROCEDURE ANALYSE() 你可以得到相關(guān)的建議。

4.14. 從 PROCEDUREANALYSE() 取得建議

語(yǔ)法：SELECT * FROM student LIMIT 1,1 PROCEDURE ANALYSE(1);

PROCEDURE ANALYSE() 會(huì)讓 MySQL 幫你去分析你的字段和其實(shí)際的數(shù)據(jù)，并會(huì)給你一些有用的建議。只有表中有實(shí)際的數(shù)據(jù)，這些建議才會(huì)變得有用，因?yàn)橐鲆恍┐蟮臎Q定是需要有數(shù)據(jù)作為基礎(chǔ)的。

例如，如果你創(chuàng)建了一個(gè) INT 字段作為你的主鍵，然而并沒有太多的數(shù)據(jù)，那么，PROCEDURE ANALYSE()會(huì)建議你把這個(gè)字段的類型改成 MEDIUMINT 。或是你使用了一個(gè) VARCHAR 字段，因?yàn)閿?shù)據(jù)不多，你可能會(huì)得到一個(gè)讓你把它改成 ENUM 的建議。這些建議，都是可能因?yàn)閿?shù)據(jù)不夠多，所以決策做得就不夠準(zhǔn)。

一定要注意，這些只是建議，只有當(dāng)你的表里的數(shù)據(jù)越來越多時(shí)，這些建議才會(huì)變得準(zhǔn)確。

4.15. SHOW STATUS的其他參數(shù)

通過 SHOW STATUS可以提供服務(wù)器狀態(tài)信息，SHOW STATUS 可以根據(jù)需要顯示 session 級(jí)別的統(tǒng)計(jì)結(jié)果和 global級(jí)別的統(tǒng)計(jì)結(jié)果。

以下幾個(gè)參數(shù)對(duì) Myisam 和 Innodb 存儲(chǔ)引擎都計(jì)數(shù)：

1.Com_select執(zhí)行 select 操作的次數(shù)，一次查詢只累加1；

2.Com_insert 執(zhí)行 insert 操作的次數(shù)，對(duì)于批量插入的insert 操作，只累加一次；

3.Com_update 執(zhí)行 update 操作的次數(shù)；

4.Com_delete執(zhí)行 delete 操作的次數(shù)；

以下幾個(gè)參數(shù)是針對(duì) Innodb 存儲(chǔ)引擎計(jì)數(shù)的，累加的算法也略有不同：

1.Innodb_rows_read 查詢返回的行數(shù)；

2.Innodb_rows_inserted 執(zhí)行 Insert 操作插入的行數(shù)；

3.Innodb_rows_updated 執(zhí)行 update 操作更新的行數(shù)；

4.Innodb_rows_deleted執(zhí)行 delete 操作刪除的行數(shù)；

通過以上幾個(gè)參數(shù)，可以很容易的了解當(dāng)前數(shù)據(jù)庫(kù)的應(yīng)用是以插入更新為主還是以查詢操作為主，以及各種類型的 SQL 大致的執(zhí)行比例是多少。對(duì)于更新操作的計(jì)數(shù)，是對(duì)執(zhí)行次數(shù)的計(jì)數(shù)，不論提交還是回滾都會(huì)累加。

對(duì)于事務(wù)型的應(yīng)用，通過 Com_commit 和 Com_rollback 可以了解事務(wù)提交和回滾的情況，對(duì)于回滾操作非常頻繁的數(shù)據(jù)庫(kù)，可能意味著應(yīng)用編寫存在問題。

此外，以下幾個(gè)參數(shù)便于我們了解數(shù)據(jù)庫(kù)的基本情況：

1.Connections 試圖連接 Mysql 服務(wù)器的次數(shù)

2.Uptime服務(wù)器工作時(shí)間（秒）

3.Slow_queries 慢查詢的次數(shù)

4.16. 定位執(zhí)行效率較低的 SQL 語(yǔ)句

SHOW PROCESSLIST;

命令的輸出結(jié)果顯示了有哪些線程在運(yùn)行，可以幫助識(shí)別出有問題的查詢語(yǔ)句。

如果有 SUPER 權(quán)限，則可以看到全部的線程，否則，只能看到自己發(fā)起的線程（這是指，當(dāng)前對(duì)應(yīng)的 MySQL帳戶運(yùn)行的線程）。

得到數(shù)據(jù)形式如下（只截取了三條）：

mysql> show processlist;

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

|207|root |192.168.0.20:51718 |mytest | Sleep | 5 | | NULL

|208|root |192.168.0.20:51719 |mytest | Sleep | 5 | | NULL

|220|root |192.168.0.20:51731 |mytest |Query |84 |Locked|

select bookname,culture,value,type from book where id=001

id ，不用說了吧，一個(gè)標(biāo)識(shí)，你要 kill 一個(gè)語(yǔ)句的時(shí)候很有用。

user 列，顯示當(dāng)前用戶，如果不是 root ，這個(gè)命令就只顯示你權(quán)限范圍內(nèi)的 sql 語(yǔ)句。 host 列，顯示這個(gè)語(yǔ)句是從哪個(gè) ip 的哪個(gè)端口上發(fā)出的?？梢杂脕碜粉櫝鰡栴}語(yǔ)句的用戶。

db 列，顯示這個(gè)進(jìn)程目前連接的是哪個(gè)數(shù)據(jù)庫(kù) 。

command 列，顯示當(dāng)前連接的執(zhí)行的命令，一般就是休眠（ sleep ），查詢（ query ），連接（ connect）。

time 列，此這個(gè)狀態(tài)持續(xù)的時(shí)間，單位是秒。

state 列，顯示使用當(dāng)前連接的 sql 語(yǔ)句的狀態(tài)，很重要的列，后續(xù)會(huì)有所有的狀態(tài)的描述，請(qǐng)注意， state 只是語(yǔ)句執(zhí)行中的某一個(gè)狀態(tài)，一個(gè) sql 語(yǔ)句，已查詢?yōu)槔?，可能需要?jīng)過 copying to tmp table ， Sorting result ， Sending data 等狀態(tài)才可以完成

info 列，顯示這個(gè) sql 語(yǔ) 句，因?yàn)殚L(zhǎng)度有限，所以長(zhǎng)的 sql 語(yǔ)句就顯示不全，但是一個(gè)判斷問題語(yǔ)句的重要依據(jù)。

這個(gè)命令中最關(guān)鍵的就是 state 列， mysql 列出的狀態(tài)主要有以下幾種：

　Checking table　正在檢查數(shù)據(jù)表（這是自動(dòng)的）。

　Closing tables　正在將表中修改的數(shù)據(jù)刷新到磁盤中，同時(shí)正在關(guān)閉已經(jīng)用完的表。這是一個(gè)很快的操作，如果不是這樣的話，就應(yīng)該確認(rèn)磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負(fù)中。

　 Connect Out　復(fù)制從服務(wù)器正在連接主服務(wù)器。

　 Copying to tmp table on disk　由于臨時(shí)結(jié)果集大于 tmp_table_size ，正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)為磁盤存儲(chǔ)以此節(jié)省內(nèi)存。

　 Creating tmp table　正在創(chuàng)建臨時(shí)表以存放部分查詢結(jié)果。

　 deleting from main table　服務(wù)器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個(gè)表。

　 deleting from reference tables　服務(wù)器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄。

　 Flushing tables　正在執(zhí)行 FLUSH TABLES ，等待其他線程關(guān)閉數(shù)據(jù)表。

　 Killed　發(fā)送了一個(gè) kill 請(qǐng)求給某線程，那么這個(gè)線程將會(huì)檢查 kill 標(biāo)志位，同時(shí)會(huì)放棄下一個(gè) kill 請(qǐng)求。MySQL 會(huì)在每次的主循環(huán)中檢查 kill 標(biāo)志位，不過有些情況下該線程可能會(huì)過一小段才能死掉。如果該線程程被其他線程鎖住了，那么 kill 請(qǐng)求會(huì)在鎖釋放時(shí)馬上生效。

　 Locked　被其他查詢鎖住了。

　 Sending data　正在處理 SELECT 查詢的記錄，同時(shí)正在把結(jié)果發(fā)送給客戶端。

　 Sorting for group　正在為 GROUP BY 做排序。

　 Sorting for order　正在為 ORDER BY 做排序。

　 Opening tables　這個(gè)過程應(yīng)該會(huì)很快，除非受到其他因素的干擾。例如，在執(zhí) ALTER TABLE 或 LOCK TABLE 語(yǔ)句行完以前，數(shù)據(jù)表無(wú)法被其他線程打開。正嘗試打開一個(gè)表。

　 Removing duplicates　正在執(zhí)行一個(gè) SELECT DISTINCT 方式的查詢，但是 MySQL 無(wú)法在前一個(gè)階段優(yōu)化掉那些重復(fù)的記錄。因此， MySQL 需要再次去掉重復(fù)的記錄，然后再把結(jié)果發(fā)送給客戶端。

　 Reopen table　獲得了對(duì)一個(gè)表的鎖，但是必須在表結(jié)構(gòu)修改之后才能獲得這個(gè)鎖。已經(jīng)釋放鎖，關(guān)閉數(shù)據(jù)表，正嘗試重新打開數(shù)據(jù)表。

　 Repair by sorting　修復(fù)指令正在排序以創(chuàng)建索引。

　 Repair with keycache　修復(fù)指令正在利用索引緩存一個(gè)一個(gè)地創(chuàng)建新索引。它會(huì)比 Repair by sorting 慢些。

　 Searching rows for update　正在講符合條件的記錄找出來以備更新。它必須在 UPDATE 要修改相關(guān)的記錄之前就完成了。

　 Sleeping　正在等待客戶端發(fā)送新請(qǐng)求 .

　 System lock　正在等待取得一個(gè)外部的系統(tǒng)鎖。如果當(dāng)前沒有運(yùn)行多個(gè) mysqld 服務(wù)器同時(shí)請(qǐng)求同一個(gè)表，那么可以通過增加 --skip-external-locking 參數(shù)來禁止外部系統(tǒng)鎖。

　 Upgrading lock　 INSERT DELAYED 正在嘗試取得一個(gè)鎖表以插入新記錄。

　 Updating　正在搜索匹配的記錄，并且修改它們。

　 User Lock　正在等待 GET_LOCK() 。

　 Waiting for tables　該線程得到通知，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)。然后，為了能的重新打開數(shù)據(jù)表，必須等到所有其他線程關(guān)閉這個(gè)表。以下幾種情況下會(huì)產(chǎn)生這個(gè)通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE,ANALYZE TABLE, 或 OPTIMIZE TABLE 。

　 waiting for handler insert　 INSERT DELAYED 已經(jīng)處理完了所有待處理的插入操作，正在等待新的請(qǐng)求。

　大部分狀態(tài)對(duì)應(yīng)很快的操作，只要有一個(gè)線程保持同一個(gè)狀態(tài)好幾秒鐘，那么可能是有問題發(fā)生了，需要檢查一下。

　當(dāng)MySQL繁忙的時(shí)候運(yùn)行show processlist，會(huì)發(fā)現(xiàn)有很多行輸出，每行輸出對(duì)應(yīng)一個(gè)MySQL連接。怎么診斷發(fā)起連接的進(jìn)程是哪個(gè)？它當(dāng)前正在干嘛呢？

首先，需要通過TCP Socket而不是Unix Socket連接MySQL，這樣在show processlist的輸出中就會(huì)有來源端口號(hào)。如下，

mysql> show processlist;

+——–+——–+—————–+——+———+——+——-+——————+

+——–+——–+—————–+——+———+——+——-+——————+

+——–+——–+—————–+——+———+——+——-+——————+

在Host列有來源IP和端口號(hào)，然后我們從連接機(jī)器查看端口號(hào)是誰(shuí)打開的，

[root@localhost ~]# netstat -ntp | grep35558

… 124.115.0.68:35558 ESTABLISHED 18783/httpd

可知進(jìn)程18783發(fā)起的MySQL連接來源端口是35558，然后就可以用strace觀察這個(gè)進(jìn)程了。

4.17. 優(yōu)化 group by 語(yǔ)句

默認(rèn)情況下，MySQL 排序所有 GROUP BY col1，col2，....。

查詢的方法如同在查詢中指定 ORDER BY col1，col2，...。

如果顯式包括一個(gè)包含相同的列的 ORDER BY子句，MySQL 可以毫不減速地對(duì)它進(jìn)行優(yōu)化，盡管仍然進(jìn)行排序。

如果查詢包括 GROUP BY 但你想要避免排序結(jié)果的消耗，你可以指定 ORDER BY NULL

禁止排序，出自傳智播客Java培訓(xùn)精點(diǎn)難點(diǎn)知識(shí)總結(jié)中

例如：

SELECT jobName FROM jobinfo GROUP BYjobName ORDER BY NULL;

4.18. 優(yōu)化 order by 語(yǔ)句

1、order by 后的字段，如果要走索引，須與where 條件里的某字段建立復(fù)合索引！！或者說orcer by后的字段如果要走索引排序，它要么與where 條件里的字段建立復(fù)合索引【這里建立復(fù)合索引的時(shí)候，需要注意復(fù)合索引的列順序?yàn)椋?/font>where字段，order by 字段），這樣才能滿足最左列原則，原因可能是order by字段并能算在where 查詢條件中！】，要么它自身要在where 條件里被引用到！

2、表a

id為普通字段,上面建有索引

select * from a order by id (用不上索引)

select id from a order by id (能用上索引)

select * from a where id=XX order byid (能用上索引)

意思是說order by 要避免使用文件系統(tǒng)排序，要么把order by的字段出現(xiàn)在select 后，要么使用order by字段出現(xiàn)在where 條件里，要么把order by字段與where 條件字段建立復(fù)合索引！
Java培訓(xùn)學(xué)院始終認(rèn)為你將會(huì)是最優(yōu)秀的存在，看好你，加油！??！
本文版權(quán)歸傳智播客Java培訓(xùn)學(xué)院所有，歡迎轉(zhuǎn)載，轉(zhuǎn)載請(qǐng)注明作者出處。謝謝！
作者：傳智播客Java培訓(xùn)學(xué)院
首發(fā)：http://xamj520.com/javaee

上一篇：Java培訓(xùn)實(shí)戰(zhàn)教程之論Socket的重要性 下一篇：Java培訓(xùn)實(shí)戰(zhàn)教程之SQL SERVER函數(shù)