Skip to content

Commit 1474bc0

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[Feature] Issue#20 ADD TABLE/INDEX STATISTICS
Description: ------------ Table statistics provides direct hint information for business logic split and stress analysis; it consists of rows_inserted, rows_updated, rows_deleted, rows_changed, and rows_read Index statistics provides hint information to improve performance of range query, point query, or join; as far as index evaluation concerned, the most important thing is rows_read
1 parent 68d6a67 commit 1474bc0

23 files changed

Lines changed: 916 additions & 11 deletions

‎mysql-test/r/information_schema.result‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -84,6 +84,8 @@ TABLE_PRIVILEGES
8484
TRIGGERS
8585
USER_PRIVILEGES
8686
VIEWS
87+
TABLE_STATISTICS
88+
INDEX_STATISTICS
8789
columns_priv
8890
db
8991
event
@@ -124,6 +126,7 @@ TABLESPACES TABLESPACES
124126
TABLE_CONSTRAINTS TABLE_CONSTRAINTS
125127
TABLE_PRIVILEGES TABLE_PRIVILEGES
126128
TRIGGERS TRIGGERS
129+
TABLE_STATISTICS TABLE_STATISTICS
127130
tables_priv tables_priv
128131
time_zone time_zone
129132
time_zone_leap_second time_zone_leap_second
@@ -144,6 +147,7 @@ TABLESPACES TABLESPACES
144147
TABLE_CONSTRAINTS TABLE_CONSTRAINTS
145148
TABLE_PRIVILEGES TABLE_PRIVILEGES
146149
TRIGGERS TRIGGERS
150+
TABLE_STATISTICS TABLE_STATISTICS
147151
tables_priv tables_priv
148152
time_zone time_zone
149153
time_zone_leap_second time_zone_leap_second
@@ -164,6 +168,7 @@ TABLESPACES TABLESPACES
164168
TABLE_CONSTRAINTS TABLE_CONSTRAINTS
165169
TABLE_PRIVILEGES TABLE_PRIVILEGES
166170
TRIGGERS TRIGGERS
171+
TABLE_STATISTICS TABLE_STATISTICS
167172
tables_priv tables_priv
168173
time_zone time_zone
169174
time_zone_leap_second time_zone_leap_second
@@ -657,6 +662,7 @@ TABLESPACES
657662
TABLE_CONSTRAINTS
658663
TABLE_PRIVILEGES
659664
TRIGGERS
665+
TABLE_STATISTICS
660666
create database information_schema;
661667
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
662668
use information_schema;
@@ -667,6 +673,7 @@ TABLESPACES SYSTEM VIEW
667673
TABLE_CONSTRAINTS SYSTEM VIEW
668674
TABLE_PRIVILEGES SYSTEM VIEW
669675
TRIGGERS SYSTEM VIEW
676+
TABLE_STATISTICS SYSTEM VIEW
670677
create table t1(a int);
671678
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
672679
use test;
@@ -680,6 +687,7 @@ TABLESPACES
680687
TABLE_CONSTRAINTS
681688
TABLE_PRIVILEGES
682689
TRIGGERS
690+
TABLE_STATISTICS
683691
select table_name from tables where table_name='user';
684692
table_name
685693
user
@@ -875,6 +883,7 @@ TABLE_NAME COLUMN_NAME PRIVILEGES
875883
COLUMNS TABLE_NAME select
876884
COLUMN_PRIVILEGES TABLE_NAME select
877885
FILES TABLE_NAME select
886+
INDEX_STATISTICS TABLE_NAME select
878887
INNODB_BUFFER_PAGE TABLE_NAME select
879888
INNODB_BUFFER_PAGE_LRU TABLE_NAME select
880889
INNODB_CMP_PER_INDEX table_name select
@@ -886,6 +895,7 @@ STATISTICS TABLE_NAME select
886895
TABLES TABLE_NAME select
887896
TABLE_CONSTRAINTS TABLE_NAME select
888897
TABLE_PRIVILEGES TABLE_NAME select
898+
TABLE_STATISTICS TABLE_NAME select
889899
VIEWS TABLE_NAME select
890900
delete from mysql.user where user='mysqltest_4';
891901
delete from mysql.db where user='mysqltest_4';
@@ -895,7 +905,7 @@ table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest')
895905
AND table_name not like 'ndb%' AND table_name not like 'innodb_%'
896906
GROUP BY TABLE_SCHEMA;
897907
table_schema count(*)
898-
information_schema 31
908+
information_schema 33
899909
mysql 25
900910
create table t1 (i int, j int);
901911
create trigger trg1 before insert on t1 for each row
@@ -1345,6 +1355,7 @@ EVENTS information_schema.EVENTS 1
13451355
FILES information_schema.FILES 1
13461356
GLOBAL_STATUS information_schema.GLOBAL_STATUS 1
13471357
GLOBAL_VARIABLES information_schema.GLOBAL_VARIABLES 1
1358+
INDEX_STATISTICS information_schema.INDEX_STATISTICS 1
13481359
KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1
13491360
OPTIMIZER_TRACE information_schema.OPTIMIZER_TRACE 1
13501361
PARAMETERS information_schema.PARAMETERS 1
@@ -1363,6 +1374,7 @@ TABLES information_schema.TABLES 1
13631374
TABLESPACES information_schema.TABLESPACES 1
13641375
TABLE_CONSTRAINTS information_schema.TABLE_CONSTRAINTS 1
13651376
TABLE_PRIVILEGES information_schema.TABLE_PRIVILEGES 1
1377+
TABLE_STATISTICS information_schema.TABLE_STATISTICS 1
13661378
TRIGGERS information_schema.TRIGGERS 1
13671379
USER_PRIVILEGES information_schema.USER_PRIVILEGES 1
13681380
VIEWS information_schema.VIEWS 1

