存储架构优化的那些事(四)

作者: 勤_ | 来源:发表于2021-09-02 10:30 被阅读0次

mysql.jpeg

概述

上文我们具体的深入Mysql 索引优化的相关方案，本文将深入Mysql 数据结构优化的方案。

初始化脚本

通过如下脚本，在mysql数据库中新建对应的实验表和数据。

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)
  ENGINE = INNODB
  DEFAULT CHARSET = utf8;

INSERT INTO user_info (NAME, age) VALUES ('xys', 20);
INSERT INTO user_info (NAME, age) VALUES ('a', 21);
INSERT INTO user_info (NAME, age) VALUES ('b', 23);
INSERT INTO user_info (NAME, age) VALUES ('c', 50);
INSERT INTO user_info (NAME, age) VALUES ('d', 15);
INSERT INTO user_info (NAME, age) VALUES ('e', 20);
INSERT INTO user_info (NAME, age) VALUES ('f', 21);
INSERT INTO user_info (NAME, age) VALUES ('g', 23);
INSERT INTO user_info (NAME, age) VALUES ('h', 50);
INSERT INTO user_info (NAME, age) VALUES ('i', 15);

CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50) NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
  ENGINE = INNODB
  DEFAULT CHARSET = utf8;

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p199', 'WHH99');

-- 验证分区 
CREATE TABLE user_temp (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(20),
  PRIMARY KEY (id)
)ENGINE = INNODB PARTITION BY KEY (id) PARTITIONS 3;

INSERT INTO user_temp VALUES(1,"hello") ;
INSERT INTO user_temp VALUES(2,"world") ;
INSERT INTO user_temp VALUES(3,"nice") ;

DROP PROCEDURE IF EXISTS batchInsert; 

-- 批量插入数据
DELIMITER //
CREATE PROCEDURE batchInsert()
BEGIN
    DECLARE num INT; 
    SET num=20;
    WHILE num<=100000 DO
        INSERT INTO user_info (id, NAME, age) VALUES (num ,CONCAT('xys',num) , 20);
        INSERT INTO order_info (user_id, product_name, productor) VALUES (num, CONCAT('p',num) , CONCAT('WHH',num));
        SET num=num+1;
    END WHILE;
END
// 
DELIMITER ; #恢复;表示结束
CALL batchInsert;

SELECT COUNT(1) FROM `user_info`;
SELECT COUNT(1) FROM `order_info`;

-- user_info增加一列
ALTER TABLE user_info ADD COLUMN `oid` BIGINT(20);
UPDATE user_info u , order_info o SET u.oid = o.user_id WHERE o.user_id = u.id ;

ALTER TABLE order_info ADD COLUMN `description` VARCHAR(255) ;

-- Index Prefixes
CREATE TABLE test(blob_col BLOB);
SHOW VARIABLES LIKE 'InnoDB_large_prefix';
SET GLOBAL InnoDB_large_prefix=off;
ALTER TABLE test ADD INDEX idx_blob_col(blob_col(7600));

-- 
CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = INNODB;

INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');

优化表结构

Optimizing Data Size

设计表以最小化它们在磁盘上的空间。这可以减少写入磁盘和从磁盘读取数据的数量，从而带来巨大的改进。较小的表通常需要较少的主内存，而它们的内容在查询执行期间正在被积极处理。表数据的任何空间缩减都会导致更小的索引，从而加快处理速度。

MySQL 支持许多不同的存储引擎(表类型)和行格式。对于每个表，您可以决定使用哪种存储和索引方法。为应用程序选择合适的表格格式可以大大提高性能。

通过使用下面列出的技术，您可以获得更好的表性能并最小化存储空间:
- Table Columns
  - 尽可能使用最有效(最小)的数据类型。MySQL 有许多专门的类型，可以节省磁盘空间和内存。例如：MEDIUMINT 通常是比 INT 更好的选择，因为 MEDIUMINT 列使用的空间要少25%
  - 如果可能，将列声明为 notnull。通过更好地使用索引和消除测试每个值是否为 NULL 的开销，它使 SQL 操作更快。还节省了一些存储空间，每列一位。
- Row Format
  - 默认情况下，InnoDB 表使用 DYNAMIC 行格式创建。若要使用 DYNAMIC 以外的行格式，请配置 innodb _ default _ ROW _ format，或在 CREATE TABLE 或 ALTER TABLE 语句中显式指定 ROW _ format 选项。紧凑的行格式系列(包括 COMPACT、 DYNAMIC 和 COMPRESSED)以增加某些操作的 CPU 使用为代价，减少了行存储空间。如果工作负载典型地受到缓存命中率和磁盘速度的限制，那么它可能会更快。如果出现 CPU 速度受限的情况，那么它可能会慢一些。当使用可变长度的字符集(如 utf8mb3或 utf8mb4)时，紧凑的行格式系列还优化了 CHAR 列存储。如果 ROW _ format = REDUNDANT，CHAR (n)占据 n × 字符集的最大字节长度。许多语言可以主要使用单字节 utf8字符编写，因此固定的存储长度往往会浪费空间。使用紧凑的行格式系列，InnoDB 通过删除尾随空格，在 n 到 n × 这些列的字符集的最大字节长度的范围内分配可变量的存储空间。最小存储长度为 n 字节。
  - 对于 MyISAM 表，如果没有任何可变长度的列(VARCHAR、 TEXT 或 BLOB 列) ，则使用固定大小的行格式。这样更快，但可能会浪费一些空间。
