摘要：重启，并重建系统表空间。性能优化顺序从上到下下一篇性能管理及架构设计二数据库结构优化高可用架构设计数据库索引优化参考教学视频实战性能管理及架构设计
一、什么影响了数据库查询速度 1.1 影响数据库查询速度的四个因素
1.2 风险分析
QPS：Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
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;
修改事物的隔离级别：set session tx_isolation="read-committed";
4、持久性(DURABILITY)：从数据库的角度的持久性，磁盘损坏就不行了
redo log机制保证事务更新的一致性和持久性
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
如果innodb_file_per_table 为 ON 将建立独立的表空间，文件为tablename.ibd；
如果innodb_file_per_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加入innodb_file_per_table），并删除Inoodb相关文件（可以重建Data目录）。
3、重启MYSQL，并重建Innodb系统表空间。
4、 重新导入数据。
或者 Alter table 同样可以的转移，但是无法回收系统表空间中占用的空间。
2.4 InnoDB存储引擎的特性 2.4.1 特性一：事务性存储引擎及两个特殊日志类型：Redo Log 和 Undo LogInnodb 是一种事务性存储引擎。
完全支持事务的ACID特性。
支持事务所需要的两个特殊日志类型：Redo Log 和Undo Log
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加表级锁命令：lock table table_name write; 写锁会阻塞其它用户对该表的‘读写’操作，直到写锁被释放：unlock tables；
锁的开销越大，粒度越小，并发度越高。
表级锁通常是在服务器层实现的。
行级锁是存储引擎层实现的。innodb的锁机制，服务器层是不知道的
2.5.4 阻塞和死锁 （1）阻塞是由于资源不足引起的排队等待现象。
（2）死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。
参考条件：
事务
备份(Innobd免费在线备份)
崩溃恢复
存储引擎的特有特性
总结:Innodb大法好。
注意:尽量别使用混合存储引擎，比如回滚会出问题在线热备问题。
确定可以使用的内存上限。
内存的使用上限不能超过物理内存，否则容易造成内存溢出；（对于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更大。
不要使用外键约束保证数据的完整性。
从上到下：
下一篇：MySQL性能管理及架构设计（二）：数据库结构优化、高可用架构设计、数据库索引优化
参考教学视频：实战MySQL性能管理及架构设计
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/47604.html
摘要：数据实体之间的逻辑关系，解决数据冗余和数据维护异常。判断标准使用，可以通过输出的列来判断，对于一个索引覆盖查询，显示为查询优化器在执行查询前会决定是否有索引覆盖查询。 上一篇：MySQL性能管理及架构设计（一）：什么影响了数据库查询速度、什么影响了MySQL性能 一、数据库结构优化（非常重要） 1.1 数据库结构优化目的 1、减少数据冗余：（数据冗余是指在数据库中存在相同的数据...
摘要：要么因为并发的问题造成统计结果不准确，要么因为锁资源争用严重造成整体性能的下降。所以我们应该从应用程序着手分析一个应用程序架构的不同设计对整个系统性能的影响更合适。而类型数据的处理性能要远比类型数据的处理性能低下很多。 前言 大部分人通常认为一个数据库应用系统的性能瓶颈，最容易出现在数据的操作方面，而数据库应用系统的大部分数据操作都是通过数据库管理软件所提供的相关的接口完成。所以，数...
摘要：步优化以及其它数据库后端掘金原文链接在发表了一篇简洁有效有趣和令人信服的分钟教程描述了如何进行优化。关于的七种后端掘金对于的，在学习起来可能是比较乱的。 5 步优化 MongoDB 以及其它数据库 - 后端 - 掘金原文链接 Jared Rosoff 在 Scale Out Camp 发表了一篇简洁、有效、有趣和令人信服的《8 分钟 MongoDB 教程》描述了如何进行 MongoDB...
摘要：步优化以及其它数据库后端掘金原文链接在发表了一篇简洁有效有趣和令人信服的分钟教程描述了如何进行优化。关于的七种后端掘金对于的，在学习起来可能是比较乱的。 5 步优化 MongoDB 以及其它数据库 - 后端 - 掘金原文链接 Jared Rosoff 在 Scale Out Camp 发表了一篇简洁、有效、有趣和令人信服的《8 分钟 MongoDB 教程》描述了如何进行 MongoDB...
摘要：真正云原生数据库技术解析年，在腾讯云服务了百万客户之后，腾讯云数据库迎来了突破。由开源数据库适配业务和具体场景，腾讯云自主研发了一款真正的云原生数据库。作为腾讯云在数据库领域的重要布局，单节点读性能可以达到万，全面超越业内目前最高的万水平。 早在上个世纪50、60年代，数据二字就已不再是简单的数字信息而已。随着信息技术的不断发展，在计算机应用领域，计算机存储和处理的对象逐渐广泛，表示这...
阅读 2835·2023-04-25 20:22
阅读 3241·2019-08-30 11:14
阅读 2428·2019-08-29 13:03
阅读 3066·2019-08-26 13:47
阅读 3066·2019-08-26 10:22
阅读 1147·2019-08-23 18:26
阅读 448·2019-08-23 17:16
阅读 1804·2019-08-23 17:01
