前言
對(duì)于一個(gè)以數(shù)據(jù)為中心的應(yīng)用，數(shù)據(jù)庫(kù)的好壞直接影響到程序的性能邓深，因此數(shù)據(jù)庫(kù)性能至關(guān)重要杜窄。所以mysql數(shù)據(jù)庫(kù)的優(yōu)化操作大家都要有所了解侄旬，本文就主要總結(jié)了mysql數(shù)據(jù)庫(kù)中常見的優(yōu)化操作望抽，下面話不多說了加矛，來看看詳細(xì)的介紹吧。
一煤篙、Index索引
將Index放第一位斟览，不用說，這種優(yōu)化方式我們一直都在悄悄使用辑奈，那便是主鍵索引苛茂。有時(shí)候我們可能并不在意，如果定義適合的索引鸠窗，數(shù)據(jù)庫(kù)查詢性能(速度)將提高幾倍甚至幾十倍妓羊。
普通索引
作用是提高查詢速度。
建表稍计，創(chuàng)建索引
1
2
3
4
5
CREATETABLEtbl_name(
字段名稱 字段類型 [完整性約束條件],
~
index[索引名] (column_name)
);
創(chuàng)建索引
1CREATEINDEXindex_name ONtab_name (column_name)
刪除索引
1DROPINDEXindex_name FROMtab_name
查看索引
1SHOW indexFROMtab_name
主鍵索引
作用是加速查詢和唯一約束
建表躁绸，創(chuàng)建索引
1
2
3
4
5
CREATETABLEtbl_name(
字段名稱 字段類型 [完整性約束條件],
~
PRIMARYKEY(column_name)
);
創(chuàng)建索引
1ALTERTABLEtab_name ADDPRIMARYKEY(column_name)
刪除索引
1ALTERTABLEtab_name DROPPRIMAY KEY(column_name)
唯一索引
作用是加速查詢和唯一約束
建表，創(chuàng)建索引
1
2
3
4
5
CREATETABLEtbl_name(
字段名稱 字段類型 [完整性約束條件],
~
unique[索引名] (column_name)
);
創(chuàng)建索引
1CREATEUNIQUEINDEXindex_name ONtab_name (column_name)
刪除索引
1DROPUNIQUEINDEXindex_name FROMtab_name
二臣嚣、少用SELECT*
可能有的人查詢數(shù)據(jù)庫(kù)時(shí)净刮，遇到要查詢的都會(huì)select，這是不恰當(dāng)?shù)男袨楣柙颉Ｎ覀儜?yīng)該取我們要用的數(shù)據(jù)淹父，而不是全取，因?yàn)楫?dāng)我們select時(shí)怎虫，會(huì)增加web服務(wù)器的負(fù)擔(dān)暑认，增加網(wǎng)絡(luò)傳輸?shù)呢?fù)載，查詢速度自然就下降 大审。
三穷吮、EXPLAIN SELECT
對(duì)于這個(gè)功能估計(jì)很多人都沒見過，但是這里強(qiáng)烈推薦使用饥努。explain顯示了mysql如何使用索引來處理select語句以及連接表捡鱼。可以幫助選擇更好的索引和寫出更優(yōu)化的查詢語句酷愧。主要用發(fā)就是在select前加上explain即可驾诈。
1EXPLAIN SELECT[查找字段名] FROMtab_name ...
四、開啟查詢緩存
大多數(shù)的MySQL服務(wù)器都開啟了查詢緩存溶浴。這是提高性最有效的方法之一乍迄，而且這是被MySQL的數(shù)據(jù)庫(kù)引擎處理的。當(dāng)有很多相同的查詢被執(zhí)行了多次的時(shí)候士败，這些查詢結(jié)果會(huì)被放到一個(gè)緩存中闯两，這樣褥伴，后續(xù)的相同的查詢就不用操作表而直接訪問緩存結(jié)果了。
第一步把query_cache_type設(shè)置為ON漾狼，然后查詢系統(tǒng)變量have_query_cache是否可用：
1show variables like'have_query_cache'
之后重慢，分配內(nèi)存大小給查詢緩存，控制緩存查詢結(jié)果的最大值逊躁。相關(guān)操作在配置文件中進(jìn)行修改似踱。
五、使用NOT NULL
很多表都包含可為 NULL （空值） 的列稽煤，即使應(yīng)用程序井不需要保存 NULL 也是如此 核芽，這是因?yàn)榭蔀?NULL 是列的默認(rèn)屬性。通常情況下最好指定列為 NOT NULL酵熙，除非真 的需要存儲(chǔ) NULL 值轧简。
如果查詢中包含可為 NULL 的列，對(duì) MySQL 來說更難優(yōu)化 匾二，因?yàn)榭蔀?NULL 的列使 得索引哮独、索引統(tǒng)計(jì)和值比較都更復(fù)雜 〖傥穑可為NULL 的列會(huì)使用更多的存儲(chǔ)空間 ，在 MySQL 里也需要特殊處理 态鳖。當(dāng)可為NULL 的列被索引肘转培，每個(gè)索引記錄需要一個(gè)額 外的字節(jié)，在 MyISAM 里甚至還可能導(dǎo)致固定大小 的索引 （例如只有一個(gè)整數(shù)列的 索引） 變成可變大小的索引浆竭。
通常把可為 NULL 的列改為 NOT NULL 帶來的性能提升比較小 浸须，所以 （調(diào)優(yōu)時(shí)） 沒有 必要首先在現(xiàn)有schema中查找井修改掉這種情況 ，除非確定這會(huì)導(dǎo)致問題邦泄。但是删窒， 如果計(jì)劃在列上建索引 ，就應(yīng)該盡量避免設(shè)計(jì)成可為 NULL 的列顺囊。當(dāng)然也有例外 肌索，例如值得一提的是，InnoDB 使用單獨(dú)的位 （bit ） 存儲(chǔ) NULL 值 特碳，所 以對(duì)于稀疏數(shù)據(jù)由有很好的空間效率 诚亚。但這一點(diǎn)不適用于MyISAM 。
六午乓、存儲(chǔ)引擎的選擇
對(duì)于如何選擇MyISAM和InnoDB,如果你需要事務(wù)處理或是外鍵站宗，那么InnoDB可能是比較好的方式。如果你需要全文索引益愈，那么通常來說MyISAM是好的選擇梢灭，因?yàn)檫@是系統(tǒng)內(nèi)建的，然而，我們其實(shí)并不會(huì)經(jīng)常地去測(cè)試兩百萬行記錄敏释。所以库快，就算是慢一點(diǎn)，我們可以通過使用Sphinx從InnoDB中獲得全文索引颂暇。
數(shù)據(jù)的大小缺谴，是一個(gè)影響你選擇什么樣存儲(chǔ)引擎的重要因素，大尺寸的數(shù)據(jù)集趨向于選擇InnoDB方式耳鸯，因?yàn)槠渲С质聞?wù)處理和故障恢復(fù)湿蛔。數(shù)據(jù)庫(kù)的在小決定了故障恢復(fù)的時(shí)間長(zhǎng)短，InnoDB可以利用事務(wù)日志進(jìn)行數(shù)據(jù)恢復(fù)县爬，這會(huì)比較快阳啥。而MyISAM可能會(huì)需要
幾個(gè)小時(shí)甚至幾天來干這些事，InnoDB只需要幾分鐘财喳。
您操作數(shù)據(jù)庫(kù)表的習(xí)慣可能也會(huì)是一個(gè)對(duì)性能影響很大的因素察迟。比如： COUNT() 在 MyISAM表中會(huì)非常快耳高，而在InnoDB表下可能會(huì)很痛苦扎瓶。而主鍵查詢則在InnoDB下會(huì)相當(dāng)相當(dāng)?shù)目欤枰⌒牡氖侨绻覀兊闹麈I太長(zhǎng)了也會(huì)導(dǎo)致性能問題泌枪。大批的inserts語句在MyISAM下會(huì)快一些概荷，但是updates在InnoDB 下會(huì)更快一些——尤其在并發(fā)量大的時(shí)候。
所以碌燕，到底你檢使用哪一個(gè)呢误证？根據(jù)經(jīng)驗(yàn)來看，如果是一些小型的應(yīng)用或項(xiàng)目修壕，那么MyISAM也許會(huì)更適合愈捅。當(dāng)然，在大型的環(huán)境下使用MyISAM也會(huì)有很大成功的時(shí)候慈鸠，但卻不總是這樣的蓝谨。如果你正在計(jì)劃使用一個(gè)超大數(shù)據(jù)量的項(xiàng)目，而且需要事務(wù)處理或外鍵支持青团，那么你真的應(yīng)該直接使用InnoDB方式像棘。但需要記住InnoDB的表需要更多的內(nèi)存和存儲(chǔ)，轉(zhuǎn)換100GB的MyISAM 表到InnoDB 表可能會(huì)讓你有非常壞的體驗(yàn)壶冒。
七缕题、避免在 where 子句中使用 or 來連接
如果一個(gè)字段有索引，一個(gè)字段沒有索引胖腾，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描烟零，如：
1selectid fromt wherenum=10 orName= 'admin'
可以這樣查詢：
1
2
3
selectid fromt wherenum = 10
unionall
selectid fromt whereName= 'admin'
八瘪松、多使用varchar/nvarchar
使用varchar/nvarchar代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小锨阿，可以節(jié)省存儲(chǔ)空間宵睦，其次對(duì)于查詢來說，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些墅诡。
九壳嚎、避免大數(shù)據(jù)量返回
這里要考慮使用limit，來限制返回的數(shù)據(jù)量末早，如果每次返回大量自己不需要的數(shù)據(jù)烟馅，也會(huì)降低查詢速度。
十然磷、where子句優(yōu)化
where 子句中使用參數(shù)郑趁，會(huì)導(dǎo)致全表掃描,因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)姿搜；它必須在編譯時(shí)進(jìn)行選擇寡润。然 而，如果在編譯時(shí)建立訪問計(jì)劃舅柜，變量的值還是未知的梭纹，因而無法作為索引選擇的輸入項(xiàng)。
應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作致份，避免在where子句中對(duì)字段進(jìn)行函數(shù)操作這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描变抽。不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算知举，否則系統(tǒng)將可能無法正確使用索引瞬沦。
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了太伊，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助雇锡，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持僚焦。
