【MySQL】常用SQL--持续更新ing

一、配置信息类

        1.查看版本

select @@version;
或
select version();

        2.查看配置 

show global variables where variable_name in ('basedir','binlog_format','datadir','expire_logs_days','innodb_buffer_pool_size','innodb_log_buffer_size','innodb_log_file_size','innodb_log_files_in_group','log_bin','log_bin_basename','log_bin_trust_function_creators','log_error','log_output','long_query_time','max_connections','port','slow_query_log','slow_query_log_file','socket','character_set_server','collation_server','default_storage_engine','pid_file','log_error','user','server_id','skip_name_resolve','event_scheduler','max_allowed_packet','lower_case_table_names','performance_schema_max_sql_text_length','open_files_limit','log_timestamps','sql_mode','general_log','general_log_file','explicit_defaults_for_timestamp');

变量名	描述
basedir	MySQL安装目录的位置。
binlog_format	二进制日志记录格式，可以是STATEMENT, ROW或MIXED。
datadir	存储数据库文件（如表和索引）的目录。
expire_logs_days	设置二进制日志文件自动过期并被删除的天数。
innodb_buffer_pool_size	InnoDB存储引擎用于缓存表和索引数据的内存大小。
innodb_log_buffer_size	InnoDB存储引擎用于缓冲未写入磁盘的日志信息的内存大小。
innodb_log_file_size	每个InnoDB重做日志文件的大小。
innodb_log_files_in_group	InnoDB重做日志文件组中的文件数量。
log_bin	开启或关闭二进制日志记录功能。
log_bin_basename	二进制日志文件的基本文件名。
log_bin_trust_function_creators	允许不具有SUPER权限的用户创建安全函数。
log_error	错误日志文件的位置。
log_output	指定日志输出的目标（例如，TABLE, FILE等）。
long_query_time	定义慢查询的时间阈值（以秒为单位）。
max_connections	MySQL服务器允许的最大并发连接数。
port	MySQL监听客户端连接的TCP/IP端口。
slow_query_log	开启或关闭慢查询日志记录功能。
slow_query_log_file	慢查询日志文件的位置。
socket	本地Unix套接字文件路径。
character_set_server	服务器使用的默认字符集。
collation_server	服务器使用的默认排序规则。
default_storage_engine	创建新表时的默认存储引擎。
pid_file	进程ID文件的位置。
user	MySQL作为该用户身份运行（仅限UNIX）。
server_id	在复制环境中唯一标识服务器的数字。
skip_name_resolve	如果设置，则禁止解析主机名到IP地址。
event_scheduler	启用或禁用事件调度器。
max_allowed_packet	MySQL接收的最大单个数据包/行大小。
lower_case_table_names	控制表名是否区分大小写。
performance_schema_max_sql_text_length	performance schema 中 SQL 文本的最大长度。
open_files_limit	打开文件的最大数目。
log_timestamps	日志条目的时间戳格式。
sql_mode	设置SQL操作模式，影响数据库行为。
general_log	开启或关闭普通查询日志记录功能。
general_log_file	普通查询日志文件的位置。
explicit_defaults_for_timestamp	对于TIMESTAMP类型，默认使用显式指定的时间戳。

 

二、基础信息类

        1. 用户信息

select * from mysql.user;

        2.查看用户权限

查看root用户有哪些权限
show grants for root@'%';

        3.密码策略

SHOW VARIABLES LIKE 'validate_password%';

变量名	描述
validate_password_length	密码所需的最小长度。
validate_password_mixed_case_count	密码中包含的大写字母的最小数量。
validate_password_number_count	密码中包含的数字的最小数量。
validate_password_policy	密码策略等级，0/LOW、1/MEDIUM、2/STRONG。默认是1。
validate_password_special_char_count	密码中包含的特殊字符的最小数量。（注：这不是标准变量名，实际应为 validate_password_special_character_count）

        4.库信息

show databases;

        5. 表信息

需要连接具体的库
show tables;

        6.表描述

desc table_name;例如查看t1的表结构描述：
desc t1;

        7. 表定义

show create table table_name;输出的就是表的ddl

        8.所有表详细信息

select * from information_schema.tables;

        9. 索引信息

show index from table_name;

        10.统计信息

select * from information_schema.statistics;

        11. 视图

select * from information_schema.views;

        12.分区表

select * from information_schema.partitions;

        13.存储过程

select * from information_schema.routines;

        14. 触发器

select * from information_schema.triggers;

        15.事件

select * from information_schema.events;

