優化mysql性能及索引講析

本文主要給大家簡單講講優化mysql性能及索引講析，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔優化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行的數據

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

使用其他方式再次查詢打開profiling功能，來查看sql的實際執行計劃

打開功能

開始查詢

查看profile對數據庫的cpu，block，以及io的使用情況：

4）數據庫的schema（模式）設計對性能也有影響

5）硬件選擇對性能的影響

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

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

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

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

什么是索引？

索引（index） 是幫助mysql高效獲取數據結構，幫助dba快速定位，簡單來說就相當于字典中的目錄

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

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

如圖：這里只說重點、淺藍色的我們成為磁盤塊、可以看到每個磁盤塊包含幾個數據項，和指針（×××）其實真正的數據在葉子節點上，就是最下面的一層，而其他的不存放數據，只存放指引數據方向的索引而已。

例如：要查找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子句當中

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

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

優化mysql性能及索引講析就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。