高性能MySQL —— 3、服务器性能剖析 和 Schema与数据类型优化（索引和查询优化笔记待更新）

1. 服务器性能剖析

对于服务器性能剖析一章，因为我个人并非DBA方向而是后台开发方向，所以仅列出我觉得有用的内容。

慢查询问题定位方法：

1. 开启慢查询日志，设置long_query_time来定义判断是否慢查询的阈值。通过分析慢查询日志，找到问题SQL；
2. 使用show status 或 show global status，不间断统计每个时间段中，各个MySQL计数点的数值，并绘制图形，定位问题点；
3. 使用MySQL 5.5之后的Performance Schema收集的数据库服务器性能参数进行判断。通过performance_schema设置开关；
4. 使用information_schema库中的profiling表查看SQL语句执行中，每一步消耗的时间和资源。session级别，set @@ssesion.profiling=1开启。

2. Schema与数据类型优化

2.1 选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要：

1. 更小的通常更好
   一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型占用更少的磁盘、内存和CPU缓存，处理时需要的CPU周期也更少。
2. 简单就好
   简单数据类型的操作通常需要更少的CPU周期。
3. 尽量避免null
   可为null的列会使用更多的存储空间，在MySQL里也需要特殊处理。虽然InnoDB有单独的位存储null值，对于稀疏数据有很好的空间效率，但MyISAM没有。

例如：datatime和timestamp都可以存储时间和日期且精确到秒。但timestamp只占用datatime一半的存储空间，且会根据时区优化，具有特殊的自动更新能力；另一方面，timestamp允许的时间范围要小很多。

MySQL为了兼容性支持很多别名，如Integer、Bool、Numeric，但不会影响性能。

2.1.1 整数类型

整数类型有：TinyInt、SmallInt、MediumInt、Int(Integer)、BigInt，分别占用8、16、24、32、64位存储空间，值范围为-2^(n-1)到 2^(n-1) -1，N为存储空间位数。
如果设置unsigned，代表无符号，即二进制符号位参与数据保存，值范围0到2^n，有无符号不会影响性能。
MySQL整数指定宽度并不会限制值得合法范围，只是规定了MySQL的一些交互工具能够显示的字符的个数，对于存储来说INT(1)和INT(20)是相同的。
指定宽度：当数据宽度不足设置的宽度值时，会在左侧补0返回。但我用MySQL 8.0.20整数类型已经不能指定长度了。

2.1.2 实数类型

实数类型有：decimal(numeric)、float、double、real(默认就是double，设置REAL_AS_FLOAT则为float)
实数是带有小数部分的数字。同时不只是为了存储小数部分，可以使用decimal存储比bigint还大的整数。
float和double类型支持使用标准的浮点运算进行近似计算。
decimal用于存储精确地小数。MySQL5.0及之后，decimal支持精确计算。由于decimal的精确计算是MySQL实现的，所以比CPU原生的浮点计算要慢。
MySQL5.0及更高版本，decimal将数字保存到一个二进制字符串中，每4个字节存9个数字。如：decimal(18, 9)小数点两边各存储9个数字，小数点占一位，共需9个字节。
decimal在MySQL 5.0及之后，允许最多65个数字，小数最多30个数字。
float使用4个字节，double使用8个字节，能选择的只是存储类型；MySQL使用double作为内部浮点计算的类型。
decimal建议在需要精确小数计算时使用，数据量较大时可以考虑使用整数类型将数据拆分成数据值与小数位数，可以避免浮点计算不准确与decimal精确计算代价高的问题。

2.1.3 字符串类型

字符串类型有：Varchar、Char、Binary、VarBinary、BLOB、TEXT、ENUM、Set。
从MySQL 4.1开始，每个字符串列可以定义自己的字符集和排序规则，很大程度上影响性能。

Varchar和Char类型
这两种类型的值怎么存储在磁盘和内存中，和存储引擎的具体实现有关。
这里仅讨论InnoDB与MyISAM。

1. Varchar
   用于存储可变长字符串。比定长类型更省空间。但表使用了ROW_FORMAT=FIXED创建则会使用定长存储。
   Varchar需要使用1或2个额外字节记录字符串长度：如果列最大长度小于等于255字节，则使用1个字节记录长度，否则使用2个字节。
   Varchar节省了存储空间，所以对性能也有帮助。但由于行长可变，如果一个行占用的空间增长，且页内没有更多的空间存储，MyISAM会拆成不同的片段存储，InnoDB则需要分裂页来放进页内。
   适合使用Varchar的情况：字符串列最大长度比平均长度大很多；列的更新较少；使用UTF-8这样复杂的字符集，每个字符都是用不同的字节数进行存储。
   Varchar会保留行尾空格，InnoDB会把过长的Varchar存储为BLOB。

