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 NO3

EXPLAIN SELECT ab.name,c.`name` ceoname FROM

(SELECT a.`name`,b.`CEO` FROM emp a

LEFT JOIN dept b ON a.`deptId`=b.`id`)ab

LEFT JOIN emp c ON ab.ceo=c.`id`;



#2 NO4

EXPLAIN SELECT c.name,ab.name ceoname FROM emp c LEFT JOIN

(SELECT a.`name`,b.`id` FROM emp a

INNER JOIN dept b ON b.`CEO` = a.`id`)ab

ON c.`deptId`= ab.id;



#3  NO1

 EXPLAIN SELECT a.`name`,c.`name` ceoname FROM emp a

LEFT JOIN dept b  ON a.`deptId`= b.id

LEFT JOIN emp c ON b.`CEO`= c.`id`;



#4  NO2

EXPLAIN SELECT a.`name`,(SELECT c.name FROM emp c WHERE c.id =b.`CEO`)ceoname

 FROM emp a

LEFT 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、高性能架构模式

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

ELK日志分析系统简介

ELK日志分析系统简介

ELK日志分析系统简介 ElasticsearchLogstashKibana主要功能Kibana日志处理步骤ELK的工作原理 日志服务器 提高安全性 集中存放日志 缺陷 ​ 对日志的分析困难 ELK日志分析系统 Elasticsearch 概述:提供了一个分布式多用户能力的全文搜索引擎 核心概念 接近实时 集群 节…
阅读更多...
特性Attribute

特性Attribute

本文只提及常用的特性&#xff0c;更多特性请查看官方文档。 AddComponentMenu - Unity 脚本 API 常用特性 AddComponentMenu 添加组件菜单 使用 AddComponentMenu 属性可在“Component”菜单中的任意位置放置脚本&#xff0c;而不仅是“Component > Scripts”菜单。 使用…
阅读更多...
GODOT游戏引擎简介，包含与unity性能对比测试，以及选型建议

GODOT游戏引擎简介，包含与unity性能对比测试，以及选型建议

GODOT&#xff0c;是一个免费开源的3D引擎。本文以unity作对比&#xff0c;简述两者区别和选型建议。由于是很久以前写的ppt&#xff0c;技术原因视频和部分章节丢失了。建议当做业务参考。 GODOT目前为止遇到3个比较重大的基于&#xff0c;第一个是oprea的合作奖&#xff0c;…
阅读更多...
嵌入式开发学习（STC51-13-温度传感器）

嵌入式开发学习（STC51-13-温度传感器）

内容 通过DS18B20温度传感器&#xff0c;在数码管显示检测到的温度值&#xff1b; DS18B20介绍 简介 DS18B20是由DALLAS半导体公司推出的一种的“一线总线&#xff08;单总线&#xff09;”接口的温度传感器&#xff1b; 与传统的热敏电阻等测温元件相比&#xff0c;它是一…
阅读更多...
【C++】map和set

【C++】map和set

目录 一、容器补充1.序列式容器与关联式容器2.键值对3.树形结构的关联式容器 二、set1.set的介绍2.set的使用3.multset的介绍4.multset的使用 三、map1.map的介绍2.map的使用3.multimap的介绍4.multimap的使用 一、容器补充 1.序列式容器与关联式容器 我们已经接触过STL中的部…
阅读更多...
Mysql自动同步的详细设置步骤

Mysql自动同步的详细设置步骤

以下步骤是真实的测试过程&#xff0c;将其记录下来&#xff0c;与大家共同学习。 一、环境说明&#xff1a; 1、主数据库&#xff1a; &#xff08;1&#xff09;操作系统&#xff1a;安装在虚拟机中的CentOS Linux release 7.4.1708 (Core) [rootlocalhost ~]# cat /etc/redh…
阅读更多...
Docker学习（二十四）报错速查手册

Docker学习（二十四）报错速查手册

目录 一、This error may indicate that the docker daemon is not running 报错docker login 报错截图&#xff1a;原因分析&#xff1a;解决方案&#xff1a; 二、Get "https://harbor.xxx.cn/v2/": EOF 报错docker login 报错截图&#xff1a;原因分析&#xff1a…
阅读更多...
使用ubuntu-base制作根文件系统

使用ubuntu-base制作根文件系统

1&#xff1a;ubuntu官网下载最小根文件系统&#xff1a; 放置到电脑的ubuntu中&#xff0c; Mkdir Ubuntu_rootfs Cd Ubuntu_rootfs Sudo tar –zxvf Ubuntu-bash-xxxxxx.tar.gz 2&#xff1a;电脑的ubuntu安装qemu搭建arm模拟系统 将/usr/bin/qemu-arm-static/(64位拷贝…
阅读更多...
（黑客）自学笔记

（黑客）自学笔记

一、自学网络安全学习的误区和陷阱 1.不要试图先成为一名程序员&#xff08;以编程为基础的学习&#xff09;再开始学习 行为&#xff1a;从编程开始掌握&#xff0c;前端后端、通信协议、什么都学。 缺点&#xff1a;花费时间太长、实际向安全过渡后可用到的关键知识并不多。…
阅读更多...
Java基础面试题2

