MYSQL 数据库结构优化

WindWant

发布于 2020-09-11 11:40:29

7.5K1

发布于 2020-09-11 11:40:29

文章被收录于专栏：后端码事

数据库结构优化

优化数据大小

使表占用尽量少的磁盘空间。减少磁盘I/O次数及读取数据量是提升性能的基础原则。表越小，数据读写处理时则需要更少的内存，同时，小表的索引占用也相对小，索引处理也更加快速。

MySQL支持不同类型的存储引擎和行格式，针对不同类型，业务需求的表应该设置合适的存储引擎及索引方法。表设置建议如下：

表列

为列选择最合适（通常最小）的数据类型。MySQL 具有许多不同列类型以最大化的减少磁盘和内存占用。例如，使用足够小的整型来表示小范围的小整型数据。MEDIUMINT 通常是一个很好的选择，它只占用INT 25%，甚至更小的空间。
如果可能，则将列声明为NOT NULL。声明为NOT NULL，使得优化器能够更好的使用索引，并避免了判断NULL的处理，这使得SQL 操作执行的更加快速。同时也为每列节省了1 bit的空间。如果确实需要使用NULL 值，那么也应该避免所有列都允许NULL。
InnoDB 表默认使用动态类型(DYNAMIC )的行格式。可以通过设置默认行格式（innodb_default_row_format），或者在表定义（CREATE TABLE 或 ALTER TABLE ）中声明使用的行格式。

行格式

压缩类型的行格式，包括COMPACT, DYNAMIC, 和 COMPRESSED，对于特定操作，减少了存储空间占用，但是增加了CPU计算能力使用。如果主要的负载在缓存命中率及磁盘读写速度，那么这种格式将能够提升数据库反应速度。如果是极端情况负载受限于CPU性能，那么使用这种格式则会降低数据库性能。

压缩行格式也会对使用utf8mb3 或者 utf8mb4格式的变长CHAR 类型列存储进行优化处理。对于使用ROW_FORMAT=REDUNDANT, CHAR(N) 定义的表，每个列值最多占用 N × 个字节长度。许多语言可以使用但字节的utf8 格式表示，所以规定那个长度的定义通常会造成空间浪费。压缩行格式定义下， InnoDB 会每一个列值分配一个N 到 N× 个字节的空间。

设置压缩行格式：表定义时指明 ROW_FORMAT=COMPRESSED ；在指定 MyISAM表上运行 myisampack 命令。 (InnoDB 压缩表可读写；MyISAM 压缩表是只读的)。
对于 MyISAM 表，如果没有变类型列，如 (VARCHAR, TEXT, or BLOB columns)，所有列都是固定长度的。这种表类行对于SQL操作执行比较快速，但是会造成一些空间浪费。可以通过定义指明使用固定长度的行格式（ROW_FORMAT=FIXED），即使存在变成类型的列。
表的主键索引应该尽可能的短，这样行匹配能够更加容易和高效。对于InnoDB 类型表，主键列博阿含在二级索引中，所以对于具有较多二级索引的数据库结构，较短的主键能够节省相当的存储空间。
不要创建不必要的索引。索引能够加快查询，但是会影响插入和更新操作。如果对于一个表经常操作基于特定组合条件的查询，那么创建一个基于组合条件列的索引回避创建基于每个列的单个索引要高效。索引包含列定义顺序应该根据条件列的使用频度设置。如果经常使用不同的组合条件列查询，那么索引第一部分应该设置为所有可能条件组合交集最多的那个列。
对于存储较长字符串的列，如果列值特定长度前缀比较具有选择性，那么则比较适合设置前缀索引。索引越短执行越快，不仅因为索引占用的空间更少，同时也提高了索引缓存命中率，从而减少了磁盘读取。
合理的分表，特别对于动态格式表。
不同表的相同的列设置相同的配置，以便于加快联合查询的速度。
使用简洁的列名。通用列名使用。同时也能简化查询语句。为了保持不同数据库服务器间的可移植性。考虑保持列名大小小于18个字符。
通常来说，保持数据的非冗余性（第三范式）。为了避免不同表中同时使用一些值较长的列，可以将这些属性单独放置于表中，并赋予特定的短ID，然后其它查询可以通过此ID关联查询。
有时候空间换时间也有必要。

索引

联合查询

规范化

优化MySQL 数据类型

Numeric 数据优化

