打造扛得住的MySQL数据库架构

第一章 实例和故事

1-1 什么决定了电商双11大促的成败

web服务器可以随意扩展

数据库无法随意扩展 数据库具有 完整性和一致性

在双11大促中的数据库服务器

历史的数据库架构

• 1 台 Master -> 15 台 Slave

• 没有主从复制的组件

• 需要手动把 有相对最新数据的从服务器 转为主服务器

• 手动转换相当耗时

监控信息(影响数据库性能)

最高峰 35W次 QPS&TPS

最大值 700 并发量

磁盘IO 能力要高

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

1-3 在大促中什么影响了数据库性能

影响数据库的因素|

• sql查询速度

• 服务器硬件

• 网卡流量

• 磁盘IO

效率低下的SQL

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

风险：
大量的并发：数据库连接数被占满(max_connections默认100 生产环境要改大)
超高的CPU使用率： 因CPU资源耗尽而出现宕机

磁盘IO

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

网卡流量

风险：
网卡IO被占满（1000Mb/8 ≈ 100MB）

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

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

1-4 大表带来的问题

什么样的表称为大表 ？

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

• 表数据文件巨大，表数据文件超过10G

• 以上只是理论上，日志表就算超过千万行也对业务无影响，要根据实际例子

大表对查询的影响

• 慢查询：很难在一定的时间内过滤出所需要的数据

大表对DDL操作的影响

• 建立索引需要很长的时间

风险:
MySQL版本<5.5 建立索引会锁表
MySQL版本>=5.5 虽然不会锁表但会引起主从延迟

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

风险:
会造成长时间的主从延迟
影响正常的数据操作

如何处理数据库中的大表

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

难点:
分表主键的选择
分表后跨分区数据的查询和统计

• 大表的历史数据归档 <font color="red">减少对前后端业务的影响</font>

难点:
归档时间点的选择
如何进行归档操作

1-5 大事务带来的问题

什么是事务

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

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

事务具有以下特性

• 原子性

• 一致性

• 隔离性

• 持久性

事务的原子性(ATOMICITY)

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

例:

1. 检查理财账户中余额是否高于2000元
2. 从理财账户的余额中减去2000元
3. 在活动存款账户上增加2000元
   这三步不管哪步崩溃都无法进行

事务的一致性(CONSISTENCY)

定义：
一致性是指事务将数据库从一种一致性状态转换到另外一种一致性状态，在事务开始之前和事务结束之后数据库中的数据完整性没有被破坏
例:
还是原来的例子，总的账户余额保持一致，就叫做一致性。

事务的隔离性(ISOLATION)

定义：
隔离性要求一个事务对数据库中数据的修改，在未提交完成前对于其它事务是不可见的

例:
还是原来的例子，在转账还未完成时，还是能看到理财账户中的2000元

     SQL标准定义的四种隔离级别

• 未提交读(READ UNCOMMITED)（脏读）

• 已提交写(READ COMMITED)一般的SQL数据库的级别 （这就是不可重复读）

• 可重复读(REPEATABLE READ)

• 可串行化(SERIALIZABLE) 很少用，因为容易锁

• 1-4 隔离性由低到高 1-4 并发性由高到低

事务的持久性(DURABILITY)

定义：
一旦事务提交，这其所作的修改就会永久保存到数据库中。

此时即使系统崩溃，已经提交的修改数据也不会丢失。

什么是大事务

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

风险:
锁定太多的数据，造成大量的阻塞和锁超时
回滚时所需的时间比较长
执行时间长，容易造成主从延迟

如何处理大事务

1. 避免一次处理太多的数据

2. 移出不必要在事务中的SELECT操作

总结

• 直观的展示了数据库在繁忙时的系统状态

• 简单了解了对性能有影响的一些因素

第二章 什么影响了MySQL性能

2-1 影响性能的几个方面

1. 硬件-------cpu 内存 磁盘IO

2. 操作系统

3. 数据库存储引擎的选择

MySQL的最大特点是插件式存储引擎
MyISAM:不支持事务，表级锁。

InnoDB:事务存储引擎，完美支持行级锁，事务ACID特性。

4. 数据库参数配置 (DBA要懂) 前3项的影响加起来也许都没第4项的影响大

5. 数据库结构设计和SQL语句（<font color="red">重点</font>）

2-2 CPU资源和可用内存大小

如何选择CPU？

• 我的应用是CPU密集型的吗？

MySQL不支持多CPU对同一SQL并发处理

• 我们系统的并发量如何？

1 * CPU -> 1 * SQL

40 * CPU -> 40 * SQL

QPS是秒级的，SQL一般是毫秒或者纳秒级别的

web应用中一般并发量比较高，核心数量比频率重要

• 我们使用的MySQL的版本

老版本对多核CPU支持很差

5.6-5.7对多核CPU支持有所改善

• 选择32位还是64位CPU？（现在没啥32位了吧）

现在想买32位CPU可能都买不到了

64位CPU使用32位的服务器版本

内存

目前内存速度还是大于SSD的

• MYSIAM对内存的缓存

内存对MYISAM

• InnoDB数据对内存的缓存

内存对InnoDB

提示:
内存越多越好，但对性能影响有限,并不能无限的增加性能。数据库能使用的内存是有限，如果它所有的数据都缓存到内存中则再加内存也没有意义。

不过多余的内存增加操作系统等其他服务的性能

2-3 磁盘的配置和选择

1. 使用传统机器硬盘

2. 使用 RAID 增强传统机器硬盘的性能

3. 使用固态存储 SSD 和 PCIe 卡

4. 使用网络存储 NAS 和 SAN

传统磁盘

• 最常见

• 使用最多

• 价格低

• 存储空间大

• 读、写较慢

传统机器硬盘读取数据的过程

