MySQL调优

news/2024/5/11 5:56:32/文章来源:https://blog.csdn.net/qq_41595149/article/details/129192630

MySQL调优

数据库优化常见方案

  1. 优化shema,sql语句+索引
  2. 加缓存，memcached,redis
  3. 主从复制，读写分离
  4. 垂直拆分
  5. 水平拆分

为了知道怎么优化SQL,必须先清楚SQL的生命周期

SQL生命周期

  1. 应用服务器连接数据库服务器，建立一个TCP/IP连接，发送SQL请求给MySQL服务器
  2. 查询缓存，有缓存则直接返回数据到应用服务器，没有则进入到SQL解析器
  3. SQL解析器：匹配SQL语句，主要是解析语法是否正确,查询中的表，列名是否存在，检查表名，列名是否有歧义
  4. 查询优化器:MySQL服务器自己对SQL做优化找到SQL的最佳执行方案，生成执行计划，优化的方面有索引优化（利用索引和列是否为空来优化count,min，max等聚合函数），顺序优化（重新定义表的关联关系），将外连接转换为内连接，使用等价变换，比如（1=1 and a>1）将被优化为a>1,如果索引列包含查询的所有列，则使用索引返回需要的数据，把子查询转换成关联查询，减少表的查询次数，
  5. SQL执行器：判断用户权限，根据执行计划调用存储引擎接口获取数据
  6. 将处理结果通过连接返回到应用服务器

image-20230219180341134

慢查询日志

在优化SQL前需要先找到需要优化的SQL,一般是通过慢查询日志来查询

查询是否开启慢查询日志

SHOW VARIABLES LIKE 'slow_query_log';

image-20230223073602878

开启慢查询日志

SET GLOBAL slow_query_log = 'ON';

查询慢查询日志的路径

默认和数据文件放一起

show VARIABLES like '%slow_query_log_file%';

image-20230219142030300

慢查询记录时间的阈值

默认十秒

show VARIABLES like '%long_query_time%';

image-20230219142051315

查询是否开启记录未使用索引的SQL

show VARIABLES like '%log_queries_not_using_indexes%';

image-20230223074706493

找到了需要优化的SQL,下面开始分析SQL的组成

SQL执行计划

MySQL使用explain关键字来分析SQL，只要在SQL语句前加上一个explain关键字，就可以得到一个SQL的执行计划

explain select * from sys_user where id = 2979;

image-20230219154514132

执行计划字段详解

ID

执行顺序的标识，值越大的越优先执行，相同的值由上往下执行

select_type

查询语句的类型，下面是各个值

  1. SIMPLE：简单的select查询，不包含任何子查询和联合查询
  2. PRIMARY:主查询，如果有子查询的话，最外层的查询会被标记为PRIMARY
  3. SUBQUERY:在select或where列表中包含了子查询，表示该语句属于子查询语句
  4. DERIVED:生成的临时表的查询语句，也就是子查询from的一部分
  5. DEPENDENT SUBQUERY:子查询中的第一个SELECT,子查询依赖于外出查询的结果
  6. UNION:表示union中的第二个或后面的select 语句
  7. UNION RESULT:从UNION语句中获取结果

table

显示这一行的数据来源于那张表

type

定位SQL性能因素最重要的指标，值包括system,const,eq_ref,ref,Range,index,All,性能从高到低

  • System: 表只有一行记录，基本不会出现
  • Const:通过索引一次就找到了数据，一般出现在使用了primary key或者unique索引匹配到了数据，匹配的条件常量(字符串，数字)
  • eq_ref:使用主键索引或者非空唯一索引，在表中只有一条记录与索引键匹配，匹配条件是某个表的列(需要转义替换才能拿到的值，简单理解为关联查询)
  • ref:非唯一性索引扫描，和eq_ref不同的是eq_ref匹配的是唯一索引，ref它返回所有匹配某个单独值的行，它可能会找到多个符合条件的行
  • range:范围数据扫描
  • index:全索引扫描,通过扫描整棵索引树来获取到的结果
  • All:全表扫描

possible_keys

可能会用到的索引

Key

实际使用的索引，如果为空，表示没有使用索引

key_len

使用到的索引key长度，如果为联合索引则显示已命中的联合索引长度之和(如：联合索引为a+b+c,如果索引命中了a+b,那么长度就为a+b的索引长度，通过可以通过key_len来分析联合索引所命中的情况)

