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;