- Indexes
  - 表的主索引应该尽可能短。这使得识别每一行变得简单而有效。对于 InnoDB 表，主键列在每个辅助索引条目中都是重复的，因此如果有许多辅助索引，一个简短的主键可以节省相当大的空间。
  - 只创建提高查询性能所需的索引。索引有利于检索，但会减慢插入和更新操作的速度。
  - 如果主要通过对列的组合进行搜索来访问表，那么可以在这些列上创建单个复合索引，而不是为每个列创建单独的索引。
  - 索引的第一部分应该是最常用的列。如果在从表中进行选择时总是使用多列，那么索引中的第一列应该是重复次数最多的那一列，以便更好地压缩索引。
  - 如果长字符串列的第一个字符数很可能有唯一的前缀，那么最好只索引这个前缀，使用 MySQL 的支持在列的最左边创建一个索引。
Optimizing MySQL Data Types
- 对于可以表示为字符串或数字的唯一id或其他值，更支持使用numeric columns而不是字符串列。由于较大的数值可以比相应的字符串以更少的字节存储，因此传输和比较它们所需的内存更少，速度更快。
- 在比较不同列的值时，尽可能使用相同的字符集和排序规则声明这些列，以避免在运行查询时进行字符串转换。
- 对于大小小于8KB 的列值，使用二进制 VARCHAR 而不是 BLOB。Groupby 和 orderby 子句可以生成临时表，如果原始表不包含任何 BLOB 列，则这些临时表可以使用 MEMORY 存储引擎。
- 如果表包含诸如 name 和 address 之类的字符串列，但是许多查询不检索这些列，那么可以考虑将字符串列分割成一个单独的表，并在必要时使用带外键的连接查询。当 MySQL 从一行中检索任何值时，它会读取一个数据块，其中包含该行的所有列(可能还有其他相邻行)。保持每一行较小，只使用最常用的列，允许在每个数据块中填充更多的行。这样的压缩表减少了常见查询的磁盘 i/o 和内存使用。
- 当你在 InnoDB 表中使用一个随机生成的值作为主键时，如果可能的话，在它前面加上一个升序值，比如当前的日期和时间。当连续的主值物理上相邻存储时，InnoDB 可以更快地插入和检索它们。
- 当存储包含文本数据的大型 blob 时，请考虑首先压缩它。
- 对于具有多个列的表，为了减少不使用 BLOB 列的查询的内存需求，可以考虑将 BLOB 列拆分为一个单独的表，并在需要时使用联接查询来引用它。
- Analysis ()检查查询结果，并返回对结果的分析，该分析为每个列提供最佳数据类型，这可能有助于减少表大小。MySQL 5.7.18中已被弃用，并在 MySQL 8.0中被删除。
```
SELECT * FROM order_info PROCEDURE ANALYSE(10, 2000);
```
  image-20210830155532178.png
Optimizing for Many Tables

MySQL 是多线程的，因此可能有许多客户机同时为给定的表发出查询。为了最小化多个客户端会话在同一个表上具有不同状态的问题，每个并发会话都独立地打开该表。这会使用额外的内存，但通常会提高性能。对于 MyISAM 表，每个打开该表的客户机的数据文件都需要一个额外的文件描述符。table_open_cache、max_connections 系统变量影响服务器保持打开的文件的最大数量。如果您增加了这些值中的一个或两个，您可能会遇到操作系统对每个进程打开的文件描述符数量强加的限制。许多操作系统允许您增加打开文件的限制，尽管不同系统的方法差别很大。例如，对于200个并发运行的连接，指定一个至少为200 * n 的表缓存大小，其中 n 是您执行的任何查询中每个联接的最大表数。您还必须为临时表和文件保留一些额外的文件描述符。
```
FLUSH TABLES;
SHOW GLOBAL VARIABLES LIKE '%table_open_cache%';
SHOW GLOBAL STATUS LIKE 'Opened_tables';
```
Internal Temporary Table Use in MySQL

在某些情况下，服务器在处理语句时创建内部临时表。用户无法直接控制何时发生这种情况。要确定一个语句是否需要一个临时表，使用 EXPLAIN 并检查 Extra 列以查看它是否显示为 Using temporary。有些查询条件阻止使用内存中的临时表，在这种情况下，服务器将使用磁盘上的表:
- 表中存在 BLOB 或 TEXT 列。
- 如果使用 UNION 或 UNION ALL，则 SELECT 列表中存在最大长度大于512(二进制字符串为字节，非二进制字符串为字符)的任何字符串列。
内部临时表可以保存在内存中，由 MEMORY 存储引擎处理，或者由 InnoDB 或 MyISAM 存储引擎存储在磁盘上。如果内部临时表是作为内存中表创建的，但变得太大，MySQL 会自动将其转换为磁盘上表。内存中临时表的最大大小由 tmp _ table _ size 或 max_heap _ table _ size 值定义，以较小者为准。

当在内存或磁盘上创建内部临时表时，服务器会增加 Created_tmp_tables 值。当在磁盘上创建内部临时表时，服务器会增加 Created_tmp_disk_tables 的值。如果在磁盘上创建了太多的内部临时表，请考虑增加 tmp_table_size 和 max_heap_table_size 设置。
```
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL VARIABLES LIKE '%tmp_table_size%';
SHOW GLOBAL VARIABLES LIKE '%max_heap_table_size%';
SHOW GLOBAL VARIABLES LIKE 'internal_tmp_disk_storage_engine'; -- 磁盘上的临时表由 InnoDB 或 MyISAM 存储引擎管理
```

参考

Optimizing Database Structure

存储架构优化的那些事(四)

概述

初始化脚本

优化表结构

参考

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读