数据库

高性能MySQL分析

2020-05-19  本文已影响0人  陈晨_软件五千言

Schema与数据类型优化

选择优化的数据类型

有几个简单的原则：

整数类型
无符号的数字上限可以提高一倍
为整数类型指定宽度，如INT（11），不会限制值的合法范围，只是规定了Mysql的一些交互工具（命令行或客户端）用来显示的字符个数。对于存储和计算来讲，int(1) 和 int(20)是相同的。

实数类型
浮点类型在存储同样范围的值时，通常比Decimal使用更少的空间，Float使用4个字节，Double使用8个字节相比Float有更高的精度和更大的范围。这里能选择的是存储类型，Mysql内部使用Double作为内部浮点计算的类型。

字符串类型
CHAR和VARCHAR
VARCHAR节省了存储空间，如果行占用存储空间增长，并且在页内没有更多的空间存储，MyISAM拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。
下列情况使用Varchar是合适的：

InnoDB把过长的VARCHAR存储为BLOB
CHAR是定长的，会删除末尾的空格。CHAR(1)需要一个字节，VARCHAR(1)需要2个字节，因为还需要多一个字节存储长度。
类似的还有BINNARY和VARBINARY，填充使用的\0（0字节）

BLOB和TEXT
都是为了存储很大的数据设计的字符串数据类型，分别采用二进制和字符方式存储。不同在于BLOB存储的是二进制数据，没有排序规则或者字符集。
排序也只是对每个列的max_sort_length字节而不是整个字符串排序。
查询如果涉及BLOB，服务器不能在内存临时表中存储BLOB，必须要使用磁盘临时表，无论它多小。

日期和时间类型
DATETIME可以存储1001到9999年，精度为秒，与时区无关，使用8个字节的存储空间。TIMESTAMP保存了1970年1月1日以来的秒数。只使用4个字节的存储空间。从1970到2038年。

位数据类型
这些类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。
BIT
5.0之前BIT是TINYINT的同义词。之后则完全不同。MyISAM会打包所有的BIT列，InnoDB和Memory使用足够存储最小整数类型来存放BIT，所以不能节省存储空间。Mysql把BIT当作字符串类型而不是数字，会造成一些混乱。例如 a bit(8)，值为b'00111001'二进制等于57（ascii显示值等于9），a=9，a+0=57。应该谨慎使用，如果想存储true/false，可以使用CHAR(0)

选择标识符（identifier）
整数类型是最好的选择，很快并且可以使用AUTO_INCREMENT。避免使用字符串作为标识列，很耗空间，通常比数字类型慢，MyISAM默认对字符串使用压缩索引，会导致查询慢很多。

Scheme设计中的陷阱

范式和反范式
在范式化的数据库中，每个事实数据会出现并且只出现一次，相反，在反范式化的数据库中，信息是冗余的。

  1. 第一范式
    确保数据表中每列（字段）的原子性。
    如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
    例如：user用户表，包含字段id,username,password

  2. 第二范式
    在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
    如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。
    例如：一个用户只有一种角色，而一个角色对应多个用户。则可以按如下方式建立数据表关系，使其满足第二范式。
    user用户表，字段id,username,password,role_id
    role角色表，字段id,name
    用户表通过角色id（role_id）来关联角色表

  3. 第三范式
    在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关。
    例如：一个用户可以对应多个角色，一个角色也可以对应多个用户。则可以按如下方式建立数据表关系，使其满足第三范式。
    user用户表，字段id,username,password
    role角色表，字段id,name
    user_role用户-角色中间表，id,user_id,role_id
    像这样，通过第三张表（中间表）来建立用户表和角色表之间的关系，同时又符合范式化的原则，就可以称为第三范式。

  4. 反范式化
    反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。
    例如：在上例中的user_role用户-角色中间表增加字段role_name。
    反范式化可以减少关联查询时，join表的次数。

范式的优点

缺点是通常需要关联，代价相对昂贵，也可能使得一些索引策略无效。

反范式的优点
避免关联
查询相对高效（当索引合理）

创建高性能索引

索引可以包含一个或多个列，如果索引包含多个列，那列的顺序也十分重要，因为Mysql只能最高效的使用索引的最左前缀列。

B-Tree的索引列是顺序组织存储的，很适合查找范围数据。适用于全键值、键值范围或键前缀查找。

