mysql 库_MYSQL是什么

mysql 库_MYSQL是什么1. information_schema库 1. 字符集和排序规则有关的系统表 CHARACTER_SETS :存储数据库相关字符集信息(memory存储引擎) COLLATIONS :字符集对应的排序规则 COLLATION_CHARACTER_SET_APPLICABILITY:就是一个字符集

mysql

1.  information_schema库

    1.  字符集和排序规则有关的系统表

        CHARACTER_SETS :存储数据库相关字符集信息(memory存储引擎)

        COLLATIONS :字符集对应的排序规则

        COLLATION_CHARACTER_SET_APPLICABILITY:就是一个字符集和连线校对的一个对应关系而已       

下面我们说一下character sets和collations的区别:
字符集(character sets)存储字符串,是指人类语言中最小的表义符号。例如’A'、’B'等;
排序规则(collations)规则比较字符串,collations是指在同一字符集内字符之间的比较规则
每个字符序唯一对应一种字符集,但一个字符集可以对应多种字符序,其中有一个是默认字符序(Default Collation)
 MySQL中的字符序名称遵从命名惯例:以字符序对应的字符集名称开头;以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如:在字符序“utf8_general_ci”下,字符“a”和“A”是等价的
看一下有关于字符集和校对相关的MySQL变量:
character_set_server:默认的内部操作字符集
character_set_client:客户端来源数据使用的字符集
character_set_connection:连接层字符集
character_set_results:查询结果字符集
character_set_database:当前选中数据库的默认字符集
character_set_system:系统元数据(字段名等)字符集
再看一下MySQL中的字符集转换过程:
(1). MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection;
(2). 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集,其确定方法如下:
使用每个数据字段的CHARACTER SET设定值;
若上述值不存在,则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展,非SQL标准);
若上述值不存在,则使用对应数据库的DEFAULT CHARACTER SET设定值;
若上述值不存在,则使用character_set_server设定值。
(3). 将操作结果从内部操作字符集转换为character_set_results。

    2.  权限相关的一些表

        SCHEMA_PRIVILEGES:提供了数据库的相关权限,这个表是内存表是从mysql.db中拉去出来的。

        TABLE_PRIVILEGES:提供的是表权限相关信息,信息是从 mysql.tables_priv 表中加载的

        COLUMN_PRIVILEGES :这个表可以清楚就能看到表授权的用户的对象,那张表那个库以及授予的是什么权限,如果授权的时候加上with grant option的话,我们可以看得到PRIVILEGE_TYPE这个值必须是YES。

        USER_PRIVILEGES:提供的是表权限相关信息,信息是从 mysql.user 表中加载的

        通过表我们可以很清晰看得到MySQL授权的层次,SCHEMA,TABLE,COLUMN级别,当然这些都是基于用户来授予的。可以看得到MySQL的授权也是相当的细密的,可以具体到列,这在某一些应用场景下还是很有用的,比如审计等。

    3.  存储数据库系统的实体对象的一些表

        COLUMNS:存储表的字段信息,所有的存储引擎

        INNODB_SYS_COLUMNS :存放的是INNODB的元数据, 他是依赖于SYS_COLUMNS这个统计表而存在的。

        ENGINES :引擎类型,是否支持这个引擎,描述,是否支持事物,是否支持分布式事务,是否能够支持事物的回滚点

        EVENTS :记录MySQL中的事件,类似于定时作业

        FILES :这张表提供了有关在MySQL的表空间中的数据存储的文件的信息,文件存储的位置,这个表的数据是从InnoDB in-memory中拉取出来的,所以说这张表本身也是一个内存表,每次重启重新进行拉取。也就是我们下面要说的INNODB_SYS_DATAFILES这张表。还要注意一点的是这张表包含有临时表的信息,所以说和SYS_DATAFILES 这张表是不能够对等的,还是要从INNODB_SYS_DATAFILES看。如果undo表空间也配置是InnoDB 的话,那么也是会被记录下来的。

        PARAMETERS :参数表存储了一些存储过程和方法的参数,以及存储过程的返回值信息。存储和方法在ROUTINES里面存储。

        PLUGINS :基本上是MySQL的插件信息,是否是活动状态等信息。其实SHOW PLUGINS本身就是通过这张表来拉取的数据

        ROUTINES:关于存储过程和方法function的一些信息,不过这个信息是不包括用户自定义的,只是系统的一些信息。

        SCHEMATA:这个表提供了实例下有多少个数据库,而且还有数据库默认的字符集

        TRIGGERS :这个表记录的就是触发器的信息,包括所有的相关的信息。系统的和自己用户创建的触发器。

        VIEWS :视图的信息,也是系统的和用户的基本视图信息。

    4.  约束外键相关的一些表

        REFERENTIAL_CONSTRAINTS:这个表提供的外键相关的信息,而且只提供外键相关信息

        TABLE_CONSTRAINTS :这个表提供的是 相关的约束信息

        INNODB_SYS_FOREIGN_COLS :这个表也是存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的

        INNODB_SYS_FOREIGN :存储的INNODB关于外键的元数据信息和SYS_FOREIGN_COLS 存储的信息是一致的,只不过是单独对于INNODB来说的

        KEY_COLUMN_USAGE:数据库中所有有约束的列都会存下下来,也会记录下约束的名字和类别

    5.  管理的一些表

        GLOBAL_STATUS ,GLOBAL_VARIABLES,SESSION_STATUS,SESSION_VARIABLES:这四张表分别记录了系统的变量,状态(全局和会话的信息),作为DBA相信大家也都比较熟悉了,而且这几张表也是在系统重启的时候回重新加载的。也就是内存表。

        PARTITIONS :MySQL分区表相关的信息,通过这张表我们可以查询到分区的相关信息(数据库中已分区的表,以及分区表的分区和每个分区的数据信息)

        PROCESSLIST:show processlist其实就是从这个表拉取数据,PROCESSLIST的数据是他的基础。由于是一个内存表,所以我们相当于在内存中查询一样,这些操作都是很快的。

        INNODB_CMP_PER_INDEX,INNODB_CMP_PER_INDEX_RESET:这两个表存储的是关于压缩INNODB信息表的时候的相关信息,有关整个表和索引信息都有.我们知道对于一个INNODB压缩表来说,不管是数据还是二级索引都是会被压缩的,因为数据本身也可以看作是一个聚集索引。
        INNODB_CMPMEM ,INNODB_CMPMEM_RESET:这两个表是存放关于MySQL INNODB的压缩页的buffer pool信息,但是要注意一点的就是,用这两个表来收集所有信息的表的时候,是会对性能造成严重的影响的,所以说默认是关闭状态的。如果要打开这个功能的话我们要设置innodb_cmp_per_index_enabled参数为ON状态。
        INNODB_BUFFER_POOL_STATS :表提供有关INNODB 的buffer pool相关信息,和show engine innodb status提供的信息是相同的。也是show engine innodb status的信息来源。
        INNODB_BUFFER_PAGE_LRU,INNODB_BUFFER_PAGE :维护了INNODB LRU LIST的相关信息,详细请见小编笔记
