文章目錄一、前言二、慢查詢概要2.1 第一步，慢查詢分析之前的配置2.1.1 方式一：修改my.ini2.1.2 方式二：修改數據庫2.2 第二步，找到執行慢的sql語句2.3 第三步，找到原因兩種方式之一，explain分析，explain各個字段的解釋2.4 第四步，找到原因兩種方式之一，profile分析，找到慢查詢的本質原因，profile各個字段的解釋2.4.1 explain制造慢sq

文章目錄

• 一、前言
• 二、慢查詢概要
• • 2.1 第一步，慢查詢分析之前的配置
  • • 2.1.1 方式一：修改my.ini
    • 2.1.2 方式二：修改數據庫
  • 2.2 第二步，找到執行慢的sql語句
  • 2.3 第三步，找到原因兩種方式之一，explain分析，explain各個字段的解釋
  • 2.4 第四步，找到原因兩種方式之一，profile分析，找到慢查詢的本質原因，profile各個字段的解釋
  • • 2.4.1 explain制造慢sql語句，profile找到慢的sql語句
    • 2.4.2 show profile for query 具體id
    • • 2.4.2.1 sending data發送數據慢，就是網絡IO
      • 2.4.2.2 Copying to tmp table臨時表慢
    • 2.4.3 查看cpu、內存和IO信息
• 三、SQL優化
• • 3.1 第一，明確SQL優化兩個目標（IO成本+CPU成本）
  • 3.2 第二，SQL執行的11個步驟，搞懂MySQL的語句執行順序（11條）
  • 3.3 第三，表設計（表設計5條 + 字段設計5條）
  • • 3.3.1 表設計層面（5條）
    • 3.3.2 字段設計層面（5條）
  • 3.4 第四，高效的SQL語句（索引+單表優化+多表優化）
  • • 3.4.1 索引（索引兩方向：優先使用索引，避免索引失效）
    • • 3.4.1.1 避免索引失效
      • 3.4.1.2 優先使用索引
    • 3.4.2 單表查詢 10點
    • • 1、單表查詢：避免索引失效，不要在 where 子句中對字段進行 null 值判斷，這會進行全表掃描
      • 2、單表查詢，開啟慢查詢日志，定位慢查詢的SQL語句
      • 3、單表查詢，數據庫壓力轉移到Java后臺
      • 4、單表查詢，where 查詢條件數量 + 查詢條件順序
      • 5、單表查詢，返回值和關鍵字大寫優化（select *，limit）
      • 6、單表查詢：計數優化，不要使用 count(列名)或 count(常量)來替代 count(*)
      • 7、單表查詢：去重優化，能用GROUP BY的就不用DISTINCT
      • 8、單表查詢，LIMIT優化，巧借索引優化orderby排序
      • 9、單表查詢：排序優化，order by 字段加索引
      • 10、單表查詢：分組優化，group by 調節順序為復合索引順序
    • 3.4.3 多表連接查詢 4點
    • • 1、多表連接，五個：用 join 代替子查詢，連接字段類型一致，連接字段加索引，主表是小表，orderby和groupby字段在主表，否則無用
      • 2、多表查詢，很多時候用exists代替in是一個好的選擇（理由：雖然in與exists效率在不同場景效率有高有低，但not exists比not in的效率高）
      • 3、盡量避免向客戶端返回大數據量，應該使用分頁
      • 4、用Union ALL代替OR（比較少用，一般都用OR）
  • 3.5 第五，通用
• 四、小結

一、前言

SQL中，廣義的查詢就是crud操作，狹義的查詢僅僅是select查詢操作，慢查詢就是指廣義的查詢，否則為什么不叫慢查詢、慢更新、慢刪除。

慢查詢就是那些執行慢的sql語句，包括crud，一般是查詢，所以稱為慢查詢

問題1：怎么一定一個sql語句是慢的？
回答：根據實際需要，如果前端反饋，執行3s是慢的，就是在my.ini中（Windows是my.ini，Linux是my.conf）中設置long_query_time=3，表示操作3s的查詢就是慢查詢，要記錄下來，好好分析。

問題：為什么這些sql語句會慢呢？為什么這些sql語句滿足long_query_time設置的時間？
回答：執行慢的sql語句不滿足我們的要求，
（1）找到原因 explain + profile；
（2）要想辦法解決，這就是SQL優化。

二、慢查詢概要

2.1 第一步，慢查詢分析之前的配置

2.1.1 方式一：修改my.ini

修改配置文件 在 my.ini 增加幾行:

[mysqld] long_query_time=2 // 慢查詢的定義時間（超過2秒就是慢查詢）// 慢查詢log日志記錄（ slow_query_log） ## 5.5 版本以下配置 log-slow-queries="mysql-slow-query.log" ## 5.5 版本及以上配置 slow-query-log=on slow_query_log_file="mysql-slow-query.log"//記錄沒有使用索引的query log-query-not-using-indexes

2.1.2 方式二：修改數據庫

mysql > set global slow_query_log=ON mysql > set global long_query_time = 3600; mysql > set global long_queries_not_using_indexes=ON;

2.2 第二步，找到執行慢的sql語句

找到慢查詢日志文件路徑：

mysql > show variables like 'slow_query_log_file

使用msql提供的日志分析工具mysqldumpslow分析找出查詢時間最慢的十條sql：