关于possible_keys和key的三种关系场景

possible_keys != null && key != null：正常使用到了索引的情况

possible_keys != null && key==null,这种情况说明通过索引并不能提升多少效率，一般在表的数据量很少，或者是索引的字段离散性不高，执行计划发现用索引和扫描差不多

possible_keys == null && key!= null：这种情况一般为where条件没有命中索引，但是查询的列是索引字段，也就是查询的列命中了覆盖索引的情况

ref

实际用到的索引是哪个表的列，const代表常量

row

扫描的数据行数，不是准确的值，只是估算，一般来说扫描的数据行数越少，性能越好

filtered

返回结果的行数占需读取行数的百分比，值越大越好

rows

查询的结果集大小

Extra

对整个SQL做概括性总结，包含使用了什么索引，排序方式

  • using where:使用了where条件.
  • using index:使用了覆盖索引（通常是一种好现象，意味着查询的数据直接在二级索引返回了，从而减少了回表的过程）。
  • using filesort:文件排序，使用了非索引的字段进行排序(通常这种情况需要优化)。
  • using index sort:使用了索引排序,通常这是一种好现象，索引天然有序，避免了通过sort buffer来排序的流程
  • using temporary:使用了临时表（常见于group by,order by）
  • using join buffer:使用 了join buffer缓存(这种情况关注一下查询的字段是不是没有建立索引)
  • using index condition:索引下推

SQL优化

优化原则

正确使用索引

优化查询列

尽量避免select *，改使用select 列名，避免返回多余的列。

优化前：select * from sys_suer
优化后:select id,username,nickname,mobile from sys_user

优化where子句

优化方案：避免索引失效，可能导致全表扫描的情况

  1. 避免对字段进行null判断，用特殊值代替，如0

    优化前：select * from sys_user where id = null
优化后：select * from sys_user where id = 0

  2. 避免使用!=或<>操作符

    优化前:select * from sys_user where dept_id <> 2;
优化后:explain select * from sys_user where dept_id > 2 union all select * from sys_user where dept_id < 2;

  3. 避免使用or连接条件

    优化前: select * from sys_user where id = 3 or id = 4;
优化后：select * from sys_user where id = 3 union all select * from sys_user where id = 2;

  4. 避免使用参数，表达式，函数，操作

    在应用层将参数转换成常量

  5. 避免在where子句中的“=”左边进行函数,算术运算或者其他表达式运算

优化长难语句

优化方案：分解关联查询，执行单个查询，减少锁的竞争，减少冗余记录的查询

优化关联查询

优化方案：确定ON或者USING子句中是否有索引，确保GROUP BY和ORDER BY只有一个表中的列

优化子查询

优化方案：使用关联查询，优化GROUP BY和DISTINCT,这两种可以根据索引来优化，使用索引列分组效率更高，如果不需要ORDER BY进行GROUP BY时加ORDER BY NULL,mysql不会再进行文件排序

优化LIMIT分页

优化方案：记录上次查询的大ID,下次查询时直接根据该ID来查询因为LIMIT偏移量越大，查询效率越低，因为MySQL不是跳过偏移量，而是先把偏移量+要取出的出来，然后抛弃偏移量后再返回

优化前:select * from sys_user order by id desc limit 1,20
优化后:select * from sys_user where id > 1 order by id desc limit 20

优化 UNION查询

优化方案： UNION ALL的效率高于UNION

like语句优化

优化前：select * from sys_user where username like "%ws%"
优化后：select * from sys_user where username like "ws%"

优化后符合最左前缀原则，会走索引，第一种会索引失效

索引优化

分类

  • 功能上分类：普通索引(NORMAL)，唯一索引(UNIQUE)，主键索引（PRIMARY KEY），全文索引（FULLTEXT）
  • 实现方式分类：聚簇索引(主键属于聚簇索引)，非聚簇索引
  • 字段个数分类：单列索引，多列索引（联合索引，覆盖索引）

原则

  1. 左前缀原则，mysql会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配
  2. 频繁作为查询条件的字段适合创建索引
  3. 频繁更新的字段不适合创建索引
  4. 尽量扩展索引，不要新建索引，一个联合索引比多个单个索引效率更高

