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 charset=utf8;

-- 删表
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 <= rows
do
insert into t1 (id,id1,name) values(n,n,md5(n));
set n=n+1;
end while;  
commit;
end//
delimiter ;

-- 调用存储过程
call pro_insert_Nrows(100);

-- 删除存储过程
drop procedure pro_insert_Nrows;
drop procedure if exists pro_insert_Nrows;
复制

触发器

触发器一般是用来自动更新相关表的.

-- 查询触发器
show triggers;
select * from information_schema.triggers where TRIGGER_SCHEMA='db1';

-- 查看触发器DDL
show create trigger tg_1;

-- 创建触发器
delimiter $$
create trigger tg_1 after insert on t1 for each row
begin
insert into t2(id,id1,name) values(new.id,new.id1,new.name);
end$$
delimiter ;

-- 测试触发
insert into t1(id) values(9999)
select * from t2 where id=9999;

-- 触发器是自动触发的, 不能手动调用
-- 但是可以通过设置变量来决定是否启用触发器 (其实就是触发器里面判断变量, 实际上还是触发了...)

-- 删除触发器
drop trigger tg_1;
drop trigger if exists tg_1;
复制

event

相当于oracle的job, linux的crontab 就是定时任务

默认是关闭的, 需要手动开启event. 变量:event_scheduler=ON (可动态设置)

开启后, 会有个event_scheduler用户连上来…

-- 查询event
show events;
select * from information_schema.events where EVENT_SCHEMA='db1';

-- 查看event的DDL
show create event db1.event_account_user_by_ddcw;

-- 创建event
-- 在event创建1小时后, 给db1.t1字段值加1
CREATE EVENT e_t1
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE db1.t1 SET id = id + 1;
      
-- 删除event

-- 查看event执行情况
select event_name,last_executed from information_schema.events where event_name='e_t1';

-- 删除
drop event e_t1;
drop event if exists e_t1;
复制

分区表

基础操作

-- 查看分区情况
SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
 FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME LIKE 'k1%';
 
-- 删除某个分区
alter table employees drop partition p0;

-- 添加分区
alter table employees add partition (partition p4 VALUES LESS THAN (41));

-- 合并分区
-- 分解分区
复制

RANGE

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
复制

LIST

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);
复制

KEY

相当于hash, 只不过使用的是mysql自带的hash

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
复制

HASH

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;
复制

复合分区

分区下又分区, 就是子分区…

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );
复制

临时表

GTID不允许临时表

Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions

create temporary table t2023(id int);
复制

join

-- inner join = join = cross join   #仅限mysql语法

-- left join
select * from db1.t1 as aa left join db1.t2 as bb on aa.id=bb.id;

-- inner join
select * from db1.t1 as aa inner join db1.t2 as bb on aa.id=bb.id;
select * from db1.t1 as aa join db1.t2 as bb on aa.id=bb.id;

-- rigth join
select * from db1.t1 as aa right join db1.t2 as bb on aa.id=bb.id;
复制

prepare statement

PREPARE stmt_1 FROM 'select * from t1 where id=?';
set @id_1 = 1;
EXECUTE stmt_1 USING @id_1;

DEALLOCATE PREPARE stmt_1; -- 释放
DROP PREPARE stmt_1 ; -- 删除
复制

#python或者其它变成语言则是使用 %s 或者:1
cursor.execute("update db1.t1 set name=%s where id=%s",('aa',1))  -- mysql/postgres
cursor.execute("update db1.t1 set name=:1 where id=:2",('aa',1)) -- oracle
复制

查看mysql是否升级过

就是统计版本信息 出现了几次. 5.7/8.0 都可以使用

grep 'Version: ' /data/mysql_3308/mysqllog/dblogs/mysql3308.err | awk -F 'Version:' '{print $2}' | awk -F "'" '{print $2}' | sed '/^$/d' | uniq -c

复制

15:53:30 [root@ddcw21 ~]#grep 'Version: ' /data/mysql_3308/mysqllog/dblogs/mysql3308.err | awk -F 'Version:' '{print $2}' | awk -F "'" '{print $2}' | sed '/^$/d' | uniq -c
    112 5.7.38-log
15:53:31 [root@ddcw21 ~]#

复制