MySQL：性能優(yōu)化-索引、語句、配置（基于5.7）

   小生博客：http://xsboke.blog.51cto.com

                             -------謝謝您的參考，如有疑問，歡迎交流

一、 數(shù)據(jù)庫的類型

  1） 第一代數(shù)據(jù)庫：

基于層次模型與網(wǎng)狀模型的數(shù)據(jù)庫

• 層次型數(shù)據(jù)庫比較經(jīng)典的是IBM公司的IMS（InformationManagement System）數(shù)據(jù)庫，層次型數(shù)據(jù)庫提供了良好的完整性支持，模型簡單，對具有一對多層關(guān)系的部們描述非常自然、直觀，容易理解，比較適用于那些實體間聯(lián)系是固定的且預(yù)先定義好的環(huán)境，其性能優(yōu)于關(guān)系模型；但是層次模型數(shù)據(jù)庫對查詢、插入和刪除操作限制的比較多

• 網(wǎng)狀型數(shù)據(jù)庫更能夠直接的描述現(xiàn)實世界，具有良好的性能，存取效率較高；但是隨著應(yīng)用環(huán)境的增大，數(shù)據(jù)庫的架構(gòu)也會越來越復(fù)雜，而且其DDL/DML語言也相對復(fù)雜，不利于用戶的使用。

  2） 第二代數(shù)據(jù)庫：

關(guān)系型數(shù)據(jù)庫

• 常用的關(guān)系型數(shù)據(jù)庫有db2、oracle、sqlserver、mysql

• 關(guān)系型數(shù)據(jù)庫適用于存放結(jié)構(gòu)化的數(shù)據(jù)，是基于行和列構(gòu)成的二維表，對于非結(jié)構(gòu)化的數(shù)據(jù)一般存放在系統(tǒng)存儲中。

  3） 第三代數(shù)據(jù)庫

面向?qū)ο笮蛿?shù)據(jù)庫

• 面向?qū)ο髷?shù)據(jù)庫系統(tǒng)是為了滿足新的數(shù)據(jù)庫應(yīng)用需要而產(chǎn)生的新一代數(shù)據(jù)庫系統(tǒng)，具有易維護(hù)、質(zhì)量高、效率高、易擴(kuò)展的特點，主要是為了支持非常復(fù)雜的數(shù)據(jù)模型，適用于工程設(shè)計領(lǐng)域；但是由于模型較為復(fù)雜，使得很多系統(tǒng)管理功能難以實現(xiàn)也不具備SQL處理集合數(shù)據(jù)的強(qiáng)大能力。

二、 影響mysql性能的因素

  1. 商業(yè)需求的影響

例：要求論壇帖子總量實時更新

1） Myisam存儲引擎：

• Myisam有專用的計數(shù)器，當(dāng)需要統(tǒng)計行數(shù)時，直接讀取計數(shù)器即可，所以使用myisam存儲引擎的數(shù)據(jù)庫在這方面不需要很擔(dān)心，但是myisam不支持事務(wù)，不支持行鎖，所以現(xiàn)在一般都很少使用

2）Innodb存儲引擎：

• Innodb存儲引擎是沒有專用的計數(shù)器的，所以在統(tǒng)計帖子總量時會相當(dāng)耗費(fèi)時間，雖然可以專門為統(tǒng)計帖子總數(shù)去創(chuàng)建一個，但是如果出現(xiàn)非常大的并發(fā)寫入，那這種方法就不很難解決了

  2. 系統(tǒng)架構(gòu)及實現(xiàn)的影響

1)二進(jìn)制多媒體數(shù)據(jù)

• 如果將圖片、音樂、視頻等其他的一些相關(guān)二進(jìn)制文件直接存放在數(shù)據(jù)庫中，將會占用大量的數(shù)據(jù)庫空間資源和消耗數(shù)據(jù)庫主機(jī)的CPU資源，解決辦法是將這些二進(jìn)制文件存放在磁盤中

2）超大文本數(shù)據(jù)

• 對于一些比較大的文本數(shù)據(jù)也不適用于存儲在數(shù)據(jù)庫中，因為會使數(shù)據(jù)庫的性能地下，而且浪費(fèi)空間，所以可以存放在磁盤中

  3. 查詢語句對性能的浪費(fèi)

