MySQL效能優化指南
本文的主要目標是介紹如何優化MySQL資料庫,通過定期分析表和檢查表、定期優化表以及一些常用SQL語句的優化手段,可以幫助你學會在MySQL開發中編寫更為高效的SQL。
1、定期分析表和檢查表
分析表的語法如下:
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]...
以上語句用於分析和儲存表的關鍵字分佈,分析的結果將可以使得系統得到準確的統計資訊,使得SQL能夠生成正確的執行計劃。如果使用者感覺實際執行計劃並不是預期的執行計劃,執行一次分析表可能會解決問題。在分析期間,使用一個讀取鎖定對錶進行鎖定。這對於MyISAM,DBD和InnoDB表有作用。
例如分析一個資料表
analyze table table_name
檢查表的語法如下:
CHECK TABLE tb1_name[,tbl_name]...[option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
檢查表的作用是檢查一個或多個表是否有錯誤,CHECK TABLE 對MyISAM 和 InnoDB表有作用,對於MyISAM表,關鍵字統計資料被更新
CHECK TABLE 也可以檢查檢視是否有錯誤,比如在檢視定義中被引用的表不存在。
2. 定期優化表
優化表的語法如下:
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]...
如果刪除了表的一大部分,或者如果已經對含有可變長度行的表(含有 VARCHAR、BLOB或TEXT列的表)進行更多更改,則應使用OPTIMIZE TABLE命令來進行表優化。這個命令可以將表中的空間碎片進行合併,並且可以消除由於刪除或者更新造成的空間浪費,但OPTIMIZE TABLE 命令只對MyISAM、 BDB 和InnoDB表起作用。
例如: optimize table table_name
注意: analyze、check、optimize執行期間將對錶進行鎖定,因此一定注意要在MySQL資料庫不繁忙的時候執行相關的操作。
常用的SQL優化
我們在開發的時候常常用到的SQL語句,無非是INSERT、GROUPBY等等。對於這些SQL語句,我們怎麼進行優化?
1. 大批量插入資料
- 當用load命令匯入資料的時候,適當的設定可以提高匯入的速度。
- 對於MyISAM儲存引擎的表,可以通過如下方式快速的匯入大量的資料
程式碼如下:
ALTER TABLE tb1_name DISABLE KEYS; loading the data ALTER TABLE tb1_name ENABLE KEYS;
DISABLE KEYS 和 ENABLE KEYS 用來開啟或者關閉MyISAM表非唯一索引的更新。在匯入大量的資料到一個非空的MyISAM表時,通過設定這兩個命令,可以提高匯入的效率。
對於匯入大量的資料到一個空的MyISAM表時,預設就是先匯入資料然後才建立索引的,索引不用進行設定。
load data infile '/home/mysql/text_txt' into table text
對於InnoDB型別的表,這種方式不能提高匯入資料的效率,但也有幾種針對InnoDB型別的表進行優化的方式。
1. 因為InnoDB型別的表式按照主鍵的順序儲存的,所以將匯入的資料按照主鍵的順序排序,可以有效提高匯入資料的效率。
2. 在匯入資料前執行 SET UNIQUE_CHECKS=0,關閉唯一性校驗,在匯入結束後執行SET UNIQUE_CHECKS=1,恢復唯一性校驗,可以提高匯入的效率。
3. 如果應用使用自動提交的方式,建議在匯入前執行SET AUTOCOMMIT=0,關閉自動提交,匯入結束後執行SET AUTOCOMMIT=1,開啟自動提交,也可以提高匯入效率。
優化INSERT語句
當進行資料INSERT的時候,可以考慮採用以下幾種方式進行優化
1. 如果同時從一個客戶插入很多行,儘量使用多個值表的INSERT語句,這種方式將大大縮短客戶端與MySQL資料庫的連結、關閉等消耗,使得效率比分開執行的單個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 語句馬上執行,其實資料都被放在記憶體的佇列中,並沒有真正寫入磁碟,這比每條語句分別插入要快得多;LOW_PRIORITY剛好相反,在所有其他使用者對錶的讀寫完後才進行插入。
3. 將索引檔案和資料檔案分在不同的磁碟上存放
4. 如果進行批量插入,可以增加bulk_insert_buffer_size變數值的方法來提高速度,但是,這隻能對於MyISAM表使用。
5. 當從一個文字檔案中裝載一個表時,使用LOAD DATA INFILE。 這通常比使用很多insert語句快20倍左右。
以上是MySQL 效能優化的一些其它需要注意的地方,大家結合和,充分發揮mysql的效能。
相關文章
- MySQL分頁效能優化指南MySql優化
- MySQL資料庫的效能優化指南MySql資料庫優化
- 一份平民化的MySQL效能優化指南MySql優化
- 前端效能優化指南前端優化
- MongoDB效能優化指南MongoDB優化
- mysql效能優化MySql優化
- MySQL——效能優化MySql優化
- MySQL 效能優化之索引優化MySql優化索引
- MySQL 效能優化之SQL優化MySql優化
- MySQL 效能優化方案MySql優化
- Mysql效能優化一MySql優化
- MySQL系列:效能優化MySql優化
- MySQL 效能優化技巧MySql優化
- Linux CPU 效能優化指南Linux優化
- Vue 應用效能優化指南Vue優化
- PHP 應用效能優化指南PHP優化
- Angular應用效能優化指南Angular優化
- MySQL8.0效能優化MySql優化
- MySQL高效能優化MySql優化
- MySQL效能優化小結MySql優化
- MySQL效能優化實戰MySql優化
- 4.MySQL效能優化MySql優化
- MySQL5:效能優化MySql優化
- React 16 載入效能優化指南React優化
- Spark效能優化指南:高階篇Spark優化
- Spark效能優化指南:基礎篇Spark優化
- MySQL-效能優化-索引和查詢優化MySql優化索引
- MySQL 效能優化之快取引數優化MySql優化快取
- 效能優化指南:效能優化的一般性原則與方法優化
- 【MySQL】效能優化之 order by (一)MySql優化
- 如何優化MySQL insert效能優化MySql
- MYSQL 大資料效能優化MySql大資料優化
- Vue 專案效能優化 — 實踐指南Vue優化
- Oracle效能優化求生指南讀後感Oracle優化
- 「MySQL」高效能索引優化策略MySql索引優化
- MySQL 效能優化——B+Tree 索引MySql優化索引
- MySQL的SQL效能優化總結MySql優化
- MySQL效能優化之索引設計MySql優化索引