高性能 MySql —— 学习笔记

高性能 MySql —— 学习笔记

读《高性能 MYSQL》笔记,仅用回顾复习作用,不能具备参考价值。

Table Of Contens

第一章 架构与历史

Mysql 逻辑架构:

mysql服务器的逻辑架构图

  • 第一层是连接处理,并非是 Mysql 独有,大多数应用都有类似的架构。比如连接、授权、安全等。

  • 第二层是 Mysql 核心功能层,包括解析、分析、优化、缓存以及内置函数(日期、时间、数学、加密)等。所有跨存储引擎的功能也在这一层实现如:触发器、视图、存储过程等。

  • 第三层包含存储引擎,负责 Mysql 中数据的存储与提取。每一种存储引擎都有各自的优势与劣势,mysql 服务器通过 API 与存储引擎通信,这些 API 屏蔽了不同存储引擎之间的差异,使得这些差异对上层的查询透明。存储引擎不会去解析 SQL,不同存储引擎之间也不会互相通信,只是响应上层 mysql 服务器的请求。

并发控制

只要有多个查询在同一时刻操作数据,就有会有并发问题。Mysql 可以从服务层存储引擎层控制并发。

读写锁

读写锁也称为共享锁和排它锁,读锁是共享的(互不阻塞的),多个连接可以同时读取同一份资源而不产生并发问题;写锁是排它的(一个写锁会阻塞其他写锁和读锁),这有这样才会保证给定时间内只有一个用户可以执行操作,并防止其他用户读取正在写入的数据。

锁粒度

加锁操作(如:加锁、检查锁是否释放、释放锁等)都是会消耗系统资源的,如果花费大量的时间来管理锁而不是存储数据,那么系统性能也会受到影响。

因此一种提高并发性能的方案是让锁定对象更有选择性,尽量锁定部分数据而不是全部资源。锁定的资源越少,并发控制的性能越高。

锁策略是指在锁的开销和数据安全之间寻求平衡。Mysql 提供多种选择,每一个存储引擎都实现了自己的锁策略和锁粒度。

表锁
Mysql 最基本的锁策略,也是锁开销最小的策略,对数据的写操作(插入、更新、删除)会锁定整张表。当拥有一个写锁时,将阻塞其他连接对数据的读写操作,直至写锁释放。读锁是互不阻塞的。

行锁
行锁可以最大程序的支持并发,同时也带来了锁的开销。行级锁只在存储引擎中实现,mysql 服务器层没有实现,服务器层不了解存储引擎的锁实现。

事务

事务是一组原子性的 SQL 查询,是一个独立的工作单元。事务中的 SQL 语句要么全部成功,要么全部失败。

一个良好的事务处理系统必须具备 ACID 标准,不基于 ACID 标准谈事务的概念是没有意义的。

ACID 即:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。

「原子性」

一个事务被视为一个独立的工作单元,事务中的操作要么提交成功,要么全部失败回滚,不可能只执行其中一部分操作。

「一致性」

数据库总是从一个状态到另一个状态的变化。若事务提交成功,A 状态到了 B 状态;若事务执行失败,回到 A 状态;不存在第三种状态 C。

「隔离型」

通常来说,一个事务所做的修改在提交成功之前,对其他事务是不可见的。隔离性是四个性质中最复杂的一个,其中细分了许多 「隔离级别」

「持久性」

一旦事务提交了,所做的修改将永久保存到数据库中,即使系统崩溃,修改的数据也不会丢失。当然这是理想的情况,很多时候不能够保证 100% 的持久性。

隔离级别

SQL 标准定义了四种隔离级别,每一种级别中都规定了一个事务所做的修改,哪些在事务内和事务见是可见的,哪些是不可见的。较低的隔离通常可以执行更高 的并发,系统开销也会更低。

「READ UNCOMMIT 未提交读」
事务中的修改,即使没有提交,对其他事务也是可见的。这也叫做脏读(drity read),这个级别会导致很多问题,很少使用这个级别。并且从性能上来说,read uncommit 不会比其他级别高出多少,但是缺少了其他级别的很多优势。

「READ COMMIT 提交读」
大多数数据库隔离级别都是 read commit,但 Mysql 不是。

read commit 满足事务隔离性的基本定义,一个事务开始时只能看到已经提交的事务所做的修改。

read commit 也叫做「不可重复读」,两次执行相同的查询,可能得到不同的结果。

「REPEATABLE READ 可重复读」
Mysql 的默认隔离级别。