三、运行情况类 

        1. 连接情况

show full processlist;
或
select * from information_schema.processlist;

        2. 事务情况

select * from information_schema.innodb_trx;

        3.锁情况

                3.1 被锁表

show open tables where in_use > 0;

                3.2 innodb行锁

select * from information_schema.innodb_locks;

                3.3  锁等待

select * from information_schema.innodb_lock_waits;

                3.4 阻塞

SELECTp2.`HOST` 被阻塞方host,p2.`USER` 被阻塞方用户,r.trx_id 被阻塞方事务id,r.trx_mysql_thread_id 被阻塞方线程号,TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) 等待时间,r.trx_query 被阻塞的查询,l.lock_table 阻塞方锁住的表,m.`lock_mode` 被阻塞方的锁模式,m.`lock_type` '被阻塞方的锁类型(表锁还是行锁)',m.`lock_index` 被阻塞方锁住的索引,m.`lock_space` 被阻塞方锁对象的space_id,m.lock_page 被阻塞方事务锁定页的数量,m.lock_rec 被阻塞方事务锁定行的数量,m.lock_data 被阻塞方事务锁定记录的主键值,p.`HOST` 阻塞方主机,p.`USER` 阻塞方用户,b.trx_id 阻塞方事务id,b.trx_mysql_thread_id 阻塞方线程号,b.trx_query 阻塞方查询,l.`lock_mode` 阻塞方的锁模式,l.`lock_type` '阻塞方的锁类型(表锁还是行锁)',l.`lock_index` 阻塞方锁住的索引,l.`lock_space` 阻塞方锁对象的space_id,l.lock_page 阻塞方事务锁定页的数量,l.lock_rec 阻塞方事务锁定行的数量,l.lock_data 阻塞方事务锁定记录的主键值,IF(p.COMMAND = 'Sleep',CONCAT(p.TIME, ' 秒'),0) 阻塞方事务空闲的时间
FROMinformation_schema.INNODB_LOCK_WAITS wINNER JOINinformation_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_idINNER JOINinformation_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_idINNER JOINinformation_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_idAND l.`lock_trx_id` = b.`trx_id`INNER JOINinformation_schema.INNODB_LOCKS m ON m.`lock_id` = w.`requested_lock_id`AND m.`lock_trx_id` = r.`trx_id`INNER JOINinformation_schema.PROCESSLIST p ON p.ID = b.trx_mysql_thread_idINNER JOINinformation_schema.PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
ORDER BY 等待时间 DESC;

              4.索引情况        

                   4.1 冗余索引

SELECTobject_schema,count(*)
FROMPERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage
WHEREcount_star = 0
AND sum_timer_wait = 0
GROUP BY object_schema
order by count(*) desc;

               4.2 查看性能较差的索引 TOP10

SELECTobject_schema,object_name,index_name,max_timer_wait
FROMPERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage
ORDER BYmax_timer_wait DESC
LIMIT 10;

                4.3 未使用的索引

SELECTobject_schema,count(*)
FROMPERFORMANCE_SCHEMA .table_io_waits_summary_by_index_usage
WHEREindex_name IS NULL
GROUP BYobject_schema
ORDER BYcount(*) DESC;

四、数据情况类

        1. 实例数据大小

SELECT'all_database' AS '数据库',SUM(table_rows) AS '记录数',SUM(TRUNCATE (data_length / 1024 / 1024 / 1024, 2) + TRUNCATE (index_length / 1024 / 1024 / 1024, 2)) AS '数据容量(GB)'
FROMinformation_schema.tables
ORDER BYdata_length DESC,index_length DESC limit 10;

        2.库数据大小

SELECTtable_schema AS '数据库',sum(table_rows) AS '记录数',sum(TRUNCATE (data_length / 1024 / 1024 / 1024, 2) + TRUNCATE (index_length / 1024 / 1024 / 1024, 2)) AS '数据容量(GB)'
FROMinformation_schema.tables
-- WHERE table_schema = 'stdsj_dw'  -- 指定数据库名
GROUP BY table_schema
ORDER BYdata_length DESC,index_length DESC limit 10;

        3.表数据大小

SELECTtable_schema AS '数据库',table_name AS '表名',table_rows AS '记录数',TRUNCATE (data_length / 1024 / 1024 / 1024, 2) AS '数据容量(GB)',TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
FROMinformation_schema.tables
-- WHERE table_schema = 'stdsj_dw'  -- 指定数据库名
ORDER BYtable_rows DESC limit 10;