红黑树是一种含有红黑结点并能自平衡的二叉查找树。它必须满足下面性质：
性质1：每个节点要么是黑色，要么是红色。
性质2：根节点是黑色。
性质3：每个叶子节点（NIL）是黑色。
性质4：每个红色结点的两个子结点一定都是黑色。
性质5：任意一结点到每个叶子结点的路径都包含数量相同的黑结点。
从性质5又可以推出：
性质5.1：如果一个结点存在黑子结点，那么该结点肯定有两个子结点

哈希索引（hash index）只有精确匹配索引所有列的查询才有效。只包含哈希值和行指针，不存储字段值，所以不能避免读取行。
并不是按照索引值顺序存储，所以无法用于排序。
也不支持部分索引列匹配查找。只支持等值查询，不支持范围查询。

高性能的索引策略

独立的列才能使用到索引，列不能使用操作符或者表达式
多列索引，当使用到多个单列索引时，会进行多个索引的联合操作（索引合并）

选择合适的索引列顺序
正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。
在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。

聚簇索引
并非一种单独的索引类型，而是一种数据存储方式。InnoDB在同一个结构中保存了B-Tree索引和数据行。
InnoDB使用主键聚集数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义一个主键作为聚簇索引。InnoDB只聚集同一个页面的记录。
优点：

缺点：

覆盖索引
如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？所以一个索引包含（或者覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。

索引排序
只有索引的列顺序和orderby的顺序完全一致，并且列的正序，逆序都一样时，才能使用索引对结果进行排序。如果查询需要关联多张表，则只有当orderby的引用字段全部为第一个表时，才能使用索引进行排序。

索引和数据的碎片化
B-Tree索引可能会碎片化。
表的数据存储也可能碎片化：
行碎片
这种碎片指的时数据行被存储到多个地方的多个片段中。即使只查询一行记录，也会导致性能下降。
行间碎片
逻辑上顺序的页，或者行再磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大影响。
剩余空间碎片
指数据页中有大量的空余空间，会导致服务器读取大量不需要的数据造成浪费。

查询性能优化

查询的声明周期大致按照顺序：
从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。执行时最重要的阶段，包含了大量为检索数据到存储引擎的调用以及调用后的数据处理，包括排序，分组等。

慢查询基础：优化数据访问

是否请求了不需要的数据

是否在扫描额外的记录

衡量查询开销的三个指标如下：

响应时间是 服务时间 和 排队时间 之和。
扫描的行数和返回的行数理想情况下应该是相同的，一般在1:1到10:1之间
扫描的行数和访问类型：在EXPAIN语句中的type列反应了访问类型。访问类型有很多中，包括全表扫描，索引扫描，范围扫描，唯一索引查询，常数引用等。这些速度是从慢到快，扫描行数也是从多到少。

重构查询的方式

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

Mysql支持多个简单查询，一个通用服务器上可以支持每秒10万的查询，一个千兆网卡满足每秒2000次的查询。Mysql内部每秒能扫描内存中上百万行数据，相比之下响应数据给客户端就慢得多了

切分查询

将一个大查询分而治之，例如一个删除大量数据的语句，拆分为多个小的删除。

分解关联查询

有很多好处：

执行查询的基础

执行查询的过程：

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

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

通信协议是“半双工”的，意味着任何一个时刻，要么是服务端向客户端发送数据，要么是客户端向服务端发送数据。这种协议让MySQL通信简单快速。但是也意味着没法进行流量控制，一旦一端开始发送消息，另一端要完整接收完整个消息才能响应它。客户端用一个单独的数据包将查询传给服务器，所以查询语句特别长的时候，参数max_allowed_packet特别重要。

查询状态

最简单使用SHOW FULL PROCESSLIST查看当前状态，状态值有如下几种：

查询缓存

检查缓存是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同页不会匹配，如果命中在返回结果集之前MySQL会检查一次用户权限，这是无需解析SQL的，因为查询缓存中有保存当前查询需要的表信息。

查询优化处理

语法解析器和预处理

MySQL通过关键字将SQL语句解析，生成语法解析树，使用MySQL语法规则验证和解析查询。例如是否使用了错误的关键字，关键字顺序是否正确，引号前后是否正确匹配。
预处理根据MySQL规则进一步检查解析树是否合法。例如数据表、列是否存在，名字和别名是否有歧义。
下一步预处理器会验证权限。

查询优化器

语法树已经合法，优化器将其转为了执行计划。优化器作用就是找到最好的执行计划。
可以通过查询当前回话的Last_query_cost的值来得知MySQL计算当前查询成本。
根据一系列统计信息计算得来：每个表或者索引的页面个数，索引的基数（索引中不同值的数量），索引和数据行的长度，索引分布的情况。
优化器在评估成本的时候不考虑任何缓存，假设读取任何数据都需要一次磁盘IO

