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

《MySQL查询速度慢与性能差的原因与解决方法》由会员分享，可在线阅读，更多相关《MySQL查询速度慢与性能差的原因与解决方法（5页珍藏版）》请在装配图网上搜索。

1、MySQL查询速度慢与性能差的原因与解决方法一、什么影响了数据库查询速度1. 影1响数据库查询速度的四个因素暫I査询速度I影响数据库的因素网卡流量风险分析QSQ意思是每秒查询率，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。S是的缩写，也就是事务数秒。它是软件测试结果的测量单位。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。:最好不要在主库上数据库备份，大型活动前取消这样的计划。效率低下的：超高的Q与。汶量的并发：数据连接数被占满（默认，一般把连接数设置得大一些）。并发量同一时刻数据库服务器处理的

2、请求数量超高的使用率：资源耗尽出现宕机。磁盘：磁盘性能突然下降、大量消耗磁盘性能的计划任务。解决：更快磁盘设备、调整计划任务、做好磁盘维护。网卡流量：如何避免无法连接数据库的情况减少从服务器的数量（从服务器会从主服务器复制日志）进行分级缓存（避免前端大量缓存失效）避免使用进行查询分离业务网络和服务器网络大表带来的问题（重要）1.4大.表1的特点记录行数巨大，单表超千万表数据文件巨大，超过个1.4大.表2的危害慢查询：很难在短时间内过滤出需要的数据查询字区分度低要在大数据量的表中筛选出来其中一部分数据会产生大量的磁盘降低磁盘效率对影响：建立索引需要很长时间：MySQL建立索引会锁表MySQL建立

3、索引会造成主从延迟（y建立索引，先在组上执行，再在库上执行）修改表结构需要长时间的锁表：会造成长时间的主从延迟（4秒8延0迟）1.4如.何3处理数据库上的大表分库分表把一张大表分成多个小表难点：分表主键的选择分表后跨分区数据的查询和统计大事务带来的问题（重要*什么是事务事务是憩据库系统区别于具它一切文件系统的重宴蒔性之一事务是一纭具有原子性的SQL语句r或是一个独立的工作单元1.5.2事务的ACID属性1、原子性(atomicity):全部成功，全部回滚失败。银行存取款。2、一致性(consistent):银行转账的总金额不变。3、隔离性(isolation):隔离性等级：未提交读(READU

4、NCOMMITED)脏读，两个事务之间互相可见；已提交读(READCOMMITED)符合隔离性的基本概念，一个事务进行时，其它已提交的事物对于该事务是可见的，即可以获取其它事务提交的数据。可重复读REPEATABLEREAD)InnoDB的默认隔离等级。事务进行时，其它所有事务对其不可见，即多次执行读，得到的结果是一样的！可串行化(SERIALIZABLE)在读取的每一行数据上都加锁，会造成大量的锁超时和锁征用，严格数据一致性且没有并发是可使用。查看系统的事务隔离级别：showvariableslike%iso%;开启一个新事务：begin;提交一个事务：commit;修改事物的隔离级别：se

