表操作

数据类型

int

TINYINT 0-255 2^8 1byte

SMALLINT 0-65535 2^16 2byte

MEDIUMINT 0-16777215 2^24 3byte

INT BIGINT 0-4294967295 2^32 4byte

int(M)，这里的M代表的并不是存储在数据库中的具体的长度，以前总是会误以为int(3)只能存储3个长度的数字，int(11)就会存储11个长度的数字，这是大错特错的。

M指最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关。 如果字段数据类型是 int(4)，则：当显示数值
10 时，在左边要补上 “00”；

当显示数值 100 是，在左边要补上“0”；

当显示数值 1000000 时，已经超过了指定宽度“（4）”，因此按原样输出。

这个M=4我们可以简单的理解成为, 我们建立这个长度是为了告诉MYSQL数据库我们这个字段的存储的数据的宽度为4位数,
当然如果你不是5位数(只要在该类型的存储范围之内)MYSQL也能正常存储.

int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。说白了，除了显示给用户的方式有点不同外，int(M)
跟 int 数据类型是相同的。 int(M) 只有跟 zerofill 结合起来，才能使我们清楚的看到不同之处

定义时需要注意是否使用 unsigned 符号类型，再就是显示补齐宽度值

VARCHAR TEXT BLOB

BLOB和TEXT列不能有默认值

varchar(n)这里的n表示字符数，比如varchar（200）

最大大小是65532字节

每个汉字3个字节

MySQL中varchar最大长度是多少？

varchar最多能存储65535个字节的数据。null标识位和前缀长度列表会占用2-3个字节。

字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766;

字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。

若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。

varchar会走索引吗

当MySQL中字段为int类型时，搜索条件where num=‘111‘ 与where num=111都可以使用该字段的索引。
当MySQL中字段为varchar时，搜索条件where num=‘111‘ 可以使用索引，where num=111 不可以使用索引。因为对varchar型字段的索引，字段类型不同造成的隐式转换，导致索引失效

datetime & timestrap

参考

alter

alter table tablename  modify/add/rename/drop (name varchar2(20));

union

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。 UNION ALL 来选取重复的值。

join

group

报表

常用的数据指标

DAU 日活

SELECT dt,COUNT(DISTINCT id) AS c
FROM xxx
GROUP BY dt;

7日日活

次日留存率

// 两天都在的用户数/每天的用户数
SELECT ta.dt,c/ca
FROM(
	SELECT t1dt
		,COUNT(DISTINCT id) AS c
	FROM(
		SELECT t1.id
			,t1.dt AS t1dt
			,t2.dt AS t2dt
		FROM yhlc t1
		JOIN yhlc t2
		ON t1.id=t2.id
		WHERE TIMESTAMPDIFF(DAY,t1.dt,t2.dt)=1
	) t
	GROUP BY t1dt
) ta
JOIN(
	SELECT dt,COUNT(DISTINCT id) AS ca
	FROM yhlc
	GROUP BY dt
) tb
ON ta.t1dt=tb.dt;

次日的用户留存数

//昨天和今天都在的用户数
SELECT t1dt,COUNT(DISTINCT id) AS c
FROM(
	SELECT t1.id
		,t1.dt AS t1dt
		,t2.dt AS t2dt
	FROM yhlc t1
	JOIN yhlc t2
	ON t1.id=t2.id
	WHERE TIMESTAMPDIFF(DAY,t1.dt,t2.dt)=1
) t
GROUP BY t1.dt;

7日留存

SELECT
	log_day '日期',
	count( user_id_day0 ) '新增数量',
	count( user_id_day1 ) / count( user_id_day0 ) '次日留存率',
	count( user_id_day2 ) / count( user_id_day0 ) '3日留存率',
	count( user_id_day7 ) / count( user_id_day0 ) '7日留存率',
	count( user_id_day30 ) / count( user_id_day0 ) '30日留存率' 
FROM
	(
	SELECT DISTINCT
		log_day,
		a.user_id_day0,
		b.user_id AS user_id_day1,
		c.user_id AS user_id_day3,
		d.user_id AS user_id_day7,
		e.user_id AS user_id_day30 
	FROM
		( SELECT DISTINCT 
				Date( login_time ) AS log_day, 
				user_id AS user_id_day0 
				FROM 
				t_user_login 
				GROUP BY user_id 
				ORDER BY log_day 
				) a
		LEFT JOIN t_user_login b ON DATEDIFF( DATE( b.login_time ), a.log_day ) = 1 
		AND a.user_id_day0 = b.user_id
		LEFT JOIN t_user_login c ON DATEDIFF( date( c.login_time ), a.log_day ) = 2 
		AND a.user_id_day0 = c.user_id
		LEFT JOIN t_user_login d ON datediff( date( d.login_time ), a.log_day ) = 6 
		AND a.user_id_day0 = d.user_id
		LEFT JOIN t_user_login e ON datediff( date( e.login_time ), a.log_day ) = 29 
		AND a.user_id_day0 = e.user_id 
	) temp 
GROUP BY
	log_day

MySQL

慢查询优化

排查慢查询的思路：看检索行数和查询时间。用explain和show profile看一条sql查询的细节。

1. 没有索引的，加索引，或者改where条件或orderby的条件；
2. 有索引但是没有走，用force index。选择索引是优化器的工作
3. 有索引遇到最左前缀匹配：in的语句把in拆开，改成unionall（不适用于limit rn, pn）
4. 最左前缀匹配：大于小于不等于：把相关的范围条件取出来，放在PHP里面判断。
5. 数据量太大，扫码行数太多：一次取不出来，分成多次取，每次有序的、取固定数目的值。直到完。这样每次取的数量都很稳定。绝对不会慢查询。
6. 查询条件太多，可以只根据一个条件去查询，对取出的结果再做其他条件的判断。这样就可以把MySQL查询的压力转移到程序里。

索引

索引类型

原理

B+树

一棵B+树可以存多少行数据？

调优

执行引擎

select count(*) count(1) count(列名)

主从同步，binlog，主从延时问题

事务

锁

常见的SQL题目

1.查找表中多余的重复记录

时间格式

Y-m-d H-i-s YYYY-MM-DD hh:mm:ss
Date_format格式说明：
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%H 小时(00-23)
%h 小时(01-12)
%i 分钟，数值(00-59)
%M 月名
%m 月，数值(00-12)
%p AM或PM
%s 秒(00-59)
%W 星期名
%w 周的天（0=星期日, 6=星期六）
%Y 年，4位
%y 年，2位

时区

我们是 UTC + 8 GMT + 8

参考

参考

最后更新于 2023年2月6日 by qlili