MySQL的查询优化器是一个复杂部件，使用了很多优化的执行策略。优化策略简单分为两种：静态优化和动态优化。
静态优化直接对解析树进行优化，静态优化在第一次万能充后就一直有效，使用不同参数执行查询页不会发生变化，可以认为是一种“编译时优化”。
动态优化和查询的上下文有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。可以认为时“运行时优化”。
MySQL能够处理的优化类型：

MySQL执行关联查询

MySQL先从一个表中循环取出单条数据，在嵌套循环到下一个表中寻找匹配的行，依次直到找到所有表中匹配的行，然后根据各个表匹配的行返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果不行就返回上一层次关联表。
MySQL多表关联的指令树时一颗左侧深度优先的树。

关联查询优化器

MySQL的最优执行计划中的关联表的顺序，通过预估需要读取的数据页来选择，读取的数据页越少越好。
关联顺序的调整，可能会让查询进行更少的嵌套循环和回溯操作。
可以使用STRAIGHT_JOIN关键字重写查询，让优化器按照查询顺序执行。

排序优化

排序时成本很高的操作，从性能角度考虑，应该尽量避免排序，或者避免对大量数据进行排序。
当不能用索引生成排序结果时，MySQL需要字节进行排序，如果数据量小使用内存，数据量大使用磁盘。不过统一都称为文件排序（filesort）。
MySQL有两种排序算法：

查询执行引擎

查询执行阶段就根据执行计划，调用存储引擎的实现接口来完成。
查询结果返回时，即使不需要返回结果集给客户端，MySQL返回查询信息，例如影响到的行数。

查询优化的局限性

关联子查询（in+子查询）
使用join，或者使用函数GROUP_CONCAT()在in中构造一个由分好分隔的列表，有时候比关联更快，in加子查询性能糟糕，一般建议使用exists等效改写。

优化特定类型的查询

优化count查询

MyISAM的count函数非常快，只有在没有条件的前提下。
近似值：某些不需要精确值的情况下，可以使用EXPLAIN出来的优化器估算行数。

优化关联查询

优化子查询

在5.6之前尽量转换使用join，5.6之后没有太多差别

优化group by和distinct

groupby 使用主键列效率更高。

优化limit

“延迟关联”，首先使用索引覆盖来选取范围内的主键，接下来根据这些主键获取对应数据。

分区表

分区表限制：

在数据量超大的时候B-Tree就无法起作用了，除非是索引覆盖查询，否则数据库服务器需要根据索引扫描的结果回表，查询所有符合条件的记录。如果数据量巨大，这将产生大量随机IO，数据库的响应时间将大到不可接受的程度。

MySQL优化服务器配置

MySQL配置的工作原理

MySQL从 命令行参数和配置文件中获取配置信息。配置文件一般是在 /etc/my.cnf 或 /etc/mysql/my.cnf。
确认配置文件路径，可以使用下列命令

$ which mysql
/bin/mysql
$/bin/mysql --verbose --help|grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

配置文件分为多个部分，每个部分的开头是用方括号括起来的分段名称。客户端会读取client部分，服务器通常读取mysqld部分。

配置项都使用小鞋，单次之间用下划线或者横线隔开。

常用变量及其效果

InnoDB事务日志

InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，无需在每个事务提交时把缓冲池的脏块刷新到磁盘中。
InnoDB用日志把随机IO变成顺序IO，一旦日志写入磁盘，事务就持久化了，即使变更还没有写到数据文件。
InnoDB最后是要把变更写入数据文件，日志有固定大小。InnoDB的日志是环形方式写的：当写到日志的尾部，会重新跳转到开头继续写，但不会覆盖到还没应用到数据文件的日志记录，因为这样会清掉已经提交事务的唯一持久化记录。
InnoDB使用一个后台线程只能地刷新这些变更到数据文件。这个线程可以批量组合写入，是的数据写入更顺序，以提高效率。事务日志把数据文件的随机IO转换为几乎顺序的日志文件和数据文件IO，把刷新操作转移到后台使得查询可以更快完成，并且缓和查询高峰时IO的压力。

InnoDB表空间

InnoDB把数据保存在表空间内，本质上是一个由一或多个磁盘文件组成的虚拟文件系统。InnoDB用表空间实现很多功能，不只是存储表和索引。它还保存了回滚日志（旧版本号），插入缓冲（Insert Buffer）、双写缓冲（Doublewrite Buffer），以及其他内部数据结构。
InnoDB使用双写缓冲来避免页没写完整锁导致的数据损坏。这是一个特殊的保留区域，再一些连续的块中足够保存100个页。本质上是一个最近写回的页面的备份拷贝。当InnoDB从缓冲池刷新页面到磁盘时，首先把他们写到双写缓冲，然后再把他们写到其所属的数据区域中，可以保证每个页面的写入都是原子并且持久化的。页面在末尾都有校验值（Checksum）来确认是否损坏。

