MySQL之优化案例

最新推荐文章于 2024-12-24 10:20:55 发布

原创

最新推荐文章于 2024-12-24 10:20:55 发布 · 2.5k 阅读

40 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #索引 #数据库

本文通过单表、双表及三表查询案例，深入探讨MySQL查询优化。通过模拟数据，展示如何针对不同查询场景创建合适的索引，以减少全表扫描和文件排序，提高查询效率。在双表和三表连接查询中，强调了优化内层循环和正确使用索引的重要性。

MySQL之优化案例

单表简单案例

模拟数据

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');

模拟查询

查询category_id 为1且comments>1的情况下，观看数量最多的文章

explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1 --分析sql

在这里插入图片描述

type：all，全表扫描，情况不容乐观
Using filesort：文件内排序，情况不容乐观*2

如何优化

show index from article --查看表索引

在这里插入图片描述

新建索引

ALTER TABLE article ADD INDEX idx_article_ccv (category_id, comments, views)  --第一种方式
CREATE INDEX idx_article_ccv ON article (category_id, comments, views)  --第二种方式

再次查看执行计划

在这里插入图片描述

全表扫描已解决，但是文件排序依然存在
索引不合适

删除并重建索引

DROP INDEX idx_article_ccv ON article -- 删除索引
CREATE INDEX idx_article_ccv ON article (category_id,views)  --重建索引

再次查看执行计划

在这里插入图片描述
完美收工

双表简单案例

模拟数据

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))

最低0.47元/天解锁文章