mysql > mysqldumpslow -s 10 /mysql/mysql01_slow.log

2.3 第三步，找到原因兩種方式之一，explain分析，explain各個字段的解釋

問題：為什么使用explain來做慢查詢分析？
回答：explain關鍵字可以模擬優化器執行SQL查詢語句，所以用來分析sql慢查詢語句

使用explain分析這10條sql：

explain SQL_NO_CACHE select * from emp where name = 'Jefabc'

注意：mysql8.0以下，SQL_NO_CACHE 指明了該查詢不走緩存，避免了查詢速度時高時低，影響判斷。
ps：使用explain一般看一看索引使用是否正確，盡量避免回表。


explain各個字段解釋
id：每個執行計劃都有一個 id，如果是一個聯合查詢union，這里還將有多個 id。
select_type：表示 SELECT 查詢類型，常見的有四種
SIMPLE（普通查詢，即沒有聯合查詢、子查詢）、
PRIMARY（主查詢）、UNION（UNION 中后面的查詢）、SUBQUERY（子查詢）等。
table：當前執行計劃查詢的表，如果給表起別名了，則顯示別名信息。
partitions：訪問的分區表信息。
type：這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為：system > const > eq_ref > ref > range > index > ALL。
system/const：表中只有一行數據匹配，此時根據索引查詢一次就能找到對應的數據。如果是 B + 樹索引，我們知道此時索引構造成了多個層級的樹，當查詢的索引在樹的底層時，查詢效率就越低。const 表示此時索引在第一層，只需訪問一層便能得到數據。
eq_ref：使用唯一索引掃描，常見于多表連接中使用主鍵和唯一索引作為關聯條件。
ref：非唯一索引掃描，還可見于唯一索引最左原則匹配掃描。
range：索引范圍掃描，比如，<，>，between 等操作。
index：索引全表掃描，此時遍歷整個索引樹。
ALL：表示全表掃描，需要遍歷全表來找到對應的行。
possible_keys：可能使用到的索引。
key：實際使用到的索引。
key_len：實際使用的索引的長度。
ref：關聯 id 等信息。
rows：查找到記錄所掃描的行數。
filtered：查找到所需記錄占總掃描記錄數的比例。
Extra：額外的信息。

2.4 第四步，找到原因兩種方式之一，profile分析，找到慢查詢的本質原因，profile各個字段的解釋

問題：為什么使用profile做慢查詢分析？
回答：Show profile 是mysql 提供可以用來分析當前會話中語句執行的資源消耗情況。

以下四個中若出現一個或多個，表示sql 語句 必須優化。
1、converting HEAP to MyISAM ： 查詢結果太大，內存都不夠用了，往磁盤上搬了；
2、creating tmp table ：創建臨時表，拷貝數據到臨時表，然后再刪除；
3、copying to tmp table on disk ：把內存中臨時表復制到磁盤，危險！！！
4、 locked

2.4.1 explain制造慢sql語句，profile找到慢的sql語句

第一步，查看一下我的數據庫版本

Show profiles是5.0.37之后添加的，要想使用此功能，要確保版本在5.0.37之后。

mysql> Select version();| version() | | 5.0.82-community-nt |

版本是支持show profiles功能的。接下來進入mysql性能跟蹤診斷的世界

第二步，查看是否打開了profiles功能,默認是關閉的

mysql> use test;Database changedmysql> show profiles;Empty set (0.00 sec)

顯示為空，說明profiles功能是關閉的。

第三步，開啟profile

mysql> set profiling=1;Query OK, 0 rows affected (0.00 sec)

第四步，explain執行下面的查詢

mysql> explain select distinct player_idfrom task limit 20;mysql> select distinct player_id from task ;

第五步，執行 show profiles

mysql> show profiles; | Query_ID | Duration | Query || 1 | 0.00035225 | explain select distinct player_id from task limit 20 | | 2 | 1.91772775 | select distinct player_id from task |

Query_ID 這個需要就是給show profile for query 具體id來用的。

此時可以看到執行select distinct player_id from task 用了1.91772775秒的時間

第六步，調整hostory大小

注意如果hostory比較小可以適當調大，不然query不到

SET profiling_history_size=100

?

2.4.2 show profile for query 具體id

2.4.2.1 sending data發送數據慢，就是網絡IO

根據prifile進一步分析，

show profile for query 具體id

可以清楚的看到該sql的所有執行階段，如鎖等待、執行、優化、發送數據、內存排序，在下圖中可以看到Sending data發送數據耗時1.39s。慢查詢主要原因是網絡IO。

2.4.2.2 Copying to tmp table臨時表慢

**根據query_id 查看某個查詢的詳細時間耗費，是Copying to tmp table **

mysql> show profile for query 2;| Status | Duration || starting | 0.000052 | | Opening tables | 0.000009 | | System lock | 0.000003 | | Table lock | 0.000007 | | init | 0.000013 | | optimizing | 0.000003 | | statistics | 0.000009 | | preparing | 0.000008 | | Creating tmp table | 0.000074 | | executing | 0.000002 | | Copying to tmp table |1.916551 | // 大量時間 四條中第三條，很嚴重，臨時表到 | Sending data | 0.000667 | | end | 0.000004 | | removing tmp table | 0.000065 | | end | 0.000002 | | end | 0.000002 | | query end | 0.000003 | | freeing items | 0.000245 | | closing tables | 0.000006 | | logging slow query | 0.000002 | | cleaning up | 0.000003 |

