MySQL：性能優化-索引、語句、配置（基于5.7）

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

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

一、 數據庫的類型

  1） 第一代數據庫：

基于層次模型與網狀模型的數據庫

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

• 網狀型數據庫更能夠直接的描述現實世界，具有良好的性能，存取效率較高；但是隨著應用環境的增大，數據庫的架構也會越來越復雜，而且其DDL/DML語言也相對復雜，不利于用戶的使用。

  2） 第二代數據庫：

關系型數據庫

• 常用的關系型數據庫有db2、oracle、sqlserver、mysql

• 關系型數據庫適用于存放結構化的數據，是基于行和列構成的二維表，對于非結構化的數據一般存放在系統存儲中。

  3） 第三代數據庫

面向對象型數據庫

• 面向對象數據庫系統是為了滿足新的數據庫應用需要而產生的新一代數據庫系統，具有易維護、質量高、效率高、易擴展的特點，主要是為了支持非常復雜的數據模型，適用于工程設計領域；但是由于模型較為復雜，使得很多系統管理功能難以實現也不具備SQL處理集合數據的強大能力。

二、 影響mysql性能的因素

  1. 商業需求的影響

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

1） Myisam存儲引擎：

• Myisam有專用的計數器，當需要統計行數時，直接讀取計數器即可，所以使用myisam存儲引擎的數據庫在這方面不需要很擔心，但是myisam不支持事務，不支持行鎖，所以現在一般都很少使用

2）Innodb存儲引擎：

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

  2. 系統架構及實現的影響

1)二進制多媒體數據

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

2）超大文本數據

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

  3. 查詢語句對性能的浪費

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

  4. 數據庫schema設計對性能的影響

   就是數據庫的數據結構

  5. 硬件性能的影響

l  磁盤的I/O性能：內存、硬盤、網卡等

l  CPU的處理能力

l  網絡設備：交換路由、網線網卡等

三、mysql性能優化-----索引

  1. 索引作用

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

  2. 索引的類型

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

  3. B-tree索引的結構及原理

• 在查詢數據時，首先將磁盤塊1調入內存，然后根據數據項的范圍查詢到相對應的指針，然后根據指針再次調用一個磁盤塊，就這樣通過循環查詢縮小查詢范圍，最后得到想要的數據

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

• 底層存放真實數據的叫葉子，而其他層都是非葉子節點

  4. 索引的優點

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

  5. 索引的缺點

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

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

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

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

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

  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）組合索引

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

• 組合索引的有效列

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

5）全文索引（幾乎不使用，一般用于對大數據進行查詢時）

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

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

• CREATE FULLTEXT INDEX indexname ON tablename(column)

  8. 查詢索引

• mysql> show index from 表名；

• mysql> show keys from 表名；

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

l  當表的記錄較多，查詢速度較慢時我們就需要通過建立索引的方式優化數據庫的查詢性能

l  一般經常出現在where和join子句中的列需要建立索引

l  使用索引的注意事項

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

• 當在列上進行運算時，索引也會失效

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

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

四、mysql性能優化的思路

  1. 基本思路

• 通過show查詢系統的狀態等信息，去優化數據庫

• 通過慢查詢日志去優化查詢速度

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

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

  2. 索引優化

1)比如有用戶投訴公司網站查詢數據特別慢，這時需要我們優化數據庫的查詢性能，用戶是不知道使用了什么sql語句的，所以需要我們去找到sql語句，并對其進行優化

• 首先開啟慢查詢日志，記錄查詢超過N秒的語句

• 然后根據條件創建索引等

   2)        慢查詢日志

  3） 通過explain工具模擬語句執行，進行分析查詢

  4） 通過profiling工具對實際進行的查詢語句進行分析，相比explain更準確

    執行要測試的語句并查看反饋的信息

    通過語句的query_id號可以查看語句執行的詳細信息

五、 配置優化

   就是my.cnf中的參數配置

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

   查看當前已經建立的連接

   查看試圖連接或已連接數

   當連接大于最大連接數時，可能報錯1040：too  many  connections

   判斷當前最大連接數的方法：

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

   當mysql并發連接數達到最大時，允許被放入隊列的連接數

   查看當前正在等待的隊列數

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

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

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

   作用：

      決定索引處理速度

   優化方法：

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

   1)        配置參數

   2）作用：

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

   3） 通過檢查狀態值‘Qcache%’可以判斷QC設置是否合理

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

• Qcache_free_memory：Query Cache 中目前剩余的內存大小。

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

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

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

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

• Qcache_queries_in_cache：當前Query Cache 中cache 的Query 數量；

• Qcache_total_blocks：當前Query Cache 中的block 數量；。

   4） 關于query_cache的配置

  6. Max_connect_errors：與mysql安全有關的計數器值，最大錯誤連接數，

    只對myisam存儲引擎有效

• 防止暴力破解，當用戶連續輸錯多少次密碼后鎖定用戶

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

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

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

• 此配置是一個連接級（connection級）參數，每個連接的用戶都被單獨分配一個緩沖區

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

    只對myisam存儲引擎有效

• 最大值為1GB

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

    connection級參數，只對myisam存儲引擎有效

  10. Tread_cache_size：設置服務器線程緩存，只對myisam存儲引擎有效

• 設置規則：1GB內存8個、2GB內存16個、3GB內存32個

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

  11. Innodb_buffer_pool_size：相當于key_buffer_size對于MyISAM表的作用一樣

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

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

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

  13. innodb_thread_concurrency ：設置innodb線程的并發數量

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

  14. innodb_log_buffer_size

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

  15. innodb_log_file_size

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

  16. innodb_log_files_in_group

• 設置日志文件的組數

  17. read_buffer_size ：

• MySql 讀入緩沖區大小。

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

• 和sort_buffer_size一樣，該參數對應的分配內存也是每個連接獨享。

  18. read_rnd_buffer_size：

• MySql 的隨機讀（查詢操作）緩沖區大小。

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

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

  19. bulk_insert_buffer_size ：

• 批量插入數據緩存大小，可以有效提高插入效率，默認為8M。

  20. binary log日志文件的配置參數

六、優化后通過mysqlslap工具可以進行測試

  優化前

  將所有優化語句寫入，進行測試

七、 優化語句總結