MySQL 查询速度慢与性能差的原因与解决方法

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

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

1.2 风险分析

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

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

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

效率低下的 sql:超高的 QPS与 TPS。大量的并发:数据连接数被占满( max_connection默认 100,一般把连接数设置得大一些)。 并发量:同一时刻数据库服务器处理的请求数量超高的 CPU使用率: CPU资源耗尽出现宕机。磁盘 IO:磁盘 IO性能突然下降、大量消耗磁盘性能的计划任务。解决:更快磁盘设备、调整计划任务、做好磁盘维护。

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

减少从服务器的数量(从服务器会从主服务器复制日志)进行分级缓存(避免前端大量缓存失效)避免使用 select* 进行查询分离业务网络和服务器网络

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

1.4.1 大表的特点

记录行数巨大,单表超千万表数据文件巨大,超过 10个 G

1.4.2 大表的危害

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

2.对 DDL影响:

建立索引需要很长时间:

MySQL-v<5.5 建立索引会锁表MySQL-v>=5.5 建立索引会造成主从延迟( mysql建立索引,先在组上执行,再在库上执行)

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

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

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

难点:

分表主键的选择分表后跨分区数据的查询和统计

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

1.5.1 什么是事务

1.5.2事务的 ACID属性

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

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

3、隔离性(isolation):

隔离性等级:

未提交读( READ UNCOMMITED) 脏读,两个事务之间互相可见;已提交读( READ COMMITED)符合隔离性的基本概念,一个事务进行时,其它已提交的事物对于该事务是可见的,即可以获取其它事务提交的数据。可重复读( REPEATABLE READ) InnoDB的默认隔离等级。事务进行时,其它所有事务对其不可见,即多次执行读,得到的结果是一样的!可串行化( SERIALIZABLE) 在读取的每一行数据上都加锁,会造成大量的锁超时和锁征用,严格数据一致性且没有并发是可使用。

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

开启一个新事务: begin;

提交一个事务: commit;

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

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

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

1.5.3 大事务

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

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

锁定太多数据,造成大量阻塞和锁超时;回滚时所需时间比较长,且数据仍然会处于锁定;如果执行时间长,将造成主从延迟,因为只有当主服务器全部执行完写入日志时,从服务器才会开始进行同步,造成延迟。

解决思路:

避免一次处理太多数据,可以分批次处理;移出不必要的 SELECT操作,保证事务中只有必要的写操作。二、什么影响了MySQL性能( 非常重要)

2.1 影响性能的几个方面

服务器硬件。服务器系统(系统参数优化)。存储引擎。 MyISAM: 不支持事务,表级锁。 InnoDB: 支持事务,支持行级锁,事务 ACID。数据库参数配置。数据库结构设计和SQL语句。(重点优化)

2.2 MySQL体系结构

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

MySQL是 插件式的存储引擎,其中存储引擎分很多种。只要实现符合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及以后默认)独立表空间

1.1 系统表空间无法简单的收缩文件大小,造成空间浪费,并会产生大量的磁盘碎片。1.2 独立表空间可以通过 optimeze table 收缩系统文件,不需要重启服务器也不会影响对表的正常访问。2.1 如果对多个表进行刷新时,实际上是顺序进行的,会产生IO瓶颈。2.2 独立表空间可以同时向多个文件刷新数据。

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

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

1、使用mysqldump 导出所有数据库数据(存储过程、触发器、计划任务一起都要导出 )可以在从服务器上操作。2、停止MYsql 服务器,修改参数(my.cnf加入innodbfileper_table),并删除Inoodb相关文件(可以重建Data目录)。3、重启MYSQL,并重建Innodb系统表空间。4、 重新导入数据。

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

2.4 InnoDB存储引擎的特性

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

Innodb 是一种事务性存储引擎。完全支持事务的 ACID特性。支持事务所需要的两个特殊日志类型: RedoLog 和 UndoLog

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

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

2.4.2 特性二:支持行级锁

InnoDB支持行级锁。行级锁可以最大程度地支持并发。行级锁是由存储引擎层实现的。

2.5 什么是锁

2.5.1 锁

2.5.2 锁类型

2.5.3 锁的粒度

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

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

锁的开销越大,粒度越小,并发度越高。表级锁通常是在服务器层实现的。行级锁是存储引擎层实现的。innodb的锁机制,服务器层是不知道的

2.5.4 阻塞和死锁

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

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

参考条件:

事务备份( Innobd免费在线备份)崩溃恢复存储引擎的特有特性

总结: Innodb 大法好。

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

2.7 配置参数

2.7.1 内存配置相关参数

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

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

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

sort_buffer_size #定义了每个线程排序缓存区的大小,MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存(直接分配该参数的全部内存)join_buffer_size #定义了每个线程所使用的连接缓冲区的大小,如果一个查询关联了多张表,MySQL会为每张表分配一个连接缓冲,导致一个查询产生了多个连接缓冲read_buffer_size #定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小,MySQL有查询需要时会为其分配内存,其必须是4k的倍数;read_rnd_buffer_size #索引缓冲区大小,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 性能优化顺序

从上到下:

(0)

