mirror of
https://github.com/MariaDB/server.git
synced 2025-04-22 23:25:37 +02:00
6e6a1b316c
stats_deinit(): Replaces dict_stats_deinit(). Deinitialize the statistics for persistent tables, so that they will be reloaded or recalculated on a subsequent ha_innobase::open(). ha_innobase::rename_table(): Invoke stats_deinit() so that the subsequent ha_innobase::open() will reload the InnoDB persistent statistics. That is, it will remain possible to have the InnoDB persistent statistics reloaded by executing the following: RENAME TABLE t TO tmp, tmp TO t; dict_table_close(table): Replaced with table->release(). There will no longer be any logic that would attempt to ensure that the InnoDB persistent statistics will be reloaded after FLUSH TABLES has been executed. This also fixes the problem that dict_table_t::stat_modified_counter would be frequently reset to 0, whenever ha_innobase::open() is invoked after the table reference count had dropped to 0. dict_table_close(table, thd, mdl): Remove the parameter "dict_locked". Do not try to invalidate the statistics. ha_innobase::statistics_init(): Replaces dict_stats_init(table). Reviewed by: Thirunarayanan Balathandayuthapani
139 lines
5 KiB
Text
139 lines
5 KiB
Text
CREATE TABLE t (
|
|
a INT,
|
|
b INT,
|
|
c INT GENERATED ALWAYS AS(a+b),
|
|
d INT GENERATED ALWAYS AS(a+b+b),
|
|
KEY idxa (a),
|
|
KEY vidxcd (c, d)
|
|
) ENGINE=INNODB STATS_PERSISTENT=1 STATS_AUTO_RECALC=1;
|
|
INSERT INTO t (a,b) VALUES (1, 2);
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxa n_diff_pfx01 a
|
|
idxa n_diff_pfx02 a,DB_ROW_ID
|
|
idxa n_leaf_pages Number of leaf pages in the index
|
|
idxa size Number of pages in the index
|
|
vidxcd n_diff_pfx01 c
|
|
vidxcd n_diff_pfx02 c,d
|
|
vidxcd n_diff_pfx03 c,d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
ALTER TABLE t ADD COLUMN e INT GENERATED ALWAYS AS(a+a+b), ADD INDEX idxb (b), ALGORITHM=INPLACE;
|
|
select count(*) from t;
|
|
count(*)
|
|
1
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxa n_diff_pfx01 a
|
|
idxa n_diff_pfx02 a,DB_ROW_ID
|
|
idxa n_leaf_pages Number of leaf pages in the index
|
|
idxa size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxcd n_diff_pfx01 c
|
|
vidxcd n_diff_pfx02 c,d
|
|
vidxcd n_diff_pfx03 c,d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
ALTER TABLE t DROP COLUMN c, DROP INDEX idxa, ALGORITHM=INPLACE;
|
|
select count(*) from t;
|
|
count(*)
|
|
1
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxcd n_diff_pfx01 d
|
|
vidxcd n_diff_pfx02 d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
ALTER TABLE t ADD INDEX vidxe (e), ALGORITHM=INPLACE;
|
|
select count(*) from t;
|
|
count(*)
|
|
1
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxcd n_diff_pfx01 d
|
|
vidxcd n_diff_pfx02 d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
vidxe n_diff_pfx01 e
|
|
vidxe n_diff_pfx02 e,DB_ROW_ID
|
|
vidxe n_leaf_pages Number of leaf pages in the index
|
|
vidxe size Number of pages in the index
|
|
ALTER TABLE t ADD COLUMN f INT GENERATED ALWAYS AS(a + a), ADD INDEX vidxf (f), ALGORITHM=INPLACE;
|
|
select count(*) from t;
|
|
count(*)
|
|
1
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxcd n_diff_pfx01 d
|
|
vidxcd n_diff_pfx02 d,DB_ROW_ID
|
|
vidxcd n_leaf_pages Number of leaf pages in the index
|
|
vidxcd size Number of pages in the index
|
|
vidxe n_diff_pfx01 e
|
|
vidxe n_diff_pfx02 e,DB_ROW_ID
|
|
vidxe n_leaf_pages Number of leaf pages in the index
|
|
vidxe size Number of pages in the index
|
|
vidxf n_diff_pfx01 f
|
|
vidxf n_diff_pfx02 f,DB_ROW_ID
|
|
vidxf n_leaf_pages Number of leaf pages in the index
|
|
vidxf size Number of pages in the index
|
|
ALTER TABLE t DROP INDEX vidxcd;
|
|
SELECT index_name, stat_name, stat_description
|
|
FROM mysql.innodb_index_stats
|
|
WHERE database_name = 'test' AND table_name = 't';
|
|
index_name stat_name stat_description
|
|
GEN_CLUST_INDEX n_diff_pfx01 DB_ROW_ID
|
|
GEN_CLUST_INDEX n_leaf_pages Number of leaf pages in the index
|
|
GEN_CLUST_INDEX size Number of pages in the index
|
|
idxb n_diff_pfx01 b
|
|
idxb n_diff_pfx02 b,DB_ROW_ID
|
|
idxb n_leaf_pages Number of leaf pages in the index
|
|
idxb size Number of pages in the index
|
|
vidxe n_diff_pfx01 e
|
|
vidxe n_diff_pfx02 e,DB_ROW_ID
|
|
vidxe n_leaf_pages Number of leaf pages in the index
|
|
vidxe size Number of pages in the index
|
|
vidxf n_diff_pfx01 f
|
|
vidxf n_diff_pfx02 f,DB_ROW_ID
|
|
vidxf n_leaf_pages Number of leaf pages in the index
|
|
vidxf size Number of pages in the index
|
|
DROP TABLE t;
|