mysql效能最佳化注意事項以及索引

標籤：mysql最佳化   索引類型   dba   b-tree   

                  mysql效能最佳化注意事項以及索引

一：資料庫的最佳化方面

1商業需求的影響

比如說論壇裡的文章統計，並且即時更新

從功能上來說通過命令   select  count（*） from 表名 可以得到結果，如果論壇每秒產生產產生千上萬條文章，我們沒有採用myisam儲存而用的是innodb儲存；就算再好的裝置也不可能很快的查詢出來。

註：在where和count（*）使用中myisam比innodb要快的多；因為myisam內建了一個計數器，count（*）可以直接從計數器當中讀取，而innodb則要掃描全表。     

所以在innodb上執行count（*）時一般伴隨where，而且where中要包含主鍵以外的索引列。

如果必須要實施更新就專門為這個功能建立一個表，要想查詢結果就專門查看這個表就好了；到時候每秒產生的上萬條文章也是一種麻煩，反過來說但是到底有多少人會關注這個即時更新，如果把即時更新去掉就很容易實現；在通過建立統計表，每隔一定的時間去重新整理便可以。這就是不合理的商業要求。

2：系統架構以及實現的影響

1）二進位多媒體資料

主要包括圖片、視屏、其他二進位檔案，如果放到資料庫中資料空間資源消耗非常嚴重，另外一個就是消耗主機的cpu資源，因為資料庫本就不是處理這些的優勢，

解決辦法：可以將這些二進位多媒體資料放到一個專門的文字檔中，然後給資料庫做一個串連指向這個文字檔，實現資料庫調用多媒體檔案，有不用消耗資料庫的空間和cpu資源。

2）超大文本資料

如果大的文本資料放到資料庫當中也會造成空間的佔用浪費問題。

解決方案：可以使用非關係型資料庫進行儲存

3）查詢語句對效能的影響

每個sql語句在最佳化前後的效能差異也是各不相同

在資料庫管理軟體中，最大效能瓶頸就是在於磁碟io、也就是資料的存取操作上面，而對於同一份資料，當我們以不同的方式去尋找某一點內容時候，所需的讀取資料量可能會有天壤之別，搜消耗的資源也區別很大

首先進行編寫一個指令碼插入20000行的資料

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M02/98/BD/wKiom1k_4Xvyuz39AAEtFnYqw9A671.png-wh_500x0-wm_3-wmp_4-s_1463024725.png" title="圖片1.png" alt="wKiom1k_4Xvyuz39AAEtFnYqw9A671.png-wh_50" /> 

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M02/98/BD/wKiom1k_4ZHzoSMzAAAMgJ3_Osg051.png-wh_500x0-wm_3-wmp_4-s_58498681.png" title="圖片2.png" alt="wKiom1k_4ZHzoSMzAAAMgJ3_Osg051.png-wh_50" /> 

比如執行sql語句時可以用explain來查看執行計畫：

 

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M02/98/BD/wKioL1k_4cnxkiovAABZJLKSILs550.png-wh_500x0-wm_3-wmp_4-s_3276056154.png" title="圖片3.png" alt="wKioL1k_4cnxkiovAABZJLKSILs550.png-wh_50" /> 

使用其他方式重新查詢開啟profiling功能，來查看sql的實際執行計畫

開啟功能

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M01/98/BD/wKiom1k_4d3CIzWNAAARMXU4rY4020.png-wh_500x0-wm_3-wmp_4-s_2475442831.png" title="圖片4.png" alt="wKiom1k_4d3CIzWNAAARMXU4rY4020.png-wh_50" /> 

開始查詢

 

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M01/98/BD/wKioL1k_4fLTucF7AAB1UAJtdF0215.png-wh_500x0-wm_3-wmp_4-s_4205556329.png" title="圖片5.png" alt="wKioL1k_4fLTucF7AAB1UAJtdF0215.png-wh_50" /> 

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M00/98/BE/wKioL1k_4nWSnj-BAAA6UnOpfyY455.png-wh_500x0-wm_3-wmp_4-s_3287841957.png" title="圖片6.png" alt="wKioL1k_4nWSnj-BAAA6UnOpfyY455.png-wh_50" /> 

