慢查詢?nèi)罩?slow query log

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

打開慢日志開關(guān)

因?yàn)殚_啟慢查詢?nèi)罩臼怯写鷥r(jià)的（跟 bin log、optimizer-trace 一樣），所以它默認(rèn)是關(guān)閉的：

show variables like 'slow_query%';

image.png

除了這個(gè)開關(guān)，還有一個(gè)參數(shù)，控制執(zhí)行超過(guò)多長(zhǎng)時(shí)間的 SQL 才記錄到慢日志，默認(rèn)是 10 秒

show variables like 'long_query_time';

可以直接動(dòng)態(tài)修改參數(shù)（重啟后失效）。

set @@global.slow_query_log=1; -- 1 開啟，0 關(guān)閉，重啟后失效 
set @@global.long_query_time=3; -- mysql 默認(rèn)的慢查詢時(shí)間是 10 秒，另開一個(gè)窗口

或者修改配置文件 my.cnf。以下配置定義了慢查詢?nèi)罩镜拈_關(guān)、慢查詢的時(shí)間、日志文件的存放路徑。

slow_query_log = ON
 long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log

模擬慢查詢：

select sleep(10);
--查詢 user_innodb 表的 500 萬(wàn)數(shù)據(jù)（檢查是不是沒有索引）。
SELECT * FROM `user_innodb` where phone = '136';

慢日志分析

日志內(nèi)容

show global status like 'slow_queries'; -- 查看有多少慢查詢
 show variables like '%slow_query%'; -- 獲取慢日志目錄

假設(shè)文件在： /var/lib/mysql/ localhost-slow.log

cat /var/lib/mysql/ localhost-slow.log

image.png

有了慢查詢?nèi)罩?，怎么去分析統(tǒng)計(jì)呢？比如 SQL 語(yǔ)句的出現(xiàn)的慢查詢次數(shù)最多，平均每次執(zhí)行了多久？

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目錄下

mysqldumpslow --help

例如：查詢用時(shí)最多的 20 條慢 SQL：

mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log

image.png

Count 代表這個(gè) SQL 執(zhí)行了多少次；
Time 代表執(zhí)行的時(shí)間，括號(hào)里面是累計(jì)時(shí)間；
Lock 表示鎖定的時(shí)間，括號(hào)是累計(jì)；
Rows 表示返回的記錄數(shù)，括號(hào)是累計(jì)。

除了慢查詢?nèi)罩局?，還有一個(gè) SHOW PROFILE 工具可以使用。

SHOW PROFILE

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
SHOW PROFILE 是谷歌高級(jí)架構(gòu)師 Jeremy Cole 貢獻(xiàn)給 MySQL 社區(qū)的，可以查看SQL 語(yǔ)句執(zhí)行的時(shí)候使用的資源，比如 CPU、IO 的消耗情況。
在 SQL 中輸入 help profile 可以得到詳細(xì)的幫助信息。

查看是否開啟

select @@profiling;
 set @@profiling=1;

查看 profile 統(tǒng)計(jì)
（命令最后帶一個(gè) s）

show profiles;

image.png

6.2E-5，小數(shù)點(diǎn)左移 5 位，代表 0.000062 秒。

也可以根據(jù) ID 查看執(zhí)行詳細(xì)信息，在后面帶上 for query + ID。

show profile for query 1;

除了慢日志和 show profile，如果要分析出當(dāng)前數(shù)據(jù)庫(kù)中執(zhí)行的慢的 SQL，還可以通過(guò)查看運(yùn)行線程狀態(tài)和服務(wù)器運(yùn)行信息、存儲(chǔ)引擎信息來(lái)分析。

其他系統(tǒng)命令

show processlist 運(yùn)行線程
https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

show processlist;

這是很重要的一個(gè)命令，用于顯示用戶運(yùn)行線程?？梢愿鶕?jù) id 號(hào) kill 線程。也可以查表，效果一樣：

select * from information_schema.processlist;

image.png

show status 服務(wù)器運(yùn)行狀態(tài)
https://dev.mysql.com/doc/refman/5.7/en/show-status.html SHOW STATUS 用于查看 MySQL 服務(wù)器運(yùn)行狀態(tài)（重啟后會(huì)清空），有 session 和 global 兩種作用域，格式：參數(shù)-值。

可以用 like 帶通配符過(guò)濾。

SHOW GLOBAL STATUS LIKE 'com_select'; -- 查看 select 次數(shù)

