动力节点索引优化解决方案学习笔记——查询优化

最新推荐文章于 2024-07-18 21:11:52 发布
最新推荐文章于 2024-07-18 21:11:52 发布
阅读量304 收藏
点赞数
分类专栏： mysql 文章标签： 学习 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/G823909/article/details/127941683
版权

3.查询优化

创建一张测试表并插入数据：

drop table if exists students;
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id",
  sname VARCHAR (24) COMMENT '学生姓名',
  age INT COMMENT '年龄',
  score INT COMMENT '分数',
  time TIMESTAMP COMMENT '入学时间'
);
 
INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,time) VALUES('小红',23,80,now());
INSERT INTO students(sname,age,score,time) VALUES('小绿',24,80,now());
INSERT INTO students(sname,age,score,time) VALUES('黑',23,70,now());

3.1索引失效

1）最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。

alter table students add index idx_sname_age_score(sname,age,score);

-- 索引失效情况
① explain select * from students where sname="小明" and age = 22 and score = 100;
② explain select * from students where sname="小明" and age = 22;
③ explain select * from students where sname="小明";
④ explain select * from students where sname="小明" and score = 80;

可以看到①②都遵守最佳左前缀法则，但是②的where条件没有完全覆盖包含索引的列，所以①的key_len相对较高，即索引使用率高
③遵守但是④不遵守，两者都锁定了sname，但是score使索引不再生效，所以③④的索引使用率一样
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2）不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。

-- 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。
explain select * from students where left(sname,2) = "小明";

在这里插入图片描述

3）存储引擎不能使用索引中范围条件右边的列。

-- 存储引擎不能使用索引中范围条件右边的列。
explain select * from students where sname="小明" and age > 22 and score = 100;

在这里插入图片描述

4）Mysql在使用不等于时无法使用索引会导致全表扫描。

-- Mysql在使用不等于时无法使用索引会导致全表扫描。
explain select * from students where sname!="小明";

在这里插入图片描述

5）is null可以使用索引，但是is not null无法使用索引。

-- is null可以使用索引，但是is not null无法使用索引。
explain select * from students where sname is not null;

在这里插入图片描述

6）like以通配符开头会使索引失效导致全表扫描。

-- like以通配符开头会使索引失效导致全表扫描。
explain select * from students where sname like "%黑%";

在这里插入图片描述

7）字符串不加单引号索引会失效。

-- 字符串不加单引号索引会失效。
explain select * from students where sname = 123;

在这里插入图片描述

8）使用or连接时索引失效。

-- 使用or连接时索引失效。
explain select * from students where sname="小明" or age = 22;

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

建议：
1.对于单值索引，尽量选择针对当前查询字段过滤性更好的索引。
2.对于组合索引，当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好。
3.对于组合索引，尽量选择能够包含在当前查询中where子句中更多字段的索引。
4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

3.2单表查询优化

例子：

-- 单表查询优化
CREATE TABLE IF NOT EXISTS article (
id INT(10) PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) NOT NULL,
category_id INT(10) NOT NULL,
views INT(10) NOT NULL,
comments INT(10) 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'),
(1, 1, 3, 3, '3', '3');

#1.查询category_id为1的，且comments大于1的情况下，views最多的id和author_id的信息
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;
#2.建立索引
alter table article add index idx_ccv(category_id,comments,views);
#3.再次测试
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;
#4.重新创建索引
drop index idx_ccv on article;
alter table article add index idx_cv(category_id,views);
#5.再次测试
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;

3.3关联查询优化

例子：