innodb buffer pool小解
        INNODB_BUFFER_PAGE :这个表就比较屌了,存的是buffer里面缓冲的页数据。查询这个表会对性能产生很严重的影响,千万不要再我们自己的生产库上面执行这个语句,除非你能接受服务短暂的停顿
        INNODB_SYS_DATAFILES :这张表就是记录的表的文件存储的位置和表空间的一个对应关系(INNODB)
        INNODB_TEMP_TABLE_INFO :这个表会记录所有的INNODB的所有用户使用到的信息,但是只能记录在内存中和没有持久化的信息。
        INNODB_METRICS :提供INNODB的各种的性能指数,是对INFORMATION_SCHEMA的补充,收集的是MySQL的系统统计信息。这些统计信息都是可以手动配置打开还是关闭的。有以下参数都是可以控制的:innodb_monitor_enable, innodb_monitor_disable, innodb_monitor_reset, innodb_monitor_reset_all。
        INNODB_SYS_VIRTUAL :表存储的是INNODB表的虚拟列的信息,当然这个还是比较简单的,在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与实际存储一列数据相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。
        INNODB_CMP,INNODB_CMP_RESET:存储的是关于压缩INNODB信息表的时候的相关信息,详细请见推荐笔记。

    6.  表信息和索引信息的一些表

        TABLES  这张表毫无疑问了,就是记录的数据库中表的信息,其中包括系统数据库和用户创建的数据库。show table status like ‘test1’\G的来源就是这个表;

        TABLESPACES 却是标注的活跃表空间。 这个表是不提供关于innodb的表空间信息的,对于我们来说并没有太大作用,因为我们生产库是强制INNODB的;

        INNODB_SYS_TABLES 这张表依赖的是SYS_TABLES数据字典中拉取出来的。此表提供了有关表格的格式和存储特性,包括行格式,压缩页面大小位级别的信息(如适用)提供的是关于INNODB的表空间信息,其实和SYS_TABLESPACES 中的INNODB信息是一致的。
        STATISTICS:这个表提供的是关于表的索引信息,所有索引的相关信息。
        INNODB_SYS_INDEXES:提供相关INNODB表的索引的相关信息,和SYS_INDEXES 这个表存储的信息基本是一样的,只不过后者提供的是所有存储引擎的索引信息,后者只提供INNODB表的索引信息。
        INNODB_SYS_TABLESTATS:这个表就比较重要了,记录的是MySQL的INNODB表信息以及MySQL优化器会预估SQL选择合适的索引信息,其实就是MySQL数据库的统计信息
