mysql之优化案例

最新推荐文章于 2024-07-23 18:29:36 发布
最新推荐文章于 2024-07-23 18:29:36 发布
阅读量597 收藏
点赞数
分类专栏： mysql 文章标签： mysql sql 数据库
原文链接：https://blog.csdn.net/single_0910/article/details/113844814
版权

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');

  
  

  
  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

模拟查询

  • 查询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

在这里插入图片描述

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

如何优化

show index from article --查看表索引

  
  

  
  
  • 1

在这里插入图片描述

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

  
  

  
  
  • 1
  • 2
再次查看执行计划

在这里插入图片描述
在这里插入图片描述

  • 全表扫描已解决，但是文件排序依然存在
  • 索引不合适
删除并重建索引
DROP INDEX idx_article_ccv ON article -- 删除索引
CREATE INDEX idx_article_ccv ON article (category_id,views)  --重建索引

  
  

  
  
  • 1
  • 2
再次查看执行计划

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

双表简单案例

模拟数据

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)));

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49

查看执行计划

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

 
 

 
 
  • 1

在这里插入图片描述
由于是LEFT JOIN，所以左表是主表，因此第一次索引尝试加在主表上

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

 
 

 
 
  • 1
  • 2

在这里插入图片描述

结论：虽然type变为index，但是扫描行数依然是全表扫描。

  • 只对右表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

 
 

 
 
  • 1
  • 2
  • 3

在这里插入图片描述

结果：type变为ref，rows只扫描了一行。
结论：这是由于LEFT JOIN特性决定的，由于左表数据全都有，所以关键在于如何从右表进行搜索，所以右表一定要添加索引。

三表简单案例

模拟数据

  • 在双表的基础上新增一张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)));

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 三表均没有新建索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card

 
 

 
 
  • 1

在这里插入图片描述

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

  • 在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

 
 

 
 
  • 1
  • 2
  • 3

在这里插入图片描述

总结

  • 语句优化应尽可能减少join语句中NestedLoop的循环总次数，即“永远用小结果集驱动大结果集”。
  • 优先优化NestedLoop的内层循环。
  • 尽量保证join语句中被驱动表的条件字段添加了索引（即LEFT JOIN在右表上添加，反之亦然）。
  • 当无法保证被驱动表的条件字段添加索引时，且内存资源充足的前提下，不妨调整join buffer以达到性能优化的目的。
