MySQL 数据库架构

1 架构
master-slave
性能,qps,tps,cpu,disk,memory,network,raid卡，fashion卡，

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

并发量：同一时刻需要db处理 连接量： 大表：1000w行或者表文件10g       影响：慢查询，对表ddl操作（长时间的锁表，主从复制延时，对数据操作影响）       处理：分库分表，把一个大表分成多个小表       难点：分表主键的选择，订单号，供应商，地区分表             分表后跨分区的查询和统计         影响后端已有的业务                     处理：对大表的历史数据归档，减少对前后端业务的影响       难点：归档时间的选择，比如订单，1年前，或者几个月，纯日志归档，可以1个月             如何进行归档操作，主从延时， 大事务：事务是数据库系统区别于其他一切文件系统的重要特性         是一组具有原子性的sql语句，或者是一个独立的单元         原子性 atomicity，要么全部成功，要么全部失败         一致性 consistency，将一种一致性状态转换到另外一种一致性状态，完整性没有被破坏         隔离性isolation，一个事务对数据进行修改，未提交前，其他事务是不可见           RR/RC--可重读，已提交读（不可重复读）         持久性 durability，所做的修改永远保存到数据库中     大事务，运行时间比较长，操作的数据比较多的事务     风险：锁定太多的数据，找出大量的阻塞和锁超时      回滚时所需要的时间比较长          执行时间长，容易造成主从延时     处理：避免一次处理太多的数据，多批次处理           移除不必要的事务中的select操作

2 性能影响

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

硬件 操作系统/服务器系统 数据库engine的选择 数据库参数配置 数据库表结构设计和sql语句      cpu，--cpu选择，频率，数量     计算密集型--频率     系统并发量--数量 16，32     web类应用，mysql版本5.6,5.7 ，数量>频率    内存：数据库利用内存有限，ssd，fusion io    磁盘：传统磁盘，磁头，磁盘--存储容量，传输速度，访问时间，主轴转速          raid传统磁盘，raid0，raid1，raid5，raid10(raid1,raid0)          ssd,-更好随机读写性能，更好支持并发，容易损坏（每次写入，需要擦除）          ssd，--slave 单线程          pci-e ssd--接口          san-光纤到服务器，网络存储--数据库备份          nas--网络设备    网络：网络带宽，网络质量         --网络隔离     --多网卡绑定     --高性能交换机

　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

操作系统；     linux     centos系统参数优化     /etc/sysctl.conf       net.core.somaxconn=65535       net.core.netdev_max_backlog=65535       net.ipv4.tcp_max_syn_backlog=65535       net.ipv4.tcp_fin_timeout=10       net.ipv4.tcp_tw_reuse=1       net.ipv4.tcp_tw_recycle=1       net.core.wmem_default=87380       net.core.wmem_max       net.core.rmem_default       net.core.rmem_max       net.ipv4.tcp_keepalive_time/intvl/probes=120/30/3       kernel.shmmax=4g       vm.swappiness=0/10     /etc/security/limit.conf       * soft nofile 65535       * hard nofile 65535     磁盘调度策略     /sys/block/devname/queue/scheduler      --deadline  文件系统      linux ext3,ext4,xfs      ext3/4系统挂载参数 /etc/fstab data=writeback           noatime，nodiratime

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

mysql：engine       客户端--连接管理器-查询缓存-查询解析-查询优化器-mysql服务层       myisam--查询优化器生成的临时表              --并发性，锁级别              --表损坏修复-check table tablename，repair table tablename，myisamcheck          --fulltext index，text block index，压缩表myisampack -b -f myisam.MYI(read only)          --非事务型应用，只读类应用--压缩，空间类应用--空间函数5.7之前       innodb--事务，ibd，ibdata-tablespace undo，redo-顺序，undo 随机，             --mvcc，2pc，doublewrite，自适应hash index，wal日志预写，插入缓冲             --使用独立表空间，mysqldump导出数据，新建data目录，导入原来的ibdata的数据             --支持行级锁，RR，gap lock，支持并发，行级锁是在engine层实现             --锁，管理共享资源的并发访问，实现事务的隔离性             --x 锁，s锁，--innodb两步获得锁             --阻塞，死锁             --show engine innodb status             --5.7-支持全文索引，空间函数          csv--普通csv文件             --数据以文本方式存储在文件中             --.csv文件存储表内容，csm表的元数据             --1,"aaa"-所有列不能为null，不支持索引，不适合大表，不适合在线处理，             --可对数据文件直接进行编辑，flush tables             --适合为数据交换的中间表，数据-csv文件-mysql，web程序          archive--对表数据进行压缩，磁盘io更少              --arz后缀，只支持insert，select，高并发操作              --只允许在自增id上增加index              --使用场景，日志和数据采集类应用              memory--所有数据保存在内存中，只有frm文件              --支持hash索引和btree索引，所有字段固定长度char(10)，不支持blob和text等大字段              --表级锁，大小有max_heap_table_size 参数决定，旧表无效，必须重建              --vs临时表，查询优化器，系统使用的临时表（myisam，memory），create tempary table建立的临时表              --使用场景，找着或映射表，保存中产生的中间表          federated--类似oracle dblink，访问远程mysql服务器上表的方法              --本地不存数据，数据放在远程服务器              --本地需要保存表结构  engine选择：事务，备份，崩溃恢复，存储引擎的特有特性

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

mysql 参数             set global 参数名=参数值          set @@global.参数名=参数值          set session      set @@session 1 内存     sort_buffer_size,join_buffer_size，read_buffer_size myisam读，read_rnd_buffer_size每个session     系统预留内存     innodb_buffer_pool_size     key_buffer_size 2 io     innodb_log_file_size         innodb_log_files_in_group         innodb_log_buffer_size         innodb_flush_log_at_trx_commit          innodb_flush_method     innodb_file_per_table     innodb_doublewrite     expire_logs_days     max_allowed_packet     skip_name_resolve 3 安全     sysdate_is_now     read_only     skip_slave_start     sql_mode 4 其他     sync_binlog     tmp_table_size,max_heap_table_size     max_connections               数据库结构设计和sql优化         过分的反范式化为表建立太多的列          过分的范式化造成太多的表关联         使用不恰当的分区表         使用外键保证数据的完整性

3 数据库测试

?

1 2 3

基准测试-上线之前，压力测试-逻辑    sysbench，tpcc-mysql,mysqlslap，orzdba qps，tps，响应时间，并发量，cpu,io,network

4 数据库结构优化

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

减少数据冗余 尽量避免数据维护中出现更新， 插入和删除异常  节约数据存储空间 提高查询效率    需求分析：全面了解产品设计的存储需求，存储需求，数据处理需求，数据的安全性和完整性 逻辑设计：设计数据的逻辑存储结构，数据实体之间的逻辑关系，解决数据冗余和数据维护异常 物理设计：根据所使用的数据库特点进行表结构设计       mysql，innodb，varchar，int，date 维护优化：根据实际情况对索引，存储结构进行优化   设计范式：1 表中的列不可再分，单一属性，单一属性的列由基本数据类型构成，二维表         2 表中只有一个业务主键，表中不存在非主键列对部分主键的依赖     3 存在传递依赖关系