mysql锁分析 语法 返回第一个null的方法

show OPEN TABLES where In_use > 0;
show index from t_bwprocess_bw;
查看当前的事务
select * from information_schema.innodb_trx;
查看当前锁定的事务
select * from information_schema.innodb_locks;
查看当前等锁的事务
select * from information_schema.innodb_lock_waits;
show processlist;
show tables;
show status like 't_bwprocess_bw%';
SELECT @@tx_isolation
lock table t_bwprocess_bw write;
unlock tables;
执行
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;
查询所有数据的大小select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
查看指定数据库的大小select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='prod_zdsb';
查看所有数据库show databases;
查看数据库创建时间SELECT create_date FROM sys.databases WHERE name = 'Test'
查看数据库最大连接数show variables like '%max_connections%';

QPS(每秒Query量)QPS = Questions(or Queries) / uptime

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

TPS(每秒事务量)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';
show global status;
show global status like '%...%';

设置数据库访问白名单,@后面的是指定白名单的ip

GRANT ALL ON . to root@'192.168.1.4' ;

查询正在执行的sql

select * from information_schema.PROCESSLIST where info is not null;

查询执行的sql

SELECT * from mysql.general_log ORDER BY event_time DESC;

开启日志模式show variables like 'log_bin'

SET GLOBAL log_output = 'TABLE';SET GLOBAL general_log = 'ON';

错误日志log-errol开启方式：

在my.ini的[mysqld]选项下：添加代码：log-error=E:\log-error.txt

查看当前日志

当前的日志mysql> show master status

log开启方式：

在my.ini的[mysqld]选项下：添加代码：log=E:/mysql_log.txt

二进制日志：log-bin，开启方式：

在my.ini的[mysqld]选项下：添加代码：log-bin=E:/mysql_log_bin。记录所有的更改数据的语句，可使用mysqlbinlog命令恢复数据

创建数据库实例

create instance instancename;

在指定实例下创建用户

add account instance:instancename accountname:name;

查看指定用户的权限

SHOW GRANTS FOR 'prod_zdsb'@'%';
1.最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,
比如a=3 and b=4 and c<5 and d=6。如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引,则a,b,d的顺序可以任意调整
2.=和in可以乱序,比如a=1 and b=2 and c=3建立索引(a,b,c)索引可以任意顺序,mysql的查询优化器会优化成索引可以识别的形式

------------------衡量索引标准------------------------------
1.show status like 'Handler_read%'
2handler_read_key这个值越高越好，说明用索引查询的次数
3handler_read_rnd_next这个值越高说明查询低效

show status like 'Handler_read%'

EXPLAIN t_bwprocess_bw
SHOW COLUMNS FROM t_bwprocess_bw
DESCRIBE t_bwprocess_bw
ANALYZE TABLE t_bwprocess_bw
STRAIGHT_JOIN
当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解

当extra出现Using filesor或Using temproary时，表示无法使用索引，必须尽快做优化。
key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
一般来说，得保证查询至少达到range级别，最好能达到ref，type出现index和all时，表示走的是全表扫描没有走索引，效率低下，这时需要对sql进行调优。
type结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

add database {"instName":"rm-fk4frq8wthuya5u7n","databaseName":"prod_zdsb","databaseCharset":"utf8","region":"cn-langfang-lt-d01","engine":"MySQL","accountInfo":[{"name":"prod_zdsb","privilege":"ReadWrite","selected":false}],"departmentId":"215","projectI

modify account {"accountrightinfo":{"instname":"rm-fk4frq8wthuya5u7n","accountname":"prod_zdsbread","databaseInfo":[{"database":"prod_zdsb","privilege":"ReadOnly","selected":false}],"originInfo":[],"region":"cn-langfang-lt-d01","departmentId":"215","proje
关联表修改
UPDATE pbm_goods.lmshop_goods g
JOIN pbm_goods.lmshop_goods_spec gs ON g.id = gs.goods_id
JOIN pbm_admin.lmshop_goods_audit ga ON ga.goods_id = g.id
JOIN pbm_admin.lmshop_goods_spec_audit gas ON gas.goods_spec_id = gs.id
SET
g.goods_up_time = NOW(),
g.goods_show = 1,
gs.spec_show = 1 , gs.spec_up_time = NOW(),
g.goods_status = 30 ,
gs.goods_spec_status = 30 ,
ga.goods_status = 30,
gas.goods_spec_status = 30,
ga.is_new = 0
WHERE
ga.is_new = 1
AND gas.is_new =1
AND g.goods_serial IN
('SPU2021042210011900',
'SPU2021042210012400',
'SPU2021042210012100',
'SPU2021042210012600')
关联删除级联逻辑表
-- 彻底删除申请单
DELETE i, ig, ii, il, oa, ag
FROM lmshop_request_notes AS i
left JOIN lmshop_indent_goods ig ON i.id = ig.request_id
left JOIN lmshop_indent_invoice ii ON i.id = ii.request_id
left JOIN lmshop_indent_log il ON i.id = il.request_id
left JOIN lmshop_order_address oa ON i.id = oa.request_id
left JOIN lmshop_address_goods ag ON i.id = ag.request_id
WHERE i.pr_id in ('PRTK0020210300000434');
返回参数中第一个不为null的值
coalesce
ifnull