mysql高级三：sql性能优化+索引优化+慢查询日志

内容介绍
单表索引失效案例

0、思考题：如果把100万数据插入MYSQL ，如何提高插入效率

（1）关闭自动提交，只手动提交一次

（2）删除除主键索引外其他索引

（3）拼写mysql可以执行的长sql，批量插入数据

（4）使用java多线程

（5）使用框架，设置属性，实现批量插入

1、计算、函数导致索引失效

CREATE INDEX idx_name ON emp (NAME);

EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';

EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; ----索引失效

2 LIKE以%开头索引失效

EXPLAIN SELECT * FROM emp WHERE NAME LIKE '%ab%'; ----索引失效

3、不等于(!= 或者<>)索引失效

EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;

EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; ----索引失效

4、IS NOT NULL 和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;

EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; ----索引失效

5、类型转换导致索引失效

EXPLAIN SELECT * FROM emp WHERE NAME='123';

EXPLAIN SELECT * FROM emp WHERE NAME= 123; ----索引失效

6、全值匹配我最爱

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND deptid = 4 AND emp.name = 'abcd';

CREATE INDEX idx_age ON emp(age);

CREATE INDEX idx_age_deptid ON emp(age,deptid);

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

7、最佳左前缀法则

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;

CREATE INDEX idx_age_name ON emp (age,NAME);

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd';

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30;

8、索引中范围条件右边的列失效

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abc' AND emp.deptId>1000 ;

CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);

关联查询优化

1、数据准备

