MySQL关于数据字典的一个疑问

今天看着MySQL的数据字典,突然想到一个问题:为什么MySQL数据字典 information_schema中的表名是大写,而performance_schema和其他库中的是小写?

带着这个问题,我开始了一些猜测和自我论证。

首先大小写的这个情况是相对不兼容的。

比如在performance_schema中,根据关键字user可以找到两个相关的表。

mysql> show tables  like 'user%';
+--------------------------------------+
| Tables_in_performance_schema (user%) |
+--------------------------------------+
| user_variables_by_thread             |
| users                                |
+--------------------------------------+
2 rows in set (0.00 sec)

但是如果我改做大写,是不能识别的,这在其他的数据库里也是类似的处理方式。

mysql> desc USERS;
ERROR 1146 (42S02): Table 'performance_schema.USERS' doesn't exist
mysql> select database();
+--------------------+
| database()         |
+--------------------+
| performance_schema |
+--------------------+
1 row in set (0.00 sec)

而在information_schema中,则是相对兼容的。

mysql> select count(*)from tables; select count(*)from TABLES;
+----------+
| count(*) |
+----------+
|      383 |
+----------+
1 row in set (0.01 sec)
+----------+
| count(*) |
+----------+
|      383 |
+----------+
1 row in set (0.00 sec)

如果从物理文件的角度来看,你会发现在MySQL中information_schema这个数据库和其他数据库不同,没有一个指定的目录存在。

[root@dev01 mysql]# ll
total 188796
-rw-r----- 1 mysql mysql       56 Jan  2 12:37 auto.cnf
-rw-r----- 1 mysql mysql        5 Mar 13 14:26 dev01.pid
drwxr-x--- 2 mysql mysql    12288 Mar  9 10:44 devopsdb
drwxr-x--- 2 mysql mysql     4096 Jan  2 12:38 dms_metadata
-rw-r----- 1 mysql mysql     1292 Jan 26 19:44 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Mar 13 23:27 ibdata1
-rw-r----- 1 mysql mysql 50331648 Mar 13 23:27 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Mar 13 23:27 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Mar 13 23:36 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jan 24 19:04 kmp
drwxr-x--- 2 mysql mysql     4096 Jan  2 12:37 mysql
-rw-r----- 1 mysql mysql   324407 Mar 13 21:54 mysqld.log
drwxr-x--- 2 mysql mysql     4096 Jan  2 12:37 performance_schema
drwxr-x--- 2 mysql mysql    12288 Jan  2 12:37 sys
drwxr-x--- 2 mysql mysql     4096 Mar 13 23:27 test

这个数据的存储就好比Oracle里面的系统表空间,所以information_schema是名副其实的数据字典库。

而performance_schema则是一个内存库,它的存储引擎是特别的一种,不是InnoDB也不是MyISAM,Memory,而是performance_schema

带着疑问我继续切换到了information_schema中,可以很明显的发现information_schema中的数据字典大多是Memory存储引擎。

mysql> show create table tables \G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
 。。。
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

还要一些是InnoDB的。

mysql>  show create table PLUGINS\G
*************************** 1. row ***************************
       Table: PLUGINS
