时间戳

首页 > 解决方案 > mysql数据的物理磁盘重写

mysql - mysql数据的物理磁盘重写

问题描述

我多年来第一次使用 mysql 来帮助朋友。问题：使用 INT 和 CHAR 值更新很多的 mysql 表。此 Web 应用程序站点托管在大型通用提供商上，因此我无法直接控制设置/参数/等。这张表的性能变得非常非常糟糕，以至于处理一个最多需要 10 秒的数据页有时需要 15 分钟。

我最初尝试将所有更新作为单个事务运行，而不是在网络应用程序的 php 循环中运行 50 条语句（几年前编写的）。问题，至少我认为，这个应用程序运行在一个带有许多其他通用网站的巨型 mysql 实例上，并且磁盘速度无法处理这么多的更新。

我可以在此提供程序上使用 chron/batch 作业。网络应用程序主要在工作时间使用，所以我可以限制在夜间访问网络应用程序。

我通常使用 postgresql 或 ms sql server，所以我对 mysql 的了解相当有限。

如果我强制删除表并在一夜之间重写，性能会提高吗？有没有类似postgres的vacuum之类的mysql功能？我曾尝试搜索信息，但不幸的是，使用 rewrite table 之类的词只会引用 sql 语法助手或性能调整。

或者，我想我可以在 mysql 中创建一个新的存储机制，只要它可以通过 php 脚本完成。对于经常更新的东西，会有比默认存储引擎更好的存储模式吗？

标签： mysql

解决方案

我在一个 Web 应用程序上工作，我们使用 mysql（它真的很棒！）来扩展非常大的数据。

除了@Lam Nguyen 在他的回答中所说的之外，还有几件事需要考虑，

  1. 检查您正在使用哪个 mysql 引擎，以查看它在select、insert、update. 要检查您在此处使用的引擎是一个示例查询，您可以使用它运行您的石蕊测试。

mysql> show table status where name="<your_table_name>";
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Login | InnoDB |      10 | Dynamic    |    2 |           8192 |       16384 |               0 |            0 |         0 |           NULL | 2019-04-28 12:16:59 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

mysql 安装附带的默认引擎是InnoDB. InnoDB 在插入行时不会获取任何锁。

  • SELECT ... FROM 是一致读取，读取数据库的快照并且不设置锁，除非事务隔离级别设置为 SERIALIZABLE。

  • 锁定读取、UPDATE 或 DELETE 通常会在 SQL 语句处理过程中扫描的每个索引记录上设置记录锁。

InnoDB 锁集

  1. 检查您正在索引的列。索引您真正会经常查询的列。避免索引 char 列。

要检查表的哪些列被索引运行，

mysql> show index from BookStore2;
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table      | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Bookstore2 |          0 | PRIMARY        |            1 | ISBN_NO     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| Bookstore2 |          1 | SHORT_DESC_IND |            1 | SHORT_DESC  | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| Bookstore2 |          1 | SHORT_DESC_IND |            2 | PUBLISHER   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.03 sec)

  1. 不要对表中的大型数据集运行内部查询。要实际查看您的查询explain在您的查询上运行了什么并查看迭代的行数

mysql> explain select * from login;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | login | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

  1. 避免加入太多的表。
  2. 确保您使用标准中的主键进行查询，或者至少您正在查询索引列。
  3. 当您的表变得太大时，请确保将其拆分为多个集群。

通过一些调整，我们仍然能够在最短的时间内获得查询结果。

推荐阅读