Java基础面试题2

Java基础面试题 一、IO和多线程专题 1.介绍下进程和线程的关系 进程&#xff1a;一个独立的正在执行的程序 线程&#xff1a;一个进程的最基本的执行单位&#xff0c;执行路径 多进程&#xff1a;在操作系统中&#xff0c;同时运行多个程序 多进程的好处&#xff1a;可以充…
阅读更多...
[webpack] 基本配置 (一)

[webpack] 基本配置 (一)

文章目录 1.基本介绍2.功能介绍3.简单使用3.1 文件目录和内容3.2 下载依赖3.3 启动webpack 4.基本配置4.1 五大核心概念4.2 基本使用 1.基本介绍 Webpack 是一个静态资源打包工具。它会以一个或多个文件作为打包的入口, 将我们整个项目所有文件编译组合成一个或多个文件输出出去…
阅读更多...
macbook怎么卸载软件？2023年最新全新解析macbook电脑怎样删除软件

macbook怎么卸载软件？2023年最新全新解析macbook电脑怎样删除软件

macbook怎么卸载软件&#xff1f;2023年最新全新解析macbook电脑怎样删除软件。关于Mac笔记本如何卸载软件_Mac笔记本卸载软件的四种方法的知识大家了解吗&#xff1f;以下就是小编整理的关于Mac笔记本如何卸载软件_Mac笔记本卸载软件的四种方法的介绍&#xff0c;希望可以给到…
阅读更多...
LeetCode 热题 100 JavaScript--206. 反转链表

LeetCode 热题 100 JavaScript--206. 反转链表

/*** Definition for singly-linked list.* function ListNode(val, next) {* this.val (valundefined ? 0 : val)* this.next (nextundefined ? null : next)* }*/ /*** param {ListNode} head* return {ListNode}*/1、逐个断键&#xff0c;将后一个节点放到前面 …
阅读更多...
网络可靠性之链路聚合

网络可靠性之链路聚合

网络的可靠性 网络的可靠性指当设备或者链路出现单点或者多点故障时保证网络服务不间断的能力网络的可靠性是可以从单板、设备、链路多个层面实现。 链路聚合 以太网链路聚合&#xff1a; 通过将多个物理接口捆绑成为一个逻辑接口&#xff0c;可以再不进行硬件升级的条件下&a…
阅读更多...
neo4j入门实例介绍

neo4j入门实例介绍

使用Cypher查询语言创建了一个图数据库&#xff0c;其中包含了电影《The Matrix》和演员Keanu Reeves、Carrie-Anne Moss、Laurence Fishburne、Hugo Weaving以及导演Lilly Wachowski和Lana Wachowski之间的关系。 CREATE (TheMatrix:Movie {title:The Matrix, released:1999,…
阅读更多...
LeetCode-Java(06)

LeetCode-Java(06)

24. 两两交换链表中的节点 非递归解法 class Solution {public ListNode swapPairs(ListNode head) {ListNode pre new ListNode(0);pre.next head;ListNode temp pre;while(temp.next ! null && temp.next.next ! null) {ListNode start temp.next;ListNode end …
阅读更多...
Android平台GB28181设备接入端如何降低资源占用和性能消耗

Android平台GB28181设备接入端如何降低资源占用和性能消耗

背景 我们在做GB28181设备接入模块的时候&#xff0c;考虑到好多设备性能一般&#xff0c;我们一般的设计思路是&#xff0c;先注册设备到平台侧&#xff0c;平台侧发calalog过来&#xff0c;获取设备信息&#xff0c;然后&#xff0c;设备侧和国标平台侧维持心跳&#xff0c;…
阅读更多...
如何在Spring MVC中使用@ControllerAdvice创建全局异常处理器

如何在Spring MVC中使用@ControllerAdvice创建全局异常处理器

文章目录 前言一、认识注解&#xff1a;RestControllerAdvice和ExceptionHandler二、使用步骤1、封装统一返回结果类2、自定义异常类封装3、定义全局异常处理类4、测试 总结 前言 全局异常处理器是一种 &#x1f31f;✨机制&#xff0c;用于处理应用程序中发生的异常&#xff…
阅读更多...
HCIA---OSI/RM--开放式系统互联参考模型

HCIA---OSI/RM--开放式系统互联参考模型

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 文章目录 前言一、pandas是什么&#xff1f;二、使用步骤 1.引入库2.读入数据总结 一.OSI--开放式系统互联参考模型简介 OSI开放式系统互联参考模型是一种用于计算机网络通信…
阅读更多...
C# Blazor 学习笔记(11):路由跳转和信息传值

C# Blazor 学习笔记(11):路由跳转和信息传值

文章目录 前言路由跳转测试用例路由传参/路由约束想法更新&#xff1a;2023年8月4日 前言 Blazor对路由跳转进行了封装。 ASP.NET Core Blazor 路由和导航 NavigationManager 类 本文的主要内容就是全局的跳转 路由跳转 路由跳转就要用到NavigationManager 类。 其实最常用…
阅读更多...
最新文章

Copyright @ 2022~2023