2. Char
   char类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间。
   char类型存储会删除所有行尾空格，会根据需要采用空格填充方便比较。
   适合存储很短的字符串，或者所有值都接近同一个长度。
   例如存储密码的MD5，使用Char(1)存储1和0，若使用单字节字符集这样仅需要1个字节，而Varchar(1)需要两个字节，tinyint需要8个字节。

3. Binary和VarBinary
   存储的是二进制字符串的字节码，采用零字节填充，检索时不会去掉填充值。MySQL比较Binary字符串时，每次按一个字节的数值进行比较，因此二进制比较比字符比较简单很多也就更快。

BLOB和TEXT类型
都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式保存。
分别属于
TinyTEXT（最大2⁸-1字节）、TEXT（最大2¹⁶-1字节）、MediumTEXT（最大2²⁴-1字节）、LongTEXT（最大2³²-1字节）；
TinyBLOB（最大2⁸-1字节）、BLOB（最大2¹⁶-1字节）、MediumBLOB（最大2²⁴-1字节）、LongBLOB（最大2³²-1字节）

MySQL把每个BLOB和TEXT值当做一个独立的对象处理。当值太大时，InnoDB会使用专门的“外部”存储区域进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。
BLOB存储二进制数据，没有排序规则和字符集；TEXT有字符集和排序规则。
TEXT排序只对每个列最前max_sort_length字节排序，或使用order by sustring(column, length)。
MySQL不能将BLOB和TEXT列全部长度的字符串进行索引，也不能使用这些索引消除排序。

使用枚举ENUM代替字符串类型
枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储时非常紧凑，会根据列表值的数据压缩到一个或者两个字节中。MySQL内部会将每个值在列表中的位置保存为整数，并在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。
枚举字段按照内部存储的整数排序。
如想排序可以：1.按照顺序定义枚举列；2. 使用FIELD()显示指定排序，但会导致MySQL无法利用索引消除排序。

添加或删除字符串需要Alter Table。如果能接受只在ENUM字符串列表末尾添加元素，在MySQL 5.1及之后就不用重建整个表来修改。
ENUM与Varchar进行关联可能会导致QPS下降，但能够有效的缩小表大小。

Set
Set与ENUM类型，区别在于ENUM只能指定一个值，set可以指定多个值，可以使用FIND_IN_SET()方便查询，一般来说也无法走索引。

2.1.4 日期和时间类型

日期和时间类型有：date、year、datetime、timestamp
最小时间粒度为秒。

datetime
保存1001年到9999年，精度为秒。封装格式为YYYYMMDDHHMMSS的整数，与时区无关。8个字节。

timestamp
时间戳，记录1970-01-01 08:00:00后的秒数，只能表示1970到2038年。使用from_unixtime()和unix_timestamp()进行日期和时间戳的转换，依赖时区。4个字节。

2.1.5 位数据类型

包括：bit

bit
存储以二进制格式存储数据，BIT(N)指定长度为N。MyISAM会打包存储，如长度1-8只有使用1个字节，而InnoDB会转为能够存储的最小整数类型。
我是用的MySQL 8.0.20中，单独查询bit字段返回二进制内容，涉及到计算则会转为十进制处理。

2.1.6 JSON

Mysql 5.7新增对于json的支持，对于json格式相关处理可以参考《MySQL 5.7新增对JSON支持》

2.1.7 地理空间数据Geometry

包括：Geometry、Point、LineString、Polygon、MultiPoint、MultiLineString、MultiPolygon、GeometryCollection

类型	含义	说明	示例
Geometry	间数据	任意一种空间类型
Point	点	坐标值	POINT(121.474 31.2329)
LineString	线	线，由一系列点连接而成	LINESTRING(3 0, 3 3, 3 5)
Polygon	多边形	由多条线组成	POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))
MultiPoint	点集合	集合类，包含多个点	MULTIPOINT(0 0, 20 20, 60 60)
MultiLineString	线集合	集合类，包含多条线	MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
MultiPolygon	多边形集合	集合类，包含多个多边形	MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0)), ((5 5, 7 5, 7 7, 5 7, 5 5)))
GeometryCollection	空间数据集合	集合类，可以包括多个点、线、多边形	GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

2.2 MySQL schema设计中的陷阱