CREATE TABLE IF NOT EXISTS class (
id INT(10) AUTO_INCREMENT,
card INT(10),
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS book (
bookid INT(10) AUTO_INCREMENT,
card INT(10),
PRIMARY KEY (bookid)
);
 
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.联表查询
explain select *
from class
left join book
on class.card = book.card;
#2.建立索引
alter table book add index idx_card(card);
#3.测试
explain select *
from class
left join book
on class.card = book.card;

内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。我们最好保证被驱动表上的字段建立了索引。

3.4排序优化

在这里插入图片描述

1.尽量避免使用Using FileSort方式排序。
2.order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。
3.where子句中如果出现索引范围查询会导致order by索引失效。

3.5分组优化

drop table if exists students;
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id",
  sname VARCHAR (24) COMMENT '学生姓名',
  age INT COMMENT '年龄',
  score INT COMMENT '分数',
	time TIMESTAMP COMMENT '入学时间'
);
 
INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,time) VALUES('小红',23,80,now());
INSERT INTO students(sname,age,score,time) VALUES('小绿',24,80,now());
INSERT INTO students(sname,age,score,time) VALUES('黑',23,70,now());

-- 分组优化
alter table students add index idx_sas(sname,age,score);
explain select count(*),sname 
from students 
where sname="小明" and age > 22
GROUP BY score;
--只因--
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql索引优化解决方案.doc
03-07
"Mysql索引优化解决方案" MySQL索引是帮助MySQL高效获取数据的数据结构。MySQL在存储数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种引用（指向）表中的数据，这样我们就可以通过数据结构上实现...
MySQL学习笔记(3)——索引练习
12-14
根据之前的MYSQL学习笔记(2)的学习，通过一些小练习来加深对索引的理解。 建下表并插入数据最后建立相关索引： CREATE TABLE IF NOT EXISTS `test`( id int PRIMARY KEY auto_increment, c1 char(10), c2 char(10), ...
动力节点索引优化解决方案学习笔记——索引介绍
G823909的博客
11-19 591
MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。
mysql索引优化
LJJ1338的博客
09-10 1965
SQL如下： CREATE TABLE IF NOT EXISTS staffs( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(24) NOT NULL DEFAULT "" COMMENT'姓名', age INT NOT NULL DEFAULT 0 COMMENT'年龄', pos VARCHAR(20)...
动力节点索引优化解决方案学习笔记——性能分析
G823909的博客
11-19 441
SQL中对大量数据进行比较、关联、排序、分组时CPU的瓶颈。实例内存满足不了缓存数据或排序等需要，导致产生大量的物理IO。查询数据时扫描过多数据行，导致查询效率低。
Mysql索引性能优化问题解决方案
01-19
mysql 创建的优化就是加索引,可是有时候会遇到加索引都没法达到想要的效果的情况, 加上了所以,却还是搜索的全数据,原因是sql EXPLAIN SELECT cs.sid, -- c.courseFrontTitle, -- c.imgBig, cs.studyStatus, ...
MySQL学习笔记(2)——索引
12-14
总的来说，理解并合理使用索引是优化MySQL查询性能的关键，需要根据实际情况权衡索引带来的增益和维护成本。在设计数据库时，应考虑查询模式，有针对性地创建和使用索引，同时注意避免上述可能导致索引失效的操作。...
SQL学习笔记——表结构转换查询
01-21
1、纵表转横表 建表 --先调用自建的数据库再建表 use ljh create table sc( sname char(20), course char(20), score int) --随机插入几个记录 insert into sc ...转换表结构查询 select sname as 'Nam
2024Datawhale AI夏令营---基于术语词典干预的机器翻译挑战赛--学习笔记
weixin_61573157的博客
07-15 1079
机器翻译（Machine Translation，简称MT）是自然语言处理领域的一个重要分支，其目标是将一种语言的文本自动转换为另一种语言的文本。机器翻译的发展可以追溯到20世纪50年代，经历了从基于规则的方法、统计方法到深度学习方法的演变过程。当前，机器翻译正朝着更加智能化和个性化方向发展。一方面，结合上下文理解、情感分析等技术，提高翻译的准确性和自然度；另一方面，通过用户反馈和个性化学习，提供更加符合用户需求的翻译服务。同时，跨语言信息检索、多模态翻译等新兴领域也正在成为研究热点。
昇思25天学习打卡营第14天|LLM-文本解码原理--以MindNLP为例
wwt72的博客
07-17 602
限制输出序列的最大长度为50个token。top-p=0.95，top-p采样表示在每一步生成token时，只从概率分布中累计概率达到95%的token中进行采样，有助于保持生成文本的流畅性和质量，同时允许一些低概率的token被选中，从而增加多样性。表示禁用了top-k采样，因为在top-k采样中，通常是从概率最高的k个token中随机选择一个token作为下一个输出，而这里设置为0表示不限制token的选择，实际上这将等同于使用 softmax 概率分布直接进行采样。这有助于提高生成文本的多样性。
kubernetes学习日志（七）
最新发布
qq_47037022的博客
07-18 346
本文记录了service管理，ingress管理，Dashboard管理插件，集群鉴权策略，角色与全局角色，赋权与全局角色赋权。
NumPy库学习之np.random.rand函数
qq_46396470的博客
07-15 302
是 NumPy 库中的一个函数，用于生成随机数。这些随机数是从均匀分布 [0, 1) 中抽取的，即每个数都在0到1之间，但不包括1。
实现了一个心理测试的小程序，微信小程序学习使用问题总结
yuanfangderen
07-15 342
6. 如何获取多个 radio 框的值（不是表单）。可以借助bindchange 方法 加 e.target.dataset.xx， xx 类似js的name属性，然后e.detail.value 相当于js的 value。bindchange 方法 参数e, e.detail.value。如果想要获取其他属性，使用data-xx 指定，然后 e.target.dataset.xx 获取。3. 不刷新页面的情况下，清除radio选中的值。7. 使用setData 才会双向绑定 标签中的元素。
纯前端小游戏，4096小游戏，有音效，Html5，可学习使用
m0_62996549的博客
07-18 231
【代码】纯前端小游戏，4096小游戏，有音效，Html5，可学习使用。
钓鱼攻击 - 基础学习
Reset
07-16 695
钓鱼攻击是一种典型的欺诈式攻击手段，攻击者通过伪装成可以信任的角色，利用电子邮件或其他通信渠道向被攻击者发送植入了木马的文档或恶意链接，并诱骗被攻击者点击执行，从而实现对被攻击者计算机的远程控制或恶意程序感染。
python 语法学习 day 7
2303_79973545的博客
07-15 311
-----元组之间可以进行比较，比较的规则是逐个比较元组中的元素。首先比较第一个元素，如果相等，则比较第二个元素，以此类推。如果所有元素都相等，那么元组相等；否则，比较的结果取决于第一个不相等元素的比较结果。题意:统计单词的种类以及数量(忽略大小写)，最终以降序输出（出现次数相同的单词根据单词的大小升序输出）-------掌握如何将一个英语句子中的单词拆出来,并且去掉标点符号。EOF，输入后产生异常-->>捕获异常，结束输入。第一次提交的答案是：先把所有输入值放在列表里面。------字典排序。
安卓学习中遇到的问题【bug】
小心星的博客
07-15 1070
安卓学习中遇到的问题【bug】
Cadence23学习笔记（三）
zjb6668的博客
07-15 349
焊接FPC的时候先上锡，焊台加热后再图上焊油，再放置元器件，再加热：万用表测电流只需要把表笔接到电流的孔位即可测量电流，不需要接再串联一个负载：有些线性电源两个通道可以串联，并联起来使用；
【C++进阶学习】第六弹——set和map——体会用C++来构建二叉搜索树
2301_80220607的博客
07-12 4355
【C++进阶学习】第六弹——set和map——数据结构进阶知识点，适合初学者学习！！！
WPF学习(7) --MVVM模式
Fourglsl的博客
07-15 325
WPF学习(7) --MVVM模式

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值