InnoDB的多线程

InnoDB的内存

  1. Master Thread每秒刷新一次
  2. 每个事务提交时会刷新
  3. redo log buffer剩余空间小于1/2时

Checkpoint技术

InnoDB存储引擎内部有两种：

InnoDB关键特性

插入缓冲

Insert Buffer是一颗B+树，全局唯一，负责对所有表的辅助索引进行Insert Buffer。
Merge Insert Buffer是合并到真正的辅助索引中的操作，在下面几种情况时发生：

自适应Hash索引（Adaptive Hash Index）

InnoDB 会监控各种索引列的查询，如果判断建立哈希索引可以提高访问速度，则会自动建立。AHI是通过缓冲池的B+树构建而来，不需要对整张表结构建立哈希索引。有如下要求：

异步IO

异步IO（Asychronous IO，AIO）

文件

二进制日志（binlog）

记录了对MySQL数据库执行更改的所有操作，不包括SELECT和SHOW。

mysql> mysqlmaster status;

File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
binlog.001663 5924141

mysql> show binlog events in 'binlog.001663' limit 5;

binlog文件名（Log_name） 日志开始位置（Pos） 事件类型（Event_type） 服务器编号（Server_id） 日志结束位置（End_log_pos） 信息
binlog.001663 5878887 Anonymous_Gtid 1 5878966 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663 5878966 Query 1 5879057 BEGIN
binlog.001663 5879057 Table_map 1 5879148 table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663 5879148 Update_rows 1 5879340 table_id: 8291 flags: STMT_END_F
binlog.001663 5879340 Xid 1 5879371 COMMIT /* xid=4800934 */
binlog.001663 5879371 Anonymous_Gtid 1 5879450 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663 5879450 Query 1 5879541 BEGIN
binlog.001663 5879541 Table_map 1 5879632 table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663 5879632 Update_rows 1 5879824 table_id: 8291 flags: STMT_END_F
binlog.001663 5879824 Xid 1 5879855 COMMIT /* xid=4800956 */
binlog.001663 5879855 Anonymous_Gtid 1 5879934 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.001663 5879934 Query 1 5880025 BEGIN
binlog.001663 5880025 Table_map 1 5880116 table_id: 8291 (vv_oaxxljob.XXL_JOB_QRTZ_SCHEDULER_STATE)
binlog.001663 5880116 Update_rows 1 5880308 table_id: 8291 flags: STMT_END_F
binlog.001663 5880308 Xid 1 5880339 COMMIT /* xid=4800988 */

MySQL5.1引入了binlog_format参数，参数有STATEMENT、ROW、MIXED三种。

  1. 表的存储引擎为NDB，对表的DML操作以ROW格式记录。
  2. 使用了UUID() USER() CURRENT_USER() FOUND_ROWS() ROW_COUNT()
  3. 使用了INSERT DELAY语句
  4. 使用了用户自定义函数
  5. 使用了临时表

要查看binlog日志文件的内容，必须使用MySQL提供的工具mysqlbinlog。

表结构定义文件

MySQL定义了frm为后缀名的文件，记录了表结构（视图）定义。

InnoDB存储引擎文件

表空间文件（tablespace file）

默认有一个初始大小为10MB，名为ibdata1的文件

重做日志文件（redo log file）

默认情况下会有 ib_logfile0和ib_logfile1作为 redo log file 。每个InnoDB至少有一个重做日志文件组（group），文件组下有两个重做日志文件，用户可以设置多个镜像日志组（mirrored log groups）

表

索引组织表（index organized table）

MySQL默认创建一个6字节大小的指针（_rowid）

InnoDB逻辑存储结构

所有的数据都被逻辑地存放在一个空间内，称之为表空间（tablespace），表空间又由段（segment），区（extent）、页（page）组成，页在某些文档中也成为块（block）


InnoDB逻辑存储结构

表空间

如果启用了 innodb_file_per_table的参数，每张表的数据可以单独放到一个表空间内 ，其中存放的是数据、索引、和插入缓冲Bitmap页，其他类的数据如回滚（undo）信息、插入缓冲索引页、系统事务信息、二次写缓冲还是放在原本的共享表空间。

段

