分分钟解决 MySQL 查询速度慢与性能差

发布时间:2019年11月05日 阅读:141 次

 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第1张

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

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

分分钟解决 MySQL 查询速度慢与性能差 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第2张

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 查询速度慢与性能差 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第3张

1.5.2事务的 ACID属性

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

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

3、隔离性(isolation):

隔离性等级:

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

开启一个新事务: begin;

提交一个事务: commit;

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

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

分分钟解决 MySQL 查询速度慢与性能差 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第4张

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

1.5.3 大事务

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

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

解决思路:

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

2.1 影响性能的几个方面

2.2 MySQL体系结构

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

分分钟解决 MySQL 查询速度慢与性能差 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第5张

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 查询速度慢与性能差 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第6张

2.5.2 锁类型

分分钟解决 MySQL 查询速度慢与性能差 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第7张

2.5.3 锁的粒度

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

分分钟解决 MySQL 查询速度慢与性能差 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第8张

将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更大。

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

2.8 性能优化顺序

从上到下:

分分钟解决 MySQL 查询速度慢与性能差 分分钟解决 MySQL 查询速度慢与性能差 系统运维 第9张


本文标题:分分钟解决 MySQL 查询速度慢与性能差
本文链接:http://malonghua.com/post/124.html
作者授权:除特别说明外,本文由 SylarM 原创编译并授权 猪逻辑公园工作室 刊载发布。
版权声明:本文不使用任何协议授权,您可以任何形式自由转载或使用。
Tag:MySQL 查询速度慢 性能差
相关文章

发表评论: