首页 > 数据库 >MYSQL:由一条慢查询引入思考 (MYSQL8)

MYSQL:由一条慢查询引入思考 (MYSQL8)

时间:2023-10-16 17:00:40浏览次数:53  
标签:SQL MYSQL8 mysql 查询 索引 MYSQL query 数据

原文地址:https://mp.csdn.net/mp_blog/creation/editor/130300178

 开始之前,我们先思考以下几个问题(下文将围绕以下三个问题展开):

1. 什么是慢查询,查询多少秒以上算是慢查询?

2. 如何解决慢查询和如何避免慢查询?

3. 提升查询性能必知必会

 

目录

一、慢查询

1.1 什么是慢查询?

1.2 如何查看系统中存在哪些慢查询?

1.3 引起慢查询的原因和影响

二、如何解决和避免慢查询

三、提升SQL性能必知必会

3.1 添加索引提升速度

3.2 索引失效大致如下场景

 


 

一、慢查询

1.1 什么是慢查询?

mysql 慢查询指的是执行时间过长的 sql 查询语句,影响数据库的性能和用户体验。

1.2 如何查看系统中存在哪些慢查询?

首先开启慢查询监控。

mysql有一个配置是long_query_time,值是数字,单位是秒。当一条SQL语句执行耗时超过long_query_time的值时,mysql就认为这条sql为慢查询SQL。

相当命令如下

// 查看慢查询是否开启
show variables like 'slow_query_log';
// 开启慢查询(值可以是1或on)
set global slow_query_log = 1;
// 关闭慢查询(值可以是1或off)
set global slow_query_log = 0;

// 查看long_query_time值
show variable like 'long_query_time';
// 设置long_query_time值 (单位是秒)
set global long_query_time=5;

  注:若用以上命令来配置,则在重启后会失效,若想永久生效可以配置在my.conf中。

  开启了配置之后,通过以下命令获取慢查询SQL的日志路径

show variables like 'slow_query_log_file%'

1.3 引起慢查询的原因和影响

在B端的应用系统中容易出现慢查询。B端系统上线一段时间之后,随着数据量越来越大,系统的性能问题也逐步浮水面,最常见的就是因慢查询引起的功能反应速度变慢而使用户体验越来越差。

慢查询可能会引起CPU损耗过高和系统IO压力增加,更严重的情况是整个系统崩溃。

