mysql 日常随笔

前言

1.常用CURD

1.增

参考博客

1.单行插入

insert into tableName (colunm1,colunm2,...) value(value1,value2,...);

2.多行插入

insert into tableName(colunm1,colunm2,..) values(value1,value2...),(value1,value2...);

3.搭配set插入

insert into tableName  set colunm1=value1,colunm2=value2....;

4.搭配select插入

-- 从当前表
insert into tableName(colunm1,colunm2,..) select colunm1,colunm2,..;
-- 从其他表
insert into tableName(colunm1,colunm2,..) select colunm1,colunm2,.. from tableName1;

2.删

delete from table_name where id='123'

3.改

1.将表a的字段更新到表b

-- x表有a,b,c字段，y表有d,e,f字段，当x表的a字段等于y表的d字段时，将x表的b字段更新到y表的e字段，将x表的c字段更新到y表的f字段
UPDATE y  
JOIN x ON y.d = x.a  
SET y.e = x.b, y.f = x.c;

4.查

1.查找某字段值包含中文的数据

SELECT *
FROM student
WHERE name REGEXP '[^\x00-\x7F]+';

2.字符串转换

1.varchar转Date

SELECT STR_TO_DATE('2021-05-20 12:30:45','%Y-%m-%d %H:%i:%s') AS datetime;

2.Date的大小比较

SELECT * FROM your_table WHERE date_column1 > '2023-01-01';

3.Date类型的加减

1.date_add()

DATE_ADD() 函数是用于在日期时间值上添加指定的时间间隔的函数。它可以根据你的需求，在日期或日期时间字段中增加年、月、日、小时、分钟或秒钟等时间单位。
参考博客：SQL_时间函数-DATE_ADD()函数

DATE_ADD() 函数接受三个参数：日期时间值、时间间隔和时间单位。
日期时间值可以是日期类型、日期时间类型或时间戳类型。
时间间隔是一个整数，表示要添加的数量。
时间单位可以是年份 (YEAR)、月份 (MONTH)、日期 (DAY)、小时 (HOUR)、分钟 (MINUTE)、秒钟 (SECOND) 等等。
返回计算后的日期时间值。

-- 筛选24小时前到现在
where 1=1
and o.ARRIVAL_TIME > date_add(sysdate(),interval -24 hour) 

-- 7天后的时间，2023-09-14
SELECT DATE_ADD('2023-09-07', INTERVAL 7 DAY);

2.datediff()日期的加减

注意：
datediff()函数，只进行日期层级的加减计算，时分秒部分不参与计算
DATEDIFF( ‘2024-01-09 12:00:30’, ‘2024-01-08 11:04:00’) 与
DATEDIFF( ‘2024-01-09 12:00:30’, ‘2024-01-08 14:00:10’) 的计算结果是一样的

DATEDIFF(end_date, start_date)
end_date：表示要计算的时间段的结束日期。
start_date：表示要计算的时间段的开始日期。

3.unix_timestamp()

-- 小于24小时
(unix_timestamp(now())-unix_timestamp(STR_TO_DATE(s.crtd_tm, '%Y%m%d%H%i%s')))/(3600*24) < 1
-- 大于等于24小时，小于48小时
(unix_timestamp(now())-unix_timestamp(STR_TO_DATE(s.crtd_tm, '%Y%m%d%H%i%s')))/(3600*24) >=1 and (unix_timestamp(now())-unix_timestamp(STR_TO_DATE(s.crtd_tm, '%Y%m%d%H%i%s')))/(3600*24)  < 2

4.timestampdif()

注意：
第一个参数为HOUR时，计算粒度为小时（分秒不参与计算）
第一个参数为minute时，计算粒度为分钟（秒不参与计算），
TIMESTAMPDIFF(minute, ‘2024-01-09 12:00:30’, ‘2024-01-09 12:00:40’) 与 TIMESTAMPDIFF(minute, ‘2024-01-09 12:00:30’, ‘2024-01-09 12:00:20’) 的计算结果是一样的

