本文已参与「新人创作礼」活动，一起开启掘金创作之路。
持续更新
mysql查看事务
SELECT
a.trx_id,
a.trx_state,
a.trx_started,
a.trx_query,
a.trx_weight,
a.trx_lock_memory_bytes,
b.ID,
b.USER,
b.DB,
b.COMMAND,
b.TIME,
b.STATE,
b.INFO,
c.PROCESSLIST_USER,
c.PROCESSLIST_HOST,
c.PROCESSLIST_DB,
d.SQL_TEXT
FROM
information_schema.INNODB_TRX a
LEFT JOIN
information_schema.PROCESSLIST b
ON
a.trx_mysql_thread_id = b.id
LEFT JOIN
PERFORMANCE_SCHEMA.threads c
ON
b.id = c.PROCESSLIST_ID
LEFT JOIN
PERFORMANCE_SCHEMA.events_statements_current d
ON
d.THREAD_ID = c.THREAD_ID;
mysql开窗函数实现
求每个部门收入最高的员工
SELECT
*
FROM
(
SELECT
IF(@v_department_id=c.department_id,@rn:=@rn+1,@rn:=1) AS rn,
@v_department_id:= c.department_id,
c.department_id,
c.name,
c.salary
FROM
(
SELECT
b.DEPARTMENT_ID,
b.name,
SUM(b.SALARY) SALARY
FROM
department a,
salary b
WHERE
a.id = b.department_id
GROUP BY
b.DEPARTMENT_ID,
b.name) c, (select @v_department_id:=-1) d
ORDER BY
c.department_id,
c.salary DESC) d
WHERE
d.rn=1;
//试验数据
CREATE TABLE department (id int, name varchar(100)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE salary (department_id int, name varchar(100), salary int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO department (id, name) VALUES (1, '霸气的部门');
INSERT INTO department (id, name) VALUES (2, '牛皮的部门');
INSERT INTO department (id, name) VALUES (3, '最拽的部门');
INSERT INTO salary (department_id, name, salary) VALUES (1, 'Superman', 1000);
INSERT INTO salary (department_id, name, salary) VALUES (1, 'Superman', 2000);
INSERT INTO salary (department_id, name, salary) VALUES (1, 'spiderman', 3500);
INSERT INTO salary (department_id, name, salary) VALUES (1, 'hulk', 6000);
INSERT INTO salary (department_id, name, salary) VALUES (2, 'Jack', 5000);
INSERT INTO salary (department_id, name, salary) VALUES (2, 'Rose', 3000);
INSERT INTO salary (department_id, name, salary) VALUES (2, 'Tom', 4000);
INSERT INTO salary (department_id, name, salary) VALUES (3, 'Jerry', 2000);
INSERT INTO salary (department_id, name, salary) VALUES (3, 'Harry', 3000);
mysql查看配置参数
show variables like '%buffer%';
mysql查看当前正在使用的表
show open tables;
mysql查看引擎状态（包含最后一次死锁原因记录）
show engine innodb status
mysql查看当前进程
show processlist;（进程对应的sql可能显示不全）
show full processlist;（进程对应的sql可以显示全，内容可能会贼多）
mysql查看执行计划
explain select * from tableName;
desc select * from tableName;
mysql导出结构不包含数据
// 如果不指定表则代表导出整个库，表可以指定多个
mysqldump --skip-lock-table --no-data -hip -uuser -ppwd -Pport dbname tableName > dump.sql
mysql导出数据不包含结构
mysqldump --skip-lock-table --no-create-info --no-create-db -hip -uuser -ppwd -Pport dbname tableName > dump.sql
mysql查看建表语句
show create table filter_monitor
mysql创建、删除、查看索引
// 创建
ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD UNIQUE (column_list);
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);
CREATE TABLE `pack_group` (
PRIMARY KEY (`id`),
UNIQUE `idx_shop_beehive_packable_time` (`shop_id`,`beehive_id`,`packable_time`),
KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分组状态表';
// 删除
DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;
// 查看
show index from tblname;
show keys from tblname;
mysql查看表碎片
如果索引的Cardinality属性与count(distinct字段）差别很大，说明碎片很多，需要整理，不然执行可能会很慢
select count(distinct columnName) from tableName;
show index from tableName;
mysql innodb引擎数据碎片整理
// 查看表的磁盘碎片，如果很多，则进行收集整理
alter table tableName engine=innodb;
analyze table tableName;(不会立即整理，会后台慢慢整理，不会阻塞当前会话）
optimize table tableName;(立即整理，阻塞当前会话，不可终止，如果终止可能会导致表数据文件损坏）
mysql 更改字段
alter table tableName modify `columnName` tinyint NOT NULL COMMENT '注释', modify ...;
ALTER TABLE tableName CHANGE oldColumnName newColumnName CHAR(32) NOT NULL DEFAULT '123';
mysql查看端口与版本号
show global variables like 'port';
show global variables like 'version';
mysql查看事务隔离级别
select @@global.tx_isolation;