引起慢查询的原因大致归纳如下:

  1. 没有索引或者SQL没有命中索引导致索引失效(思考:为什么会索引失效?
  2. 单表数据量过多,导致查询遇到瓶颈。比如2000万条数据(思考:为什么有些表两千万条数据很快,而有些表两千万条数据就很慢?
  3. 网络速度慢
  4. I/O吞吐量小,形成了瓶颈效应
  5. 热点数据导致单点负载不均衡
  6. 内存不足
  7. 查询结果集的数据量过大
  8. 行锁或表锁
  9. 返回不必要的列

二、如何解决和避免慢查询

  • 提高网速、更换更高容量的硬盘、增加内存或者 cpu 的数量等等
  • 调整配置参数:mysql 有许多参数可以配置,可以根据实际情况调整这些参数,如增加缓存大小、线程池大小等等。

  • 添加索引:索引可以提高查询效率,特别是对于大型表。通过分析慢查询日志或者使用 explain 命令找到需要优化的查询语句,然后为其中涉及的列添加索引(注意不要添加过多的索引)

  • 优化查询语句:合理优化查询语句可以减少查询时间。例如,可以尝试减少子查询的数量,避免使用SELECT *,多表JOIN,避免使用 like '%xxx%' 的模糊查询等。

  • 批量处理数据:有时候大量数据的操作往往比单个数据的操作更有效率。因此,尽可能以批量方式操作数据,如使用 insert .. values() 和 update .. set .. where in() 等。

  • 分库分表:若数据量较大,可能会对单个数据库的性能造成压力。此时可以考虑将数据分散存储到多个数据库中,或者将单张表的数据拆分为多张表来存储。注意,这种方法需要谨慎设计,在实际应用中可能会引入更多的问题。

  • 表中的大字段剥离

  • 字段冗余

  • 减少sql中函数运算与其他计算

  • 修改SQL语句:优化查询语句,避免使用SELECT *、子查询、多表JOIN等不必要的操作。
  • 数据库优化:调整数据库参数、内存占用、磁盘IO等,提高系统性能,增加查询效率。
  • 针对查询频繁的热点数据增加缓存,引入非关系型数据库
  • 主从复制,读写分离,一般情况下,查询的情况比写的情况多,所以考虑将数据库分为主库,从库,主库处理写的操作,从库处理读的操作

       

三、提升SQL性能必知必会

常常听到同事们讨论有关慢查询的问题 “哎这个SQL怎么这么慢,看一下加索引了没有?”

思考:添加索引后为什么查询速度会增加?为什么即使添加索引也不能提升查询速度?

3.1 添加索引提升查询速度

  • 数据内存中比较相比mysql的查询产生io的耗时可忽略不计,所以查询速度取决于查询过程中的IO次数耗时,即提高查询次数的有效方法是减少IO次数(mysql的数据是存储在磁盘中)
  • mysql innoDB引擎索引数据结构是B+tree结构(树节点称为数据叶)
  • 每个数据叶默认大小为16kb(16384)(show VARIABLES like 'innodb_page_size';)
  • 假设我们用bigint做为主键索引大概占8个字节,(B+tree特点)有指向下一个的指针大概占6个字符,则每个数据叶可以存放的索引信息有 16384 / (8 + 6)= 1170个索引信息。
    • 对于主键过引,假设一行数据1kb,则叶子可存16条数据。当B+Tree的高度为h = 2 则数据量为 1170 * 16 = 18720条数据,当B+Tree的高度为 h = 3 则数据量为1170 * 11170 * 16 = 21902400条数据(2190.24万)
    • 对于非主键索引,则叶子节点的索引信息有 16384 /(8+8)= 1024个索引信息。若h=2 则数据量为 1170 * 1024 = 1198080,若h=3 则数据量为 1170*1170*1024 = 1401753600条数据(14亿零175.36万)
  • 结论:
    1. 非主键索引,索引覆盖,14亿条数据情况下只需要3次io即可查询到想要的数据
    2. 主键索引查询,2190.24万条数据情况下只走需要3次io即可查询到想要的数据

思考:非主键索引查询速度快还是主键索引查询速度快?为什么InnoDB的索引用B+Tree结构而不用其他数据结构?

3.2 索引失效场景

  • 前缀模糊查询,如 like 以%开头(为什么失效?)。%是配置所有,配置所有即全表配置,所以索引失效。
  • 使用or操作符。mysql一个表的查询语句中只使用一个索引,涉及两个字段以上,MySQL无法使用索引,会转向全表扫描。
  • 联合索引未使用第一个字段。联合索引生效原则是最左匹配原则。
  • 索引列上使用函数。因为索引保存的是索引字段的原始值,而不是经过函数计算后的值,自然就没办法走索引了。
  • 隐式转换会使用索引失效。
  • 索引列重复数据非常多。
  • 不等于导致索引失效。
  • 关联查询中关联字段的字符集不同。不同的字符集进行比较前需要进行转换会造成索引失效
  • is null使用索引,is not null不使用索引。

 

注:后续一起来研究InnoDb B+Tree

标签:SQL,MYSQL8,mysql,查询,索引,MYSQL,query,数据
From: https://www.cnblogs.com/imwucx/p/17767760.html

相关文章

  • mysql常用查询
    1、Mysql查询某个表的字段名selectCOLUMN_NAMEfrominformation_schema.COLUMNSwheretable_name='business_info'; 2、生成数据字典selectTABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENTfrominformation_schema.columnswhereTABLE_SCHEMA......
  • Linux部署MySQL过程记录
    一.Linux版本cat/proc/versioncat/etc/redhat-release 二.MySQL安装2.1检查是否已安装MySQLrpm-qa|grep-imysql 2.2解压安装文件到指定文件夹tar-xvf对应mysql的安装包名字.tar-c解压到指定目录下tar-xvfmysql-5.7.43-1.el7.x86_64.rpm-bundle.tar-C......
  • SQL语句的基本使用5-多值子查询
    这个作业属于哪个课程https://edu.cnblogs.com/campus/uzz/cs3这个作业要求在哪里https://edu.cnblogs.com/campus/uzz/cs3/homework/13074这个作业的目标SQL语句的基本使用5-多值子查询1.在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日......
  • MySQL锁表问题
    今天遇到一个情况,一条SQL语句执行提示如下异常。Lockwaittimeoutexceeded;tryrestartingtransaction翻译一下是说,获取锁超时,尝试重新启动事务。翻译一下就是当我们申请对表进行加锁时,另外一个事务已经对这个表加了锁,经过一段时间等待后,我们的事务依然没有获取到锁,所以提......
  • MySQL的InnoDB索引失效的场景和优化
    康师傅YYDS索引失效案例索引最好是全值匹配。where条件中等值比,同时where的条件一起创建联合索引。最佳左前缀如果有一个联合索引,要想使用到,需要从联合索引的最左边的字段开始写,一点一点的写上。主键插入顺序在InnoDB中,使用自增的主键。避免索引页面分裂计算、函数、类型......
  • ubuntu 22.04安装mysql5.7
    ubuntu22.04系统安装mysql5.7一、查看系统默认安装的数据库版本apt-getupdateapt-cachepolicymysql-serverubuntu20.04自带的版本是8.0.34,而我们需要安装mysql5.7的版本就需要换源二、更换apt源1.备份原来的source文件cp/etc/apt/sources.list/etc/apt/sources......
  • 大集合里查询数据-布隆过滤器
    1.问题场景有100亿个url被加入了黑名单,现在提供一个url要去判断是否属于黑名单。也就是一个很简单的一个东西是否属于一个集合的问题。一般来说用set就能解决这种问题,但是由于url数目太多,内存中无法开辟一个这么大的空间去存放所有url,这个时候就需要我们去使用一种结构,去减少状......
  • gorm 使用where in 条件查询时,使用uint8[] 类型报错的解决方案
    出现问题:在开发过程中,遇到这样一个问题,GORMModel如下:typeTeststruct{ ... cloumnTypeuint8`gorm:"notnull;default:0"`...}其中有一个类型字段,数据范围是1-10所以使用uint8字段来存储,在查询某些类型的数据时,使用了下面的查询语句varlist[]model.......
  • update left join 在MySQL和SQL Server使用方式区别
    (1)MySQL使用UPDATEhayl_service_infot1leftjoinhayl_Old_infot2ont1.CERT_NO=t2.CERT_NOsett1.AAP0112=t2.ADDRESSwheret1.AAP0112=''(2)SQLServers使用UPDATEhayl_service_infosetAAP0112=t2.ADDRESSfromhayl_service_infot1leftjoin......
  • MySQL分区表详解
    本文已收录至GitHub,推荐阅读......