(3.15)mysql基础深入——mysql默认数据库
关键词:Mysql默认数据库,mysql系统数据库
系统数据库的组成
一共4个(5.7版本)(低版会少下列一个,多一个test默认数据库,注意test是默认安装但不是系统数据库)
【1】information_schema(可以理解成字典表) 【2】mysql(存放系统信息)
【3】performance_schema(监控、统计、分析mysql) 【4】sys(【3】库的简化)
详细分析
【1】information_schema
提供数据库的元数据:比如数据库的数据名,表名,列信息,访问权限,索引,视图,存储过程,函数等信息;
常用表: (1)查表:select * from information_schema.tables; (2)查列:select * from information_schema.columns; (3)查主键值:select * from select * from information_schema.key_column_usage where table_schema='db_name' and table_name = 'tab_name' ; (4)查约束:select * from from information_schema.table_constraints; (5)查索引:select * from from information_schema.statistics;
(6)查表在哪个库:select table_schema from tables where table_name ='tab_name' (7)查字段在哪个表:select table_schema,table_name from columns where column_name ='column_name'
【2】mysql
mysql数据库是Mysql核心的数据库,类似于sql server中的master库,oracle中的system部分功能。 主要负责存储数据库的用户/权限等Mysql自己需要使用的控制和管理信息。 比如:用户信息、权限信息、储存过程、envent、时间信息等 【2.1】成本模型 (1)engine_cost:IO (2)server_cost:CPU 【2.2】权限模型 (1)columns_priv:列权限 (2)db:库权限 (3)user:用户权限 (4)tables_priv:表权限 (5)procs_priv:存储过程/函数权限 (6)proxies_priv:代理用户权限 (7)host:主机权限,5.7已弃用 【2.3】innodb系列 (1)innodb_index_stats:Innodb索引统计信息 (2)innodb表统计信息 【2.4】其他模型 (1)event:事件与任务调度 (2)func:函数 (3)gitd_executed:gtid,mysql启动阶段会读这个表来获取gtid里面的变量值 (4)help_:以此开头的全是mysql帮助信息 (5)ndb_binlog_index:innodb表统计信息 (6)plugin:插件表 (7)porc:存储过程
【3】performance_schema
sys < performance_schema:收集数据库服务器性能参数,得到数据库的运行统计信息,可以更好的利用这些信息分析定位问题;
mysql 5.5增加的,需要使用的时候需要设置参数performance_schema=on
mysql 5.6默认打开
这个数据库的信息相当复杂,所以oracle公司官方把performance_schema简化到sys数据库。
该库中的表也分几类;
(1)setup 配置表,配置监控选项
(2)当前enents表,记录当前的哪些线程正在做一些什么事情
(3)各种事件的历史记录表。
(4)各种事件的统计表
(5)杂项表
-- 常用SQL语句 use performance_schema; (1)哪个SQL执行最多?哪个平均响应事件最多? select * from events_statements_sumary_by_digest order by count_star desc limit 10; select * from events_statements_sumary_by_digest order by avg_timer_wait desc limit 10;
(2)哪个SQL扫描的行数最多?哪个SQL临时表用的最多? select * from events_statements_sumary_by_digest order by sum_rows_examined desc limit 10;
select * from events_statements_sumary_by_digest order by sum_created_tmp_disk_tables desc limit 10;
结果集最多: order by sum_rows_sent desc
排序数最多: order by sum_sort_rows desc (3)哪个表、文件逻辑IO最多(热数据)? select * from file_sumary_by_instance order by sum_number_of_bytes_read+sum_number_of_bytes_write desc limit 2; (4)哪个索引没有使用过? select * from table_io_waits_sumary_by_index_usage where index_name is not null and count_star=0 and object_schema <> 'mysql' order by object_schema,object_name (5)哪个索引使用最多 select * from table_io_waits_sumary_by_index_usage order by sum_timer_wait desc limit 1; (6)哪个等待事件消耗的时间最多 select * from event_wais_sumary_global_by_event_name where event_name !='idle' order by sum_timer_wait desc limit 1;
【4】sys
所有的数据来自performance_schema,主要是简化DBA的工作,快速了解DB的运行情况。
有2类表
(1)字母开头的:适合阅读,是格式化后的数据;
(2)X$开头的:这是适合工具采集的加工用的数据,原始数据。
【4.1】简介库中的表信息
(1)host开头:主机信息,IP信息
(2)innodb开头:记录Innodb相关信息
(3)io开头:数据库维护的相关IO信息
(4)memory开头:内存信息
(5)processlist:常用,显示线程的相关情况
(6)schema开头:表的相关信息
(7)statements开头:语句相关不同维度的信息
(8)user开头:用户与主机相关信息
(9)wait开头:等待相关信息
【4.2】sys库能做什么?
(1)谁使用了最多的资源?基于IP或是用户?
(2)大部分连接来自哪里及发送的SQL情况?
(3)机器执行多的SQL语句是什么样?
(4)哪个文件产生了最多的IO,它的IO模式是怎么样的?
(5)哪个表的IO最多?
(6)哪张表被访问过最多?
(7)哪些语句延迟比较严重?
(8)哪些SQL语句使用了磁盘临时表
(9)哪张表占用了最多的BUFFER POOL
(10)每个库占用多少BUFFER POOL
(11)每个链接分配多少内存?
(12)mysql内部现在有多少个线程在运行?
详解: use sys; (1)谁使用了最多的资源?基于IP或是用户? select * from host_summary limit 10; select * from io_global_by_file_by_bytes limit 10; select * from user_summary limit 10; memory_global_total; (2)大部分连接来自哪里及发送的SQL情况? 查看当前链接情况:select host,current_connections,statements from host_summary; 查看当前正在执行的SQL:select conn_id,user,current_statement,last_statement from session; (3)机器执行多的SQL语句是什么样? select * from statement_analysis order by exec_count desc limit 10; (4)哪个文件产生了最多的IO,它的IO模式是怎么样的?
(5)哪个表的IO最多?(6)哪张表被访问过最多? (5) select * from io_global_by_file_by_bytes limit 10; (6) select * from statement_analysis order by exec_count desc limit10; (7)哪些语句延迟比较严重? select * from statement_analysis order by avg_latency desc limit 10; (8)哪些SQL语句使用了磁盘临时表 select db,query,temp_tables,tmp_disk_tables from statement_analysis where tmp_tables >0 (9)哪张表占用了最多的BUFFER POOL select * from innodb_buffer_stats_by_table order by pages desc limit 10; (10)每个库占用多少BUFFER POOL select * from innodb_buffer_stats_by_schema; (11)每个链接分配多少内存? (12)mysql内部现在有多少个线程在运行? select user,count(*) from processlist group by user; 查看所有的进程信息: select * from processlist ;