1. 太多的列
   MySQL存储引擎api工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。

2. 太多的关联
   表关联太多，解析和优化查询的代价也会越高。

3. 过度枚举、变相枚举
   在内容改动不大，数据量不多的时候才可以考虑枚举，但实际不推荐枚举。

4. NULL
   如果条件允许，可以用不可能值来代替NULL。

2.3 范式化和反范式化

数据库三范式：
1、列不可再分；
2、属性完全依赖于主键；
3、属性不依赖于其它非主属性，属性直接依赖于主键。

2.3.1 范式的优点和缺点

优点：

1. 范式化的更新操作通常比反范式化要快；
2. 当数据较好的范式化时，重复数据就更少，需要修改的也更少；
3. 范式化表通常很小，能更好的放入内存，执行操作更快；
4. 更好的避免了distinct或者group by。

缺点：
依赖于表间关联，复杂查询代价昂贵，可能使索引无效。

2.3.2 反范式的优点和缺点

优点：

1. 避免表关联，很多时候表关联会使我们在增删改一张表的时候不得不先去额外维护一张或多张子表；
2. 即使全表扫描，当数据量大于内存时能够快得多，避免了随机I/O；
3. 能更好的利用索引。

缺点：
数据冗余，容易出现脏数据，数据维护可能需要维护多张表，但大部分时候个人感觉还是要比表关联来得方便。

2.3.3 混用范式化和反范式化

在现实中很少会极端的使用完全范式化或完全反范式化。
常见的混用是在不同的表中存储相同的特定列，如有需要可以通过触发器更新缓存值；

2.3.4 缓存表和汇总表

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据，有时也需要创建一张完全独立的汇总表或缓存表。
如使用缓存表存储可以简单的从其他表获取但速度较慢的数据；使用汇总表保存group by后的聚合数据如每天的数据报表。

缓存表可以使用MyISAM引擎获得更小的索引占用空间，并且可以做全文搜索。

使用缓存表和汇总表，必须决定是实时维护还是定期重建。
重建时可以通过“影子表”来实现，如：
create table summary_new like summary;
rename table summary to summary_old, summary_new to summary;

物化视图
预先计算并且存储在磁盘上的表，可以通过各种各样的策略刷新和更新。
MySQL并不原生支持，可以通过Flexviews工具自己实现。通过读取服务器二进制日志并解析相关行的改变，每次去进行数据的维护。

计数器表
可以单独创建一张表存储计数器，如点击数、下载数等，通过配置多行相同的条目，使用随机更新，来避免频繁更新时引发串行事务，如：
update click_count set cnt = cnt + 1 where name = ‘download’ and slot = RAND() * 100;
select sum(cnt) from click_count where name = ‘download’;

2.4 加快Alter Table操作的速度

MySQL执行大部分修改表结构操作的方法是用新的结构创建一张空表，从旧表中查出所有数据插入新表，然后删除旧表。
可以通过两种手段加快操作：

1. 先在一台不提供服务的机器进行alter table，然后切换数据库；
2. 或通过“影子拷贝”，先用要求的表结构创建一张新表，然后重命名和删表完成交换。

并不是所有的Alter table都会引起表重建，可以通过修改.frm文件来避免重建表。

2.4.1 只修改.frm文件

MySQL支持修改默认值不用重新建表：
通过Alter table [table] alter column [column] set default [value]；修改默认值

MySQL不支持，但通过下述步骤，以下操作有可能不需要重建表：

1. 移除一个列的Auto_Increment属性；
2. 增删改ENUM和SET常量。

操作步骤：

1. 创建一张有相同结构的空表，并进行所需的修改；
2. 执行flush tables with read lock;添加读锁并关闭所有正在使用的表，且禁止任何表被修改；
3. 交换.frm文件；
4. 执行unlock tables释放读锁。

2.4.2 快速创建索引

存在一定的风险。
通过先禁用索引，然后载入数据，再重新启用索引可以快速简历索引。
原理是将构建索引的工作延迟到数据完全载入以后，这时能够通过排序来构建索引了。这样会快很多，且使得索引树的碎片更少更紧凑。

MyISAM
alter table [table] disable keys;
– load the data
alter table [table] enable keys;
对唯一索引无效，MyISAM会在内存中构造唯一索引，并为载入的每一行检查唯一性。一旦索引超过有效内存大小，载入操作就会变得越来越慢。

InnoDB
依赖于快速在线索引创建功能。先删除所有非唯一索引，然后增加新的列，最后重新创建删除掉的索引。