MySQL 怼了架构师关于 optimize table 需求 与 为什么反对 optimize table

时间:2024-01-22 17:49:01



MySQL  怼了架构师关于 optimize table 需求 与 为什么反对 optimize table_mysql

2024年1月某些星象的原因,导致我个人的星盘在1月大概率要和某些人要有不愉快。这不就来了,在一次关于mysql 数据库数据表清理后,关于optimize table 的问题上,我毫无悬念的和架构师们进行了一次非常不nice 的沟通。

随意就有了此篇的文章,因为我这个人比较的要通过实际的情况来说明问题,而不是用权威来压制,那样没有品。事情简单的说一下,几十个库,几百张表,要进行数据的清理,这没有问题,我们会使用自动的手段来进行,而后面一个架构师提出,希望能对表进行optimize table 的操作,并且提出这样的好处多多。 

但是,但是,但是,说话办事就怕光站在自己的角度来说问题,我对此要求是拒绝的,并且我拿出了 PG SQL SERVER ORACLE 甚至 IBM DB2 的一些理论对于对方无礼的需求进行驳斥,因为这个架构师提出,optimize table 很快,MYSQL8 有新功能,大致的意思我翻译一下,optimize table 的任何过程中,不会对表产生影响,产生锁,产生业务影响。因为我们是 7*24小时的业务,所以DB 对于表在一些操作的中,是非常忌讳,产生TABLE LOCK 并且是无预估的长时间的表不可用的情况。 

那么我们来看看到底MYSQL8  是否如这个架构师讲的,很快不会对业务有什么影响。

我们使用一个,说新不新 ,说旧不旧的MySQL数据库版本,8.031 来验证MYSQL8 对这个optimize table 并没有多少改进。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

1 我们验证当数据库有事务在操作数据表时,是否会对optimize table 的命令产生影响,也就是之间的操作是互斥的。结果可以参见下图,必然是互相影响。optimize table 被  一个对表进行数据插入的事务卡主了。

MySQL  怼了架构师关于 optimize table 需求 与 为什么反对 optimize table_架构师_02

mysql> select * from schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: test
                 object_name: test
           waiting_thread_id: 71
                 waiting_pid: 7
             waiting_account: db_admin@mysql830
           waiting_lock_type: SHARED_NO_READ_WRITE
       waiting_lock_duration: TRANSACTION
               waiting_query: optimize table test
          waiting_query_secs: 1245
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 81
                blocking_pid: 8
            blocking_account: db_admin@mysql830
          blocking_lock_type: SHARED_WRITE
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 8
sql_kill_blocking_connection: KILL 8
1 row in set (0.01 sec)

同时在genernal log 中跟踪相关操作,这里可以看到操作本身是没有分解操作的,原子性的语句进行传递,如有从库也会进行传递到从库执行。

MySQL  怼了架构师关于 optimize table 需求 与 为什么反对 optimize table_架构师_03

在MySQL 8 中越来越智能的sys 库中的lock_waits 视图可以清晰的看到,optimize table 到底上了什么锁,,那么这个锁是什么这个锁属于metadata_locks 中的其中一种

INTENTION_EXCLUSIVE, 

SHARED, 

SHARED_HIGH_PRIO, 

SHARED_READ, 

SHARED_WRITE, 

SHARED_UPGRADABLE,

 SHARED_NO_WRITE, 

SHARED_NO_READ_WRITE, 

EXCLUSIVE

那么我们在执行了这个操作后,出现什么情况,从下面的图中可以清晰的看到,我们在一个就只有1行的数据表中,进行了optimize table 的操作,然后我们毫无悬念的发现在执行完毕这个命令后,表的文件的日期更新了,这里可以证明,在8.031 版本的mysql 中与我们之前MYSQL 的版本对于optimize table 的原理毫无实质的变化。

+-----------+----------+----------+-------------------------------------------------------------------+
| Table     | Op       | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.test | optimize | status   | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
mysql> select * from test;
+----+--------------+--------------+
| id | name         | title        |
+----+--------------+--------------+
|  1 | å°æŽ       | å°æŽ       |
+----+--------------+--------------+
1 row in set (0.00 sec)
[mysql@mysql830 test]$ ll -ah
total 3.4G
drwxr-x---  2 mysql mysql  190 Nov 17 12:58 .
drwxr-x--- 11 mysql mysql 4.0K Nov 17 12:38 ..
-rw-r-----  1 mysql mysql 112K Apr 14  2023 app_user_1000.ibd
-rw-r-----  1 mysql mysql 3.4G Apr 14  2023 app_user.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 05:04 bm_card_account_recharge_
-rw-r-----  1 mysql mysql 144K Apr 15  2023 orders_copy.ibd
-rw-r-----  1 mysql mysql 160K Apr 15  2023 orders.ibd
-rw-r-----  1 mysql mysql 128K Apr 14  2023 payments.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 12:35 read_table.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 12:58 test.ibd
[mysql@mysql830 test]$ ll -ah
total 3.4G
drwxr-x---  2 mysql mysql  190 Nov 17 13:32 .
drwxr-x--- 11 mysql mysql 4.0K Nov 17 12:38 ..
-rw-r-----  1 mysql mysql 112K Apr 14  2023 app_user_1000.ibd
-rw-r-----  1 mysql mysql 3.4G Apr 14  2023 app_user.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 05:04 bm_card_account_recharge_ext.ibd
-rw-r-----  1 mysql mysql 144K Apr 15  2023 orders_copy.ibd
-rw-r-----  1 mysql mysql 160K Apr 15  2023 orders.ibd
-rw-r-----  1 mysql mysql 128K Apr 14  2023 payments.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 12:35 read_table.ibd
-rw-r-----  1 mysql mysql 112K Nov 17 13:32 test.ibd

