mysql中怎么優(yōu)化sql語句

mysql中怎么優(yōu)化sql語句，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

優(yōu)化目標

　　1.減少 IO 次數(shù)

　　IO永遠是數(shù)據(jù)庫最容易瓶頸的地方，這是由數(shù)據(jù)庫的職責所決定的，大部分數(shù)據(jù)庫操作中超過90%的時間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮，當然，也是收效最明顯的優(yōu)化手段。

　　2.降低 CPU 計算

　　除了 IO 瓶頸之外，SQL優(yōu)化中需要考慮的就是 CPU 運算量的優(yōu)化了。order by, group by,distinct … 都是消耗 CPU 的大戶(這些操作基本上都是 CPU 處理內存中的數(shù)據(jù)比較運算)。當我們的 IO 優(yōu)化做到一定階段之后，降低 CPU 計算也就成為了我們 SQL 優(yōu)化的重要目標

優(yōu)化方法

　　改變 SQL 執(zhí)行計劃

　　明確了優(yōu)化目標之后，我們需要確定達到我們目標的方法。對于 SQL 語句來說，達到上述2個目標的方法其實只有一個，那就是改變 SQL 的執(zhí)行計劃，讓他盡量“少走彎路”，盡量通過各種“捷徑”來找到我們需要的數(shù)據(jù)，以達到 “減少 IO 次數(shù)” 和“降低 CPU 計算” 的目標

常見誤區(qū)

1.count(1)和count(primary_key) 優(yōu)于 count(*)

　　很多人為了統(tǒng)計記錄條數(shù)，就使用 count(1) 和 count(primary_key) 而不是 count(*) ，他們認為這樣性能更好，其實這是一個誤區(qū)。對于有些場景，這樣做可能性能會更差，應為數(shù)據(jù)庫對 count(*) 計數(shù)操作做了一些特別的優(yōu)化。

2.count(column) 和 count(*) 是一樣的

　　這個誤區(qū)甚至在很多的資深工程師或者是 DBA 中都普遍存在，很多人都會認為這是理所當然的。實際上，count(column)和 count(*) 是一個完全不一樣的操作，所代表的意義也完全不一樣。

　　count(column) 是表示結果集中有多少個column字段不為空的記錄

　　count(*) 是表示整個結果集有多少條記錄

3.select a,b from … 比 select a,b,c from … 可以讓數(shù)據(jù)庫訪問更少的數(shù)據(jù)量

　　這個誤區(qū)主要存在于大量的開發(fā)人員中，主要原因是對數(shù)據(jù)庫的存儲原理不是太了解。

　　實際上，大多數(shù)關系型數(shù)據(jù)庫都是按照行(row)的方式存儲，而數(shù)據(jù)存取操作都是以一個固定大小的IO單元(被稱作 block或者 page)為單位，一般為4KB，8KB… 大多數(shù)時候，每個IO單元中存儲了多行，每行都是存儲了該行的所有字段(lob等特殊類型字段除外)。

　　所以，我們是取一個字段還是多個字段，實際上數(shù)據(jù)庫在表中需要訪問的數(shù)據(jù)量其實是一樣的。

　　當然，也有例外情況，那就是我們的這個查詢在索引中就可以完成，也就是說當只取 a,b兩個字段的時候，不需要回表，而c這個字段不在使用的索引中，需要回表取得其數(shù)據(jù)。在這樣的情況下，二者的IO量會有較大差異。

4.order by 一定需要排序操作

　　我們知道索引數(shù)據(jù)實際上是有序的，如果我們的需要的數(shù)據(jù)和某個索引的順序一致，而且我們的查詢又通過這個索引來執(zhí)行，那么數(shù)據(jù)庫一般會省略排序操作，而直接將數(shù)據(jù)返回，因為數(shù)據(jù)庫知道數(shù)據(jù)已經(jīng)滿足我們的排序需求了。

　　實際上，利用索引來優(yōu)化有排序需求的 SQL，是一個非常重要的優(yōu)化手段

　　延伸閱讀：MySQL ORDER BY 的實現(xiàn)分析，MySQL 中 GROUP BY 基本實現(xiàn)原理以及 MySQL DISTINCT 的基本實現(xiàn)原理這3篇文章中有更為深入的分析，尤其是第一篇

5.執(zhí)行計劃中有 filesort 就會進行磁盤文件排序

　　有這個誤區(qū)其實并不能怪我們，而是因為 MySQL 開發(fā)者在用詞方面的問題。filesort 是我們在使用 explain 命令查看一條SQL 的執(zhí)行計劃的時候可能會看到在 “Extra” 一列顯示的信息。

