description:mysql的一些常用sql, 有些是原创的, 有些是官网给的例子, 还有些是以前不知道哪找的
博客地址:
https://cloud.tencent.com/developer/user/1130242
https://www.modb.pro/topic/625137
github地址: https://github.com/ddcw
基础操作
启动
mysqld --defaults-file=/data/mysql_3308/conf/mysql_3308.cnf --user=mysql #--debug
停止
kill `pidof mysqld`
登录
mysql -h192.168.101.21 -P3308 -uroot -p123456 #走TCP/IP (也会被抽象为fd)
mysql -S /data/mysql_3308/run/mysql.sock -p123456 #走SOCK
用户操作和授权
-- 创建用户
create user 'u1'@'%' identified by '123456';
create user 'repl'@'%' identified WITH 'mysql_native_password' by 'repl'; -- 指定密码插件
-- 删除用户
drop user 'u1'@'%';
-- 修改密码
alter user 'u1'@'%' identified by '123456';
-- 修改用户名
rename user u3@'%' to 'new_u3'@'%';
-- 查询用户
select user,host from mysql.user;
select user();
select current_user();
-- 查看用户ddl
show create user 'u1'@'%';
-- 授权
grant replication client,replication slave on *.* to 'repl'@'%';
-- 回收权限
revoke replication client,replication slave on *.* from 'repl'@'%';
-- 查询用户的权限
show grants for 'u1'@'%';
-- 刷新权限
flush privileges;
角色 role
mysql可以使用角色管理权限, 需要设置 activate_all_roles_on_login = ON
仅8.0 才有role
-- 创建role
create role 'dba_role'@'%';
-- 给role授权
grant all on *.* to 'dba_role'@'%' WITH GRANT OPTION;
-- 把role分配给具体的用户
grant 'dba_role' to 'u2023'@'%';
grant 'dba_role' to 'u1'@'%';
-- 查看用户权限
show grants for 'u2023'@'%';
-- 查看role权限
show grants for 'dba_role'@'%';
-- 回收用户的role权限
revoke 'dba_role' from 'u1'@'%';
库操作
-- 建库
create database db1;
-- 删库
drop database db1;
-- 查看当前库
select database();
show databases; -- 查看所有库
-- 切换当前数据库
use db1;
-- 查看库DDL信息
show create database db1;
-- 跑路
-_-
表/视图操作
-- 建表
create table t1(
id int primary key auto_increment,
name varchar(50),
birthday date,
sex set('男','女'),
jdoc JSON,
b binary(4)
) engine=InnoDB
-- 删表
drop table t1;
drop table if exists t1; -- 不报错, mysql,pg才支持. oracle不支持
-- 查看建表DDL
show create table t1;
-- 查看表结构信息
desc t1;
-- 修改表结构
alter table t1 add column new_col varchar(20);
-- 在线修改表结构, onlineDDL
alter table t1 add column new_col_2 varchar(10), ALGORITHM=INPLACE, LOCK=NONE; -- 修改表结构不支持inplace算法
-- 重名名表
alter table t1 rename to t1_2;
-- 查询表的索引
show index from db1.t1;
-- 加索引
alter table db1.t1_2 add index idx_2(name);
-- 删除索引
drop index idx_2 on db1.t1_2;
-- 清空表数据
truncate t1_2;
-- 创建视图
create view test_v1 as select * from db1.t1_2;
-- 删除视图
drop view db1.test_v1;
行操作
-- 查询
select * from t1_2;
-- 删除
delete from t1_2 where id = 2;
delete from t1_2 limit 1;
-- 插入
insert into t1_2(id,name) values(1,'test');
-- 更新
update t1_2 set name='newtest' where id=1;
事务
-- begin
begin;
insert into t1_2(id,name) values(2,'test');
commit;
-- start transaction
start transaction;
insert into t1_2(id,name) values(3,'test');
commit;
-- savepoint
begin;
update t1_2 set name='test savepoint' where id=3;
savepoint p1;
update t1_2 set name='after p1' where id=3;
rollback to p1;
commit;
select name from t1_2 where id=3;
-- xa
xa start 'testxa';
insert into t1_2(id,name) values(5,'testXA');
xa end 'testxa';
xa prepare 'testxa';
xa commit 'testxa';
常用查询
查看innodb缓存命中率
select HIT_RATE from INNODB_BUFFER_POOL_STATS;
查询非innodb表
select table_name,table_schema,engine
from information_schema.tables
where engine != 'innodb'
and table_schema not in('mysql', 'sys', 'information_schema', 'performance_schema');
查询无主键的表
SELECT
aa.TABLE_SCHEMA, aa.TABLE_NAME
FROM
(SELECT
TABLE_SCHEMA, TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA NOT IN ('sys' , 'information_schema', 'mysql', 'performance_schema')) AS aa
LEFT JOIN
(SELECT
TABLE_SCHEMA, TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'PRIMARY KEY') AS bb ON aa.TABLE_SCHEMA = bb.TABLE_SCHEMA
AND aa.TABLE_NAME = bb.TABLE_NAME
WHERE
bb.TABLE_NAME IS NULL;
查询自增键使用率
-- 查询自增键使用率
select table_schema,table_name,column_name,auto_increment_ratio from sys.schema_auto_increment_columns;
-- 查询使用率达到95%的
select table_schema,table_name,column_name,auto_increment_ratio from sys.schema_auto_increment_columns where auto_increment_ratio>=0.95;
查询大表
-- 查询大于32GB的表, 行数超过10,000,0000的表
select TABLE_SCHEMA,TABLE_NAME,ENGINE,DATA_LENGTH,TABLE_ROWS from information_schema.tables where TABLE_ROWS > 10000000 and DATA_LENGTH > 32212254720;
查询冷表
-- 查询超过90天未使用的表
select TABLE_SCHEMA,TABLE_NAME,ENGINE,UPDATE_TIME from information_schema.tables where UPDATE_TIME < DATE_SUB(current_timestamp(), INTERVAL 90 DAY) and TABLE_SCHEMA not in ('sys','information_schema','mysql','performance_schema');
查询碎片表
-- 查询碎片率超过40%的表. 碎片率计算方式可能不一样, DATA_FREE/(DATA_LENGTH+DATA_FREE)
select * from (select TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH,DATA_FREE,round(DATA_FREE/(DATA_LENGTH+DATA_FREE)*100,2) as fragment_rate from information_schema.tables where DATA_LENGTH>0 and TABLE_SCHEMA not in ('sys','information_schema','mysql','performance_schema')) as aa where aa.fragment_rate > 40;
查询冗余索引和未使用的索引
-- 冗余索引
select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,dominant_index_columns,sql_drop_index from sys.schema_redundant_indexes;
-- 低版本的冗余索引可能没得相关的系统试图
SELECT a.table_schema AS '数据库', a.table_name AS '表名', a.index_name AS '索引1', b.index_name AS '索引2', a.column_name AS '重复列名'
FROM information_schema.statistics a
JOIN information_schema.statistics b ON a.table_schema = b.table_schema
AND a.table_name = b.table_name
AND a.seq_in_index = b.seq_in_index
AND a.column_name = b.column_name
WHERE a.seq_in_index = 1
AND a.index_name != b.index_name;
-- 未使用的索引(仅这次启动后)
select * from sys.schema_unused_indexes;
查询锁
-- 等待元数据锁的
select * from sys.schema_table_lock_waits;
-- 正在等待innodb锁的事务
select * from information_schema.innodb_lock_waits;
或者
select * from sys.innodb_lock_waits\G
-- innodb锁
select * from information_schema.innodb_locks;
查询内存使用
-- 各个库内存使用情况
select * from sys.innodb_buffer_stats_by_schema;
-- 各存储引擎/event使用内存的情况
SELECT SUBSTRING_INDEX(event_name,'/',2) AS
code_area, sys.format_bytes(SUM(current_alloc))
AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;
-- 使用内存最多的前20张表
select * from sys.innodb_buffer_stats_by_table limit 20;
-- 使用内存前10的event
select event_name, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_global_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;
-- 使用内存前10的线程
select thread_id, event_name, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_thread_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;
-- 使用内存前10的账号
select USER, HOST, EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_account_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;
-- 使用内存前10的主机
select HOST, EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_host_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;
-- 使用内存前10的用户名
select USER, EVENT_NAME, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_user_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 10;
-- 用户内存使用(当前内存,累计分配内存,内存是经常分配和回收的, 索引累计分配内存会很大, 可以反应出某个用户的业务量)
select * from sys.user_summary;
查询IO使用
-- 主机IO使用
select * from sys.host_summary_by_file_io;
-- 主机IO使用详情
select * from sys.host_summary_by_file_io_type;
-- 用户IO使用情况
select * from sys.user_summary_by_file_io;
-- 用户IO使用详情
select * from sys.user_summary_by_file_io_type;
-- 各线程IO使用
select * from sys.io_by_thread_by_latency;
-- 全局IO等待排行
select * from sys.io_global_by_wait_by_latency;
-- 文件的IO等待排行
select * from sys.io_global_by_file_by_latency;
查询等待/延迟
-- 查询等待类
select * from sys.wait_classes_global_by_avg_latency;
-- 各用户各事件等待
select * from sys.waits_by_user_by_latency;
-- 用户等待汇总
select * from sys.user_summary_by_statement_latency;
-- 用户各类SQL执行情况
select * from sys.user_summary_by_statement_type;
查询使用临时表/文件的sql
-- 使用临时表的sql
select * from sys.statements_with_temp_tables;
查询使用sort的sql
select * from sys.statements_with_sorting;
查询数据库/表大小
-- 查询所有库大小之和
select round(sum(data_length)/1024/1024,2) as total_table_MB from information_schema.tables where TABLE_SCHEMA not in ('test','sys','mysql','information_schema','performance_schema');
-- 查询最大的10张表
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('sys','information_schema','mysql','performance_schema') ORDER BY DATA_LENGTH DESC LIMIT 10;
-- 各数据库大小
SELECT
aa.table_schema,
aa.DATA_SIZE_MB,
aa.INDEX_SIZE_MB,
aa.DATA_SIZE_MB + aa.INDEX_SIZE_MB AS TOTAL_SIZE
FROM
(SELECT
table_schema,
ROUND(SUM(data_length / 1024 / 1024), 2) AS DATA_SIZE_MB,
ROUND(SUM(index_length / 1024 / 1024), 2) AS INDEX_SIZE_MB
FROM
information_schema.tables
WHERE
TABLE_SCHEMA NOT IN ('sys' , 'mysql', 'information_schema', 'performance_schema')
GROUP BY table_schema) AS aa
ORDER BY 4 DESC;
查询使用SSL连接的
SELECT
sbt.variable_value AS tls_version,
t2.variable_value AS cipher,
processlist_user AS user,
processlist_host AS host
FROM
performance_schema.status_by_thread AS sbt
JOIN
performance_schema.threads AS t ON t.thread_id = sbt.thread_id
JOIN
performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id
WHERE
sbt.variable_name = 'Ssl_version'
AND t2.variable_name = 'Ssl_cipher'
ORDER BY tls_version;
一键巡检
mysql -h127.0.0.1 -P3308 -p -H -e "CALL sys.diagnostics(1, 1, 'current')" > /tmp/t20220811.html
查询统计信息
-- 查询db2.t1的统计信息
select * from mysql.innodb_table_stats where database_name='db2' and table_name='t1';
-- 收集db2.t1的统计信息 (myisam使用: myisamchk --analyze)
analyze table db2.t1; -- 会锁表
查询表内存使用
-- 不建议在生产上使用
-- 比如查询db1.sbtest1表 内存使用 (主键索引是含本行所有字段数据的)
SELECT INDEX_NAME, COUNT(*) AS Pages,
ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
AS 'Total Data (MB)'
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME = '`db1`.`sbtest1`'
GROUP BY INDEX_NAME;
查询redo刷盘量
select * from sys.io_global_by_wait_by_latency where event_name like '%innodb_log%'\G
查询MDL锁
SELECT
locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_ID AS granted_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
"Metadata Lock" AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = 'PENDING'
AND b.lock_status = 'GRANTED'
AND a.OWNER_THREAD_ID b.OWNER_THREAD_ID
AND a.lock_type = 'EXCLUSIVE'
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT
thread_id,
group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE
t1.granted_thread_id = t2.thread_id \G
查询执行时间/次数最多的SQL
select * from sys.statement_analysis limit 10\G
函数
函数必须有返回, 存储过程则无所谓
-- 查看某库下的函数
select * from mysql.proc where db='db1' and type='FUNCTION';
show function status where db='db1';
-- 查看函数DDL
show create function hello;
-- 创建函数
delimiter //
CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!')//
delimiter ;
-- 调用函数
select hello('aaa');
-- 删除函数
drop function hello;
drop function if exists hello;
存储过程
-- 查询某库下的存储过程
select * from mysql.proc where db='t1' and type='PROCEDURE';
show procedure status where db='db1';
-- 查看存储过程DDL
show create PROCEDURE test20230205;
-- 创建存储过程
delimiter //
create procedure pro_insert_Nrows( IN rows int)
begin
declare n int;
set n=1;
set autocommit=off;
while n
