本文参考了《高性能MySQL》(第三版),为学习笔记

一、并发控制

触发时机:多个查询需要在同一时刻修改数据,就会产生并发控制的问题。

1.读写锁

提出问题:多个用户读取邮箱数据没有问题,但是当其中一个用户要删除某一个邮箱时,可能会导致其他用户报错退出或者读到不一致的邮箱数据。

解决方法:上述是经典的并发问题,可以通过锁的方式来解决。

读锁(共享锁):读锁是共享的,即互不阻塞,多个用户可以同一时刻读取同一资源

写锁(排他锁):一个读锁会阻塞其他的写锁和读锁。

2.锁粒度

定义:为什么会有锁粒度这个概念呢?因为提高共享资源并发性的方式就是让锁定对象更有选择性,尽量只锁定需要修改的部分数据,而不是所有资源。锁粒度表示被封锁的对象的粒度,例如数据项、记录、文件或整个数据库,锁粒度越小事务的并行度越高。也叫锁策略,因为加锁也会产生各种资源开销,所以要在锁的开销的数据的安全性之间寻求一种平衡。

表锁

表锁是开销最小的策略,顾名思义锁定整张表。可以在一个用户修改数据时阻塞其他用户对该表的读写操作。

行级锁

行级锁可以最大程度支持并发处理,同时也带来了最大的锁开销。

二、事务

事务相关知识请看这篇文章:事务隔离

三、nnoDB存储引擎

InnoDB的数据存储在表空间中,还可以将每个表的数据和索引存放在单独的文件中。

InnoDB采用MVCC来支持高并发,默认隔离级别是可重复读,并且通过间隙锁策略防止幻读的出现,间隙锁使得InnoDB不仅仅锁定查询涉及的行,还会对索引中的间隙进行锁定,以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的(主键索引),其二级索引(非主键索引)中必须包含主键列。

四、MySQL基准测试

1.为什么要基准测试

  1. 观察系统在不同压力下的行为
  2. 重现系统中的异常以解决异常
  3. 测试系统当前的运行情况,诊断一些无法预测的问题
  4. 测试应用适应可变环境的能力

2.基准测试的策略

一种是对整个系统的整体测试:因为有时候MySQL并非总是应用的瓶颈,只有整体测试才能发现各部分之间的缓存带来的影响。

另一种是单独测试MySQL:有时候不需要了解整个应用的情况,只需关注MySQL的性能。

3.基准测试的指标

  1. 吞吐量:单位时间内的事务处理数
  2. 响应时间或者延迟:任务所需的整体时间
  3. 并发性:任意时间有多少同时发生的并发请求
  4. 可扩展性:应对业务压力可能发生变化的情况

4.基准测试方法

  1. 设计和规划:提出问题,明确目标,采用标准还是专用基准测试
  2. 多长时间:原则上越长时间越好,因为达到长期稳定状态可能要很长时间
  3. 获取系统性能和状态:尽可能多的收集被测试系统的信息
  4. 获取准确的测试结果:不断修改参数以达到准确结果
  5. 运行基准测试并分析结果:一般是自动化基准测试
  6. 根据结果绘图

5.基准测试工具

集成式测试工具

单组件式测试工具

五、数据类型

1.存储数据类型的选择原则

  1. 越小越好:占用更少cpu和磁盘
  2. 简单为主:整形比字符操作代价更低,用MySQL内建的类型
  3. 避免NULL:因为可为NULL的列会使得索引,索引统计和值比较都更复杂,占用更多的空间,当可为NULL的列被索引时,每个索引记录需要一个额外的字节

2.整数类型

类型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

位数:8、16、24、32、64

范围:-2^(n-1)2^(n-1)-1,n表示位数,加UNSIGNED为正数,范围变成02^n-1

宽度:例如 INT(11),这里11表示的是宽度而不是长度,不会限制存储范围,设置的字符宽度只对数值长度不满足宽度时有效,比如插入的数据为123,宽度小于11,所以在客户端显示时会显示为00000000123,当宽度超过11后,这个显示宽度就不起作用了,即不需要在前面加0了。所以INT(11)和INT(5)可以存储的数据范围是一样的,只是限制的最低显示宽度有所不同。

3.实数类型

实数是带有小数部分的数字。MySQL有3种实数类型:FLOAT(表示单精度浮点数值)、DOUBLE(表示双精度浮点数值)、DECIMAL(定点型),三者都可以用:类型(m, n),比如DECIMAL(18, 9),总长度为18,小数点后(小数)占9位,小数点前(整数)占9位,又因为每4个字节存9位数字,则一共会使用9个字节,整数和小数各占4字节,注意小数点也会占1字节。

DOUBLE和FLOAT等浮点类型在存储同样范围的值时,通常比DECIMAL使用更少的空间,且DECIMAL有更高的精度和更大的范围,所以在对小数进行精确计算时采用DECIMAL——例如存储财务数据,DOUBLE和FLOAT容易产生精度缺失。