對于數(shù)據(jù)庫管理軟件，最大的性能瓶頸就在于磁盤的IO，當(dāng)查詢某個數(shù)據(jù)時，需要多次進(jìn)行磁盤IO；將數(shù)據(jù)塊加載到內(nèi)存并且進(jìn)行數(shù)據(jù)查找，當(dāng)查詢某個數(shù)據(jù)時，可能需要幾百上千次進(jìn)行這種磁盤IO的操作，所以在資源消耗方面是很大的，但是如果以不同的方式去尋找其中的某一點內(nèi)容時，所需的IO次數(shù)可能相對就減少，這里說的也就是索引

  4. 數(shù)據(jù)庫schema設(shè)計對性能的影響

   就是數(shù)據(jù)庫的數(shù)據(jù)結(jié)構(gòu)

  5. 硬件性能的影響

l  磁盤的I/O性能：內(nèi)存、硬盤、網(wǎng)卡等

l  CPU的處理能力

l  網(wǎng)絡(luò)設(shè)備：交換路由、網(wǎng)線網(wǎng)卡等

三、mysql性能優(yōu)化-----索引

  1. 索引作用

索引通過減少磁盤IO而優(yōu)化了查詢速度，但是在一定程度上降低了增刪改的性能，因為索引相當(dāng)于一本書的目錄，書的內(nèi)容改變，索引也需要更新，而且索引是存放在物理文件中的，相對的也占用了磁盤空間

  2. 索引的類型

索引一般分為B-tree、R-tree和全文索引，但是innodb存儲引擎是不支持全文索引的，而且常用的索引就是B-tree索引也叫平衡樹索引

  3. B-tree索引的結(jié)構(gòu)及原理

• 在查詢數(shù)據(jù)時，首先將磁盤塊1調(diào)入內(nèi)存，然后根據(jù)數(shù)據(jù)項的范圍查詢到相對應(yīng)的指針，然后根據(jù)指針再次調(diào)用一個磁盤塊，就這樣通過循環(huán)查詢縮小查詢范圍，最后得到想要的數(shù)據(jù)

• 使用索引進(jìn)行查詢可能只需要幾次的磁盤IO就能找到想要的數(shù)據(jù)，而沒有索引時需要遍歷數(shù)據(jù)庫進(jìn)行查找數(shù)據(jù)可能要經(jīng)過幾十上百甚至更多的磁盤IO才能得到想要的數(shù)據(jù)，這就體現(xiàn)了索引的有點

• 底層存放真實數(shù)據(jù)的叫葉子，而其他層都是非葉子節(jié)點

  4. 索引的優(yōu)點

• 提高查詢速度，快速定位數(shù)據(jù)，大大提高mysql的查詢（包括排序，分組）效率

  5. 索引的缺點

• 索引是以文件形式存放在硬盤的，從一定程度上占用了硬盤空間

• 寫數(shù)據(jù)時需要更新索引，這對數(shù)據(jù)庫是個很大的開銷

  6. 不建議使用索引的情況

• 表記錄較少時（一般為1000行）

• 索引的選擇性較低時，比如有一列是性別，只有男女兩種數(shù)據(jù)，這時選擇性就較低

  7. 索引的類型

索引包括單列索引和組合索引

1）普通索引

• 這是最基本的索引，它沒有任何限制

• CREATE INDEX indexName ON tablename(column1[,column2,……])

2）唯一索引

• 它與前面的普通索引類似，不同的就是索引列的值必須唯一，但允許空值，空值是指null。如果是組合索引，組合列的值必須唯一

• CREATE UNIQUE INDEX indexName ON tablename(column1[,column2,……])

3）主鍵索引：一種特殊的唯一索引，不允許有空值，一般在建表的時候同時建立主鍵索引

• CREATE TABLE tablename(ID INT NOT NULL,  username VARCHAR(16) NOT NULL,  PRIMARY KEY(ID) );

4）組合索引

• 為了進(jìn)一步提升MySQL的效率，就要考慮建立組合索引，組合索引就是由多個列組成的索引

• 組合索引的有效列

• 組合索引有最左前綴的特點，比如創(chuàng)建一個索引，列值取A、B、C，在使用命令創(chuàng)建索引時，三個值的順序?qū)Q定什么情況下使用索引，如果從左至右一次為A、B、C，那么在查詢數(shù)據(jù)時，只有條件為ABC、AB、A時才使用索引，而且條件的順序不能顛倒，如果條件為BC、B、C時，索引將不被使用

5）全文索引（幾乎不使用，一般用于對大數(shù)據(jù)進(jìn)行查詢時）

• 只用于MyISAM 表 對文本域進(jìn)行索引。字段類型包括char、varchar、text

• 不過切記對于大容量的數(shù)據(jù)表，生成全文索引是一個非常消耗時間非常消耗硬盤空間的做法。

• CREATE FULLTEXT INDEX indexname ON tablename(column)

  8. 查詢索引