1. 移动磁头到磁盘表面上等正确位置

2. 等待磁盘旋转，使的所需的数据在磁头之下

3. 等待磁盘旋转过去，所有所需的数据都被磁头读出

如何选择传统机器硬盘

1. 存储容量

2. 传输速度（也就是上面的第三步）

3. 访问时间

4. 主轴转速

5. 物理尺寸

2-4 使用 RAID 增强传统机器硬盘的性能

什么是RAID

RAID是磁盘冗余队列的简称(Redundant arrays of Independent Disk)

简单来说RAID的作用就是可以把多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据的完整性的技术

RAID 0---常用的RAID组别

RAID 0 是最早出现的RAID模式，也称为数据条带。是组建磁盘阵列中最简单的一种形式，只需要2块以上的磁盘即可，成本低，可以提高整个磁盘的性能和吞吐量。RAID 0 没有提供冗余或错误修复能力，但是实现成本是最低的。

RAID 1---常用的RAID组别

RAID 1 又称磁盘镜像,原理是把一个磁盘的数据镜像到另一个磁盘上，也就是说数据在写入一块磁盘的同时，会在另一块闲置的磁盘上生成镜像文件，在不影响性能情况下最大限度的保证系统的可靠性和可修复性。

RAID 5---常用的RAID组别

RAID 5 又称为分布式奇偶校验的独立磁盘阵列

通过分布式奇偶校验块把数据分散到多个磁盘上，这样如果任何一个盘数据失效，都可以从奇偶校验块中重建。但是如果两块磁盘失效，则整个卷的数据都无法恢复。

RAID 10---常用的RAID组别

RAID 10 又称为分片的镜像

它是对磁盘先做 RAID 1 之后对两组 RAID 1 的磁盘再做 RAID 0 , 所以对读写都有良好的性能，相对于 RAID 5 重建起来更简单,速度也更快。

RAID 级别的选择

主库一般使用 RAID 10 从库使用 RAID 0 或 RAID 5

2-5 使用固态存储 SSD 或 PCIe 卡

固态存储也称为闪存(Flash Memory)

特点

• 相比机械硬盘固态磁盘有更好的随机读写性能

• 相比机械磁盘固态磁盘能更好的支持并发

• 相比机械磁盘固态磁盘更容易损坏（缺点）

SSD(固态硬盘)的特点

1. 使用 SATA 接口 可以替换传统磁盘而不需要任何改变

2. SATA 接口的 SSD 同样支持 RAID 技术

PCIe SSD(Fusion-IO)卡的特点

1. 无法使用 SATA 接口 需要独特的驱动和配置

2. 价格相对于SSD更贵 性能比SSD更好

固态存储的使用场景

• 适用于存在大量随机I/O的场景

• 使用于解决单线程负载的I/O瓶颈

2-6 使用网络存储 SAN 和 NAS

SAN(Storage Area Network) 和 NAS(Network-Attached Storage)是两种外部文件存储设备加载到服务器上的方法

SAN

• SAN 设备通过光纤连接到服务器，设备通过接口访问，服务器可以将其当做硬盘使用

• 大量顺序读写、读写I/O、缓存、I/O合并、随机读写慢、不如本地RAID磁盘

NAS

• NAS 设备使用网络连接，通过基于文件的协议如 NFS 或 SMB 来访问

网络存储使用的场景

• 数据库备份

网络对性能的影响

• 网络带宽对性能的影响

  • 延迟

  • 吞吐量（带宽）

• 网络质量对性能的影响

建议

• 采用高性能和高带宽的网络接口设备和交换机
• 对多个网卡进行绑定，增强可用性和带宽
• 尽可能的进行网络隔离

2-7 总结：服务器硬件对性能的影响

• CPU

  • 64位CPU一定要工作在64位的系统下

  • 对于并发比较高的场景 CPU 的数量比频率重要

  • 对于 CPU 密集性场景和复杂SQL则频率越高越好

• 内存

  • 选择主板能使用的最高频率的内存

  • 内存的大小对性能很重要，所以尽可能的大

• I/O子系统

  • PCIe->SSD->RAID10->磁盘->SAN

• 网络

2-8 操作系统对性能的影响-MySQL适合的操作系统

MySQL适合的操作系统

• Windows(大小写)

• FreeBSD(老版本对MySQL的支持不是很好)

• Solaris(原来只能在SUM公司下的机器运行，现在可以在X86下运行了)

• Linux

2-9 CentOS 系统参数优化

影响重大的a tiile 优化参数

• 内核相关参数(/etc/sysctl.conf)
  

增加连接数

+ net.core.somaxconn=65535

+ net.core.netdev_max_backlog=65535

+ net.ipv4.tcp_max_syn_backlog=65535

加快TCP回收效率

+ 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=16777216

+ net.core.rmem_default=87380

+ net.core.rmem_max=16777216

失效连接所占用TCP系统资源，加快系统回收的效率

+ net.ipv4.tcp_keepalive_time=120

+ net.ipv4.tcp_keepalive_intvl=30

+ net.ipv4.tcp_keepalive_probes=3

内存相关的参数

+ kernel.shmmax=4294967295

Linux 内核参数中最重要的参数之一，用于定义单个共享内存段的最大值。

> 注意：

    1. 这个参数应该设置的足够大，以便能在一个共享内存段下容纳下整个

    的Innodb缓冲池的大小。

    2. 这个值的大小对于64位linux系统，可取的最大值为物理内存值-1byte，

    建议值为大于物理内存的一半，一半取值大于Innodb 缓着冲池的大小即可，

    可以去物理内存-1byte。

+ vm.swappiness=0

这个参数当内存不足时会对性能参数比较明显的影响

