doc

• 8.3

• Mysql45讲

log / variables

SHOW VARIABLES LIKE '%max_connections%';
 
SET GLOBAL max_connections = 1000;

show variables like '%log_bin%'

show variables like '%time_zone%';

set GLOBAL  time_zone='+8:00';

set  time_zone='+8:00';

set GLOBAL  general_log = 'ON';

set global log_output='table';

SELECT * from mysql.general_log order by event_time desc  limit 1;

truncate mysql.general_log;

select * from mysql.general_log where argument like '%%'  ;

递归查询

-- Mysql版层级查询(根据id查所有子节点的信息)(id,pid)
create  function getChildren(val varchar(64))
returns varchar(4000)
begin
	declare oTemp varchar(4000);
	declare oTempChild varchar(4000);
set oTemp = "";
set oTempChild = val;
while oTempChild is not null 
do
	set oTemp = concat(oTemp,',',otempchild);
    select group_concat(id) into oTempChild from TestA where  find_in_set(pid,oTempChild)>0;
end while ;
return oTemp;
END;
-- 
select * from TestA where find_in_set(xlh,getChildren('4'));

性能

-- 锁时间TOP10
SELECT DIGEST_TEXT, SUM_LOCK_TIME FROM 
performance_schema.events_statements_summary_by_digest ORDER BY SUM_LOCK_TIME DESC LIMIT 10
-- 平均响应时间TOP10
SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM 
performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;
-- 排序记录数TOP10
SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM 
performance_schema.events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC LIMIT 10;

-- 扫描记录数TOP10
SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM 
performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC 
LIMIT 10;
-- 创建临时表TOP10
SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM 
performance_schema.events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_TABLES DESC 
LIMIT 10

kill process

SELECT * FROM information_schema.PROCESSLIST where length(info) >0 ;

kill 742862

mock 表

创建模拟数据表

create table `mock`(
	`id` bigint(20) unsigned not null auto_increment,
    `name` varchar(50) default "" comment "用户姓名",
    `email` varchar(50) not null comment "用户邮箱",
    `phone` varchar(20) default "" comment "手机号",
    `gender` tinyint(4) unsigned default "0" comment "性别(0:男;1:女)",
    `password` varchar(100) not null comment "密码",
    `age` tinyint(4) default "0" comment "年龄",
    `create_time` datetime not null,
    `update_time` timestamp not null default current_timestamp on update current_timestamp,
    primary key(`id`)
)engine=innodb default charset=utf8mb4 comment "模拟数据";

创建存储过程

set global log_bin_trust_function_creators=TRUE;
 
delimiter $$
create function mock_data()
returns int 
begin
	declare num int default 1000001;
	declare i int default 1;
	while i < num do
		insert into mock(`name`,`email`,`phone`,`gender`,`password`,`age`,`create_time`)values
(concat("用户",i),concat(floor(rand()*999999999),"@qq.com"),concat("18",floor(100000000+rand()*(999999999-100000000))),floor(rand()*2),floor(rand()*999999),floor(18+rand()*42),current_timestamp);
		set i = i + 1;
	end while;
	return i;
end;
delimiter $$
select mock_data();

json array 按行展开

SELECT *
FROM JSON_TABLE('[5, 6, 7]', "$[*]"
                COLUMNS (
                    Value INT PATH "$"
                    )
     ) data;

set @delimited = 'a,b,c';

SELECT *
FROM JSON_TABLE(
             CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),
             "$[*]"
             COLUMNS (
                 Value varchar(50) PATH "$"
                 )
     ) data;

seq 表

WITH RECURSIVE seq AS (
   SELECT 1 AS v
   UNION ALL
   SELECT v + 1 FROM seq WHERE v < 10
)
SELECT * FROM seq;

虚拟字段 GENERATED

https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

CREATE TABLE IF NOT EXISTS database_entity (
    id VARCHAR(36) GENERATED ALWAYS AS (json ->> '$.id') STORED NOT NULL,
    fullyQualifiedName VARCHAR(256) GENERATED ALWAYS AS (json ->> '$.fullyQualifiedName') NOT NULL,
    json JSON NOT NULL,
    updatedAt BIGINT UNSIGNED GENERATED ALWAYS AS (json ->> '$.updatedAt') NOT NULL,
    updatedBy VARCHAR(256) GENERATED ALWAYS AS (json ->> '$.updatedBy') NOT NULL,
    deleted BOOLEAN GENERATED ALWAYS AS (json -> '$.deleted'),
    PRIMARY KEY (id),
    UNIQUE (fullyQualifiedName)
);

Window Function

https://baijiahao.baidu.com/s?id=1728966619393719484&wfr=spider&for=pc

类比oracle的 ROW_NUMBER() PARTITION BY,分组后排序

   SELECT stu_id,name,subject,score,
    ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS ROW_NUM,
    DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS DENSE_RK,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS RK
   FROM tb_score ts ;

优化-连接查询和子查询

-- 子查询(可以用于echarts的柱状图统计中),这样的查询效率更低,最好使用join
select a.name,
	(select b.b from TestB b where b.id = a.id)
from TestA a

-- left join + group by
SELECT COUNT(A.ID) AS NUM ,B.NAME AS NAME from  A
LEFT JOIN B ON A.ID = B.ID
GROUP BY B.NAME

优化-避免使用Not IN 和 IN

-- A是B的子表,查出所有无效的子表数据
SELECT * FROM  A WHERE A.TID NOT IN (SELECT ID FROM B) -- in是一种相关子查询,子查询会被循环执行
-- 使用 NOT EXISTS 替代
SELECT * FROM A WHERE  NOT EXISTS (SELECT B.ID FROM  B WHERE A.TID =B.ID )-- exists是非相关子查询,子查询先执行且只执行一次

问题-一个审批流程问题记录

现有任务表和任务详情表,用来保存审批流程 其数据结构简写如下: TASK { id,desc} TASK_DETAIL { id,tid,name,seq,state } 后者的seq是审批顺序,从1开始,tid关联task.id,state保存审批状态:1 已审批,0未审批. name为审批人的姓名 现在需要根据name 来查找出所有自己待审批的任务desc.

待审批的任务:流程前的人都已经审批通过或者流程第一个人待审批的合计. 所以隐藏条件就是,seq-1的那条数据的状态是已通过.

select a.tid from  task_detail a inner join
(select tid,(seq-1)as seq from task_detail where state = 0 and name = 'NameTest') b
on a.tid = b.tid and a.seq = b.seq
where a.state = 1 
union all 
select tid from task_detail where seq = 1 and state = 0 and name = 'NameTest'