Mysql之索引优化案例

最新推荐文章于 2022-09-29 16:31:13 发布
最新推荐文章于 2022-09-29 16:31:13 发布
阅读量258 收藏 1
点赞数 1
分类专栏： mysql 索引 面试 文章标签： mysql 索引 mysql优化 面试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42620326/article/details/117692569
版权
面试 同时被 3 个专栏收录
56 篇文章 2 订阅
订阅专栏
mysql
9 篇文章 0 订阅
订阅专栏
索引
9 篇文章 0 订阅
订阅专栏
本文详细探讨了如何通过创建索引来优化Mysql查询性能，包括单表查询的全表扫描问题解决，双表LEFT JOIN优化策略，以及三表联查的性能提升技巧。通过实例演示了针对不同类型JOIN操作选择合适索引的重要性。
摘要由CSDN通过智能技术生成

Mysql之索引优化案例

1.单表简单案例

1.1创建表

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

在这里插入图片描述

1.2 问题：

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

如何优化，查询表的索引

show index from article --查看表索引

1.3 解决：新建索引

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)  --重建索引

1.4 再次执行

在这里插入图片描述
完美解决

2.双表简单案例

2.1创建表并插入数据

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)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

explain显示执行计划

EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card

在这里插入图片描述

2.2 由于是LEFT JOIN，所以左表是主表，因此第一次索引尝试加在主表上

  • 只对左表class新增索引
CREATE INDEX idx_class_card ON class (card)
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card

在这里插入图片描述
结果：
虽然type变为index，但是扫描行数依然是全表扫描。

2.3继续改进

  • 只对右表book 新增索引
DROP INDEX idx_class_card on class --删除class表索引
CREATE INDEX idx_book_card ON book (card)
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card

在这里插入图片描述
结果：type变为ref，rows只扫描了一行。

2.4 结论

这是由于LEFT JOIN特性决定的，由于左表数据全都有，所以关键在于如何从右表进行搜索，所以右表一定要添加索引。

3.三表简单案例

3.1添加一张表

  • 在双表的基础上新增一张phone表
CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

注意：三表均没有新建索引

执行计划

EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card

结果： 全表扫描，且使用了连接缓存

3.2改进

  • 在phone和book表新增索引
CREATE INDEX idx_phone_card ON phone(card)
CREATE INDEX idx_book_card ON book (card)
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card

在这里插入图片描述

3.3总结

  1. 语句优化应尽可能减少join语句中NestedLoop的循环总次数，即“永远用小结果集驱动大结果集”。
    优先优化NestedLoop的内层循环。
  2. 尽量保证join语句中被驱动表的条件字段添加了索引（即LEFT JOIN在右表上添加，反之亦然）。
  3. 当无法保证被驱动表的条件字段添加索引时，且内存资源充足的前提下，不妨调整join
    buffer以达到性能优化的目的。
MySQL索引优化的实际案例分析
09-10
主要介绍了MySQL索引优化的一些实际案例,主要是用到Order by desc/asc limit M的方法,需要的朋友可以参考下
MySQL索引优化实例说明
weixin_30741653的博客
04-22 112
下面分别创建三张表，并分别插入1W条简单的数据用来测试，详情如下： [1] test_a 有主键但无索引 CREATE TABLE `test_a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(100) NOT NULL, `content` text NOT NULL, `nu...
索引优化分析案例
爱笑的k11的博客
04-07 170
索引优化分析案例 预先准备好数据 SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS `itdragon_order_list`; CREATE TABLE `itdragon_order_list` ( `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，默认自增长', `transacti...
MySQL的索引单表优化案例分析
XYC3939的博客
02-08 210
建表 建立本次优化案例中所需的数据库及数据表 CREATE DATABASE db0206; USE db0206; CREATE TABLE `db0206`.`article`( `id` INT(11) NOT NULL AUTO_INCREMENT, `author_id` INT(11) UNSIGNED NOT NU...
【MySQL进阶】索引优化案例
Welcome,Everyone.
10-12 390
MySQL之索引优化单表索引优化案例 单表索引优化案例 例如：一个 article 表的字段如下： create table article( id int(10) unsigned primary key auto_increment comment '序号', author_id int(10) unsigned not null comment '作者id', category_id int(10) unsigned not null comment '分类id', comments in
Mysql索引优化案例.pdf
07-30
Mysql索引优化案例 在对数据库进行操作时，尤其是在处理包含大量数据的表时，查询的性能问题是一个常见且重要的话题。在实际工作中，对于Mysql数据库进行索引优化是提高查询效率、减少查询时间的重要手段。本案例将...
Mysql性能优化案例 – 覆盖索引分享
12-15
现在有一个根据user_id建立的索引：uid 查询语句也很简单：取得某用户的图片集合 代码如下: select picname, smallimg from pics where user_id = xxx; 优化前 执行查询语句（为了查看真实执行时间，强制不使用...
MYSQL索引优化案例分析
onlynima的博客
12-06 201
（一）单表 案例： 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) UNSIGNE...
MySQL索引的用处
weixin_33834137的博客
10-16 181
MySQL索引在MySQL数据库中，可以有效提高查询的效率，尤其是查询数据量非常大时，效果更为明显，往往能使查询速度加快成千上万倍。 MySQL索引是很重要的概念，应用的范围非常广。那么，MySQL索引究竟有何作用呢？下文将带您一起寻找答案。 MySQL索引作用在索引列上，除了上面提到的有序查找之外，数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时...
Mysql 索引优化例子详解优化
码农桃子的博客
07-16 270
创建表、创建索引 分析case索引使用情况 一个20左右的表，建立三个左右的索引常规沟通，但是根据自己情况 1： explain select * from test where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4'; explain select * from test where c1 = 'a1' and c3 = ...
Mysql索引优化+小案例
袁的博客
09-29 429
Mysql索引优化+小案例
索引优化的案例
我就是我的博客
03-01 269
文章目录索引优化单表优化过程多表优化过程总结 索引优化 单表优化过程 ## 建表语句 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) unsigne...
索引优化案例
仲翎逸仙
04-12 214
1. 单表     先建表                  #查询category_id 为1 且comments大于1的情况下,views最多的article_id    出现了全表扫描,而且出现了Using filesort ,必须优化.     该案例部分用到了索引, 由于在comment处出现了范围查询,导致后面的views字段索引失效,所以出现了using filesort.     ...
MySQL - 索引优化案例实操
热门推荐
小工匠
08-02 1万+
文章目录DB VersionTable DB Version mysql> select version(); +------------+ | version() | +------------+ | 5.7.29-log | +------------+ 1 row in set 默认隔离级别 RR 可重复读 Table CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar
MySQL索引优化案例
woshiwjma956的博客
05-13 151
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
MySQL-02.索引优化实例
weixin_49352973的博客
08-22 463
1.数据准备 #1.建立员工表，并创建name,age,position索引，id为自增主键 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DE
Mysql查询语句优化之索引优化案例
I can @ me
07-15 270
 sql优化说到底还是索引的优化,创建索引并使之生效.   单表优化CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `stuName` varchar(60) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` varchar(30) DEFAULT NULL,...
陌生人社交APP MySQL索引优化案例解析
资源摘要信息:《行业-83案例实战：陌生人社交APP的MySQL索引设计实战（3）》是一份专注于MySQL数据库索引优化的实战教程文档。这份文档很可能详细地介绍了在开发和优化一个陌生人社交应用时，针对MySQL数据库所采用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

东北亚大中华区首席搬砖工具人

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值