Linux系统内存交换区:

    在Linux系统安装时都会有一个特殊的磁盘分区，称之为系统交换分区。

    使用 free -m 在系统中可以看到类似下面内容其中swap就是交换分区。

    当操作系统因为没有足够内存时就会将一些<font color="red">虚拟内存

    </font>写到<font color="red">磁盘的交换区</font>中这样就会发生

    内存交换

在MySQL服务器上是否要使用交换分区有一些争议:

    在MySQL服务所在的Linux系统上完全禁用交换分区。

    带来的风险：

      1. 降低操作系统的性能

      2. 容易造成内存溢出、崩溃，或都被操作系统Kill掉

结论：

    在MySQL服务器上保留交换区还是很必要的，但是要控制何时使用交换分区

vm.swappiness=0就是告诉Linux内核除非虚拟内存完全满了，否则不要使用交换区。

• 增加资源限制(/etc/security/limit.conf)

  这个文件实际上是Linux PAM 也就是插入式认证模块的配置文件。

  打开文件数的限制。

  * soft nofile 65535

  * hard nofile 65535

  加到limit.conf 文件末尾就可以了

  *      表示对所有用户有效

  soft    指的是当前系统生效的设置

  hard    表明系统中能设定的最大值

  nofile  表示所限制的资源是打开文件的最大数目

  65535  就是限制的数量

  结论：

    把可打开的文件数量增加到了65535个以保证可以打开足够多的文件句柄。

  注意：

    这个文件的修改需要重启系统才可以生效。

• 磁盘调度策略(/sys/block/devname/queue/scheduler)

cat /sys/block/sda/queue/scheduler

noop anticipatory deadline [cfq]

noop(电梯式调度策略)

    NOOP实现了一个FIFO队列，它像电梯的工作方法一样对I/O请求进行组织，当

    有一个新的请求到来时，它将强求合并最近的请求之后，以此来保证请求同一

    介质。NOOP倾向饿死读而利于写，因此NOOP对于闪存设备、RAM及嵌入式系统

    是最好的选择。

deadline(截止时间调度策略) 

    Deadline确保了再一个截止时间内服务请求，这个截止时间是可调整的，而

    默认读期限短语写期限。这样就防止了写操作因为不能被读取而饿死的现象，

    Deadline对数据库类应用是最好的选择。

anticipatory(预料I/O调度策略)

    本质上与Deadline一样，但在最后一次读操作后，要等6ms，才能继续进行对

    其它I/O请求进行调度。他会在每个6ms中插入行的I/O操作，而会将一些小写

    入流合并成一个大写入流，用写入延时换区最大的写入吞吐量。AS适合于*写

    入较多的环境*，比如文件服务器，AS对数据库环境表现很差。

2-10 文件系统对性能的影响

Windows

• FAT

• NTFS

Linux

• EXT3

• EXT4

• XFS(江湖传闻性能最高)

EXT3/4系统的挂载参数(/etc/fstab)

data=writeback | ordered | journal

noatime,nodiratime

/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

2-11 MySQL体系结构

MySQL 插件式的存储引擎

客户端

• PHP

• JAVA

• C API

• .Net

• ODBC

• JDBC

存储引擎层

• InnoDB

• MyISAM

• CSV

• Memory

• ARCHIVE

• xtraDB

• ...

2-12 MySQL常用的存储引擎之MyISAM

• MySQL5.5之前版本默认的存储引擎

• MyISAM存储引擎表由MYD和MYI组成

frm文件存的是数据表结构信息
MYD存的是数据信息
MYI存的是索引信息

特性

• 并发性与锁级别（表锁）（读写的话并发性支持不好，只读还行）

• 表损坏修复（因为不是修复事务，可能会造成数据丢失）

check table tablename;

repair table tablename;

• MyISAM表支持的索引类型

  • Btree + 全文索引

• MyISAM表支持数据压缩

  • 命令行：myisampack

限制

• 版本< MySQL5.0默认表大小为4G

• 如存储大表则要修改MAX_Rows 和 AVG_ROW_LENGTH

• 版本> MySQL5.0时默认支持为256TB

适用场景

• 非事务型应用

• 只读类应用

• 空间类应用

2-13 MySQL常用的存储引擎之InnoDB

• MySQL5.5.8及以后的版本默认存储引擎

• Innodb 使用表空间进行 数据存储

  
  innodb_file_per_table 参数
  
  ON:独立表空间：tablename .ibd
  
  OFF:系统表空间: ibdataX
  
  

  • 系统表和独立表空间要如何选择

  比较：
  
  系统表空间无法简单的收缩文件大小
  独立表空间可以通过optimeize table命令收缩系统文件
  
  系统表空间会产生IO瓶颈
  独立表空间可以同时向多个文件刷新数据

  建议：
  对InnoDB使用独立表空间

把原来存在于系统表空间中的表转移到独立表空间的方法

步骤:

1. 使用mysqldump导出所有数据库表数据

2. 停止MySQL服务，修改参数，并删除InnoDB相关文件

3. 重启MySQL服务，重建Innodb系统表空间

4. 重新导入数据

2-14 InnoDB存储引擎的特性（1）

系统表空间和独立表空间要如何选择

• Innodb 数据字典信息

• Undo 回滚段

Innodb存储引擎的特性

• Innodb是一种事务性存储引擎

• 完全支持事务的ACID特性（原子 一致 隔离 持久）

• Redo Log（持久性） 和 Undo Log

• Innodb支持行级锁

• 行级锁可以最大程度的支持并发

• 行级锁是由存储引擎层实现的

什么是锁

• 锁对主要作用是管理共享资源的并发访问

• 锁用于实现事务的隔离性

锁的类型

• 共享锁（也称读锁）

• 独占锁（也称写锁、排它锁）

锁的粒度

• 表级锁（并发支持不好）

• 行级锁（开销大）

2-15 InnoDB存储引擎的特性（2）

