MySQL性能管理与架构笔记-CSDN博客

磁盘IO

最好不要在主库上数据备份，大型活动前取消备份计划
大促中什么影响数据库性能：SQL查询、服务器硬件、网卡流量、磁盘IO：影响：超高QPS和TPS（效率低下的SQL）、大并发（连接数被占满max_connection = 100）超高CPU使用率（CPU资源耗尽）、磁盘IO性能突然下降（使用更快的磁盘设备），其它大量消耗磁盘性能的计划（调整计划任务，做好磁盘维护）网卡流量，网卡IO被占满（避免无法连接数据库的情况：减少从服务器的数量，进行分级缓存，避免select×查询，分离业务网络和服务网络）

大表问题（千万行记录数，表数据巨大，超过10G）

慢查询：很难一定时间过滤所需的数据
对DDL操作影响：建立索引需要很长时间（5.5建立索引锁表，5.5后引起主从延迟）、修改表结构需要长时间锁表（造成长时间主从延迟，5.5单线程，5.6多线程）
影响正常的数据操作（修改阻塞，连接数占满）

处理：分库分表：把大表分成多个小表。难点：分表主键的选择（区，供应商），分表后的跨分区数据的查询与统计（多表和一表）大表的历史数据归档，减少对前后端业务的影响。难点：归档时间点的选择（订单1年前）

事务

数据库系统区别其它一切文件系统的重要特性，事务是一组原子性的SQL语句，或是独立的工作单元。具有原子性、一致性、隔离性、持久性
复制代码

四种隔离级别：未提交读（脏读）、已提交读（默认），可重复读、串行化。隔离性由低到高，并发行由高到低。
大事务：运行时间长，操作数据多。

风险：锁定太多数据造成大量阻塞和锁超时、回滚时所需的时间较长、执行时间长、容易造成主从延迟。

处理：避免一次处理太多数据（分批处理）、移除不必要在事务中的select操作

性能影响

硬件：服务器系统、不同引擎、数据库参数配置、结构设计和SQL语句
CPU资源和可用内存大小：热数据 > 可用内存（磁盘）、网络（大量数据被查询时）、升级CPU密集型（多，并发量：不支持多CPU对同一SQL并发处理，5.6～）

注意：64位使用32位服务器（资源限制）、内存是越多越好（缓存对读和写都有宜处，多次写入变成一次）、内存主频和CPU主频类似（频率越高，速度越快，主板支持的最大内存频率尽量相同品牌，颗粒，频率，电压，校验技术和型号、单容量尽可能大）
磁盘配置和选择：传统磁盘（常见低、慢）因素：存储容量、传输速度、访问时间、主轴转速（7200-10000-15000）、物理尺寸
RAID增加传统机器磁盘性能：RAID是磁盘冗余队列，多个合成一个，并保证数据完整性的技术
固态存储SSD或PCIE卡：更好的随机读写性能、更好的支持并发、更容易损坏。SSD特点（大量随机IO场景，解决单线程负载“从服务器”）：使用sata接口（接口限制2.6.3），支持RAID技术。PCIE特点：无法支持sata接口（独特驱动的配置），价格比SSD更贵，性能更好（内存）
网络存储SAN和NAS（谨慎）：数据库备份、网络性能的限制延迟、带宽、质量。

建议：采用高性能和高宽带的网络接口设备和交换机（WM）、对多个网卡进行绑定，增强可用性和宽带、尽可能的进行网络隔离（内外网）

操作系统：windows不区分大小写，linux区分（《Linux性能优化大师》）

centos系统参数优化：内核参数etc/sysctl.conf, 增加资源限制/etc/security/limit.conf,磁盘策略sys/block/devname/queue/scheduer Windows： FAT，NTFS Linux：ext3,ext4,XFS

MySQL体系结构

存储引擎针对的是表，而不是库（库的表可以使用不同的引擎）锁：共享锁（读锁），独占锁（写锁）、锁的粒度（策略行，表级锁，行级锁）

myisam：frm 文件存放表格定义，表由myd（数据）和myi（索引）组成。

特性：并发性弱与innoDB，表级锁，表修复（check table tablename）,支持全文索引，数据压缩

限制：5.0之前表大小（4G），修改max_raws.avg_row_length

