mysql服務器常見的優化方法

在學習過程中整理了9條mysql優化的一些小技巧，這些主要是在寫程序時碰到的一些問題了，各程序員還是需要深入了解了，下面一起來看看吧。

1.查詢時，能不用* 就不用，盡量寫全字段名。

2.索引不是越多越好，每個表控制在6個索引以內。范圍where條件的情況下，索引不起作用，比如where value<100

3.大部分情況連接效率遠大於子查詢，但是有例外。當你對連接查詢的效率都感到不能接受的時候可以試試用子查詢，雖然大部分情況下你會更失望，但總有碰到驚喜的時候不是麼…

4.多用explain 和 profile分析查詢語句

5.有時候可以1條大的SQL可以分成幾個小SQL順序執行，分了吧，速度會快很多。

6.每隔一段時間用alter table table_name engine=innodb;優化表

7.連接時注意:小表 jion 大表的原則

8.學會用explain 和 profile判斷是什麼原因使你的SQL慢。

9.查看慢查詢日志，找出執行時間長的SQL試著優化去吧~~

優化Group By語句
默認情況下，MySQL 排序所有GROUP BY col1，col2，....。查詢的方法如同在查詢中指定ORDER BY col1，col2，...。如果顯式包括一個包含相同的列的ORDER BY子句，MySQL 可以毫不減速地對它進行優化，盡管仍然進行排序。如果查詢包括GROUP BY 但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。
 
優化Order by語句
在某些情況中，MySQL 可以使用一個索引來滿足ORDER BY 子句，而不需要額外的排序。where 條件和order by 使用相同的索引，並且order by 的順序和索引順序相同，並且order by 的字段都是升序或者都是降序。
 
優化insert語句
如果你同時從同一客戶插入很多行，使用多個值表的INSERT 語句。這比使用分開 INSERT 語句快(在一些情況中幾倍)。

 代碼如下 復制代碼 Insert into test values(1,2),(1,3),(1,4)…

 
如果你從不同客戶插入很多行，能通過使用INSERT DELAYED 語句得到更高的速度。Delayed 的含義是讓insert 語句馬上執行，其實數據都被放在內存的隊列中，並沒有真正的寫入磁盤；這比每條語句都分別插入要快的多；LOW_PRIORITY剛好相反，在所有其他用戶對表的讀寫完成後才進行插入。
 
將索引文件和數據文件分在不同的磁盤上存放（利用建表中的選項）；
如果進行批量插入，可以增加bulk_insert_buffer_size 變量值的方法來提高速度，但是，這只能對myisam表使用
 
當從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍；
根據應用情況使用replace 語句代替insert；
根據應用情況使用ignore 關鍵字忽略重復記錄。
 
大批量插入數據
1. 對於Myisam 類型的表，可以通過以下方式快速的導入大量的數據。

 代碼如下 復制代碼 ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;

這兩個命令用來打開或者關閉Myisam 表非唯一索引的更新。在導入大量的數據到一個非空的Myisam 表時，通過設置這兩個命令，可以提高導入的效率。對於導入大量數據到一個空的Myisam 表，默認就是先導入數據然後才創建索引的，所以不用進行設置。
 
2. 而對於Innodb 類型的表，這種方式並不能提高導入數據的效率。對於Innodb 類型的表，我們有以下幾種方式可以提高導入的效率：
a. 因為Innodb 類型的表是按照主鍵的順序保存的，所以將導入的數據按照主鍵的順序排列，可以有效的提高導入數據的效率。如果Innodb 表沒有主鍵，那麼系統會默認創建一個內部列作為主鍵，所以如果可以給表創建一個主鍵，將可以利用這個優勢提高導入數據的效率。
 
b. 在導入數據前執行SET UNIQUE_CHECKS=0，關閉唯一性校驗，在導入結束後執行SETUNIQUE_CHECKS=1，恢復唯一性校驗，可以提高導入的效率。
c. 如果應用使用自動提交的方式，建議在導入前執行SET AUTOCOMMIT=0，關閉自動提交，導入結束後再執行SET AUTOCOMMIT=1，打開自動提交，也可以提高導入的效率。
 
查詢的優化
讀為主可以設置low_priority_updates=1，寫的優先級調低，告訴MYSQL盡量先處理讀求
 
 
為查詢緩存優化你的查詢
大多數的MySQL服務器都開啟了查詢緩存。這是提高性最有效的方法之一，而且這是被MySQL的數據庫引擎處理的。當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個緩存中，這樣，後續的相同的查詢就不用操作表而直接訪問緩存結果了。

這裡最主要的問題是，對於程序員來說，這個事情是很容易被忽略的。因為，我們某些查詢語句會讓MySQL不使用緩存。請看下面的示例：

 代碼如下 復制代碼 // 查詢緩存不開啟
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 
// 開啟查詢緩存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

 
拆分大的 DELETE 或 INSERT 語句
如果你需要在一個在線的網站上去執行一個大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，別的操作都進不來了。

Apache 會有很多的子進程或線程。所以，其工作起來相當有效率，而我們的服務器也不希望有太多的子進程，線程和數據庫鏈接，這是極大的占服務器資源的事情，尤其是內存。

如果你把你的表鎖上一段時間，比如30秒鐘，那麼對於一個有很高訪問量的站點來說，這30秒所積累的訪問進程/線程，數據庫鏈接，打開的文件數，可能不僅僅會讓你泊WEB服務Crash，還可能會讓你的整台服務器馬上?炝恕?/p>