阻塞和死锁

• 什么是阻塞

• 什么是死锁

  • 两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象

Innodb状态检查

show engine innodb status

适用场景

• Innodb适合于大多数的OLTP应用

• 5.7版本之后支持全文索引和空间函数

2-16 MySQL常用存储引擎之CSV

文件系统存储特点

• 数据以文本方式存储在文件中

• .CSV文件存储表内容

• .CSM文件存储表的元数据如表状态和数据量

• .frm文件存储表结构信息

特点

• 以CSV格式进行数据存储

• 所有列必须都是不能为NULL

• 不支持索引，不适合大表，不适合在线处理

• 可以对数据文件直接编辑 保存文本文件内容

适用场景

• 适合做为数据交换的中间表

  • excel->CSV文件->MySQL数据目录

  • 数据->CSV文件->其他web程序

2-17 MySQL常用存储引擎之Archive

文件系统存储特点

• 以zlib对表数据进行压缩，磁盘I/O更少

• 数据存储在ARZ为后缀的文件中

特点

• 只支持insert和select操作

• 只允许在自增ID列上加索引

适用场景

• 日志和数据采集类应用

2-18 MySQL常用存储引擎之Memory

文件系统存储特点

• 也称为HEAP存储引擎，所以数据保存在内存中

• 重启后表数据丢失，表结构会保留

特点

• 支持HASH索引和BTree索引

  • 等值查找用HASH

  • 范围查找用BTree

• 所有字段都为固定长度

  • 计算使用varchar(10) 也等于 char(10)

• 不支持BLOG和TEXT等大字段

• Memory存储引擎使用表级锁

• 最大大小由max_heap_table_size参数决定

容易混淆的概念

Memory存储引擎表 VS 临时表

• 临时表

  • 系统使用的临时表

    • 超过限制使用myisam临时表

    • 未超限制使用Memory表

  • create temporary table 建立的临时表

适用场景

• 用于查找或者是映射表，例如邮编和地区的对应表

• 用于保存数据分析中产生的中间表

• 用于缓存周期性聚合数据的结果表

Memory数据易丢失，所以要求数据可再生

2-19 MySQL常用存储引擎之Federated（MySQL默认禁止，性能不好）

文件系统存储特点

特点

• 提供了访问远程MySQL服务器上表的方法

• 本地不存储数据，数据全部放到远程服务器上

• 本地需要保存表结构和远程服务器的连接信息

如何使用

• 默认禁止，启用需要在启动时增加federated参数

mysql://username[:password]@host_name[:port_num]/db_name/tbl_name

适用场景

• 偶尔的统计分析及手工查询

2-20 如何选择存储引擎

参考条件

• 事务

• 备份

• 崩溃恢复

• 存储引擎的特有特性

• 不要混合使用存储引擎

2-21 到 2-25 待续

2-26 数据库设计对性能的影响

什么影响了性能

• 过分的反范式化为表建立太多的列

• 过分的范式化造成太多的表关联（MYSQL最多关联61个表,最好在10个以内）

• 在OLTP环境中使用不恰当的分区表(分区表最好还是在OLAP环境中使用)

• 使用外键保证数据的完整性

2-27 总结

性能优化顺序

• 数据库结构设计和SQL语句

• 数据库存储引擎的选择和参数配置

• 系统选择及优化

• 硬件升级

第三章 MySQL 基准测试

3-1 什么是基准测试

• 测量系统性能

• 优化是否有效

定义:
基准测试是一种测量和评估软件性能指标的活动用于建立某个时刻的性能基准，以便当系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响

基准测试是针对系统设置的一种压力测试

• 基准测试

  直接、简单、易于比较 ，用于评估服务器的处理能力

• 压力测试

  对真实的业务数据进行测试，获得真实系统所能承受的压力

eg:

• 压力测试需要针对不同主题，所使用的数据和查询也是真实用到的

• 基准测试可能不关心业务逻辑，所使用的查询和业务的真实性可以和业务环境没关系

3-2 如何进行基准测试

基准测试的目的

• 建立MySQL服务器的性能基准线
  确定当前MySQL服务器运行情况

• 模拟比当前系统更高的负载，以找出系统的扩展瓶颈

• 测试不同的硬件、软件和操作系统配置

• 证明新的硬件设备是否配置正确

如何进行基准测试

1. 对整个系统进行基准测试

   从系统入口进行测试(如网站Web前端，手机APP前端)

   • 优点：

     • 能够测试整个系统的性能，包括web服务器缓存、数据库等

     • 能反映出系统中各个组件接口间的性能问题体现真实性能状况

   • 缺点：

     • 测试设计复杂，消耗时间长

2. 单独对MySQL进行基准测试

   • 优点：

     • 测试设计简单，所需要耗费时间短

   • 缺点：

     • 无法全面了解整个系统的性能基准线

MySQL基准测试的常见指标

• 单位时间内所处理的事务数（TPS）

• 单位时间内所处理的查询数（QPS）

• 响应时间

  平均响应时间、最小响应时间、最大响应时间、各时间所占百分比

• 并发量：同时处理的查询请求的数量

  并发量不等于连接数

3-3到3-6未完待续

第四章 MySQL数据库结构优化

4-1 数据库结构优化介绍

良好的数据库逻辑设计和物理设计是数据库获得高性能的基础

• 查询语句尽量简单

  • 反范式化设计 能加快一些语句的查询速度 也可能会影响一些其他的语句性能

数据库结构优化的目的

• 减少数据冗余（不代表没有冗余）

• 尽量避免数据维护中出现更新，插入和删除异常

  • 插入异常：如果表中的某个实体随着另一个实体而存在

  • 更新异常：如果更改表中的某个实体的单独属性时，需要对多行进行更新

  • 删除异常：如果删除表中某一实体则会导致其他实体的消失