查看profile對資料庫的cpu，block，以及io的使用方式：

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M01/98/BE/wKioL1k_4oiBfpozAABUs1irruk160.png-wh_500x0-wm_3-wmp_4-s_1869069810.png" title="圖片7.png" alt="wKioL1k_4oiBfpozAABUs1irruk160.png-wh_50" /> 

 

4）資料庫的schema（模式）設計對效能也有影響

5）硬體選擇對效能的影響

資料庫主機是儲存資料的地方，所以io效能必須要優先考慮，無論是什麼資料庫都必須考慮的因素，當然和io相關的板卡

另外cpu的處理能力也不能忽視，企業中必須使用多核，另外記憶體也必須要大至少要64G

其實資料庫的最佳化不單單從物理方面進行提高配置，也包括邏輯化如串連數。。。，和商業的需求。總之就是系統架構最佳化、邏輯結構精簡化、硬體設施理性化

 

 

二、索引的介紹以及建立和使用

 

什麼是索引？

索引（index） 是協助mysql高效擷取資料結構，協助dba快速定位，簡單來說就相當於字典中的目錄

索引的類型在上章講過有三種{B-Tree、R-Tree、Full-Tree}類型、最常用的是B-Tree

這裡主要介紹的是B-Tree的索引結構：

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M00/98/BE/wKioL1k_4qGifoL-AAOWM6ruWeM416.png-wh_500x0-wm_3-wmp_4-s_1599930196.png" title="圖片8.png" alt="wKioL1k_4qGifoL-AAOWM6ruWeM416.png-wh_50" /> 

這裡只說重點、淺藍色的我們成為磁碟塊、可以看到每個磁碟塊包含幾個資料項目，和指標（黃色）其實真正的資料在葉子節點上，就是最下面的一層，而其他的不存放資料，只存放指引資料方向的索引而已。

例如：要尋找29，首先把磁碟塊1，載入到記憶體，發生一次io，在記憶體中用二分尋找確定29在17和35之間，鎖定磁碟塊1的p2指標，由此往下推算，直到第三層算出為止。

 

索引的優點：

讓mysql高效的運行，可以大大提高mysql的查詢效率，資料約束，快速定位

使用索引的代價：

1）需要載入到記憶體，以檔案的形式存放在硬碟中，所以增加磁碟的開銷

2）寫資料，需要更新索引，對資料庫是很大的開銷，降低表更新、添加和刪除的速度

 

不建議使用索引的情況：

1）表記錄較少

2）索引的選擇性較低，指不重複的索引與表記錄數的比值，取值範圍（0-1），選擇性越高，索引價值越大

 

1：普通索引

最基本的索引，沒有任何限制

create index index_name on tablename（columm1【column2，。。。。。】）

2：唯一索引

和普通索引類似，不同的就是索引列的值必須唯一，但允許空值，指的就是null，如果是複合式索引，列的值必須唯一。

create table tablename（id int not null,username varchar(16) not null,primary key(id)）;

3：複合式索引

為了進一步提升mysql的效率，可以使用複合式索引

create index index_name on table_name（column1，column2，column3）；

這樣的複合式索引效率高於單列的索引，而且採用的是最左首碼的結果。簡單理解就是從最左邊開始組合。

4：全文索引

只用於myisam表對文本域進行索引。欄位包括char、varchar、text

 

不過切記大容量的資料表，產生全文索引是一個非常消耗時間和硬碟的做法

 

查看索引

show  index  from  table_name

show  keys  from  table_name

建立索引的時機：

一般在where和join子句中需要建立索引

使用索引的注意事項：

某些情況下like 才需要建立索引，因為在一萬用字元%和-開頭查詢時，mysql不會使用索引

select * from table-name where name like ‘%admin’；

另外還有就是不能再列上進行運算

select * from users where YEAR(adddate)<2000;

強每個行上進行運算，將導致索引失效而進行全表掃描

可修改為select * from users where adddate<2000-10-4;

總結：

索引的最佳化過程中主要用於存在where和join子句當中

索引中的列的基數越大，索引的效果越好

使用的短索引，如果對字串進行索引，應該指定一個前置長度，可節省大量的索引空間，提升查詢的速度

本文出自 “apache” 部落格，請務必保留此出處http://xiaorenwutest.blog.51cto.com/12754924/1935146

mysql效能最佳化注意事項以及索引