WL#6599: New Data Dictionary and I_S integration
Affects: Server-8.0
—
Status: Complete
Introduction:
-------------
The INFORMATION_SCHEMA tables on 5.7 are temporary tables that
are created and filled during execution of the query. The
meta-data is read from .FRM files, storage engines and server in
memory data structures. Reading data from .FRM files involves
preparation of TABLE_SHARE object for each and every table and
this is time consuming.
With the introduction of data dictionary in 8.0, most of
INFORMATION_SCHEMA tables meta-data can be read from data
dictionary tables. The goal of this WL is,
1. To re-implement the possible INFORMATION_SCHEMA tables to pick
meta-data from data-dictionary tables and avoid creation of
temporary tables and preparation of TABLE_SHARE object upon every
query execution that contains INFORMATION_SCHEMA table.
2. The implementation would also provide following modes,
2.1 Support retrieval of dynamic table or index statistics from a
new DD table where the statistics are already stored. The DD table
would be updated using ANALYZE TABLE command. This allows a
INFORMATION_SCHEMA system view to present the dynamic statistics to
the users faster. It is understood that the statistics presented
this way would be from the time when user ran ANALYZE TABLE and are
not latest statistics.
2.2 Support retrieval of dynamic table or index statistics from
storage engine. This allows a INFORMATION_SCHEMA system view to
present latest dynamic statistics to the users. The design would
also involve developing customer storage engine API's that would
help get the latest table statistics without running open_table()
on a given table.
2.3 Provide support for user to switch between behaviour 2.1 and
2.2 based on a session variable.
3. With the re-implementation of several INFORMATION_SCHEMA
tables, there will be compatibility issues. We must try to
support the current behaviour as much of possible. However few
specific cases are,
- The current behavior is that the order of row's returned from
I_S query is fixed as of now. After this WL implementation the
rows returned from every execution of INFORMATION_SCHEMA system
view might depend on optimizer decisions and hence the order might
change.
- On 5.7, I_S tables are temporary tables and then the 'CREATE
TABLE LIKE' and 'HANDLER COLUMNS OPEN' command would work on I_S
tables. On 8.0, few I_S tables are system views and these two
command expect the input to be a BASE table and not a view. This
brings in a limitation.
- The --ignore-db-dir option was mostly added to avoid SQL server
to read physical directories like lost+found. Now that 8.0 server
do not depend of file-system folder to find databases, this option
is removed.
- Upon execution of I_S system views, the resulting column names
are show in capital. This seems to be result of how views are
merged.
There could be other compatibility issues, that should be
investigated by the WL.
4. Discuss possible solution and outline a plan for implementation.
1) Definitions and Abbreviation:
--------------------------------
DD : Data Dictionary
I_S : INFORMATION_SCHEMA
System View : A MySQL view that represent a I_S table.
TS : Dynamic table statistics, like,
TABLE_ROWS
AVG_ROW_LENGTH
DATA_LENGTH
MAX_DATA_LENGTH
INDEX_LENGTH
DATA_FREE
AUTO_INCREMENT
CREATE_TIME
UPDATE_TIME
CHECK_TIME
IS : Dynamic Index statistics i.e., index CARDINALITY.
TS_DD : DD table mysql.table_stat to store table statistics
IS_DD : DD table mysql.index_stat to store index statistics
FDS : Fast Dynamic Table/Index Statistics, but not
latest. Statistics will be based on last
execution of ANALYZE TABLE command.
LDS : Latest Dynamic Table/Index Statistics read from
storage engine. This would be slower compare to
read LDS compared to FDS.
INTERNAL_UDF: User defined functions implemented to enable
reading SE statistics dynamically in LDS
configuration.
2) Functional requirements:
---------------------------
FR-01 Implement possible I_S tables as a System View over the DD
tables.
a) I_S tables that mostly fetch static meta-data.
I.e., it does not read TS/IS details.
CHARACTER_SETS
COLLATIONS
SCHEMATA
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
REFERENTIAL_CONSTRAINTS
EVENTS
ROUTINES
TABLESPACES
TABLE_CONSTRAINTS
TRIGGERS
VIEWS
KEY_COLUMN_USAGE
b) I_S tables that mostly read both static meta-data
and dynamic table statistics.
TABLES
STATISTICS
FILES
Note that the SHOW CREATE command on system view would now show
the system view definition.
FR-02 Implement possible SHOW commands to use new I_S system view
introduced by FR-01.
SHOW CHARSET
SHOW COLLATION
SHOW DATABASES
SHOW TABLES
SHOW TABLE STATUS
SHOW COLUMNS
SHOW KEYS/INDEXES
SHOW STATISTICS
DESCRIBE
FR-03 Introduce new TS_DD table to store dynamic table
statistics, to enable FDS retrieval from I_S system view.
FR-04 Introduce new IS_DD table to store dynamic index
statistics, to enable FDS retrieval from I_S system view.
FR-05 It must be possible for I_S system view to read dynamic
table statistics from storage engine enabling to read LDS.
FR-06 It must be possible for I_S system views to pick dynamic
table statistics from TS_DD and IS_DD tables providing
FDS.
FR-07 Enable MySQL users to pick either FDS or LDS based on a
session variable.
FR-08 Implement new INTERNAL_UDF's which would retrieve LDS
from storage engine. This would be used in new I_S system
view definition.
FR-09 Implement INTERNAL_UDF's to do quick retrieval of
TS/IS from storage engine by giving the table name. The mean
of 'quick' here is to read statistics quicker compared to
opening the underlying table using open_table() and then
reading the statistics. Designing a new UDF will help avoid
building complete TABLE_SHARE for a table.
Any new SE API's required to read quick TS/IS values would
be implemented as separate work.
FR-10 Store meta-data of I_S tables into DD during bootstrapping
itself. As this information never changes, we can stored
them once. This include I_S tables that are not system views too.
FR-11 Create I_S system views as part of bootstrapping
procedure.
FR-12 Introduce new DD columns enhancing DD table definitions
to store required information by I_S, Like
COLUMN_TYPE, DEFAULT_VALUE_UTF8, INDEX_ORDER, ROW_FORMAT.
FR-13 Implement SHOW command on temporary table to skip using
I_S system views and use the current approach of creating
temporary table and filling it during execution of query
itself. There are temporary tables created by CREATE
TEMPORARY TABLE command, whose metadata is not stored in DD
tables.
FR-14 Introduce new MySQL extension I_S.TABLES.LAST_ALTERED
column projecting the new information that is now
available in DD i.e, mysql.tables.last_altered. Possibly
we can enhance other I_S.* tables where data dictionary
tables now contain the last_altered information.
FR-15 Allow I_S system views to be executed without waiting for
concurrent writes to DD tables.
FR-16 Store mandatory plugin schema tables meta-data into DD
tables upon bootstrap.
FR-17 Store dynamic plugin schema tables meta-data into DD table
upon plugin install. And remove the same during plugin
uninstall.
FR-18 Enable use of I_S system view under locked table modes.
FR-19 Make SHOW commands to read view column meta-data.
FR-20 Enable users to provide information schema and system view
names in mix case letters and do not treat them as case
sensitive, this is required to match the old behavior.
The reason we need to handle this case is that the
TABLES.TABLE_NAME maps to DD table column mysql.tables.name with
the new implementation. Whose collation would make it
depend on l_c_t_n. However, in 5.7 this was fixed to utf8_general_ci
which enabled users to automatically do case insensitive
comparison. Requirement is to try and match the old behavior.
When user provide a I_S schema and table name, convert them
to lowercase when possible. For e.g., we could do this when table
name is added to open_table list by add_table_to_list(). This
enables use to support part of old behavior but not complete.
The restriction would be that I_S schema and table name
provide in WHERE clauses of SHOW and I_S query would be treated
with same collation requirements as that of mysql.tables.name.
FR-21 The 'System View' here is mostly treated by the MySQL server in
the same way as that of user tables, except that we handle them
in lock table mode a bit differently. See 2.1.11 for more info.
1. Architecture of I_S without DD:
==================================
I_S tables are implemented in MySQL as temporary tables which are created on
the fly during query execution. The existing architecture is that, this
temporary table is filled with required details read from,
1) FRM files.
2) Retrieved from SE, OR
3) SQL server global data structures.
1.1 The low level details of current implementation is as below:
----------------------------------------------------------------
ST_SCHEMA_TABLE structure represents I_S table and ST_FIELD_INFO
represents fields of I_S table. These two data structures are use
to create the temporary table for I_S. A array of ST_SCHEMA_TABLE
object is used to represent 31 information schema tables related
to SQL engine.
struct ST_SCHEMA_TABLE
{
const char* table_name;
ST_FIELD_INFO *fields_info;
// 1st level callback function
int (*fill_table) (THD *thd, TABLE_LIST *tables, Item *cond);
// 2nd level callback function
int (*process_table) (THD *thd, TABLE_LIST *tables,
TABLE *table,
bool res, LEX_STRING *db_name,
LEX_STRING *table_name);
...
}
struct ST_FIELD_INFO
{
const char* field_name;
uint field_length;
enum_field_types field_type;
int value;
uint field_flags;
...
}
1.1.1 )
'ST_SCHEMA_TABLE.fill_table' function pointer points to
callback function which fills records into the I_S temporary
table.
Below are some of the I_S tables and its corresponding callback
functions. There are mostly I_S tables that read the required
information from MySQL data structures.
-----------------------------------------------------
I_S TABLE | (*fill_table)()
------------------------------------------------------
CHARACTER_SETS | fill_schema_charsets
COLLATIONS | fill_schema_collation
ENGINES | fill_schema_engines
GLOBAL_STATUS | fill_status
GLOBAL_VARIABLES | fill_variables
OPEN_TABLES | fill_open_tables
OPTIMIZER_TRACE | fill_optimizer_trace_info
FILES | hton_fill_schema_table
COLUMN_PRIVILEGES | fill_schema_column_privileges
EVENTS | Events::fill_schema_events
-------------------------------------------------------
1.1.2 )
I_S tables which reads meta-data of database objects like
TABLES, TRIGGER, COLUMNS, VIEW uses callback function pointed to
by 'ST_SCHEMA_TABLE.process_table' to read meta-data from given
table.. As these I_S tables require list of all databases and
their tables list from which the details are extracted, the
function get_all_tables() is invoked at first.
Below are list of callback function which are responsible to
fill data into respective I_S tables.
------------------------------------------------------------------------
I_S TABLE |(*fill_table)()|(*process_table)()
------------------------------------------------------------------------
COLUMNS |get_all_tables |get_schema_column_record
KEY_COLUMN_USAGE |get_all_tables |get_schema_key_column_usage_record
PARTITIONS |get_all_tables |get_schema_partitions_record
REFERENTIAL_CONSTRAINTS|get_all_tables |get_referential_constraints_record
STATISTICS |get_all_tables |get_schema_stat_record
TABLES |get_all_tables |get_schema_tables_record
TABLE_CONSTRAINTS |get_all_tables |get_schema_constraints_record
TRIGGERS |get_all_tables |get_schema_triggers_record
VIEWS |get_all_tables |get_schema_views_record
------------------------------------------------------------------------
1.1.3 )
Pseudo code for get_all_tables() function
-----------------------------------------
// 2nd argument TABLE_LIST *tables represents temporary table 'T' created to
store resultset
for ('database' in databases_list) (directory scan on DATA_DIR + I_S)
{
for ('table' in tables_list of 'database') (directory scan on DATA_DIR/)
{
if ('table' is a I_S table)
- create temporary table 'TT' (creates .frm file for I_S)
- open table 'TT'
- Access meta-data using TABLE_SHARE,handler,handlerton,etc.
- call (*process_table)() to store meta-data into 'T'
- Present resulting rows to mysql client.
- close table 'TT'
- remove table 'TT' (removes .frm file)
else
- open table (open .frm file first time and then read from TABLE_CACHE)
- access meta info using TABLE_SHARE, handler, handlerton,etc of table
- call (*process_table)() to store meta-data into 'T'
- close table
endif
}
}
2. Architecture of I_S with DD:
===============================
2.1 Design Overview
-------------------
The approach of implementing new I_S table is to define I_S
system view over DD tables, representing a I_S table. This will
completely eliminate the need of preparing a temporary table for
each I_S table during execution and other costs that are
described in section 1) above. I_S query execution would work
same as executing a query on a view.
Following are the changes involved:
2.1.1 ) I_S tables that can be implemented as a System View (FR-01).
The names of new system views that are implemented are listed in FR-01.
Definition of each system view is listed in LLD.
2.1.2 ) I_S tables whose implementation remains the same.
ENGINES
GLOBAL_STATUS
GLOBAL_VARIABLES
OPTIMIZER_TRACE
PLUGINS
PROCESSLIST
PROFILING
OPEN_TABLES
SESSION_STATUS
SESSION_VARIABLES
SCHEMA_PRIVILEGES
TABLE_PRIVILEGES
COLUMN_PRIVILEGES
USER_PRIVILEGES
STATUS
2.1.3 ) List of commands that would be re-implemented to use new
I_S system views defined in 2.1.1. FR-02.
- We prepare a SELECT_LEX representing a SELECT query over I_S system view
by 'build_show_*()' function in sql/dd/show.cc. The approach is to prepare
a
Parse Tree node and let the executor execute it.
2.1.4 ) Implementation of FDS and LDS. (FR-03..09)
Following are the details of implementation to support
FDS and LDS configuration over I_S system views.
a) SQL server bootstrap will creates TS_DD and IS_DD
tables during DD initialization.
b) ANALYZE TABLE command updates TS_DD and IS_DD tables with
TS/IS meta-data read from SE. This will be picked by I_S tables
in FDS configuration.
c) There are two version of each I_S system view which read
TDS/IDS. This applies to I_S tables listed under 2.1.1 b)
c1) I_S system view definition that joins with TS_DD and
IS_DD to read FDS.
c2) I_S system view definition that invokes new
INTERNAL_UDF's to read LDS. These system view
definitions are suffixed with text '_dynamic' in there
view name.
d) SQL server would switch between c1) and c2) configuration
based on session variable 'information_schema_stats'.
This is implemented such that the 'add_table_to_list()' function
would rename the I_S system view name picking the one
required based on 'information_schema_stats' setting.
SQL server will use c1) behavior by default which
chooses the FDS configuration, that means that the
'information_schema_stats' is set to 'cached' by default.
E.g.,
We have I_S.TABLES system view representing c1) and
I_S.TABLES_DYNAMIC system view that represents c2) version.
Where a user executes a statement with I_S table name
e.g., 'SELECT * FROM INFORMATION_SCHEMA.TABLES', the parser
invokes 'add_table_to_list()' function. This function intern
checks if the 'information_schema_stats=latest', then replace
the I_S table name from INFORMATION_SCHEMA.TABLES to
INFORMATION_SCHEMA.TABLES_DYNAMIC. This same concept would be
applied to other I_S tables in 2.1.1 b)
Note that this behavior applies for SHOW commands implementation
which uses I_S tables.
2.1.5) I_S and concurrency (FR-15)
2.1.6) Allow view creation under bootstrapping (FR-11)
The new I_S system views should be created during
bootstrapping similar to the way DD tables are created. This
avoid possibility of SQL server picking wrong I_S system view
definition, accidentally modified by user OR due to user
using a old DB patch.
TODO: The upgrade needs to handle drop and re-create
the system views, mainly when upgrading from 8.0 to higher
versions.
2.1.7) Store meta-data of plugin schema tables. (FR-10,FR-16,FR-17)
FR-10
As described by Praba in 1.1.3 above, the 5.7 server would
end-up creating a temporary table to retrieve I_S table metad-data
and present that in output of I_S.TABLES. With FR-01
implemented we cannot do the same.
As the I_S tables schema does not change, we will store the
meta-data of I_S tables during server bootstrapping procedure.
FR-16:
The 5.7 server would request meta-data of I_S system tables
owned by storage engines upon execution of every I_S table listed
in 2.1.1 a) & b). With FR-01 implemented the I_S system views
cannot retrieve this meta-data from SE. I.e., 2.1.4 c1 and c2 would
be modeled to read rows from SE during execution, which is
not possible.
As the SE schema tables meta-data is pretty much fixed and
does not change, we would store such mandatory plugins schema
table meta-data into DD tables during bootstrapping. This would
save lot of time spent to read data from SE during execution.
FR-17 - TBD
Dynamic plugins can own schema tables. On 5.7 these tables
are requested by SQL server through SE API and the values are
filled in by SE engine itself.
With FR-01 implemented, a system view cannot read this
information from SE. Hence the plan is to store the Plugin's
schema table meta-data into DD table when the plugin is
installed. And we remove the same when plugin is un-installed.
2.1.8) Introducing new DD columns (FR-12)
On 5.7 server we read meta-data of table from TABLE_SHARE
prepared after opening the table. This TABLE_SHARE contains
information like below, which are mostly not persistent as is in
.FRM by are computed when the TABLE_SHARE is prepared. This
information was not available in DD tables readily.
So we added following new DD columns which are required by
I_S tables.
COLUMNS.COLUMN_KEY
COLUMNS.COLUMN_TYPE
COLUMNS.DEFAULT_VALUE_UTF8
TABLES.ROW_FORMAT
2.1.9) SHOW COLUMNS for temporary tables (FR-13)
SHOW COLUMNS command would pick data from I_S system view
after FR-01. This bring data from DD tables. However, the
meta-data of temporary tables is not stored/persistent in the DD
tables. So the meta-data of temporary table would not be seen by
I_S system view.
SHOW COLUMNS command on a temporary tables would skip using
the I_S system view and fall-back to the old model of creating a
temporary table and filling it with the data temporary table
meta-data from the SQL server memory data structures.
2.1.10) Introducing new I_S.TABLES.LAST_ALTERED field (FR-14)
There is a plan to introduce this new I_S column as a MySQL
extension.
2.1.11) I_S query execution under locked table mode: (FR-18)
I_S tables are temporary tables on 5.7. This make them
possible to access under LOCK TABLE command with explicitly
locking I_S tables. Now with FR-01 is implemented, the query
execution procedure fails to process I_S system view as the under
laying DD table are not locked.
The SQL server is made to open the DD tables used by a I_S
system view without requiring them to be locked explicitly.
2.1.12) Quick retrieval [Avoid open_table()] of latest statistics
using INTERNAL_UDF's (FR-08, FR-09)
When 'information_schema_stats=latest' the TS/IS
statistics are read from SE. The way we read these values on 5.7
is by opening the underlying table and reading handler->info()
values. If the UDF's in 8.0 does the same, the performance of I_S
query on 8.0 will not improvement and would remain same.
In effort to improve performance of I_S query with TS/IS
data, new SE API's are designed (see LLD section 2) to request
the TS/IS statistics based on the table name, without doing a
full blown table open.
These new SE API's are implemented by 'InnoDB' SE alone for
now. So, the INTERNAL_UDF's would use these new SE API's only for
'InnoDB' tables. For non-InnoDB tables, the INTERNAL_UDF's would
following the same procedure as that of 5.7 to open the table and
invoke handler->info() to read the TS/IS values.
3 Compatibility issues:
-----------------------
Please refer main.dd_is_compatibility for test cases for each of
below change in behavior.
a) Requires execution of ANALYZE TABLE in FDS mode. I.e.,
when 'information_schema_stats=cached'.
Steps:
1) start server.
2) create a table.
3) insert few rows.
4) read I_S.TABLES dynamic statistics.
If we carry on following steps on 5.7 server then we do get
TS/IS read from SE.
However on 8.0 server, step 4) would get NULL for all the
dynamic statistics. The reason is that the default configuration
of I_S system view is to pick the TS/IS values from TS_DD and
IS_DD tables, which is based on
'information_schema_stats=cached'. TS_DD/IS_DD tables
needs to be updated by a explicit ANALYZE TABLE command. Without
which the step 4) would result in NULL due to LEFT JOIN done
in system view definition with TS_DD/IS_DD.
The correct steps to get TS/IS on 8.0 would be:
Steps:
1) start server.
2) create a table.
3) insert few rows.
4) run ANALYZE TABLE of the table.
5) read I_S.TABLES dynamic statistics.
b) The DD tables are listed in I_S query output.
We will hide the DD table meta-data to be exposed by I_S queries.
c) Capital cased I_S table column names.
Upon execution of a I_S query, the resulting column names
were in lower case on 5.7. However, on 8.0 with I_S system views,
SQL server present the I_S table column name in capital letters.
This is because optimizer does view merging instead of temp table
algorithm, due to which the column names are changed to uppercase.
One workaround to get column name with desired (lower/upper) case,
is to use a alias for the projections from the query, e.g.,
SELECT table_name as 'table_name' FROM INFORMATION_SCHEMA.TABLES;
table_name
----------
t1
Whereas without the alias we get,
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
TABLE_NAME
----------
t1
d) The order of row returned I_S query.
On 5.7, the order of row returned by I_S query was predictable
upon multiple execution of same query. With FR-01 being
implemented, the problem is that the optimizer do not guaranty
the order of rows returned from the SELECT.
On 8.0, it is recommended to use ORDER BY clause on I_S query
to order the rows based on users desire.
e) CREATE_TIME stored in DD table.
On 5.7, the CREATE_TIME of a table is not preserved by SQL
server and it is requested to SE. Moreover, not all SE's do
return CREATE_TIME.
On 8.0, SQL server store the CREATE_TIME of table in
mysql.tables.create_time.
f) Removal of --ignore-db-dir option from server.
This option was mostly added to avoid SQL server to read
physical directories like lost+found. Now that 8.0 server do not
depend of file-system folder to find databases, this option is
removed.
g) 'CREATE TABLE LIKE ' and
'HANDLER COLUMNS OPEN on '
On 5.7, I_S tables are temporary tables and then the 'CREATE
TABLE LIKE' and 'HANDLER COLUMNS OPEN' command would work on I_S
tables.
On 8.0, few I_S tables are system views and these two
command expect the input to be a BASE table and not a view. This
brings in a limitation.
h) I_S schema name and the table name are treated as
case-sensitive based on l_c_t_n setting, especially when these
names are used in WHERE clause of SHOW commands. This is because
the TABLES.TABLE_NAME maps to mysql.tables.name, which works based
on l_c_t_n.
i) Refering the behavior in FR-20, the restriction would
be that I_S schema and table name provide in WHERE clauses
of SHOW and I_S query would be treated with same collation
requirements as that of mysql.tables.name.
j) Prepared statements of SHOW commands fail differently for e.g.
ER_BAD_DB_ERROR unknown schema, instead of ER_TOO_BIG_SELECT for
big SELECT's. This is consequence of we checking for existing
database first and then JOIN::optimize(). In 5.7 this was done in
the other way.
k) ANALYZE TABLE under innodb read only mode fails with
error/warning. This would be a restrictions with wl6599. It is
recommended to use 'information_schema_stats=latest' to get latest
statistics from IS queries in read only mode.
l) Most of the INFORMATION_SCHEMA table are re-implemented as view.
Current method of dumping INFORMATION_SCHEMA does not work
for views.
OTOH, INFORMATION_SCHEMA DB content dump is only used to reload the
data into another tables for analysis purpose. This feature is not the
core responsibility of mysqlpump tool. INFORMATION_SCHEMA DB
content can be dumped using other methods like SELECT INTO OUTFILE ...
for such purpose. Hence ignoring INFORMATION_SCHEMA DB dump from
mysqldump and mysqlpump tool.
m) MySQL 'DISABLE KEYS' functionality is only applicable to MyISAM
tables, but looks like its marked for InnoDB table too in trunk.
This problem is fixed now. It has been discussed with Martin Hansson
who worked on WL8697 which introduced invisible index. He agreed that
the behavior on trunk is bad and needed to be fixed. We have fixed
that now.
n) In the 5.7 code, value for IS_UPDATABLE column of view is evaluated based
on view is mergable or not and view has at least one fields which
can be updated. This value is stored in the schema table.
But there are many other factors to decide view is updatable or not.
In 5.7 code too, these factors are checked while creating or
altering view but this value is not used while filling schema table.
Due to which we were getting wrong IS_UDPATABLE for some views.
Now we get correct is_updatable value for views as we
read the value stored in dicationary tables while creating or altering
view.
o) Most of the INFORMATION_SCHEMA table are re-implemented as view as
this WL. Current method of dumping INFORMATION_SCHEMA does not work
for views.
OTOH, INFORMATION_SCHEMA DB content dump is only used to reload the data
into another tables for analysis purpose. This feature is not the
core responsibility of mysqlpump tool. INFORMATION_SCHEMA DB
content can be dumped using other methods like SELECT INTO OUTFILE ...
for such purpose. Hence ignoring INFORMATION_SCHEMA DB dump from
mysqldump and mysqlpump tool.
p) ANLYZE TABLE operation on non-transactional table was incompatible
with FLUSH TABLES WITH READ LOCK. ANLYZE TABLE operation
used to wait for GLOBAL READ LOCK before.
Now ANLYZE TABLE operations for transactional and non-transactional
tables, ignores GLOBAL READ LOCK. So analyze table operation
is compatible with FLUSH TABLES WITH READ LOCK now.
q) In the 5.7 code, operation on INFORMATION_SCHEMA tables
acquires MDL lock on the tables to open definitions from the
data dictionary while filling schema table. With system view
implementation, as the rows are fetched from the new data dictionary
tables, MDL lock on the database objects are not acquired.
This is true in most of the cases except when
"information_schema_stats=latest" and operation on INFORMATION_SCHMEA
table requires latest statstics values.
Only in this case MDL lock of type MDL_SHARED_HIGH_PRIO is acquired on
the database object for the explicit(operation) duration.
1 System view definitions implementing FR-01: (Mar 10 2016)
-----------------------------------------------------------
--
-- INFORMATION_SCHEMA.COLLATIONS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.collations
AS
SELECT col.name AS COLLATION_NAME,
cs.name AS CHARACTER_SET_NAME,
col.id AS ID,
IF(EXISTS(SELECT * FROM mysql.character_sets
WHERE mysql.character_sets.default_collation_id=
col.id),
'Yes','') AS IS_DEFAULT,
IF(col.is_compiled,'Yes','') AS IS_COMPILED,
col.sort_length AS SORTLEN
FROM mysql.collations col JOIN mysql.character_sets cs ON
col.character_set_id=cs.id;
--
-- INFORMATION_SCHEMA.CHARACTER_SETS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.character_sets as
SELECT cs.name AS CHARACTER_SET_NAME,
col.name AS DEFAULT_COLLATE_NAME,
cs.comment AS DESCRIPTION,
cs.mb_max_length AS MAXLEN
FROM mysql.character_sets cs JOIN mysql.collations col ON
cs.default_collation_id = col.id;
--
-- INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.collation_character_set_applicability AS
SELECT col.name AS COLLATION_NAME,
cs.name AS CHARACTER_SET_NAME
FROM mysql.character_sets cs JOIN mysql.collations col ON cs.id =
col.character_set_id;
--
-- INFORMATION_SCHEMA.SCHEMATA
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.schemata AS
SELECT cat.name AS CATALOG_NAME,
sch.name AS SCHEMA_NAME,
cs.name AS DEFAULT_CHARACTER_SET_NAME,
col.name AS DEFAULT_COLLATION_NAME,
NULL AS SQL_PATH
FROM mysql.schemata sch JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations col ON sch.default_collation_id = col.id
JOIN mysql.character_sets cs ON col.character_set_id= cs.id
WHERE CAN_ACCESS_DATABASE(sch.name);
--
-- INFORMATION_SCHEMA.TABLES
--
-- There are two definitions of information_schema.tables.
-- 1. INFORMATION_SCHEMA.TABLES view which picks dynamic column
-- statistics from mysql.table_stats which gets populated when
-- we execute 'anaylze table' command.
--
-- 2. INFORMATION_SCHEMA.TABLES_DYNAMIC view which retrieves dynamic
-- column statistics using a internal UDF which opens the user
-- table and reads dynamic table statistics.
--
-- MySQL server uses definition 1) by default. The session variable
-- dynamic_information_schema_stats=true would enable use of definition 2).
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.tables AS
SELECT cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
tbl.type AS TABLE_TYPE,
IF(tbl.type = 'VIEW', NULL, tbl.engine) AS ENGINE,
IF(tbl.type = 'VIEW', NULL, 10 /* FRM_VER_TRUE_VARCHAR */) AS VERSION,
tbl.row_format AS ROW_FORMAT,
stat.table_rows AS TABLE_ROWS,
stat.avg_row_length AS AVG_ROW_LENGTH,
stat.data_length AS DATA_LENGTH,
stat.max_data_length AS MAX_DATA_LENGTH,
stat.index_length AS INDEX_LENGTH,
stat.data_free AS DATA_FREE,
stat.auto_increment AS AUTO_INCREMENT,
tbl.created AS CREATE_TIME,
stat.update_time AS UPDATE_TIME,
stat.check_time AS CHECK_TIME,
col.name AS TABLE_COLLATION,
stat.checksum AS CHECKSUM,
IF (tbl.type = 'VIEW', NULL,
GET_DD_CREATE_OPTIONS(tbl.options,
IF(IFNULL(tbl.partition_expression,'NOT_PART_TBL')='NOT_PART_TBL', 0,
1)))
AS CREATE_OPTIONS,
IF (tbl.type = 'VIEW', 'VIEW', tbl.comment) AS TABLE_COMMENT
FROM mysql.tables tbl JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
LEFT JOIN mysql.collations col ON tbl.collation_id=col.id
LEFT JOIN mysql.table_stats stat ON tbl.name=stat.table_name
AND sch.name=stat.schema_name
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden;
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.tables_dynamic AS
SELECT cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
tbl.type AS TABLE_TYPE,
IF(tbl.type = 'VIEW', NULL, tbl.engine) AS ENGINE,
IF(tbl.type = 'VIEW', NULL, 10 /* FRM_VER_TRUE_VARCHAR */) AS VERSION,
tbl.row_format AS ROW_FORMAT,
INTERNAL_TABLE_ROWS(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS TABLE_ROWS,
INTERNAL_AVG_ROW_LENGTH(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS AVG_ROW_LENGTH,
INTERNAL_DATA_LENGTH(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS DATA_LENGTH,
INTERNAL_MAX_DATA_LENGTH(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS MAX_DATA_LENGTH,
INTERNAL_INDEX_LENGTH(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS INDEX_LENGTH,
INTERNAL_DATA_FREE(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS DATA_FREE,
INTERNAL_AUTO_INCREMENT(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS AUTO_INCREMENT,
tbl.created AS CREATE_TIME,
INTERNAL_UPDATE_TIME(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS UPDATE_TIME,
INTERNAL_CHECK_TIME(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS CHECK_TIME,
col.name AS TABLE_COLLATION,
INTERNAL_CHECKSUM(sch.name, tbl.name,
IF(IFNULL(tbl.partition_type,'')='',tbl.engine,''),
tbl.se_private_id) AS CHECKSUM,
IF (tbl.type = 'VIEW', NULL,
GET_DD_CREATE_OPTIONS(tbl.options,
IF(IFNULL(tbl.partition_expression,'NOT_PART_TBL')='NOT_PART_TBL', 0,
1)))
AS CREATE_OPTIONS,
IF (tbl.type = 'VIEW', 'VIEW',
INTERNAL_GET_COMMENT_OR_ERROR(tbl.comment)) AS TABLE_COMMENT
FROM mysql.tables tbl JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
LEFT JOIN mysql.collations col ON tbl.collation_id=col.id
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden;
--
-- INFORMATION_SCHEMA.COLUMNS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.columns AS
SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
col.ordinal_position AS ORDINAL_POSITION,
IFNULL (col.default_value_utf8, '') AS COLUMN_DEFAULT,
IF (col.is_nullable = 1, 'YES','NO') AS IS_NULLABLE,
SUBSTRING_INDEX(SUBSTRING_INDEX(col.column_type_utf8, '(', 1), ' ', 1) AS
DATA_TYPE,
INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 0) AS
CHARACTER_MAXIMUM_LENGTH,
INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 1) AS
CHARACTER_OCTET_LENGTH,
IF (col.numeric_precision = 0, NULL, col.numeric_precision) AS
NUMERIC_PRECISION,
IF (col.numeric_scale = 0 && col.numeric_precision = 0, NULL,
col.numeric_scale) AS NUMERIC_SCALE,
col.datetime_precision AS DATETIME_PRECISION,
CASE col.type
WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, cs.name))
WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, cs.name))
WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, cs.name))
WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, cs.name))
WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, cs.name))
WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, cs.name))
ELSE NULL
END AS CHARACTER_SET_NAME,
CASE col.type
WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, coll.name))
WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, coll.name))
WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, coll.name))
WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, coll.name))
WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, coll.name))
WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, coll.name))
WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, coll.name))
WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, coll.name))
WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, coll.name))
ELSE NULL
END AS COLLATION_NAME,
col.column_type_utf8 AS COLUMN_TYPE,
col.column_key AS COLUMN_KEY,
IF(IFNULL(col.generation_expression_utf8,'IS_NOT_GC')='IS_NOT_GC',
IF (col.is_auto_increment=TRUE,
CONCAT(IFNULL(CONCAT("on update ", col.update_option, " "),''),
"auto_increment"),
IFNULL(CONCAT("on update ", col.update_option),'')),
IF(col.is_virtual, "VIRTUAL GENERATED", "STORED GENERATED")) AS EXTRA,
GET_DD_COLUMN_PRIVILEGES(sch.name, tbl.name, col.name) AS `PRIVILEGES`,
IFNULL(col.comment, "") AS COLUMN_COMMENT,
IFNULL(col.generation_expression_utf8, "") AS GENERATION_EXPRESSION
FROM mysql.columns col JOIN mysql.tables tbl ON col.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll ON col.collation_id=coll.id
JOIN mysql.character_sets cs ON coll.character_set_id= cs.id
WHERE CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name) AND NOT tbl.hidden;
--
-- INFORMATION_SCHEMA.STATISTICS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.statistics_base AS
(SELECT cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE','0','1') AS NON_UNIQUE,
sch.name AS INDEX_SCHEMA,
idx.name AS INDEX_NAME,
icu.ordinal_position AS SEQ_IN_INDEX,
col.name AS COLUMN_NAME,
CASE WHEN icu.order = 'DESC' THEN 'D'
WHEN icu.order = 'ASC' THEN 'A'
ELSE NULL END AS COLLATION,
GET_DD_INDEX_SUB_PART_LENGTH(icu.length, col.type, col.char_length,
col.collation_id, idx.options) AS SUB_PART,
NULL AS PACKED,
if (col.is_nullable = 1, 'YES','') AS NULLABLE,
CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL'
WHEN idx.algorithm = 'SE_PRIVATE' THEN ''
ELSE idx.algorithm END AS INDEX_TYPE,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',
'',IF(INTERNAL_KEYS_DISABLED(sch.name, tbl.name,
tbl.options),'disabled', ''))
AS COMMENT,
idx.comment AS INDEX_COMMENT,
IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
idx.ordinal_position AS INDEX_ORDINAL_POSITION,
icu.ordinal_position AS COLUMN_ORDINAL_POSITION,
tbl.engine AS ENGINE,
tbl.se_private_id AS SE_PRIVATE_ID
FROM mysql.index_column_usage icu JOIN mysql.indexes idx ON
idx.id=icu.index_id
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.columns col ON icu.column_id=col.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll ON tbl.collation_id=coll.id
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden);
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.statistics
AS
(SELECT TABLE_CATALOG,
TABLE_SCHEMA,
sb.TABLE_NAME AS `TABLE_NAME`,
NON_UNIQUE,
INDEX_SCHEMA,
sb.INDEX_NAME AS `INDEX_NAME`,
SEQ_IN_INDEX,
sb.COLUMN_NAME AS `COLUMN_NAME`,
COLLATION,
stat.cardinality AS CARDINALITY,
SUB_PART,
PACKED,
NULLABLE,
INDEX_TYPE,
COMMENT,
INDEX_COMMENT,
IS_VISIBLE
FROM information_schema.statistics_base sb
LEFT JOIN mysql.index_stats stat
ON sb.table_name=stat.table_name
and sb.table_schema=stat.schema_name
and sb.index_name=stat.index_name
and sb.column_name=stat.column_name);
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.statistics_dynamic AS
(SELECT TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
NON_UNIQUE,
INDEX_SCHEMA,
INDEX_NAME,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION,
INTERNAL_INDEX_COLUMN_CARDINALITY(TABLE_SCHEMA,TABLE_NAME, INDEX_NAME,
INDEX_ORDINAL_POSITION,
COLUMN_ORDINAL_POSITION,
ENGINE,
SE_PRIVATE_ID)
AS CARDINALITY,
SUB_PART,
PACKED,
NULLABLE,
INDEX_TYPE,
COMMENT,
INDEX_COMMENT,
IS_VISIBLE
FROM information_schema.statistics_base);
--
-- INFORMATION_SCHEMA.TABLE_CONSTRAINTS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.table_constraints AS
(SELECT cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
CONVERT(idx.name USING utf8) AS CONSTRAINT_NAME,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
IF (idx.type='PRIMARY', 'PRIMARY KEY', idx.type) AS CONSTRAINT_TYPE
FROM mysql.indexes idx JOIN mysql.tables tbl ON idx.table_id = tbl.id
JOIN mysql.schemata sch ON tbl.schema_id= sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
AND idx.type IN ('PRIMARY', 'UNIQUE')
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden)
UNION
(SELECT cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
CONVERT(fk.name USING utf8) AS CONSTRAINT_NAME,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
'Foreign Key' AS CONSTRAINT_TYPE
FROM mysql.foreign_keys fk JOIN mysql.tables tbl ON fk.table_id = tbl.id
JOIN mysql.schemata sch ON fk.schema_id= sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden);
--
-- INFORMATION_SCHEMA.KEY_COLUMN_USAGE
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.key_column_usage AS
(SELECT cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
CONVERT(idx.name USING utf8) AS CONSTRAINT_NAME,
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
icu.ordinal_position AS ORDINAL_POSITION,
NULL AS POSITION_IN_UNIQUE_CONSTRAINT,
NULL AS REFERENCED_TABLE_SCHEMA,
NULL AS REFERENCED_TABLE_NAME,
NULL AS REFERENCED_COLUMN_NAME
FROM mysql.indexes idx JOIN mysql.tables tbl ON idx.table_id = tbl.id
JOIN mysql.schemata sch ON tbl.schema_id= sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.index_column_usage icu ON icu.index_id=idx.id
JOIN mysql.columns col ON icu.column_id=col.id
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden)
UNION
(SELECT cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
CONVERT(fk.name USING utf8) AS CONSTRAINT_NAME,
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
fkcu.ordinal_position AS ORDINAL_POSITION,
icu.ordinal_position AS POSITION_IN_UNIQUE_CONSTRAINT,
fk.referenced_table_schema AS REFERENCED_TABLE_SCHEMA,
fk.referenced_table_name AS REFERENCED_TABLE_NAME,
fkcu.referenced_column_name AS REFERENCED_COLUMN_NAME
FROM mysql.foreign_keys fk JOIN mysql.tables tbl ON fk.table_id = tbl.id
JOIN mysql.foreign_key_column_usage fkcu ON fkcu.foreign_key_id=fk.id
JOIN mysql.schemata sch ON fk.schema_id= sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.columns col ON fkcu.column_id=col.id
JOIN mysql.indexes idx ON fk.unique_constraint_id=idx.id
JOIN mysql.index_column_usage icu ON idx.id=icu.index_id
WHERE CAN_ACCESS_TABLE(sch.name, tbl.name) AND NOT tbl.hidden);
--
-- INFORMATION_SCHEMA.VIEWS
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW information_schema.views AS
SELECT cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
vw.name AS TABLE_NAME,
vw.view_definition_utf8 AS VIEW_DEFINITION,
vw.view_check_option AS CHECK_OPTION,
vw.view_is_updatable AS IS_UPDATABLE,
vw.view_definer AS DEFINER,
IF (vw.view_security_type = 'DEFAULT', 'DEFINER', vw.view_security_type)
AS SECURITY_TYPE,
(SELECT cs.name
FROM mysql.collations coll JOIN mysql.character_sets cs
ON coll.character_set_id= cs.id
WHERE coll.id=vw.view_client_collation_id) AS CHARACTER_SET_CLIENT,
(SELECT coll.name
FROM mysql.collations coll
WHERE coll.id=vw.view_connection_collation_id) AS COLLATION_CONNECTION
FROM mysql.tables vw JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
WHERE vw.type = 'VIEW' AND CAN_ACCESS_TABLE(sch.name, vw.name);
--
-- INFORMATION SCHEMA VIEWS implementing SHOW statements
--
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.show_statistics AS
(SELECT
TABLE_SCHEMA as `Database`,
sb.TABLE_NAME AS `Table`,
NON_UNIQUE AS `Non_unique`,
sb.INDEX_NAME AS `Key_name`,
SEQ_IN_INDEX AS `Seq_in_index`,
sb.COLUMN_NAME AS `Column_name`,
COLLATION AS `Collation`,
stat.cardinality AS `Cardinality`,
SUB_PART AS `Sub_part`,
PACKED AS `Packed`,
NULLABLE AS `Null`,
INDEX_TYPE AS `Index_type`,
COMMENT AS `Comment`,
INDEX_COMMENT AS `Index_comment`,
IS_VISIBLE AS `Visible`,
INDEX_ORDINAL_POSITION,
COLUMN_ORDINAL_POSITION
FROM information_schema.statistics_base sb
LEFT JOIN mysql.index_stats stat
ON sb.table_name=stat.table_name
and sb.table_schema=stat.schema_name
and sb.index_name=stat.index_name
and sb.column_name=stat.column_name);
CREATE OR REPLACE DEFINER=`root`@`localhost` VIEW
information_schema.show_statistics_dynamic AS
(SELECT
TABLE_SCHEMA as `Database`,
TABLE_NAME AS `Table`,
NON_UNIQUE AS `Non_unique`,
INDEX_NAME AS `Key_name`,
SEQ_IN_INDEX AS `Seq_in_index`,
COLUMN_NAME AS `Column_name`,
COLLATION AS `Collation`,
INTERNAL_INDEX_COLUMN_CARDINALITY(TABLE_SCHEMA, TABLE_NAME, INDEX_NAME,
INDEX_ORDINAL_POSITION,
COLUMN_ORDINAL_POSITION,
ENGINE,
SE_PRIVATE_ID)
AS `Cardinality`,
SUB_PART AS `Sub_part`,
PACKED AS `Packed`,
NULLABLE AS `Null`,
INDEX_TYPE AS `Index_type`,
COMMENT AS `Comment`,
INDEX_COMMENT AS `Index_comment`,
IS_VISIBLE AS `Visible`,
INDEX_ORDINAL_POSITION,
COLUMN_ORDINAL_POSITION
FROM information_schema.statistics_base);
2. Definitions of new data dictionary tables added by this WL :
---------------------------------------------------------------
CREATE TABLE `table_stats` (
`schema_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_rows` bigint(20) unsigned DEFAULT NULL,
`avg_row_length` bigint(20) unsigned DEFAULT NULL,
`data_length` bigint(20) unsigned DEFAULT NULL,
`max_data_length` bigint(20) unsigned DEFAULT NULL,
`index_length` bigint(20) unsigned DEFAULT NULL,
`data_free` bigint(20) unsigned DEFAULT NULL,
`auto_increment` bigint(20) unsigned DEFAULT NULL,
`checksum` bigint(20) unsigned DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL,
`check_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`schema_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `index_stats` (
`schema_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`column_name` varchar(64) COLLATE utf8_bin NOT NULL,
`cardinality` bigint(20) unsigned DEFAULT NULL,
UNIQUE KEY `schema_name`
(`schema_name`,`table_name`,`index_name`,`column_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
3. New SE API's introduced:
--------------------------
The following new handlerton API's are introduced to read the
table statistics from storage engine. This is currently implemented
only for 'InnoDB' engine.
/**
@brief
Retrieve ha_statistics from SE.
@param1 db_name Name of schema
@param2 table_name Name of table
@param3 se_private_id SE private id of the table.
@param4 flags Type of statistics to retrieve.
@param5 stats (OUT) Contains statistics read from SE.
@returns false on success,
true on failure
*/
bool (*get_table_statistics)(const char *db_name,
const char *table_name,
dd::Object_id se_private_id,
uint flags,
ha_statistics &stats);
/**
@brief
Retrieve index column cardinality from SE.
@param1 db_name Name of schema
@param2 table_name Name of table
@param3 index_name Name of index
@param4 index_ordinal_position Position of index.
@param5 column_ordinal_position Position of column in index.
@param6 se_private_id SE private id of the table.
@param7 cardinality (OUT) cardinality being returned by SE.
@returns false on success,
true on failure
*/
bool (*get_index_column_cardinality)(const char *db_name,
const char *table_name,
const char *index_name,
uint index_ordinal_position,
uint column_ordinal_position,
dd::Object_id se_private_id,
ulonglong *cardinality);
4. Change made in mysqlpump tool:
With the introduction of new information schema views on top
of new data dictionary, the way the SHOW command works is
changed. We now have two ways of SHOW command picking table
statistics.
One is to read it from DD table mysql.table_stats and
mysql.index_stats. For this to happen, we need to execute
ANALYZE TABLE prior to execution of mysqlpump tool. As the
tool can run on whole database, we would end-up running
ANALYZE TABLE for all tables in the database irrespective of
whether statistics are already present in the statictics
tables. This could be a time consuming additional step to
carry.
Second option is to read statistics from SE itself. This
options looks safe and execution of mysqlpump tool need not
care if ANALYZE TABLE command was run on every table. We
always get the statistics, which match the behavior without
data dictionary.
The first option would be faster as we do not opening the
underlying tables during execution of SHOW command. However
the first option might read old statistics, so we feel second
option is preferred here to get statistics dynamically from
SE by setting information_schema_stats=latest for this
session, which would match the old behavior in 5.7.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.