对于唯一的IDs 或者其它既可以使用string类型也可以使用numbers类型的列，优先使用numeric 类型。因为较大的数值型值可以使用较少的字节位进行排序，所以在传输和比较上需要较少的内存，并且执行更快。
数值类型的列，在很多情况下访问将更快，存储也更紧凑。同时，应用中，因为避免了转换的过程，也是的应用更加简洁。

Character 和 String 类型优化

优化指引：

使用二进制排序规则进行快速比较和排序。.
对于列比较，最好设置为形同的字符集和排序规则，避免查询时的字符串转换。
对于小于8KB 的列，使用二进制的VARCHAR 类型。GROUP BY 和 ORDER BY 会使用临时表，如果表中不含BLOB 列，那么临时表就可以使用MEMORY 存储引擎。
根据表不同列的使用频率合理划分表。When MySQL 读取行数据时，它会读取包含一行所有数据（或者多行（相关联行））的数据块。设计表只存储最常用的少数列，使得读取数据时，一个数据块能够包含更多的数据行，从而减少磁盘I/O和内存使用。
如果使用随机生成的值作为InnoDB 表主键，最好前置一些自增的值，如当前的时间戳等。当连续的主键物理上也临近存储，那么InnoDB 可以更快的查询和查询。
数值类型优先使用。

BLOB 类型优化

当需要排序包含文本数据的大对象列时，可以考虑首先压缩它。但是当整个表使用InnoDB 或者 MyISAM.压缩时，则要避免此操作。
可以考虑将包含的BLOB 类型列分离开来，单独存放，如有需要，可以联合查询。
因为BLOB类型的数据读取对服务器的需求不同，可以考虑将专门包含BLOB类型列的表放置在不同的存储设备上，或者另外的数据库中。如，BLOB读取需要大量连续的磁盘读取，那么使用传统的磁盘存储会比SSD设备更合适。
如果可以用二进制VARCHAR 类型列替代BLOB，那么优先使用。
如果需要对包含长文本的列进行等值比较，可以额外存储一列存储长文本列的HASH值，并在其上创建索引，然后使用此列进行等值查询。(常用hash函数： MD5() 或者 CRC32()) ，因为hash函数可能产生重复的值，所以查询仍然要包含 AND blob_column = long_string_value 条件限制，来应对这种情况。Has列，存储更短，更容易遍历。

多表优化

一些针对单个查询的优化手段涉及分表操作，但是当表的数量逐渐增多，涉及多表查询的优化问题则是另一个需要考虑的问题。

是如何打开和关闭表的

mysqladmin status执行如下：

Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

如上述 Open table输出：

MySQL 是多线程的，同一时间可能有多个客户端同时访问一个表。为了减少并发访问的问题，每个并发的会话会独立的打开表，这样虽然增加了内存消耗，但是在性能上却有了很大的提升。对于MyISAM 类型表，每个会话打开一个表则需要额外的一个文件句柄。（其它会话共享表的文件句柄）

服务器能够使用的最大文件句柄数量受table_open_cache 和 max_connections 系统变量影响。更改任何一个可以增大这种限制数量。大多数操作系统允许修改文件句柄限制，方法各有不同。

table_open_cache 和 max_connections是关联的。例如，对于200个并发连接访问，设置一个至少200 * N, 的表缓存（N代表联合查询中涉及的最多表数量），则同时需要增加设置相应的文件句柄以供临时表和文件使用。

确保操作系统能够处理table_open_cache 相关设置的句柄数。如果 table_open_cache 设置太高， MySQL可能会消耗完系统文件句柄，提示拒绝连接或者无法执行查询。

另外需要注意的是MyISAM 存储引擎打开一张表需要两个文件句柄，如果涉及分区，那么打开每一个分区需要两个文件句柄，因此打开一个分区MyISAM表，需要分区数*2 个文件句柄。可以在使用mysqld 启动时添加 --open-files-limit 选项，设置可用句柄数。

打开表需要的缓存数控制在 table_open_cache 设置。服务器启动时会自动设置打开表缓存大小，如果要明确设置其大小，则需要在启动服务时设置table_open_cache系统变量。MySQL可能会临时打开超过设定允许的表来执行查询。

MySQL在以下情况下会关闭已打开不再使用的表：

缓存已满，有线程尝试打开不再缓存中的表。
缓存超过table_open_cache 设定，并且缓存中存在不再使用的表。
表刷新操作执行时。当FLUSH TABLES 语句，或者mysqladmin flush-tables or mysqladmin refresh 命令被执行。

表缓存满时，服务器会使用以下策略来定位使用缓存表：

