mysql小结

select host from user where user='root';查看允许连接的主机

update user set host = '%' where user ='root';

FLUSH PRIVILEGES;

添加用户

CREATE USER 'prod_zdsb'@'localhost' IDENTIFIED BY 'ProdZdsb_2019';

添加访问数据库权限

grant all privileges on prod_zdsb.* to 'prod_zdsb'@'localhost'
revoke all on *.* from prod_zdsb@localhost;

查询是否锁表
show OPEN TABLES where In_use > 0;

mysql> use sj;  //切换到sj 库

mysql> show OPEN TABLES where In_use>0 ;  //查询是否有表锁住

Query OK, 0 rows affected (0.00 sec)

mysql> LOCK TABLE sj.t1 write ;     //给sj里的t1表加写锁
Query OK, 0 rows affected (0.00 sec)

mysql> show OPEN TABLES where In_use>0  ; //查询是否有表锁住
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| sj | t1 | 1 | 0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> unlock table ;   给锁住的表解锁
Query OK, 0 rows affected (0.00 sec)

mysql> show OPEN TABLES where In_use>0
-> ;
Empty set (0.00 sec)

2、查询进程
show processlist

3、查询锁

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
Empty set, 1 warning (0.00 sec)

4、查询锁等待

select * from information_schema.INNODB_LOCK_WAITS;

5、查询事务

select * from information_schema.INNODB_TRX;

6、查看mysql操作日志

show variables like '%general_log%';
set global general_log = on;
show variables like 'log_output';
set global log_output='table';
set global log_output='FILE';
select * from mysql.general_log;

执行
show engine innodb status;
查看latest detected deadlock
-----------------创建倒序索引，对于经常更新最新的数据---------------------------------------------
create index idx_cid_sjhm on t_client_client(cid desc,sjhm)
创建匹配左前n字符索引
ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);
强制使用索引
select id from t_bwprocess_bw force index(idx_bwprocess_bw_createtime) where create_time <='2021-03-01'
AND 只要用到了最左侧a列，和顺序无关 都会使用 索引。不包含最左侧的 a 的不使用索引。OR 不使用索引
最左侧的‘a’列 被大于，小于，不等于比较的 ，不使用索引
最左侧a=某某，后面列大于小于无所谓，都使用索引（但后面必须 and ）
a = 某，后面order 无所谓 都 使用索引
基于索引使用prepare 预处理语句
PREPARE stmt_name FROM 'SELECT * FROM t_bwprocess_bw WHERE id =? ORDER BY id ASC LIMIT 50';
SET @a = 3;
EXECUTE stmt_name USING @a;
DEALLOCATE PREPARE stmt_name;
查看表show table status;
查看每个表存储信息SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS,DATA_LENGTH,CREATE_TIME,UPDATE_TIME
FROM information_schema.tables
order by UPDATE_TIME DESC;
use information_schema;

QPS:

show global status like 'Question%';
show global status like 'uptime%';

TPS=(Com_commit+Com_rollback)/uptime

show global status like 'Com_commit';
show global status like 'Com_rollback';

key Buffer 命中率
key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%
show global status like 'key%';

InnoDB Buffer命中率innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
show status like 'innodb_buffer_pool_read%';

Query Cache命中率Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;
show status like 'Qcache%';

Table Cache状态量比较 open_tables 与 opend_tables 值
show global status like 'open%';

Thread Cache 命中率Thread_cache_hits = (1 - Threads_created / connections ) * 100%
show global status like 'Thread%';
show global status like 'Connections';

Table_locks_waited/Table_locks_immediate=0.3%Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的
show global status like '%lock%';

复制延时量查看延时时间
show slave status;

Tmp Table 状况(临时表状况)Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，可能是排序句子过多或者是连接句子不够优化
show status like 'Create_tmp%';

Binlog Cache 使用状况如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小
show status like 'Binlog_cache%';

Innodb_log_waits 量Innodb_log_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待

show status like 'innodb_log_waits';