show engine 存儲(chǔ)引擎運(yùn)行信息
https://dev.mysql.com/doc/refman/5.7/en/show-engine.html show engine 用來(lái)顯示存儲(chǔ)引擎的當(dāng)前運(yùn)行信息，包括事務(wù)持有的表鎖、行鎖信息；事務(wù)的鎖等待情況；線程信號(hào)量等待；文件 IO 請(qǐng)求；buffer pool 統(tǒng)計(jì)信息。
例如：

show engine innodb status;

如果需要將監(jiān)控信息輸出到錯(cuò)誤信息 error log 中（15 秒鐘一次），可以開啟輸出。

show variables like 'innodb_status_output%';
 -- 開啟輸出：
 SET GLOBAL innodb_status_output=ON; 
SET GLOBAL innodb_status_output_locks=ON;

我們現(xiàn)在已經(jīng)知道了這么多分析服務(wù)器狀態(tài)、存儲(chǔ)引擎狀態(tài)、線程運(yùn)行信息的命令，如果讓你去寫一個(gè)數(shù)據(jù)庫(kù)監(jiān)控系統(tǒng)，你會(huì)怎么做？

其實(shí)很多開源的慢查詢?nèi)罩颈O(jiān)控工具，他們的原理其實(shí)也都是讀取的系統(tǒng)的變量和狀態(tài)。
現(xiàn)在我們已經(jīng)知道哪些 SQL 慢了，為什么慢呢？慢在哪里？
MySQL 提供了一個(gè)執(zhí)行計(jì)劃的工具（在架構(gòu)中我們有講到，優(yōu)化器最終生成的就是一個(gè)執(zhí)行計(jì)劃），其他數(shù)據(jù)庫(kù)，例如 Oracle 也有類似的功能。
通過(guò) EXPLAIN 我們可以模擬優(yōu)化器執(zhí)行 SQL 查詢語(yǔ)句的過(guò)程，來(lái)知道 MySQL 是怎么處理一條 SQL 語(yǔ)句的。通過(guò)這種方式我們可以分析語(yǔ)句或者表的性能瓶頸。

explain 可以分析 update、delete、insert 么？ MySQL 5.6.3以前只能分析 SELECT; MySQL5.6.3以后就可以分析update、delete、 insert 了。

EXPLAIN 執(zhí)行計(jì)劃

官方鏈接：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
我們先創(chuàng)建三張表。一張課程表，一張老師表，一張老師聯(lián)系方式表（沒有任何索引）。

DROP TABLE IF EXISTS course;

CREATE TABLE `course` (
    `cid` INT(3) DEFAULT NULL,
    `cname` VARCHAR(20) DEFAULT NULL,
    `tid` INT(3) DEFAULT NULL
) ENGINE = INNODB CHARSET = utf8mb4;

DROP TABLE IF EXISTS teacher;

CREATE TABLE `teacher` (
    `tid` INT(3) DEFAULT NULL,
    `tname` VARCHAR(20) DEFAULT NULL,
    `tcid` INT(3) DEFAULT NULL
) ENGINE = INNODB CHARSET = utf8mb4;

DROP TABLE IF EXISTS teacher_contact;

CREATE TABLE `teacher_contact` (
    `tcid` INT(3) DEFAULT NULL,
    `phone` VARCHAR(200) DEFAULT NULL
) ENGINE = INNODB CHARSET = utf8mb4;

INSERT INTO `course`
VALUES ('1', 'mysql', '1');

INSERT INTO `course`
VALUES ('2', 'jvm', '1');

INSERT INTO `course`
VALUES ('3', 'juc', '2');

INSERT INTO `course`
VALUES ('4', 'spring', '3');

INSERT INTO `teacher`
VALUES ('1', 'qingshan', '1');

INSERT INTO `teacher`
VALUES ('2', 'jack', '2');

INSERT INTO `teacher`
VALUES ('3', 'mic', '3');

INSERT INTO `teacher_contact`
VALUES ('1', '13688888888');

INSERT INTO `teacher_contact`
VALUES ('2', '18166669999');

INSERT INTO `teacher_contact`
VALUES ('3', '17722225555');

explain 的結(jié)果有很多的字段，我們?cè)敿?xì)地分析一下。先確認(rèn)一下環(huán)境：

select version(); 
show variables like '%engine%';

id

id 是查詢序列編號(hào)。

id 值不同
id 值不同的時(shí)候，先查詢 id 值大的（先大后小）。

-- 查詢 mysql 課程的老師手機(jī)號(hào)
EXPLAIN 
SELECT 
  tc.phone 