• mysql> show index from 表名；

• mysql> show keys from 表名；

  9. 在什么情況下需要建立索引，應(yīng)該選擇什么列建立索引

l  當(dāng)表的記錄較多，查詢速度較慢時我們就需要通過建立索引的方式優(yōu)化數(shù)據(jù)庫的查詢性能

l  一般經(jīng)常出現(xiàn)在where和join子句中的列需要建立索引

l  使用索引的注意事項

• 當(dāng)使用like做模糊查詢時，以通配符“%”或“_”為開頭做查詢將不會使用索引

• 當(dāng)在列上進(jìn)行運(yùn)算時，索引也會失效

• MySQL只對一下操作符才使用索引：<,<=,=,>,>=,between,in,

• 不要過渡建立索引，適可而止，因為每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能，因為在修改表的內(nèi)容時，索引必須進(jìn)行更新，有時可能需要重構(gòu)，因此，索引越多，所花的時間越長。

四、mysql性能優(yōu)化的思路

  1. 基本思路

• 通過show查詢系統(tǒng)的狀態(tài)等信息，去優(yōu)化數(shù)據(jù)庫

• 通過慢查詢?nèi)罩救?yōu)化查詢速度

• Explain工具可以模擬語句執(zhí)行，然后通過輸出結(jié)果得到語句的執(zhí)行信息

• Profiling工具可以使語句真實執(zhí)行，并且通過輸出結(jié)果得到語句的執(zhí)行信息

  2. 索引優(yōu)化

1)比如有用戶投訴公司網(wǎng)站查詢數(shù)據(jù)特別慢，這時需要我們優(yōu)化數(shù)據(jù)庫的查詢性能，用戶是不知道使用了什么sql語句的，所以需要我們?nèi)フ业?/span>sql語句，并對其進(jìn)行優(yōu)化

• 首先開啟慢查詢?nèi)罩?，記錄查詢超過N秒的語句

• 然后根據(jù)條件創(chuàng)建索引等

   2)        慢查詢?nèi)罩?/span>

  3） 通過explain工具模擬語句執(zhí)行，進(jìn)行分析查詢

  4） 通過profiling工具對實際進(jìn)行的查詢語句進(jìn)行分析，相比explain更準(zhǔn)確

    執(zhí)行要測試的語句并查看反饋的信息

    通過語句的query_id號可以查看語句執(zhí)行的詳細(xì)信息

五、 配置優(yōu)化

   就是my.cnf中的參數(shù)配置

  1. Max_connections：最大連接數(shù)，只對myisam存儲引擎有效

   查看當(dāng)前已經(jīng)建立的連接

   查看試圖連接或已連接數(shù)

   當(dāng)連接大于最大連接數(shù)時，可能報錯1040：too  many  connections

   判斷當(dāng)前最大連接數(shù)的方法：

  2. Back_log：mysql請求隊列數(shù)，只對myisam存儲引擎有效

   當(dāng)mysql并發(fā)連接數(shù)達(dá)到最大時，允許被放入隊列的連接數(shù)

   查看當(dāng)前正在等待的隊列數(shù)

  3. Wait_timeout 和interactive_timeout，只對myisam存儲引擎有效

   非交互式和交互式的閑時超時時間

  4. Key_buffer_size：索引緩沖區(qū)大小，只對myisam存儲引擎有效

   作用：

      決定索引處理速度

   優(yōu)化方法：

  5. Query_cache_size:查詢緩存，簡稱QC，只對myisam存儲引擎有效

   1)        配置參數(shù)

   2）作用：

• 使用查詢緩沖區(qū)，mysql將查詢過的結(jié)果存放在緩沖區(qū)中，今后對于同樣的select語句（select語句的大小寫和空格），將直接從緩沖區(qū)中讀取結(jié)果

   3） 通過檢查狀態(tài)值‘Qcache%’可以判斷QC設(shè)置是否合理

• Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)。如果該值顯示較大，則說明Query Cache 中的內(nèi)存碎片較多了，查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% ，如果查詢緩存碎片率超過20%，使用FLUSHQUERY CACHE對緩存中的碎片進(jìn)行整理

• Qcache_free_memory：Query Cache 中目前剩余的內(nèi)存大小。

• Qcache_hits：表示有多少次命中緩存。我們主要可以通過該值來驗證我們的查詢緩存的效果。數(shù)字越大，緩存效果越理想。

• Qcache_inserts：表示多少次未命中然后插入。

