一.測試篇

1.測試目的,就是量化找出短板(基礎參數配置)2.測試三大指標 IOPS:每秒處理的IO請求數，即IO響應速度（注意和IO吞吐量的區別） QPS:每秒請求（查詢）次數 TPS:每秒事務數3.測試工具 mysqlslap(略) sysbench tpcc-mysql

二.mysql性能調優的思路

1.確定問題范圍 a.是周期性的變化還是偶爾問題? b.是服務器整體性能的問題, 還是某單條語句的問題? c.具體到單條語句, 這條語句是在等待上花的時間,還是查詢上花的時間.2.定位問題手段 a.通過 mysqladmin 判斷服務器整體系統負載 b.通過 show processlist/或慢查詢日志 觀察長時間運行語句狀態 c.通過 profile + explan 定位單個語句慢的位置3.值得注意的mysql線程狀態:
[mysql@mysql01 ~]$ mysql -uroot -p123456 -e 'show processlist\G'|grep State:|sort|uniq
converting HEAP to MyISAM 查詢結果太大時,把結果放在磁盤 create tmp table 創建臨時表(如group時儲存中間結果) Copying to tmp table on disk 把內存臨時表復制到磁盤 locked 被其他查詢鎖住 注：通過mysqladmin+debug確定locked的詳細信息

三.臨時表的使用規則

在處理請求的某些場景中,服務器創建內部臨時表. 即表以MEMORY引擎在內存中處理,或以MyISAM引擎儲存在磁盤上處理.如果表過大,服務器可能會把內存中的臨時表轉存在磁盤上.用戶不能直接控制服務器內部用內存還是磁盤存儲臨時表--什么情況下產生內存臨時表? 1: group by 的列和order by 的列不同時 2: 表聯查時,取A表的內容,group/order by另外表的列 3: distinct 和 order by 一起使用時 4: 開啟了 SQL_SMALL_RESULT 選項 5：union合并查詢時會用到臨時表--什么情況下臨時表寫到磁盤上? 1:取出的列含有text/blob類型時 ---內存表儲存不了text/blob類型 2:在group by 或distinct的列中存在>512字節的string列 3:select 中含有>512字節的string列,同時又使用了union或union all語句

四.表的優化與列類型選擇

表的優化 1:定長與變長分離核心且常用字段,宜建成定長,放在一張表.如 id int, 占4個字節, char(4) 占4個字符長度,time 而varchar, text,blob,這種變長字段,適合單放一張表, 用主鍵與核心表關聯起來. 2:常用字段和不常用字段要分離.需要結合網站具體的業務來分析,分析字段的查詢場景,查詢頻度低的字段,單拆出來. 3:合理添加冗余字段列選擇原則 1:字段類型優先級 整型 > date,time > enum,char>varchar > blob整型: 定長,沒有國家/地區之分,沒有字符集的差異time：定長,運算快,節省空間. （All date and time columns shall be INT UNSIGNED NOT NULL, and shall store a Unix timestamp in UTC）enum: 能起來約束值的目的, 內部用整型來存儲,但與char聯查時,內部要經歷串與值的轉化Char：定長, 考慮字符集和(排序)校對集varchar, 不定長 要考慮字符集的轉換與排序時的校對集,速度慢.text/Blob 無法使用內存臨時表 2:夠用就行,不要慷慨 大的字段浪費內存,影響速度 3:盡量避免用NULL()NULL不利于索引,要用特殊的字節來標注.索引文件在磁盤上占據的空間其實更大.（mysql5.5里,關于null已經做了優化,大小區別已不明顯）Enum列的說明 1: enum列在內部是用整型來儲存的 2: enum列與enum列相關聯速度最快 3: enum列比(var)char 的弱勢---在碰到與char關聯時,要轉化. 要花時間. 4: 優勢在于,當char非常長時,enum依然是整型固定長度.當查詢的數據量越大時,enum的優勢越明顯. 5: enum與char/varchar關聯 ,因為要轉化,速度要比enum->enum,char->char要慢,

五.索引優化策略