可以看到 Copying to tmp table 部分耗費了大量時間，這是因為distinct查看會用到臨時表

2.4.3 查看cpu、內存和IO信息

那么可不可以查看占用cpu、 io等信息呢

mysql> show profile block io,cpu for query2; | Status | Duration | CPU_user |CPU_system | Block_ops_in | Block_ops_out || starting | 0.000052 | NULL | NULL | NULL | NULL | | Opening tables | 0.000009 | NULL | NULL | NULL | NULL | | System lock | 0.000003 | NULL | NULL | NULL | NULL | | Table lock | 0.000007 | NULL | NULL | NULL | NULL | | init | 0.000013 | NULL | NULL | NULL | NULL | | optimizing | 0.000003 | NULL | NULL | NULL | NULL | | statistics | 0.000009 | NULL | NULL | NULL | NULL | | preparing | 0.000008 | NULL | NULL | NULL | NULL | | Creating tmp table | 0.000074 | NULL | NULL | NULL | NULL | | executing | 0.000002 | NULL | NULL | NULL | NULL | | Copying to tmp table（耗時） | 1.916551 | NULL | NULL | NULL |NULL | | Sending data | 0.000667 | NULL | NULL | NULL | NULL | | end | 0.000004 | NULL | NULL | NULL | NULL | | removing tmp table | 0.000065 | NULL | NULL | NULL | NULL | | end | 0.000002 | NULL | NULL | NULL | NULL | | end | 0.000002 | NULL | NULL | NULL | NULL | | query end | 0.000003 | NULL | NULL | NULL | NULL | | freeing items | 0.000245 | NULL | NULL | NULL | NULL | | closing tables | 0.000006 | NULL | NULL | NULL | NULL | | logging slow query | 0.000002 | NULL | NULL | NULL | NULL | | cleaning up | 0.000003 | NULL | NULL | NULL | NULL |

另外還可以看到memory,swaps,context switches,source 等信息

三、SQL優化

3.1 第一，明確SQL優化兩個目標（IO成本+CPU成本）

減少 IO 次數

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

降低 CPU 計算

除了 IO 瓶頸之外，SQL優化中需要考慮的就是 CPU 運算量的優化了。order by，group by，distinct等操作都十分占用CPU資源(這些操作基本上都是 CPU 處理內存中的數據比較運算)。當我們的 IO 優化做到一定階段之后，降低 CPU 計算也就成為了我們 SQL 優化的重要目標。

3.2 第二，SQL執行的11個步驟，搞懂MySQL的語句執行順序（11條）

MySQL的語句一共分為11步，如下圖所標注的那樣，最先執行的總是FROM操作，最后執行的是LIMIT操作。其中每一個操作都會產生一張虛擬的表，這個虛擬的表作為一個處理的輸入。

select m.Province,S.Name from member m
left join ShippingArea s on m.Province=s.ShippingAreaID;

下面具體分析一下查詢處理的每一個階段。

1、FORM: 對FROM的左邊的表和右邊的表計算笛卡爾積。產生虛表vitual table 1

2、ON: 對虛表VT1進行ON篩選，只有那些符合< join-condition>的行才會被記錄在虛表VT2中。

3、JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就會作為外部行添加到虛擬表VT2中，產生虛擬表VT3，如果包含兩個以上的表的話，那么就會對上一個join連接產生的結果VT3和下一個表重復執行步驟1~3這三個步驟，一直到處理完所有的表為止。

4、WHERE： 對虛擬表VT3進行WHERE條件過濾。只有符合< where-condition>的記錄才會被插入到虛擬表VT4中。

5、GROUP BY: 根據group by子句中的列，對VT4中的記錄進行分組操作，產生VT5。

6、CUBE | ROLLUP: 對表VT5進行cube或者rollup操作，產生表VT6。

7、HAVING： 對虛擬表VT6應用having過濾，只有符合< having-condition>的記錄才會被插入到虛擬表VT7中。

8、SELECT： 執行select操作，選擇指定的列，插入到虛擬表VT8中。

9、DISTINCT： 對VT8中的記錄進行去重。產生虛擬表VT9。

10、ORDER BY: 將虛擬表VT9中的記錄進行排序操作，產生虛擬表VT10。

11、 LIMIT：取出指定行的記錄，產生虛擬表VT11，并將結果返回。

金手指：記憶方式：輸入、判斷、返回
輸入：from on join
判斷：where判斷、group by分組、having判斷
返回：select輸出，distinct去重、order by排序、limit返回
單表查詢，設置條件：用 Where 子句替換 HAVING 子句 因為 HAVING 只會在檢索出所有記錄之后才對結果集進行過濾

大多數關系型數據庫都是按照行(row)的方式存儲，而數據存取操作都是以一個固定大小的IO單元(被稱作block或者page)為單位，一般為4KB，8KB…… 大多數時候，每個IO單元中存儲了多行，每行都是存儲了該行的所有字段(blob等特殊類型字段除外)。
所以，我們是取一個字段還是多個字段，實際上數據庫在表中需要訪問的數據量其實是一樣的。
當然，也有例外情況，那就是我們的這個查詢在索引中就可以完成，也就是說當只取 a，b兩個字段的時候，不需要回表，而c這個字段不在使用的索引中，需要回表取得其數據。在這樣的情況下，二者的IO量會有較大差異。
大多數時候并不會影響到 IO 量，但是當還存在order by操作的時候，select子句中的字段多少會在很大程度上影響到排序效率。