• Qcache_lowmem_prunes：多少條Query 因為內(nèi)存不足而被清除出Query Cache。通過“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結(jié)合，能夠更清楚的了解到我們系統(tǒng)中Query Cache 的內(nèi)存大小是否真的足夠，是否非常頻繁的出現(xiàn)因為內(nèi)存不足而有Query 被換出。

• Qcache_not_cached：不適合進(jìn)行緩存的查詢的數(shù)量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)。

• Qcache_queries_in_cache：當(dāng)前Query Cache 中cache 的Query 數(shù)量；

• Qcache_total_blocks：當(dāng)前Query Cache 中的block 數(shù)量；。

   4） 關(guān)于query_cache的配置

  6. Max_connect_errors：與mysql安全有關(guān)的計數(shù)器值，最大錯誤連接數(shù)，

    只對myisam存儲引擎有效

• 防止暴力破解，當(dāng)用戶連續(xù)輸錯多少次密碼后鎖定用戶

• 清除計數(shù)器：重啟mysql服務(wù)或者使用命令flush  hosts

  7. Sort_buffer_site：排序緩沖區(qū)大小，只對myisam存儲引擎有效

• 影響帶有order by或group  by的查詢語句查詢速度

• 此配置是一個連接級（connection級）參數(shù)，每個連接的用戶都被單獨(dú)分配一個緩沖區(qū)

  8. Max_allowed_packet：限制server接受的select、update語句包的大小，

    只對myisam存儲引擎有效

• 最大值為1GB

  9. Join_buffer_size：影響帶有多表查詢類型語句的查詢速度，

    connection級參數(shù)，只對myisam存儲引擎有效

  10. Tread_cache_size：設(shè)置服務(wù)器線程緩存，只對myisam存儲引擎有效

• 設(shè)置規(guī)則：1GB內(nèi)存8個、2GB內(nèi)存16個、3GB內(nèi)存32個

• 這個值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時,那么客戶端的線程將被放到緩存中以響應(yīng)下一個客戶而不是銷毀(前提是緩存數(shù)未達(dá)上限)

  11. Innodb_buffer_pool_size：相當(dāng)于key_buffer_size對于MyISAM表的作用一樣

• InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引。對于單獨(dú)的MySQL數(shù)據(jù)庫服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%

• 根據(jù)MySQL手冊，對于2G內(nèi)存的機(jī)器，推薦值是1G（50%）。 如果你的數(shù)據(jù)量不大，并且不會暴增，那么無需把 innodb_buffer_pool_size 設(shè)置的太大了。

  12. innodb_flush_log_at_trx_commit：控制事務(wù)日志刷寫的時間點

  13. innodb_thread_concurrency ：設(shè)置innodb線程的并發(fā)數(shù)量

• 默認(rèn)值為0表示不限制，若要設(shè)置則與服務(wù)器的CPU核數(shù)相同或是cpu的核數(shù)的2倍，建議用默認(rèn)設(shè)置，一般為8

  14. innodb_log_buffer_size

• 確定事務(wù)日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，對于較大的事務(wù)，可以增大緩存大小。

  15. innodb_log_file_size

• 確定數(shù)據(jù)日志文件的大小，以M為單位，更大的設(shè)置可以提高性能.

  16. innodb_log_files_in_group

• 設(shè)置日志文件的組數(shù)

  17. read_buffer_size ：

• MySql 讀入緩沖區(qū)大小。

• 對表進(jìn)行順序掃描的請求將分配一個讀入緩沖區(qū)，MySql會為它分配一段內(nèi)存緩沖區(qū)。如果對表的順序掃描請求非常頻繁，并且你認(rèn)為頻繁掃描進(jìn)行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。

• 和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個連接獨(dú)享。

  18. read_rnd_buffer_size：

• MySql 的隨機(jī)讀（查詢操作）緩沖區(qū)大小。

• 當(dāng)按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時，MySql會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但MySql會為每個客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大。

• 順序讀是指根據(jù)索引的葉節(jié)點數(shù)據(jù)就能順序地讀取所需要的行數(shù)據(jù)。隨機(jī)讀是指一般需要根據(jù)輔助索引葉節(jié)點中的主鍵尋找實際行數(shù)據(jù)，而輔助索引和主鍵所在的數(shù)據(jù)段不同，因此訪問方式是隨機(jī)的。

  19. bulk_insert_buffer_size ：

• 批量插入數(shù)據(jù)緩存大小，可以有效提高插入效率，默認(rèn)為8M。

  20. binary log日志文件的配置參數(shù)

六、優(yōu)化后通過mysqlslap工具可以進(jìn)行測試

  優(yōu)化前

  將所有優(yōu)化語句寫入，進(jìn)行測試

七、 優(yōu)化語句總結(jié)