當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

java中mysql的优化,Java培训实战教程之mysql优化

發(fā)布時間：2023/12/20 数据库 16 豆豆

生活随笔收集整理的這篇文章主要介紹了 java中mysql的优化,Java培训实战教程之mysql优化小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

Java培訓(xù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表只實際對應(yīng)一個磁盤文件。MEMORY類型的表訪問非常得快，因為它的數(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)用特點選擇合適的存儲引擎,對于復(fù)雜的應(yīng)用系統(tǒng)可以根據(jù)實際情況選擇

多種存儲引擎進(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í)行計劃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í)行計劃：

參數(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實際決定使用的索引。如果沒有選擇索引，鍵是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)一步搜索讀取實際的行來檢索表中的列信息。當(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?估計使用索引比全表掃描更慢，則不使用索引。例如：如果?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ù)，很低的值表明增加索引得到的性能改善不高，因為索引并不經(jīng)常使用。

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

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

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

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

例如：

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

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ù)器是兩臺獨立的服務(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很像存儲過程，是一種運行在后臺的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，因為?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)中過程中的一個真實的案例，如果表中的所有字段都是“固定長度”的，整個表會被認(rèn)為是“static”或?“fixed-length”。?例如，表中沒有如下類型的字段：VARCHAR，TEXT，BLOB。

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

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

并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要分配那么多的空間。

4.9.??垂直分割

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

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

示例二：你有一個叫?“l(fā)ast_login”?的字段，它會在每次用戶登錄時被更新。但是，每次更新時會導(dǎo)致該表的查詢緩存被清空。所以，你可以把這個字段放到另一個表中，這樣就不會影響你對用戶ID，用戶名，用戶角色的不停地讀取了，因為查詢緩存會幫你增加很多性能。

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

4.10.? ??拆分大的?DELETE?或?INSERT?語句

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

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

如果你把你的表鎖上一段時間，比如30秒鐘，那么對于一個有很高訪問量的站點來說，這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(*)?這類的計算是超快無比的。

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

4.12.? ??越小的列會越快

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

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

4.13.? ??使用?ENUM?而不是?VARCHAR

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

如果你有一個字段，比如“性別”，“國家”，“民族”，“狀態(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ù)據(jù)，并會給你一些有用的建議。只有表中有實際的數(shù)據(jù)，這些建議才會變得有用，因為要做一些大的決定是需要有數(shù)據(jù)作為基礎(chǔ)的。

例如，如果你創(chuàng)建了一個?INT?字段作為你的主鍵，然而并沒有太多的數(shù)據(jù)，那么，PROCEDURE ANALYSE()會建議你把這個字段的類型改成?MEDIUMINT?。或是你使用了一個?VARCHAR?字段，因為數(shù)據(jù)不多，你可能會得到一個讓你把它改成?ENUM?的建議。這些建議，都是可能因為數(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)計結(jié)果和?global級別的統(tǒng)計結(jié)果。

以下幾個參數(shù)對?Myisam?和?Innodb?存儲引擎都計數(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?存儲引擎計數(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í)行比例是多少。對于更新操作的計數(shù)，是對執(zhí)行次數(shù)的計數(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é)果顯示了有哪些線程在運行，可以幫助識別出有問題的查詢語句。

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

得到數(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)槔?#xff0c;可能需要經(jīng)過?copying to tmp table?，?Sorting result?，?Sending data?等狀態(tài)才可以完成

info?列，顯示這個?sql?語?句，因為長度有限，所以長的?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)前沒有運行多個?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繁忙的時候運行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)精點難點知識總結(jié)中

例如：

SELECT jobName FROM jobinfo GROUP BYjobName ORDER BY NULL;

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

1、order by?后的字段，如果要走索引，須與where?條件里的某字段建立復(fù)合索引！！或者說orcer by后的字段如果要走索引排序，它要么與where?條件里的字段建立復(fù)合索引【這里建立復(fù)合索引的時候，需要注意復(fù)合索引的列順序為(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)秀的存在，看好你，加油！！！

本文版權(quán)歸傳智播客Java培訓(xùn)學(xué)院所有，歡迎轉(zhuǎn)載，轉(zhuǎn)載請注明作者出處。謝謝！

作者：傳智播客Java培訓(xùn)學(xué)院

首發(fā)：http://www.itcast.cn/javaee

總結(jié)

以上是生活随笔為你收集整理的java中mysql的优化,Java培训实战教程之mysql优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。