3.3 第三，表設計（表設計5條 + 字段設計5條）

3.3.1 表設計層面（5條）

1、【表引擎】選擇正確的表引擎 MyISAM Innodb

（1）MyISAM 適合于一些需要大量查詢的應用，但其對于有大量寫操作并不是很好，甚至你只是需要update一個字段，整個表都會被鎖起來，而別的進程，就算是讀進程都無法操作直到讀操作完成。（2）MyISAM 對于 SELECT COUNT(*) 這類的計算是超快無比的。

InnoDB 的趨勢會是一個非常復雜的存儲引擎，對于一些小的應用，它會比 MyISAM 還慢。他是它支持“行鎖” ，于是在寫操作比較多的時候，會更優秀。并且，他還支持更多的高級應用，比如：事務。

金手指：與傳統的ISAM與MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事務（Transaction）功能。

2、【表設計-無邏輯意義自增id】給所有的InnoDB表都設計一個無邏輯意義的自增列做主鍵，對于絕大多數場景都是如此，真正純只讀用InnoDB表的并不多；

3、【表設計-反范式設計，減少回表】適當的使用字段冗余的反范式設計，以空間換時間有的時候會很高效，比如：有用戶id字段的表，用戶在查詢時如果經常需要同時獲得用戶名，此時可以將用戶名當一個冗余字段在該表中存儲，這樣就可以不做連接即可獲得用戶名。
要求：該冗余字段的值一般不變或者很少變化。

4、【表存儲——數據量過大，水平分表】通常地，單表物理大小不超過10GB，單表行數不超過1億條，行平均長度不超過8KB，如果機器性能足夠，這些數據量MySQL是完全能處理的過來的，不用擔心性能問題，數據量過大就要水平分表，不要放在同一個表中。

5、【表存儲——表中存儲數據而不是運算】數據庫直接存放數值，不要在數據庫中做運算。

優化數據庫結構
合理的數據庫結構不僅可以使數據庫占用更小的磁盤空間，而且能夠使查詢速度更快。數據庫結構的設計，需要考慮數據冗余、查詢和更新的速度、字段的數據類型是否合理等多方面的內容。
1、垂直分表，將字段很多的表分解成多個表
對于字段比較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來形成新表。因為當一個表的數據量很大時，會由于使用頻率低的字段的存在而變慢。
2、多對多關系表，增加中間表
對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。通過建立中間表，把需要經常聯合查詢的數據插入到中間表中，然后將原來的聯合查詢改為對中間表的查詢，以此來提高查詢效率。

三范式優化+反范式優化+分庫分表
1）三大范式優化： 比如消除冗余（節省空間。。）
2）反范式優化：比如適當加冗余等（減少 join）
3）分庫： 分區將數據在物理上分隔開，不同分區的數據可以制定保存在處于不同磁盤上的數據文件里。這樣，當對這個表進行查詢時，只需要在表分區中進行掃描，而不必進行全表掃描，明顯縮短了查詢時間，另外處于不同磁盤的分區也將對這個表的數據傳輸分散在不同的磁盤 I/O，一個精心設置的分區可以將數據傳輸對磁盤 I/O 競爭均勻地分散開。對數據量大的時時表可采取此方法。可按月自動建表分區。
4）分表：垂直拆分和水平拆分：
案例： 簡單購物系統暫設涉及如下表：
1.產品表（數據量 10w，穩定）
2.訂單表（數據量 200w，且有增長趨勢）
3.用戶表 （數據量 100w，且有增長趨勢）
以 mysql 為例講述下水平拆分和垂直拆分，mysql 能容忍的數量級在百萬靜態數據可以到千萬
垂直拆分：
解決問題：表與表之間的 io 競爭
不解決問題：單表中數據量增長出現的壓力
方案： 把產品表和用戶表放到一個 server 上 訂單表單獨放到一個 server 上
水平拆分：
解決問題：單表中數據量增長出現的壓力
不解決問題：表與表之間的 io 爭奪
方案：
用戶表通過性別拆分為男用戶表和女用戶表，男用戶表放一個 server 上 女用戶表放一個 server 上(女的愛購物)
訂單表通過已完成和完成中拆分為已完成訂單和未完成訂單 ，未完成訂單放一個 server 上 已完成訂單放到一個server上

3.3.2 字段設計層面（5條）

1、yes 兩條【字段-數據類型夠用選小的，加上not null提交性能】字段長度滿足需求前提下，盡可能選擇長度小的；字段屬性盡量都加上NOT NULL約束，可一定程度提高性能；

2、no 過大不要【字段-數據類型盡量不使用Text/Blob，垂直分表】盡可能不使用TEXT/BLOB類型，確實需要的話，建議垂直分表，拆分到子表中，不要和主表放在一起，避免SELECT * 的時候讀性能太差。

3、select 1條【字段-使用數字型而不是字符型】數據類型盡量用數字型，數字型比字符型的效率高，如果表中某個字段存儲的都是數字，那么該字段設計為數字型字段，而不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數字型而言只需要比較一次就夠了。