　　實際上，只要一條 SQL 語句需要進行排序操作，都會顯示“Using filesort”，這并不表示就會有文件排序操作。

基本原則

1.盡量少 join

　　MySQL 的優(yōu)勢在于簡單，但這在某些方面其實也是其劣勢。MySQL 優(yōu)化器效率高，但是由于其統(tǒng)計信息的量有限，優(yōu)化器工作過程出現(xiàn)偏差的可能性也就更多。對于復雜的多表 Join，一方面由于其優(yōu)化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現(xiàn)離 Oracle 等關系型數(shù)據(jù)庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優(yōu)于這些數(shù)據(jù)庫前輩。

2.盡量少排序

　　排序操作會消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL 的響應時間。

　　對于MySQL來說，減少排序有多種辦法，比如：

　　上面誤區(qū)中提到的通過利用索引來排序的方式進行優(yōu)化

　　減少參與排序的記錄條數(shù)

　　非必要不對數(shù)據(jù)進行排序

　　…

3.盡量避免 select *

　　很多人看到這一點后覺得比較難理解，上面不是在誤區(qū)中剛剛說 select 子句中字段的多少并不會影響到讀取的數(shù)據(jù)嗎?

　　是的，大多數(shù)時候并不會影響到 IO 量，但是當我們還存在 order by 操作的時候，select 子句中的字段多少會在很大程度上影響到我們的排序效率，這一點可以通過我之前一篇介紹 MySQL ORDER BY 的實現(xiàn)分析的文章中有較為詳細的介紹。

　　此外，上面誤區(qū)中不是也說了，只是大多數(shù)時候是不會影響到 IO 量，當我們的查詢結果僅僅只需要在索引中就能找到的時候，還是會極大減少 IO 量的。

4.盡量用 join 代替子查詢

　　雖然 Join 性能并不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優(yōu)勢。MySQL 的子查詢執(zhí)行計劃一直存在較大的問題，雖然這個問題已經(jīng)存在多年，但是到目前已經(jīng)發(fā)布的所有穩(wěn)定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就 承認這一問題，并且承諾盡快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。

5.盡量少 or

　　當 where 子句中存在多個條件以“或”并存的時候，MySQL 的優(yōu)化器并沒有很好的解決其執(zhí)行計劃優(yōu)化問題，再加上MySQL 特有的 SQL 與 Storage 分層架構方式，造成了其性能比較低下，很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。

6.盡量用 union all 代替 union

　　union 和 union all 的差異主要是前者需要將兩個(或者多個)結果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的 CPU 運算，加大資源消耗及延遲。所以當我們可以確認不可能出現(xiàn)重復結果集或者不在乎重復結果集的時候，盡量使用 union all 而不是 union。

7.盡量早過濾

　　這一優(yōu)化策略其實最常見于索引的優(yōu)化設計中(將過濾性更好的字段放得更靠前)。

　　在 SQL 編寫中同樣可以使用這一原則來優(yōu)化一些 Join 的 SQL。比如我們在多個表進行分頁數(shù)據(jù)查詢的時候，我們最好是能夠在一個表上先過濾好數(shù)據(jù)分好頁，然后再用分好頁的結果集與另外的表 Join，這樣可以盡可能多的減少不必要的 IO 操作，大大節(jié)省 IO 操作所消耗的時間。

8.避免類型轉換

　　這里所說的“類型轉換”是指 where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致的時候發(fā)生的類型轉換：

　　人為在column_name 上通過轉換函數(shù)進行轉換

　　直接導致 MySQL(實際上其他數(shù)據(jù)庫也會有同樣的問題)無法使用索引，如果非要轉換，應該在傳入的參數(shù)上進行轉換

　　由數(shù)據(jù)庫自己進行轉換

　　如果我們傳入的數(shù)據(jù)類型和字段類型不一致，同時我們又沒有做任何類型轉換處理，MySQL 可能會自己對我們的數(shù)據(jù)進行類型轉換操作，也可能不進行處理而交由存儲引擎去處理，這樣一來，就會出現(xiàn)索引無法使用的情況而造成執(zhí)行計劃問題。

9.優(yōu)先優(yōu)化高并發(fā)的 SQL，而不是執(zhí)行頻率低某些“大”SQL

　　對于破壞性來說，高并發(fā)的 SQL 總是會比低頻率的來得大，因為高并發(fā)的 SQL 一旦出現(xiàn)問題，甚至不會給我們任何喘息的機會就會將系統(tǒng)壓跨。而對于一些雖然需要消耗大量 IO 而且響應很慢的 SQL，由于頻率低，即使遇到，最多就是讓整個系統(tǒng)響應慢一點，但至少可能撐一會兒，讓我們有緩沖的機會。

