MySQL数据库查询性能优化的4个技巧干货

目录
  • 前言
  • SQL的执行频率
  • 慢查询日志
  • show profiles详情分析
  • explain执行计划
    • 1、ID参数
    • 2、select_type参数
    • 3、type参数

前言

MySQL性能优化是一个老生常谈的问题，无论是在实际工作中还是面试中，都不可避免遇到相应的场景，下面博主就总结一些能够帮助大家解决这个问题的小技巧。

SQL优化之前需要确认哪些SQL需要优化，这时就需要引起SQL性能分析工具，主要优化的是查询语句。

SQL的执行频率

SQL性能优化一般是针对查询语句，所以在定位是否需要优化之前，可以先确认表的更删查改的一个执行频率对比，如果是查询占主导地位，则可以一步排查。

MySQL支持客户端通过show [session|global] status命令对服务器状态进行查询。

查看执行频率方式：

show global status like ‘com_______’(7个下划线,表示后面会有7个字符)

慢查询日志

确认了SQL的执行频率，则需要通过慢查询日志进行进一步定位哪些SQL语句执行时间占用较长。

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认是10s)的所有SQL语句的日志。

默认情况下，慢查询日志是没有开启的，需要在MySQL的配置文件(linux下为:/etc/my.cnf)中配置如下指令：

  • 查询服务端是否开启慢查询日志：show variables like 'slow_query_log';
  • 在mysql的配置文件中添加如下配置启动：
  • slow_query_log=1;开启mysql慢日志查询开关
  • long_query_time=xx;设置慢日志时间，只要SQL执行时间查过该值，则视为慢查询，记录在慢日志中。
  • 配置完成后重启mysql服务端
  • linux中mysql的慢日志文件在: /var/lib/mysql/localhost-slow.log

window可以在my.ini文件中配置具体的地址

Query_time SQL执行的时间,越长则越慢

Lock_time 在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间

Rows_sent 查询返回的行数

Rows_examined 查询检查的行数

show profiles详情分析

通过慢查询日志，我们可以定位到超过设置阈值的慢SQL，但是实际业务中，这并不能完全具有代表性，因为阈值是主观设置的，可能有大量执行时间低于阈值的SQL也存在问题，因此慢日志SQL并不能完全定位出所有的慢SQL。

show profiles 能够让我们了解到SQL执行时时间都耗费到哪里了。 通过have_profiling参数，可以查看mysql是否支持该profile操作。

格式: select @@have_profiling;

默认情况下，profiling是关闭的，可以同set指令开启session|global级别的profiling。

格式: set global | session profiling = 1;

优化方案：

  • show profiles; 查看每一条SQL的耗时基本情况
  • show profile for query query_id; 查询指定query_id的SQL语句各个阶段的耗时情况
  • show profile cpu for query query_id; 查询指定query_id的SQL语句cpu使用情况

explain执行计划

前面介绍的几种方式都是通过执行时间长短来判断SQL语句执行的性能好坏，但是这个相对来说是比较片面的，想要更全面地评判SQL语句好坏，则需要使用explain查看SQL的执行计划。

Explain或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句过程中表如何连接和连接的顺序。

语法：explain | desc select xxxx...

1、ID参数

select中的查询序号，表示的是查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上往下，id不同，值越大，越先执行)

2、select_type参数

表示select查询类型，常见的有SIMPLE(简单表，即不使用表连接或者子查询)、primary(主查询，即外层查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)

3、type参数

表示连接/访问类型，性能由好到差的连接类型为：null、system、const、eq_ref、ref、range、index、all

在优化的时候，尽量将type往前优化，最差也要为index

  • null：查询的时候不访问任何表，如:select "1"
  • system：当访问一些系统表的时候会出现
  • const：根据主键或者唯一索引访问时，会出现const
  • eq_ref：待确认
  • ref：使用非唯一性索引进行访问时，可能出现ref
  • range：
  • index：使用到了索引，但是对整个索引都进行了遍历，性能也比较差
  • all：全表扫描，性能最差

4、possible_key参数：显示在执行查询时，表中可能被使用到的索引，一个或者多个、

5、key参数：在执行查询时，实际上会命中的索引

6、key_len参数：表示使用到的索引的字节数，该值为索引字段最大可能长度，在不损失精确性的前提下，长度越短越好。

7、rows参数：MySQL认为必须要执行查询的行数，在idb引擎表中，是一个估计值，可能并不总是准确的