4、select 1條【字段-接上面，對于字符類型，使用固定長度而不是可變長度】
char/nchar為固定長度，如果某個字段中字符長度已知固定，使用char/nchar效率比varchar/nvarchar效率高。

5、select 1條【取值有限用枚舉Enum】
如果你有一個字段，比如“性別”，“國家”，“民族”，“狀態”或“部門”，你知道這些字段的取值是有限而且固定的，那么，你應該使用 ENUM 而不是 VARCHAR。

3.4 第四，高效的SQL語句（索引+單表優化+多表優化）

3.4.1 索引（索引兩方向：優先使用索引，避免索引失效）

如下第二、第三句SQL，使用索引與不使用索引，查詢速度相差巨大。（索引可用在select后，也可用于on，where后的條件中，參考索引使用理論基礎）

SELECT * FROM test1; -- 1.863 2.008 2.062（260萬條數據） -- 此處有個select*的誤區，實際上不加索引的select* 比select col速度來得快SELECT NAME FROM test1; -- 4.989 5.001 4.855 （260萬條數據, name不加索引）SELECT NAME FROM test1; -- 1.227 1.476 1.403 （260萬條數據, name加索引）

3.4.1.1 避免索引失效

1）應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

2）應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。

如：
select id from t where num is null
可以在 num 上設置默認值 0，確保表中 num列沒有 null 值，然后這樣查詢：
select id from t where num=0
注意：關于 null,isNull,isNotNull 其實是要看成本的，是否回表等因素總和考慮，才會決定是要走索引還是走全表掃描

【索引】禁止任何對where字段的計算，函數等操作

索引沒起作用的情況
1、使用LIKE關鍵字的查詢語句
在使用LIKE關鍵字進行查詢的查詢語句中，如果匹配字符串的第一個字符為“%”，索引不會起作用。只有“%”不在第一個位置索引才會起作用。
2、使用多列索引的查詢語句
MySQL可以為多個字段創建索引。一個索引最多可以包括16個字段。對于多列索引，只有查詢條件使用了這些字段中的第一個字段時，索引才會被使用。

盡量避免在索引過的字符數據中，使用非打頭字母搜索，導致索引無效

SELECT id FROM table WHERE NAME LIKE ‘%L%’; // 錯誤SELECT id FROM table WHERE NAME LIKE ‘L%’; // 正確

即使NAME字段建有索引， 第一個查詢無法利用索引完成加快操作，引擎不得不對全表所有數據逐條操作來完成任務。而第二個查詢能夠使用索引來加快操作。

不要在where子句中的“=”左邊進行函數、算術運算或其他表達式運算，導致索引無效

任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊。

SELECT * FROM T1 WHERE F1/2=100應改為:SELECT * FROM T1 WHERE F1=100*2 SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)= '5378'應改為:SELECT * FROM RECORD WHERE CARD_NO LIKE '5378%' SELECT member_number, first_name, last_name FROM membersWHERE DATEDIFF(yy,datofbirth,now()) > 21應改為:SELECT member_number, first_name, last_name FROM membersWHERE dateofbirth < DATEADD(yy,-21,now())

索引：
%% like查詢, not in,not exist 都無法應用索引
用了"聚合函數"查詢,就無法應用索引；
復合索引,一般都是看第一個條件索引

3.4.1.2 優先使用索引

【索引-前綴索引】對一個VARCHAR(N)列創建索引時，通常取其50%（甚至更小）左右長度創建前綴索引就足以滿足80%以上的查詢需求了，沒必要創建整列的全長度索引；

【SQL語句-輸入參數where子句-索引代替全表掃描】盡量避免在where子句中對字段進行null值判斷。這會進行全表掃描 Select id,name from user where name is null;

【SQL語句-輸入參數where子句-索引代替全表掃描】盡量避免在where子句中對字段進行表達式操作。這會導致引擎放棄使用索引而進行全表掃描 SELECT id,name FROM user where age/12;

【優先使用復合索引代替單個索引，減少索引數量】多用復合索引，少用多個獨立索引，尤其是一些基數（Cardinality）太小（比如說，該列的唯一值總數少于255）的列就不要創建獨立索引了；

【SQL語句-輸入參數or子句-索引代替全表掃描】對于OR子句，如果要利用索引，則OR之間的每個條件列都必須用到索引，如果沒有索引，則應該考慮增加索引。

【復合索引的順序】在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。

3.4.2 單表查詢 10點

1、單表查詢：避免索引失效，不要在 where 子句中對字段進行 null 值判斷，這會進行全表掃描

錯誤：應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致數據庫放棄使用索引而進行全表掃描，降低查詢速度。可以在col上設置默認值，確保表中col列not null值，然后查詢。

以下實測結果表明：在不加索引的情況下，判空操作比判非空默認值操作速度快得多，加索引的情況下兩種情況差不多。

SELECT id from table where col is null –- 2.003 1.963 1.975 （table col 未加入索引，260萬數據） SELECT id from table where col = ‘0’ -– 4.407 5.256 5.434 （table col 未加入索引，260萬數據）SELECT id from table where col is null –- 1.549 1.884 1.818 （table col 加入索引，260萬數據） SELECT id from table where col = ‘0’ -– 1.626 1.854 1.532 （table col 加入索引，260萬數據）

2、單表查詢，開啟慢查詢日志，定位慢查詢的SQL語句

【單表查詢-慢查詢】開啟慢查詢日志，定期用explain或desc優化慢查詢中的SQL語句