这个表的记录是记录在内存当中的,是一个内存表,每次重启后就会重新记录,所以只能记录从上次重启后的数据库统计信息。有了这个表,我们对于索引的维护就更加方便了,我们可以查询索引的使用次数,方便清理删除不常用的索引,提高表的更新插入等效率,节省磁盘空间。
        INNODB_SYS_FIELDS :这个表记录的是INNODB的表索引字段信息,以及字段的排名
        INNODB_FT_CONFIG :这张表存的是全文索引的信息
        INNODB_FT_DEFAULT_STOPWORD:这个表存放的是stopword 的信息,是和全文索引匹配起来使用的,和innodb的 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 是相同的,这个STOPWORD必须是在创建索引之前创建,而且必须指定字段为varchar。stopword 也就是我们所说的停止词,全文检索时,停止词列表将会被读取和检索,在不同的字符集和排序方式下,会造成命中失败或者找不到此数据,这取决于停止词的不同的排序方式。我们可以使用这个功能筛选不必要字段。
        INNODB_FT_INDEX_TABLE:这个表存储的是关于INNODB表有全文索引的索引使用信息的,同样这个表也是要设置innodb_ft_aux_table以后才能够使用的,一般情况下是空的
        INNODB_FT_INDEX_CACHE :这张表存放的是插入前的记录信息,也是为了避免DML时候昂贵的索引重组

    7.  Mysql优化相关的一些表

        OPTIMIZER_TRACE :提供的是优化跟踪功能产生的信息.关于这个我也谢了做了一个小测试

        PROFILING:SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能,不能查看和剖析其他连接的语句,以及剖析时所引起的消耗。SHOW PROFILES显示最近发给服务器的多条语句,条数根据会话变量profiling_history_size定义,默认是15,最大值为100。设为0等价于关闭分析功能。详细信息请见MySQL profile

        INNODB_FT_BEING_DELETED,INNODB_FT_DELETED: INNODB_FT_BEING_DELETED 这张表是INNODB_FT_DELETED的一个快照,只在OPTIMIZE TABLE 的时候才会使用。

    8.  Mysql事务和锁的一些表

        INNODB_LOCKS:现在获取的锁,但是不含没有获取的锁,而且只是针对INNODB的。

        INNODB_LOCK_WAITS:系统锁等待相关信息,包含了阻塞的一行或者多行的记录,而且还有锁请求和被阻塞改请求的锁信息等。

        INNODB_TRX:包含了所有正在执行的的事物相关信息(INNODB),而且包含了事物是否被阻塞或者请求锁。

