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 表中加载的
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 buffer pool小解
6. 表信息和索引信息的一些表
TABLES 这张表毫无疑问了,就是记录的数据库中表的信息,其中包括系统数据库和用户创建的数据库。show table status like ‘test1’\G的来源就是这个表;
TABLESPACES 却是标注的活跃表空间。 这个表是不提供关于innodb的表空间信息的,对于我们来说并没有太大作用,因为我们生产库是强制INNODB的;
7. Mysql优化相关的一些表
OPTIMIZER_TRACE :提供的是优化跟踪功能产生的信息.关于这个我也谢了做了一个小测试
PROFILING:SHOW PROFILE可以深入的查看服务器执行语句的工作情况。以及也能帮助你理解执行语句消耗时间的情况。一些限制是它没有实现的功能,不能查看和剖析其他连接的语句,以及剖析时所引起的消耗。SHOW PROFILES显示最近发给服务器的多条语句,条数根据会话变量profiling_history_size定义,默认是15,最大值为100。设为0等价于关闭分析功能。详细信息请见MySQL profile
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