【單表查詢-預處理語句】使用預處理語句。例如使用PDO來操作mysql
在性能方面，當一個相同的查詢被使用多次的時候，這會為你帶來可觀的性能優勢。你可以給這些Prepared Statements(預處理語句)定義一些參數，而MySQL只會解析一次。

3、單表查詢，數據庫壓力轉移到Java后臺

將數據庫的壓力轉移至后臺
數據庫的資源是寶貴的，我們可以將不必要的操作轉移至Java端處理。如判空，字段值拼接，字符串TRIM，日期格式化等等。
如下SQL，均可由Java處理。

SELECT IFNULL(SUM(col),0) from table; // 判空SELECT CONCAT(col, 'hello world') from table; //字段值拼接SELECT TRIM(col) from table; // 字符串TRIMSELECT DATE_FORMAT(col,'%d %b %Y %T:%f') from table; // 日期格式化

4、單表查詢，where 查詢條件數量 + 查詢條件順序

where子句兩個
第一，查詢條件盡量少
第二，越精確的條件,應該放在前面，先過濾掉大數據;

where子句中，盡量少的條件
如下面這句SQL，如果?where條件中col1=1，col2=2是能夠精確查找結果的最簡條件，則無需加入冗余的其他條件。
SELECT * FROM table where col1=1 and col2=2 and col3=3

where子句中，越精準的條件越早過濾
where條件中，越精確的條件（能夠剔除大部分結果的條件）放前面。
又如，在多個表進行分頁數據查詢的時候，我們最好是能夠在一個表上先過濾好數據分好頁，然后再用分好頁的結果集與另外的表Join，這樣可以盡可能多的減少不必要的 IO 操作，大大節省IO操作所消耗的時間。

5、單表查詢，返回值和關鍵字大寫優化（select *，limit）

【SQL語句-返回值】滿足業務需求下，返回值不要使用select * from 的形式查詢數據，只查詢需要使用的列
【SQL語句-返回值】滿足業務需求下，返回值使用limit減少返回的行數，減少網絡數據傳輸時間和帶寬浪費，但是CPU消耗還是要的

【SQL語句-關鍵字】所有的SQL關鍵字用大寫，避免SQL語句重復編譯造成系統資源的浪費

6、單表查詢：計數優化，不要使用 count(列名)或 count(常量)來替代 count(*)

【單表查詢】不要使用 count(列名)或 count(常量)來替代 count(*)
【單表查詢】使用 ISNULL()來判斷是否為 NULL 值

count(*) count(1) count(id)的效率

SELECT COUNT(*) FROM test1; -- 0.749 0.752 0.750 你看，count(*)性能很好SELECT COUNT(1) FROM test1; -- 1.578 0.900 1.455SELECT COUNT(id) FROM test1; -- 0.740 0.763 0.751

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

第一，定義不同
count(column) 和 count()，count(1)是一個完全不一樣的操作，所代表的意義也完全不一樣。
count(column) 是表示結果集中有多少個column字段不為空的記錄。
count()，count(1)是表示整個結果集有多少條記錄。

查詢盡量用具體的字段，而不是直接select * ；聚合函數不適用大量數據查詢過濾；
count()—會吧null空列也統計進去
count(列)—只會統計非空字段
在mysql5.6之后，count()速度比count(1)和count(列) 更快

7、單表查詢：去重優化，能用GROUP BY的就不用DISTINCT

使用GROUP BY去重比DISTINCT效率高。

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10用下面的語句替換：SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

8、單表查詢，LIMIT優化，巧借索引優化orderby排序

【單表查詢-limit】如果查詢中有LIMIT的話，LIMIT也會在排序之后應用，所以即使需要返回較少的數據，臨時表和需要排序的數據量仍然會非常大

單表查詢-優化LIMIT分頁，使用索引優化orderby排序，因為索引是有序的，加上覆蓋索引
在系統中需要分頁的操作通常會使用limit加上偏移量的方法實現，同時加上合適的order by 子句。如果有對應的索引，通常效率會不錯，否則MySQL需要做大量的文件排序操作。
一個非常令人頭疼問題就是當偏移量非常大的時候，例如可能是limit 10000,20這樣的查詢，這是mysql需要查詢10020條然后只返回最后20條，前面的10000條記錄都將被舍棄，這樣的代價很高。
優化此類查詢的一個最簡單的方法是盡可能的使用索引覆蓋掃描，而不是查詢所有的列。然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候這樣做的效率會得到很大提升。
對于下面的查詢：
select id,title from collect limit 90000,10;
該語句存在的最大問題在于limit M,N中偏移量M太大（我們暫不考慮篩選字段上要不要添加索引的影響），導致每次查詢都要先從整個表中找到滿足條件 的前M條記錄，之后舍棄這M條記錄并從第M+1條記錄開始再依次找到N條滿足條件的記錄。如果表非常大，且篩選字段沒有合適的索引，且M特別大那么這樣的代價是非常高的。 試想，如我們下一次的查詢能從前一次查詢結束后標記的位置開始查找，找到滿足條件的100條記錄，并記下下一次查詢應該開始的位置，以便于下一次查詢能直接從該位置 開始，這樣就不必每次查詢都先從整個表中先找到滿足條件的前M條記錄，舍棄，在從M+1開始再找到100條滿足條件的記錄了。
方法一：慮篩選字段（title）上加索引
title字段加索引 （此效率如何未加驗證）
方法二：先查詢出主鍵id值（優化LIMIT分頁，使用索引優化orderby排序）
select id,title from collect where id>=
(select id from collect order by id limit 90000,1) limit 10;
原理：先查詢出90000條數據對應的主鍵id的值，然后直接通過該id的值直接查詢該id后面的數據。
方法三：“關延遲聯”
如果這個表非常大，那么這個查詢可以改寫成如下的方式：
Select news.id, news.description from news
inner join (select id from news order by title limit 50000,5) as myNew using(id);
這里的“關延遲聯”將大大提升查詢的效率，它讓MySQL掃描盡可能少的頁面，獲取需要的記錄后再根據關聯列回原表查詢需要的所有列。這個技術也可以用在優化關聯查詢中的limit。
方法四：建立復合索引 acct_id和create_time
select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10
注意sql查詢慢的原因都是:引起filesort，所以一定要避免filesort