多列索引上,索引發揮作用,需要滿足左前綴要求. 以 index(a,b,c) 為例, ----------------------------------------------------------------- 語句 | 索引是否發揮作用 ----------------------------------------------------------------- Where a=3 | 是,只使用了a列 Where a=3 and b=5 | 是,使用了a,b列 Where a=3 and b=5 and c=4 | 是,使用了abc Where b=3 / where c=4 | 否 Where a=3 and c=4 | a列能發揮索引,c不能 Where a=3 and b>10 and c=7 | A能利用,b能利用, C不能利用 where a=3 and b like ‘xxxx%’ and c=7 | A能用,B能用,C不能用 -----------------------------------------------------------------假設某個表有一個聯合索引（c1,c2,c3,c4）一下——只能使用該聯合索引的c1,c2,c3部分 A where c1=x and c2=x and c4>x and c3=x B where c1=x and c2=x and c4=x order by c3 C where c1=x and c4= x group by c3,c2 D where c1=x and c5=x order by c2,c3 E where c1=x and c2=x and c5=? order by c2,c3create table t4 ( c1 tinyint(1) not null default 0, c2 tinyint(1) not null default 0, c3 tinyint(1) not null default 0, c4 tinyint(1) not null default 0, c5 tinyint(1) not null default 0, index c1234(c1,c2,c3,c4) ); insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);對于A: c1=x and c2=x and c4>x and c3=x <==等價==> c1=x and c2=x and c3=x and c4>x 因此 c1,c2,c3,c4都能用上. 如下: mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t4type: range possible_keys: c1234key: c1234key_len: 4 #可以看出c1,c2,c3,c4索引都用上ref: NULLrows: 1Extra: Using where 對于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3 c1 ,c2索引用上了,在c2用到索引的基礎上,c3是排好序的,因此不用額外排序. 而c4沒發揮作用. mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t4type: ref possible_keys: c1234key: c1234key_len: 2ref: const,constrows: 1Extra: Using where 1 row in set (0.00 sec)mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t4type: ref possible_keys: c1234key: c1234key_len: 2ref: const,constrows: 1Extra: Using where; Using filesort 1 row in set (0.00 sec)對于 C: 只用到c1索引,因為group by c3,c2的順序無法利用c2,c3索引 mysql> explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t4type: ref possible_keys: c1234key: c1234key_len: 1 #只用到c1,因為先用c3后用c2分組,導致c2,c3索引沒發揮作用ref: constrows: 1Extra: Using where; Using temporary; Using filesort 1 row in set (0.00 sec)mysql> explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t4type: ref possible_keys: c1234key: c1234key_len: 1ref: constrows: 1Extra: Using where 1 row in set (0.00 sec)D語句: C1確定的基礎上,c2是有序的,C2之下C3是有序的,因此c2,c3發揮的排序的作用. 因此,沒用到filesort mysql> explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t4type: ref possible_keys: c1234key: c1234key_len: 1ref: constrows: 1Extra: Using where 1 row in set (0.00 sec)E: 這一句等價與 elect * from t4 where c1=1 and c2=3 and c5=2 order by c3; 因為c2的值既是固定的,參與排序時并不考慮mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G *************************** 1. row ***************************id: 1select_type: SIMPLEtable: t4type: ref possible_keys: c1234key: c1234key_len: 2ref: const,constrows: 1Extra: Using where 1 row in set (0.00 sec)

五.聚簇索引與非聚簇索引

innodb的主索引文件上 直接存放該行數據,稱為聚簇索引,次索引指向對主鍵的引用 myisam的主索引和次索引,都指向物理行(磁盤位置).注意: innodb來說, 1: 主鍵索引 既存儲索引值,又在葉子中存儲行的數據 2: 如果沒有主鍵, 則會Unique key做主鍵 3: 如果沒有unique,則系統生成一個內部的rowid做主鍵.聚簇索引 優勢: 根據主鍵查詢條目比較少時,不用回行(數據就在主鍵節點下) 劣勢: 如果碰到不規則數據插入時,造成頻繁的頁分裂與頁移動.

----《整理自燕十八mysql高性能》

總結

以上是生活随笔為你收集整理的MySQL性能优化笔记整理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。