磁盘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),主从复制监控,服务器资源监控(磁盘空间与分区)
..........
复制代码