表空间是由各个段组成的，包括数据段、索引段、回滚段等。数据段就是B+树的叶子节点（Leaf node segment），索引段即B+树的非索引节点（Non-leaf node segment）。

区

区是连续页组成的空间，任何情况下每个区的大小都为1MB，为了保证区中页的连续性，InnoDB一次从磁盘申请4-5个区，默认情况页大小为16KB，一个区中一共有64个连续的页。InnoDB1.0.x引入压缩页，每个页的大小可以通过key_block_size设置为2k、4k、8k。1.2.x版本新增了参数innodb_page_size，通过该参数可以将默认页的大小设置为4k、8k。

页

InnoDB中常见的页类型有：

行

MySQL的存储是面向列的（row-oriented）,数据是按行存储的。页存放的记录有硬性定义最多存放16KB/2 - 200行，即7992行。

InnoDB数据页结构

数据页由下面7个部分组成：

行溢出数据
InnoDB会将一条记录中的某些列存储在真正的数据列之外，BLOB，LOB字段可能不一定会将字段放在溢出页面，VARCHAR也有可能会放进溢出页面。
Oracle VarCHAR2最多存放4000字节，MSSQL最多8000字节，MySQL最多65535（存在其他开销，最长65532）。当发生行溢出时，数据存放在页类型Uncompress BLOB页面。数据页只保存数据的前768字节。

锁

lock与latch
latch一般称为闩锁，轻量级，要求锁定的时间非常短。在InnoDB中，分为mutex（互斥量）与rwlock（读写锁）。用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。
lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。在commit或者rollback之后释放，有死锁检测机制。

锁的类型

上述两种都是悲观锁，乐观锁就是CAS（Compare and Swap）

一致性非锁定读（consistent nonlocking read）
是指InnoDB通过MVCC（Multi Version Concurrency Control）读取数据库当前行的方式。如果读取的行正在进行update或者delete操作，则读取一个快照。在Read Committed和Repeatedable Read中使用。前者读取最新的快照，后者使用事务开始时的快照。
一致性锁定读（locking read）
也可以显式的对读取加锁，有两种操作：

行锁的3种算法

死锁

两个事务执行时，因争夺锁资源互相等待的场景。
解决死锁最简单的就是超时，通过innodb_lock_wait_timeout控制超时时间。
当前普遍使用的是wait-for graph（主动检测的方式），这要求数据库保存两种信息：

通过上述信息，可以在事务请求锁并发生等待时都进行判断，在上述两个信息构造的图中是否存在回路，如果存在就表示存在死锁。
采用深度优先算法实现，InnoDB1.2之前采用递归方式，之后采用非递归提高了性能。

事务的实现（ACID）

事务的隔离性由锁来实现，redo log（重做日志）保证事务的原子性和持久性，undo log（）保证事务的一致性。
redo恢复提交事务修改的页操作，是物理日志，记录的是页的物理修改操作。
undo回滚某个行记录到特定版本，是逻辑日志，记录的是行的修改记录。

redo

存在 redo log buffer和redo log file，buffer写入file时需要调用fsync操作，此操作取决于磁盘性能，决定了事务提交的性能也就是数据库的性能。

UNIX的写操作
一般情况下，对硬盘（或者其他持久存储设备）文件的write操作，更新的只是内存中的页缓存（page cache），而脏页面不会立即更新到硬盘中，而是由操作系统统一调度，如由专门的flusher内核线程在满足一定条件时（如一定时间间隔、内存中的脏页达到一定比例）内将脏页面同步到硬盘上（放入设备的IO请求队列）。
因为write调用不会等到硬盘IO完成之后才返回，因此如果OS在write调用之后、硬盘同步之前崩溃，则数据可能丢失。虽然这样的时间窗口很小，但是对于需要保证事务的持久化（durability）和一致性（consistency）的数据库程序来说，write()所提供的“松散的异步语义”是不够的，通常需要OS提供的同步IO（synchronized-IO）原语来保证
fsync的功能是确保文件fd所有已修改的内容已经正确同步到硬盘上，该调用会阻塞等待直到设备报告IO完成。除了同步文件的修改内容（脏页），fsync还会同步文件的描述信息（metadata，包括size、访问时间st_atime & st_mtime等等），因为文件的数据和metadata通常存在硬盘的不同地方，因此fsync至少需要两次IO写操作

undo

delete和update操作产生的删除语句并不是马上执行，而是将delete_flag标记为1，最后有purge操作来统一完成。用undo log来执行，执行之后的空间不会回收，只会用于重用。

上一篇 下一篇

猜你喜欢

热点阅读