相关推荐

  • MySQL 查询速度慢与性能差的原因与解决方法

    一.什么影响了数据库查询速度 1.1 影响数据库查询速度的四个因素 1.2 风险分析 QPS: QueriesPerSecond意思是"每秒查询率",是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准. TPS: 是 TransactionsPerSecond的缩写,也就是事务数/秒.它是软件测试结果的测量单位.客户机在发送请求时开始计时,收到服务器响应后结束计时,以此来计算使用的时间和完成的事务个数. Tips: 最好不要在主库上数据库备

  • Mysql 报Row size too large 65535 的原因及解决方法

    报错信息:Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535 向mysql的表插件一个字段 类型为text时,或修改一个字段类型为text时,报出上面的错误.其实我对这个错误的原因理解也不是很深,给出一些我查到的解释吧 大意是数据表中有一个设定长度为64K的字段索引,当表中字段(不知道是字段名字还是什么)不能超过这个长度,65,535所说明的是针对的是整个表的

  • MySQL命令行界面中出现字符错误提示的原因及解决方法

    ERROR 2019 (HY000): Can't initialize character set gb2312 搞了好半天,MySQL都重装了两次,号悲剧... 之前设置了系统编码全都是UTF-8了的 vi /etc/sysconfig/i18n LANG=zh_CN.UTF-8 LANGUAGE=zh_CN.UTF-8:zh_CN.GB2312:zh_CN SUPPORTED=zh_CN.UTF-8:zh_CN:zh:en_US.UTF-8:en_US:en SYSFONT=lat0-su

  • MySQL 查询速度慢的原因

    谈到MySQL性能优化,查询优化作为优化的源头,它也是最能体现一个系统是否更快.本章以及接下来的几章将会着重讲解关于查询性能优化的内容,从中会介绍一些查询优化的技巧,帮助大家更深刻地理解MySQL如何真正地执行查询.究竟慢在哪里.如何让其快起来,并明白高效和低效的原因何在,这样更有助于你更好的来优化查询SQL语句. 本章从"为什么查询速度这么慢"开始谈起,让你能够清楚的知道查询可能会慢在哪些环节,这样将有助于你更好的优化查询,做到心中有数,高人一筹. 一.慢在哪? **真正衡量查询速度

  • 为什么MySQL查询速度会慢

    目录 一.先了解一下MySQL查询的执行过程 二.是否查询了不需要的数据 三.衡量查询开销的几个重要指标 1.响应时间 2.扫描的行数和返回的行数 3.扫描的行数和访问类型 4.一般可以通过explain的Extra列查看查询的优劣 一.先了解一下MySQL查询的执行过程 MySQL在查询时,它是由很多子任务组成的,每个子任务都会消耗一定的时间,如果要想优化查询,实际上要优化其子任务,可以消除一些子任务.减少子任务的执行次数.让子任务执行的更快. MySQL查询的执行过程:从客户端到服务器.然后

  • MySQL无法创建外键的原因及解决方法

    关联2张表时出现了无法创建外键的情况,从这个博客看到,问题出在第六点的Charset和Collate选项在表级和字段级上的一致性上.我的2张表的编码charset和collate不一致,2张表都执行执行SQL语句: alter table 表名 convert to character set utf8; 完美解决问题: ps:下面看下MySQL无法创建外键.查询外键的属性 MyISAM 和InnoDB 讲解 InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各

  • mysql登录报错提示:ERROR 1045 (28000)的解决方法

    本文分析了mysql登录报错提示:ERROR 1045 (28000)的解决方法.分享给大家供大家参考,具体如下: 一.问题: 公司linux系统的mysql数据库root用户设置过密码,但常常用命令'mysql -u root -p'登录报错,有时又能登录.登录报错信息为: [root@localhost ~]# mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localho

  • Mysql挂掉后无法重启报pid文件丢失的解决方法

    阿里云单核2G的配置挂着两个企业网站,访问量一般.最近每天几乎都会出现网站打不开显示数据库链接失败的问题. 多方寻求原因发现,mysql的pid文件缺失,并无法重启自建,后来也看了其他帖子说关闭日志什么的未果,查看系统日志发现,是因为内存满了导致mysql进程被杀,然后就一直挂起状态. Sep 25 11:33:48 iZ28jcqqr7lZ kernel: Out of memory: Kill process 23201 (mysqld) score 53 or sacrifice chil

  • MySQL数据中很多换行符和回车符的解决方法

    发现问题 今天一大早客户给我打电话:"小陈儿,昨晚我往数据库导了几十万条数据,然后在web界面很多都搜不到,你们系统做的啥玩意儿啊?这么多BUG!得啵得啵得-"(省略2000字)又习惯性的喷了一遍我们这个项目做的辣鸡~~ 得得得,客户随便喷,我就当吃了个早饭了

  • MYSQL时区导致时间差了14或13小时的解决方法

    目录 CST 时区 排错过程 解决方案 MySQL时区有问题(相差13或14小时) p>我一般使用MYSQL定义字段类型时,一般使用TIMESTAMP时间戳来定义创建时间与更新时间,并将其定义为默认值为CURRENT_TIME,但是由于场景特殊,现在我需要将一个任务的开始时间与结束时间记录,并写入数据库,那么我的开始时间戳与结束时间戳则不应该是使用数据库自带的默认值的,而是应该使用我使用java代码里面传进去的LocalDateTime.now()方法.但是插入后数据我发现有问题,插入的时间比我

随机推荐