Create Table: CREATE TEMPORARY TABLE `PLUGINS` (
  `PLUGIN_NAME` varchar(64) NOT NULL DEFAULT '',
  `PLUGIN_VERSION` varchar(20) NOT NULL DEFAULT '',
  `PLUGIN_STATUS` varchar(10) NOT NULL DEFAULT '',
。。。
  `LOAD_OPTION` varchar(64) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

所以数据字典的结构其实还算是比价繁杂,涉及多个存储引擎,涉及多中规则和处理方式。

如果我们仔细查看上面的语句,就会发现,这些数据字典都是temporary table.

明白了这些,对我们分析问题的方向就很有利了。

所以我的初步设想就是通过这种命名方式能够标识出来它就是临时表,避免混淆。

怎么理解呢。

如果一个数据库中存在一个临时表,一个普通表,名字都是test,可不可行?

不要猜行不行,而是快速验证一下。

mysql> create table tmp (id int,name varchar(30));
Query OK, 0 rows affected (0.09 sec)
mysql> create temporary table tmp(id int,name varchar(30));

Query OK, 0 rows affected (0.00 sec)

这个时候插入一条记录,显示成功,但是我们却没有办法判断到底是插入到了哪个表里。

mysql> insert into tmp values(1,'aa');
Query OK, 1 row affected (0.00 sec)

所以我们可以用排除的方式来验证,我们删掉tmp,然后查看剩下的数据到底在哪里?

删除成功,但是这个时候我们还需要其他的信息来佐证。

mysql> drop table tmp ;
Query OK, 0 rows affected (0.00 sec)

查看tmp的定义信息,很明显drop的tmp是临时表。

mysql> show create table tmp ;
+-------+---------------------------------------------+
| Table | Create Table                                                                                           
+-------+--------------------------------------------+
| tmp   | CREATE TABLE `tmp` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------+
1 row in set (0.00 sec)

那么插入的数据到了哪里呢,一查便知,显示为0,则很显然数据是插入到了临时表tmp中。

mysql> select count(*)from tmp ;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

而如果我们继续换个思路,定义两个表,一个是大写的TABLES,一个是小写的tables

则默认的情况下也是不会冲突的,尽管tables是在数据字典层面的一个表,但是在其他数据库中依旧可以正常处理,命名还是不会冲突。

mysql> create table TABLES  (id INT );
Query OK, 0 rows affected (0.12 sec)
mysql> create table tables  (id INT );
Query OK, 0 rows affected (0.11 sec)

所以这个问题的初步理解就是为了在数据字典层面作为一种清晰的标识,而如果想得到更多的信息,还是得翻翻代码的实现了。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2018-03-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏技术博客

Oracle初级性能优化总结

  关于对Oracle数据库查询性能优化的一个简要的总结。 从来数据库优化都是一项艰巨的任务。对于大数据量,访问频繁的系统,优化工作显得尤为重要。由于Oracl...

15330
来自专栏乐沙弥的世界

ORA-06502 assigning values from SQL to PL/SQL variables

    最近SQL查询返回的结果给PL/SQL变量出现ORA-06502错误。这个错误的描述是ORA-06502: PL/SQL: numeric or val...

7210
来自专栏chenssy

MySQL的索引是什么?怎么优化?

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,...

10330
来自专栏Pythonista

Mysql基础

数据库(database,DB)是指长期存储在计算机内的,有组织,可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储,具有较小的冗余,较高的数据...

25420
来自专栏Albert陈凯

2018-07-20 oracle优化:避免全表扫描

例如:在City-State-Zip列创建了三列复合索引,那么仅对State列限定条件不能使用这个索引,因为State不是索引的主列。

27040
来自专栏「3306 Pai」社区

NOT NULL列用IS NULL也能查到数据?

有没有觉得很奇怪,为什么查到了2条 dt 列值为 '0000-00-00 00:00:00' 的记录?

10100
来自专栏沃趣科技

语句效率统计视图 | 全方位认识 sys 系统库

在上一篇《统计信息查询视图|全方位认识 sys 系统库》中,我们介绍了利用sys 系统库的查询统计信息的快捷视图,本期将为大家介绍语句查询效率语句统计信息相关的...

26350
来自专栏PHP在线

MySQL索引入门简述

用来加快查询的技术很多,其中最重要的是索引。通常索引能够快速提高查询速度。如果不适用索引,MYSQL必须从第一条记录开始读完整个表,直到找出相关的行。表越大,花...

15430
来自专栏眯眯眼猫头鹰的小树杈

猫头鹰的深夜翻译:如何优化MYSQL查询

索引除了能够确保唯一的标记一条记录,还能是MySQL服务器更快的从数据库中获取结果。索引在排序中的作用也非常大。

9420
来自专栏逍遥剑客的游戏开发

汉字转成拼音首字母

49360

扫码关注云+社区

领取腾讯云代金券