十 一 丶
关注
专栏目录
MySQL之优化案例
会者定离，一期一祈
02-19 2372
准备工作 模拟数据表并插入数据 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) UNSI
MySQL索引优化的实际案例分析
12-15
Order by desc/asc limit M是我在mysql sql优化中经常遇到的一种场景，其优化原理也非常的简单，就是利用索引的有序性，优化器沿着索引的顺序扫描，在扫描到符合条件的M行数据后，停止扫描；看起来非常的简单，但是...
MySQL优化实例
十年磨一剑的专栏
12-16 675
MySQL优化实例来源: ChinaUnix博客 　日期： 2008.10.10 23:42　(共有0条评论) 我要评论                 在Apache, PHP,MySQL的体系架构中，MySQL对于性能的影响最大，也是关键的核心部分。对于Discuz!论坛程序也是如此，MySQL的设置是否合理优化，直接影响到论坛的速度
分享30个MySQL调优案例，大胆的在简历上写“擅长MySQL”吧！
最新发布
radapp的博客
07-23 1062
今天介绍一下 MySQL 数据库优化，从网上收集了 30 个我觉得以我的水平可以用得上（看得懂）的，主打一个接地气能落地，吃好就好（咱也不是 DBA 应付一下面试好了），优化方式实在是太多了，不用给自己增加负担，不信你可以问问 Kimi, 分分钟给你列上百个.MySQL 优化可以从多个方面进行优化，当然对于研发来说，只需要关注 表结构 和 SQL 及索引即可，况且性价比最高。SQL优化策略适用于数据量较大的场景下，如果数据量较小（10万内），没必要，哈哈。
mysql数据库优化实例_实例剖析：MySQL数据库优化详解
weixin_42126399的博客
02-05 202
数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。本章主要讲解了几种优化MySQL的方法，并且给出了例子。记着，总有各种办法能让系统运行的更快，当然了，这需要更多的努力。1.优化概述让系统运行得快得最重要因素是数据库基本的设计。并且还必须清楚您的系统要用来做什么，以及存在...
MySQL 优化实例
雪锋的笔记
07-05 337
1：数据库的存储引擎是innodb 表中有1千多万数据，如何优化select count(*) from tab where 1;    解决方案： 1)取出最大的自增ID，并记录缓存  maxID                       2) select count(*) from tab where iAutoID <= MaxID; 记录缓存为fixCount          ...
Apache/MySQL/PHP 集体升级
weixin_33733810的博客
05-08 97
刚刚进行了一系列更新： PHP 升级到 5.2.6 MySQL 升级到 5.1.24-rc Apache 升级到 2.2.8 更新都比较顺利，先停掉 mysql 和 apache。 PLAIN TEXT >> CODE: net stop mysql httpd -k stop ...
MySQL优化案例系列-mysql分页优化
09-10
本文将深入探讨MySQL分页查询的优化技术。 ### 分页查询的传统方法及问题 在MySQL中，通常使用`ORDER BY ... LIMIT start, offset`的方式来进行分页查询。这种查询方式是通过给定一个起始位置（start）和偏移量...
Mysql性能优化案例 – 覆盖索引分享
12-15
产品中有一张图片表，数据量将近100万条，有一条相关的查询语句，由于执行频次较高，想针对此语句进行优化 表结构很简单，主要字段： 代码如下: user_id 用户ID picname 图片名称 smallimg 小图名称 一个用户会有...
mysql 优化经典案例
一束阳光
08-13 791
Mysql优化系列（1）--Innodb引擎下mysql自身配置优化 https://www.cnblogs.com/kevingrace/p/6133818.html
MySQL优化、问题案例分析
01-16
有实例的案例分析，深度解读sql 优化，开发测试运维都适用，你值得拥有！
mysql数据库优化实例_经过高手优化的MySQL数据库实例_MySQL
weixin_36302268的博客
02-05 204
据说这是高手优化的MySQL,供大家参考,其中连接数: max_connections=1500可以根据服务器的性能更改.#set-variable=connect_timeout=5#set-variable=wait_timeout=5建议启用,负担重的服务器可以适当减少持续连接时间[mysqld]basedir=D:/mysql#bind-address=ipdatadir=D:/mysql...
MySQL优化案例
Filwaod
08-02 550
准备：两表，一个dept，一个emp dept表 CREATE TABLE `dept` ( `dno` int(10) NOT NULL DEFAULT '0', `dname` varchar(50) NOT NULL DEFAULT '', `dlocation` varchar(30) DEFAULT '', PRIMARY KEY (`dno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; emp表 CREATE TABLE `emp` ( `
mysql百万数据设计和优化_mysql 优化实例（百万级数据）
weixin_42355272的博客
01-27 234
$db=dblink();$db->pagesize=20;$sql="select id from collect where vtype=$vtype";$db->execute($sql);$strpage=$db->strpage();//将分页字符串保存在临时变量，方便输出while($rs=$db->fetch_array()){$strid.=$rs['id'...
Mysql语句优化的原则——让你写sql更加顺手
热门推荐
u011277123的博客
12-26 1万+
IT小明 2017-12-22 07:12:41 使用索引的原则： 1.最左前缀匹配原则。 mysql会一直向右匹配直到遇到范围查询(>、 不会用到b的索引： where a=1 and c>0 and b=2 会用到b的索引： where a=1 and b=2 and c>0 2.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/cou
MySql sql优化实例
nidongla的博客
03-29 93
记录一次在工作中遇到sql优化实例： 　　mysql优化一直是工作中一个非常普遍的问题，我们工作中会不可避免的遇到需要根据业务去写一些比较复杂的sql语句，或者更多的呢，是我们在新接手一个项目后，尤其是一些特别老的业务系统，经常会遇到一些查询非常慢的问题。这时候就需要我们去想办法来改善我们的查询方式，来提高我们的查询效率； 前提： 　　目前我基于的存储引擎为INNODB 　　 　　我们在开始之前先得理解， 　　一、我们mysql中的数据最终是存储在磁盘上，而内存与磁盘之间的交互速度是受限制的，除非物理上提.
mysql优化实例
idontbuyit的专栏
10-17 281
1.查询所有字段的sql耗时：  0.003497 秒 2.查询所需字段sql耗时(limit 3)：0.001194 秒 SELECT group_sn,small_desc,group_name,end_time,group_image,(already_orders+already_orders_virtual) as already_orders,ext_info,market
mysql sql优化案例
10-20
下面是一个MySQL SQL优化案例： 假设有一个订单表（order）和一个商品表（product），订单表有订单ID、订单时间和订单金额等字段，商品表有商品ID、商品名称、商品价格等字段。现在要查询订单表中某个时间段的订单...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值