SELECT TIMESTAMPDIFF(HOUR, '2024-01-04 00:00:05', '2024-01-06 00:01:05') AS diff

3.修改表结构

1.修改字段

1.增加字段

ALTER TABLE your_table ADD reward varchar(10) default null comment '奖惩单位' AFTER pro_res;

2.修改索引

1.增加索引

-- 单个字段索引
CREATE INDEX idx_lastname ON users(last_name);

-- 多字段联合索引
CREATE INDEX idx_name ON users(last_name, first_name);

2.基于索引优化MIN/MAX操作

4.mysql执行计划

1.MySQL执行查询过程

2.查询语句执行顺序

mysql执行顺序如下：

1. from 阶段

2. where 阶段

3. group by 阶段

4. having 阶段

5. select 阶段

6. order by 阶段

7. limit 阶段

按照以上书写顺序，完整的执行顺序应该是这样：

1. from子句识别查询表的数据；

2. where子句基于指定的条件对记录进行筛选；

3. group by 子句将数据划分成多个组别，如按性别男、女分组； 有聚合函数时，要使用聚集函数进行数据计算；

4. Having子句筛选满足第二条件的数据；

5. 执行select语句进行字段筛选。筛选重复数据；

6. 对数据进行排序；

7. 执行limit进行结果限定

3.explain 执行计划

1.Explain 执行计划中各字段的信息

参考博客1：面试官：不会看 Explain执行计划，简历敢写 SQL 优化？
参考博客2：Mysql优化之Explain详解、索引最佳实践

5.mysql调优

1.SQL性能测试

1.sql查询缓存

1. 清除整个查询缓存
   MySQL 不提供直接的方式来清除特定查询语句的缓存，因为查询缓存是基于查询结果集的。如果你想要清除查询缓存，你可以通过清除整个查询缓存来间接实现。清除查询缓存的方法是，在当前MySQL会话中执行以下命令：RESET QUERY CACHE;
   这个命令会清除当前MySQL实例的查询缓存。
2. 避免单词查询缓存
   如果你想要避免将来执行某个特定查询时使用缓存，你可以考虑不对那个查询使用查询缓存，或者在查询中使用一些特定的提示来避免缓存，例如：SELECT SQL_NO_CACHE * FROM your_table;在这个例子中，SQL_NO_CACHE 提示告诉MySQL不要缓存这个查询的结果。

请注意，查询缓存在MySQL中并不总是默认启用的，它可能需要在my.cnf或my.ini配置文件中启用。如果没有启用查询缓存，上述命令不会产生任何效果。

2.索引失效

1.聚合函数

  
1.在使用sum()、avg()等聚合函数时导致索引失效
2.max()、min()等函数与where同时使用时，例如在下列语句中，即时id市住建，mysql也会在扫描在过滤sex = '2’后得到的结果集里的所有数据

select max(id) as max_id from student where sex = '2' -- 2:女性

2.关键字in + 子查询

在 where column_name in (select xx from ...)中，例如下列语句中，即时子查询中的结果集很小且id是主键，也会导致外层查询的索引失效

3.索引调优

1.索引覆盖

2

4.sql调优案例

1.找分组组内最大的记录

基本背景：表内共有约7千个员工，每个员工负责若干个订单，每个订单有几千~几万条记录，每天记录会根据用upload_date字段存该条记录上传的时间。
查询需求：查出每个员工最新的一条记录。
数据表基本信息：

1. 表m_engineer_temp的数据量为6千2百万 x 24
2. 主键为id，bigint类型，自增唯一主键
3. srv_bp字段（工号）上建有非唯一索引。表中共有7千个不同的工号。
4. upload_date, srv_bp上建有联合索引

版本1：只查单个工号
执行时间：srv_bp无索引2m22s 有索引4s

select
	SQL_NO_CACHE 
	id,srv_bp,updated_date,order_id
