扛得住双11的Mysql

电商双11数据库笔记

保障完整性和一致性

数据库架构

master(1)—slave(15)

没有主从复制组件

当Master发生鼓掌时,DBA手动选择一个新的服务器

缺陷:耗时较多

监控

1. QPS和TPS
2. 并发量&CPU使用率
3. 磁盘IO

fashion IO 吞吐量比普通磁盘大很多

• 服务器性能急剧下降,造成大量的阻塞
• 原因:数据库备份远程同步计划任务
• 解决方案:最好不要在主库上数据库备份,大型活动前取消这类计划

影响数据库的因素

1. sql查询速度
2. 服务器硬件
3. 网卡流量
4. 磁盘IO

超高的QPS和TPS

风险:效率低下的SQL

QPS:每秒钟处理的查询量

大量的并发和超高的CPU使用率

风险:大量的并发导致数据库连接数被占满(max_connection默认为100)

超高的CPU使用率因CPU资源耗尽而出现宕机

磁盘IO

风险:磁盘IO性能突然下降

使用更快的磁盘设备

其它大量消耗磁盘性能的计划任务(调整计划任务,做好磁盘维护)

网卡流量

风险:网卡IO被占满

如何避免无法连接数据库的情况

1. 减少从服务器的数量
2. 进行分级缓存
3. 避免使用”select *”进行查询
4. 分离业务网路和服务器网路

大表带来的问题

记录行数巨大,单表超过千万行

表数据文件巨大,表数据文件超过10G

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

CREATE TABLE `buy_logs`(
    `id` int(11) not null auto_increment comment '自增id',
    `uid` int(11) not null comment '下单用户id',
    `order_from` varchar(5) not null comment '订单来源:web,jd,tb,qq',
    `order_id` int(11) not null comment '订单id',
    `create_time` datatime not null comment '下单时间',
    primary key(`id`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

订单数据达到了亿条,订单来源少,区分度低,查询一部分数据会产生大量的磁盘IO,降低磁盘效率

大促时销售量的排行,会搞掉数据库

大表对DDL操作的影响

建立索引需要很长的时间

MySQL版本<5.5 建立索引会锁表

MySQL版本>=5.5 虽然不会锁表但会引起主从延迟

修改表结构需要长时间锁表

风险:会造成长时间的主从延迟

影响正常的数据操作,造成数据库连接数被占满

如何处理数据库中的大表

分库分表把一张大表分成多个小表

难点

1. 分表主键的选择
2. 分表后跨分区数据的查询和统计

大表的历史数据归档

减少对前后端业务的影响

难点

1. 归档时间点的选择
2. 如何进行归档操作

大事务带来的问题

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

事务是一组具有原子性的SQL语句,或是一个独立的工作单元

事务特性

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

原子性ATOMICITY

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

eg:银行

一致性CONSISTENCY

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

隔离性ISOLATION

要求一个事务对数据库中数据的修改,在未提交完成前对于其它事务是不可见的

SQL标准四种隔离级别

1. 未提交读(READ UNCOMMITED):事务中的修改,即使没有提交,其它事务也都是的可见的—脏读(Dirty Read);实际应用一般很少使用
2. 已提交读(READ COMMITED):大多数数据库系统的默认隔离级别(Mysql不是…),也叫做不可重复读,因为两次执行同样的查询,可能会得到不一样的结果
3. 可重复读(REPEATABLE READ):可重复读解决了脏读的问题,该级别保证了在同一个事务中多次读取记录的结果是一致的.可重复读隔离级别还是无法解决另一个幻读(Phantom read)问题—当某个事务在读取某个范围内记录时,另外一个事务中又在该范围插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻读.MySQL默认级别
4. 可串行化(SERIALIZABLE):最高隔离级别,简单来说，可串行化会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑用该级别。

并发性由高到低(1~4)

--查询数据库隔离级别
show variables like '%iso%';

--设定已提交读
set session tx_isolation='read-committed';

持久性DURABILITY

一旦事务提交,则其所做的修改就会永久保存到数据库中.此时即使系统崩溃,已经提交的修改数据也不会丢失

大事务

定义:运行时间比较长,操作的数据比较多的事务

风险:

1. 锁定太多的数据,造成大量的阻塞和锁超时
2. 回滚时所需时间比较长
3. 执行时间长,容易造成主从延迟

处理:

1. 避免一次处理太多的数据(分批次处理)
2. 移出不必要的事务中的SELECT操作

---

source

1. 数据库高分笔记02：隔离级别
2. imooc-打造扛得住的MySQL数据库架构