影响数据库性能的因素：

  • 服务器硬件
  • 数据库配置参数（影响较大）
  • 服务器所在的操作系统
  • 数据库的存储引擎
  • 数据库结构设计和sql语句
  • 网卡流量

QPS和TPS概念：

  • QPS是指每秒查询率QPS是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准
    -TPS是指 一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程，等于服务器最终响应时间减去请求时间的中间时长。

超高的tps带来的问题：

超高的tps的风险是执行sql效率低下。

大量的并发和超高的cpu使用率带来的问题：

大量的并发容易导致数据库的连接数被占满，其中mysql最大连接数为max-conntections 默认数为100.
超高的cpu使用率容易导致服务器的cpu资源耗尽而导致数据库服务器宕机。

磁盘IO

磁盘io性能突然下降时，应使用更快的磁盘设备，并做好计划的调整和磁盘维护工作。

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

  • 减少从服务的数量，当一个master拥有越多则消耗的流量越大。
  • 进行分级缓存，较少对磁盘的操作数。
  • 避免使用select * from tableName的语句进行查询，加剧磁盘io。
  • 分离业务网络和服务网络。

大表的定义：

大表定义从两个维度考虑：
1 ： 表中记录行数数量巨大，在mysql中一般而言单表行记录数不应该超过千万行。
2 ： 单表数据文件巨大，表数据文件不应该超过10G。

大表带来的问题：

  • 查询较慢。很难在一定时间里过滤符合的数据。
  • 对DDL操作的影响较大。建立索引需要较长的时间，在mysql5.5以前建索引会导致表锁。虽然在5.5以后不会导致锁表但是会导致主从数据库服务器延时。
  • 修改表结构需要较长的时间锁表，导致主从延时。
  • 影响正常的数据操（容易造成阻塞，导致连接数增加）

如何处理大表带来的问题

  • 进行分库分表，分库分表应该注意分区键的选择和避免分库分表后数据查询和统计进行跨分区查询。应该避免对现有业务的影响。
  • 对表中数据很少使用的数据进行历史归档，归档应该选择好数据归档条件。

大事务：

大事务是指：运行时间较长的，操作的数据比较多的事务。
影响：
1：当锁定太多数据时，容易造成大量的阻塞和锁超时。（在mysql中innodb默认是行锁，但是为了数据的一致性要求会去锁定更多的数据）。
2 : 当一个事务提交失败后回滚所需的时间较长。
3：sql执行的时间较长，容易导致主从延时。

如何处理大事务：

  • 避免一次操作太多数据导致锁定太多的数据（cud）应适当的分批次进行操作当数据量大的时候。
  • 在事务中尽量放在修改操作，把不必要的select移除。

数据库设计的流程(初期)：

充分的了解产品设计的存储需求。如存储需求和数据处理需求及数据的安全性和完整性。

mysql常用的存储引擎

存储名称

是否支持事务

锁粒度

适合的运用场景

不合适运用的场景

MyIsam

不支持

支持并发插入的表级锁

select /insert

读写操作频繁的场景

MRG_MyIsam

不支持

支持并发插入的表级锁

数据归档/数据仓库

全局查找过多的情况

Innodb

支持

支持MVVC的行级锁

事务处理

无

Archive

不支持

行级锁

日志记录只支持insert和select

需要随机读取或者更新的场景

NDB Cluster

支持

行级锁

高可用

需要随机读取或者更新的场景

如何选择字段类型

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

1. mysql整数类型

列类型

存储空间

有符号范围

无符号范围

tinyint

1 byte

-128，127

0，255

SMALLINT

2 byte

-32 768，32 767

0，65 535

MEDIUMINT

3 byte

-8 388 608，8 388 607

0，16 777 215

INT

4 byte

-2 147 483 648，2 147 483 647

0，4 294 967 295

BIGINT

8 byte

-9,223,372,036,854,775,808，9 223 372 036 854 775 807

0，18 446 744 073 709 551 615

2 浮点型

列类型

存储空间

是否是双精度类型

float

4 byte

否

double

8 byte

否

decimal

每4byte存9数字，小数点占一个字节

是

3 字符串类型
char 和varchar
varchar类型的存储特点：

  • 用于存储变长的字符串，只占用必要的存储空间。
  • 列的最大长度小于255则占用一个额外的字节用于记录字符串的长度。
  • 列的最大长度大于255时要占用额外的两个字节用于记录字符串的长度。

varchar 长度的选择问题

  • 使用最小符合需求的长度，mysql在内存中使用了固定长度，过长浪费内存。

varchar使用场景：

  • 字符串的最大长度比平均长度大很多。（变长）
  • 字符串很少被更新，经常更新容易导致内存存储的碎片化。
  • 使用多字节字符集存储字符串。

char类型的特点：

  • char类型是定长的。
  • 字符串存储在插入类型的列中会删除尾部的空格。
  • 最大的长度为255byte

适用的场景：

  • 存储的字符串长度接近所设的值。
  • 存储较短的字符串
  • 使用使用在经常被被修改的字符的列上（mysql会一次性的分配内存空间）

4 时间类型：

  • datetime 已yyyy-MM-DD HH:mm:ss [fraction]的格式存储日期时间与时区无关，占用8Byte 范围为1000-9999
  • timestamp : 存储了由格林尼治时间从1970年1月1日到当前的时间的秒数。已YYYY-MM-DD HH:mm:ss 【fraction】的格式显示，占四个byte,范围为1970-01-01到2038-01-01。显示时间需要依赖指定时区。在行次改的数据修改时可以自动修改timestamp列的值（自动修改）
  • date类型 ：
    优点：找用的字节数比使用的字符串，datetime，int存储要少，使用date类型只占3byte.
  • time :用于时间数据，格式为：HH:mm:ss

mysql 时间类型的选择注意事项：

  • 不要使用字符串类型来存储日期时间类型。
  • 日期时间类型通常比字符串占用的存储空间小。
  • 日期时间类型在进行查找过滤是可以利用日期来进行对比。
  • 日期时间类型拥有丰富的处理函数，可以方便进行日期时间的各种操作处理。
  • 使用int存储时间不如使用timestamp类型进行存储。