MySQL 數(shù)據(jù)庫優(yōu)化的具體方法說明
更新時間:2010年05月20日 01:37:53 作者:
以下的文章主要講述的是實現(xiàn)MySQL數(shù)據(jù)庫簡單實用優(yōu)化的具體方法,以及在實際操作中有哪些具體操作步驟是值得我們大家注意的。
以下的文章主要描述的是MySQL數(shù)據(jù)庫簡單實用優(yōu)化的具體方法的實現(xiàn),中包括如何定期的表進(jìn)行分析與檢查, 以及如何正確對表進(jìn)行定期的優(yōu)化,以下就是具體方案的描述,希望在你今后的學(xué)習(xí)中會有所幫助。
1、定期分析表和檢查表
分析表的語法如下:
以上語句用于分析和存儲表的關(guān)鍵字分布,分析的結(jié)果將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計信息,使得SQL能夠生成正確的執(zhí)行計劃。如果用戶感覺實際執(zhí)行計劃并不是預(yù)期的執(zhí)行計劃,執(zhí)行一次分析表可能會解決問題。在分析期間,使用一個讀取鎖定對表進(jìn)行鎖定。這對于MyISAM,DBD和InnoDB表有作用。
例如分析一個數(shù)據(jù)表
檢查表的語法如下:
檢查表的作用是檢查一個或多個表是否有錯誤,CHECK TABLE 對MyISAM 和 InnoDB表有作用,對于MyISAM表,關(guān)鍵字統(tǒng)計數(shù)據(jù)被更新
CHECK TABLE 也可以檢查視圖是否有錯誤,比如在視圖定義中被引用的表不存在。
2. 定期優(yōu)化表
優(yōu)化表的語法如下:
如果刪除了表的一大部分,或者如果已經(jīng)對含有可變長度行的表(含有 VARCHAR、BLOB或TEXT列的表)進(jìn)行更多更改,則應(yīng)使用OPTIMIZE TABLE命令來進(jìn)行表優(yōu)化。這個命令可以將表中的空間碎片進(jìn)行合并,并且可以消除由于刪除或者更新造成的空間浪費,但OPTIMIZE TABLE 命令只對MyISAM、 BDB 和InnoDB表起作用。
例如: optimize table table_name
注意: analyze、check、optimize執(zhí)行期間將對表進(jìn)行鎖定,因此一定注意要在MySQL數(shù)據(jù)庫不繁忙的時候執(zhí)行相關(guān)的操作。
常用的SQL優(yōu)化
我們在開發(fā)的時候常常用到的SQL語句,無非是INSERT、GROUPBY等等。對于這些SQL語句,我們怎么進(jìn)行優(yōu)化?
1. 大批量插入數(shù)據(jù)
當(dāng)用load命令導(dǎo)入數(shù)據(jù)的時候,適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的速度。
對于MyISAM存儲引擎的表,可以通過如下方式快速的導(dǎo)入大量的數(shù)據(jù)
ALTER TABLE tb1_name DISABLE KEYS;
loading the data
ALTER TABLE tb1_name ENABLE KEYS;
DISABLE KEYS 和 ENABLE KEYS 用來打開或者關(guān)閉MyISAM表非唯一索引的更新。在導(dǎo)入大量的數(shù)據(jù)到一個非空的MyISAM表時,通過設(shè)置這兩個命令,可以提高導(dǎo)入的效率。
對于導(dǎo)入大量的數(shù)據(jù)到一個空的MyISAM表時,默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的,索引不用進(jìn)行設(shè)置。
對于InnoDB類型的表,這種方式不能提高導(dǎo)入數(shù)據(jù)的效率,但也有幾種針對InnoDB類型的表進(jìn)行優(yōu)化的方式。
1. 因為InnoDB類型的表式按照主鍵的順序保存的,所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排序,可以有效提高導(dǎo)入數(shù)據(jù)的效率。
2. 在導(dǎo)入數(shù)據(jù)前執(zhí)行 SET UNIQUE_CHECKS=0,關(guān)閉唯一性校驗,在導(dǎo)入結(jié)束后執(zhí)行SET UNIQUE_CHECKS=1,恢復(fù)唯一性校驗,可以提高導(dǎo)入的效率。
3. 如果應(yīng)用使用自動提交的方式,建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0,關(guān)閉自動提交,導(dǎo)入結(jié)束后執(zhí)行SET AUTOCOMMIT=1,打開自動提交,也可以提高導(dǎo)入效率。
優(yōu)化INSERT語句
當(dāng)進(jìn)行數(shù)據(jù)INSERT的時候,可以考慮采用以下幾種方式進(jìn)行優(yōu)化
1. 如果同時從一個客戶插入很多行,盡量使用多個值表的INSERT語句,這種方式將大大縮短客戶端與MySQL數(shù)據(jù)庫的鏈接、關(guān)閉等消耗,使得效率比分開執(zhí)行的單個INSERT語句快.
例如:
insert into test values(1,2)
insert into test values(3,4)
insert into test values(5,6)
將上面三句改為:insert into test values(1,2),(3,4),(5,6)......
2. 如果從不同客戶插入很多行,能通過使用INSERT DELAYED 語句得到更高的速度。
DELAYED 的含義是讓INSERT 語句馬上執(zhí)行,其實數(shù)據(jù)都被放在內(nèi)存的隊列中,并沒有真正寫入磁盤,這比每條語句分別插入要快得多;LOW_PRIORITY剛好相反,在所有其他用戶對表的讀寫完后才進(jìn)行插入。
3. 將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放
4. 如果進(jìn)行批量插入,可以增加bulk_insert_buffer_size變量值的方法來提高速度,但是,這只能對于MyISAM表使用。
5. 當(dāng)從一個文本文件中裝載一個表時,使用LOAD DATA INFILE。 這通常比使用很多insert語句快20倍左右。
下面是mysql 性能優(yōu)化的一些其它需要注意的地方,大家結(jié)合和,充分發(fā)揮mysql的性能。
mysql數(shù)據(jù)庫查詢優(yōu)化
MySQL優(yōu)化全攻略-相關(guān)數(shù)據(jù)庫命令
mysql 數(shù)據(jù)庫中my.ini的優(yōu)化 2G內(nèi)存針對站多 抗壓型的設(shè)置
1、定期分析表和檢查表
分析表的語法如下:
復(fù)制代碼 代碼如下:
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]...
以上語句用于分析和存儲表的關(guān)鍵字分布,分析的結(jié)果將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計信息,使得SQL能夠生成正確的執(zhí)行計劃。如果用戶感覺實際執(zhí)行計劃并不是預(yù)期的執(zhí)行計劃,執(zhí)行一次分析表可能會解決問題。在分析期間,使用一個讀取鎖定對表進(jìn)行鎖定。這對于MyISAM,DBD和InnoDB表有作用。
例如分析一個數(shù)據(jù)表
復(fù)制代碼 代碼如下:
analyze table table_name
檢查表的語法如下:
復(fù)制代碼 代碼如下:
CHECK TABLE tb1_name[,tbl_name]...[option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
檢查表的作用是檢查一個或多個表是否有錯誤,CHECK TABLE 對MyISAM 和 InnoDB表有作用,對于MyISAM表,關(guān)鍵字統(tǒng)計數(shù)據(jù)被更新
CHECK TABLE 也可以檢查視圖是否有錯誤,比如在視圖定義中被引用的表不存在。
2. 定期優(yōu)化表
優(yōu)化表的語法如下:
復(fù)制代碼 代碼如下:
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]...
如果刪除了表的一大部分,或者如果已經(jīng)對含有可變長度行的表(含有 VARCHAR、BLOB或TEXT列的表)進(jìn)行更多更改,則應(yīng)使用OPTIMIZE TABLE命令來進(jìn)行表優(yōu)化。這個命令可以將表中的空間碎片進(jìn)行合并,并且可以消除由于刪除或者更新造成的空間浪費,但OPTIMIZE TABLE 命令只對MyISAM、 BDB 和InnoDB表起作用。
例如: optimize table table_name
注意: analyze、check、optimize執(zhí)行期間將對表進(jìn)行鎖定,因此一定注意要在MySQL數(shù)據(jù)庫不繁忙的時候執(zhí)行相關(guān)的操作。
常用的SQL優(yōu)化
我們在開發(fā)的時候常常用到的SQL語句,無非是INSERT、GROUPBY等等。對于這些SQL語句,我們怎么進(jìn)行優(yōu)化?
1. 大批量插入數(shù)據(jù)
當(dāng)用load命令導(dǎo)入數(shù)據(jù)的時候,適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入的速度。
對于MyISAM存儲引擎的表,可以通過如下方式快速的導(dǎo)入大量的數(shù)據(jù)
復(fù)制代碼 代碼如下:
ALTER TABLE tb1_name DISABLE KEYS;
loading the data
ALTER TABLE tb1_name ENABLE KEYS;
DISABLE KEYS 和 ENABLE KEYS 用來打開或者關(guān)閉MyISAM表非唯一索引的更新。在導(dǎo)入大量的數(shù)據(jù)到一個非空的MyISAM表時,通過設(shè)置這兩個命令,可以提高導(dǎo)入的效率。
對于導(dǎo)入大量的數(shù)據(jù)到一個空的MyISAM表時,默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的,索引不用進(jìn)行設(shè)置。
復(fù)制代碼 代碼如下:
load data infile '/home/mysql/text_txt' into table text
對于InnoDB類型的表,這種方式不能提高導(dǎo)入數(shù)據(jù)的效率,但也有幾種針對InnoDB類型的表進(jìn)行優(yōu)化的方式。
1. 因為InnoDB類型的表式按照主鍵的順序保存的,所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排序,可以有效提高導(dǎo)入數(shù)據(jù)的效率。
2. 在導(dǎo)入數(shù)據(jù)前執(zhí)行 SET UNIQUE_CHECKS=0,關(guān)閉唯一性校驗,在導(dǎo)入結(jié)束后執(zhí)行SET UNIQUE_CHECKS=1,恢復(fù)唯一性校驗,可以提高導(dǎo)入的效率。
3. 如果應(yīng)用使用自動提交的方式,建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0,關(guān)閉自動提交,導(dǎo)入結(jié)束后執(zhí)行SET AUTOCOMMIT=1,打開自動提交,也可以提高導(dǎo)入效率。
優(yōu)化INSERT語句
當(dāng)進(jìn)行數(shù)據(jù)INSERT的時候,可以考慮采用以下幾種方式進(jìn)行優(yōu)化
1. 如果同時從一個客戶插入很多行,盡量使用多個值表的INSERT語句,這種方式將大大縮短客戶端與MySQL數(shù)據(jù)庫的鏈接、關(guān)閉等消耗,使得效率比分開執(zhí)行的單個INSERT語句快.
例如:
復(fù)制代碼 代碼如下:
insert into test values(1,2)
insert into test values(3,4)
insert into test values(5,6)
將上面三句改為:insert into test values(1,2),(3,4),(5,6)......
2. 如果從不同客戶插入很多行,能通過使用INSERT DELAYED 語句得到更高的速度。
DELAYED 的含義是讓INSERT 語句馬上執(zhí)行,其實數(shù)據(jù)都被放在內(nèi)存的隊列中,并沒有真正寫入磁盤,這比每條語句分別插入要快得多;LOW_PRIORITY剛好相反,在所有其他用戶對表的讀寫完后才進(jìn)行插入。
3. 將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放
4. 如果進(jìn)行批量插入,可以增加bulk_insert_buffer_size變量值的方法來提高速度,但是,這只能對于MyISAM表使用。
5. 當(dāng)從一個文本文件中裝載一個表時,使用LOAD DATA INFILE。 這通常比使用很多insert語句快20倍左右。
下面是mysql 性能優(yōu)化的一些其它需要注意的地方,大家結(jié)合和,充分發(fā)揮mysql的性能。
mysql數(shù)據(jù)庫查詢優(yōu)化
MySQL優(yōu)化全攻略-相關(guān)數(shù)據(jù)庫命令
mysql 數(shù)據(jù)庫中my.ini的優(yōu)化 2G內(nèi)存針對站多 抗壓型的設(shè)置
相關(guān)文章
mysql數(shù)據(jù)庫無法被其他ip訪問的解決方法
這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫無法被其他ip訪問的解決方法,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧。2017-09-09
MySQL 自動備份與數(shù)據(jù)庫被破壞后的恢復(fù)方法
當(dāng)數(shù)據(jù)庫服務(wù)器建立好以后,我們首先要做的不是考慮要在這個支持?jǐn)?shù)據(jù)庫的服務(wù)器運行哪些受MySQL提攜的程序,而是當(dāng)數(shù)據(jù)庫遭到破壞后,怎樣安然恢復(fù)到最后一次正常的狀態(tài),使得數(shù)據(jù)的損失達(dá)到最小。2010-03-03
解決mysql與navicat建立連接出現(xiàn)1251錯誤
在本篇文章里小編給大家整理了一篇關(guān)于mysql與navicat建立連接出現(xiàn)1251錯誤怎么解決的技術(shù)文章,需要的朋友們參考下。2019-08-08
Mysql 根據(jù)一個表數(shù)據(jù)更新另一個表的某些字段(sql語句)
這篇文章主要介紹了Mysql 根據(jù)一個表數(shù)據(jù)更新另一個表的某些字段,本文給出了sql語句,感興趣的朋友可以跟隨腳本之家小編一起學(xué)習(xí)吧2018-05-05
MySQL里實現(xiàn)類似SPLIT的分割字符串的函數(shù)
SQL對字符串的處理能力比較弱,比如我要循環(huán)遍歷象1,2,3,4,5這樣的字符串,如果用數(shù)組的話,遍歷很簡單,但是T-SQL不支持?jǐn)?shù)組,所以處理下來比較麻煩2012-09-09