该级别解决的脏读并且支持可重复读(同个事务中多次读取同样的记录的结果是一致的)。不过无法解决「幻读」问题,即当一个事务读取某个范围的记录时,另一个事务插入了新的记录,该事务再次读取会出现幻行。

不过 InnDB 存储引擎通过多版本并发控制(MVVC)解决了幻读。

「SERIALIZABLE 可串行化」
最高的隔离级别,强制事务串行执行。serializable 会给每一行数据加锁,所以可能导致大量超时和锁占用。除非非常需要确保数据一致性并且不需要并发情况,否则不考虑使用该级别。

各个隔离级别比较:

隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
READ UNCOMMIT yes yes yes no
READ COMMIT no yes yes no
PEREATABLE READ no no yes no
SERIALIZABLE no no no yes

第四章 Schema 与 数据类型优化

选择数据类型的几种原则:

1)更小的通常更好
更小的数据类型速度越快,占用更少的磁盘、内存和 CPU 缓存。但最好确保数据类型将不会扩充,因为扩大数据类型的范围是十分耗时和消耗资源的操作。

2)简单就好
简单的数据类型通常需要更少的 CPU 周期。整型比字符型的操作代价要低,这是由于字符型的校对和排序规则比整型更复杂导致的。

能用 Mysql 内置的时间类型就不用字符串,用整型存储 IP 地址不要用字符串。

3)避免使用 NULL
可为 NULL 的列会使得索引、索引统计、比较变得复杂,Mysql 需要更多的空间对 NUll 列做特殊处理。

NULL 列不要用作索引。

整数类型

数字类型分为整数和实数,整数的几种 MYSQL 类型如下:

类型 占用位数 字节
TINYINT 8 1
SMALLINT 16 2
MEDIUMINT 24 3
INT 32 4
BIGINT 64 8

整数类型分为有符号和无符号(UNSIGNED),无符号表示不支持负数。使用无符号大约可以使存储空间提高一倍。

有符号和无符号使用相同的存储空间,具有相同的性能,可以根据实际情况选择。

Mysql 中可以给整数指定宽度,如 INT(11),但对大多数应用来说是没有意义的。它没有限制数的存储范围,而只是规定在一些 MYSQL 客户端工具中显示的字符的宽度。对于存储和计算来收,INT(1) 和 INT(11) 是没有区别的。

实数类型

实数是带有小数的数字。MYSQL 中支持的类型有 「浮点类型(float / double)」「Decimal 类型」

类型 字节数 位数
float 4 32
double 8 64
decimal 16 128
  • 浮点类型是不精确类型,不支持精确计算,存储同样范围的值占用空间较 decimal 少;

  • decimal 类型是精确类型,可存储精确小数,支持精确计算(Mysql 5.0 开始),但计算代价较高。

decimal(a,b)

  • a 指定小数点左边和右边可以存储的十进制数字的最大个数,最大精度38。
  • b 指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a 之间的值。默认小数位数是 0。

建议:

1. 只指定数值类型,不指定精度。

2. 尽量在只对小数计算时才使用 Decimal,数据量大情况下最好是使用 BIGINT 代替 DECIMAL。 小数按倍数相乘之后存入数据库,取出时按倍数相除即可。

字符串类型

VARCHAR & CHAR
varchar 比 char 更节省空间,一定程度上也提高了性能,因为仅使用必要的空间。但由于行是变长的,做更新操作可能时会比 vhar 多做一些额外操作。

char 是变长的,根据定义的字符串的长度分配固定的空间,当存储的值少于固定长度时以空格填充。char 类型适合用于存储长度短、值的长度固定的如 MD5 加密后的密码,因为是定长的,不易产生碎片,特定类型的数值存储效率比 varchar 要高。

BINARY & VARBINARY
binary 和 varbinary 与 char 和 varchar 类似,只不过 binary、varbinary 存储的是二进制字符串。二进制字符串与普通字符串类似,不过存储的是字节码而不是字符。

BLOB & TEXT
存储大数据量的字符串类型,分别存储二进制大字符串和普通文本大字符串。

  • BLOB 类型有:TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、BIGBLOB。
  • TEXT 类型有:TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、BIGTEXT。

时间类型

DATETIME
能保存较大范围的值,从 1001 年到 9999 年,精度为秒。与时区无关,使用 8 个字节存储。

TIMESTAMP
该类型保存了从 1970 年 1 月 1 日午夜以来的描述,表示的范围为 1970 年到 2038 年。只占用 4 个字节。与时区相关。