建议

  1. 尽量使用自增主键
  2. 索引字段越小越好，因为查询索引的时候需要把索引列转换成一个关键字来查询，字段越小，转换的时间越短
  3. 索引不要超过6个
  4. 删除冗余和无效的索引
  5. 尽量使用数字型字段
  6. 非空字段应该指定列为NOTNULL，在mysql中，含有空值的列很难进行查询优化，因为他使得索引的统计信息变得更加复杂，应该用0或者一个特殊的值来代替空值
  7. 将离散大的字段（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查询字段的差异值，返回值越大说明字段的离散程度越高

索引案例

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (`id` int NOT NULL AUTO_INCREMENT,`username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户名',`nickname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '昵称',`gender` tinyint(1) NULL DEFAULT 1 COMMENT '性别((1:男;2:女))',`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',`dept_id` int NULL DEFAULT NULL COMMENT '部门ID',`avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用户头像',`mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系方式',`status` tinyint(1) NULL DEFAULT 1 COMMENT '用户状态((1:正常;0:禁用))',`email` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户邮箱',`deleted` tinyint(1) NULL DEFAULT 0 COMMENT '逻辑删除标识(0:未删除;1:已删除)',`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',PRIMARY KEY (`id`) USING BTREE,INDEX `sys`(`username`, `dept_id`, `nickname`, `deleted`) USING BTREE,INDEX `dept_id`(`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1021664 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;

有效的索引

  1. 符合最左匹配原则，where后面的字段顺序和索引顺序一致

    explain select * from sys_user where username = '有来技术' and dept_id = 2 and nickname = "test用户" and deleted = 1

image-20230223234727187

  1. 覆盖索引，虽然不符合最左匹配原则，但是查询的列都在索引中

    explain select username,nickname,deleted from sys_user where nickname = "test用户" and deleted = 1

    image-202302232348418223.索引下推，虽然username是索引字段，但是后面加上了模糊查询，并且是以%开头，应该是用不了索引的，但是MYSQL在5.6引入了(index Condition Pushdown)简称ICP特性，在存储引擎层优化了这种情况，也能使用索引

   explain select * from sys_user where username = '有来技术' and email like '%youlai'

image-20230223235039353

无效的索引

  1. 不符合最左匹配原则

    explain select * from sys_user where dept_id = 2 and nickname = "test用户" and deleted = 1

    image-20230223225539068

  2. 模糊查询以%开头

     explain select * from sys_user where username like '%有来技术'

    image-20230223232159388

  3. 使用!=导致索引失效，虽然username是索引字段，但是因为使用了！=，需要回表根据值来过滤数据，所以索引失效了

     explain select * from sys_user where username != '有来技术'

    image-20230223231553123

4.使用了计算表达式

explain select * from sys_user where dept_id - 1 = 1

image-20230223234129601

5.使用索引自身类型不同的值

explain select * from sys_user where username = 1

image-20230223234353336

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

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

相关文章

公网远程连接Oracle数据库【内网穿透】

公网远程连接Oracle数据库【内网穿透】

文章目录1. 数据库搭建2. 内网穿透2.1 安装cpolar内网穿透2.2 创建隧道映射3. 公网远程访问4. 配置固定TCP端口地址4.1 保留一个固定的公网TCP端口地址4.2 配置固定公网TCP端口地址4.3 测试使用固定TCP端口地址远程OracleOracle&#xff0c;是甲骨文公司的一款关系数据库管理系…
阅读更多...
【OpenAI】基于 Gym-CarRacing 的自动驾驶练习项目 | 路径训练功能的实现 | GYM-Box2D CarRacing

【OpenAI】基于 Gym-CarRacing 的自动驾驶练习项目 | 路径训练功能的实现 | GYM-Box2D CarRacing

限时开放&#xff0c;猛戳订阅&#xff01; &#x1f449; 《一起玩蛇》&#x1f40d; &#x1f4ad; 写在前面&#xff1a; 本篇是关于多伦多大学自动驾驶专业项目的博客。GYM-Box2D CarRacing 是一种在 OpenAI Gym 平台上开发和比较强化学习算法的模拟环境。它是流行的 Box2…
阅读更多...
数据库浅谈之 Bloom Filter

数据库浅谈之 Bloom Filter

数据库浅谈之 Bloom Filter HELLO&#xff0c;各位博友好&#xff0c;我是阿呆 &#x1f648;&#x1f648;&#x1f648; 这里是数据库浅谈系列&#xff0c;收录在专栏 DATABASE 中 &#x1f61c;&#x1f61c;&#x1f61c; 本系列阿呆将记录一些数据库领域相关的知识 &am…
阅读更多...
亚马逊短期疲软，但长期前景乐观

亚马逊短期疲软，但长期前景乐观

来源&#xff1a;猛兽财经 作者&#xff1a;猛兽财经 由于投资者对亚马逊(AMZN)前景的担忧&#xff0c;导致该公司的股价在过去一年中下跌了39%。然而猛兽财经认为亚马逊近期面临的不利因素只是暂时的&#xff0c;该公司还是有充分的条件可以在医疗保健和物流领域获得重大增长机…
阅读更多...
华为OD机试题，用 Java 解【N 进制减法】问题

华为OD机试题，用 Java 解【N 进制减法】问题

最近更新的博客 华为OD机试 - 猴子爬山 | 机试题算法思路 【2023】华为OD机试 - 分糖果（Java） | 机试题算法思路 【2023】华为OD机试 - 非严格递增连续数字序列 | 机试题算法思路 【2023】华为OD机试 - 消消乐游戏（Java） | 机试题算法思路 【2023】华为OD机试 - 组成最大数…
阅读更多...
华为OD机试题，用 Java 解【快递运输】问题

华为OD机试题，用 Java 解【快递运输】问题

最近更新的博客 华为OD机试 - 猴子爬山 | 机试题算法思路 【2023】华为OD机试 - 分糖果（Java） | 机试题算法思路 【2023】华为OD机试 - 非严格递增连续数字序列 | 机试题算法思路 【2023】华为OD机试 - 消消乐游戏（Java） | 机试题算法思路 【2023】华为OD机试 - 组成最大数…
阅读更多...
我希望早点知道的关于成长的建议

我希望早点知道的关于成长的建议

人上了年纪&#xff0c;往往在诸如更加闭塞&#xff0c;更加固执这些缺点之外&#xff0c;再多出来一个缺点&#xff1a;那就是动不动就爱给别人建议。我当然也未能免俗。有时候会听到同样悲观且固执的过来人告诉我&#xff0c;这些建议说了和没说效果都一样&#xff0c;人们在…
阅读更多...
「媒体邀约」四川有哪些媒体，成都活动媒体邀约

「媒体邀约」四川有哪些媒体，成都活动媒体邀约

传媒如春雨&#xff0c;润物细无声&#xff0c;大家好&#xff0c;我是51媒体网胡老师。 四川省位于中国西南地区&#xff0c;是中国的一个省份。成都市是四川省的省会&#xff0c;成都市是中国西部地区的政治、经济、文化和交通中心&#xff0c;也是著名的旅游胜地。每年的文…
阅读更多...
关于iframe一些通讯的记录(可适用工作流审批，文中有项目实践，欢迎咨询）

关于iframe一些通讯的记录(可适用工作流审批，文中有项目实践，欢迎咨询）

一.知识点(1).我们可以通过postMessage(发送方)和onmessage(接收方)这两个HTML5的方法, 来解决跨页面通信问题&#xff0c;或者通过iframe嵌套的不同页面之间的通信a.父页面代码如下<div v-if"src" class"iframe"><iframeref"iframe"id…
阅读更多...
Linux——进程概念（进程状态）

Linux——进程概念（进程状态）

目录 进程状态 三态模型 五态模型 七态模型 Example eg1:阻塞态&#xff1a;等待某种资源的过程 eg2:挂起态 Linux内核源代码 Linux进程状态查看 Linux运行状态 R运行状态&#xff08;running&#xff09;: S睡眠状态&#xff08;sleeping)&#xff1a; D磁盘休眠状…
阅读更多...
HEVC 编码速率控制

HEVC 编码速率控制

视频传输带宽通常都会受到一定的限制&#xff0c;为了在满足通信带宽和传输时延限制的情况下有效传输视频数据&#xff0c;保证视频业务的播放质量&#xff0c;需要对视频编码过程进行速率控制&#xff0c;所谓速率控制&#xff0c;就是通过选择一系列编码失真尽量小&#xff0…
阅读更多...
一篇了解分布式id生成方案

一篇了解分布式id生成方案

系统唯一ID是我们在设计一个系统的时候常常会遇见的问题&#xff0c;也常常为这个问题而纠结。生成ID的方法有很多&#xff0c;适应不同的场景、需求以及性能要求。所以有些比较复杂的系统会有多个ID生成的策略。下面就介绍一些常见的ID生成策略。 1.数据库自增长序列或字段 …
阅读更多...
DCL单例模式是如何保证数据安全的？

DCL单例模式是如何保证数据安全的？

承接上文证明CPU指令是乱序执行的DCL单例&#xff08;Double Check Lock&#xff09;到底需不需要volatile&#xff1f;new对象这一步&#xff0c;对应着汇编层面的这3个指令&#xff0c;指令0是申请空间&#xff0c;设置默认值&#xff1b;指令7是执行构造方法&#xff0c;设置…
阅读更多...
计算机网络概述 第二部分

计算机网络概述 第二部分

5.网络分层 ①OSI 7层模型 数据链路层 (Data Link Layer) 实现相邻&#xff08;Neighboring&#xff09;网络实体间的数据传输 成帧&#xff08;Framing&#xff09;&#xff1a;从物理层的比特流中提取出完整的帧 错误检测与纠正&#xff1a;为提供可靠数据通信提供可能 …
阅读更多...
stm32f407探索者开发板（二十一）——窗口看门狗

stm32f407探索者开发板（二十一）——窗口看门狗

文章目录一、窗口看门狗概述1.1 看门狗框图1.2 窗口看门狗工作过程总结1.3 超时时间1.4 为什么需要窗口看门狗1.5 其他注意事项二、常用寄存器和库函数2.1 控制寄存器WWDG_ CR2.2 配置寄存器WWDG_ CFR2.3 状态寄存器WWDG_SR三、手写窗口看门狗3.1 配置过程3.2 初始化窗口看门狗…
阅读更多...
【微信小程序】-- 常用视图容器类组件介绍（六）

【微信小程序】-- 常用视图容器类组件介绍（六）

&#x1f48c; 所属专栏&#xff1a;【微信小程序开发教程】 &#x1f600; 作  者&#xff1a;我是夜阑的狗&#x1f436; &#x1f680; 个人简介&#xff1a;一个正在努力学技术的CV工程师&#xff0c;专注基础和实战分享 &#xff0c;欢迎咨询&#xff01; &#…
阅读更多...
LeetCode 725. 分隔链表

LeetCode 725. 分隔链表

LeetCode 725. 分隔链表 难度&#xff1a;middle\color{orange}{middle}middle 题目描述 给你一个头结点为 headheadhead 的单链表和一个整数 kkk &#xff0c;请你设计一个算法将链表分隔为 kkk 个连续的部分。 每部分的长度应该尽可能的相等&#xff1a;任意两部分的长度差…
阅读更多...
绿通科技在创业板开启申购：超额募资约19亿元，收入依赖贴牌

绿通科技在创业板开启申购：超额募资约19亿元，收入依赖贴牌

2月23日&#xff0c;广东绿通新能源电动车科技股份有限公司&#xff08;下称“绿通科技”&#xff0c;SZ:301322&#xff09;开启申购。据贝多财经了解&#xff0c;绿通科技本次上市的发行价为131.11元/股&#xff0c;发行数量为1749万股&#xff0c;市盈率73.75倍。 按发行价…
阅读更多...
逆向 x品会 edata

逆向 x品会 edata

逆向 x品会 edata 版本 7.88.6 帖子底部有参考说明 charles 抓包 目标字段 edata edata 搜索关键字 跟进找到是edata >>> KeyInfo native esNav 方法 private static native String esNav(Context context, String str, String str2, String str3, int i); …
阅读更多...
XX项目自动化测试方案模板，你学会了吗？

XX项目自动化测试方案模板，你学会了吗？

目录 1、引言 2、自动化实施目标 3、自动化技术选型 4、测试环境需求 5、人员进度安排 总结感谢每一个认真阅读我文章的人&#xff01;&#xff01;&#xff01; 重点&#xff1a;配套学习资料和视频教学 1、引言 文档版本 版本 作者 审批 备注 V1.0 Vincent XXX …
阅读更多...
推荐文章
最新文章

Copyright @ 2022~2023

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