MySQL機能優化。本站提示廣大學習愛好者：（MySQL機能優化）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL機能優化正文
1. 簡介
在Web運用法式系統架構中，數據耐久層（平日是一個關系數據庫）是症結的焦點部門，它對體系的機能有異常主要的影響。MySQL是今朝應用最多的開源數據庫，然則MySQL數據庫的默許設置機能異常的差，僅僅是一個玩具數據庫。是以在產物中應用MySQL數據庫必需停止需要的優化。
優化是一個龐雜的義務，本文描寫MySQL相干的數據庫設計和查詢優化，辦事器端優化，存儲引擎優化。
2. 數據庫設計和查詢優化
在MySQL Server機能調優中，起首要斟酌的就是Database Schema設計，這一點長短常主要的。一個蹩腳的Schema設計即便在機能調優的MySQL Server上運轉，也會表示出很差的機能；和Schema類似，查詢語句的設計也會影響MySQL的機能，應當防止寫出低效的SQL查詢。這一節將具體評論辯論這兩方面的優化。
2.1 Schema Design
Schema的優化取決於將要運轉甚麼樣的query，分歧的query會有分歧的Schema優化計劃。2.2節將引見Query Design的優化。Schema設計異樣遭到預期數據集年夜小的影響。Schema設計時重要斟酌：尺度化，數據類型，索引。
2.1.1 尺度化
尺度化是在數據庫中組織數據的進程。個中包含，依據設計規矩創立表並在這些表間樹立關系；經由過程撤消冗余度與紛歧致相干性，該設計規矩可以同時掩護數據並進步數據的靈巧性。平日數據庫尺度化是讓數據庫設計相符某一級其余范式，平日知足第三范式便可。也有第四范式（也稱為 Boyce Codd范式，BCNF)）與第五范式存在，然則在現實設計中很少斟酌。疏忽這些規矩能夠使得數據庫的設計不太完善，但這不該影響功效。
尺度化的特色：
1) 一切的“對象”都在它本身的table中，沒有冗余。
2) 數據庫平日由E-R圖生成。
3) 簡練，更新屬性平日只須要更新很少的記載。
4) Join操作比擬耗時。
5) Select，sort優化辦法比擬少。
6) 實用於OLTP運用。
非尺度化的特色：
1) 在一張表中存儲許多數據，數據冗余。
2) 更新數據開支很年夜，更新一個屬性能夠會更新許多表，許多記載。
3) 在刪除數據是有能夠喪失數據。
4) Select，order有許多優化的選擇。
5) 實用於DSS運用。
尺度化和非尺度化都有各自的優缺陷，平日在一個數據庫設計中可以混雜應用，一部門表格尺度化，一部門表格保存一些冗余數據：
1) 對OLTP應用尺度化，對DSS應用非尺度化
2) 應用物化視圖。MySQL不直接支撐該數據庫特征，然則可以用MyISAM表取代。
3) 冗余一些數據在表格中，例如將ref_id和name存在統一張表中。然則要留意更新成績。
4) 關於一些簡略的對象，直接應用value作為建。例如IP address等
5) Reference by PRIMARY/UNIQUE KEY。MySQL可以優化這類操作，例如：
java 代碼
select city_name
from city,state
where state_id=state.id and state.code=‘CA'” converted to “select city_name from city where state_id=12
2.1.2 數據類型
最根本的優化之一就是使表在磁盤上占領的空間盡量小。這能帶來機能異常年夜的晉升，由於數據小，磁盤讀入較快，而且在查詢進程中表內容被處置所占用的內存更少。同時，在更小的列上建索引，索引也會占用更少的資本。
可使用上面的技巧可使表的機能更好而且使存儲空間最小：
1) 應用准確適合的類型，不要將數字存儲為字符串。
2) 盡量地應用最有用(最小)的數據類型。MySQL有許多節儉磁盤空間和內存的專業化類型。
3) 盡量應用較小的整數類型使表更小。例如，MEDIUMINT常常比INT好一些，由於MEDIUMINT列應用的空間要少25%。
4) 假如能夠，聲明列為NOT NULL。它使任何工作更快並且每列可以節儉一名。留意假如在運用法式中確切須要NULL，應當毫無疑問應用它，只是防止 默許地在一切列上有它。
5) 關於MyISAM表，假如沒有任何變長列(VARCHAR、TEXT或BLOB列)，應用固定尺寸的記載格局。這比擬快然則不幸地能夠會糟蹋一些空間。即便你曾經用CREATE選項讓VARCHAR列ROW_FORMAT=fixed，也能夠提醒想應用固定長度的行。
6) 應用sample character set，例如latin1。盡可能少應用utf-8，由於utf-8占用的空間是latin1的3倍。可以在不須要應用utf-8的字段下面應用latin1，例如mail，url等。
2.1.3 索引
一切MySQL列類型可以被索引。對相干列應用索引是進步SELECT操作機能的最好門路。應用索引應當留意以下幾點：
1) MySQL只會應用前綴，例如key(a, b) …where b=5 將應用不到索引。
2) 要選擇性的應用索引。在變更很少的列上應用索引其實不是很好，例如性別列。
3) 在Unique列上界說Unique index。
4) 防止樹立應用不到的索引。
5) 在Btree index中（InnoDB應用Btree），可以在須要排序的列上樹立索引。
6) 防止反復的索引。
7) 防止在已有索引的前綴上樹立索引。例如：假如存在index（a，b）則去失落index（a）。
8) 掌握單個索引的長度。應用key（name（8））在數據的後面幾個字符樹立索引。
9) 越是短的鍵值越好，最好應用integer。
10) 在查詢中要應用到索引（應用explain檢查），可以削減讀磁盤的次數，加快讀取數據。
11) 鄰近的鍵值比隨機好。Auto_increment就比uuid好。
12) Optimize table可以緊縮和排序index，留意不要頻仍運轉。
13) Analyze table可以更新數據。
2.2 Designing queries
查詢語句的優化是一個Case by case的成績，分歧的sql有分歧的優化計劃，在這裡我只列出一些通用的技能。
1) 在有index的情形下，盡可能包管查詢應用了准確的index。可使用EXPLAIN select …檢查成果，剖析查詢。
2) 查詢時應用婚配的類型。例如select * from a where id=5， 假如這裡id是字符類型，同時有index，這條查詢則應用不到index，會做全表掃描，速度會很慢。准確的應當是 … where id=”5” ，加上引號注解類型是字符。
3) 應用--log-slow-queries –long-query-time=2檢查查詢比擬慢的語句。然後應用explain剖析查詢，做出優化。
3. 辦事器端優化
3.1 MySQL裝置
MySQL有許多刊行版本，最好應用MySQL AB宣布的二進制版本。也能夠下載源代碼停止編譯裝置，然則編譯器和類庫的一些bug能夠會使編譯完成的MySQL存在潛伏的成績。
假如裝置MySQL的辦事器應用的是Intel公司的處置器，可使用intel c++編譯的版本，在Linux World2005的一篇PPT中提到，應用intel C++編譯器編譯的MySQL查詢速度比正常版本快30%閣下。Intel c++編譯版本可以在MySQL官方網站下載。
3.2 辦事器設置優化
MySQL默許的設置機能很差，所以要做一些參數的調劑。這一節引見一些通用的參數調劑，不觸及詳細的存儲引擎（重要指MyISAM，InnoDB，相干優化在4中引見）。
--character-set：假如是單一說話應用簡略的character set例如latin1。盡可能罕用Utf-8，utf-8占用空間較多。
--memlock：鎖定MySQL只能運轉在內存中，防止swapping，然則假如內存不敷時有能夠湧現毛病。
--max_allowed_packet：要足夠年夜，以順應比擬年夜的SQL查詢，對機能沒有太年夜影響，重要是防止湧現packet毛病。
--max_connections：server許可的最年夜銜接。太年夜的話會湧現out of memory。
--table_cache：MySQL在統一時光堅持翻開的table的數目。翻開table開支比擬年夜。普通設置為512。
--query_cache_size： 用於緩存查詢的內存年夜小。
--datadir：mysql寄存數據的根目次，和裝置文件離開在分歧的磁盤可以進步一點機能。
4. 存儲引擎優化
MySQL支撐分歧的存儲引擎，重要應用的有MyISAM和InnoDB。
4.1 MyISAM
MyISAM治理非事務表。它供給高速存儲和檢索，和全文搜刮才能。MyISAM在一切MySQL設置裝備擺設裡被支撐，它是默許的存儲引擎，除非設置裝備擺設MySQL默許應用別的一個引擎。
4.1.1 MyISAM特征
4.1.1.1 MyISAM Properties
1) 不支撐事務，宕機遇損壞表
2) 應用較小的內存和磁盤空間
3) 基於表的鎖，並發更新數據會湧現嚴重機能成績
4) MySQL只緩存Index，數據由OS緩存
4.1.1.2 Typical MyISAM usages
1) 日記體系
2) 只讀或許絕年夜部門是讀操作的運用
3) 全表掃描
4) 批量導入數據
5) 沒有事務的低並發讀/寫
4.1.2 MyISAM優化要點
1) 聲明列為NOT NULL，可以削減磁盤存儲。
2) 應用optimize table做碎片整頓，收受接管余暇空間。留意僅僅在異常年夜的數據變更後運轉。
3) Deleting/updating/adding年夜量數據的時刻制止應用index。應用ALTER TABLE t DISABLE KEYS。
4) 設置myisam_max_[extra]_sort_file_size足夠年夜，可以明顯進步repair table的速度。
4.1.3 MyISAM Table Locks
1) 防止並發insert，update。
2) 可使用insert delayed，然則有能夠喪失數據。
3) 優化查詢語句。
4) 程度分區。
5) 垂直分區。
6) 假如都不起感化，應用InnoDB。
4.1.4 MyISAM Key Cache
1) 設置key_buffer_size variable。MyISAN最重要的cache設置，用於緩存MyISAM表格的index數據，該參數只對MyISAM有影響。平日在只應用MyISAM的Server中設置25-33%的內存年夜小。
2) 可使用幾個分歧的Key Caches（對一些hot data）。
a) SET GLOBAL test.key_buffer_size=512*1024;
b) CACHE INDEX t1.i1, t2.i1, t3 IN test;
2) Preload index到Cache中可以進步查詢速度。由於preloading index是次序的，所以異常快。
a) LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES；
4.2 InnoDB
InnoDB給MySQL供給了具有提交，回滾和瓦解恢復才能的事務平安（ACID兼容）存儲引擎。InnoDB供給row level lock，而且也在SELECT語句供給一個Oracle作風分歧的非鎖定讀。這些特點增長了多用戶安排和機能。沒有在InnoDB中擴展鎖定的須要，由於在InnoDB中row level lock合適異常小的空間。InnoDB也支撐FOREIGN KEY束縛。在SQL查詢中，你可以自在地將InnoDB類型的表與其它MySQL的表的類型混雜起來，乃至在統一個查詢中也能夠混雜。
InnoDB是為在處置偉大數據量時取得最年夜機能而設計的。它的CPU應用效力異常高。
InnoDB存儲引擎曾經完整與MySQL辦事器整合，InnoDB存儲引擎為在內存中緩存數據和索引而保持它本身的緩沖池。 InnoDB存儲它的表＆索引在一個表空間中，表空間可以包括數個文件（或原始磁盤分區）。這與MyISAM表分歧，好比在MyISAM表中每一個表被存在分別的文件中。InnoDB 表可所以任何年夜小，即便在文件尺寸被限制為2GB的操作體系上。
很多須要高機能的年夜型數據庫站點上應用了InnoDB引擎。有名的Internet消息站點Slashdot.org運轉在InnoDB上。 Mytrix, Inc.在InnoDB上存儲跨越1TB的數據，還有一些其它站點在InnoDB上處置均勻每秒800次拔出/更新的負荷。
4.2.1 InnoDB特征
4.2.1.1 InnoDB Properties
1) 支撐事務，ACID，外鍵。
2) Row level locks。
3) 支撐分歧的隔離級別。
4) 和MyISAM比擬須要較多的內存和磁盤空間。
5) 沒有鍵緊縮。
6) 數據和索引都緩存在內存hash表中。
4.2.1.2 InnoDB Good For
1) 須要事務的運用。
2) 高並發的運用。
3) 主動恢復。
4) 較疾速的基於主鍵的操作。
4.2.2 InnoDB優化要點
1) 盡可能應用short，integer的主鍵。
2) Load/Insert數據時按主鍵次序。假如數據沒有按主鍵排序，先排序然後再停止數據庫操作。
3) 在Load數據是為設置SET UNIQUE_CHECKS=0，SET FOREIGN_KEY_CHECKS=0，可以免外鍵和獨一性束縛檢討的開支。
4) 應用prefix keys。由於InnoDB沒有key緊縮功效。
4.2.3 InnoDB辦事器端設定
innodb_buffer_pool_size：這是InnoDB最主要的設置，對InnoDB機能有決議性的影響。默許的設置只要8M，所以默許的數據庫設置上面InnoDB機能很差。在只要InnoDB存儲引擎的數據庫辦事器下面，可以設置60-80%的內存。更准確一點，在內存容量許可的情形上面設置比InnoDB tablespaces年夜10%的內存年夜小。
innodb_data_file_path：指定表數據和索引存儲的空間，可所以一個或許多個文件。最初一個數據文件必需是主動擴大的，也只要最初一個文件許可主動擴大。如許，當空間用完後，主動擴大數據文件就會主動增加（以8MB為單元）以包容額定的數據。例如： innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend兩個數據文件放在分歧的磁盤上。數據起首放在ibdata1中，當到達900M今後，數據就放在ibdata2中。一旦到達50MB，ibdata2將以8MB為單元主動增加。假如磁盤滿了，須要在別的的磁盤下面增長一個數據文件。
innodb_autoextend_increment: 默許是8M, 假如一次insert數據量比擬多的話, 可以恰當增長.
innodb_data_home_dir：放置表空間數據的目次，默許在mysql的數據目次，設置到和MySQL裝置文件分歧的分區可以進步機能。
innodb_log_file_size：該參數決議了recovery speed。太年夜的話recovery就會比擬慢，太小了影響查詢機能，普通取256M可以統籌機能和recovery的速度
。
innodb_log_buffer_size：磁盤速度是很慢的，直接將log寫道磁盤會影響InnoDB的機能，該參數設定了log buffer的年夜小，普通4M。假如有年夜的blob操作，可以恰當增年夜。
innodb_flush_logs_at_trx_commit=2： 該參數設定了事務提交時內存中log信息的處置。
1) =1時，在每一個事務提交時，日記緩沖被寫到日記文件，對日記文件做到磁盤操作的刷新。Truly ACID。速度慢。
2) =2時，在每一個事務提交時，日記緩沖被寫到文件，但纰謬日記文件做到磁盤操作的刷新。只要操作體系瓦解或失落電才會刪除最初一秒的事務，否則不會喪失事務。
3) =0時， 日記緩沖每秒一次地被寫到日記文件，而且對日記文件做到磁盤操作的刷新。任何mysqld過程的瓦解會刪除瓦解前最初一秒的事務
innodb_file_per_table：可以存儲每一個InnoDB表和它的索引在它本身的文件中。
transaction-isolation=READ-COMITTED: 假如運用法式可以運轉在READ-COMMITED隔離級別，做此設定會有必定的機能晉升。
innodb_flush_method： 設置InnoDB同步IO的方法：
1) Default – 應用fsync（）。
2) O_SYNC 以sync形式翻開文件，平日比擬慢。
3) O_DIRECT，在Linux上應用Direct IO。可以明顯進步速度，特殊是在RAID體系上。防止額定的數據復制和double buffering（mysql buffering 和OS buffering）。
innodb_thread_concurrency： InnoDB kernel最年夜的線程數。
1) 起碼設置為(num_disks+num_cpus)*2。
2) 可以經由過程設置成1000來制止這個限制
5. 緩存
緩存有許多種，為運用法式加上恰當的緩存戰略會明顯進步運用法式的機能。因為運用緩存是一個比擬年夜的話題，所以這一部門還須要進一步驟研。
6. Reference
1) http://www.mysqlperformanceblog.com/
2) Advanced MySQL Performance Optimization, Peter Zaitsev, Tobias Asplund, MySQL Users Conference 2005
3) Improving MySQL Server Performance with Intel C++ Compiler，Peter Zaitsev，Linux World 2005
4) MySQL Performance Optimization, Peter Zaitsev, Percona Ltd, OPEN SOURCE DATABASE CONFERENCE 2006
5) MySQL Server Settings Tuning, Peter Zaitsev, co-founder, Percona Ltd, 2007
6) MySQL Reference Manual