除了特殊情况之外,尽量使用 TIMESTAMP,因为它比 DATETIME 空间利用效率更高。

位类型

Mysql 5.0 之前,BIT 与 TINYINT 没有区别,MYSQL 5.0 之后这两者是不同的数据类型。

BIT 最大支持 64 个位,Mysql 将 BIT 视为字符串类型,当检索 bit(1) 时,返回的是二进制 的 0 或 1 的字符串,而不是数字。

注意:在数字上下文中检索时,会将字符串转为数字。如:存储值为 b’00111001’(二进制等于57)的列检索时会得到 57 的字符串,然后根据 57 在 ASCII 码中的位置得出数字 9。这通常不是我们需要的。因此应尽量避免使用这个类型。

特殊类型

如 IP 地址,应该用无符号整数来存储。IP 之间的小数点只是为了让人们利于区分,MYSQL 提供 INET_ATON()INET_NTOA() 函数在这两者之间转换。

Schema 设计的陷阱

  1. 避免设计太多的列
  2. 避免滥用枚举
  3. 避免太多的关联
  4. 尽量避免设计可为 NULL 的列,但有时必须这样做。

范式与反范式

范式的优点:

  • 范式化的更新操作通常比反范式化要快。
  • 范式化很少有重复数据。
  • 范式化的表通常更小,可以更好的放在内存里,执行会更快。
  • 有很少的数据意味着检索列时需要更少的 DISTINCT 或者 GROUP BY 语句。

范式的缺点:
通常需要关联,代价昂贵 ,可能使得索引策略无效。

最佳的设计是混合范式化与反范式化

第五章 创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构,设计良好的索引比不好的索引可能要好几个量级。在 Mysql 中,存储引擎先在索引中搜索对应的值,再根据匹配的索引记录找到对应的数据行。

索引可以包含一个或多个列,如果包含多个列,那么列的顺序十分重要,因为 Mysql 只能高效的使用索引最左前缀的列。创建一个包含多列的索引和创建多个只包含一个列的索引是大不相同的。

索引的类型

索引有很多类型,不同的类型可以为不同的场景提供更好的性能。在 Mysql 中,索引是存储在存储引擎层而不是在服务层实现的,所以没有统一的索引标准。

B-Tree 索引

B-TREE 索引是大多数 MYSQL 存储引擎使用的索引类型,InnoDB 使用就是 B-TREE。这类索引适用于「全键值」「键值范围」「键前缀」查找,键前缀查找只适用于根据最左前缀查找。B-Tree 是最常见的索引,支持按顺序存储数据,所以可以用来做 GROUP BY 和 ORDER BY 操作。以下查询类型使用 B-Tree 会十分有效:

1)全值匹配
利用索引中所有的列进行匹配的查询。

2)匹配最左前缀
对于多个列组成的索引,匹配最左列的查询使用 b-tree 效率很高。

3)匹配列前缀
匹配某个列的值的开头部分,如 first_name 是最左列,查找姓从 J 开头的人的查询会走索引。这里的匹配列前缀是在最左前缀前提下的。

4)精确匹配某一列并范围匹配另外一列
这些必须都是索引列的一部分。

5)只访问索引的查询
查询只访问索引,不访问其他数据行。

因为索引数的节点都是有序的,所以除了按值查找之外,还可以用来查询之中的 ORDER BY 操作。如果 ORDER BY 字句满足上述查询要求,那么索引也可以满足对应的排序需求。

B-Tree 的限制:

  • 不是按最左列的查询不会走索引。
  • 不是列前缀的查询也不会走索引,如查找名字以 J 结尾的用户。
  • 不能跳过索引中的列查询。即如果查询条件中只包含索引的第一列和最后一列,那么索引只会利用第一列。
  • 如果查询中有某个列的范围查找,则右边的所有列都无法利用索引优化查找。

B-Tree 索引中,索引列的顺序对索引的查询效率十分重要,有时需要用相同的列但不同的顺序建立索引来满足不同的查询要求。

哈希索引

基于哈希表实现,只有精确匹配所有列的查询才会有效。对于每一行数据,存储引擎都会对其中所有的索引列计算一个哈希码。哈希索引将所有的哈希吗存储在索引中,同时在哈希表中保存指向每个数据行的指针。

在 Mysql 中,只有 Memory 引擎显式支持哈希索引。

空间数据索引(R-Tree)

