分享Mysql優化思路

一、整體最佳化思路

首先建構腳本觀察查詢數，連接數等數據，確定環境原因以及內部SQL執行原因，然後根據具體原因做具體處理。

推薦：《mysql影片教學》

二、建立腳本觀察狀態

mysqladmin -uroot -p  ext \G

 

該命令可獲取當前查詢數量等信息，定時輪詢並將結果重定向到文字中，然後處理成圖表。

三、處理對策

#1.若是規律性出現查詢慢，考慮快取雪崩問題。

對於此問題只需將快取的失效時間處理成不要相近時間同時失效，失效時間盡量離散化，或集中到午夜失效。

2.若非規律性查詢緩慢，考慮設計缺乏最佳化

處理方法：

a：開啟profiling記錄查詢操作，並取得語句執行詳細資訊

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 查看語句執行情況，索引使用，掃描範圍等等

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：相關最佳化手法

表格的最佳化與列類型選擇

列選擇原則：

1：欄位類型優先權整數> date,time > char,varchar > blob

#原因：整數,time運算快，節省空間

char/varchar要考慮字元集的轉換與排序時的校對集，速度慢

blob無法使用記憶體暫存表

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

原因：大的字段浪費內存，影響速度

以varchar(10), varchar( 300)儲存的內容相同，但在表格聯查時，varchar(300)要花更多記憶體

3：盡量避免使用NULL

原因：NULL不利於索引，要用特殊的位元組來標註.

在磁碟上佔據的空間其實更大

#索引最佳化策略

##1.索引類型

1.1 B-tree索引(排好序的快速尋找結構)

註：Myisam,innodb中，預設用的是B-tree索引

1.2 hash索引

在memory表裡，預設是hash索引，hash的理論查詢時間複查度為O（1）

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

a.hash函數計算後的結果是隨機的，如果是在磁碟上放置數據，以主鍵為id為例，那麼隨著id的增長，id對應的行，在磁碟上隨機放置。

b.無法對範圍查詢進行最佳化

c.無法利用前綴索引，例如在b-tree中，field列的值為“helloworld”，索引查詢xx=hello/xx =helloworld都可以利用索引（左前綴索引），但hash索引無法做到，因為hash(hello)與hash(helloworld)並無關聯關係。

d.排序也無法最佳化

e.必須回行，透過索引拿到資料位置，必須回到表中取資料.

2.b-tree索引的常見誤解

2.1 在where條件常用的列上都加上索引

例：where cat_id=3 and price>100； //查詢第3個欄位，100元以上的商品

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

2.2 在多列上建立索引後，查詢哪個列，索引都會發揮作用

2.2 在多列上建立索引後，查詢哪個列，索引都會發揮作用

正解：多列索引上，索引發揮作用，需要滿足左前綴要求（層層索引）

以index(a, b,c)為例：

语句 索引是否发挥作用
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列能发挥索引作用，c列不能
where a=3 and b>10 and c=7 a,b能发挥索引作用，c列不能

高效能索引策略

#1.對於innodb而言，因為節點下有資料文件，因此節點的分割將會變得比較慢，對於innodb的主鍵，盡量用整數，而且是遞增的整數。

2.索引的長度直接影響索引檔案的大小，影響增刪改的速度，並間接影響查詢速度（佔用記憶體多）。

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

a.截的越短，重複度越高，區分越小，索引效果越不好

b.截的越長，雖然區分度提高，但索引檔變大影響速度

所以盡量在長度上找到一個平衡點使效能最大化，慣用手法：截取不同長度來測試索引區分度

區分度測試： 

select count(distinct left(word, 1)) / count(*) from table;

測試完成後可依測試所得的最優長度建立索引 

alter table table_name add index word(word(4));

理想的索引

1.查詢頻繁

2.區分度高

3.長度小

4.盡量覆寫常用查詢欄位

以上是分享Mysql優化思路的詳細內容。更多資訊請關注PHP中文網其他相關文章！