MySQL 8.0.18で導入されたEXPLAIN ANALYZEについて

MySQL SQL

概要

MySQL 8.0.18でEXPLAIN ANALYZEという句が導入されたのを知ったので、どんなものかメモします。
公式の説明はexplainのEXPLAIN ANALYZE による情報の取得という段落に記載されています。

以下の情報を取得できるようです。

  • 推定実行コスト
  • 戻された行の推定数
  • 最初の行を返す時間
  • すべての行 (実際のコスト) を返す時間 (ミリ秒)
  • イテレータによって返された行数
  • ループ数

実行されるコストや時間など、本番環境で実行する前に知っておきたい情報が揃ってますね。

EXPLAIN ANALYZEを実行してみる

下準備で３テーブル用意してそれぞれ1000行Insert 

CREATE TABLE Users (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    UserName VARCHAR(50),
    Email VARCHAR(100) UNIQUE,
    SignUpDate DATE,
    ProfileDescription TEXT
);

CREATE TABLE Blogs (
    BlogID INT AUTO_INCREMENT PRIMARY KEY,
    UserID INT,
    Title VARCHAR(100),
    Content TEXT,
    PublishDate DATE,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

CREATE TABLE Comments (
    CommentID INT AUTO_INCREMENT PRIMARY KEY,
    BlogID INT,
    UserID INT,
    Comment TEXT,
    CommentDate DATE,
    FOREIGN KEY (BlogID) REFERENCES Blogs(BlogID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

DELIMITER //

CREATE PROCEDURE InsertDummyData()
BEGIN
  DECLARE i INT DEFAULT 1;
  
  WHILE i <= 1000 DO
    INSERT INTO Users (UserName, Email, SignUpDate, ProfileDescription)
    VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), CURDATE(), 'This is a sample description.');
    
    INSERT INTO Blogs (UserID, Title, Content, PublishDate)
    VALUES (i, CONCAT('Blog Post ', i), 'This is sample blog content.', CURDATE());
    
    INSERT INTO Comments (BlogID, UserID, Comment, CommentDate)
    VALUES (i, i, 'This is a sample comment.', CURDATE());
    
    SET i = i + 1;
  END WHILE;
END;

//
DELIMITER ;

CALL InsertDummyData();

インデックス有りでSQL実行

explain analyze select * from Users
inner join Blogs on Users.UserID = Blogs.UserID
inner join Comments on Comments.BlogID =  Blogs.BlogID;

インデックス有り結果

-> Nested loop inner join  (cost=802 rows=1000) (actual time=0.0995..4.66 rows=1000 loops=1)
    -> Nested loop inner join  (cost=452 rows=1000) (actual time=0.0836..2.98 rows=1000 loops=1)
        -> Filter: (comments.BlogID is not null)  (cost=102 rows=1000) (actual time=0.0588..1.03 rows=1000 loops=1)
            -> Table scan on Comments  (cost=102 rows=1000) (actual time=0.058..0.952 rows=1000 loops=1)
        -> Filter: (blogs.UserID is not null)  (cost=0.25 rows=1) (actual time=0.00163..0.00174 rows=1 loops=1000)
            -> Single-row index lookup on Blogs using PRIMARY (BlogID=comments.BlogID)  (cost=0.25 rows=1) (actual time=0.00148..0.00152 rows=1 loops=1000)
    -> Single-row index lookup on Users using PRIMARY (UserID=blogs.UserID)  (cost=0.25 rows=1) (actual time=0.00145..0.00149 rows=1 loops=1000)

インデックスが効かないjoinで実行

explain analyze select * from Users
inner join Blogs on Users.UserID = Blogs.UserID
inner join Comments on Comments.CommentDate =  Blogs.PublishDate;

インデックスが効かないjoin 結果

-> Inner hash join (comments.CommentDate = blogs.PublishDate)  (cost=100461 rows=100000) (actual time=3.27..179 rows=1e+6 loops=1)
    -> Table scan on Comments  (cost=0.0189 rows=1000) (actual time=0.00992..0.598 rows=1000 loops=1)
    -> Hash
        -> Nested loop inner join  (cost=452 rows=1000) (actual time=0.0427..2.65 rows=1000 loops=1)
            -> Filter: (blogs.UserID is not null)  (cost=102 rows=1000) (actual time=0.0286..1 rows=1000 loops=1)
                -> Table scan on Blogs  (cost=102 rows=1000) (actual time=0.0281..0.917 rows=1000 loops=1)
            -> Single-row index lookup on Users using PRIMARY (UserID=blogs.UserID)  (cost=0.25 rows=1) (actual time=0.00141..0.00144 rows=1 loops=1000)

Costが1000以下だったのが100461まで増加していますね。このように可視化して見れるのは面白いですね。