這篇文章主要介紹了MySQL優(yōu)化方法有哪些，具有一定借鑒價值，需要的朋友可以參考下。下面就和我一起來看看mysql優(yōu)化方法吧。

五通橋ssl適用于網(wǎng)站、小程序/APP、API接口等需要進行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場價格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

該命令可獲取當前查詢數(shù)量等信息，定時輪詢并將結(jié)果重定向到文本中，然后處理成圖表。

三、處理對策

1.若是規(guī)律性出現(xiàn)查詢慢，考慮緩存雪崩問題。

對于該問題只需將緩存的失效時間處理成不要相近時間同時失效，失效時間盡量離散化，或者集中到午夜失效。

2.若非規(guī)律性查詢緩慢，考慮設(shè)計缺乏優(yōu)化

處理方法：

a：開啟profiling記錄查詢操作，并獲取語句執(zhí)行詳細信息

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 index

c：相關(guān)優(yōu)化手法

表的優(yōu)化與列類型選擇

列選擇原則：

1：字段類型優(yōu)先級 整型 > date,time > char,varchar > blob

原因：整型,time運算快，節(jié)省空間

char/varchar要考慮字符集的轉(zhuǎn)換與排序時的校對集，速度慢

blob無法使用內(nèi)存臨時表

2：夠用就行，不要慷慨(如 smallint,varchar(N))

原因：大的字段浪費內(nèi)存，影響速度

以varchar(10), varchar(300)存儲的內(nèi)容相同，但在表聯(lián)查時，varchar(300)要花更多內(nèi)存

3：盡量避免使用NULL

原因：NULL不利于索引，要用特殊的字節(jié)來標注.

在磁盤上占據(jù)的空間其實更大

索引優(yōu)化策略

1.索引類型

1.1 B-tree索引(排好序的快速查找結(jié)構(gòu))

注：Myisam,innodb中，默認用的是B-tree索引

1.2 hash索引

在memory表里，默認是hash索引，hash的理論查詢時間復(fù)查度為O（1）

疑問：既然hash索引如此高效，為何不都用他？

a.hash函數(shù)計算后的結(jié)果是隨機的，如果是在磁盤上放置數(shù)據(jù)，以主鍵為id為例，那么隨著id的增長，id對應(yīng)的行，在磁盤上隨機放置。

b.無法對范圍查詢進行優(yōu)化

c.無法利用前綴索引，比如在b-tree中，field列的值為“helloworld”，索引查詢xx=hello/xx=helloworld都可以利用索引（左前綴索引），但hash索引無法做到，因為hash(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個欄目，100元以上的商品

誤：cat_id和price上都加上索引。其實只能用上一個索引，他們都是獨立索引.

2.2 在多列上建立索引后，查詢哪個列，索引都將發(fā)揮作用

2.2 在多列上建立索引后，查詢哪個列，索引都將發(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而言，因為節(jié)點下有數(shù)據(jù)文件，因此節(jié)點的分裂將會變得比較慢，對于innodb的主鍵，盡量用整型，而且是遞增的整型。

2.索引的長度直接影響索引文件的大小，影響增刪改的速度，并間接影響查詢速度（占用內(nèi)存多）。

3.針對列中的值，從左往右截取部分來建索引。

a.截的越短，重復(fù)度越高，區(qū)分越小，索引效果越不好

b.截的越長，雖然區(qū)分度提高，但索引文件變大影響速度

所以盡量在長度上找到一個平衡點使性能最大化，慣用手法：截取不同長度來測試索引區(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)容可以對大家有一定的參考價值，可以學以致用。如果喜歡本篇文章，不妨把它分享出去讓更多的人看到。

本文名稱：mysql優(yōu)化方法有哪些
網(wǎng)站URL：http://jinyejixie.com/article4/ggshoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁設(shè)計公司、標簽優(yōu)化、商城網(wǎng)站、網(wǎng)站內(nèi)鏈、網(wǎng)站營銷、網(wǎng)站改版

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)