10.從全局出發(fā)優(yōu)化，而不是片面調整

　　SQL 優(yōu)化不能是單獨針對某一個進行，而應充分考慮系統(tǒng)中所有的 SQL，尤其是在通過調整索引優(yōu)化 SQL 的執(zhí)行計劃的時候，千萬不能顧此失彼，因小失大。

11.盡可能對每一條運行在數(shù)據(jù)庫中的SQL進行 explain

優(yōu)化 SQL，需要做到心中有數(shù)，知道 SQL 的執(zhí)行計劃才能判斷是否有優(yōu)化余地，才能判斷是否存在執(zhí)行計劃問題。在對數(shù)據(jù)庫中運行的 SQL 進行了一段時間的優(yōu)化之后，很明顯的問題 SQL 可能已經(jīng)很少了，大多都需要去發(fā)掘，這時候就需要進行大量的 explain 操作收集執(zhí)行計劃，并判斷是否需要進行優(yōu)化。

優(yōu)化Group By語句

默認情況下，MySQL 排序所有GROUP BY col1，col2，....。查詢的方法如同在查詢中指定ORDER BY col1，col2，...。如果顯式包括一個包含相同的列的ORDER BY子句，MySQL 可以毫不減速地對它進行優(yōu)化，盡管仍然進行排序。如果查詢包括GROUP BY 但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。

優(yōu)化Order by語句

在某些情況中，MySQL 可以使用一個索引來滿足ORDER BY 子句，而不需要額外的排序。where 條件和order by 使用相同的索引，并且order by 的順序和索引順序相同，并且order by 的字段都是升序或者都是降序。

優(yōu)化insert語句

如果你同時從同一客戶插入很多行，使用多個值表的INSERT 語句。這比使用分開 INSERT 語句快(在一些情況中幾倍)。Insert into test values(1,2),(1,3),(1,4)…

如果你從不同客戶插入很多行，能通過使用INSERT DELAYED 語句得到更高的速度。Delayed 的含義是讓insert 語句馬上執(zhí)行，其實數(shù)據(jù)都被放在內存的隊列中，并沒有真正的寫入磁盤；這比每條語句都分別插入要快的多；LOW_PRIORITY剛好相反，在所有其他用 戶對表的讀寫完成后才進行插入。

將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放（利用建表中的選項）；

如果進行批量插入，可以增加bulk_insert_buffer_size 變量值的方法來提高速度，但是，這只能對myisam表使用

當從一個文本文件裝載一個表時，使用LOAD DATA INFILE。這通常比使用很多INSERT語句快20倍；

根據(jù)應用情況使用replace 語句代替insert；

根據(jù)應用情況使用ignore 關鍵字忽略重復記錄。

大批量插入數(shù)據(jù)

1. 對于Myisam 類型的表，可以通過以下方式快速的導入大量的數(shù)據(jù)。

ALTER TABLE tblname DISABLE KEYS;

loading the data

ALTER TABLE tblname ENABLE KEYS;

這兩個命令用來打開或者關閉Myisam 表非唯一索引的更新。在導入大量的數(shù)據(jù)到一個非空的Myisam 表時，通過設置這兩個命令，可以提高導入的效率。對于導入大量數(shù)據(jù)到一個空的Myisam 表，默認就是先導入數(shù)據(jù)然后才創(chuàng)建索引的，所以不用進行設置。

2. 而對于Innodb 類型的表，這種方式并不能提高導入數(shù)據(jù)的效率。對于Innodb 類型的表，我們有以下幾種方式可以提高導入的效率：

a. 因為Innodb 類型的表是按照主鍵的順序保存的，所以將導入的數(shù)據(jù)按照主鍵的順序排列，可以有效的提高導入數(shù)據(jù)的效率。如果Innodb 表沒有主鍵，那么系統(tǒng)會默認創(chuàng)建一個內部列作為主鍵，所以如果可以給表創(chuàng)建一個主鍵，將可以利用這個優(yōu)勢提高導入數(shù)據(jù)的效率。
 

b. 在導入數(shù)據(jù)前執(zhí)行SET UNIQUE_CHECKS=0，關閉唯一性校驗，在導入結束后執(zhí)行SETUNIQUE_CHECKS=1，恢復唯一性校驗，可以提高導入的效率。

c. 如果應用使用自動提交的方式，建議在導入前執(zhí)行SET AUTOCOMMIT=0，關閉自動提交，導入結束后再執(zhí)行SET AUTOCOMMIT=1，打開自動提交，也可以提高導入的效率。