• 节约数据存储空间

• 提高查询效率

4-2 数据库结构设计

数据库结构设计的步骤

1. 需求分析：全面了解产品设计的存储需求

   • 存储需求

   • 数据处理需求

   • 数据的安全性和完整性

2. 逻辑设计：设计数据的逻辑存储结构

   • 数据实体之间的逻辑关系，解决数据冗余和数据维护异常

3. 物理设计：根据所使用的数据库特点进行表结构设计

   • 关系型数据库：Oralce，SQLServer，MySQL，postgresSQL

   • 非关系型数据库： mongo,Redis,Hadoop

4. 维护优化：根据实际情况对索引、存储结构等进行优化

数据库设计范式

定义：设计出没有数据冗余和数据维护异常的数据库结构

数据库设计的第一范式

• 数据库表中的所有字段都只具有单一属性

• 单一属性的列是由基本的数据类型所构成的

• 设计出来的表都是简单的二维表

数据库设计的第二范式

• 要求一个表中只有具有一个业务主键

  也就是说符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系

数据库设计的第三范式

• 每一个非主属性既不部分依赖于也不传递依赖于业务主键

  也就是在第二范式的基础上消除了非主属性对主键的传递依赖

4-3 需求分析及逻辑设计

需求说明

按下面的需求设计一个电子商务网站的数据库结构

1. 本网站只销售图书类商品

2. 需要具有以下功能

   • 用户登录

   • 用户管理

   • 商品展示

   • 商品管理

   • 供应商管理

   • 在线销售

需求分析及逻辑设计

用户登录及用户管理功能

• 用户必须注册并登录系统才能进行网上交易
  
  用户名作为业务主键(视频上演示是如此)

• 同一时间一个用户只能在一个地方登录

• 用户属性：{用户名，密码，手机号，姓名，注册日期，在线状态，出生日期} 用户名为主键

  只有一个业务主键，一定是符合第二范式
  没有属性和业务主键存在传递依赖关系，符合第三范式

商品展示及商品管理功能

• 商品信息:{商品名称，分类名称，出版社名称，图书价格，图书描述，作者}

  商户名称和分类名称为主键 不满足第二范式

  • 商品信息：{商品名称，出版社名称，图书价格，图书描述，作者}

  • 分类信息：{分类名称，分类描述}

  • 商品分类（对应关系表）：{商品名称，分类名称}

供应商管理功能

• 供应商信息：{出版社名称，地址，电话，联系人，银行账号} 出版社信息PERMARY KEY

在线销售功能

• 在线销售：{订单编号，下单用户名，下单日期，订单金额，订单商品分类，订单商品名，订单商品单价，订单商品数量，支付金额，物流单号}

  1. 只有一个业务主键，符合第二范式

  2. 订单商品单价，订单商品数量，订单编号 存在着传递关系，不符合第三范式

  3. 数据冗余=>订单商品信息和订单信息表中的数据

• 拆分

  • 订单表：{订单编号，下单用户名，下单日期，支付金额，物流单号}

  • 订单商品关联表：{订单编号，订单商品分类，订单商品名，商品数量}

• 编写SQL查询出每一个用户的订单总金额

select 下单用户名,sum(d.商品价格*b.商品数量) from 订单表 a join 订单商品关联表 b on a.订单编号=b.订单编号 join 商品分类关联表 c on c.商品名称=b.商品名称 and c.分类名称=b.订单商品分类 join 商品信息表 d on d.商品名称=c.商品名称 group by 下单用户名

1. 关联表越多性能越差

2. 如果商品价格变动那会发生什么变化

假设下单用户就是商品的收货人，我们在发货前一定要查询出每个订单的下单人信息，而这些信息全部记录在用户信息表中
编写SQL查询出下单用户和订单详情

select a.订单编号, e.用户名, e.手机号, d.商品名称, c .商品数量, d.商品价格 join 商品分类关联表 c on c .商品名称= b.商品名称 join 商品信息表 d on d.商品名称= c.商品名称 join 用户信息表 e on e.用户名= a.下单用户名

完全符合范式化的设计有时并不能得到良好的SQL查询性能

4-4 需求分析及逻辑设计-反范式化设计

什么叫做反范式化设计

反范式化是针对范式化而言的，在前面介绍了数据库设计的范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对数据库设计范式的要求进行违反，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间

图书在校销售网站数据库的反范式化改造

• 原来的表：

  • 商品信息：{商品名称，出版社名称，图书价格，图书描述，作者}

  • 分类信息：{分类名称，分类描述}

  • 商品分类关系：{商品名称，分类名称}

• 改造后的表

  • 商品信息：{商品名称，分类名称，出版社名称，图书价格，图书描述，作者}

  • 分类信息：{分类名称，分类描述}

• 原来的表：

  • 订单表：{订单编号，下单用户名，下单日期，支付金额，物流单号}

  • 订单商品关联表：{订单编号，订单商品分类，订单商品名，商品数量}

• 改造后的表：

  • 订单表：{订单编号，下单用户名，手机号，下单日期，支付金额，物流单号，订单金额}

  • 订单商品关联表：{订单编号，订单商品分类，订单商品名，商品数量，商品单价}

反范式化改造后的查询

• 编写SQL查询出每一个用户的订单总金额

select 下单用户名,sum(订单金额) from 订单表 group by 下单用户名

• 编写SQL查询出下单用户和订单详情

select a.订单编号, a.用户名, a.手机号, b.商品名称, b.商品单价, b.商品数量 from 订单表 a join 订单商品关联表 b on a.订单编号=b.订单编号

总结

• 不能完全按照范式化的要求进行设计

• 考虑以后如何使用表

4-5 范式化设计与反范式化设计优缺点

范式化设计的优缺点