‎mysql-test/r/information_schema_db.result‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,13 +36,16 @@ TABLE_PRIVILEGES
3636
TRIGGERS
3737
USER_PRIVILEGES
3838
VIEWS
39+
TABLE_STATISTICS
40+
INDEX_STATISTICS
3941
show tables from INFORMATION_SCHEMA like 'T%';
4042
Tables_in_information_schema (T%)
4143
TABLES
4244
TABLESPACES
4345
TABLE_CONSTRAINTS
4446
TABLE_PRIVILEGES
4547
TRIGGERS
48+
TABLE_STATISTICS
4649
create database `inf%`;
4750
create database mbase;
4851
use `inf%`;

‎mysql-test/r/mysqld--help-notwin.result‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -719,6 +719,8 @@ The following options may be given as the first argument:
719719
--range-alloc-block-size=#
720720
Allocation block size for storing ranges during
721721
optimization
722+
--rds-indexstat Control INDEX_STATISTICS
723+
--rds-tablestat Control TABLE_STATISTICS
722724
--rds-threads-running-ctl-mode=name
723725
Control which statements will be affected by threads
724726
running control, Values: SELECTS(default), ALL.
@@ -1259,6 +1261,8 @@ query-cache-type OFF
12591261
query-cache-wlock-invalidate FALSE
12601262
query-prealloc-size 8192
12611263
range-alloc-block-size 4096
1264+
rds-indexstat FALSE
1265+
rds-tablestat FALSE
12621266
rds-threads-running-ctl-mode SELECTS
12631267
rds-threads-running-high-watermark 151
12641268
read-buffer-size 131072

‎mysql-test/r/mysqlshow.result‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -110,6 +110,8 @@ Database: information_schema
110110
| TRIGGERS |
111111
| USER_PRIVILEGES |
112112
| VIEWS |
113+
| TABLE_STATISTICS |
114+
| INDEX_STATISTICS |
113115
| INNODB_CMP_RESET |
114116
| INNODB_RSEG |
115117
| INNODB_SYS_DATAFILES |
@@ -175,6 +177,8 @@ Database: INFORMATION_SCHEMA
175177
| TRIGGERS |
176178
| USER_PRIVILEGES |
177179
| VIEWS |
180+
| TABLE_STATISTICS |
181+
| INDEX_STATISTICS |
178182
| INNODB_CMP_RESET |
179183
| INNODB_RSEG |
180184
| INNODB_SYS_DATAFILES |