FROM
  teacher_contact tc 
WHERE tcid = 
  (SELECT 
    tcid 
  FROM
    teacher t 
  WHERE t.tid = 
    (SELECT 
      c.tid 
    FROM
      course c 
    WHERE c.cname = 'mysql')) ;

image.png

先查課程表，再查老師表，最后查老師聯(lián)系方式表。子查詢只能以這種方式進(jìn)行，只有拿到內(nèi)層的結(jié)果之后才能進(jìn)行外層的查詢。

id 值相同

-- 查詢課程 ID 為 2，或者聯(lián)系表 ID 為 3 的老師
EXPLAIN 
SELECT 
  t.tname,
  c.cname,
  tc.phone 
FROM
  teacher t,
  course c,
  teacher_contact tc 
WHERE t.tid = c.tid 
  AND t.tcid = tc.tcid 
  AND (c.cid = 2 
    OR tc.tcid = 3) ;

image.png

id 值相同時(shí)，表的查詢順序是從上往下順序執(zhí)行。例如這次查詢的 id 都是 1，查詢的順序是 teacher t（3 條）——course c（4 條）——teacher_contact tc（3 條）。
teacher 表插入 3 條數(shù)據(jù)后：

INSERT INTO `teacher` VALUES  (4, 'james', 4) ;

 INSERT INTO `teacher` VALUES (5, 'tom', 5); 
 
 INSERT INTO `teacher` VALUES (6, 'seven', 6); 
 
-- （備份）恢復(fù)語(yǔ)句 
DELETE FROM teacher where tid in (4,5,6); 
COMMIT;

id 也都是 1，但是從上往下查詢順序變成了：teacher_contact tc（3 條）——teacher t（6 條）——course c（4 條）。

image.png

為什么數(shù)據(jù)量不同的時(shí)候順序會(huì)發(fā)生變化呢？這個(gè)是由笛卡爾積決定的。

舉例：假如有 a、b、c 三張表，分別有 2、3、4 條數(shù)據(jù)，如果做三張表的聯(lián)合查詢，當(dāng)查詢順序是 a→b→c 的時(shí)候，它的笛卡爾積是：234=64=24。如果查詢順序是 c →b→a，它的笛卡爾積是 432=122=24

因?yàn)?MySQL 要把查詢的結(jié)果，包括中間結(jié)果和最終結(jié)果都保存到內(nèi)存，所以 MySQL 會(huì)優(yōu)先選擇中間結(jié)果數(shù)據(jù)量比較小的順序進(jìn)行查詢。所以最終聯(lián)表查詢的順序是 a→b→ c。這個(gè)就是為什么 teacher 表插入數(shù)據(jù)以后查詢順序會(huì)發(fā)生變化。

（小標(biāo)驅(qū)動(dòng)大表的思想）

既有相同也有不同
如果 ID 有相同也有不同，就是 ID 不同的先大后小，ID 相同的從上往下。

select type 查詢類型

這里并沒有列舉全部（其它：DEPENDENT UNION、DEPENDENT SUBQUERY、 MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。下面列舉了一些常見的查詢類型：

SIMPLE
簡(jiǎn)單查詢，不包含子查詢，不包含關(guān)聯(lián)查詢 union。

image.png

再看一個(gè)包含子查詢的案例：

-- 查詢 mysql 課程的老師手機(jī)號(hào)
EXPLAIN 
SELECT 
  tc.phone 
FROM
  teacher_contact tc 
WHERE tcid = 
  (SELECT 
    tcid 
  FROM
    teacher t 
  WHERE t.tid = 
    (SELECT 
      c.tid 
    FROM
      course c 
    WHERE c.cname = 'mysql')) ;

image.png

PRIMARY
子查詢 SQL 語(yǔ)句中的主查詢，也就是最外面的那層查詢
SUBQUERY
子查詢中所有的內(nèi)層查詢都是 SUBQUERY 類型的
DERIVED
衍生查詢，表示在得到最終查詢結(jié)果之前會(huì)用到臨時(shí)表。例如：

-- 查詢 ID 為 1 或 2 的老師教授的課程 
EXPLAIN 
SELECT 
  cr.cname 
FROM
  (SELECT 
    * 
  FROM
    course 
  WHERE tid = 1 
    UNION
    SELECT 
      * 
    FROM
      course 
    WHERE tid = 2) cr ;

image.png

對(duì)于關(guān)聯(lián)查詢，先執(zhí)行右邊的 table（UNION），再執(zhí)行左邊的 table，類型是 DERIVED。

