MySql的最佳化步驟

MySql最佳化的一般步驟：

1.通過show status 命令瞭解各種sql的執行效率

　　SHOW STATUS提供msyql伺服器的狀態資訊

　　一般情況下，我們只需要瞭解以”Com”開頭的指令

　　show session status like ‘Com%’：顯示當前的串連的統計結果

　　show global status like ‘Com%’ ：顯示自資料庫上次啟動至今的統計結果

　　註：預設是session層級的

　　其中Com_XXX表示XXX語句所執行的次數。
重點注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個參數，可以容易地瞭解到當前資料庫的應用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執行比例是多少。

　　另外，還有幾個參數需要注意下：

　　show status like ‘Connections’// 試圖串連MySQL伺服器的次數

　　show status like ‘Uptime’//伺服器工作的時間（單位秒）

　　show status like ‘Slow_queries’//慢查詢的次數 (預設是10秒中就當做是慢查詢，如所示)

　　

　　a) 如何查詢mysql的慢查詢時間

　　　　Show variables like 'long_query_time';

　　b) 修改mysql 慢查詢時間

　　　　set long_query_time=2//如果查詢時間超過2秒就算作是慢查詢

2. 定位執行效率較低的SQL語句（dql出現問題的機率較dml的大）

　　問題是：如何在一個項目中，找到慢查詢的select語句？

　　答案：mysql支援把慢查詢語句記錄到記錄檔中。程式員需要修改php.ini的設定檔，預設情況下，慢查詢記錄是不開啟的。

　　開啟慢查詢記錄的步驟：

　　開啟 my.ini ,找到 [mysqld] 在其下面添加

　　long_query_time = 2

　　log-slow-queries = D:/mysql/logs/slow.log #設定把日誌寫在那裡，可以為空白，系統會給一個預設的檔案

     例子：我們資料表中有1千萬條的資料量

　　DQL語句：SELECT * FROM order_copy WHERE id=12345;

　　

　　查詢耗時：19s>2s，所以mysql會將該條select語句記錄到慢查詢日誌中

　　SELECT * FROM order_copy WHERE id=12345的執行時間：

　　添加索引前：19s

　　添加索引後：0.08s

3.通過explain分析低效率的SQL語句的執行情況

　　使用explain分析該dql語句：

EXPLAIN SELECT * FROM order_copy WHERE id=12345
會產生如下資訊：
select_type:表示查詢的類型。
table:輸出結果集的表
type:表示表的連線類型(system和const為佳)
possible_keys:表示查詢時，可能使用的索引
key:表示實際使用的索引
key_len:索引欄位的長度
rows:掃描的行數
Extra:執行情況的描述和說明

注意：要盡量避免讓type的結果為all，extra的結果為：using filesort

4.確定問題並採取相應的最佳化措施

• 常用的最佳化措施是添加索引。添加索引，我們不用加記憶體，不用改程式，不用調sql，只要執行個正確的’create index’，查詢速度就可能提高百倍千倍。但是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的I/O。

例如：給欄位id添加索引：

ALTER TABLE order_copy ADD PRIMARY KEY(id)

給1千萬的資料添加primary key 需要耗時： 428秒（7分鐘）

EXPLAIN SELECT * FROM order_copy WHERE id=12345

正是因為給id添加了索引，才使得rows的結果為1

但是索引並不是可以隨便添加的，以下幾種情況需牢記在心：

• 較頻繁的作為查詢條件欄位應該建立索引

　　  select * from order_copy where id = $id

• 唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件

　　 select * from order_copy where sex=’女’

• 更新非常頻繁的欄位不適合建立索引

　　  select * from order_copy where order_state=’未付款’

• 不會出現在WHERE子句中欄位不該建立索引

索引的類型：

• PRIMARY 索引      => 在主鍵上自動建立
• INDEX 索引          => 就是普通索引
• UNIQUE 索引        => 相當於INDEX + Unique
• FULLTEXT            => 只在MYISAM 儲存引擎支援, 目的是全文索引，在內容系統中用的多， 在全英文網站用多(英文詞獨立). 中文資料不常用，意義不大 國內全文索引通常 使用 sphinx 來完成.

索引的使用

• 建立索引 create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
  alter table table_name ADD INDEX [index_name] (index_col_name,...)