• 优点：

  • 可以尽量的减少数据冗余

    • 数据表更新快体积小

  • 范式化的更新操作比反范式化更快

  • 范式化的表通常比反范式化更小

• 缺点：

  • 对于查询需要对多个表进行关联

  • 更难进行索引优化

反范式化设计的优缺点

• 优点：

  • 可以减少表的关联

  • 可以更好的进行索引优化

• 缺点：

  • 存在数据冗余及数据维护异常

  • 对数据的修改需要更多的成本

4-6 物理设计介绍

物理设计设计的内容

• 定义数据库、表及字段的命名规范

• 选择合适的存储引擎

• 为表中的字段选择合适的数据类型

• 建立数据库结构

定义数据库、表及字段的命名规范

• 数据库、表及字段的命名要遵守可读性原则

• 数据库、表及字段的命名要遵守表意性原则

• 数据库、表及字段的命名要遵守长名原则

选择合适的存储引擎

4-7 物理设计-数据类型的选择

为表中的字段选择合适的数据类型

当一个列可以选择多种数据类型时，应该优先考虑素质类型，
其次是日期或二进制类型，最后是支付类型。
对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

如何选择正确的整数类型

如何选择正确的实数类型

如何选择VARCHAR和CHAR类型

VARCHAR类型的存储特点

定义的都是字符长度不是字节长度，UTF8为例，一个字符占3个字节;

• varchar用于存储边长字符串，只占必要的存储空间

• 列的最大长度小于255则只占用一个额外字节用于记录字符串长度

• 列的最大长度大于255这要占用两个额外字节用于记录字符串长度

• 正因为两个字节记录字符串长度，varchar最大长度为 65535，最大存储字符数量(defalut null 则 65535-2-1=65532，not null 则 65535-2=65533)

VARCHAR长度的选择问题

• 使用最小的符合需求的长度

• varchar(5)和varchar(200)存储 'MySQL' 字符串性能不同

VARCHAR的适用场景

• 字符串列的最大长度比平均长度大很多

• 字符串列很少被更新

• 使用了多字节字符集存储字符串

CHAR类型的存储特点

• CHAR类型是定长的

• 字符串存储在CHAR类型的列中会删除末尾的空格

• CHAR类型的最大宽度为255

CHAR的适用场景

• CHAR类型适合存储所长度近似的值

• CHAR类型适合存储短字符串

• CHAR类型适合存储经常更新的字符串列

4-8 物理设计-如何存储日期类型

DATETIME类型

以YYYY-MM-DD HH:MM:SS[.fraction] 格式存储日期时间

datetime = YYYY-MM-DD HH:MM:SS

datetime(6) = YYYY-MM-DD HH:MM:SS.fraction(微秒需要datetime(6))

DATETIME类型与时区无关，占用8个字节的存储空间

时间范围1000-01-01 00:00:00到9999-12-31 23:59:59

TIMESTAMP类型

存储了由格林尼治时间1970年1月1日到当前时间的秒数

以YYYY-MM-DD HH:MM:SS.[fraction]的格式显示，占用4个字节(timestamp(6))

时间范围1970-01-01到2038-01-19

• timestamp类型显示依赖于所指定的时区

• 在行的数据修改时可以自动修改timestamp列的值

DATE类型和TIME类型

DATE类型的有点:

1. 占用的字节数比使用字符串（8字节）、datetime（8字节）、int（4字节）存储要少，使用dete类型只需要3个字节

2. 使用Date类型还可以利用日期时间函数进行日期之间的计算

   • date类型用于保存1000-01-01到9999-12-31之间的日期

TIME类型

time类型用于存储时间数据，格式为HH:MM:SS

存储日期时间数据的注意事项

• 不要使用字符串类型来存储日期时间数据
  
  日期时间类型通常比字符串占用的存储空间小
  
  日期时间类型在进行查找过滤时可以利用日期来进行对比
  
  日期时间类型还有丰富的处理函数，可以方便的对时期类型进行日期计算

• 使用INT存储日期时间不如使用TIMESTAMP类型（这是视频作者说的，个人并不觉得）

4-9 物理设计-总结

物理设计

• 存储空间

• 存储引擎

• 数据类型

• 没有特殊要求推荐使用INNODB

• 为表中的每个列选择合适的类型

• 如何选择表的主键

  • 主键应该尽可能的小

  • 主键应该是顺序增长的
    

  增加数据的插入效率

  • Innodb的主键和业务主键可以不同（为了业务主键的唯一性，可以使用唯一索引）

• 数据库结构

  • 查询性能要求

  • 范式化要求

第五章 MySQL高可用架构设计

未完待续

第六章 数据库索引优化

6-1 Btree索引和Hash索引

• 太多或太少的索引都对数据库没有正面的影响

MySQL支持的索引类型

B-tree索引的特点（通常说的索引就是B-tree索引）

• B-tree索引以B+树的结构存储数据

• B-tree索引能够加快数据的查询速度

• B-tree索引更适合进行范围查找

在什么情况下可以用到B-tree索引

• 全值匹配的查询

  • order_sn='9876432119900'

• 匹配最左前缀的查询

  • 联合索引的最左侧的字段有用到就可以用到，索引 index(a,b,c), 当查询条件where a='xxx' 时就可以用到，顺序很重要 where a='xxx' and b='yyy'也可以用到，但 where b='xxx' and c='yyy' and a='zzz' 就用不了了

• 匹配列前缀查询

  • order_sn like '9876%'

• 匹配范围值的查询

  • order_sn>'9876432119900' and order_sn <'9876432119999'

• 精确匹配左前列并范围匹配另外一列

• 只访问索引的查询

B-tree索引的使用限制

• 如果不是按照索引最左列开始查找，则无法使用索引

• 使用索引时不能跳过索引中的列

