高性能MySQL篇

MySQL最重要的特性是存储引擎架构

用户认证、是否具有执行某个查询的权限

可重复读是MySQL的默认隔离级别，而大多数数据库是读提交。

InnoDB目前处理死锁的方法：将持有最少行级排它锁的事务进行回滚。

默认采用自动提交（AUTOCOMMIT）模式，即除非显示开始事务，否则每个查询都被当成一个事务执行提交。
启动MySQL方式mysql -u root -p，use mysql

1. Schema设计

1.1 数据类型

（1）尽量使用正确存储数据的最小数据类型
（2）使用MySQL内建的类型而不是字符串来存储日期和时间；应该使用整型存储IP地址
（3）尽量避免NULL。最好指定列为NOT NULL

1.1.1 整数类型

类型名	TINYINT	SMALLINT	MEDIUMINT	INT	BIGINT
位数	8	16	24	32	64

可选 UNSIGNED 属性，表示无符号数。

可以指定宽度，例如 INT(11)，这只是规定了命令行客户端显示字符的个数，不会限制值的合法范围。

选择这些类型决定了怎么保存数据，整数计算一般使用64位的 BIGINT 整数。（此处理解是，计算时会进行强制类型转换都转换为 BIGINT）

1.1.2 实数类型

（1）浮点类型 FLOAT和 DOUBLE 用于近似计算，效率更高。选择的只是存储类型，MySQL使用 DOUBLE 作为内部浮点计算的类型。

（2）DECIMAL 类型用于存储精确的小数，支持精确计算。MySQL服务器自身实现的。

原理：将数字打包到一个二进制字符串中（每4个字节表达9位数字，小数点占1字节）

一般用于存储财务数据。当数据量比较大时，可以考虑采用 BIGINT 替代，根据小数位数乘以相应的倍数，再将结果存储到 BIGINT 里。

（3）上述两者都可以指定精度，例如 DECIMAL(m, d)，m为总有效位数，d为小数位数。

小数部分和整数部分分别存放到整数类型中，从小数点位置出发，每隔 9 个十进制位就划分为一组（最后一个不足 9 位的也划分为一组）。

最后将整个结果的最高位置为 1。存储负数将对应正数按位取反即可。

（4）m 默认值为 10，d 默认值为 0；m 的范围为 1 ~ 65，d 最大值为 0 ~ 30，且 d 不能超过 m 值。

1.1.3 字符串类型

	CHAR	VARCHAR
特点	会删除所有末尾空格	保留末尾空格
适合情况	1.定长类型，例如密码的MD5值；2.经常变更的数据，不容易产生碎片；3.非常短的列；	1.字符串列的最大长度比平均长度大很多；2.列更新很少；3.使用了 UTF-8 复杂的字符集，每个字符都使用不同的字节数存储。

（1）VARCHAR 类型的行占用空间增长，并且页内没有更多空间时，MyISAM会将行拆分不同的片段存储，InnoDB需要分裂页

（2）BINARY 和 VARBINARY ， 存储的是二进制字符串。存储的是字符码而非字符，采用的是 \0 填充。

这里的存储的是字符码而非字符是指，每个字节对应一个字节码（0~255），对ASCII编码，每个字符占一个字节即可，那么只能用 BINARY(1) 存储，‘兮’ 等汉字占两个字节，只能使用 BINARY(2) 及以上大小进行存储。

而CHAR 占用字节大小和编码方式相关。

（3）BLOB 存储的是二进制数据， TEXT 是字符方式。

对 BLOB 和 TEXT 列进行排序， 只对每个列的最前 max_sort_lenth 字节进行排序。也不能对 BLOB 和 TEXT 全部长度的字符串进行索引。

（4）枚举类型；MySQL 在内部只需保存每个值的位置，在表的 frm 文件中保存 “数字 - 字符串”的 “查找表”。

在较大数据量下，CHAR/VARCHAR 列与枚举列进行关联效率会比较低，不如VARCHAR 与 VARCHAR 直接关联。ENUM 与 ENUM 关联效率最高。（例如，进行JOIN操作）

另一方面，将CHAR/VARCHAR 列转换为 ENUM 会减少表的大小。

采用整数主键而避免采用基于字符串的值进行关联

（5）MySQL在内部使用整数存储 ENUM 和 SET 类型，做比较操作时转换为字符串。

1.1.4 日期和时间类型

MySQL 能存储的最小时间粒度为秒。

	DATETIME	TIMESTAMP
表达方式	文本表示YYYYMMDDHHMMSS	和UNIX时间戳相同，转换方式为：FROM_UNIXTIME()将Unix时间戳转换为日期；UNIX_TIMESTAMP() 则反过来
占用存储空间	8个字节	4个字节
特点	与时区无关	依赖时区
特殊属性		插入没有指定第一个 TIMESTAMP 列的值，设置为当前时间；插入记录时，默认也会更新第一个 TIMESTAMP 列的值；默认为 NOT NULL

尽量使用 TIMESTAMP ，其空间效率更高

可以采用 BIGINT 类型存储微妙级别的时间戳，或者使用 DOUBLE 存储秒之后的小数部分。

1.1.5 位数据类型

（1）BIT(n)，n 表示位数，最大为 64

创建一个可以为空的 CHAR(0) 列，可以保存 NULL 或者空字符串，代表 True/False（只需一个bit的存储空间）。

（2）SET；保存很多 True/False 值。

当向 SET 列插入数据时，列的值若在 SET 中未出现

会报错

创建表格
CREATE TABLE acl (
    -> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
    -> );

