常用SQL以及命令

https://www.modb.pro/db/1792723679335485440

常用SQL

自动不补全库和表名

连按两次tap键

查看某个数据库大小

mysql> select table_schema as '数据库',table_name as '表名',table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as ' 索引容量(MB)' from information_schema.tables  where table_name='kg_items';

-- 查看某个表对应的数据库
select table_schema from information_schema.tables where table_name='xxx';

查询数据库占用内存前30

select table_schema as '数据库',table_name as '表名',table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as ' 索引容量(MB)' from information_schema.tables  order by data_length desc, index_length desc limit 30;

查看平均响应时间top10的SQL

SELECT QUERY_SAMPLE_TEXT,AVG_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 10\G

查看执行次数top10的SQL

SELECT QUERY_SAMPLE_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10\G

分析二进制文件binlog

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-rows /data/dba/yanhao/application/mysql/dataMysql57/binlog.000001

添加dba用户，并授权

CREATE USER 'admin_dba'@'%' IDENTIFIED BY 'ujXRjO9W6z7flBCd';

GRANT  SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'admin_dba'@'%' WITH GRANT OPTION;

flush privileges;

查看存在碎片的表

select concat('optimize table ',table_schema,'.',table_name,';'),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY';

查看指定表的碎片情况

show table status like 'xxx' \G

select concat('optimize table ',table_schema,'.',table_name,';'),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY';


select concat('optimize table ',table_schema,'.',table_name,';'),data_free/1024/1024 as "data_free(MB)",engine from information_schema.tables where table_name = 'Mail_193' and engine !='MEMORY';

优化表空间

optimize table xxx;
# OPTIMIZE TABLE语句可以重新组织表、索引的物理存储，减少存储空间，提高访问的I/O效率。类似于碎片整理功能。
# 需要有足够的空间才能进行OPTIMIZE TABLE。 (剩余空间必须 > 被 OPTIMIZE 的表的大小)

#OPTIMIZE 只对独立表空间(innodb_file_per_table=1)才有用，对共享表空间不起作用。

#对于共享表空间，如果需要瘦身： 必须将数据导出，删除ibdata1，然后将 innodb_file_per_table 设置为独立表空间， 然后将数据导入进来。

找到碎片化最严重的表

SELECT table_schema, TABLE_NAME, concat(data_free/1024/1024, 'M') as data_free
FROM `information_schema`.tables
WHERE data_free > 3 * 1024 * 1024
        AND ENGINE = 'innodb'
ORDER BY data_free DESC

查看空闲空间

select
    round(sum(data_length + index_length) / 1024 /1024, 2) "Total Size (MB)",
    round(sum(data_free) /1024 /1024, 2) "Free Space (MB)"
from
    information_schema.tables;
    
-- 查看当前所有表占用的空间和空闲空间

查询表的所属数据库

select table_schema as '数据库',table_name as '表名',table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as ' 索引容量(MB)' from information_schema.tables where table_name like '%user_privacy%' order by d
ata_length desc, index_length desc limit 30;

查询使用了临时表的SQL

select thread_id,sql_text, rows_sent,rows_examined,created_tmp_tables,no_index_used,no_good_index_used from performance_schema.events_statements_history where created_tmp_tables>0 or created_tmp_disk_tables > 0;

查询没有使用索引或没有好索引的SQL语句

select thread_id,sql_text, rows_sent,rows_examined,created_tmp_tables,no_index_used,no_good_index_used from performance_schema.events_statements_history where no_index_used>0 or no_good_index_used > 0;

查询返回行数过多的SQL语句

select sql_text, rows_sent,rows_examined from performance_schema.events_statements_history where rows_examined>rows_sent \G

查找未使用过的索引

select * from sys.schema_unused_indexes;

explain analyze和explain

-- explain展示查询优化器对该查询计划估计的代价，但是不执行该查询。
-- explain analyze不仅会显示查询计划，还会实际运行语句，但是其会丢掉任何来自select语句的输出。

--如果想在DML语句上使用explain analyze但不影响数据，则可以明确把explain analyze用在一个事务中。
begin; 
explain analyze ...
rollback;

-- explain analyze结果解析
cost:优化器评估的成本
rows:第一个rows表示估计访问的行数，第二个rows表示真正执行的行数。
actual time:前面的是获取第一行所耗费的时间，第二个是获取所有记录花费的时间，如果循环多次，则该值就是一次循环对应的平均时间。

查看连接IP

-- sql
select user, host from information_schema.processlist where User !='admin_dba' and User !='rdsadmin' and host!='localhost';

过滤IP去重

## 过滤ip去重
dbalogin xxxx -e "show processlist;" | grep [0-9] | awk '{print $3}' | awk -F ":" '{print $1}' | sort | uniq -c|grep -vi "localhost"

检查是否还有写入

show master status;

查看当前登录用户

select user();

查看数据库用户列表

select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user order by query;

查看某用户的权限

show grants for 'username'@'%'; 

MySQL常用变量

• 慢日志时长：long_query_time

• 慢日志状态：slow_query_log

• 慢日志位置：slow_query_log_file

MySQL常用命令

• show processlist

https://www.cnblogs.com/libaiyun/p/16462461.html

• 查看死锁

show engine innodb status;
select * from sys.innodb_lock_waits;

• pager

类似于管道符，可以过滤输出的信息。

• 查看扩展索引是否开启

 show variables like 'optimizer_switch' \G
 
 -- use_index_extensions=on

Linux

创建软连接

ln -s [target_file_or_directory] [link_name]