当然同样的命令还有  alter table tablename force;和 alter table tablename engine=innodb;

那么我们来说说我们为什么要反对这个事情,

1 这么多数据库,这么多表,并且这些表其中有大表,操作这个部分的时间不可控,业务是不会等你操作完 optimize table 然后在去工作,他要anytime anywhere的运行,如果进行了optimize table DB 无法控制表不可用的时间,因为有些表里面N个索引,实际上这就是重建了一张表。然后改名的原子性操作。

2  操作具有风险性,如果此时由于大量运行optimize table 导致IOPS 上升,或者等待这些表的事务持续的等待,undo log 里面的数据无法及时进行purge,最终是否有可能导致数据库出现,基于探针判定主库不可用的问题,导致的数据库切换,这个问题的责任谁来负责。

3  一组数据库有从库,你的语句在此时会直接binlog 给从库,从库也会进行此操作,主库不可用,从库也不可用,并且统统的 UP ,到时我连切换的库都没有。(当然可以在执行时禁止BINLOG 传输此命令,避免从库一起来做相关的操作)

为什么要写此篇文章,因对一些开发人员和架构师的不负责任和缺乏职业素养的问题,非常憎恶。自己一句话,将别人至于尴尬和危险的境地,你于心何忍,你一句话别人要付出什么,这次我怼你算是轻的,下次我可以更狠,你以为做一个 DB 是光看看几篇文章就可以胜任的,笑话。

附群友的一些对此事的看法

MySQL  怼了架构师关于 optimize table 需求 与 为什么反对 optimize table_mysql_04

另DB 人员自己也的打铁自身重,如果你技不压人,你就只能被人家压!

最后,下面是关于这块的代码,alter table table name  engine= innodb; 

/// Set for ALTER [COLUMN] ... SET DEFAULT ... | DROP DEFAULT
    ALTER_CHANGE_COLUMN_DEFAULT = 1ULL << 8,

    /// Set for DISABLE KEYS | ENABLE KEYS
    ALTER_KEYS_ONOFF = 1ULL << 9,

    /// Set for FORCE
    /// Set for ENGINE(same engine)
    /// Set by mysql_recreate_table()
    ALTER_RECREATE = 1ULL << 10,

    /// Set for ADD PARTITION
    ALTER_ADD_PARTITION = 1ULL << 11,
Alter_info(const Alter_info &rhs, MEM_ROOT *mem_root);

  bool add_field(THD *thd, const LEX_STRING *field_name,
                 enum enum_field_types type, const char *length,
                 const char *decimal, uint type_modifier, Item *default_value,
                 Item *on_update_value, LEX_CSTRING *comment,
                 const char *change, List<String> *interval_list,
                 const CHARSET_INFO *cs, bool has_explicit_collation,
                 uint uint_geom_type, Value_generator *gcol_info,
                 Value_generator *default_val_expr, const char *opt_after,
                 std::optional<gis::srid_t> srid,
                 Sql_check_constraint_spec_list *check_cons_list,
                 dd::Column::enum_hidden_type hidden, bool is_array = false);

 private:
  Alter_info &operator=(const Alter_info &rhs);  // not implemented
  Alter_info(const Alter_info &rhs);             // not implemented
};

/** Runtime context for ALTER TABLE. */
class Alter_table_ctx {
 public:
  Alter_table_ctx();

  Alter_table_ctx(THD *thd, Table_ref *table_list, uint tables_opened_arg,
                  const char *new_db_arg, const char *new_name_arg);

  ~Alter_table_ctx();

  /**
     @return true if the table is moved to another database, false otherwise.
  */
  bool is_database_changed() const { return (new_db != db); }

  /**
     @return true if the table name is changed, false otherwise.
  */
  bool is_table_name_changed() const { return (new_name != table_name); }

  /**
     @return true if the table is renamed (i.e. its name or database changed),
             false otherwise.
  */
  bool is_table_renamed() const {
    return is_database_changed() || is_table_name_changed();
  }

  /**
     @return path to the original table.
  */
  const char *get_path() const {
    assert(!tmp_table);
    return path;
  }

MySQL  怼了架构师关于 optimize table 需求 与 为什么反对 optimize table_mysql_05