所以，如果你有一個大的處理，你定你一定把其拆分，使用 LIMIT 條件是一個好的方法。下面是一個示例：

 代碼如下 復制代碼

while (1) {
    //每次只做1000條
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 沒得可刪了，退出！
        break;
    }
    // 每次都要休息一會兒
    usleep(50000);
}

 

where語句的優化
1.盡量避免在 where 子句中對字段進行表達式操作

 代碼如下 復制代碼 select id from uinfo_jifen where jifen/60 > 10000;
優化後:
Select id from uinfo_jifen where jifen>600000;

2.應盡量避免在where子句中對字段進行函數操作，這將導致mysql放棄使用索引

 代碼如下 復制代碼

select uid from imid where datediff(create_time,'2011-11-22')=0
優化後
select uid from imid where create_time> ='2011-11-21‘ and create_time<‘2011-11-23’;

 

索引的優化
MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE。

盡量不要寫!=或者<>的sql，用between或> and <代替，否則可能用不到索引

Order by 、Group by 、Distinct 最好在需要這個列上建立索引，利於索引排序

盡量利用mysql索引排序

沒辦法的情況下，使用強制索引Force index(index_name)

盡量避勉innodb用非常大尺寸的字段作為主鍵

較頻繁的作為查詢條件的字段應該創建索引;

選擇性高的字段比較適合創建索引;

作為表關聯字段一般都需要創索引.

更新非常頻繁的字段不適合創建索引;

不會出現在 WHERE 子句中的字段不該創建索引.

選擇性太低的字段不適合單獨創建索引

 

盡量不要用子查詢

 代碼如下 復制代碼

mysql> explain select uid_,count(*) from smember_6 where uid_ in (select uid_ from alluid) group by uid_;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+
| 1 | PRIMARY | smember_6 | index | NULL | PRIMARY | 8 | NULL | 53431264 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | alluid | ALL | NULL | NULL | NULL | NULL | 2448 | Using where |

--優化後

| mysql> explain select a.uid_,count(*) from smember_6 a,alluid b where a.uid_=b.uid_ group by uid_;
+----+-------------+-------+------+---------------+---------+---------+------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+------------+------+---------------------------------+
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2671 | Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY | PRIMARY | 4 | ssc.b.uid_ | 1 | Using index

 

 

Join的優化
如果你的應用程序有很多 JOIN 查詢，你應該確認兩個表中Join的字段是被建過索引的。這樣，MySQL內部會啟動為你優化Join的SQL語句的機制。
而且，這些被用來Join的字段，應該是相同的類型的。例如：如果你要把 DECIMAL 字段和一個 INT 字段Join在一起，MySQL就無法使用它們的索引。對於那些STRING類型，還需要有相同的字符集才行。（兩個表的字符集有可能不一樣）
 

表的優化

盡可能的使用 NOT NULL
除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的字段保持 NOT NULL。
不要以為 NULL 不需要空間，其需要額外的空間，並且，在你進行比較的時候，你的程序會更復雜。
當然，這裡並不是說你就不能使用NULL了，現實情況是很復雜的，依然會有些情況下，你需要使用NULL值。
下面摘自MySQL自己的文檔：
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

explain 分析

select ….
變體：
1.explain extended select ….
將執行計劃“反編譯”成select語句；
運行show warnings 可以得到被mysql優化器優化後的語句
2.explain partitions select …
用於分區表的explain
運行結果含義：
type： all ，index，range，ref，eq_ref,const,system null  從左到右，最差到最好；
all: full table scan ;mysql將遍歷全表以找到匹配的行；
index ： index scan; index 和 all的區別在於index類型只遍歷索引；
range：索引范圍掃描，對索引的掃描開始於某一點，返回匹配值的行，常見與between ，< ,>等查詢；
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，常見於使用非唯一索引即唯一索引的非唯一前綴進行查找；
eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配，常用於主鍵或者唯一索引掃描；
const，system：當mysql對某查詢某部分進行優化，並轉為一個常量時，使用這些訪問類型。如果將主鍵置於where列表中，mysql就能將該查詢轉化為一個常量。
possible keys：
指出mysql能使用哪個索引在表中找到行，查詢涉及到的字段若存在索引，則該索引被列出，但是不一定被查詢使用。
key：顯示mysql在查詢中實際使用的索引，若沒有使用索引，則顯示為null。
key_len:表示索引中使用的字節數，可以通過該列計算查詢中使用的索引的長度。key_len顯示的值為索引字段的最大長度，並非實際使用長度。即key_len是根據表定義計算而得，不是通過表內檢索出的。
ref：表示上述表的連接匹配條件，即哪些列或者常量被用於查找索引上的值；
rows：表示mysql根據表統計信息以及索引選用情況，估算的找到所需記錄所需要的行數；
extra：包含不適合在其他列中顯示但是十分重要的額外信息
a：using index：該值表示相迎的select操作中使用了覆蓋索引（cover index）
b：using where：表示mysql服務器再存儲引擎受到記錄後進行“後過濾”；
c：using temporary ：表示mysql需要使用臨時表來存儲結果集，常見於排序和分組查詢；
d：using filesort：mysql中無法利用索引完成排序，稱為“文件排序”；
explain局限：
explain不會告訴你關於觸發器，存儲過程的信息或者用戶自定義的函數對查詢的影響情況。
explain不會考慮cache。