MySQL数据库架构--入门

一般Web服务器支持横向扩展,而数据库服务器则不支持横向扩展(复制数据会破坏数据库的一致性和完整性)。

缺少主从复制组件的情况下,需手动切换主从服务器,然后主从复制(主从复制时间较长)。

QPS:Queries Per Second意思是“每秒查询率”,是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。

TPS:是TransactionsPerSecond的缩写,也就是事务数/秒。它是软件测试结果的测量单位。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时,收到服务器响应后结束计时,以此来计算使用的时间和完成的事务个数。

并发量:同一时间处理的请求的数量
同时连接数:平时连接数远大于请求数,大部分都处于sleeping状态

磁盘IO:在数据中心中,服务器、网络、存储这三大核心组件构建了整个数据中心IT基础架构。

注意:最好不要在主库上进行数据库备份。

影响数据库性能的因素
  1. sql查询速度
  2. 网卡流量
  3. 服务器硬件
  4. 磁盘IO

超高的QPS和TPS
风险: 效率低下的SQL
随着访问量的增长,会有很高的QPS和TPS,这个时候,每处理一个sql语句的时间就很重要。很多数据库问题都是慢查询导致。

大量的并发和超高的CPU使用率
风险:
大量的并发:数据库连接数被占满(max_connections默认100,生产中改大一些)
连接数占满,可能导致有用的数据库连接无法连接。
超高的CPU使用率:因CPU资源耗尽而出现宕机

磁盘IO
风险:磁盘IO性能突然下降(使用更快的磁盘设备)
其他大量消耗磁盘性能的计划任务(调整计划任务,做好磁盘维护)

网卡流量
风险:网卡IO被占满(1000Mb/8≈100MB)
如何避免无法连接数据库的情况:

  1. 减少从服务器的数量(因为从服务器都要从主服务器复制日志,从服务器越多,网络流量越大)

  2. 进行分级缓存

  3. 避免使用select *进行查询

  4. 分离业务网络和服务器网络

大表带来的影响

什么样的表称之为大表:(相对而言)

  • 记录函数巨大,单表超过千万行
  • 表数据文件巨大,表数据文件超过10G

大表对查询的影响:
慢查询:很难在一定时间内过滤出所需要的数据

大表对DDL操作的影响:

  1. 建立索引需要很长时间
    风险:
    MySQL版本<5.5 建立索引会锁表
    MySQL版本>=5.5虽不会锁表但会引起主从延迟
  1. 修改表结构需要长时间锁表
    风险:
    会造成长时间的主从延迟

    DDL操作都是先在主服务器进行,然后通过日志复制,然后在从服务器运行

    影响正常的数据操作(在主表上进行的会其他操作会进行阻塞)

如何处理数据库中的大表

  1. 分库分表把一张大表分成多个小表
    难点:
    分表主键的选择
    分表后跨分区数据的查询和统计

  2. 大表的历史数据归档减少对前后端业务的影响
    难点:
    归档时间点的选择
    如何进行归档操作

大事务带来的问题

什么是事务?

  1. 事务是数据库系统区别于其他一切文件系统的重要特性之一

    对于文件系统,如果突然出现文档损坏则容易出现数据的丢失。

  2. 事务是一组具有原子性的SQL语句,或是一个独立的工作单元。(一条SQL语句或多条SQL语句的组合)

事务的四个特性:

  • 原子性

  • 一致性

  • 隔离性

  • 持久性

事务的原子性(ATOMICITY)

定义:一个书屋必须被事务一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交完成,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作

事务的一致性(CONSISTENCY)
定义:一致性是指事务将数据库从一种一致性状态转换到另一种一致性状态,在事务开始之前和事务结束后数据库中数据的完整性没有被破坏。

事务的隔离性(ISOLATION)
定义:隔离性要求一个事务对数据库中数据的修改,在未提交完成前对于其他事务是不可见的

SQL标准中定义的四种隔离级别
如何查看事务隔离级别:

1
2
3
4
5
6
7
mysql> show variables  like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.40 sec)

如何设置事务隔离级别:

1
2
mysql> set session tx_isolation='REPEATABLE-READ';
Query OK, 0 rows affected (0.32 sec)

  • 未提交读(READ UNCOMMITED)
    指的是即使一个事务未提交,但是对数据的修改对其他事务是能够被可见的,未提交的数据被称为脏数据

  • 已提交读(READ COMMITED)
    指的是一个事务在未被提交时,对其他事务是不可见的,只有被提交之后,才是可见的。

  • 可重复读(REPEATABLE READ)
    指的是对同一个事务中的同一个数据在多次读取的时候,结果是一样的
    不同事务对同一张表进行操作的时候,可重复读是不能读取其他事务对表进行的操作,而已提交读中是可以读取其他事务对表的操作。

    例子:我们在两个会话中进行操作,一个会话读取,一个会话新增,我们在读取会话是看不到新增会话的内容的,即使新增会话的事务已经提交。

  • 可串行化(SERIALIZABLE)
    串行化会对每个读取的行上都加锁,会导致大量的锁超时和锁争用问题。

隔离性由低到高,并发性是由高到低。
InnoDB的默认隔离级别是可重复读。

事务的持久性(DURABILITY)
定义:一旦事务被提交,则其所做的修改就会永远保存到数据库中。此时,即使系统崩溃,已提交的修改数据也不会丢失。

什么是大事务?
定义:运行时间长比较长,操作的数据比较多的事务
风险:
锁定太多的数据,造成大量的阻塞和锁超时
回滚时间所需时间长,回滚的时候,数据同样是处于锁定状态
执行时间长,容易造成主从延迟(需要先在主服务器进行)

如何处理大事务?

  1. 避免一次处理太多的数据(分批处理)
  2. 移除不必要在事务中的SELECT操作(只有必要的写操作)
知识就是财富
如果您觉得文章对您有帮助, 欢迎请我喝杯水!