‎mysql-test/suite/funcs_1/r/is_columns_is.result‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -114,6 +114,10 @@ def information_schema GLOBAL_STATUS VARIABLE_NAME 1 NO varchar 64 192 NULL NUL
114114
def information_schema GLOBAL_STATUS VARIABLE_VALUE 2 NULL YES varchar 1024 3072 NULL NULL NULL utf8 utf8_general_ci varchar(1024) select
115115
def information_schema GLOBAL_VARIABLES VARIABLE_NAME 1 NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select
116116
def information_schema GLOBAL_VARIABLES VARIABLE_VALUE 2 NULL YES varchar 1024 3072 NULL NULL NULL utf8 utf8_general_ci varchar(1024) select
117+
def information_schema INDEX_STATISTICS INDEX_NAME 3 NO varchar 192 576 NULL NULL NULL utf8 utf8_general_ci varchar(192) select
118+
def information_schema INDEX_STATISTICS ROWS_READ 4 0 NO int NULL NULL 10 0 NULL NULL NULL int(21) select
119+
def information_schema INDEX_STATISTICS TABLE_NAME 2 NO varchar 192 576 NULL NULL NULL utf8 utf8_general_ci varchar(192) select
120+
def information_schema INDEX_STATISTICS TABLE_SCHEMA 1 NO varchar 192 576 NULL NULL NULL utf8 utf8_general_ci varchar(192) select
117121
def information_schema KEY_COLUMN_USAGE COLUMN_NAME 7 NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select
118122
def information_schema KEY_COLUMN_USAGE CONSTRAINT_CATALOG 1 NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) select
119123
def information_schema KEY_COLUMN_USAGE CONSTRAINT_NAME 3 NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select
@@ -304,6 +308,14 @@ def information_schema TABLE_PRIVILEGES PRIVILEGE_TYPE 5 NO varchar 64 192 NULL
304308
def information_schema TABLE_PRIVILEGES TABLE_CATALOG 2 NO varchar 512 1536 NULL NULL NULL utf8 utf8_general_ci varchar(512) select
305309
def information_schema TABLE_PRIVILEGES TABLE_NAME 4 NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select
306310
def information_schema TABLE_PRIVILEGES TABLE_SCHEMA 3 NO varchar 64 192 NULL NULL NULL utf8 utf8_general_ci varchar(64) select
311+
def information_schema TABLE_STATISTICS ROWS_CHANGED 4 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select
312+
def information_schema TABLE_STATISTICS ROWS_CHANGED_X_INDEXES 5 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select
313+
def information_schema TABLE_STATISTICS ROWS_DELETED 7 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select
314+
def information_schema TABLE_STATISTICS ROWS_INSERTED 6 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select
315+
def information_schema TABLE_STATISTICS ROWS_READ 3 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select
316+
def information_schema TABLE_STATISTICS ROWS_UPDATED 8 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select
317+
def information_schema TABLE_STATISTICS TABLE_NAME 2 NO varchar 192 576 NULL NULL NULL utf8 utf8_general_ci varchar(192) select
318+
def information_schema TABLE_STATISTICS TABLE_SCHEMA 1 NO varchar 192 576 NULL NULL NULL utf8 utf8_general_ci varchar(192) select
307319
def information_schema TRIGGERS ACTION_CONDITION 9 NULL YES longtext 4294967295 4294967295 NULL NULL NULL utf8 utf8_general_ci longtext select
308320
def information_schema TRIGGERS ACTION_ORDER 8 0 NO bigint NULL NULL 19 0 NULL NULL NULL bigint(4) select
309321
def information_schema TRIGGERS ACTION_ORIENTATION 11 NO varchar 9 27 NULL NULL NULL utf8 utf8_general_ci varchar(9) select
@@ -510,6 +522,10 @@ NULL information_schema FILES CHECKSUM bigint NULL NULL NULL NULL bigint(21) uns
510522
3.0000 information_schema GLOBAL_STATUS VARIABLE_VALUE varchar 1024 3072 utf8 utf8_general_ci varchar(1024)
511523
3.0000 information_schema GLOBAL_VARIABLES VARIABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
512524
3.0000 information_schema GLOBAL_VARIABLES VARIABLE_VALUE varchar 1024 3072 utf8 utf8_general_ci varchar(1024)
525+
3.0000 information_schema INDEX_STATISTICS TABLE_SCHEMA varchar 192 576 utf8 utf8_general_ci varchar(192)
526+
3.0000 information_schema INDEX_STATISTICS TABLE_NAME varchar 192 576 utf8 utf8_general_ci varchar(192)
527+
3.0000 information_schema INDEX_STATISTICS INDEX_NAME varchar 192 576 utf8 utf8_general_ci varchar(192)
528+
NULL information_schema INDEX_STATISTICS ROWS_READ int NULL NULL NULL NULL int(21)
513529
3.0000 information_schema KEY_COLUMN_USAGE CONSTRAINT_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512)
514530
3.0000 information_schema KEY_COLUMN_USAGE CONSTRAINT_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64)
515531
3.0000 information_schema KEY_COLUMN_USAGE CONSTRAINT_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
@@ -700,6 +716,14 @@ NULL information_schema TABLESPACES NODEGROUP_ID bigint NULL NULL NULL NULL bigi
700716
3.0000 information_schema TABLE_PRIVILEGES TABLE_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)
701717
3.0000 information_schema TABLE_PRIVILEGES PRIVILEGE_TYPE varchar 64 192 utf8 utf8_general_ci varchar(64)
702718
3.0000 information_schema TABLE_PRIVILEGES IS_GRANTABLE varchar 3 9 utf8 utf8_general_ci varchar(3)
719+
3.0000 information_schema TABLE_STATISTICS TABLE_SCHEMA varchar 192 576 utf8 utf8_general_ci varchar(192)
720+
3.0000 information_schema TABLE_STATISTICS TABLE_NAME varchar 192 576 utf8 utf8_general_ci varchar(192)
721+
NULL information_schema TABLE_STATISTICS ROWS_READ bigint NULL NULL NULL NULL bigint(21)
722+
NULL information_schema TABLE_STATISTICS ROWS_CHANGED bigint NULL NULL NULL NULL bigint(21)
723+
NULL information_schema TABLE_STATISTICS ROWS_CHANGED_X_INDEXES bigint NULL NULL NULL NULL bigint(21)
724+
NULL information_schema TABLE_STATISTICS ROWS_INSERTED bigint NULL NULL NULL NULL bigint(21)
725+
NULL information_schema TABLE_STATISTICS ROWS_DELETED bigint NULL NULL NULL NULL bigint(21)
726+
NULL information_schema TABLE_STATISTICS ROWS_UPDATED bigint NULL NULL NULL NULL bigint(21)
703727
3.0000 information_schema TRIGGERS TRIGGER_CATALOG varchar 512 1536 utf8 utf8_general_ci varchar(512)
704728
3.0000 information_schema TRIGGERS TRIGGER_SCHEMA varchar 64 192 utf8 utf8_general_ci varchar(64)
705729
3.0000 information_schema TRIGGERS TRIGGER_NAME varchar 64 192 utf8 utf8_general_ci varchar(64)