UNION
用到了 UNION 查詢。同上例。
UNION RESULT
主要是顯示哪些表之間存在 UNION 查詢。<union2,3>代表 id=2 和 id=3 的查詢存在 UNION。同上例。

type 連接類型

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
所有的連接類型中，上面的最好，越往下越差。
在常用的鏈接類型中：system > const > eq_ref > ref > range > index > all
這里并沒有列舉全部（其他： fulltext 、 ref_or_null 、 index_merger 、 unique_subquery、index_subquery）。
以上訪問(wèn)類型除了 all，都能用到索引

const
主鍵索引或者唯一索引，只能查到一條數(shù)據(jù)的 SQL

CREATE TABLE single_data (
    id int(3) PRIMARY KEY,
    content varchar(20)
);

INSERT INTO single_data
VALUES (1, 'a');

EXPLAIN SELECT *
FROM single_data a
WHERE id = 1;

image.png

system
system是 const 的一種特例，只有一行滿足條件。例如：只有一條數(shù)據(jù)的系統(tǒng)表。

image.png
eq_ref
通常出現(xiàn)在多表的 join 查詢，表示對(duì)于前表的每一個(gè)結(jié)果,，都只能匹配到后表的一行結(jié)果。一般是唯一性索引的查詢（UNIQUE 或 PRIMARY KEY）。
eq_ref 是除 const 之外最好的訪問(wèn)類型。
先刪除 teacher 表中多余的數(shù)據(jù)，teacher_contact 有 3 條數(shù)據(jù)，teacher 表有 3 條數(shù)據(jù)

DELETE FROM teacher WHERE tid IN (4, 5, 6);

為 teacher_contact 表的 tcid（第一個(gè)字段）創(chuàng)建主鍵索引。

ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);

為 teacher 表的 tcid（第三個(gè)字段）創(chuàng)建普通索引。

ALTER TABLE teacher ADD INDEX idx_tcid (tcid);

執(zhí)行以下 SQL 語(yǔ)句：

select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

image.png

小結(jié)：以上三種 system，const，eq_ref，都是可遇而不可求的，基本上很難優(yōu)化到這個(gè) 狀態(tài)。

ref
查詢用到了非唯一性索引，或者關(guān)聯(lián)操作只使用了索引的最左前綴。
例如：使用 tcid 上的普通索引查詢：

image.png
range
索引范圍掃描。
如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 這些，type 類型就為 range。
不走索引一定是全表掃描（ALL），所以先加上普通索引。

ALTER TABLE teacher ADD INDEX idx_tid (tid);

執(zhí)行范圍查詢（字段上有普通索引）：

image.png

IN 查詢也是 range（字段有主鍵索引）

image.png

index
Full Index Scan，查詢?nèi)克饕械臄?shù)據(jù)（比不走索引要快）。

image.png
all
Full Table Scan，如果沒有索引或者沒有用到索引，type 就是 ALL。代表全表掃描。
NULL
不用訪問(wèn)表或者索引就能得到結(jié)果，例如：

EXPLAIN select 1 from dual where 1=1;

小結(jié)：一般來(lái)說(shuō)，需要保證查詢至少達(dá)到 range 級(jí)別，最好能達(dá)到 ref。 ALL（全表掃描）和 index（查詢?nèi)克饕┒际切枰獌?yōu)化的。

possible_key、key

可能用到的索引和實(shí)際用到的索引。如果是 NULL 就代表沒有用到索引。 possible_key 可以有一個(gè)或者多個(gè)，可能用到索引不代表一定用到索引。反過(guò)來(lái)，possible_key 為空，key 可能有值嗎？

表上創(chuàng)建聯(lián)合索引：

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
 ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

執(zhí)行計(jì)劃（改成 select name 也能用到索引）：

explain select phone from user_innodb where phone='126';

image.png

結(jié)論：是有可能的（這里是覆蓋索引的情況）。
如果通過(guò)分析發(fā)現(xiàn)沒有用到索引，就要檢查 SQL 或者創(chuàng)建索引。

key_len

索引的長(zhǎng)度（使用的字節(jié)數(shù)）。跟索引字段的類型、長(zhǎng)度有關(guān)

rows

MySQL 認(rèn)為掃描多少行才能返回請(qǐng)求的數(shù)據(jù)，是一個(gè)預(yù)估值。一般來(lái)說(shuō)行數(shù)越少越好

filtered

