概要
MySQL 8.0.18でEXPLAIN ANALYZEという句が導入されたのを知ったので、どんなものかメモします。
公式の説明はexplainのEXPLAIN ANALYZE による情報の取得という段落に記載されています。
以下の情報を取得できるようです。
- 推定実行コスト
- 戻された行の推定数
- 最初の行を返す時間
- すべての行 (実際のコスト) を返す時間 (ミリ秒)
- イテレータによって返された行数
- ループ数
実行されるコストや時間など、本番環境で実行する前に知っておきたい情報が揃ってますね。
EXPLAIN ANALYZEを実行してみる
下準備で3テーブル用意してそれぞれ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まで増加していますね。このように可視化して見れるのは面白いですね。