MyISAM 引擎支持 R-Tree,可用作地理位置数据存储。这种索引无需前缀查询,可以从所有纬度来检索数据,但必须使用 Mysql 的 GIS 相关函数的支持,Mysql 对此支持并不完善,大部人很少使用这个特性。

全文索引

全文索引是一种特殊类型的索引,它查找的是文本中的关键字而不是比较索引中的值。

索引的优点

索引可以让服务器快速定位到表的指定位置,并且根据索引的数据结构的不同,也存在一些附加作用。

其优点有:

  1. 大大减少服务器需要扫描的数据量
  2. 可以帮助服务器避免排序和临时表
  3. 可以将随机 I/O 变为顺序 I/O

正确的创建和利用索引

独立的列

如果查询的列不是独立的,则 Mysql 不会使用索引。“独立的列”是指索引列不能是表达式的一部分,也不能是函数的参数 。

前缀索引和索引选择性

有时候索引列是很长的字符串,这会让索引变得大而慢,虽然 MYSQL 可能会隐式的转换成哈希索引,但有时候不足够,另一种方案是采用「前缀索引」。

顾名思义,前缀索引是指 索引只索引列的前面部分值。创建前缀索引需要注意的地方是分配合理的前缀大小,前缀大小决定着索引选择性

索引选择性是指通过该前缀索引能够过滤掉的行数,选择性越高,过滤的行数越多。例如:唯一索引的选择性是 1,因为通过唯一索引能够精确定位到某一条数据。这是最好的索引选择性,性能也是最好的。

对 BLOB、TEXT、很长的 varchar 必须使用前缀索引,长文本创建前缀索引关键点在于分配多少大小的前缀以保证足够高的选择性,但同时又不能太长(以便节约空间)。

创建前缀索引的语法:

1
2
3
- column 是列名
- n 是前缀索引字符宽度
create table ...... add key(column(n));

选择合适的索引列的顺序

当不考虑排序和分组的时候,可以将选择性最高的列放在索引最前列。

聚簇索引

覆盖索引

如果一个索引包含查询中所有的列,称之为「覆盖索引」。

……

第六章 查询性能优化

数据库性能的优化是个大工程,库表结构优化、索引优化、查询优化 需要一个不漏。

查询慢的因素

如果把查询看作一个任务,那么一个查询实际上是由许多个子任务组成。每个子任务都会消耗一定的时间。优化查询时间,实际上是优化子任务的查询,要么消除其中一些没有必要的子任务,要么减少子任务的查询时间,要么让子任务运行的更快。

除了一个查询含有多个子任务会导致慢之外,网络、CPU计算、数据统计、执行分析、锁等待 等因素也是导致查询慢的重要原因。

优化数据访问

查询性能低下最基本的原因是访问的数据太多,大部分的性能低下的查询都可以通过减少访问的数据量来优化,以下两个步骤很有效:

  1. 确认应用程序是否在 检索 大量超过需要的数据。这通常意味着访问了大量不需要的行,有时候也可能是访问了太多的列。
  2. 确认 Mysql 服务器层是否在 分析 大量超过需要的数据行。

是否请求了不需要的数据

有些查询会请求超过实际需要的数据,然后这些多余的数据会被应用程序丢弃。这会给 Mysql 服务器带来额外的负担,并增加网络开销,也会消耗应用服务器 CPU 和内存的资源。在设计查询中需要注意以下几点:

  • 避免查询不需要的数据。 推荐使用 LIMIT。
  • 多表关联查询时返回全部列。 应该只取需要的数据。
  • 总是取出全部列。 这样做不仅会让优化器无法使用「覆盖索引」扫描优化,还会为服务器带来额外的 I/O、内存和 CPU消耗。
  • 重复性的查询相同的数据 这类情况下考虑使用应用层缓存。

Mysql 是否在扫描额外的记录

确定查询只返回需要的数据以后,下一个应该注意的是看查询为了返回结果是否扫描了过多的数据。对于 Mysql,有以下几个简单的衡量指标:

  • 响应时间
  • 扫描的行数
  • 返回的行数

响应时间
响应时间是服务时间和排队时间的总和。服务时间是指数据库真正处理这个查询花费的时间,排队时间是指服务器因为等待某些资源而没有真正执行查询的时间,可能是等 I/O 操作完成,也可能是等行锁释放等等。

扫描的行数返回的行数
理想情况下扫描的行数和返回的行数应该是相同的,但实际情况是很少的。一般扫描的行数与返回的行数的比率很小,通常在 1:1 和 10:1 之间。