使用场景：非事务型应用，只读类应用，空间类应用（GPS数据）
InnoDB：表空间（独立".ibd"，共享）进行数据压缩，系统表空间无法压缩文件大小，独立表空间可以通过命令optimize table 压缩，系统产生IO瓶颈，可同时刷新多个。

系统表空间——>独立表空间：1.mysqldump导出数据库。2.停止MySQL服务，修改参数，并删除InnoDB相关文件。3.重启MySQL服务，重建InnoDB表空间，4.重新导入数据

特性：支持事务的ACID特性，redo log（提交事务）和undo log（未提交事务），支持行级锁，行级锁可最大程度支持并发，锁用于实现事务隔离性。

阻塞和死锁：开启一个排它锁“占用了被阻塞事务等待的资源”（慢查询、频繁访问的表）。共享锁：过多的阻塞连接大量堆集占用大量资源。死锁是两个或以上的事务相互占用了对方等待的资源，系统自动会发现处理（粒度小的回滚）
CSV：文件方式存储二进制，所有列不能为null，不支持索引，支持数据编辑。适合做为数据交互的中间表
Archive：以zlib对表数据进行压缩，减少IO，只支持insert，select只支持自增ID索引。适合做日志采集类应用
memory:数据保存在内存中，Hash(等值)，Btree(范围)索引,所有字段固定长度，不支持blog、text大类型，使用表级锁。最大大小max_heap_table_size决定。适合查找或映射表，用于保存数据产生中间表，缓存结果
Feterated：提供访问远程MySQL服务器表的方法，本地不缓存数据，本地连接信息，默认禁止。偶尔的数据分析和查询

总结：事物、备份、崩溃恢复、存储引擎的特有性，不要混合使用存储引擎。

获取配置信息命令行参数：mysqld_safe --detadir=/data/sql_data. "--help --vebose | grep -D | 'default options'"
内存相关配置参数：确定可以使用的内存上限（小于物流内存）、确定MySQL连接使用的内存、确定操作系统预留的内存、缓存池内存分配等。
IO相关配置参数：单日志大小、事务个数，缓存多少写入、单独表空间、双写缓存等。
安全相关配置参数：自动清理binlog天数、控制接收包大小、禁止DNS查找（ip授权）等。
其它：默认最大连接数、内存临时表大小等。
数据库设计注意事项：过分反范式化为表建太多的列，过分范式化造成太多表关联（最大限制64张表）、在OLTP环境中使用不恰当的分区表、使用外键保证数据的完整性。
性能优化顺序：数据库结构设计和SQL语句、数据库引擎（不要混）和参数配置、系统选择及优化、硬件升级。

MySQL基准测试

基准测试是一种测量和评估软件性能指标的活动，用于建立某个时刻性能的基准，以便当系统发生硬件发生变化时，重新进行基准测试以评估变化对性能的影响。优点：直接、简单、易于比较，用于评估服务器的处理能力。

目的：建立MySQL服务器的性能基准线，模拟比当前系统更高的负载，以找出系统的扩展瓶颈，测试不同硬件、软件和操作系统配置。证明新硬件设备是否配置正确。
工具：mysqlslap（自带）、sysbench ......

数据库结构优化

良好的数据库逻辑设计和物理设计是数据库获得高性能的基础。
复制代码

目的：减少数据冗余、尽量避免数据维护中出现更新、插入、删除异常、节约数据存储空间，提高查询效率。
设计步骤：全面了解产品设计的存储需求、数据处理需求、数据安全性和完整性（需求分析），设计数据的逻辑存储结构，数据实体之间的逻辑关系，解决数据冗余和数据维护异常（逻辑设计），根据所使用数据库特点进行表结构设计（物理设计），根据实际情况对索引、存储结构等进行优化（维护）。第一范式：数据库表中所有字段都只具有单一属性，单一属性列由基本的数据类型构成，设计出来的表都是简单二维表。第二范式：要求一张表只有一个业务主键，不能存在非主键列只对部分主键的依赖关系（学分——>课程 != 学号）第三范式：每一个非主属性既不部分依赖，也不传递依赖于业务主键，第二范式的基础上消除了非主键对主键的传递依赖冗余。

表设计总结：定义数据库、表字段命名规范、选择合适的存储引擎、表字段定义合适类型、建立数据库结构。 .......

