MySQL复习笔记

常用函数

日期时间

时间戳转格式化日期

1
2

# 输出 1970-01-01 08:00:01
SELECT FROM_UNIXTIME(1) AS t

日期转时间戳

1
2

# 输出 0
SELECT UNIX_TIMESTAMP('1970-01-01 08:00:00') AS t

ACID

事务的隔离级别

默认隔离级别：可重复读

锁

排他锁：低并发场景性能低，适合高并发场景。

乐观锁：CAS，比较并替换。高并发的情况下，乐观锁性能低，使用于低并发场景。

自旋：获得不到锁，不断尝试获取锁的过程称之为自旋。

死锁如何解决

死锁不能解决，只能避免。

• 所有事务都按照相同的加锁顺序，业务约定顺序容易遗忘，最好按照表名字典序上锁
• 给锁设置超时时间，死锁时超时自动释放

配置事务隔离级别

todo

READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE

事务1加排他锁查询，where条件无索引。 事务2更新操作，where条件无索引，均阻塞。 事务2更新操作，where条件有索引，非同一行数据，不阻塞。 事务2更新操作，where条件有索引，且事务1操作同一行数据，阻塞。

事务1加排他锁查询，where条件无索引。 事务2排他锁查询操作，where条件无索引，均阻塞。 事务2排他锁查询操作，where条件有索引，非同一行数据，不阻塞。 事务2排他锁查询操作，where条件有索引，其与事务1操作同一行数据，阻塞。

查询优化

select * 的缺点

• 不能有效利用索引覆盖，索引覆盖就是你查询的字段都有索引，你一张表不可能所有字段都建索引
• 让查询变慢，查询的数据量越大，IO 就越慢，网络传输也越慢

使用 char 还是 varchar

char 是定长，varchar 是变长。定长的好处是固定长度，很容易计算下一个数据。如果数据的长度是固定的，那就选 char。

应用：

• md5 值是固定 32 位，肯定选 char
• 姓名虽然看似不定长，有人 2 个字，有人 3 个字，更有甚者 6 个字，为了提高查找效率，我们完全可以大方点给他char(32)，如果你名字真超过 32 个长度，你赢了
• 手机号用 char，大陆手机号 11 位，还不固定吗，计算是座机，空出几个字符又如何，空间换时间不丢人
• 手机号不要用 int，因为 char 支持模糊查询，更适合他的业务

不使用视图，触发器，存储过程，事件 Event

缺点：

• 可移植性不好，底层换数据库类型，这些东西都要重写
• 并发大的时候，效率低。数据库只适合干存储和索引，计算的事情交给业务层代码来完成。

const

主键查询

使用 like 查询主键就变成了 all。

唯一性索引 unique key，如果使用 like 查询就是 range，使用 = 查询就是 const，可见性能差距还是挺大的。

ref

使用了普通索引，或者唯一性索引的左前缀。

range

常见于>，<，=，in，like，和between的查询，且使用了索引。

insert优化

解决添加重复数据的问题

insert ignore into

添加 ignore 关键字，如果添加的记录存在主键索引或唯一性索引重复时，返回 0。

replace into

使用replace into替代insert into，此操作即先添加新记录，如果发现存在重复主键索引或唯一性索引时，==先删除旧记录==，==再插入新记录==。

on duplicate key update …

在 insert into 语句后添加，不重复则插入，存在重复，则执行后面的更新语句。

索引优化

区分度小的字段不适合建索引

比如性别，状态

长字符串，部分索引或倒序索引

分组必排序，排序就要用索引，否则文件排序

联合索引，区分度高的字段放前面

这样能提供性能

性能优化

高并发大数据的互联网业务，架构设计思路是“解放数据库 CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU 计算还是上移吧。

数据库连接池

binlog日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，MySQL 5.7.7 之后，默认值是 ROW。

日志格式通过 binlog-format 指定：

• STATMENT：基于 SQL 语句的复制，每一条会修改数据的sql语句会记录到 binlog 中
• ROW：基于行的复制
• MIXED：基于 STATMENT 和 ROW 两种模式的混合复制，比如一般的数据操作使用 row 格式保存，有些表结构的变更语句，使用 statement 来记录

主从复制延时

三个线程：

延时原理

• 主库 A 执行完成一个事务，写入 binlog，该时刻记为 T1。
• 传给从库 B，从库接受完这个 binlog 的时刻记为 T2。
• 从库 B 执行完这个事务，该时刻记为 T3。

那么所谓主从延迟，就是同一个事务，从库执行完成的时间和主库执行完成的时间之间的差值，即 T3-T1。

我们也可以通过在从库执行show slave status，返回结果会显示seconds_behind_master，表示当前从库延迟了多少秒。

seconds_behind_master 如何计算：

每一个事务的 binlog 都有一个时间字段，用于记录主库上写入的时间。 从库取出当前正在执行的事务的时间字段，跟当前系统的时间进行相减，得到的就是 seconds_behind_master，也就是前面所描述的 T3-T1。

原因

• 主库并发性地写，导致 T2-T1 时间变长
• 从库配置低，读取压力过大，导致 T3-T2 时间变长，所以主从机器最好相同配置
• 某个从库网络阻塞，波动。导致 T2-T1 变长
• 大事务的执行，因为只要事务提交了，主库才写 binlog

影响

解决手段

• 对实时性要求高的业务，查主库，避免查从库

本质性解决，缺点，丧失了主从架构的优越性。

• 接口层缓存业务单号

缓存业务单号+有效时间段，对提交的业务单号做重复提交校验。如果担心后期缓存的业务单号过多，可以给缓存的单号添加一个失效时间，失效时间大于主从复制的延时时长即可。

• 前端做防重复提交处理

简单，效果好，不影响服务器性能。缺点防君子不防小人。

系统命令

查看被锁的表

1

SHOW OPEN TABLES WHERE in_use > 0;

查看被锁的事务

1

SELECT * FROM information_schema.INNODB_LOCKS;

查看等待锁的事务

1

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

精读报错

字符集不同的字段比较出错

连表时遇到 2 个字符集不同的字段的比较，出错。

1

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

解决办法，在连表 ON 条件的字段后，加上同一字符集标识，例如 COLLATE utf8_unicode_ci。