• NOT IN 和 <> 操作无法使用索引

• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

Hash索引的特点

• Hash 索引时基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引。

• 对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。

Hash索引的限制

• Hash索引必须进行二次查找

• Hash索引无法用于排序

• Hash索引不支持部分索引查找也不支持范围查找

• Hash索引中Hash码的计算可能存在 Hash 冲突

为什么要使用索引

• 索引大大减少了存储引擎需要扫描的数据量

• 索引可以帮助我们进行排序以避免使用临时表

• 索引可以把随机I/O变为顺序I/O

索引是不是越多越好

• 索引会增加写操作的成本

• 太多索引会增加查询优化器的选择时间

6-2 安装演示数据库

略

6-3 索引优化策略（上）

• 索引列上不能使用表达式或函数

• 前缀索引和索引列的选择性

CREATE INDEX index_name ON table(col_name(n));

索引的选择性是不重复的索引值和表的记录数的比值

• 联合索引

  • 如何选择索引列的顺序

    • 经常会被使用到的列优先

    • 选择性高的列优先

    • 宽度小的列优先

• 覆盖索引

  • 优点：

    • 可以优化缓存，减少磁盘IO操作

    • 可以减少随机IO，变随机IO操作变为顺序IO操作

    • 可以避免对Innodb主键索引的二次查询

    • 可以避免MyISAM表进行系统调用

  • 无法使用覆盖索引的情况

    • 存储引擎不支持覆盖索引

    • 查询中使用了太多的列

    • 使用了双%%号的like查询

6-4 索引优化策略（中）

使用索引来优化查询

• 使用索引扫描来优化排序

  • 通过排序操作

  • 按照索引顺序扫描数据

  • 索引的列顺序和Order By子句的顺序完全一致

  • 索引中所有列的放行（升序，降序）和Order by子句完全一致

  • Order by中的字段全部在关联表中的第一张表中

• 模拟Hash索引优化查询

  • 只能处理键值的全值匹配查找

  • 所使用的Hash函数决定这索引键的大小

6-5 索引优化策略（下）

利用索引优化锁

• 索引可以减少锁定的行数

• 索引可以加快处理速度，同时也加快了锁的释放

索引的维护和优化

• 删除重复和冗余的索引

  • primary key(id),unique key(id),index(id) -> primary key(id)

  • index(a),index(a,b) -> index(a,b)

  • primary key(id),index(a,id) -> primary key(id),index(a)

• pt-duplicate-key-checker h=127.0.0.1 查询是否有冗余索引

• 查找未被使用过的索引

  • 查找未被使用过的索引

• 更新索引统计信息及减少索引碎片

  • analyze table table_name

  • optimize table table_name （会锁表要小心使用）

第七章 SQL查询优化

7-1获取有性能问题的SQL的三种方法

查询优化，索引优化，库表结构优化需要齐头并进。

• 通过用户反馈获取存在性能问题的SQL（比较被动）

• 通过慢查日志获取存在性能问题的SQL

• 实时获取存在性能问题的SQL

7-2 慢查询日志介绍

主要开销

• 磁盘IO

  • 顺序写入的，对大部分情况来说可以忽略不计

• 存储日志所需要的磁盘空间

  • 主要要考虑存储日志所需要的大量的磁盘空间

语句

• slow_query_log 启动停止记录慢查日志

  • ON为开启

  • set global

  • 可以通过脚本定时开关参数

• slow_query_log_file 制定慢查日志的存储路径及文件

  • 默认情况下保存在MySQL的数据目录中

  • 日志存储和数据存储分开存储

• long_query_time 制定记录慢查日志SQL执行时间的伐值

  • 默认值为10秒，最低到微秒，如果是100微秒 则要表达为 0.0001

  • 一般设置为0.001秒 ，一毫秒比较合适

• log_queries_not_using_indexes 是否记录未使用索引的SQL

MySQL 慢查询日志分析工具

• 常用的慢查日志分析工具(mysqldumpslow)

  • 汇总除查询条件外其他完全相同的SQL，

  • 并将分析结果按照参数中说指定的顺序输出。

• mysqldumpslow -s r -t 10 slow-mysql.log

• -s order (c,t,l,r,at,al,ar) 指定按哪种排序方式输出结果

  • c:总次数

  • t:总时间

  • l:锁的时间

  • r:总数据行

  • at,al,ar:t,l,r平均数

    • at = 总时间/总次数

    • al = 总时间/锁时间

    • ar = 总时间/数据行

• -t top 制定取前几条作为结束输出

7-3 慢查询日志实例

• 使用慢查询日志获取有性能问题的SQL

• 常用的慢查日志分析工具(pt-query-digest)

  • pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssW0rd slow-mysql.log

7-4 实时获取性能问题SQL

如何实时获取有性能问题的SQL

• information_schema数据库->PROCESSLIST表

  
  SELECT `id`,`user`,`host`,`DB`,`command`,`time`,`state`,`info` FROM information_schema.PROCESSLIST WHERE TIME>=60
  
  

7-5 SQL的解析预处理及生成执行计划

查询速度为什么会慢

MySQL服务器处理查询请求的整个过程

• 客户端发送SQL请求给服务器

• 服务器检查是否可以在查询缓存中命中该SQL

• 服务器端进行SQL解析，预处理，再由优化器生成对于的执行计划

• 根据执行计划，调用存储引擎API来查询数据

• 将结果返回给客户端

• 优先检查这个查询是否命中查询缓存中的数据。

• 通过一个对大小写敏感的哈希值查找实现的。

• Hash查找只能进行全值匹配。

• 用户权限

• 从查询缓存中直接返回结果并不容易

• 对于一个读写频繁的系统使用查询珲春很可能会降低查询处理的效率

