MySQL数据库自带系统数据库功能超详细介绍

select user, host from mysql.user;

-- 授予用户对test数据库的select权限
grant select on test.* to 'user'@'localhost';

-- 授予用户对所有数据库的select权限
grant select on *.* to 'user'@'localhost';

-- 授予用户对特定表的权限
grant select, insert on test.users to 'user'@'localhost';

-- 授予用户对特定列的权限
grant select (name, email) on test.users to 'user'@'localhost';

-- 查看用户权限
show grants for 'user'@'localhost';

-- 查看用户权限的详细信息
select * from mysql.user where user = 'user' and host = 'localhost';

-- 修改用户密码
alter user 'user'@'localhost' identified by 'new_password';

-- 重置密码
set password for 'user'@'localhost' = password('new_password');

-- 刷新权限
flush privileges;

-- 删除用户权限
revoke select on test.* from 'user'@'localhost';

-- 删除用户
drop user 'user'@'localhost';

-- 停止mysql服务
sudo systemctl stop mysql

-- 以跳过权限检查的方式启动
sudo mysqld_safe --skip-grant-tables &

-- 登录mysql
mysql -u root

-- 重置密码
use mysql;
update user set authentication_string = password('new_password') where user = 'root';

-- 重启mysql服务
sudo systemctl restart mysql

select schema_name as `database`
from information_schema.schemata;

select table_name as `table`
from information_schema.tables
where table_schema = 'your_database_name';

select 
    column_name as `column`,
    data_type as `type`,
    is_nullable as `nullable`,
    column_key as `key`,
    column_default as `default`,
    extra as `extra`
from information_schema.columns
where table_schema = 'your_database_name'
and table_name = 'your_table_name';

select 
    index_name as `index`,
    column_name as `column`,
    seq_in_index as `seq`,
    non_unique as `nonunique`
from information_schema.statistics
where table_schema = 'your_database_name'
and table_name = 'your_table_name';

select 
    constraint_name as `constraint`,
    referenced_table_name as `referenced table`,
    referenced_column_name as `referenced column`
from information_schema.key_column_usage
where table_schema = 'your_database_name'
and table_name = 'your_table_name'
and referenced_table_name is not null;

select 
    table_name as `view`,
    view_definition as `definition`
from information_schema.views
where table_schema = 'your_database_name';

-- 比较两个数据库的表结构差异
select 
    table_name,
    column_name,
    data_type as source_type,
    (select data_type 
     from information_schema.columns 
     where table_schema = 'target_db' 
     and table_name = source.table_name 
     and column_name = source.column_name) as target_type
from information_schema.columns source
where table_schema = 'source_db'
and (select data_type 
     from information_schema.columns 
     where table_schema = 'target_db' 
     and table_name = source.table_name 
     and column_name = source.column_name) is null
or (select data_type 
    from information_schema.columns 
    where table_schema = 'target_db' 
    and table_name = source.table_name 
    and column_name = source.column_name) <> source.data_type;

show variables like 'performance_schema';
-- 默认值为 on（mysql 5.7+）

-- 开启所有事件的监控（谨慎使用，可能影响性能）
update performance_schema.setup_consumers set enabled = 'yes';

-- 按需开启特定监控（推荐）
update performance_schema.setup_consumers set enabled = 'yes' 
where name like 'events_statements%'; -- 开启sql语句监控

-- 监控所有用户的sql语句
update performance_schema.setup_actors set enabled = 'yes', history = 'yes' 
where user = '%';

select 
    digest_text as 'sql语句',
    count_star as '执行次数',
    sum_timer_wait / 1e9 as '总耗时(秒)',
    avg_timer_wait / 1e9 as '平均耗时(秒)'
from performance_schema.events_statements_summary_by_digest
order by sum_timer_wait desc
limit 10;

select 
    event_id,
    sql_text as 'sql语句',
    timer_wait / 1e9 as '耗时(秒)',
    lock_time / 1e9 as '锁等待时间(秒)',
    rows_examined as '扫描行数',
    rows_sent as '返回行数'
from performance_schema.events_statements_history
order by timer_wait desc
limit 10;

select 
    event_name as '事件类型',
    count_star as '次数',
    sum_timer_wait / 1e9 as '总等待时间(秒)',
    avg_timer_wait / 1e9 as '平均等待时间(秒)'
from performance_schema.file_summary_by_event_name
where event_name like '%read%'
order by sum_timer_wait desc;

select 
    thread_id,
    event_id,
    sql_text,
    timer_start,
    timer_wait / 1e9 as '已运行时间(秒)'
from performance_schema.events_transactions_current
where state = 'active'
order by timer_wait desc;

-- 查看执行时间最长的10个查询
select * from sys.statement_analysis 
order by total_latency desc 
limit 10;

-- 查看操作最频繁的表
select * from sys.schema_table_statistics 
order by rows_fetched + rows_inserted + rows_updated + rows_deleted desc 
limit 10;

-- 查看当前锁等待链
select * from sys.innodb_lock_waits;

-- 按主机统计文件io延迟
select * from sys.host_summary_by_file_io 
order by io_latency desc;

select 
    digest_text as 'sql语句',
    count_star as '执行次数',
    sum_created_tmp_disk_tables as '磁盘临时表次数',
    sum_created_tmp_tables as '临时表总数'
from sys.statements_with_temp_tables
order by sum_created_tmp_tables desc
limit 10;

select 
    id, 
    user, 
    host, 
    db, 
    command, 
    time, 
    state, 
    info
from sys.processlist
order by time desc;

select 
    event_name as '内存事件',
    count_star as '次数',
    sum_number_of_bytes_used / 1024 / 1024 as '总内存(mb)'
from sys.memory_global_by_current_bytes
order by sum_number_of_bytes_used desc
limit 10;

select 
    table_schema as '数据库',
    table_name as '表名',
    index_name as '索引名',
    seq_in_index as '索引顺序',
    column_name as '列名',
    cardinality as '基数',
    non_unique as '是否唯一'
from sys.schema_index_statistics
order by table_schema, table_name, index_name;