　　  添加主鍵(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 聯合主鍵

• 刪除索引 DROP INDEX index_name ON tbl_name;
  alter table table_name drop index index_name;
• 刪除主鍵(索引)比較特別: alter table t_b drop primary key;
• 查詢索引(均可) show index from table_name;
  show keys from table_name;
  desc table_Name;

相關資料：

explain的使用:http://www.cnblogs.com/you-yang/archive/2012/01/18/2325841.html

轉載請註明出處：http://www.cnblogs.com/hongfei/archive/2012/10/20/2732516.html

MySql最佳化的一般步驟：

1.通過show status 命令瞭解各種sql的執行效率

　　SHOW STATUS提供msyql伺服器的狀態資訊

　　一般情況下，我們只需要瞭解以”Com”開頭的指令

　　show session status like ‘Com%’：顯示當前的串連的統計結果

　　show global status like ‘Com%’ ：顯示自資料庫上次啟動至今的統計結果

　　註：預設是session層級的

　　其中Com_XXX表示XXX語句所執行的次數。
重點注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個參數，可以容易地瞭解到當前資料庫的應用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執行比例是多少。

　　另外，還有幾個參數需要注意下：

　　show status like ‘Connections’// 試圖串連MySQL伺服器的次數

　　show status like ‘Uptime’//伺服器工作的時間（單位秒）

　　show status like ‘Slow_queries’//慢查詢的次數 (預設是10秒中就當做是慢查詢，如所示)

　　

　　a) 如何查詢mysql的慢查詢時間

　　　　Show variables like 'long_query_time';

　　b) 修改mysql 慢查詢時間

　　　　set long_query_time=2//如果查詢時間超過2秒就算作是慢查詢

2. 定位執行效率較低的SQL語句（dql出現問題的機率較dml的大）

　　問題是：如何在一個項目中，找到慢查詢的select語句？

　　答案：mysql支援把慢查詢語句記錄到記錄檔中。程式員需要修改php.ini的設定檔，預設情況下，慢查詢記錄是不開啟的。

　　開啟慢查詢記錄的步驟：

　　開啟 my.ini ,找到 [mysqld] 在其下面添加

　　long_query_time = 2

　　log-slow-queries = D:/mysql/logs/slow.log #設定把日誌寫在那裡，可以為空白，系統會給一個預設的檔案

     例子：我們資料表中有1千萬條的資料量

　　DQL語句：SELECT * FROM order_copy WHERE id=12345;

　　

　　查詢耗時：19s>2s，所以mysql會將該條select語句記錄到慢查詢日誌中

　　SELECT * FROM order_copy WHERE id=12345的執行時間：

　　添加索引前：19s

　　添加索引後：0.08s

3.通過explain分析低效率的SQL語句的執行情況

　　使用explain分析該dql語句：

EXPLAIN SELECT * FROM order_copy WHERE id=12345
會產生如下資訊：
select_type:表示查詢的類型。
table:輸出結果集的表
type:表示表的連線類型(system和const為佳)
possible_keys:表示查詢時，可能使用的索引
key:表示實際使用的索引
key_len:索引欄位的長度
rows:掃描的行數
Extra:執行情況的描述和說明

注意：要盡量避免讓type的結果為all，extra的結果為：using filesort

4.確定問題並採取相應的最佳化措施

• 常用的最佳化措施是添加索引。添加索引，我們不用加記憶體，不用改程式，不用調sql，只要執行個正確的’create index’，查詢速度就可能提高百倍千倍。但是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的I/O。

例如：給欄位id添加索引：

ALTER TABLE order_copy ADD PRIMARY KEY(id)

給1千萬的資料添加primary key 需要耗時： 428秒（7分鐘）

EXPLAIN SELECT * FROM order_copy WHERE id=12345

正是因為給id添加了索引，才使得rows的結果為1

但是索引並不是可以隨便添加的，以下幾種情況需牢記在心：

• 較頻繁的作為查詢條件欄位應該建立索引

　　  select * from order_copy where id = $id

• 唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件

　　 select * from order_copy where sex=’女’

• 更新非常頻繁的欄位不適合建立索引

　　  select * from order_copy where order_state=’未付款’

• 不會出現在WHERE子句中欄位不該建立索引

索引的類型：

• PRIMARY 索引      => 在主鍵上自動建立
• INDEX 索引          => 就是普通索引
• UNIQUE 索引        => 相當於INDEX + Unique
• FULLTEXT            => 只在MYISAM 儲存引擎支援, 目的是全文索引，在內容系統中用的多， 在全英文網站用多(英文詞獨立). 中文資料不常用，意義不大 國內全文索引通常 使用 sphinx 來完成.

索引的使用

• 建立索引 create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
  alter table table_name ADD INDEX [index_name] (index_col_name,...)

　　  添加主鍵(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 聯合主鍵

• 刪除索引 DROP INDEX index_name ON tbl_name;
  alter table table_name drop index index_name;
• 刪除主鍵(索引)比較特別: alter table t_b drop primary key;
• 查詢索引(均可) show index from table_name;
  show keys from table_name;
  desc table_Name;

相關資料：

explain的使用:http://www.cnblogs.com/you-yang/archive/2012/01/18/2325841.html

轉載請註明出處：http://www.cnblogs.com/hongfei/archive/2012/10/20/2732516.html