5、tsessiontx_isolation=read-committed;4、持久性(DURABILITY):从数据库的角度的持久性，磁盘损坏就不行了|未提交读(READUNCOMMITED)f已提交读(READCOMMITED)宙低可重复读(REPEATABLEREAD)到高可串行(L(SERIALIZABLE)redolog机制保证事务更新的一致性和持久性1.5.3大事务运行时间长，操作数据比较多的事务；风险：锁定数据太多，回滚时间长，执行时间长。锁定太多数据，造成大量阻塞和锁超时；回滚时所需时间比较长，且数据仍然会处于锁定；如果执行时间长，将造成主从延迟，因为只有当主服务器全部执行完写入日

6、志时，从服务器才会开始进行同步，造成延迟。解决思路：避免一次处理太多数据，可以分批次处理；移出不必要的SELECT操作，保证事务中只有必要的写操作。二、什么影响了MySQL性能(非常重要)2.1影响性能的几个方面服务器硬件。服务器系统(系统参数优化)。存储引擎。MylSAM:不支持事务，表级锁。InnoDB:支持事务，支持行级锁，事务ACID。数据库参数配置。数据库结构设计和SQL语句。(重点优化)MySQL体系结构分三层：客户端-服务层-存储引擎目目目存储引第层MySQL是插件式的存储引擎，其中存储引擎分很多种。只要实现符合mysqI存储引擎的接口，可以开发自己的存储引擎所有跨存储引擎的功能

7、都是在服务层实现的。MySQL的存储引擎是针对表的，不是针对库的。也就是说在一个数据库中可以使用不同的存储引擎。但是不建议这样做。InnoDB存储引擎MySQL5.5及之后版本默认的存储引擎：InnoDB。InnoDB使用表空间进行数据存储。showvariableslikeinnodbile_per_table如果innodbfileper_table为ON将建立独立的表空间，文件为tablename.ibd;如果innodbfileper_table为OFF将数据存储到系统的共享表空间，文件为bdataX（X为从1开始的整数）；.frm:是服务器层面产生的文件，类似服务器层的数据字典，记录

8、表结构。（MySQL5.5默认）系统表空间与（MySQL5.6及以后默认）独立表空间1.1系统表空间无法简单的收缩文件大小，造成空间浪费，并会产生大量的磁盘碎片。1.2独立表空间可以通过optimezetable收缩系统文件，不需要重启服务器也不会影响对表的正常访问。2.1如果对多个表进行刷新时，实际上是顺序进行的，会产生IO瓶颈。2.2独立表空间可以同时向多个文件刷新数据。强烈建立对Innodb使用独立表空间，优化什么的更方便，可控。2.3.3系统表空间的表转移到独立表空间中的方法1、使用mysqldump导出所有数据库数据（存储过程、触发器、计划任务一起都要导出）可以在从服务器上操作。2、

9、停止MYsql服务器，修改参数（f加入innodbfileper_table），并删除Inoodb相关文件（可以重建Data目录）。3、重启MYSQL，并重建Innodb系统表空间。4、重新导入数据。或者Altertable同样可以的转移，但是无法回收系统表空间中占用的空间。InnoDB存储引擎的特性2.4.1特性一：事务性存储引擎及两个特殊日志类型：RedoLog和UndoLogInnodb是一种事务性存储引擎。完全支持事务的ACID特性。支持事务所需要的两个特殊日志类型：RedoLog和UndoLogRedoLog：实现事务的持久性（已提交的事务）。UndoLog：未提交的事务，独立于表空

10、间，需要随机访问，可以存储在高性能io设备上。Undo日志记录某数据被修改前的值，可以用来在事务失败时进行rollback;Redo日志记录某数据块被修改后的值，可以用来恢复未写入datafile的已成功事务更新的数据。2.4.2特性二：支持行级锁InnoDB支持行级锁。行级锁可以最大程度地支持并发。行级锁是由存储引擎层实现的。2.5什么是锁2.5.1锁*锁对主要作用是管連共享资源的并发访问锁用于实现事务的隔离性2.5.2锁类型共享锁（也称读锁）写锁读锁不慕容独占锁（也称写锁）不兼容兼容2.5.3锁的粒度MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关表级锁行级锁将tab

11、le_name加表级锁命令：locktabletable_namewrite;写锁会阻塞其它用户对该表的读写操作，直到写锁被释放：unlocktables;锁的开销越大，粒度越小，并发度越高。表级锁通常是在服务器层实现的。行级锁是存储引擎层实现的innodb的锁机制，服务器层是不知道的2.5.4阻塞和死锁（1）阻塞是由于资源不足引起的排队等待现象。（2）死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。2.6如何选择正确的存储引擎参考条件：事务备份（Innobd免费在线备份）崩溃恢复存储引擎的特有特性总结：

12、Innodb大法好。注意:尽量别使用混合存储引擎，比如回滚会出问题在线热备问题。2.7配置参数2.7.1内存配置相关参数确定可以使用的内存上限。内存的使用上限不能超过物理内存，否则容易造成内存溢出；（对于32位操作系统，MySQL只能试用3G以下的内存。）确定MySQL的每个连接单独使用的内存。sort_buffer_size#定义了每个线程排序缓存区的大小，MySQL在有查询、需要做排序操作时才会为每个缓冲区分配内存（直接分配该参数的全部内存）join_buffer_size#定义了每个线程所使用的连接缓冲区的大小，如果一个查询关联了多张表,MySQL会为每张表分配一个连接缓冲，导致一个查询

13、产生了多个连接缓冲ead_buffer_size#定义了当对一张MyISAM进行全表扫描时所分配读缓冲池大小，MySQL有查询需要时会为其分配内存，其必须是4k的倍数；readnd_buffer_size#索引缓冲区大小，MySQL有查询需要时会为其分配内存，只会分配需要的大小。注意：以上四个参数是为一个线程分配的，如果有100个连接，那么需要x100oMySQL数据库实例：MySQL是单进程多线程（而oracle是多进程），也就是说MySQL实例在系统上表现就是一个服务进程，即进程；MySQL实例是线程和内存组成，实例才是真正用于操作数据库文件的；一般情况下一个实例操作一个或多个数据库；集群

14、情况下多个实例操作一个或多个数据库。如何为缓存池分配内存：Innodb_buffer_pool_size,定义了Innodb所使用缓存池的大小，对其性能十分重要，必须足够大，但是过大时，使得nnodb关闭时候需要更多时间把脏页从缓冲池中刷新到磁盘中；总内存-(每个线程所需要的内存*连接数)-系统保留内存key_buffer_size，定义了MylSAM所使用的缓存池的大小，由于数据是依赖存储操作系统缓存的，所以要为操作系统预留更大的内存空间；selectsum(index_length)frominformation_schema.talbeswhereengine=myisam注意：即使开发使用的表全部是Innodb表，也要为MyISAM预留内存，因为MySQL系统使用的表仍然是MylSAM表。max_connections控制允许的最大连接数，一般2000更大。不要使用外键约束保证数据的完整性。2.8性能优化顺序从上到下：数据库结构设tfflSQL语句数据库存储引擎的选择和参数配置硬件升级