查詢的優(yōu)化

讀為主可以設置low_priority_updates=1，寫的優(yōu)先級調低，告訴MYSQL盡量先處理讀求

為查詢緩存優(yōu)化你的查詢

大多數(shù)的MySQL服務器都開啟了查詢緩存。這是提高性最有效的方法之一，而且這是被MySQL的數(shù)據(jù)庫引擎處理的。當有很多相同的查詢被執(zhí)行了多次的時候，這些查詢結果會被放到一個緩存中，這樣，后續(xù)的相同的查詢就不用操作表而直接訪問緩存結果了。

這里最主要的問題是，對于程序員來說，這個事情是很容易被忽略的。因為，我們某些查詢語句會讓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 語句

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

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

如果你把你的表鎖上一段時間，比如30秒鐘，那么對于一個有很高訪問量的站點來說，這30秒所積累的訪問進程/線程，數(shù)據(jù)庫鏈接，打開的文件數(shù)，可能不僅僅會讓你泊WEB服務Crash，還可能會讓你的整臺服務器馬上掛了。

所以，如果你有一個大的處理，你定你一定把其拆分，使用 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語句的優(yōu)化

1.盡量避免在 where 子句中對字段進行表達式操作
 select id from uinfo_jifen where jifen/60 > 10000;
優(yōu)化后:
Select id from uinfo_jifen where jifen>600000;

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

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

索引的優(yōu)化

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

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

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

盡量利用mysql索引排序

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

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

較頻繁的作為查詢條件的字段應該創(chuàng)建索引;

選擇性高的字段比較適合創(chuàng)建索引;

作為表關聯(lián)字段一般都需要創(chuàng)索引.

更新非常頻繁的字段不適合創(chuàng)建索引;

不會出現(xiàn)在 WHERE 子句中的字段不該創(chuàng)建索引.

選擇性太低的字段不適合單獨創(chuàng)建索引

盡量不要用子查詢

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 |

--優(yōu)化后

| 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的優(yōu)化

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

表的優(yōu)化

盡可能的使用 NOT NULL
除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的字段保持 NOT NULL。
不要以為 NULL 不需要空間，其需要額外的空間，并且，在你進行比較的時候，你的程序會更復雜。
當然，這里并不是說你就不能使用NULL了，現(xiàn)實情況是很復雜的，依然會有些情況下，你需要使用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.”

固定長度的表會更快

如果表中的所有字段都是“固定長度”的，整個表會被認為是 “static” 或 “fixed-length”。 例如，表中沒有如下類型的字段：VARCHAR，TEXT，BLOB。只要你包括了其中一個這些字段，那么這個表就不是“固定長度靜態(tài)表”了，這樣，MySQL 引擎會用另一種方法來處理。
固定長度的表會提高性能，因為MySQL搜尋得會更快一些，因為這些固定的長度是很容易計算下一個數(shù)據(jù)的偏移量的，所以讀取的自然也會很快。而如果字段不是定長的，那么，每一次要找下一條的話，需要程序找到主鍵。
并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要分配那么多的空間。

垂直分割

"垂直分割"是一種把數(shù)據(jù)庫中的表按列變成幾張表的方法，這樣可以降低表的復雜度和字段的數(shù)目，從而達到優(yōu)化的目的。（以前，在銀行做過項目，見過一張表有100多個字段，很恐怖）

示例一：在Users表中有一個字段是家庭地址，這個字段是可選字段，相比起，而且你在數(shù)據(jù)庫操作的時候除了個人信息外，你并不需要經(jīng)常讀取或是改 寫這個字段。那么，為什么不把他放到另外一張表中呢？ 這樣會讓你的表有更好的性能，大家想想是不是，大量的時候，我對于用戶表來說，只有用戶ID，用戶名，口令，用戶角色等會被經(jīng)常使用。小一點的表總是會有 好的性能。

示例二： 你有一個叫 “l(fā)ast_login” 的字段，它會在每次用戶登錄時被更新。但是，每次更新時會導致該表的查詢緩存被清空。所以，你可以把這個字段放到另一個表中，這樣就不會影響你對用戶 ID，用戶名，用戶角色的不停地讀取了，因為查詢緩存會幫你增加很多性能。

另外，你需要注意的是，這些被分出去的字段所形成的表，你不會經(jīng)常性地去Join他們，不然的話，這樣的性能會比不分割時還要差，而且，會是極數(shù)級的下降。

關于mysql中怎么優(yōu)化sql語句問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業(yè)資訊頻道了解更多相關知識。