-- 分类CREATE TABLE IF NOT EXISTS `class` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`card` INT(10) UNSIGNED NOT NULL,PRIMARY KEY (`id`));-- 图书CREATE TABLE IF NOT EXISTS `book` (`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`card` INT(10) UNSIGNED NOT NULL,PRIMARY KEY (`bookid`));-- 插入16条记录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)));-- 插入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)));

2、左外连接实例

（1）明确角色

（2）优化

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

CREATE INDEX idx_class_card ON class(card);

CREATE INDEX idx_book_card ON book(card);

*使用LEFT JOIN，前面的是驱动表、后面是被驱动表

针对两张表的连接条件涉及的列，索引要创建在被驱动表上，驱动表尽量是小表

  • 如果驱动表上没有where过滤条件
    • 当驱动表的连接条件没有索引时，驱动表是全表扫描
    • 当针对驱动表的连接条件建立索引时，驱动表依然要进行全索引扫描
    • 因此，此时建立在驱动表上的连接条件上的索引是没有太大意义的
  • 如果驱动表上有where过滤条件，那么针对过滤条件创建的索引是有必要的

3、内连接实例

EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

CREATE INDEX idx_class_card ON class(card);

CREATE INDEX idx_book_card ON book(card);

*使用INNER JOIN，驱动表、被驱动表不固定，mysql选择

MySQL优化器也会自动选择驱动表，自动选择驱动表的原则是：索引创建在被驱动表上，驱动表是小表。

4、分析4种查询sql（mysql5）

#1 NO3EXPLAIN SELECT ab.name,c.`name` ceoname FROM(SELECT a.`name`,b.`CEO` FROM emp aLEFT JOIN dept b ON a.`deptId`=b.`id`)abLEFT JOIN emp c ON ab.ceo=c.`id`;#2 NO4EXPLAIN SELECT c.name,ab.name ceoname FROM emp c LEFT JOIN(SELECT a.`name`,b.`id` FROM emp aINNER JOIN dept b ON b.`CEO` = a.`id`)abON c.`deptId`= ab.id;#3  NO1EXPLAIN SELECT a.`name`,c.`name` ceoname FROM emp aLEFT JOIN dept b  ON a.`deptId`= b.idLEFT JOIN emp c ON b.`CEO`= c.`id`;#4  NO2EXPLAIN SELECT a.`name`,(SELECT c.name FROM emp c WHERE c.id =b.`CEO`)ceonameFROM emp aLEFT JOIN dept b ON a.`deptId`=b.`id`;

5、总结

  • 保证被驱动表的JOIN字段已经创建了索引
  • 需要JOIN 的字段，数据类型保持绝对一致。
  • LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表 。减少外层循环的次数。
  • INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表 。选择相信MySQL优化策略。
  • 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
  • 衍生表建不了索引（MySQL5.5）

其他优化

1、子查询优化

（1）获取非掌门人成员

#获取非掌门人成员

CALL proc_drop_index("atguigudb","emp");

CALL proc_drop_index("atguigudb","dept");

SELECT * FROM t_emp a WHERE a.id NOT IN 

(SELECT b.ceo FROM t_dept b WHERE b.ceo IS NOT NULL);

EXPLAIN SELECT * FROM emp a WHERE a.id NOT IN 

(SELECT b.ceo FROM dept b WHERE b.ceo IS NOT NULL);

#子查询优化NOT IN 

EXPLAIN SELECT * FROM emp a LEFT JOIN dept b ON a.id = b.ceo

WHERE  b.id IS NULL;

（2）结论

尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx = xx WHERE xx IS NULL替代

2、排序优化

（1）实例

CALL proc_drop_index("atguigudb","emp");

CALL proc_drop_index("atguigudb","dept");

CREATE INDEX idx_age_deptid_name ON emp (age,deptid,`name`);

#无过滤，不索引

EXPLAIN SELECT * FROM emp ORDER BY age,deptid;

EXPLAIN SELECT * FROM emp ORDER BY age,deptid LIMIT 10;

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid;

#顺序错，不索引

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, `name`;

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, empno;

CREATE INDEX idx_age_deptid_empno ON emp (age,deptid,`empno`);

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY `name`, deptid;

EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;

#方向反，不索引

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, `name` DESC;

EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, `name` DESC;

  1. 总结

无过滤，不索引

顺序错，不索引

方向反，不索引

3、mysql索引选择

EXPLAIN SELECT * FROM emp WHERE age =30 AND empno <101000 ORDER BY `name`;

CREATE INDEX idx_age_empno ON emp (age,`empno`);

CREATE INDEX idx_age_name ON emp (age,NAME);

*当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

也可以将选择权交给MySQL：索引同时存在，mysql自动选择最优的方案：（对于这个例子，mysql选择idx_age_empno），但是，随着数据量的变化，选择的索引也会随之变化的。

4、双路排序和单路排序

（1）双路排序（慢）

取一批数据，要对磁盘进行两次扫描。众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

（2）单路排序（快）

它的效率更快一些，因为只读取一次磁盘，避免了第二次读取数据。并且把随机IO变成了顺序IO。但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

5、分组优化

  • group by 使用索引的原则几乎跟order by一致。但是group by 即使没有过滤条件用到索引，也可以直接使用索引（Order By 必须有过滤条件才能使用上索引）
  • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

6、覆盖索引优化

总结

  • 禁止使用select *，禁止查询与业务无关字段
  • 尽量利用覆盖索引

慢查询日志

1、如何对系统查询慢做索引优化

（1）找运维人员开启生产数据库慢查询日志

（2）等待1-2周时间，积累慢查询日志

（3）借助工具获取慢查询次数最多和查询时间最长的几个sql进行优化

（4）在生产数据库，使用EXPLAIN进行sql分析，找到瓶颈，创建索引优化

（5）关闭慢查询日志。

2、是什么

一种日志记录，查看哪些SQL超出了我们的最大忍耐时间值。

3、使用

（1）开启slow_query_log

SET GLOBAL slow_query_log=1;

SHOW VARIABLES LIKE '%slow_query_log%';

（2）修改long_query_time阈值

SHOW VARIABLES LIKE '%long_query_time%'; -- 查看值：默认10秒

SET GLOBAL long_query_time=0.1; -- 设置一个比较短的时间，便于测试

（3）运行sql

（4）查看慢查询日志

（5）使用工具分析慢查询日志

-- 查看mysqldumpslow的帮助信息

mysqldumpslow --help

-- 工作常用参考

-- 1.得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

-- 2.得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

-- 3.得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

-- 4.另外建议在使用这些命令时结合 | 和more 使用 ，否则语句过多有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

1、单表索引失效案例

2、关联查询优化

3、其他优化

4、慢查询日志

5、视图

6、高性能架构模式

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/84734.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

解释器模式-自定义语言的实现

解释器模式-自定义语言的实现

有时&#xff0c;我们希望输入一串字符串&#xff0c;然后计算机能够按照预先定义的文法规则来对这个字符串进行解释&#xff0c;从而实现相应的功能。 例如&#xff0c;我们想实现简单的加减法接收器&#xff0c;只需输入一个表达式&#xff0c;它就能计算出表达式结果。比如…
阅读更多...
专注于创意设计，为您的小程序和网站建设带来更多的可能性

专注于创意设计，为您的小程序和网站建设带来更多的可能性

随着移动互联网的快速发展&#xff0c;越来越多的企业开始关注小程序和网站建设&#xff0c;以此来拓展业务和提升品牌形象。 在这个领域中&#xff0c;创意设计扮演着关键的角色。它不仅可以帮助企业打造独特的形象和品牌&#xff0c;还能够提高用户体验和购买决策的效率。 因…
阅读更多...
Word转PDF在线转换如何操作？分享转换技巧

Word转PDF在线转换如何操作？分享转换技巧

现如今&#xff0c;pdf转换器已成为大家日常办公学习必不可少的工具&#xff0c;市场上的pdf转换器主要有两种类型&#xff0c;一种是需要下载安装的&#xff0c;另一种是网页版&#xff0c;打开就可以使用的&#xff0c;今天小编给大家推荐一个非常好用的网页版pdf转换器&…
阅读更多...
react中使用路由起手式,一些思路和细节。

react中使用路由起手式,一些思路和细节。

一.安装并配置 我们选择使用react-router实现路由效果 yarn add react-router-dom下载后需要对Route进行引入&#xff0c;是个内置的组件。该组件是有两个属性一个是path&#xff0c;一个是component&#xff0c;path是组件对应的路由&#xff0c;component是对应的组件 二.…
阅读更多...
UG NX二次开发（C#）-CAM自定义铣加工的出口环境

UG NX二次开发（C#）-CAM自定义铣加工的出口环境

文章目录 1、前言2、自定义铣削加工操作3、出错原因4、解决方案4.1 MILL_USER的用户参数4.2 采用自定义铣削的方式生成自定义的dll4.2 配置加工的出口环境4.3 调用dll5、结论1、前言 作为一款大型的CAD/CAM软件， UG NX为我们提供了丰富的加工模板，通过加工模板能直接用于生成…
阅读更多...
Spring Initailizr--快速入门--SpringBoot的选择

Spring Initailizr--快速入门--SpringBoot的选择

&#x1f600;前言 本篇博文是关于IDEA使用Spring Initializer快速创建Spring Boot项目的说明&#xff0c;希望能够帮助到您&#x1f60a; &#x1f3e0;个人主页&#xff1a;晨犀主页 &#x1f9d1;个人简介&#xff1a;大家好&#xff0c;我是晨犀&#xff0c;希望我的文章可…
阅读更多...
Python-OpenCV中的图像处理-图像平滑

Python-OpenCV中的图像处理-图像平滑

Python-OpenCV中的图像处理-图像平滑 图像平滑平均滤波高斯模糊中值模糊双边滤波 图像平滑 使用低通滤波器可以达到图像模糊的目的。这对与去除噪音很有帮助。其实就是去除图像中的高频成分&#xff08;比如&#xff1a;噪音&#xff0c;边界&#xff09;。所以边界也会被模糊…
阅读更多...
【Linux】TCP协议——传输层

【Linux】TCP协议——传输层

目录 TCP协议 谈谈可靠性 TCP协议格式 序号与确认序号 窗口大小 六个标志位 确认应答机制&#xff08;ACK&#xff09; 超时重传机制 连接管理机制 三次握手 四次挥手 流量控制 滑动窗口 拥塞控制 延迟应答 捎带应答 面向字节流 粘包问题 TCP异常情况 TC…
阅读更多...
【已解决】mac端 sourceTree 解决remote: HTTP Basic: Access denied报错

【已解决】mac端 sourceTree 解决remote: HTTP Basic: Access denied报错

又是在一次使用sourcetree拉取或者提交代码时候&#xff0c;遇到了sourcetree报错&#xff1b; 排查了一会&#xff0c;比如查看了SSH keys是否有问题、是否与sourcetree账户状态有问题等等&#xff0c;最终才发现并解决问题 原因&#xff1a; 因为之前公司要求企业gitlab中…
阅读更多...
Linux/centos上如何配置管理samba服务器？

Linux/centos上如何配置管理samba服务器？

Linux/centos上如何配置管理samba服务器&#xff1f; 1 samba服务相关知识1.1 SMB协议1.2 samba工作原理1.2.1 相关进程1.2.2 samba工作流程1.2.3 samba功能 2 samba服务器安装2.1 利用光驱安装2.2 利用光盘映射文件 3 启动与停止samba服务4 配置samba服务器4.1 samba主配置文件…
阅读更多...
数据结构—图的遍历

数据结构—图的遍历

6.3图的遍历 遍历定义&#xff1a; ​ 从已给的连通图中某一顶点出发&#xff0c;沿着一些边访问遍历图中所有的顶点&#xff0c;且使每个顶点仅被访问一次&#xff0c;就叫作图的遍历&#xff0c;它是图的基本运算。 遍历实质&#xff1a;找每个顶点的邻接点的过程。 图的…
阅读更多...
ElasticSearch：项目实战（2）

ElasticSearch：项目实战（2）

ElasticSearch: 项目实战 (1) 需求&#xff1a; 新增文章审核通过后同步数据到es索引库 1、文章服务中添加消息发送方法 在service层文章新增成功后&#xff0c;将数据通过kafka消息同步发送到搜索服务 Autowiredprivate KafkaTemplate<String,String> kafkaTemplate;/…
阅读更多...
Linux系统调试课：Linux Kernel Printk

Linux系统调试课：Linux Kernel Printk

🚀返回专栏总目录 文章目录 0、printk 说明1、printk 日志等级设置2、屏蔽等级日志控制机制3、printk打印常用方式4、printk打印格式0、printk 说明 在开发Linux device Driver或者跟踪调试内核行为的时候经常要通过Log API来trace整个过程，Kernel API printk()是整个Kern…
阅读更多...
学习C语言第三天 ：关系操作符、逻辑操作符

学习C语言第三天 ：关系操作符、逻辑操作符

1.关系操作符 C语言用于比较的表达式&#xff0c;称为“关系表达式”里面使用的运算符就称(relationalexpression)&#xff0c;为“关系运算符” (relationaloperator) &#xff0c;主要有下面6个。 > 大于运算符 < 小于运算符 > 大于等于运算符 < 小于等…
阅读更多...
Docker安装Hadoop分布式集群

Docker安装Hadoop分布式集群

一、准备环境 docker search hadoop docker pull sequenceiq/hadoop-docker docker images二、Hadoop集群搭建 1. 运行hadoop102容器 docker run --name hadoop102 -d -h hadoop102 -p 9870:9870 -p 19888:19888 -v /opt/data/hadoop:/opt/data/hadoop sequenceiq/hadoop-do…
阅读更多...
二叉树题目：根据二叉树创建字符串

二叉树题目：根据二叉树创建字符串

文章目录 题目标题和出处难度题目描述要求示例数据范围 解法一思路和算法代码复杂度分析 解法二思路和算法代码复杂度分析 题目 标题和出处 标题&#xff1a;根据二叉树创建字符串 出处&#xff1a;606. 根据二叉树创建字符串 难度 3 级 题目描述 要求 给你二叉树的根结…
阅读更多...
【广州华锐视点】AR电力职业技能培训系统让技能学习更“智慧”

【广州华锐视点】AR电力职业技能培训系统让技能学习更“智慧”

随着科技的发展&#xff0c;教育方式也在不断地进步和创新。其中&#xff0c;增强现实(AR)技术的出现&#xff0c;为教育领域带来了全新的可能。AR电力职业技能培训系统就是这种创新教学方法的完美实践&#xff0c;它将虚拟与现实相结合&#xff0c;为学生提供了一个沉浸式的学…
阅读更多...
Token 失效退出至登录页面

Token 失效退出至登录页面

1. 在登录页面&#xff0c;调用登录的接口后&#xff0c;直接写上当前时间&#xff0c;保存在本地 代码&#xff1a; // 点击登录login(form) {this.$refs[form].validate((valid) > {if (valid) {this.$API.Login(this.form).then((res) > {// console.log(res, "1…
阅读更多...
AIRIOT出席IOTE生态行·北京物联网应用交流大会

AIRIOT出席IOTE生态行·北京物联网应用交流大会

8月8日&#xff0c;由物联传媒、IOTE物联展、AIoT库、AIoT星图研究院联合主办的IOTE生态行北京物联网应用交流大会圆满结束&#xff0c;超300位业界同行同台交流。 航天科技控股集团股份有限公司受邀参会&#xff0c;旗下AIRIOT物联网平台产品负责人段丽娜发表演讲&#xff0c;…
阅读更多...
JVM 性能优化思路

JVM 性能优化思路

点击下方关注我&#xff0c;然后右上角点击...“设为星标”&#xff0c;就能第一时间收到更新推送啦~~~ 一般在系统出现问题的时候&#xff0c;我们会考虑对 JVM 进行性能优化。优化思路就是根据问题的情况&#xff0c;结合工具进行问题排查&#xff0c;针对排查出来的可能问题…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发