4.字符串类型

VARCHAR:存储可变长字符串,比CHAR定长更节省空间,仅使用必要的空间。VARCHAR需要使用1或2个额外字节用来记录字符串的长度:如果列的最大长度小于等于255字节,则额外1字节记录,否则用2字节记录,如VARCHAR(10)需要11字节,VARCHAR(1000)需要1002字节。适合使用VARCHAR的场景:要存储的字符串列的最大长度比存储的平均长度大很多时适合用,因为这可以充分利用扩容而来的内存,不会产生过多内存碎片。

CHAR:存储定长字符串,适合存储很短或者长度接近的字符串。对于经常变动的数据,CHAR也比VARCHAR在存储空间上更有效率,因为定长的CHAR类型不易产生内存碎片。

BLOB和TEXT:都是为存储很大的数据而设计的字符串数据类型,BLOB采用二进制方式存储,没有排除规则或字符集,TEXT采用字符方式存储,有字符集和排序规则。

ENUM:枚举类型,有时候可以使用枚举类型代替常用的字符串类型,可以把一些不重复的字符串存储成一个预定义的集合,MySQL存储枚举时很紧凑,会根据列表值的数量压缩到一个或者两个字节中。枚举最不好的地方是添加或删除字符串必须使用AlTER TABLE。由于每个枚举值都保存为整数,必须进行查找才能转换为字符串,所以枚举列有一些开销。enum在底层的存储方式是以整型进行存储的,比如这样的字段sex enum(‘male’ , ‘female’ , ‘both’ , ‘unknow’)在查询时where sex=’male’和where sex=1是等效的

5.日期和时间类型

DATETIME:这个类型能保存大范围的值,从1001年到9999年,精度为秒,格式为YYYYMMDDHHMMSS,和时区无关,占8字节的存储空间,格式显示如:2008-01-16 22:37:08,原样读入,原样取出。

TIMESTAMP:存储范围’1970-01-01 00:00:01’ to ‘2038-01-19 03:14:07’,范围比DATETIME小得多,精度精确到小数点后六位,占用4字节存储空间。MySQL提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期,并提供了UNIX_TIMESTAMP()函数把日期转换为Unix的时间戳。TIMESTAMP存储时依赖于时区,且以时间戳的方式存储,即INT类型的方式存储,在显示时会自动以系统当前时间显示。

除了特殊情况外,一般都用TIMESTAMP类型存储,因为空间效率更高。用INT类型存储并不比TIMESTAMP更好,都是4字节,都是INT型存储。

6.位数据类型

BIT:BIT(1)定义一个包含一位的字段,BIT(2)定义包含2个位的字段,BIT位的最大长度是64位。可以使用BIT列在一列中存储一个或多个true/false值,MySQL把BIT当做字符串类型,而不是数字类型,即BIT里面存储的0或1是字符串类型。例如:存储’00111001’到BIT(8)的列并且索引时,得到的内容是其ASCII字符码为57的字符串”9”,但是在数字上下文场景时,得到的是数字57。

SET:集合类型,如果需要存储很多true/false值,可以存储到SET数据类型,是一系列打包的位的集合表示的,能有效利用存储空间。

7.选择标识符(identifier)

8.特殊类型数据

例如:低于秒级精度的时间戳,IPv4地址

六、范式和反范式

  • 第一范式

    数据表的每一列都要保持它的原子特性,不能再被分割

  • 第二范式

    在第一范式基础上,保证一张表只讲一件事情,即属性必须完全依赖于主键

  • 第三范式

    在第二范式基础上,所有的非主属性不依赖于其它的非主属性,即应该要依赖于主键

1.范式的优点和缺点

范式化的优点:

  1. 范式化的更新操作通常比反范式化要更快
  2. 当数据较好的范式化时,就只有很少或没有重复的数据素以只需要修改更少的数据
  3. 范式化的表通常更小,可以更好的放在内存里,所以执行操作会更快
  4. 很少有多余的数据意味着检索列表数据时,更少的用到DISTINCT或者GROUP BY语句

范式化的缺点:

  1. 范式化设计的表结构通常需要互相关联,关联的代价昂贵,而且可能会使一些索引策略无效,这些列如果在一个表中本可以属于同一索引

2.反范式化的优点和缺点

反范式化的优点:

  1. 可以很好地避免表关联,因为所有数据都在一张表中,能使用更有效的索引策略

反范式化的缺点:

  1. 所有数据都在一张表,太过冗余

3.混用范式化和反范式化

完全的范式化和反范式化都是实验室才有的东西,现实世界很少这么极端的使用,经常要混用

七、创建高性能的索引

这一章可以看我总结的另一篇文章:MySQL 实战 45 讲学习笔记 2

八、查询性能优化

这一章可以看我总结的另一篇文章:MySQL查询性能优化