這篇文章主要介紹了MySQL優(yōu)化方法有哪些，具有一定借鑒價(jià)值，需要的朋友可以參考下。下面就和我一起來看看mysql優(yōu)化方法吧。
五通橋ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書合作）期待與您的合作！
一、總體優(yōu)化思路
首先構(gòu)建腳本觀察查詢數(shù)，連接數(shù)等數(shù)據(jù)，確定環(huán)境原因以及內(nèi)部SQL執(zhí)行原因，然后根據(jù)具體原因做具體處理。
二、構(gòu)建腳本觀察狀態(tài)
mysqladmin -uroot -p ext \G
該命令可獲取當(dāng)前查詢數(shù)量等信息，定時(shí)輪詢并將結(jié)果重定向到文本中，然后處理成圖表。
三、處理對策
1.若是規(guī)律性出現(xiàn)查詢慢，考慮緩存雪崩問題。
對于該問題只需將緩存的失效時(shí)間處理成不要相近時(shí)間同時(shí)失效，失效時(shí)間盡量離散化，或者集中到午夜失效。
2.若非規(guī)律性查詢緩慢，考慮設(shè)計(jì)缺乏優(yōu)化
處理方法：
a：開啟profiling記錄查詢操作，并獲取語句執(zhí)行詳細(xì)信息
show variables like '%profiling%'; set profiling=on; select count(*) from user; show profiles; show profile for query 1; >>> +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000060 | | Executing hook on transaction | 0.000004 | | starting | 0.000049 | | checking permissions | 0.000007 | | Opening tables | 0.000192 | | init | 0.000006 | | System lock | 0.000009 | | optimizing | 0.000005 | | statistics | 0.000014 | | preparing | 0.000017 | | executing | 0.001111 | | end | 0.000006 | | query end | 0.000003 | | waiting for handler commit | 0.000015 | | closing tables | 0.000011 | | freeing items | 0.000085 | | cleaning up | 0.000008 | +--------------------------------+----------+
b：使用explain 查看語句執(zhí)行情況，索引使用，掃描范圍等等
重慶分公司
重慶分公司
mysql> explain select count(*) from goods \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
partitions: NULL
type: index
possible_keys: NULL
key: gid
key_len: 5
ref: NULL
rows: 3
filtered: 100.00
Extra: Using indexc：相關(guān)優(yōu)化手法
表的優(yōu)化與列類型選擇
列選擇原則：
1：字段類型優(yōu)先級 整型 > date,time > char,varchar > blob
原因：整型,time運(yùn)算快，節(jié)省空間
char/varchar要考慮字符集的轉(zhuǎn)換與排序時(shí)的校對集，速度慢
blob無法使用內(nèi)存臨時(shí)表
2：夠用就行，不要慷慨(如 smallint,varchar(N))
原因：大的字段浪費(fèi)內(nèi)存，影響速度
以varchar(10), varchar(300)存儲的內(nèi)容相同，但在表聯(lián)查時(shí)，varchar(300)要花更多內(nèi)存
3：盡量避免使用NULL
原因：NULL不利于索引，要用特殊的字節(jié)來標(biāo)注.
在磁盤上占據(jù)的空間其實(shí)更大
索引優(yōu)化策略
1.索引類型
1.1 B-tree索引(排好序的快速查找結(jié)構(gòu))
注：Myisam,innodb中，默認(rèn)用的是B-tree索引
1.2 hash索引
在memory表里，默認(rèn)是hash索引，hash的理論查詢時(shí)間復(fù)查度為O（1）
疑問：既然hash索引如此高效，為何不都用他？
a.hash函數(shù)計(jì)算后的結(jié)果是隨機(jī)的，如果是在磁盤上放置數(shù)據(jù)，以主鍵為id為例，那么隨著id的增長，id對應(yīng)的行，在磁盤上隨機(jī)放置。
b.無法對范圍查詢進(jìn)行優(yōu)化
c.無法利用前綴索引，比如在b-tree中，field列的值為“helloworld”，索引查詢xx=hello/xx=helloworld都可以利用索引（左前綴索引），但hash索引無法做到，因?yàn)閔ash(hello)與hash(helloworld)并無關(guān)聯(lián)關(guān)系。
d.排序也無法優(yōu)化
e.必須回行，通過索引拿到數(shù)據(jù)位置，必須回到表中取數(shù)據(jù).
2.b-tree索引的常見誤區(qū)
2.1 在where條件常用的列上都加上索引
例：where cat_id=3 and price>100； //查詢第3個(gè)欄目，100元以上的商品
誤：cat_id和price上都加上索引。其實(shí)只能用上一個(gè)索引，他們都是獨(dú)立索引.
2.2 在多列上建立索引后，查詢哪個(gè)列，索引都將發(fā)揮作用
2.2 在多列上建立索引后，查詢哪個(gè)列，索引都將發(fā)揮作用
正解：多列索引上，索引發(fā)揮作用，需要滿足左前綴要求（層層索引）
以index(a,b,c)為例：
語句 索引是否發(fā)揮作用 where a=3 是 where a=3 and b=5 是 where a=3 and b=5 and c=4 是 where b=3 or where c=4 否 where a=3 and c=4 a列能發(fā)揮索引作用，c列不能 where a=3 and b>10 and c=7 a,b能發(fā)揮索引作用，c列不能
高性能索引策略
1.對于innodb而言，因?yàn)楣?jié)點(diǎn)下有數(shù)據(jù)文件，因此節(jié)點(diǎn)的分裂將會變得比較慢，對于innodb的主鍵，盡量用整型，而且是遞增的整型。
2.索引的長度直接影響索引文件的大小，影響增刪改的速度，并間接影響查詢速度（占用內(nèi)存多）。
3.針對列中的值，從左往右截取部分來建索引。
a.截的越短，重復(fù)度越高，區(qū)分越小，索引效果越不好
b.截的越長，雖然區(qū)分度提高，但索引文件變大影響速度
所以盡量在長度上找到一個(gè)平衡點(diǎn)使性能最大化，慣用手法：截取不同長度來測試索引區(qū)分度
區(qū)分度測試：
select count(distinct left(word, 1)) / count(*) from table;
測試完成后可以按測試得出的最優(yōu)長度建立索引
alter table table_name add index word(word(4));
理想的索引
1.查詢頻繁
2.區(qū)分度高
3.長度小
4.盡量覆蓋常用查詢字段
關(guān)于mysql優(yōu)化方法有哪些就分享到這里了，希望以上內(nèi)容可以對大家有一定的參考價(jià)值，可以學(xué)以致用。如果喜歡本篇文章，不妨把它分享出去讓更多的人看到。
