【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;输出的就是表的ddl8.所有表详细信息
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;