MySQL简单架构一（导读）

糟糕的设计

不知道有没有人看到过这种设计。缺点就是当从服务器比较多，切换从服务器最少需要半个小时的时间；而且这种从服务器很多的架构，当访问量很大的时候，对服务器的网卡也是很大的挑战，容易引起故障；

通过监控信息来判断影响服务器 性能

QPS & TPS

并发量& CPU使用率

• 并发量：同一时间处理的请求的数量
• TPS - Transactions Per Second（每秒传输的事务处理个数），这是指服务器每秒处理的事务数，支持事务的存储引擎如InnoDB等特有的一个性能指标。
  • 计算方法：
    • TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME

use information_schema;  
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME ='COM_COMMIT';  
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME ='COM_ROLLBACK';  
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME ='UPTIME';  
select (@num_com+@num_roll)/@uptime;  

• QPS - Queries Per Second（每秒查询处理量）同时适用与InnoDB和MyISAM 引擎
  • 计算方法：
    • QPS=QUESTIONS/UPTIME
  • 1s处理100个sql，QPS=100；100ms处理1个sql，QPS=10

use information_schema;  
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME ='QUESTIONS';  
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME ='UPTIME';  
select @num_queries/@uptime; 

磁盘IO

磁盘IO用的是fashion IO
凌晨，读的峰值很大（可能说明服务器性能急剧下降），检查后，因为是数据库备份远程同步计划任务造成的。

• 最好不要在主库上数据库备份，大型活动前取消这类计划

影响数据库的因素

mysql目前并不支持多CPU，只支持单CPU；

• sql查询速度
• 服务器硬件
• 网卡流量
  • 风险：网卡IO被占满**（1000Mb/8≈100MB)**
  • 如何避免无法连接数据库的情况：
    • 1.减少从服务器的数量
    • 2.进行分级缓存
    • 3.避免使用“**select ***”进行查询
    • 4.分离业务网络和服务器网络
• 磁盘IO
  • 风险：
    • 磁盘IO性能突然下降（使用更快的磁盘设备）
    • 其它大量消耗磁盘性能的计划任务（调整计划任务)
  • 方案：
    • 主备份迁移到从备份，磁盘报警及时处理

• 大促来袭时，访问量会急速增加，对于超高的QPS和TPS,效率低下的sql是很大的风险，
• 当然，大部分数据库问题都是由于慢查询造成的，也就是说可以优化sql来解决问题；
• 风险：
  • 大量的并发（连接数,热数据远远大于服务器可用内存）：
    • 数据库连接数被占满（max_connections默认100)
  • 超高的CPU使用率：
    • 因CPU资源耗尽而出现宕机

还有什么会影响数据库性能

• 大表

  • 记录行数巨大，单表超过千万行
  • 表数据文件巨大，表数据文件超过10G
  • 影响：
    • 慢查询：很难在一定的时间内过滤出所需要的数据
      
      来源只有四个，要从上亿找一小部分数据，将产生大量的磁盘IO;

• 大表对DDL操作的影响

  • 建立索引需要很长的时间
    • 风险：
      • MySQL版本<5.5建立索引会锁表
      • MySQL版本>=5.5虽然不会锁表但会引起主从延迟
  • 修改表结构需要长时间锁表
    • 风险：
      • 会造成长时间的主从延迟
      • 影响正常的数据操作
    • 比如频繁更新的订单日志表，如果在更新时修改表结构，会导致数据连接数突然猛增，连接数被占满，500错误
    • 解决思路：分库分表

如何处理大表

• 分库分表把一张大表分成多个小表
  • 难点：
    • 分表主键的选择
    • 分表后跨分区数据的查询和统计
• 大表的历史数据归档减少对前后端业务的影响
  • 难点：
    • 归档时间点的选择
    • 如何进行归档操作

大事务带来的问题

• 事务是数据库系统区别于其它一切文件系统的重要特性之一
• 事务是一组具有原子性的SQL语句，或是一个独立的工作单元
• 原子性（ATOMICITY)
  • 定义：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作
  • 例子
    • 1.检查理财账户中的余额是否高于2000元
    • 2.从理财账户的余额中减去2000元
    • 3.在活动存款账户上增加2000元
    • 4.失败，全部操作回滚；
• 一致性（CONSISTENCY)
  • 定义：一致性是指事务将数据库从一种一致性状态转换到另外一种一致性状态，在事务开始之前和事务结束后数据库中数据的完整性没有被破坏
• 隔离性（ISOLATION）
  • 定义：隔离性要求一个事务对数据库中数据的修改，在未提交完成前对于其它事务是不可见的
  • SQL标准中定义的四种隔离级别
    • 未提交读（READ UNCOMMITED)
    • 已提交读（READ COMMITED)
    • 可重复读（REPEATABLE READ)
    • 可串行化(SERIALIZABLE)
  • 串行化会在读取的每一行上多加锁，所以可能会出现比较多的锁超时事件 （隔离性最高，并发性最低）
  • 脏读是指一个事务读取到了其他事务没有提交的数据，不可重复读是指一个事务内多次根据同一个查询条件查询出来的同一行记录的值不一样，幻读是指一个事务内多次根据同个条件查出来的记录行数不一样。
  • 
• 持久性（Durability）
  • 定义：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失

查看事务的结构
show variables like '%iso%';
更改事务结构
set session tx_isolation='事务类型';