Skip to content

Instantly share code, notes, and snippets.

@weedge
Created December 17, 2022 18:04
Show Gist options
  • Save weedge/4492804aef6daee2aac03c467676eaea to your computer and use it in GitHub Desktop.
Save weedge/4492804aef6daee2aac03c467676eaea to your computer and use it in GitHub Desktop.
show engines;
show databases;
drop database pay;
create database pay ;
use pay;
show tables;
drop table `pay`.`user_asset`;
CREATE TABLE `user_asset`
(
`userId` bigint unsigned NOT NULL DEFAULT '0',
`assetCn` bigint unsigned NOT NULL DEFAULT '0',
`assetType` tinyint unsigned NOT NULL DEFAULT '0',
`version` bigint unsigned NOT NULL DEFAULT '0',
`createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uk_user_assetType` (`userId`,`assetType`)
) DEFAULT CHARSET=utf8mb4 SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 partition by hash ( `userId` ) partitions 16;
show create table user_asset;
desc user_asset;
#show columns from user_asset;
show full columns from user_asset;
show table `pay`.`user_asset` REGIONS;
create table user_asset1 like user_asset;
drop table `pay`.`user_asset1`;
show index from pay.user_asset;
alter table pay.user_asset add unique index `ind_user_asset` (`userId`,`assetType`);
show index from pay.user_asset;
select * from `pay`.user_asset;
insert into `pay`.`user_asset` (userId, assetCn, assetType, version, createdAt) values ('100','100','1','1',now());
select count(1) from pay.user_asset;
truncate table pay.user_asset;
select userId from `pay`.user_asset group by userId,assetType having count(1)>1;
explain select userId,assetType from user_asset where userId=100 and assetType=1;
explain select userId,assetType,version from user_asset where userId in (0,100,101) and assetType=1;
explain select * from user_asset where userId in (100,101) and assetType=1;
drop table `pay`.`user_asset_record`;
create table `pay`.`user_asset_record`
(
`userId` bigint unsigned not null default '0',
`opUserType` tinyint unsigned not null default '0',
`bizId` bigint unsigned not null default '0',
`bizType` tinyint unsigned not null default '0',
`objId` varchar(128) not null default '',
`eventId` varchar(128) not null default '',
`eventType` varchar(128) not null default '',
`record` varchar(256) not null default '',
`recordOp` varchar(64) not null default '',
`createdAt` timestamp not null DEFAULT CURRENT_TIMESTAMP,
`updatedAt` timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `uk_user_opUserType_event` (`userId`,`opUserType`,`eventId`)
)engine=InnoDB default charset=utf8mb4 SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=5
PARTITION BY HASH (`userId`) PARTITIONS 16;
show create table user_asset_record;
desc user_asset_record;
#show columns from user_asset_record;
show full columns from user_asset_record;
show index from pay.user_asset_record;
show table pay.user_asset_record regions;
select * from user_asset_record;
select count(1) from user_asset_record;
truncate table user_asset_record;
set autocommit =0;
show profiles;
show variables like '%commit%';
show global variables like '%commit%';
show table status;
set autocommit =1;
#show lock status
show status like '%table_lock%';
show status like '%row_lock%';
show processlist ;
#show innodb deadlock
show engine innodb status;
#select * from information_schema.innodb_locks;
#select * from information_schema.innodb_lock_waits;
show OPEN TABLES where In_use > 0;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment