優(yōu)化mysql數(shù)據(jù)庫有哪些主要方法

下文給大家?guī)黻P于優(yōu)化mysql數(shù)據(jù)庫有哪些主要方法，感興趣的話就一起來看看這篇文章吧，相信看完優(yōu)化mysql數(shù)據(jù)庫有哪些主要方法對大家多少有點幫助吧。

優(yōu)化mysql數(shù)據(jù)庫的方法：建立Index索引，少用select語句，開啟查詢緩存，選擇適合的存儲引擎，避免在where子句中使用or來連接以及避免大數(shù)據(jù)量返回等

對于一個以數(shù)據(jù)為中心的應用，數(shù)據(jù)庫的好壞直接影響到程序的性能，因此數(shù)據(jù)庫性能至關重要。所以mysql數(shù)據(jù)庫的優(yōu)化操作大家都要有所了解，本文就主要總結了mysql數(shù)據(jù)庫中常見的優(yōu)化操作，下面話不多說了，來看看詳細的介紹吧。

一、Index索引

將Index放第一位，不用說，這種優(yōu)化方式我們一直都在悄悄使用，那便是主鍵索引。有時候我們可能并不在意，如果定義適合的索引，數(shù)據(jù)庫查詢性能(速度)將提高幾倍甚至幾十倍。

二、少用SELECT*

可能有的人查詢數(shù)據(jù)庫時，遇到要查詢的都會select，這是不恰當?shù)男袨?。我們應該取我們要用的?shù)據(jù)，而不是全取，因為當我們select時，會增加web服務器的負擔，增加網(wǎng)絡傳輸?shù)呢撦d，查詢速度自然就下降 。

三、EXPLAIN SELECT

對于這個功能估計很多人都沒見過，但是這里強烈推薦使用。explain顯示了mysql如何使用索引來處理select語句以及連接表?？梢詭椭x擇更好的索引和寫出更優(yōu)化的查詢語句。主要用發(fā)就是在select前加上explain即可。

EXPLAIN SELECT [查找字段名] FROM tab_name ...

四、開啟查詢緩存

大多數(shù)的MySQL服務器都開啟了查詢緩存。這是提高性最有效的方法之一，而且這是被MySQL的數(shù)據(jù)庫引擎處理的。當有很多相同的查詢被執(zhí)行了多次的時候，這些查詢結果會被放到一個緩存中，這樣，后續(xù)的相同的查詢就不用操作表而直接訪問緩存結果了。

第一步把query_cache_type設置為ON，然后查詢系統(tǒng)變量have_query_cache是否可用：

show variables like 'have_query_cache'

之后，分配內(nèi)存大小給查詢緩存，控制緩存查詢結果的最大值。相關操作在配置文件中進行修改。

五、使用NOT NULL

很多表都包含可為 NULL （空值） 的列，即使應用程序井不需要保存 NULL 也是如此 ，這是因為可為 NULL 是列的默認屬性。通常情況下最好指定列為 NOT NULL，除非真 的需要存儲 NULL 值。

如果查詢中包含可為 NULL 的列，對 MySQL 來說更難優(yōu)化 ，因為可為 NULL 的列使 得索引、索引統(tǒng)計和值比較都更復雜 ?？蔀镹ULL 的列會使用更多的存儲空間 ，在 MySQL 里也需要特殊處理 。當可為NULL 的列被索引肘，每個索引記錄需要一個額 外的字節(jié)，在 MyISAM 里甚至還可能導致固定大小 的索引 （例如只有一個整數(shù)列的 索引） 變成可變大小的索引。

通常把可為 NULL 的列改為 NOT NULL 帶來的性能提升比較小 ，所以 （調(diào)優(yōu)時） 沒有 必要首先在現(xiàn)有schema中查找井修改掉這種情況 ，除非確定這會導致問題。但是， 如果計劃在列上建索引 ，就應該盡量避免設計成可為 NULL 的列。當然也有例外 ，例如值得一提的是，InnoDB 使用單獨的位 （bit ） 存儲 NULL 值 ，所 以對于稀疏數(shù)據(jù)由有很好的空間效率 。但這一點不適用于MyISAM 。

六、存儲引擎的選擇

對于如何選擇MyISAM和InnoDB,如果你需要事務處理或是外鍵，那么InnoDB可能是比較好的方式。如果你需要全文索引，那么通常來說MyISAM是好的選擇，因為這是系統(tǒng)內(nèi)建的，然而，我們其實并不會經(jīng)常地去測試兩百萬行記錄。所以，就算是慢一點，我們可以通過使用Sphinx從InnoDB中獲得全文索引。

數(shù)據(jù)的大小，是一個影響你選擇什么樣存儲引擎的重要因素，大尺寸的數(shù)據(jù)集趨向于選擇InnoDB方式，因為其支持事務處理和故障恢復。數(shù)據(jù)庫的在小決定了故障恢復的時間長短，InnoDB可以利用事務日志進行數(shù)據(jù)恢復，這會比較快。而MyISAM可能會需要

幾個小時甚至幾天來干這些事，InnoDB只需要幾分鐘。

您操作數(shù)據(jù)庫表的習慣可能也會是一個對性能影響很大的因素。比如： COUNT() 在 MyISAM表中會非?？欤贗nnoDB表下可能會很痛苦。而主鍵查詢則在InnoDB下會相當相當?shù)目?，但需要小心的是如果我們的主鍵太長了也會導致性能問題。大批的inserts語句在MyISAM下會快一些，但是updates在InnoDB 下會更快一些——尤其在并發(fā)量大的時候。

所以，到底你檢使用哪一個呢？根據(jù)經(jīng)驗來看，如果是一些小型的應用或項目，那么MyISAM也許會更適合。當然，在大型的環(huán)境下使用MyISAM也會有很大成功的時候，但卻不總是這樣的。如果你正在計劃使用一個超大數(shù)據(jù)量的項目，而且需要事務處理或外鍵支持，那么你真的應該直接使用InnoDB方式。但需要記住InnoDB的表需要更多的內(nèi)存和存儲，轉換100GB的MyISAM 表到InnoDB 表可能會讓你有非常壞的體驗。

七、避免在 where 子句中使用 or 來連接

如果一個字段有索引，一個字段沒有索引，將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or Name = 'admin'

可以這樣查詢：

select id from t where num = 10
union all
select id from t where Name = 'admin'

八、多使用varchar/nvarchar

使用varchar/nvarchar代替 char/nchar ，因為首先變長字段存儲空間小，可以節(jié)省存儲空間，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些。

九、避免大數(shù)據(jù)量返回

這里要考慮使用limit，來限制返回的數(shù)據(jù)量，如果每次返回大量自己不需要的數(shù)據(jù)，也會降低查詢速度。

十、where子句優(yōu)化

where 子句中使用參數(shù)，會導致全表掃描,因為SQL只有在運行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。

應盡量避免在 where 子句中對字段進行表達式操作，避免在where子句中對字段進行函數(shù)操作這將導致引擎放棄使用索引而進行全表掃描。不要在 where 子句中的“=”左邊進行函數(shù)、算術運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引。

看了以上關于優(yōu)化mysql數(shù)據(jù)庫有哪些主要方法的詳細內(nèi)容，是否有所收獲。如果想要了解更多相關，可以繼續(xù)關注我們的行業(yè)資訊板塊。