错误示例如下：
INSERT INTO acl VALUES ('CAN_READ, CAN_DELETE'); // 不能有空格。必须和SET中一致
INSERT INTO acl VALUES ('XXX');		// 未在 SET 列中出现
	ERROR 1265 (01000): Data truncated for column 'perms' at row 1
INSERT INTO acl VALUES ('CAN_READ,CAN_DELETE');  // 正确方式

1.1.6 标识符

整数通常是标识列的最好选择。

如果存储 UUID 值，最好使用 UNHEX() 转换为16字节整数，并存储在一个BINARY(16) 列中。检索时可以通过 HEX() 格式化为16进制格式。

1.1.7 特殊类型

IP 地址和32位无符号整数相互转换。
INET_ATON()
INET_NTOA()

1.2.2 Schema 设计原则

（1）避免太多的列

MySQL 服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码为各个列。

MyISAM 的定长行结构不需要转换。
MyISAM 的变长行结构和 InnoDB 的行结构总是需要转换。

转换代价依赖于列的数量。

（2）避免太多的关联

单个查询最好在 12 个表以内做关联。

MySQL 限制每个关联操作最多只能有 61 张表。

（3）避免滥用枚举

防止过度使用枚举。尽量使用整数替代。

枚举列允许在列中存储一组定义中的单个值，集合（SET）列则允许在列中存储一组定义值中的一个或多个值。例如，定义值为(‘Y’,‘N’)时，应该使用枚举列。

MySQL 会在索引中存储 NULL 值， 而 Oracle 不会。

1.2.3 范式和反范式

范式化：每个事实数据会出现并且只出现一次。（分为多个表，通常需要关联）
反范式化：信息是冗余的，可能会存储在多个地方。（所有数据都出现在一张表，可以很好避免关联）

1.2.4 缓存表和汇总表

缓存表 表示存储那么可以比较简单从 schema 其他表获取数据的表。
汇总表 保存的是使用 GROUP BY 语句聚合数据的表。

函数 NOW() 的返回值大致格式：
2023-10-28 16:33:06
LEFT(str, N) 代表从左向右截断N个字符
LEFT(NOW()，14) 返回 2023-10-28 16:

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。

ON DUPLICATE KEY UPDATE 如果主键存在的话就执行更新操作，不存在执行插入操作。

1.2.5 加快 ALTER TABLE

InnoDB 支持通过排序来建索引，使得索引更快且有紧凑的索引布局。

影子拷贝：用要求的表结构创建新表，然后通过重命名和删表操作交换两张表。

（1）删除或改变一个列的默认值的两种方法

MODIFY COLUMN xxx xxx_type NOT NULL DEFAULT num; 会重建表，较慢
ALTER COLUMN xxx SET DEFAULT num; 直接修改 frm 文件，很快。

（2）快速创建 MyISAM 索引

先禁用索引，载入数据，再重新启动索引。（会通过排序构建索引）这个方法对唯一索引无效。

2. 查询优化

客户端发生给服务器

服务器解析，执行

返回结果给客户端

2.1 优化数据访问

应用程序是否在检索大量超过需要的数据。访问了太多的行，也可能是访问了太多的列；
MySQL服务器层是否在分析大量超过需要的数据行。

2.1.1 是否向数据库请求了不需要的数据

（1）查询不需要的记录。加 LIMIT

（2）多表关联时返回全部列。

（3）总是取出全部列。

无法完成索引覆盖扫描优化。

（4）重复查询相同的数据。利用缓存。

2.1.2 MySQL是否在扫描额外的记录

衡量查询开销的三个指标：响应时间、扫描的行数、返回的行数。都会记录到慢日志中。

查询需要扫描大量的数据但只返回少数的行，尝试优化手段为：

1. 使用索引覆盖扫描，把所有需要的列都放到索引中，无需回表。
2. 改变库表结构，使用汇总表。
3. 重写查询

2.2 重构查询

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

MySQL 的连接和断开连接都是轻量级的，在返回一个小的查询结果很高效。

2.2.2 切分查询

一次性删除表中的数据，还是分多次，每次删除1万行。

2.2.3 分解关联查询

2.3 查询执行基础

MySQL 客户端和服务器之间的通信协议是半双工的。

客户端用一个单独的数据包将查询传给服务器。

一般服务器响应给用户的数据通常很多，由多个数据包组成。

（1）查询缓存；命中查询缓存是通过一个对大小写敏感的哈希查找实现的。

（2）语法解析器和预处理；

语法解析器：检查语法错误，生成解析树；
预处理：检查解析树是否合法，表是否存在等；验证权限

（3）MySQL 的临时表是没有任何索引的。

MySQL 在排序时，对每个排序记录都会分配一个足够长的定长空间存放。

（4）查询执行引擎

查询中每一个表由一个 handler 的实例表示，根据其接口可以获取表的相关信息。

一旦使用 DISTINCT 和 GROUP BY 查询执行过程中通常需要产生临时中间表。

2.4 查询执行基础

（2）UNION 限制

如果希望 UNION 的子句能够根据 LIMIT 只取部分结果集，或者能够先排好序再合并，需要在 UNION 各子句中分别应用 LIMIT 或 ORDER BY；

分别对两个表排序，进行合并后的数据会放在临时表中，需要注意的是从临时表中取出数据的顺序并不是一定的，还需要加上全局的 ORDER BY 操作。

（3）无法利用多核来并行执行查询。不支持哈希 Join 。

（4）用户自定义变量的生命周期是在一个连接中有效。