扫描的行数和访问类型
Mysql 有好几种方式可以查找并返回同一行结果,不同的方式扫描的行数不经相同,有的不需要扫描。

EXPAIN 语句中的 type 列表示访问类型。访问类型有:全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用等。这些访问方式速度从慢到快,扫描的行数从小到大。

如果发现查询需要扫描大量的数据但只返回少数的行,通常可以尝试采用以下技巧去优化:

  • 使用索引覆盖扫描,把需要用到的列都放到索引中,这样存储引擎无需回表获取对应的行就可以返回结果了。
  • 改变库表结构。如使用单独的「汇总表」
  • 重写这个复杂查询,让 Mysql 优化器以更优化的方式执行查询。

重构查询方式

优化有问题的查询,并不是说一定需要从 Mysql 获取一摸一样的结果集,而是找到一个更优的方式获得结果。比如将查询转换另外一种写法使得性能更好,或者修改应用程序代码换一种方式获得同样的结果。

一个复杂查询还是多个简单查询

尽量使用少的查询是好的,但是有时候将一个大的查询分解为多个小查询也是很有必要的。

切分查询

将一个大的查询分解成多个查询功能一样、只完成一小部分的小查询,每次只返回一小部分的查询结果。

比如删除旧数据:定期删除大量数据时如果用一个大的语句一次性完成的化,可能需要锁住很多数据、沾满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。因此将一个大的删除语句切分成多个小的删除语句可以尽可能小的影响 Mysql 的性能,同时还可以减少 Mysql 复制的延迟。

分解关联查询

将一个关联查询分解,对每一个表进行一次单表查询,然后将结果在应用程序中做关联。使用分解关联查询有很多好处,如:

  1. 让缓存效率更高。 很多应用程序可以方便的缓存单表查询对应的结果对象(对多表关联查询的结果做缓存是不切实际的,因为往往多表查询的条件是不断变化的。而对单表做缓存可以最大程序上利用缓存)。而对于 Mysql 本身的查询缓存来说,如果关联的某个表发生变化就无法使用查询缓存了。
  2. 将查询拆解后,执行单个查询可以减少锁的竞争。
  3. 在应用层做关联,可以更容易的对数据库进行拆分,更容易做到高性能和可拓展。
  4. 查询本身效率也会提高。
  5. 可以减少冗余记录的查询。 在应用层做关联查询,意味着对于某条记录应用只需要查询一次。而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样重构还可能会减少网络和内存的消耗。
  6. 更深一步,这样做相当于在应用中实现了哈希关联,而不是使用 Mysql 的嵌套循环关联。某些场景哈希关联效率要高很多。

查询执行的基础(查询的内部机制)

要想 MYSQL 能够以更高的性能运行查询,弄清楚 MYSQL 是如何优化和执行查询是很有必要的。

  1. 客户端发送一条查询语句给服务端
  2. 服务端先检查缓存,如果命中了缓存,则立即返回存储在缓存中的结果。否则进行下一阶段。
  3. 服务端进行 SQL 解析、预处理、再由优化器生成对应的执行计划。
  4. Mysql 根据优化器生成的执行计划,调用存储引擎的 API 执行查询。
  5. 将结果返回给客户端。

每一步都比想象的要复杂,其中查询优化器是最复杂最难理解的部分。

Mysql 客户端 / 服务端通信协议

mysql 客户端和服务端之间的通信协议是「半双工」的,这意味着:任一时刻,要么是服务端向客户端发送数据,要么是客户端向服务端发送数据,两个动作不会同时发生。

这种方式有一个限制就是没法对流量进行控制,当客户端向服务端请求一个大的数据量,就必须一直等待接收完服务端的响应,不可能说接收一半就断开连接。这也是为什么推荐使用 LIMIT 的原因之一了。

查询缓存

这是查询的第一阶段。在解析一个语句之前,如果查询缓存是打开的,那么 Mysql 会优先检查这个查询是否命中查询缓存中的数据。该检查是通过一个对大小写敏感的哈希查找来实现的,即使查询和缓存中的查询有一个字节不同,也不会匹配缓存结果,这种情况会进入一个阶段的处理。

如果查询命中了缓存,Mysql 在返回结果之前则会检查该用户的权限。权限没有问题就会直接返回结果跳过其他阶段。

查询优化处理

这是查询缓存之后的下一个阶段,

# mysql

Comments

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×