MySQL默认数据库及字符集学习
MySQL数据库安装完成以后有几个默认数据库，下面来介绍其作用
一、 mysql数据库
MySQL的核心数据库，主要负责数据库用户的存储权限，自己使用的控制信息和管理权限。
1.1 成本模型表
engine_cost IO
server_cost CPU
1.2 权限相关的表
user 用户级别权限
procs_priv 存储与函数过程级别的权限
proxies_priv 代理用户权限表
columns_priv 列级别权限
db 数据库级别权限
tables_priv 表级别的权限
二 、sys数据库
sys数据库的内容来自performance_scheam,主要简化DBA的工作，快速了解DB的运行情况。
字母开头的表，适合阅读，是格式化后的数据；
X$开头的是原始数据；
2.1 sys数据库能做什么：
1.谁使用了最多的资源；
2、大部分连接来自哪里，及发送的SQL情况；
3、机器执行最多的SQL语句；
4、哪个文件产生最多的IO，
5、哪个表的IO最多；
6、那张表被访问最多；
7、那些语句延迟比较严重；
8、那些语句使用了磁盘临时表；
9、那张表占用了最多的buffer pool；
10、每个库占用了多少buffer pool；
11、每个连接分配多少内存；
12、MySQL内部有多少个线程在运行。
2.2 sys数据库常用的SQL语句：
谁使用了最多的资源: select * from host_summery limit 1;
select * from io_global_by_file_by_bytes limit 1;
查看当前连接：
mysql> select host,current_connections from host_summary;
每个库占用多少buffer_pool
mysql> select * from innodb_buffer_stats_by_schema; +---------------+------------+------------+-------+--------------+-----------+-------------+ | object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+------------+------------+-------+--------------+-----------+-------------+ | mysql | 704.00 KiB | 276.61 KiB | 44 | 0 | 0 | 1676 | | InnoDB System | 192.00 KiB | 17.88 KiB | 12 | 0 | 0 | 51 | | sys | 16.00 KiB | 338 bytes | 1 | 0 | 0 | 6 | | tes1 | 16.00 KiB | 0 bytes | 1 | 0 | 0 | 0 | +---------------+------------+------------+-------+--------------+-----------+-------------+ 4 rows in set (0.11 sec)
三、performance_shcema
收集数据库服务器的性能参数，得到数据库的运行统计信息，利用信息分析问题。
--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;
四、information_schema
提供数据库的元数据，比如数据库的数据名，表名，列信息，访问权限，索引，视图，存储过程，类似oracle的数据字典表。
查询表信息：select * from tables;
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 : 视图的信息，也是系统的和用户的基本视图信息
五、其他常用语句
查看版本：show variables like 'version'; 查数据库：show databases;
查看状态：
mysql> status; -------------- mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper Connection id: 3 Current database: information_schema Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.20-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 35 min 10 sec Threads: 4 Questions: 430 Slow queries: 0 Opens: 810 Flush tables: 1 Open tables: 353 Queries per second avg: 0.203 --------------
查看引擎：
show engines;
查看innodb引擎状态：
show engine innodb status;
查看线程：
show processlist;
查看授权：
show grants for root;
六、字符集
字符集：是一套字符与字符编码的集合，用于显示一些抽象的符号。
校验规则：字符集的排序规则。
utf8mb4 是utf8的超集，utf8是三个字节一个中文，utf8mb4是四个字节一个中文。
6.1、查看字符集
查看当前支持的字符集及校验规则： mysql> show character set;
utf8_general_ci ci不区分大小写， cs区分大小写，bin 二进制排序
查看字符集的当前规则：mysql> show collation;
查看当前数据库的字符集：
mysql> show variables like 'character%'; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /mysql/app/mysql/share/charsets/ | +--------------------------+----------------------------------+ 8 rows in set (0.01 sec)
查看当前数据库的校对规则：
mysql> show variables like 'collation%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec)
6.2 字符集设置
6.2.1 服务器级别：
1）编译时候设置；
2）配置文件设置；
[mysqld]
character_set_server=utf8
3）环境变量设置；
export LANG=en_US.utf8
6.2.2 数据库级别：
1）创建数据库
create database jl charset=utf8;
6.2.3表级别，列级别
create table zg(id int) default charset=utf8;
create table zg1(id int,c1 varchar(10) charset utf8);
create table zg2(id int,c1 varchar(10) charset utf8 collate utf8_general_ci);
6.2.4 连接级别
在连接数据库的时候指定。
6.3 修改表的字符集
mysql> alter table zg2 default charset utf8;
查看校对规则：
select * from information_schema.columns where table_name='zg1';
