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

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

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

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

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

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

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

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

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

1.2. 如何選擇合適的存儲引擎

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

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

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

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

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

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

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

并作為 1 個對象引用它們。對于諸如數(shù)據(jù)倉儲等 VLDB 環(huán)境十分適合

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

注：可以通過order by語句測試緩存，order by語句執(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變量影響其工作方式。這個變量可以設(shè)置為下面的值：

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

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

2或DEMAND：僅對以SELECTSQL_CACHE開始的那些查詢語句啟用緩存。

出自Java培訓(xùn)

另外：

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

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

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

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

查詢上線：

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ù)

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

SHOW VARIABLES LIKE 'have_query_cache';

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

| Variable_name | Value |

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

| have_query_cache | YES |

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

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

RESET QUERY CACHE：語句從查詢緩存中移出所有查詢。FLUSH TABLES語句也執(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)存較少從緩存刪除的查詢數(shù)量。

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

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

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

2.2. 高速緩存語句要求

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

SELECT * FROM tbl_name

Select * from tbl_name

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

2.3. 不緩存的語句

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

BENCHMARK()

CONNECTION_ID()

CURDATE()

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

CURTIME()

DATABASE()

帶一個參數(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. 簡介

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

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

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

EXPLAIN

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

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

參數(shù)解釋：

id：查詢的序號

select_type:select類型，simple表示簡單的查詢

table：引用的表

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

possible_keys：查詢時可以使用的索引

key：查詢時正在使用的索引

key_len：索引的長度

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

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

3.1.1. 參數(shù)詳解

id：這是SELECT的查詢序列號。

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

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

PRIMARY：最外面的SELECT

UNION：UNION中的第二個或后面的SELECT語句

DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢

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

SUBQUERY:子查詢中的第一個SELECT

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

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

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

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

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

const表最多有一個匹配行，它將在查詢開始時被讀取。

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

ref:前面的表加了索引。

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

ALL：全表掃描。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

IGNORE INDEX

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

為 Hint。

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

FORCE INDEX

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

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

3.4. 不使用索引的情況

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

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.對 where 后邊條件為字符串的一定要加引號，字符串如果為數(shù)字 mysql 會自動轉(zhuǎn)為

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

3.5. 查看索引使用情況

語法：

mysql> show status like 'Handler_read%';

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

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

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

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

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

例如：

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

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

而不是

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

4.2. 為搜索字段建索引

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

4.3. 避免 SELECT *

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

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

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

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

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

4.5. 盡可能的使用 NOT NULL

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

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

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

4.6. Prepared Statements

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

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

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

4.7. 把IP地址存成 INT

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

我們必需要使用NT，因?yàn)?/font> IP地址會使用整個32位的無符號整形。

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

SELECT INET_ATON('192.168.0.1') FROMjobinfo;

SELECT INET_NTOA(3232235521) FROM jobinfo;

4.8. 固定長度的表會更快

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

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

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

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

4.9. 垂直分割

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

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

示例二：你有一個叫 “last_login” 的字段，它會在每次用戶登錄時被更新。但是，每次更新時會導(dǎo)致該表的查詢緩存被清空。所以，你可以把這個字段放到另一個表中，這樣就不會影響你對用戶ID，用戶名，用戶角色的不停地讀取了，因?yàn)椴樵兙彺鏁湍阍黾雍芏嘈阅堋?/font>

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

4.10. 拆分大的 DELETE 或 INSERT 語句

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

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

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

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

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;

}

// 每次都要休息一會兒

usleep(50000);

}

4.11. 選擇正確的存儲引擎

在 MySQL 中有多個存儲引擎 MyISAM 和 InnoDB等，每個引擎都有利有弊。

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

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

4.12. 越小的列會越快

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

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

4.13. 使用 ENUM 而不是 VARCHAR

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

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

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

4.14. 從 PROCEDUREANALYSE() 取得建議

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

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

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

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

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

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

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

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

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

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

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

以下幾個參數(shù)是針對 Innodb 存儲引擎計(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ù)；

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

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

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

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

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

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

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

SHOW PROCESSLIST;

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

如果有 SUPER 權(quán)限，則可以看到全部的線程，否則，只能看到自己發(fā)起的線程（這是指，當(dāng)前對應(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 ，不用說了吧，一個標(biāo)識，你要 kill 一個語句的時候很有用。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

　 Locked　被其他查詢鎖住了。

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

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

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

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

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

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

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

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

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

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

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

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

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

　 User Lock　正在等待 GET_LOCK() 。

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

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

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

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

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

mysql> show processlist;

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

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

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

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

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

… 124.115.0.68:35558 ESTABLISHED 18783/httpd

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

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

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

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

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

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

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

例如：

SELECT jobName FROM jobinfo GROUP BYjobName ORDER BY NULL;

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

1、order by 后的字段，如果要走索引，須與where 條件里的某字段建立復(fù)合索引?。』蛘哒forcer by后的字段如果要走索引排序，它要么與where 條件里的字段建立復(fù)合索引【這里建立復(fù)合索引的時候，需要注意復(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)為你將會是最優(yōu)秀的存在，看好你，加油?。。?br /> 本文版權(quán)歸傳智播客Java培訓(xùn)學(xué)院所有，歡迎轉(zhuǎn)載，轉(zhuǎn)載請注明作者出處。謝謝！
作者：傳智播客Java培訓(xùn)學(xué)院
首發(fā)：http://m.fskzgqt.cn/javaee

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