9、單表查詢：排序優化，order by 字段加索引

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

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

10、單表查詢：分組優化，group by 調節順序為復合索引順序

存在的問題：通常情況下，SQL 語句中的 GROUP BY 子句會導致數據庫不得不通過一個排序（SORT）操作來實現對數據的分組，而排序被認為是一個比較耗費 CPU 和內存的操作。實際上某些情況下，如果寫法得當，當中的排序操作是可以避免的。

具體來說，在寫 GROUP BY 子句的時候，應該考慮到數據庫中已經存在的索引的情況。如果 GROUP BY 子句中所有的列恰好包括在某個索引的鍵（Key column）的范圍之內而且是處于開始的位置，那么在寫 GROUP BY 子句的時候，就應該按照該索引上鍵的先后順序來寫 GROUP BY 子句。

比如說有如下的 SQL 語句：

SELECT C2, C3, C1, AVG(C4)FROM T1GROUP BY C2, C3, C1

一般情況下，GROUP BY C2, C3, C1這樣的寫法都會導致數據庫執行一個排序操作，消耗CPU。但是，如果表 T1 上已經存在一個索引 IX1(C1, C2, C3, C4), 這里注意到 GROUP BY 子句中引用到的列（C2，C3，C1）正好是索引 IX1 中的前三個鍵，那么就可以通過改變 GROUP BY 子句中列的順序的辦法來避免這個排序操作。

可以把 SQL 語句改寫為如下所示：

SELECT C1, C2, C3, AVG(C4)FROM T1GROUP BY C1, C2, C3

通過這樣改變 GROUP BY 子句中列的順序使其與索引 IX1 中的鍵順序一致，數據庫就可以利用 IX1 來訪問其已經排序的鍵值并直接返回進行下一步操作，從而避免額外的排序操作，從而帶來查詢性能上的提高。

3.4.3 多表連接查詢 4點

1、多表連接，五個：用 join 代替子查詢，連接字段類型一致，連接字段加索引，主表是小表，orderby和groupby字段在主表，否則無用

【多表連接-join代替子連接】通常情況下，子查詢的性能比較差，建議改造成JOIN寫法；

多表查詢：嵌套子查詢如何優化
嵌套子查詢的效率低的原因：執行子查詢時，MYSQL需要創建臨時表，查詢完畢后再刪除這些臨時表，多了一個創建和銷毀臨時表的過程
嵌套子查詢的優化方案：
1、使用join，join比嵌套子查詢更高效，數據量較大時，無需真正帶入不同參數循環迭代
2、拆分為多個查詢語句

多表查詢-分解關聯查詢
解釋：將一個大的查詢分解為多個小查詢是很有必要的。
很多高性能的應用都會對關聯查詢進行分解，就是可以對每一個表進行一次單表查詢，然后將查詢結果在應用程序中進行關聯，很多場景下這樣會更高效，例如：
SELECT * FROM tag
JOIN tag_post ON tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = ‘mysql’;
分解為：
SELECT * FROM tag WHERE tag = ‘mysql’;
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);

【多表連接-連接字段CPU消耗一致】多表聯接查詢時，關聯字段類型盡量一致，省去字段類型轉換的CPU消耗；
【多表連接-連接字段都要有索引】多表聯接查詢時，關聯字段都要有索引；

【多表連接-主表是小表】多表連接查詢時，把結果集小的表（注意，這里是指過濾后的結果集，不一定是全表數據量小的）作為驅動表，解釋：小表全表掃描，用來比較大表。
【多表查詢-groupby orderby在主表上】多表查詢中，如果使用group by,order by，最好都能選擇驅動表的列或者只選擇一個表上的列，否則無法排序和分組無法用到索引。

