文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL 中查询速度慢如何解决

2024-04-02 19:55

关注

这篇文章给大家介绍MySQL 中查询速度慢如何解决,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。

一、什么影响了数据库查询速度

1.1 影响数据库查询速度的四个因素

MySQL 中查询速度慢如何解决

1.2 风险分析

QPS:  QueriesPerSecond意思是“每秒查询率”,是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

TPS: 是  TransactionsPerSecond的缩写,也就是事务数/秒。它是软件测试结果的测量单位。客户机在发送请求时开始计时,收到服务器响应后结束计时,以此来计算使用的时间和完成的事务个数。

Tips: 最好不要在主库上数据库备份,大型活动前取消这样的计划。

1.3 网卡流量:如何避免无法连接数据库的情况

1.4 大表带来的问题( 重要)

1.4.1 大表的特点

1.4.2 大表的危害

1.慢查询:很难在短时间内过滤出需要的数据 查询字区分度低 -> 要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘 io ->  降低磁盘效率

2.对 DDL影响:

建立索引需要很长时间:

修改表结构需要长时间的锁表:会造成长时间的主从延迟('480秒延迟')

1.4.3 如何处理数据库上的大表

分库分表把一张大表分成多个小表

难点:

1.5 大事务带来的问题( 重要*)*

1.5.1 什么是事务

MySQL 中查询速度慢如何解决

1.5.2事务的 ACID属性

1、原子性( atomicity):全部成功,全部回滚失败。银行存取款。

2、一致性(consistent):银行转账的总金额不变。

3、隔离性(isolation):

隔离性等级:

查看系统的事务隔离级别: show variables like'%iso%';

开启一个新事务: begin;

提交一个事务: commit;

修改事物的隔离级别: setsession tx_isolation='read-committed';

4、持久性( DURABILITY):从数据库的角度的持久性,磁盘损坏就不行了

MySQL 中查询速度慢如何解决

redolog机制保证事务更新的一致性和持久性

1.5.3 大事务

运行时间长,操作数据比较多的事务;

风险:锁定数据太多,回滚时间长,执行时间长。

解决思路:

二、什么影响了MySQL性能( 非常重要)

2.1 影响性能的几个方面

2.2 MySQL体系结构

分三层:客户端->服务层->存储引擎

MySQL 中查询速度慢如何解决

2.3 InnoDB存储引擎

MySQL5.5及之后版本默认的存储引擎: InnoDB。

2.3.1 InnoDB使用表空间进行数据存储。

show variables like'innodb_file_per_table

如果innodbfileper_table 为 ON 将建立独立的表空间,文件为tablename.ibd;

如果innodbfileper_table 为 OFF 将数据存储到系统的共享表空间,文件为ibdataX(X为从1开始的整数);

.frm :是服务器层面产生的文件,类似服务器层的数据字典,记录表结构。

2.3.2 (MySQL5.5默认)系统表空间与( MySQL5.6及以后默认)独立表空间

强烈建立对Innodb 使用独立表空间,优化什么的更方便,可控。

2.3.3 系统表空间的表转移到独立表空间中的方法

或者 Altertable 同样可以的转移,但是无法回收系统表空间中占用的空间。

2.4 InnoDB存储引擎的特性

2.4.1 特性一:事务性存储引擎及两个特殊日志类型:Redo Log 和 Undo Log

Redo Log: 实现事务的持久性(已提交的事务)。 Undo Log: 未提交的事务,独立于表空间,需要随机访问,可以存储在高性能io设备上。

Undo日志记录某数据被修改前的值,可以用来在事务失败时进行 rollback; Redo日志记录某数据块被修改后的值,可以用来恢复未写入 data  file的已成功事务更新的数据。

2.4.2 特性二:支持行级锁

2.5 什么是锁

2.5.1 锁

MySQL 中查询速度慢如何解决

2.5.2 锁类型

MySQL 中查询速度慢如何解决

2.5.3 锁的粒度

MySQL的事务支持不是绑定在MySQL服务器本身, 而是与存储引擎相关

MySQL 中查询速度慢如何解决

将table_name加表级锁命令: locktable table_name write; 写锁会阻塞其它用户对该表的‘读写’操作,直到写锁被释放:  unlock tables;

锁的开销越大,粒度越小,并发度越高。

表级锁通常是在服务器层实现的。

行级锁是存储引擎层实现的。innodb的锁机制,服务器层是不知道的

2.5.4 阻塞和死锁

(1)阻塞是由于资源不足引起的排队等待现象。  (2)死锁是由于两个对象在拥有一份资源的情况下申请另一份资源,而另一份资源恰好又是这两对象正持有的,导致两对象无法完成操作,且所持资源无法释放。

2.6 如何选择正确的存储引擎

参考条件:

总结: Innodb 大法好。

注意: 尽量别使用混合存储引擎,比如回滚会出问题在线热备问题。

2.7 配置参数

2.7.1 内存配置相关参数

确定可以使用的内存上限。

内存的使用上限不能超过物理内存,否则容易造成内存溢出;(对于32位操作系统,MySQL只能试用3G以下的内存。)

确定MySQL的 每个连接 单独 使用的内存。

注意: 以上四个参数是为一个线程分配的,如果有100个连接,那么需要×100。

MySQL数据库实例:

①MySQL是 单进程多线程(而oracle是多进程),也就是说 MySQL实例在系统上表现就是一个服务进程,即进程;

②MySQL实例是线程和内存组成,实例才是真正用于操作数据库文件的;

一般情况下一个实例操作一个或多个数据库;集群情况下多个实例操作一个或多个数据库。

如何为缓存池分配内存:

Innodb_buffer_pool_size,定义了Innodb所使用缓存池的大小,对其性能十分重要,必须足够大,但是过大时,使得Innodb  关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中;

总内存-(每个线程所需要的内存*连接数)-系统保留内存

key_buffer_size,定义了MyISAM所使用的缓存池的大小,由于数据是依赖存储操作系统缓存的,所以要为操作系统预留更大的内存空间;

select sum(index_length) from information_schema.talbes where engine='myisam'

注意: 即使开发使用的表全部是Innodb表,也要为MyISAM预留内存,因为MySQL系统使用的表仍然是MyISAM表。

max_connections 控制允许的最大连接数, 一般2000更大。

不要使用外键约束保证数据的完整性。

关于MySQL 中查询速度慢如何解决就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