這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在 server 層過(guò)濾后，剩下多少滿足查詢的記錄數(shù) 量的比例，它是一個(gè)百分比

ref

使用哪個(gè)列或者常數(shù)和索引一起從表中篩選數(shù)據(jù)。

Extra

執(zhí)行計(jì)劃給出的額外的信息說(shuō)明

using index
用到了覆蓋索引，不需要回表

EXPLAIN SELECT tid FROM teacher ;

using where
使用了 where 過(guò)濾，表示存儲(chǔ)引擎返回的記錄并不是所有的都滿足查詢條件，需要在 server 層進(jìn)行過(guò)濾（跟是否使用索引沒有關(guān)系）。

EXPLAIN select * from user_innodb where phone ='13866667777';

image.png

Using index condition（索引條件下推）
https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html
using filesort
不能使用索引來(lái)排序，用到了額外的排序（跟磁盤或文件沒有關(guān)系）。需要優(yōu)化。（復(fù)合索引的前提）

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
 ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

EXPLAIN select * from user_innodb where name ='青山' order by id;

image.png

using temporary
用到了臨時(shí)表。例如（以下不是全部的情況）：
1、distinct 非索引列

EXPLAIN select DISTINCT(tid) from teacher t

2、group by 非索引列

EXPLAIN select tname from teacher group by tname;

3、使用 join 的時(shí)候，group 任意列

EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;

總結(jié)一下：模擬優(yōu)化器執(zhí)行 SQL 查詢語(yǔ)句的過(guò)程，來(lái)知道 MySQL 是怎么處理一條 SQL 語(yǔ)句的。通過(guò)這種方式我們可以分析語(yǔ)句或者表的性能瓶頸。分析出問(wèn)題之后，就是對(duì) SQL 語(yǔ)句的具體優(yōu)化。

SQL 與索引優(yōu)化

當(dāng)我們的 SQL 語(yǔ)句比較復(fù)雜，有多個(gè)關(guān)聯(lián)和子查詢的時(shí)候，就要分析 SQL 語(yǔ)句有沒有改寫的方法。舉個(gè)簡(jiǎn)單的例子，一模一樣的數(shù)據(jù)：

-- 大偏移量的 limit 
select * from user_innodb limit 900000,10; 
-- 改成先過(guò)濾 ID，再 limit
SELECT * FROM user_innodb WHERE id >= 900000 LIMIT 10;

對(duì)于具體的 SQL 語(yǔ)句的優(yōu)化，MySQL 官網(wǎng)也提供了很多建議，這個(gè)是我們?cè)诜治?具體的 SQL 語(yǔ)句的時(shí)候需要注意的
https://dev.mysql.com/doc/refman/5.7/en/optimization.html

阿里云 polardb 默認(rèn)配置
4C16G polardb 1主3從查詢主配置
SHOW VARIABLES LIKE 'max_connections%'; 8512
SHOW VARIABLES LIKE '%innodb_buffer_pool_size%'; 12884901888 = 12G

32C256G polardb 1主3從查詢主配置
SHOW VARIABLES LIKE 'max_connections%'; 64512
SHOW VARIABLES LIKE '%innodb_buffer_pool_size%'; 206158430208 = 192G

88C710G polardb 1主3從查詢主配置
SHOW VARIABLES LIKE 'max_connections%'; 100512
SHOW VARIABLES LIKE '%innodb_buffer_pool_size%'; 572304392192 = 533G

buffer_pool_size 約等于內(nèi)存的75%

——學(xué)自咕泡學(xué)院

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL優(yōu)化器實(shí)現(xiàn)原理

MySQL優(yōu)化器實(shí)現(xiàn)原理

慢查詢?nèi)罩?slow query log

打開慢日志開關(guān)

慢日志分析

日志內(nèi)容

SHOW PROFILE

其他系統(tǒng)命令

EXPLAIN 執(zhí)行計(jì)劃

id

select type 查詢類型

type 連接類型

possible_key、key

key_len

rows

filtered

ref

Extra

SQL 與索引優(yōu)化

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySQL優(yōu)化器實(shí)現(xiàn)原理

慢查詢?nèi)罩?slow query log

打開慢日志開關(guān)

慢日志分析

日志內(nèi)容

SHOW PROFILE

其他系統(tǒng)命令

EXPLAIN 執(zhí)行計(jì)劃

id

select type 查詢類型

type 連接類型

possible_key、key

key_len

rows

filtered

ref

Extra

SQL 與索引優(yōu)化

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

possible_key、key