多表查詢，分析具體的SQL語句
1、兩個表選哪個為驅動表，表面是可以以數據量的大小作為依據，但是實際經驗最好交給mysql查詢優化器自己去判斷。
例如： select * from a where id in (select id from b );
對于這條sql語句它的執行計劃其實并不是先查詢出b表的所有id,然后再與a表的id進行比較。
mysql內部會將 in子查詢轉換成exists相關子查詢，所以它實際等同于這條sql語句：select * from a where exists(select * from b where b.id=a.id );
而exists相關子查詢的執行原理是: 循環取出a表的每一條記錄與b表進行比較，比較的條件是a.id=b.id . 看a表的每條記錄的id是否在b表存在，如果存在就行返回a表的這條記錄。
exists查詢有什么弊端？外面的a表無法使用索引，必須走全表掃描
由exists執行原理可知，a表(外表)使用不了索引，必須全表掃描，因為是拿a表的數據到b表查。而且必須得使用a表的數據到b表中查（外表到里表中），順序是固定死的。
如何優化？
加索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。
這樣優化夠了嗎？還差一些。
由于exists查詢它的執行計劃只能拿著a表的數據到b表查（外表到里表中），雖然可以在b表的id字段建索引來提高查詢效率。
但是并不能反過來拿著b表的數據到a表查，exists子查詢的查詢順序是固定死的。
為什么要反過來？
因為首先可以肯定的是反過來的結果也是一樣的。這樣就又引出了一個更細致的疑問：在雙方兩個表的id字段上都建有索引時，到底是a表查b表的效率高，還是b表查a表的效率高？
該如何進一步優化？
把查詢修改成inner join連接查詢：select * from a inner join b on a.id=b.id; （但是僅此還不夠，接著往下看）
為什么不用left join 和 right join？
這時候表之間的連接的順序就被固定住了，比如左連接就是必須先查左表全表掃描，然后一條一條的到另外表去查詢，右連接同理。仍然不是最好的選擇。
為什么使用inner join就可以？
inner join中的兩張表，如： a inner join b，但實際執行的順序是跟寫法的順序沒有半毛錢關系的，最終執行也可能會是b連接a，順序不是固定死的。如果on條件字段有索引的情況下，同樣可以使用上索引。
那我們又怎么能知道a和b什么樣的執行順序效率更高？
你不知道，我也不知道。誰知道？mysql自己知道。讓mysql自己去判斷（查詢優化器）。具體表的連接順序和使用索引情況，mysql查詢優化器會對每種情況做出成本評估，最終選擇最優的那個做為執行計劃。
在inner join的連接中,mysql會自己評估使用a表查b表的效率高還是b表查a表高，如果兩個表都建有索引的情況下，mysql同樣會評估使用a表條件字段上的索引效率高還是b表的。
利用explain字段查看執行時運用到的key（索引）
而我們要做的就是：把兩個表的連接條件的兩個字段都各自建立上索引，然后explain 一下，查看執行計劃，看mysql到底利用了哪個索引，最后再把沒有使用索引的表的字段索引給去掉就行了。

錯誤使用Left Join
left join的表在where中出現，如
Table1 left join Table2 on Table1.C1=Table2.c1 where Table2.column1=XXX
實際上已經變成了inner join
Left join會限制連接順序，而且where條件只能在連接后過濾，影響性能

2、多表查詢，很多時候用exists代替in是一個好的選擇（理由：雖然in與exists效率在不同場景效率有高有低，但not exists比not in的效率高）

select num from a where num in (select num from b);用下面的語句替換：select num from a where exists (select 1 from b where num=a.num)

---

3、盡量避免向客戶端返回大數據量，應該使用分頁

若從數據庫一次性返回的數據量過大，應該考慮相應需求是否合理，是否可以通過分頁等方法處理。

【多表查詢-分頁SQL語句】類似分頁功能的SQL，建議先用主鍵關聯，然后返回結果集，效率會高很多；

分頁SQL優化
分頁SQL可能包含order by，group by和多表join等操作，可以利用前面的優化手段
后臺分頁取數SQL可以用cursor替換，分批fetch；使用分頁SQL多次取，性能差，數據一致性沒保證

4、用Union ALL代替OR（比較少用，一般都用OR）

SELECT * FROM test1 WHERE NAME = 'test1_1' OR NAME = 'test1_2'; -- 3.179 2.902 2.806（100萬數據測試） SELECT * FROM test1 WHERE NAME = 'test1_1'union allSELECT * FROM test1 WHERE NAME = 'test1_2'; -- 2.335 2.485 2.432（100萬數據測試）

拆分復雜查詢，使用聯合查詢，可以用多條sql語句來實現一條查詢結果,提高查詢性能。

能用UNION ALL就不要用UNION

UNION ALL不執行SELECT DISTINCT函數，少一個步驟，這樣就會減少很多不必要的資源。

3.5 第五，通用

【通用】：
Mysql數據庫名和表名在 Windows 中是大小寫不敏感的，而在大多數類型的 Unix/Linux 系統中是大小寫敏感的。
MySQL大小寫敏感可以通過配置文件（Windows中的my.ini，Linux中的my.cnf）的lower_case_table_names參數來控制。
【通用】：數據庫的存儲三樣東西----表結構,表數據，索引
【通用 Mysql很少使用存儲引擎】Mysql很少采用類似SqlServer中大量使用的 “存儲過程”來實現業務邏輯（存儲過程也不是mysql的優勢）；更多的是用 簡單的查詢，而把復雜的查詢業務，直接在程序代碼中實現，壓力轉義(java中有很多優秀的orm框架)。 mysql雖然沒有sqlserver和oracle強大，但是它是開源免費，可以部署多臺sql服務器,實現分表分庫,集群，從而實現以量換性能。

四、小結

1、慢查詢和SQL調優是一體的；
2、全文金手指，都是要記憶的。

總結

以上是生活随笔為你收集整理的必看，关于sql的慢查询及解决方案的全部內容，希望文章能夠幫你解決所遇到的問題。

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