from
	m_engineer_temp			-- srv_bp无索引2m22s		     有索引4s
where 1 = 1
	and srv_bp = "123456" 
	and updated_date = (select max(updated_date) maxDate from m_engineer_temp			where srv_bp = "123456" )
limit 1

优化后–版本2 ：只查单个工号
执行时间：0.3~1s

select
	SQL_NO_CACHE 
	id,srv_bp,updated_date ,order_id
from
	m_engineer_temp			
where 1 = 1
	and srv_bp = "123456" 
	and upload_date = (select max(upload_date) maxDate from m_engineer_temp where srv_bp = "123456" )
limit 1

优化后–版本3 ：只查单个工号
执行时间：0.3~1s

select
	SQL_NO_CACHE 
	id,srv_bp,longitude,latitude,updated_date 
from
	m_engineer_temp						-- srv_bp无索引1m22s		     有索引快的0.5s，慢的2s
where 1 = 1
	and id = (select  id from m_engineer_temp where srv_bp = "123465" order by updated_date desc limit 1)	

优化后–版本4 ：可查多个工号
下列SQL执行时间约30ms，查询性能高的详细原因尚未完全理解。

select SQL_NO_CACHE r1.id as "id", r1.updated_date as "updatedDate", r1.upload_date as "uploadDate", r1.upload_status as "uploadStatus", r1.srv_bp as srvBp, r1.order_id as orderId
from m_engineer_temp as r1
inner join (
				select
					max(id) max_id
				from
					m_engineer_temp
				where
					srv_bp in ( '123456' , '456789')
					group by srv_bp 
			) as r2 	
on r1.id = r2.max_id
order by r1.upload_date;

explain的执行计划如下：

6.有趣的问题

1.自增主键的优劣

1.自增主键类型和取值范围

主键类型	范围	范围	位数
int	-2^31 ~ 2^31 - 1	(-2,147,483,648) ~ (2,147,483,647)	10位数
bigint	-2^63 ~ 2^63-1	(-9223372036854775808) ~ (9223372036854775807)	19位数

2.自增主键ID问题

参考博客：Mysql 主键设计
1.可靠性不高
  存在主键ID回溯，这个bug在mysql 8.0才修复。 主键ID回溯就是: 创建一个表，主键ID且整形且AUTO_INCREMENT，然后插入三条数据，然后show create table 查看，此时的AUTO_INCREMENT为4，删除ID=3的数据，此时AUTO_INCREMENT仍为4，重启mysql-server，此时AUTO_INCREMENT就为3了。

2.安全性不好
  对外接口很容易暴露业务信息，如/user/1接口，那么别人就知道用户ID是多少，新注册一个用户就知道用户数量是多少，轻易的使用爬虫工具进行数据的获取。

3.性能差
  自增ID由服务器生成。在高并发插入数据时，存在AUTO_INCREMENT锁竞争问题（详见InnodBD内幕p262）。

AUTO_INCREMENT锁竞争问题初步理解：
  增长在数据库中是非常常见的一种属性，也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器.插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称为AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。
  例如：使用了insert into a select … from b … 因为无法预知会插入多少行,所以会对表a直接生成insert操作的表锁，直到insert执行完毕，对b表是逐条加读锁，直到select执行完毕。如果该SQL脚本的执行过程较长，会产生一个长时间的表锁，导致性能大幅下降。
参考博客1
参考博客2
参考博客3

4.交互多
  业务还需要一次last_insert_id才能知道刚才插入数据的ID，这需要多一次的网络交互。这在高并发性能吃紧的场景下是及其浪费的。

5.局部唯一性
  只能在当前数据库中唯一，而非全局唯一，对于核心业务表，这在分布式环境下是绝对不允许的。

3.优势

1.很小的数据存储空间
2.性能最好
3.容易记忆

2.

7.DBeaver使用

1.DBeaver导出cvs的编码格式

GB2312格式导出后中文不会乱码，其他格式会导致中文乱码