優(yōu)化mysql性能及索引講析

本文主要給大家簡單講講優(yōu)化mysql性能及索引講析，相關專業(yè)術語大家可以上網(wǎng)查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔優(yōu)化mysql性能及索引講析主題吧，希望可以給大家?guī)硪恍嶋H幫助。            

一：數(shù)據(jù)庫的優(yōu)化方面

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

比如說論壇里的帖子統(tǒng)計，并且實時更新

從功能上來說通過命令   select  count（*） from 表名 可以得到結果，如果論壇每秒產(chǎn)生產(chǎn)生成千上萬條帖子，我們沒有采用myisam存儲而用的是innodb存儲；就算再好的設備也不可能很快的查詢出來。

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

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

如果必須要實施更新就專門為這個功能創(chuàng)建一個表，要想查詢結果就專門查看這個表就好了；到時候每秒產(chǎn)生的上萬條帖子也是一種麻煩，反過來說但是到底有多少人會關注這個實時更新，如果把實時更新去掉就很容易實現(xiàn)；在通過創(chuàng)建統(tǒng)計表，每隔一定的時間去刷新便可以。這就是不合理的商業(yè)要求。

2：系統(tǒng)架構以及實現(xiàn)的影響

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

主要包括圖片、視屏、其他二進制文件，如果放到數(shù)據(jù)庫中數(shù)據(jù)空間資源消耗非常嚴重，另外一個就是消耗主機的cpu資源，因為數(shù)據(jù)庫本就不是處理這些的優(yōu)勢，

解決辦法：可以將這些二進制多媒體數(shù)據(jù)放到一個專門的文本文件中，然后給數(shù)據(jù)庫做一個連接指向這個文本文件，實現(xiàn)數(shù)據(jù)庫調用多媒體文件，有不用消耗數(shù)據(jù)庫的空間和cpu資源。

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

如果大的文本數(shù)據(jù)放到數(shù)據(jù)庫當中也會造成空間的占用浪費問題。

解決方法：可以使用非關系型數(shù)據(jù)庫進行存儲

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

每個sql語句在優(yōu)化前后的性能差異也是各不相同

在數(shù)據(jù)庫管理軟件中，最大性能瓶頸就是在于磁盤io、也就是數(shù)據(jù)的存取操作上面，而對于同一份數(shù)據(jù)，當我們以不同的方式去查找某一點內(nèi)容時候，所需的讀取數(shù)據(jù)量可能會有天壤之別，搜消耗的資源也區(qū)別很大

首先進行編寫一個腳本插入20000行的數(shù)據(jù)

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

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

打開功能

開始查詢

查看profile對數(shù)據(jù)庫的cpu，block，以及io的使用情況：

4）數(shù)據(jù)庫的schema（模式）設計對性能也有影響

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

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

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

其實數(shù)據(jù)庫的優(yōu)化不單單從物理方面進行提高配置，也包括邏輯化如連接數(shù)。。。，和商業(yè)的需求?？傊褪窍到y(tǒng)架構最優(yōu)化、邏輯結構精簡化、硬件設施理性化

二、索引的介紹以及創(chuàng)建和使用

什么是索引？

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

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

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

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

例如：要查找29，首先把磁盤塊1，加載到內(nèi)存，發(fā)生一次io，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤塊1的p2指針，由此往下推算，直到第三層算出為止。

索引的優(yōu)點：

讓mysql高效的運行，可以大大提高mysql的查詢效率，數(shù)據(jù)約束，快速定位

使用索引的代價：

1）需要加載到內(nèi)存，以文件的形式存放在硬盤中，所以增加磁盤的開銷

2）寫數(shù)據(jù)，需要更新索引，對數(shù)據(jù)庫是很大的開銷，降低表更新、添加和刪除的速度

不建議使用索引的情況：

1）表記錄較少

2）索引的選擇性較低，指不重復的索引與表記錄數(shù)的比值，取值范圍（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

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

查看索引

show  index  from  table_name

show  keys  from  table_name

創(chuàng)建索引的時機：

一般在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;

總結：

索引的優(yōu)化過程中主要用于存在where和join子句當中

索引中的列的基數(shù)越大，索引的效果越好

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

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