mysql数据库一些常规操作

1.为指定mysql用户指定表授权
grant select, insert, update, delete on testdb.orders to dba@localhost ;
2.查看当前mysql所有用户
SELECT DISTINCT CONCAT(‘User: ‘’’,user,’’’@’’’,host,’’’;’) AS query FROM mysql.user;

3.查询当前数据库中是否存在表锁
show OPEN TABLES where In_use > 0;
4.解决表锁
show processlist
查询到相对应的进程===然后 kill id
5.查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
6.查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
7 show status like ‘%lock%’
查看服务器状态。
8 show variables like ‘%timeout%’;
查看服务器配置参数。
9 查看慢查询时间
show variables like “long_query_time”;默认10s
10查看慢查询配置情况
show status like “%slow_queries%”;
11.//查看慢查询日志路径
show variables like “%slow%”;
12.查看该表的所有索引
select * from all_indexes where table_name = ‘tablename’;
13.查看该表的所有索引列
select* from all_ind_columns where table_name = ‘tablename’;
14.为指定表增加索引
create index 索引名 on T_COUNT_CALL_LOGIN(列名);