The predicate columns feature relies on tidb_persist_analyze_options

[Rustin170506]

Enhancement

In TiDB we have a global variable called tidb_persist_analyze_options, the default value is true, and it will help us to store the analyze options in the system table mysql.analyze_options.
We know that the Predicate Columns collection feature relies on this system to store the column selection. So if we set tidb_persist_analyze_options to false, the predicate column feature won't work anymore. Every time you need to explicitly ANALYZE TABLE table_name PREDICATE COLUMNS to make sure TiDB only analyses the predicate columns, otherwise auto-analyze it will keep analysing all columns.

Test Result

1. Start TiDB cluster: tiup playground v8.0.0
2. Enable tidb_enable_column_tracking:

mysql> set global tidb_enable_column_tracking=1;
Query OK, 0 rows affected (0.02 sec)

mysql> select @@tidb_enable_column_tracking;
+-------------------------------+
| @@tidb_enable_column_tracking |
+-------------------------------+
|                             1 |
+-------------------------------+

3. Disable tidb_persist_analyze_options

mysql> set global tidb_persist_analyze_options=0;
Query OK, 0 rows affected (0.02 sec)

mysql> select @@tidb_persist_analyze_options;
+--------------------------------+
| @@tidb_persist_analyze_options |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

4. Create a table and insert 1 row:
   ``sql
   create table t(a int);
   insert into t values(1);

5. Select the table:
```sql
select * from t where t.a > 1;

6. Wait for 5m:

mysql> select * from mysql.column_stats_usage;
+----------+-----------+---------------------+------------------+
| table_id | column_id | last_used_at        | last_analyzed_at |
+----------+-----------+---------------------+------------------+
|      104 |         1 | 2024-04-18 16:47:09 | NULL             |
+----------+-----------+---------------------+------------------+
1 row in set (0.01 sec)

7. Analyze the table with predicate columns:
   ANALYZE TABLE t PREDICATE COLUMNS;
8. Check the analyze options:
   mysql> select * from mysql.analyze_options;
   Empty set (0.01 sec)
9. Analyze the table without predicate columns:
   ANALYZE TABLE t;
10. Check the analyzed jobs:

mysql> select * from mysql.analyze_jobs;
+----+---------------------+--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+
| id | update_time         | table_schema | table_name | partition_name | job_info                                                           | processed_rows | start_time          | end_time            | state    | fail_reason | instance       | process_id |
+----+---------------------+--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+
|  1 | 2024-04-18 16:50:39 | test         | t          |                | analyze table all columns with 256 buckets, 500 topn, 1 samplerate |              1 | 2024-04-18 16:50:39 | 2024-04-18 16:50:39 | finished | NULL        | 127.0.0.1:4000 |       NULL |
|  2 | 2024-04-18 16:53:13 | test         | t          |                | analyze table all columns with 256 buckets, 500 topn, 1 samplerate |              1 | 2024-04-18 16:53:13 | 2024-04-18 16:53:13 | finished | NULL        | 127.0.0.1:4000 |       NULL |
+----+---------------------+--------------+------------+----------------+--------------------------------------------------------------------+----------------+---------------------+---------------------+----------+-------------+----------------+------------+
2 rows in set (0.00 sec)