replace into
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
show slave status;
enforce_gtid_consistency
SELECT UNIX_TIMESTAMP('2009-08-06') ;
SELECT UNIX_TIMESTAMP() ;SELECT FROM_UNIXTIME(1502184955,'%Y-%m-%d %H:%i:%s');SELECT FROM_UNIXTIME(substring('1502184608028',1,10),'%Y-%m-%d %H:%i:%s');select date_format(from_unixtime(substring('1472009536350',1,10)),'%Y-%m-%d')select * from table where token regexp '^[0-9]+$';
show status like 'innodb_row_lock%';
show processlist;
找到锁进程，kill id ;UNLOCK TABLES;
SHOW PROCESSLIST
show full processlist;
SHOW PROFILE CPU,BLOCK IO
https://www.2cto.com/database/201703/618261.html
Binlog Dump GTID Master has sent all binlog to slave; waiting for more updates
1、将字段类型设为 TIMESTAMP
2、将默认值设为 CURRENT_TIMESTAMP
Field LIKE CONCAT('%',@0,'%')
like concat('%,',t.userid,',%')";
SELECT GROUP_CONCAT(field) FROM tbl
regexp
SHOW VARIABLES LIKE '%character_set_server%';
SHOW VARIABLES LIKE '%collation_server%';SHOW VARIABLES LIKE '%character_set_database%';SHOW VARIABLES LIKE '%character_set_client%';
SHOW VARIABLES LIKE '%character_set_connection%';SHOW VARIABLES LIKE '%character_set_connection%';
SHOW VARIABLES LIKE '%character_set_results%';
/*查询*/
SHOW GLOBAL STATUS LIKE 'Com_%' ;SHOW GLOBAL STATUS LIKE 'innodb_%';SHOW GLOBAL STATUS LIKE 'slow_%';/*开启日志*/ SHOW processlist； /*执行计划*/DESC SELECT* FROM userinfo
SELECT COUNT(*) FROM `studentaccount`;SHOW PROFILES;SHOW PROFILE FOR QUERY 10;/*SQL优化器的 优化后的SQL*
filter在Sserver层过滤后剩下多少满足查询的记录数量 */EXPLAIN EXTENDED SELECT SUM(amount) FROM customer a,payment b WHERE 1=1 AND a.customer_id=b.customer_id AND email='1111'SHOW WARNINGS;/*
*/ SELECT COUNT(*) FROM payment; SHOW PROFILES; SHOW PROFILE FOR QUERY 69;/* 语句能够查看到执行过程中线程中的每个状态和消耗的时间 *//*索引情况查看handler_read_key 的值将很高，这个值代表一个行被索引值读的次数，很低的值表名增加索引得到戏能改善不高，。hannler_read_rnd_next 的值高*/ SHOW STATUS LIKE 'handler_read%'; /* 查看进程*/ SHOW PROCESSLIST; /* kill命令使用方法 */KILL pid
/* 简单的优化方法 1、定期分析表和检查表*/ANALYZE TABLE payment;
CHECK TABLE payment;SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA= 'adultedu';
/* 禁用和启用索引 */ALTER TABLE tbl_Name DISABLE KEYS;ALTER TABLE tbl_Name ENABLE KEYS;SHOW VARIABLES LIKE '%sort_buffer_size%'
SHOW GLOBAL STATUS LIKE '%Sort_%'; SHOW VARIABLES LIKE '%buffer_pool_size%'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; SHOW VARIABLES LIKE '%max_length%'; /* b. 查看当前会话隔离级别: */SELECT @@tx_isolation;/*
c.查看系统隔离级别: */ SHOW VARIABLES LIKE '%key_cache_block_size%';SHOW STATUS LIKE 'key_%';
/*
缓存优化从索引的角度考虑
Key_reads/ Key_read_requests <0.01
Key_reads / Uptime Key_blocks_used*key_cache_block_size/key_buffer_size */
mysql:
JSON_EXTRACT
SELECT
distinct(JSON_EXTRACT(info,'$.APPLICATION.Identifier')) ApplicationIdentifier,JSON_EXTRACT(info, '$.APPLICATION.Name') NameNOT REGEXP 'server[0-9]+'
NOT REGEXP '^20[0-9]{2,2}-[0-9]{1,2}-[0-9]{1,2}$'
DATE_FORMAT(create_time,'%Y-%m-%d') = '2017-06-16'
binary platform like "A%"
sql server:
LIKE '[0-9][0-9][0-9][0-9]'; like '192.168.[0-9+].%' 方法1: Sql Server: Select * Into new_table_name from old_table_name; 这是sql server中的用:
MYSQL:
Create table new_table_name (Select * from old_table_name); insert into a select * from b
查询表级锁争用情况
获取InnoDB行锁争用情况
原理分析：create table as是ddl语句，insert into select是dml语句，insert into select每一条记录的时候都会产生undo和redo，整个过程相比create table as产生的redo和undo相当多，因此整个过程会慢也是正常的；但是create table as使用的前提是目标表的结构不存在才能使用；
当有大量数据的时候不推荐使用Insert into as，因为该语句的插入的效率很慢；