MySQL setup_instruments中关于部分信息不能修改

柔情只为你懂 2023-06-21 12:26 18阅读 0赞

**导读**：

> 作者：高鹏（网名八怪），《深入理解MySQL主从原理32讲》系列文的作者。

**想阅读八怪源码文章欢迎订阅**

![format_png][]

--------------------

**本文建议横屏观看，效果更佳**

朋友告诉我如下操作不能修改

mysql> update setup_instruments set enabled='no' where name='memory/performance_schema/table_handles';
    Query OK, 1 row affected (2.61 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    mysql> select * from setup_instruments  where name='memory/performance_schema/table_handles';
    +-----------------------------------------+---------+-------+
    | NAME                                    | ENABLED | TIMED |
    +-----------------------------------------+---------+-------+
    | memory/performance_schema/table_handles | YES     | NO    |
    +-----------------------------------------+---------+-------+
    1 row in set (0.00 sec)

我测试发现所有memory/performance\_schema/\* 的值都不能更改，但是其他值可以更改。8.0.17依然如此。

既然不能修改则跟一下update接口，我一共跟踪了：

*  table\_setup\_instruments::update\_row\_values：修改接口
 *  table\_setup\_instruments::make\_row：update\_enabled 变量传入值  
    
 *  table\_setup\_instruments::rnd\_next()：update\_enabled 定义值

几个接口。

**一、为什么不能修改**

查看table\_setup\_instruments::update\_row\_values函数你会发现memory/performance\_schema/\* 这几行值这里都会进入如下逻辑：

case 1: /* ENABLED */
            /* Do not raise error if m_update_enabled is false, silently ignore. */
            if (m_row.m_update_enabled) //这里是 false
            {
              value= (enum_yes_no) get_field_enum(f);
              m_row.m_instr_class->m_enabled= (value == ENUM_YES) ? true : false;
            }
            break;

因为m\_row.m\_update\_enabled==false 因此不能修改。其他的值这里是true。这里我们也会看到实际上值只有两个YES或者是NO，不能是其他值。如果update修改为其他值会直接报错。

**二、mupdateenabled来源**

也就是table\_setup\_instruments::rnd\_next()函数进行判断如果是VIEW\_BUILTIN\_MEMORY则会设置update\_enabled为false，具体如下：

case pos_setup_instruments::VIEW_BUILTIN_MEMORY: 
          update_enabled= false;//这里设置了false
          update_timed= false;
    ...

当然何为VIEW\_BUILTIN\_MEMORY，不太清楚，没仔细看了。

最后本表访问是全表扫描方式。因为上层接口为handler::ha\_rnd\_next，其含义为如下：

The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
    源码函数解释：Reads the next row in a table scan (also used to read the FIRST row in a table scan).
    全表扫描访问下一条数据

debug会发现不断的会访问下一条数据。最后performance\_schema是一个独立的引擎，虽然很简单。

**三、备用栈帧**

**1、修改数据**

#0  PFS_engine_table::update_row (this=0x7ffe7c1026c0, table=0x7ffe7c1b0370, old_buf=0x7ffe7c1b13f8 "'", new_buf=0x7ffe7c1b1270 "'", fields=0x7ffe7c1b1580)
       at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/pfs_engine_table.cc:573
    #1  0x0000000001942680 in ha_perfschema::update_row (this=0x7ffe7c1b0d70, old_data=0x7ffe7c1b13f8 "'", new_data=0x7ffe7c1b1270 "'")
       at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/ha_perfschema.cc:293
    #2  0x0000000000f90b70 in handler::ha_update_row (this=0x7ffe7c1b0d70, old_data=0x7ffe7c1b13f8 "'", new_data=0x7ffe7c1b1270 "'")
       at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:8509
    #3  0x000000000168ca00 in mysql_update (thd=0x7ffe7c012940, fields=..., values=..., limit=18446744073709551615, handle_duplicates=DUP_ERROR,
       found_return=0x7fffec0f4bd8, updated_return=0x7fffec0f4bd0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:887
    #4  0x0000000001692f28 in Sql_cmd_update::try_single_table_update (this=0x7ffe7c008f78, thd=0x7ffe7c012940, switch_to_multitable=0x7fffec0f4c7f)
       at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:2896
    #5  0x0000000001693475 in Sql_cmd_update::execute (this=0x7ffe7c008f78, thd=0x7ffe7c012940) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:3023
    #6  0x00000000015cc8e9 in mysql_execute_command (thd=0x7ffe7c012940, first_level=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756
    #7  0x00000000015d30c6 in mysql_parse (thd=0x7ffe7c012940, parser_state=0x7fffec0f6600) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
    #8  0x00000000015c6c5a in dispatch_command (thd=0x7ffe7c012940, com_data=0x7fffec0f6d70, command=COM_QUERY)
       at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490

**2、读取数据**

#0  table_setup_instruments::make_row (this=0x7ffe7c1026c0, klass=0x2f2e3c0, update_enabled=true, update_timed=true)
        at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/table_setup_instruments.cc:260
    #1  0x00000000019a4b1f in table_setup_instruments::rnd_next (this=0x7ffe7c1026c0)
        at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/table_setup_instruments.cc:172
    #2  0x0000000001942ab2 in ha_perfschema::rnd_next (this=0x7ffe7c1b0d70, buf=0x7ffe7c1b1270 "")
        at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/perfschema/ha_perfschema.cc:351
    #3  0x0000000000f83812 in handler::ha_rnd_next (this=0x7ffe7c1b0d70, buf=0x7ffe7c1b1270 "") at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/handler.cc:3146
    #4  0x00000000014e2b3d in rr_sequential (info=0x7fffec0f4870) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/records.cc:521
    #5  0x000000000168c7b3 in mysql_update (thd=0x7ffe7c012940, fields=..., values=..., limit=18446744073709551615, handle_duplicates=DUP_ERROR,
        found_return=0x7fffec0f4bd8, updated_return=0x7fffec0f4bd0) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:811
    #6  0x0000000001692f28 in Sql_cmd_update::try_single_table_update (this=0x7ffe7c008f78, thd=0x7ffe7c012940, switch_to_multitable=0x7fffec0f4c7f)
        at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:2896
    #7  0x0000000001693475 in Sql_cmd_update::execute (this=0x7ffe7c008f78, thd=0x7ffe7c012940) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_update.cc:3023
    #8  0x00000000015cc8e9 in mysql_execute_command (thd=0x7ffe7c012940, first_level=true) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3756
    #9  0x00000000015d30c6 in mysql_parse (thd=0x7ffe7c012940, parser_state=0x7fffec0f6600) at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
    #10 0x00000000015c6c5a in dispatch_command (thd=0x7ffe7c012940, com_data=0x7fffec0f6d70, command=COM_QUERY)
        at /root/mysqlall/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490

