mysql function setup_MySQL setup_instruments中关于部分信息不能修改

原标题：MySQL setup_instruments中关于部分信息不能修改

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

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

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

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修改为其他值会直接报错。

二、m updateenabled来源

也就是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

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

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

#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

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

#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

责任编辑：