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 服务器，修改参数（myf加⼊innodbfileper_table），并删除Inoodb相关⽂件（可以重建Data⽬录）。
mysql下载32位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 性能优化顺序
从上到下：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。