8、ref参数：待确认？

9、filtered参数：表示查询返回的行数占总读取行数的百分比，值越大越好

10、extra参数：额外的一些执行信息如排序

以上就是MySQL数据库查询性能优化的4个技巧干货的详细内容，更多关于MySQL查询性能优化的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL范围查询优化的场景实例详解

    目录 思考题 范围查询-基础 场景一: a = 1 and b = 2 and c = 3 场景二: a > 1 and b = 2 场景三:a > 1 and b = 2 and c > 3 场景四: a > 1 ORDER BY b 场景五: a IN (1,2,3) and b > 1 总结 思考题 假设有一张订单表 order,主要包含了主键订单编码 order_no.订单状态 status.提交时间 create_time 等列,并且创建了 status 列索引和

  • MySQL百万级数据大分页查询优化的实现

    MySQL百万级数据大分页查询优化的实现

    目录 一.MySQL分页起点越大查询速度越慢 二. limit大分页问题的性能优化方法 (1)利用表的覆盖索引来加速分页查询 (2)用上次分页的最大id优化 三.MySQL百万数据快速生成 3.1.创建内存表及普通表 3.2.创建函数 3.3.创建插入内存表数据的存储过程 3.4.创建内存表数据插入普通表的存储过程 3.5.运行存储过程插入数据 参考链接: 前言:在数据库开发过程中我们经常会使用分页,核心技术是使用用limit start, count分页语句进行数据的读取. 一.MySQL分页

  • MySQL慢查询优化解决问题

    目录 1. MySQL慢查询介绍 2.发现问题(主动/被动) 3.找到原因-对症下药 1.  MySQL慢查询介绍 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中.long_query_time的默认值为10,意思是运行10S以上的语句.默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参

  • MySQL定位并优化慢查询sql的详细实例

    MySQL定位并优化慢查询sql的详细实例

    目录 1.如何定位并优化慢查询sql a.根据慢日志定位慢查询sql b.使用explain等工具分析sql c.修改sql或者尽量让sql走索引 2.联合索引的最左匹配原则的成因 简单说下什么是最左匹配原则 最左匹配原则的原理 3.索引是建立得越多越好吗 总结 1.如何定位并优化慢查询sql a.根据慢日志定位慢查询sql SHOW VARIABLES LIKE '%query%' 查询慢日志相关信息 slow_query_log 默认是off关闭的,使用时,需要改为on 打开 slow_qu

  • MySql如何将查询的出来的字段进行转换

    MySql如何将查询的出来的字段进行转换

    目录 MySql将查询的出来的字段进行转换 MySql将字符串转成数字 1.直接用加法 2.使用函数 MySql将查询的出来的字段进行转换 使用mysql 查询出来的字段并不是前端想要的,可能需要一些转换,那么怎么办呢 我们可以使用case a when 条件 then ‘输出’end来解决 1表示渡口工作人员,2表示微信用户,那么我们查询之后想把1和2转化成相应的文字说明 select *, (case when ts.reciever like '%,%' then '全部人员' when

  • Mysql大数据量查询优化思路详析

    Mysql大数据量查询优化思路详析

    目录 1. 千万级别日志查询的优化 2. 几百万黑名单库的查询优化 3. Mybatis批量插入处理问题 项目场景: Mysql大表查询优化,理论上千万级别以下的数据量Mysql单表查询性能处理都是可以的. 问题描述: 在我们线上环境中,出现了mysql几千万级别的日志查询.几百万级别的黑名单库查询分页查询及条件查询都慢的问题,针对Mysql表优化做了一些优化处理. 原因分析:首先说一下日志查询,在Mysql中如果索引加的比较合适,走索引情况下千万级别查询不会超过一秒,Mysql查询的速度和检索

  • MySQL数据库查询性能优化的4个技巧干货

    MySQL数据库查询性能优化的4个技巧干货

    目录 前言 SQL的执行频率 慢查询日志 show profiles详情分析 explain执行计划 1.ID参数 2.select_type参数 3.type参数 前言 MySQL性能优化是一个老生常谈的问题,无论是在实际工作中还是面试中,都不可避免遇到相应的场景,下面博主就总结一些能够帮助大家解决这个问题的小技巧. SQL优化之前需要确认哪些SQL需要优化,这时就需要引起SQL性能分析工具,主要优化的是查询语句. SQL的执行频率 SQL性能优化一般是针对查询语句,所以在定位是否需要优化之前

  • MySQL数据库查询性能优化策略

    优化查询 使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句. 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句. 使用索引查询 MySql中提高性能的一个最有效的方式就是对数据表设计合理的索引. 索引提供了高效访问数据的方法,并且加快查询速度. 如果查询时没有使用索引,那么查询语句将扫描表中所有的记录.在数据量大的时候,这样查询速度会很慢. 使用索引进行查询,查

  • MySQL数据库的性能优化

    目录 一.MySQL数据库的优化目标.基本原则: 1.优化目标: 2.基本原则: 二.定位分析SQL语句的性能瓶颈: 1.通过show status 命令了解各种SQL的执行效率: 2.定位执行效率较低的SQL语句 3.通过explain分析慢SQL的执行计划 4.通过show profile 分析SQL的具体耗时瓶颈 三.数据库的优化方法: 一.MySQL数据库的优化目标.基本原则: 1.优化目标: MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,无论是索引优化.还是表结构优化,参数优

  • 5招带你轻松优化MySQL count(*)查询性能

    目录 前言 1 count(*)为什么性能差 2 如何优化count(*)性能 2.1 增加redis缓存 2.2 加二级缓存 2.3 多线程执行 2.4 减少join的表 2.5 改成ClickHouse 3 count的各种用法性能对比 前言 最近我在公司优化过几个慢查询接口的性能,总结了一些心得体会拿出来跟大家一起分享一下,希望对你会有所帮助. 我们使用的数据库是Mysql8,使用的存储引擎是Innodb.这次优化除了优化索引之外,更多的是在优化count(*). 通常情况下,分页接口一般

  • MySQL查询性能优化七种方式索引潜水

    MySQL查询性能优化七种方式索引潜水

    目录 前言: 有读者可能会一脸懵? 啥是索引潜水? 你给起的名字的吗?有没有索引蛙泳? 这个名字还真不是我起的,今天要讲的知识点就叫索引潜水(Index dive) . 先要从一件怪事说起: 我先造点数据复现一下问题,创建一张用户表: CREATE TABLE `user` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `name` varchar(100) NOT NULL DEFAULT '' COM

  • MySQL查询性能优化武器之链路追踪

    MySQL查询性能优化武器之链路追踪

    目录 前言 1. 查看optimizer trace配置 2. 开启optimizer trace 3. 线上问题复现 3. 使用optimizer trace 前言 MySQL优化器可以生成Explain执行计划,我们可以通过执行计划查看是否使用了索引,使用了哪种索引? 但是到底为什么会使用这个索引,我们却无从得知. 好在MySQL提供了一个好用的分析工具 — optimizer trace(优化器追踪),可以帮助我们查看优化器生成执行计划的整个过程,以及做出的各种决策,包括访问表的方法.各种

  • MySQL查询性能优化索引下推

    MySQL查询性能优化索引下推

    目录 前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围 前言 前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪 今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用 主要作用有两个: 减少回表查询的次数 减少存

  • Mysql数据库之索引优化

    Mysql数据库之索引优化

    MySQL凭借着出色的性能.低廉的成本.丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库.虽然性能出色,但所谓"好马配好鞍",如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如"精通MySQL"."SQL语句优化"."了解数据库原理"等要求.我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,

  • MongoDB数据库查询性能提高40倍的经历分享

    MongoDB数据库查询性能提高40倍的经历分享

    前言 数据库性能对软件整体性能有着至关重要的影响,本文给大家分享了一次MongoDB数据库查询性能提高40倍的经历,感兴趣的朋友们可以参考学习. 背景说明 1.数据库:MongoDB 2.数据集: A:字段数不定,这里主要用到的两个UID和Date B:三个字段,UID.Date.Actions.其中Actions字段是包含260元素JSON数组,每个JSON对象有6个字段.共有数据800万条左右. 3.业务场景:求平均数 通过组合条件从A数据表查询出(UID,Date)列表,最多可能包含数万条

  • Oracle分页查询性能优化代码详解

    对于数据库中表的数据的 Web 显示,如果没有展示顺序的需要,而且因为满足条件的记录如此之多,就不得不对数据进行分页处理.常常用户并不是对所有数据都感兴趣的,或者大部分情况下,他们只看前几页. 通常有以下两种分页技术可供选择. Select * from ( Select rownum rn,t.* from table t) Where rn>&minnum and rn<=&maxnum 或者 Select * from ( Select rownum rn,t.* fro

随机推荐