4.2.6-企业级MySQL数据库SQL语言开发与应用实战—MySQL数据库基本功能与参数文件—MySQL默认数据库(mysql/sys/infromation/等)
一、MySQL默认数据库
1、默认数据库
information_schema mysql --核心数据库(类似于Oracle的system数据库) performance_schema sys
二、MySQL库
1、mysql库的作用
mysql:是mysql核心的数据库,类似于sqlserver中的master库,oracle中的system部分功能。主要负责存储数据库的用户\权限等。
mysql自己需要使用的控制和管理信息
-- 重要:不能删,也不要随便去修改里面的表信息
2、库中的表有哪些
mysql> show tables; +------------------------------------------------------+ | Tables_in_mysql | +------------------------------------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | password_history | | plugin | | procs_priv | | proxies_priv | | replication_asynchronous_connection_failover | | replication_asynchronous_connection_failover_managed | | replication_group_configuration_version | | replication_group_member_actions | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +------------------------------------------------------+ 38 rows in set (0.01 sec)
3、表的作用
(1)成本模型
engine_cost: IO
server_cost:cpu
(2)权限相关的表:
columns_priv --列级别权限 db -- 库级别权限 user --用户帐号,全局权限 host -- 现在不用了,废弃的 tables_priv -- 表级权限 procs_priv -- 存储过程与函数相关的权限 proxies_priv -- 代理用户的权限
(3)其他表
event --事件与任务调度 func --函数 gtid_executed --gtid,mysql启动阶段会读这个表来获取gtid里面的变量值 innodb_index_stats --innodb索引统计信息 innodb_table_stats -- innodb表统计信息 ndb_binlog_index --ndb集群的二进制日志索引信息 plugin -- 插件表
三、SYS库
1、sys库的作用
sys库所有的数据来自performance schema,主要是简化DBA的工作,快速了解DB的运行情况.
sys库下面有两类表:
(1)字母开头的:适合阅读,是格化后的数据
(2)X$开头的:这是适合工具采售加工用的数据,原始数据。
2、SYS库能做什么
(1) 谁使用了最多的资源? 基于 IP或是用户
select * from host_summary limit 1;
select * from io_global_by_file_by_bytes limit 1;
select * from user_summary limit 1;
select * from 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最多?哪张表访问次数最多
select * from io_global_by_file_by_bytes limit 10; select * from statement_analysis order by exec_count desc limit 10;
(5)哪些语句延迟比较严重
select * from statement_analysis order by avg_latency desc limit 10;
(6)哪些 SQL语句使用了磁盘临时表
select db, query, tmp_tables,tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 rder by (tmp_tables+tmp_disk_tables) desc limit 20;
(7)哪张表占用了最多的 buffer pool
select * from innodb_buffer_stats_by_table order by pages desc limit 10;
(8)每个库占用多少 buffer pool
select * from innodb_buffer_stats_by_schema;
(9)每个连接分配多少内存
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;
(10)MySQL内部现在有多个线程在运行
select user, COUNT(*) from processlist group by user; select * from processlist;
四、performance_schema库
1、performance的作用
sys库来自于performance库
performance_schema:收集数据库服务器性能参数,得到数据库的运行统计信息,可以更好的利用这些信息分析问题。
mysal 5.5增加的,需要使用的时候需要设置参数performance_schema=on
mysgl5.6默认打开。
这个数据库的信息相当复杂,所以ORACLE公司官方把performanceschema简化到svs数据库,用于大家更简单的分析问题
2、表分类
1)setup 配置表,配置监控选项 2)当前enents表,记录当前的哪些线程正在做一些什么事情 3)各种事件的历史记录表。 4)各种事件的统计表 5)杂项表
3、常用的SQL语句
(1)哪个 SQL执行最多:
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest order by COUNT_STAR desc limit 1;
(2)哪个 SQL平均响应时间最多 AVG_TIMER_WAIT:
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest order by AVG_TIMER_WAIT desc limit 1;
(3)哪个 SQL扫描的行数最多:
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest order by SUM_ROWS_EXAMINED desc limit 1;
(4)哪个 SQL使用的临时表最多:
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest order by SUM_CREATED_TMP_DISK_TABLES desc limit 1; select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest order by SUM_CREATED_TMP_TABLES desc limit 1;
(5)哪个 SQL返回的结果集最多:
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest order by SUM_ROWS_SENT desc limit 1;
(6)哪个 SQL排序数最多:
select SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN from events_statements_summary_by_digest order by SUM_SORT_ROWS dec limit 1;
(7)哪个表、文件逻辑 IO最多(热数据):
select FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE from file_summary_by_instance order by SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE desc limit 2;
(8)哪个索引使用最多:
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 limit 1;
(9)哪个索引没有使用过:
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;
(10)哪个等待事件消耗的时间最多:
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 limit 1;
五、INFORMATION_SCHEMA
1、作用
提供数据库的元数据
比如数据库的数据名,表名,列信息,访问权限,索引,视图,存储过程,函数等信息
类似于Oracle的数据字典。
2、information_schema常用查询案例说明:
(1)获取 mysql所有表的 sql语句:
select * from information_schema.tables;
(2)获取表字段的 sql语句
select * from information_schema.columns;
(3)获取表主键值的 sql语句
select * from information_schema.key_column_usage where table_schema='itpux' and table_name='dept';
(4)获取表 check约束的 sql语句
select * from information_schema.table_constraints;
(5)获取表索引的 sql语句
select * from information_schema.statistics;
(6)MySQL查询某张表在哪个数据库里:
select * from tables where table_name='dept';
(7)查 deptno字段在哪个数据库的哪张表里:
select TABLE_SCHEMA,TABLE_NAME from columns where COLUMN_NAME='deptno';
(8)查询 MySQL中某个数据库中有多少张表::
select COUNT(*) tables, table_schema from information_schema.TABLES where table_schema = 'itpux' group by table_schema;
(9)查询 MySQL中某个数据库中某个表里有多少列:
select COUNT(*) from columns where TABLE_NAME='dept' and TABLE_SCHEMA='itpux';
(10)获取所有表结构(tables)
select * from information_schema.TABLES where TABLE_SCHEMA='itpux';
(11)获取表字段(columns)
select * from information_schema.COLUMNS where TABLE_SCHEMA='itpux' and TABLE_NAME='dept'
(12)获取表键值
select * from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA='itpux' and TABLE_NAME='dept'
(13)获取表 Check约束
select * from information_schema.TABLE_CONSTRAINTS where TABLE_SCHEMA='itpux' and TABLE_NAME='dept'
(14)获取表索引
select*from information_schema.STATISTICS where TABLE_SCHEMA='itpux'and TABLE_NAME='dept'
六、检查 MySQL服务器状态
1、查看数据库的版本
show variables like 'version';
2、列出所有的数据库
show databases;
3、查看服务器状态
-- 在命令行运行 status;
4、查看数据库存储引擎
show engines;
5、查看数据库存储引擎插件
show plugins;
6、查看数据库状态统计
show status;
7、查看数据库参数
show variables;
8、查看复制容灾
show master status; show slave status;
9、查看单机还是集群
show variables like '%cluster%';
10、查看触发器和存储过程
show triggers; show procedure status;
11、查看当前 mysql的线程
show processlist;
12、查看用户的授权命令
show grants for root;
13、更多
show databases; show table; show index;