--------------------

END

**识别下方二维码添加作者为好友**

![format_png 1][]

![分割线箭头动态][format_png 2]

扫码加入MySQL技术Q群

**（群号：****650149401）**

![format_png 3][]

点“在看”给我一朵小黄花![format_png 4][]

![format_png 5][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy83aDRpY0o0aWNzSkJqVlFpYUFvNkxhckN4RHpjY3VEOWVRbWd2akZpYWlhSTBuTHA5bnh0WllFcXhHbGFPanR5MnkxMmhjckx5WHE1MWVpY1R5V0FiSGhjaWJFREEvNjQwP3d4X2ZtdD1wbmc?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy83aDRpY0o0aWNzSkJqUHdoR1lzZU1hOVNkY3pSUjZGUDExWlFRYmdpYjA3ZnVQaWFLYTFCWk4xTTlRQ3NHNkw5MnpFOEt1a0VlVjd2aWNCRHV1aWFkUTdWZDczZy82NDA_d3hfZm10PXBuZw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi83aDRpY0o0aWNzSkJnOUVoVzhhWFlzZ0FpY2xQMWljbFFpY1hVc09Wa0paSlREUUNOSlBjV0tDUXJUcWNUeHFpY0FDZWMwbzBXVWlidlp5T1FRdkVPdlQ1MjVuQXcvNjQwP3d4X2ZtdD1naWY?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy83aDRpY0o0aWNzSkJqZHlLNEFBWDhDcWhuSkluc0szOGlheGpiaWI5NGF4SlA0Y3NXaWJUdDRwNGJuM2p6SFNLY3RHV3AxaWFpYnIxaWFGVUtiMU9rMUJJbFJ3UmpBLzY0MD93eF9mbXQ9anBlZw?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X2dpZi83aDRpY0o0aWNzSkJnZUQyQ2pNaWJ0VThQRXROcW5wSGFpY3FKZHlIUFE0VWxBa1Y4aWF6b1BsRDJITEFOaWFZMWNMN2pVOUtuekhiNFVPNGszOThLazBUdDViQS82NDA_d3hfZm10PWdpZg?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL3N6X21tYml6X3BuZy83aDRpY0o0aWNzSkJnZUQyQ2pNaWJ0VThQRXROcW5wSGFpY3FqSGtXWHBPcGttZG5HMlFXSWxPbjJVeE9KUkNFdDNTVWdGY1FiVTlvNFdHc1p2OTVvRGhZMVEvNjQwP3d4X2ZtdD1wbmc?x-oss-process=image/format,png