‎mysql-test/suite/funcs_1/r/is_tables_is.result‎

Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -245,6 +245,29 @@ user_comment
245245
Separator -----------------------------------------------------
246246
TABLE_CATALOG def
247247
TABLE_SCHEMA information_schema
248+
TABLE_NAME INDEX_STATISTICS
249+
TABLE_TYPE SYSTEM VIEW
250+
ENGINE MEMORY
251+
VERSION 10
252+
ROW_FORMAT Fixed
253+
TABLE_ROWS #TBLR#
254+
AVG_ROW_LENGTH #ARL#
255+
DATA_LENGTH #DL#
256+
MAX_DATA_LENGTH #MDL#
257+
INDEX_LENGTH #IL#
258+
DATA_FREE #DF#
259+
AUTO_INCREMENT NULL
260+
CREATE_TIME #CRT#
261+
UPDATE_TIME #UT#
262+
CHECK_TIME #CT#
263+
TABLE_COLLATION utf8_general_ci
264+
CHECKSUM NULL
265+
CREATE_OPTIONS #CO#
266+
TABLE_COMMENT #TC#
267+
user_comment
268+
Separator -----------------------------------------------------
269+
TABLE_CATALOG def
270+
TABLE_SCHEMA information_schema
248271
TABLE_NAME KEY_COLUMN_USAGE
249272
TABLE_TYPE SYSTEM VIEW
250273
ENGINE MEMORY
@@ -636,6 +659,29 @@ user_comment
636659
Separator -----------------------------------------------------
637660
TABLE_CATALOG def
638661
TABLE_SCHEMA information_schema
662+
TABLE_NAME TABLE_STATISTICS
663+
TABLE_TYPE SYSTEM VIEW
664+
ENGINE MEMORY
665+
VERSION 10
666+
ROW_FORMAT Fixed
667+
TABLE_ROWS #TBLR#
668+
AVG_ROW_LENGTH #ARL#
669+
DATA_LENGTH #DL#
670+
MAX_DATA_LENGTH #MDL#
671+
INDEX_LENGTH #IL#
672+
DATA_FREE #DF#
673+
AUTO_INCREMENT NULL
674+
CREATE_TIME #CRT#
675+
UPDATE_TIME #UT#
676+
CHECK_TIME #CT#
677+
TABLE_COLLATION utf8_general_ci
678+
CHECKSUM NULL
679+
CREATE_OPTIONS #CO#
680+
TABLE_COMMENT #TC#
681+
user_comment
682+
Separator -----------------------------------------------------
683+
TABLE_CATALOG def
684+
TABLE_SCHEMA information_schema
639685
TABLE_NAME TRIGGERS
640686
TABLE_TYPE SYSTEM VIEW
641687
ENGINE MyISAM
@@ -952,6 +998,29 @@ user_comment
952998
Separator -----------------------------------------------------
953999
TABLE_CATALOG def
9541000
TABLE_SCHEMA information_schema
1001+
TABLE_NAME INDEX_STATISTICS
1002+
TABLE_TYPE SYSTEM VIEW
1003+
ENGINE MEMORY
1004+
VERSION 10
1005+
ROW_FORMAT Fixed
1006+
TABLE_ROWS #TBLR#
1007+
AVG_ROW_LENGTH #ARL#
1008+
DATA_LENGTH #DL#
1009+
MAX_DATA_LENGTH #MDL#
1010+
INDEX_LENGTH #IL#
1011+
DATA_FREE #DF#
1012+
AUTO_INCREMENT NULL
1013+
CREATE_TIME #CRT#
1014+
UPDATE_TIME #UT#
1015+
CHECK_TIME #CT#
1016+
TABLE_COLLATION utf8_general_ci
1017+
CHECKSUM NULL
1018+
CREATE_OPTIONS #CO#
1019+
TABLE_COMMENT #TC#
1020+
user_comment
1021+
Separator -----------------------------------------------------
1022+
TABLE_CATALOG def
1023+
TABLE_SCHEMA information_schema
9551024
TABLE_NAME KEY_COLUMN_USAGE
9561025
TABLE_TYPE SYSTEM VIEW
9571026
ENGINE MEMORY
@@ -1343,6 +1412,29 @@ user_comment
13431412
Separator -----------------------------------------------------
13441413
TABLE_CATALOG def
13451414
TABLE_SCHEMA information_schema
1415+
TABLE_NAME TABLE_STATISTICS
1416+
TABLE_TYPE SYSTEM VIEW
1417+
ENGINE MEMORY
1418+
VERSION 10
1419+
ROW_FORMAT Fixed
1420+
TABLE_ROWS #TBLR#
1421+
AVG_ROW_LENGTH #ARL#
1422+
DATA_LENGTH #DL#
1423+
MAX_DATA_LENGTH #MDL#
1424+
INDEX_LENGTH #IL#
1425+
DATA_FREE #DF#
1426+
AUTO_INCREMENT NULL
1427+
CREATE_TIME #CRT#
1428+
UPDATE_TIME #UT#
1429+
CHECK_TIME #CT#
1430+
TABLE_COLLATION utf8_general_ci
1431+
CHECKSUM NULL
1432+
CREATE_OPTIONS #CO#
1433+
TABLE_COMMENT #TC#
1434+
user_comment
1435+
Separator -----------------------------------------------------
1436+
TABLE_CATALOG def
1437+
TABLE_SCHEMA information_schema
13461438
TABLE_NAME TRIGGERS
13471439
TABLE_TYPE SYSTEM VIEW
13481440
ENGINE MyISAM

0 commit comments

Comments
 (0)