当前未使用的表会被释放掉，lru原则。
如果有新表需要打开，但是当前缓存已满，且没有表能够释放，那么缓存会被临时扩展。这种状态下，如果有表开始处于未被使用的状态，则会被释放。

MyISAM 表对于每次并发访问都需要打开一次，也即，无论是多个线程并发访问表，或者是同一个线程一个查询中访问多次，表都需要多次打开（例如，表和自身进行关联）。每一个并发打开表都需要占用表缓存空间一个条目。每个除此打开的MyISAM 表，需要占用两个文件句柄，一个用于数据文件，一个用于索引文件。之后的每个使用都只需要一个用于数据文件的文件句柄。索引文件句柄共享使用。

注意HANDLER tbl_name OPEN 语句操作。

检查表缓存大小，可以通过查看 Opened_tables 状态变量（服务器开始后执行过的表打开次数）。如下：

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

如果此值增加的特别快，即使为执行许多 FLUSH TABLES 操作，那么应该在服务器启动时增加table_open_cache 的值。

同一个数据库中表过多的缺点

如果数据库中MyISAM 表过多，打开，关闭和创建表操作会变得很慢，当执行多表查询时，可能会发生表缓存溢出。

MySQL内部临时表

一些情况下，服务器会使用临时表执行查询，临时表的使用不受用户控制。

使用临时表情景如下：

UNION 语句。
视图的一些操作（使用临时表算法，union及聚合）。
衍生查询使用（from后使用查询返回）。
子查询或者半连接（semi-join：in或者exist）。
语句包含 ORDER BY 及使用不同列的 GROUP BY 。或者联合查询中 ORDER BY 或者 GROUP BY 使用了非第一个联合表的列。
DISTINCT 语句结合 ORDER BY 可能使用临时表。
对于有SQL_SMALL_RESULT 标志的语句，会使用内存临时表，除非语句包含需要使用磁盘存储的。
INSERT ... SELECT 语句，从同一表中查询结果然后插入统一表中。服务器会将查询获得结果放入临时表以供插入使用。.
多表更新语句。
GROUP_CONCAT() 或者 COUNT(DISTINCT) 表达式。

查看是否使用了临时表，可以通过执行计划观察Extra 列是否包含Using temporary 来判断。执行计划对于衍生表及物化表有时候不明确指出Using temporary 信息。

服务器创建临时表（内存或者磁盘）后，会增加Created_tmp_tables 状态值，当创建磁盘临时表（直接创建或者从内存临时表转换）时，会增加Created_tmp_disk_tables 状态值。

一些情景下，服务器会避免使用内存临时表而使用磁盘临时表：

表中含有BLOB 或者 TEXT 类型列。包括用户自定义存储字符串值的类型，根据存储的值类型（二进制字符串，非二进制字符串），服务器会判断是否当作BLOB 或者 TEXT 类型列处理。
SHOW COLUMNS 和 DESCRIBE s语句展示使用BLOB 类型列。

满足一定条件的UNION 操作将不会使用临时表。相反，只会保留临时表创建的数据结构，用于执行结果类型转换。表没有完全的实例化，没有行写入，也没行读取，查询的数据行直接返回到客户端。查询结果减少了内存和磁盘需求，及相应延迟。执行计划及优化器输出反映了这一策略执行。

UNION不使用临时表的情景：

UNION ALL，而不是 UNION or UNION DISTINCT.
非全局ORDER BY 条件。
UNION操作非语句中的顶层块。语句包括{INSERT | REPLACE} ... SELECT ... 。

内部临时表存储引擎

内存临时表由MEMORY 存储引擎处理。磁盘临时表由InnoDB 或者MyISAM 存储引擎处理。

当创建的内存临时表变得过大，MySQL会自动的将其转化为磁盘临时表。转化阈值由tmp_table_size 或者 max_heap_table_size 值控制（取两者较小值）。区别于专门建立的MEMORY 类型表，MEMORY 类型表只受 max_heap_table_size 变量控制，并且不会转换为磁盘存储表。

internal_tmp_disk_storage_engine 变量控制处理磁盘临时表的存储引擎。包括INNODB(默认) 和MYISAM。

Note

当设置 internal_tmp_disk_storage_engine=INNODB，查询执行过程中生成的磁盘临时表超过了InnoDB 的行或者列限制，服务器会返回Row size too large 或者 Too many columns 错误。实际应用中通常将 internal_tmp_disk_storage_engine 设置为 MYISAM。