MySQL高可用的设计

复制： 1.实现了不同服务器的数据分布（利用二进制进行日志增量，不需要太多带宽，基于行的复制进行大批量的更改时会对带宽带来一定压力，特别是跨IDC环境下进行复制，应分批进行）

2.实现数据读取的负载均衡（需要其它组件配合完成，利用DNS轮询方式把程序读连接到不同备份数据库，使用LVS，haproxy代理方式）

3.增强了数据安全性

4.实现数据库高可用和故障切换

5.实现数据库在线升级
二进制日志：记录了所有MySQL数据库的修改事件，包括增删改查和表结构修改事件。 #GTID复制（5.6）、复制性能优化
高可用（避免导致系统不可用的因数）：磁盘耗尽，减少系统不可用时间（建立完善的监督及报警），对备份数据进行恢复测试，正确配置数据库环境，对不需要的数据归档和清理（独立表空间），增加系统冗余，保证发生系统不可用时可以尽快恢复（避免单点故障、sun共享存储、DRDB磁盘复制、NDB集群、主从复制切换） #3M架构、MHA架构、读写分离和负载均衡（MaxScale） .......

数据索引优化

B-Tree索引:更适合进行范围查找（InnoDB指向的是主键，myisam指的是物理地址）

#.可用条件：全值匹配查询（order_id = 600）,匹配最左前缀的查询，匹配列前缀的查询（like ‘zhang%’），匹配范围值的查询（order_id > 300 and order_id < 600），精确匹配查找左前列并范围匹配另一列，只访问索引的查询

#限制：加索引不是按最左列，使用索引是不能跳过索引中的列，not in和<>不能使用索引，如果查询中某列范围查找（右边的所有列都无法使用索引）
Hash索引：基于Hash实现，条件精确匹配索引所有的列时，存储引擎会为每一行计算Hash码（索引——>Hash码）

#限制：无法排序、不支持范围查找、Hash冲突（选择性高的，如：身份证）

总结：索引大大减少了引擎需要扫描的数据量，帮我们进行排序，避免使用临时表，随机IO变顺序IO。缺点：增加了写操作的成本（InnoDB缓存）、太多索引会增加查询优化器的选择时间。

索引策略：不能使用表达式或函数（to_days(out_date) < 100）、前缀索引和索引列的选择性（选择不重复的索引值）

#联合索引：经常用的列优先，选择性高的列优化，宽度小的列优化

#覆盖索引：可以优化缓存，减少磁盘IO，减少随机IO，随机IO变顺序IO，避免InnoDB主键索引二次查询，避免myisam表进行系统调用。

#优化查询：索引列顺序和order by字段顺序保持一致

#利用索引优化锁：减少锁定行数，加快处理速度和锁释放
SQL查询优化、索引优化、库表结构优化需要齐头并进

#实时获取慢查询：select id,'user','host',DB,command,'time',state,info from information_schema.processList where time>30 (s)
SQL解析预处理及生成执行计划
#查询缓存对于一个读写频繁的系统不建议使用，很可能会降低查询处理的效率（锁）

#执行成本不等同实际的执行计划成本，MySQL优化器认为的最优与你认为的最优可能不一样。

......

分库分表

多个数据库拆分到不同的实例：把一个库中的表分离到不同数据库，对一个库相关表进行水平拆分到不同实例的数据库中。
选择分区键：尽量避免跨分区查询，尽量使各分区平均。 #无需分区的表；每个分区存储一份相同的数据，使用额外的节点统一存储（解决查问题）

#如何在节点上部署分片：每分片使用单一库与相同库名，多个分片表存一个库，并在表名上加分片号后缀，一个节点中部署一个库，每个库包含一个分片。

#如何分配片中的数据：按分区键Hash取模分配、分区键范围（日期、数字）、分区键和分片的映射表来分配。

#如何生成全局唯一ID：使用全局节点来生成ID（redis等缓存服务器中创建全局ID）

总结：使用数据归档，不到万不得已在使用水平拆分方式。

##数据库监控数据监控 = 系统的稳定性：对数据库服务可用性监控，数据库性能监控（QPS，TPS），主从复制监控，服务器资源监控（磁盘空间与分区）