• 所以在这种情况下建议大家不要使用查询缓存

  • query_cache_type 设置查询缓存是否可用

    • ON,OFF,DEMAND

    • DEMAND表示只有在查询语句中使用SQL_CACHE和SQL_NO_CACAHE来控制是否需要缓存

    • 建议设置OFF

  • query_cache_size 设置查询缓存的内存大小

    • 建议设置0

  • query_cache_limit 设置查询缓存可用存储的最大值

    • 加上SQL_NO_CACHE可用提高效率

  • query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据

  • query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

• MySQL依照这个执行计划和存储引擎进行交互

  • 这个阶段包括了多个子过程:

    解析SQL，预处理，优化SQL执行计划
    语法解析阶段是通过关键字对MySQL语句进行解析，并生产一棵对应的“解析树”
    MySQL解析器将使用MySQL语法规则验证和解析查询

    • 包括检查语法是否使用了正确的关键字

    • 关键字的顺序是否正确等

    • 预处理阶段是根据MySQL规则进一步检查解析树是否合法

    • 检查查询中所涉及的表和数据列是否存在及名字或别名 是否存在歧义等等

    • 语法检查全都通过了，查询优化器就可以生成查询计划了

会造成MySQL生成错误的执行计划的原因

• 统计信息不正确

• 执行计划中的成本估算不等同于实际的执行计划的成本。

  • MySQL服务器层并不知道哪些页面在内存中

  • 哪些页面在磁盘上

  • 哪些需要顺序读取

  • 哪些要页面要随机读取

• MySQL优化器锁认为的最优可能与你所认为的最优不一样

  • 基于其成本模型选择最优的执行计划并不是最快的执行计划

• MySQL从不考虑其他并发的查询，这可能会影响当前的查询速度

• MySQL有时候也会基于一些固定的规则来生成执行计划

• MySQL不会考虑不受其控制的成本

  • 存储过程

  • 用户自定义函数

MySQL优化器可优化的SQL类型

• 重新定义表的关联顺序

  • 优化器会根据统计信息来决定表的关联顺序

• 将外连接转化成内连接(优化器自动转化的)

• 使用等价变换规则

• 优化count(),min()和max()

• 将一个表达式转化为常数表达式

• 使用等价变换规则

• 子查询优化

  • 转化为关联查询

• 提前终止查询

• 对in条件进行优化

7-6 如何确定查询处理各个阶段所消耗的时间

使用profile

• set profiling = 1;

  • 启动profile

  • 这是一个session级的配置

• 执行查询

• show profiles;

  • 查看每一个查询所消耗的总时间的信息

• show profile for query N;

  • 查询的每个阶段所消耗的时间

使用performance_schema

UPDATE `setup_instruments` SET enabled='YES',TIMED='YES' WHERE NAME LIKE 'stage%';

UPDATE `setup_consumers` SET enabled='YES' WHRER NAME LIKE 'events%';

7-7 特定SQL的查询优化

如何进行大表的数据修改

大表的数据修改最好要分批处理
1000万行记录的表中删除/更新100万行记录
一次只删除/更新5000行记录
暂停几秒

大表的更新和删除

如何修改大表的结构

• 对表中的列字段类型进行修改还是会锁表

• 改变字段的宽度时还是会锁表

• 无法解决主从数据库延迟的问题

先修改从服务器，然后手动切换主从，在修改主服务器，再切回来，这种方法需要手动，有风险


主服务器建新表，将老表加触发器数据同步到新表，然后老表加排它锁，然后新表重新命名，删除老表

如何优化not in和<>查询

• 需要优化的SQL

SELECT cutsomer_id,first_name,last_name,email FROM customer WHERE customer_id NOT IN(SELECT customer_id FROM payment)

• 优化后的SQL

SELECT a.customer_id,a.first_name,a.last_name,a.email FROM customer a LEFT JOIN payment b ON a.customer_id=b.customer_id WHERE b.customer_id IS NULL;

使用汇总表优化查询

SELECT COUNT(*) FROM product_comment WHERE product_id=999

汇总表就是提前以要统计的数据进行汇总并记录到表中以备后续的查询使用

• 优化后

CREATE TABLE product_comment_cnt(product_id INT,cnt INT);

SELECT SUM(cnt) FROM(SELECT cnt FROM product_comment_cnt WHERE product_id=999 UNION ALL SELECT COUNT(*) FROM product_comment WHERE product_id=999 AND timestr>DATE(NOW())) a

第八章 数据库的分库分表

8-1 数据库分库分表的几种方式

1. 把一个实例中的多个数据库拆分到不同的实例

2. 把一个库中的表分离到不同的数据库中

3. 对一个库中的相关表进行水平拆分到不同实例的数据库中

4. 垂直拆分

8-2 数据库分片前的准备

对一个库中的相关表进行水平拆分到不同实例的数据库中（不得不的时候才分片）

• 如何选择分区键

  • 分区键要能尽量避免跨分片查询的发生

  • 分区键要能尽量使各个分片中的数据平均

• 如何存储无需分片的表

  • 每个分片中存储一份相同的数据

  • 使用额外的节点统一存储

• 如何在节点上部署分片

  • 每个分片使用单一数据库，并且数据库名也相同

  • 将多个分片表存储在一个数据库中，并在表名上加入分片号后缀

  • 在一个节点中部署多个数据库，每个数据库包含一个分片

• 如何分配分片中的数据

  • 按分区键的Hash值取模来分配分片数据

  • 按分区键的范围来分配分片数据

  • 利用分区键和分片的映射表来分配分片数据

• 如何生成全局唯一ID

  • 使用auto_increment_increment和auto_increment_offset参数

  • 使用全局节点来生成ID

  • 在Redis等缓存服务器中创建全局ID

未完待续

本文是学习笔记，如有错误，劳请各位大佬指正