The official team conducted a test regarding this and the results showed that the query performance to the IS table was greatly improved. In addition, the optimizer can also choose a better execution plan for the query to the IS table (For example, using the index on the system table for the query). After it is switched to query the physical table, it is not required to create a temporary table for queries to every IS table (The operations of temporary tables include frm file creation at the server layer, and obtaining data or global data requiring lock protection at the engine layer). This method has actually greatly accelerated the query speed to the IS table. That is to say, although the DD system table is hidden and invisible, you can still obtain most of its information through the View. row ***************************Ĭreate View: CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER 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`)
Mysql> show create table information_schema.schemata\G Some new IS tables use the View for re-designing, mainly including the tables below: According to the descriptions in the official documents, the file is there because of a function that has not been fully realized yet. In the file, detailed information about the table is recorded in the JSON format. Here we created a MyISAM table t1, and an SDI file is created accordingly. "options": "avg_row_length=0 key_block_size=0 keys_disabled=0 pack_record=1 stats_auto_recalc=0 stats_sample_pages=0 ", Mysql> create table t1 (a int, b varchar(320)) engine=myisam
#Mysql create view with engine myisam how to#
Now let's see how to create a MyISAM engine table:
In the data directory, an SDI file is generated to describe the information of this sbtest database. We can see that there is only the ibd file in the database directory, with no frm files. rw-r- 1 yinfeng.zwx users 128K Oct 5 19:44 t2.ibd rw-r- 1 yinfeng.zwx users 128K Oct 5 19:44 t1.ibd Mysql> CREATE TABLE t2 (a int primary key, b int) Mysql> CREATE TABLE t1 (a int primary key) This is the first developer version for the Version 8.0, so it may be subject to subsequent changes.įirst, we create a new database and two tables under the database to start our testing.
#Mysql create view with engine myisam code#
Because of the many changes involved, I will expand upon the changes one by one later.Īll the tests and code in this article are based on MySQL8.0.0. I am also a beginner in this field, so I will not dig deep into the topic in this article. Some other system tables such as the permission table also switched to the InnoDB engine. All metadata is stored using the InnoDB engine. To solve these problems (especially the unavailability of atomic DDL), the FRM file and other metadata files (frm, par, trn, trg, isl, db.opt) at the server layer have been canceled since MySQL 8.0. In addition, because of the separated management on the server layer and engine layer, it is hard to achieve crash-safe during DDL operations, let alone the transaction-based DDL. However, some engines (such as InnoDB) can also store metadata.
It is widely known that since MySQL adopts the architecture with a uniform server layer + different underlying engine plug-ins, an frm file is created at the server layer for every table to save metadata information related with the table definition.
0 kommentar(er)