2.  mysql库

    mysql库是核心数据库,类似于sql server中的master表,主要负责存储数据库的用户(账户)信息、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除                                        

3.  sys库

    Sys库所有的数据源来自:performance_schema。目标是把performance_schema的把复杂度降低,让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

    Sys库下有两种表

      字母开头:  适合人阅读,显示是格式化的数据

      x$开头:  适合工具采集数据,原始类数据

    1.  按 host 分组统计视图

        host_summary_by_file_io
        host_summary
        host_summary_by_statement_latency
    2.  按 user 分组统计视图
        user_summary
        user_summary_by_file_io
        user_summary_by_statement_latency
        user_summary_by_statement_type
    3.  语句效率统计视图
        schema_tables_with_full_table_scans
        statement_analysis
        statements_with_errors_or_warnings
        statements_with_full_table_scans
    4.  统计信息查询视图
        schema_auto_increment_columns
        schema_index_statistics
        schema_object_overview
        schema_redundant_indexes
        schema_table_statistics
        schema_table_statistics_with_buffer
        schema_unused_indexes
    5.  会话和锁信息查询视图
        innodb_lock_waits
        processlist
        session
        schema_table_lock_waits
    6.  查询系统里执行最多的TOP 10 SQL        

        select * from statement_analysis order by exec_count desc limit 10\G;

    7.  查询IO最高的表

        select * from io_global_by_file_by_bytes limit 10;

    8.  查询延迟比较严重语句

        select * from statement_analysis order by avg_latency desc limit 2;

    9.  查询使用磁盘临时表的SQL语句        

        select db, query, tmp_tables,tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;

    10.  查询占用了最多的buffer pool的表

        select * from innodb_buffer_stats_by_table order by pages desc limit 10;

    11.  查询每个库占用多少buffer pool       

        select * from innodb_buffer_stats_by_schema;                

    12.  查询每个连接分配多少内存

        select b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id;

4.  performance_schema库

    1.  开启mysql性能模式

        查看  mysql> SHOW VARIABLES LIKE ‘performance_schema’;

        修改  在配置文件中,添加内容:

            [mysqld]

            performance_schema=ON

    2.  performance_schema库中表的分类

--语句事件记录表,这些表记录了语句事件信息,当前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary,其中,summary表还可以根据帐号(account),主机(host),程序(program),线程(thread),用户(user)和全局(global)再进行细分)
show tables like '%statement%';

--等待事件记录表,与语句事件类型的相关记录表类似:
show tables like '%wait%';

--阶段事件记录表,记录语句执行的阶段事件的表
show tables like '%stage%';

--事务事件记录表,记录事务相关的事件的表
show tables like '%transaction%';

--监控文件系统层调用的表
show tables like '%file%';

--监视内存使用的表
show tables like '%memory%';

--动态对performance_schema进行配置的配置表
show tables like '%setup%';

    3.  实际分析      

--1、哪类的SQL执行最多?
SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--2、哪类SQL的平均响应时间最多?
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--3、哪类SQL排序记录数最多?
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--4、哪类SQL扫描记录数最多?
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--5、哪类SQL使用临时表最多?
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--6、哪类SQL返回结果集最多?
SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
--7、哪个表物理IO最多?
SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
--8、哪个表逻辑IO最多?
SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
--9、哪个索引访问最多?
SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
--10、哪个索引从来没有用过?
SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
--11、哪个等待事件消耗时间最多?
SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
--12-1、剖析某条SQL的执行情况,包括statement信息,stege信息,wait信息
SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';
--12-2、查看每个阶段的时间消耗
SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
--12-3、查看每个阶段的锁等待情况
SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

  

      

                                              

                          

今天的文章mysql 库_MYSQL是什么分享到此就结束了,感谢您的阅读,如果确实帮到您,您可以动动手指转发给其他人。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/52088.html

(0)
编程小号编程小号
上一篇 2023-08-31 18:17
下一篇 2023-08-31 18:46

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注