mysql 常见问题

Mysql FAQ

主键和UNIQUE约束都能保证某个列或者列组合的唯一性,但是:

  • 一张表中只能定义一个主键,却可以定义多个UNIQUE约束!
  • 主键列不允许存放NULL,而声明了UNIQUE属性的列可以存放NULL,而且NULL可以重复地出现在多条记录中。
1
delimiter EOF  # 将结束符改为 EOF

https://cdn.xiaobinqt.cn/xiaobinqt.io/20220419/fdd2e38117284a7f8b46ba3af60c26c9.png?imageView2/0/q/75|watermark/2/text/eGlhb2JpbnF0/font/dmlqYXlh/fontsize/1000/fill/IzVDNUI1Qg==/dissolve/52/gravity/SouthEast/dx/15/dy/15
修改结束符

由☝️图可知,将默认的结束符从 ; 改为 EOF

如果A表中的某个列或者某些列依赖与B表中的某个列或者某些列,那么就称A表为子表,B表为父表。子表和父表可以使用外键来关联起来。

父表中被子表依赖的列或者列组合必须建立索引,如果该列或者列组合已经是主键或者有UNIQUE属性,那么也就被默认建立了索引。

定义外键的语法:

1
CONSTRAINT [外键名称] FOREIGN KEY(1, 2, ...) REFERENCES 父表名(父列1, 父列2, ...);
1
2
3
4
5
6
7
8
CREATE TABLE student_score
(
    number  INT, -- 学号
    subject VARCHAR(30),
    score   TINYINT,
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY (number) REFERENCES student_info (number)
);

☝️ 如上,在对student_score表插入数据的时候,MySQL 都会检查插入的学号是否能在student_info 表中找到,如果找不到则会报错,因为student_score表中的number 列依赖于student_info表的number列,也就是,如果没有这个学生,何来成绩?

对于无符号整数类型的列,可以在查询数据的时候让数字左边补 0,如果想实现这个效果需要给该列加一个ZEROFILL属性:

1
2
3
4
5
CREATE TABLE zerofill_table
(
    i1 INT(10) UNSIGNED ZEROFILL,
    i2 INT UNSIGNED
);

INT后边的(5),这个 5 就是显示宽度,默认是10,也就是 INTINT(10) 效果是一样的。

  • 该列必须是整数类型
  • 该列必须有 UNSIGNED ZEROFILL的属性
  • 该列的实际值的位数必须小于显示宽度
  • 在创建表的时候,如果声明了ZEROFILL属性的列没有声明UNSIGNED属性,MySQL 会为该列自动生成UNSIGNED属性
  • 显示宽度并不会影响实际类型的实际存储空间
  • 对于没有声明ZEROFILL属性的列,显示宽度没有任何作用,只有在查询声明了ZEROFILL属性的列时,显示宽度才会起作用,否则可以忽略显示宽度这个东西的存在。

从 0 开始计数,第1条记录在 MYSQL 中是第 0 条。

limit 和 offset 都可以用来限制查询条数,一般用做分页。

  • 当 limit 后面跟一个参数的时候,该参数表示要取的数据的数量
1
2
select*
from user limit 3 

表示直接取前三条数据。

  • 当 limit 后面跟两个参数的时候,第一个数表示开始行,后一位表示要取的数量,例如
1
2
select *
from user limit 1,3;

从 0 行开始计算,取第 1 - 3 条数据,也就是取 1,2,3 三条数据。

  • 当 limit 和 offset 组合使用的时候,limit 后面只能有一个参数,表示要取的的数量,offset 表示开始行。
1
2
3
select *
from user limit 3
offset 1;

从 0 行开始计算,取第 1 - 3 条数据,也就是取 1,2,3 三条数据。

名称 调用示例 示例结果 描述
LEFT LEFT('abc123', 3) abc 给定字符串从左边取指定长度的子串
RIGHT RIGHT('abc123', 3) 123 给定字符串从右边取指定长度的子串
LENGTH LENGTH('abc') 3 给定字符串的长度
LOWER LOWER('ABC') abc 给定字符串的小写格式
UPPER UPPER('abc') ABC 给定字符串的大写格式
LTRIM LTRIM(' abc') abc 给定字符串左边空格去除后的格式
RTRIM RTRIM('abc ') abc 给定字符串右边空格去除后的格式
SUBSTRING SUBSTRING('abc123', 2, 3) bc1 给定字符串从指定位置截取指定长度的子串
CONCAT CONCAT('abc', '123', 'xyz') abc123xyz 将给定的各个字符串拼接成一个新字符串
名称 调用示例 示例结果 描述
NOW NOW() 2019-08-16 17:10:43 返回当前日期和时间
CURDATE CURDATE() 2019-08-16 返回当前日期
CURTIME CURTIME() 17:10:43 返回当前时间
DATE DATE('2019-08-16 17:10:43') 2019-08-16 将给定日期和时间值的日期提取出来
DATE_ADD DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY) 2019-08-18 17:10:43 将给定的日期和时间值添加指定的时间间隔
DATE_SUB DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY) 2019-08-14 17:10:43 将给定的日期和时间值减去指定的时间间隔
DATEDIFF DATEDIFF('2019-08-16', '2019-08-17') -1 返回两个日期之间的天数(负数代表前一个参数代表的日期比较小)
DATE_FORMAT DATE_FORMAT(NOW(),'%m-%d-%Y') 08-16-2019 用给定的格式显示日期和时间

常见时间单位

时间单位 描述
MICROSECOND 毫秒
SECOND
MINUTE 分钟
HOUR 小时
DAY
WEEK 星期
MONTH
QUARTER 季度
YEAR
名称 调用示例 示例结果 描述
ABS ABS(-1) 1 取绝对值
Pi PI() 3.141593 返回圆周率
COS COS(PI()) -1 返回一个角度的余弦
EXP EXP(1) 2.718281828459045 返回e的指定次方
MOD MOD(5,2) 1 返回除法的余数
RAND RAND() 0.7537623539136372 返回一个随机数
SIN SIN(PI()/2) 1 返回一个角度的正弦
SQRT SQRT(9) 3 返回一个数的平方根
TAN TAN(0) 0 返回一个角度的正切

COUNT函数使用来统计行数的,有下边两种使用方式:

  • COUNT(*):对表中行的数目进行计数,不管列的值是不是NULL。
  • COUNT(列名):对特定的列进行计数,会忽略掉该列为NULL的行。

两者的区别是会不会忽略统计列的值为NULL的行

where 竟然可以这么写😇

1
2
3
select *
from edge
where (ip, mode) = ('192.168.50.101', 2);

in 竟然可以这么写😂

1
2
3
select *
from edge
where (ip, mode) in (select '192.168.50.101', 2);
1
2
3
4
5
6
7
8
IF 表达式 THEN
    处理语句列表
[ELSEIF 表达式 THEN
    处理语句列表]
... # 这里可以有多个ELSEIF语句
[ELSE
    处理语句列表]
END IF;
1
2
3
4
5
6
7
8
CASE WHEN 表达式 THEN 处理语句
    else 表达式 end 
  
## 或者  
CASE when 表达式 then 处理语句
    when 表达式 then 处理语句
    ... 可以与多个 when 表达式 then 处理语句
   END

示例:

1
2
3
4
5
6
7
select *, 
	CASE WHEN name='大彬' THEN '角色1' 
    else '角色2' end as processed_name ,
    case when status = 1 then '已处理'
    when status = 0 then '未处理'
    when status = 2 then '待处理' end as processed_status
    from user;
1
2
3
WHILE 表达式 DO
    处理语句列表
END WHILE;
1
2
3
REPEAT
    处理语句列表
UNTIL 表达式 END REPEAT;
1
2
3
LOOP
    处理语句列表
END LOOP;

在使用 LOOP 时可以使用RETURN语句直接让函数结束就可以达到停止循环的效果,也可以使用LEAVE语句,不过使用LEAVE 时,需要先在LOOP语句前边放置一个所谓的标记。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    flag:LOOP
        IF i > n THEN
            LEAVE flag;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP flag;
    RETURN result;
END

☝️示例中,在LOOP语句前加了一个flag:,相当于为这个循环打了一个名叫flag的标记,然后在对应的END LOOP 语句后边也把这个标记名flag 给写上了。在存储函数的函数体中使用LEAVE flag语句来结束flag这个标记所代表的循环。

标记主要是为了可以跳到指定的语句中

对于主键或者有唯一性约束 的列或列组合来说,新插入的记录如果和表中已存在的记录重复的话,我们可以选择的策略不仅仅是忽略(INSERT IGNORE )该条记录的插入,也可以选择更新这条重复的旧记录。

1
2
3
4
5
6
7
8
9
CREATE TABLE `t`
(
    `idt`   int(11) NOT NULL AUTO_INCREMENT,
    `phone` char(11)    DEFAULT NULL,
    `name`  varchar(45) DEFAULT NULL,
    PRIMARY KEY (`idt`),
    UNIQUE KEY `idt_UNIQUE` (`idt`),
    UNIQUE KEY `phone_UNIQUE` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

如上表,idt 是唯一主键,phone 是 UNIQUE 唯一约束。

https://cdn.xiaobinqt.cn/xiaobinqt.io/20220419/1ba316643e874fcb9e90dcaa9b333f60.png?imageView2/0/q/75|watermark/2/text/eGlhb2JpbnF0/font/dmlqYXlh/fontsize/1000/fill/IzVDNUI1Qg==/dissolve/52/gravity/SouthEast/dx/15/dy/15
图1

表里有条记录 phone = 15212124125name = '吴彦祖',现在再添加一条记录,phone 跟 name = '吴彦祖' 是一样的,但是 name='宋江'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
INSERT INTO t (phone, name)
VALUES ('15212124125', '宋江') ON DUPLICATE KEY
UPDATE name = '宋江';

-- 对于批量插入可以这么写,`VALUES(列名)`的形式来引用待插入记录中对应列的值
INSERT INTO t (phone, name)
VALUES ('15212124125', '宋江'),
       ('15212124126', '李逵') ON DUPLICATE KEY
UPDATE name =
VALUES (`name`);

结果:

https://cdn.xiaobinqt.cn/xiaobinqt.io/20220419/6998e509ee9f4c3eacb72f9c5b42080b.png?imageView2/0/q/75|watermark/2/text/eGlhb2JpbnF0/font/dmlqYXlh/fontsize/1000/fill/IzVDNUI1Qg==/dissolve/52/gravity/SouthEast/dx/15/dy/15
图2

由结果可知,phone 电话的值没有改变,但是 name 被修改成了宋江。

也就是说,如果 t 表中已经存在 phone 的列值为 15212124125 的记录(因为 phone列具有UNIQUE 约束),那么就把该记录的 name列更新为'宋江'

对于那些是主键或者具有UNIQUE约束的列或者列组合来说,如果表中已存在的记录中有与待插入记录在这些列或者列组合上重复的值,我们可以使用VALUES(列名)的形式来引用待插入记录中对应列的值

设置单个变量可以使用 SET 关键字。

https://cdn.xiaobinqt.cn/xiaobinqt.io/20220419/7d7a7bc57efa43e1ba30412b76c53917.png?imageView2/0/q/75|watermark/2/text/eGlhb2JpbnF0/font/dmlqYXlh/fontsize/1000/fill/IzVDNUI1Qg==/dissolve/52/gravity/SouthEast/dx/15/dy/15
设置单个变量

设置多个变量可以使用 INTO 关键字。

https://cdn.xiaobinqt.cn/xiaobinqt.io/20220419/9c894c060ac34628abd1b9071d4e7e27.png?imageView2/0/q/75|watermark/2/text/eGlhb2JpbnF0/font/dmlqYXlh/fontsize/1000/fill/IzVDNUI1Qg==/dissolve/52/gravity/SouthEast/dx/15/dy/15
设置多个变量

  • innodb 支持事务,而 myisam 不支持事务。
  • innodb 支持外键,而 myisam 不支持外键。
  • innodb 默认表锁,使用索引检索条件时是行锁,而myisam是表锁(每次更新增加删除都会锁住表)。
  • innodb 和 myisam 的索引都是基于b+树,但他们具体实现不一样,innodb 的 b+ 树的叶子节点是存放数据的,myisam 的 b+ 树的叶子节点是存放指针的。
  • innodb 是聚簇索引,必须要有主键,一定会基于主键查询,但是辅助索引就会查询两次,myisam是非聚簇索引,索引和数据是分离的,索引里保存的是数据地址的指针,主键索引和辅助索引是分开的。
  • innodb 不存储表的行数,所以select count(*)的时候会全表查询,而 myisam 会存放表的行数,select count(*)的时候会查的很快。

总结:mysql 默认使用 innodb,如果要用事务和外键就使用 innodb,如果这张表只用来查询,可以用 myisam。如果更新删除增加频繁就使用 innodb。

指索引中的索引节点值不允许重复,一般配合唯一约束使用。

主键索引是一种特殊的唯一索引,和普通唯一索引的区别在于不允许有空值。

通过KEYINDEX关键字创建的索引就是这个类型,没什么限制,就是单纯的可以让查询快一点。

在 5.7 版本之前,只有 MyISAM 引擎支持。

全文索引只能创建在CHARVARCHARTEXT等这些文本类型字段上,而且使用全文索引查询时,条件字符数量必须大于 3 才生效。如果想要创建出的全文索引支持中文,需要在最后指定解析器:with parser ngram

具体的使用可以参看 全文索引的创建与使用

在聚簇索引中,索引数据和表数据在磁盘中的位置是一起的。

一张表中只能存在一个聚簇索引,一般都会选用主键作为聚簇索引。

一般聚簇索引要求索引必须是非空唯一索引才行。

在非聚簇索引中,索引节点和表数据之间用物理地址的方式维护两者的联系。

参看 索引查询时的回表问题

  • 经常频繁用作查询条件的字段应酌情考虑为其创建索引。
  • 表的主外键或连表字段,必须建立索引,因为能很大程度提升连表查询的性能。
  • 建立索引的字段,一般值的区分性要足够高,这样才能提高索引的检索效率。
  • 建立索引的字段,值不应该过长,如果较长的字段要建立索引,可以选择前缀索引。
  • 建立联合索引,应当遵循最左前缀原则,将多个字段之间按优先级顺序组合。
  • 经常根据范围取值、排序、分组的字段应建立索引,因为索引有序,能加快排序时间。
  • 对于唯一索引,如果确认不会利用该字段排序,那可以将结构改为Hash结构。
  • 尽量使用联合索引代替单值索引,联合索引比多个单值索引查询效率要高。
  • 值经常会增删改的字段,不合适建立索引,因为每次改变后需维护索引结构。
  • 一个字段存在大量的重复值时,不适合建立索引,比如之前举例的性别字段。
  • 索引不能参与计算,因此经常带函数查询的字段,并不适合建立索引。
  • 一张表中的索引数量并不是越多越好,一般控制在3,最多不能超过5
  • 建立联合索引时,一定要考虑优先级,查询频率最高的字段应当放首位。
  • 当表的数据较少,不应当建立索引,因为数据量不大时,维护索引反而开销更大。
  • 索引的字段值无序时,不推荐建立索引,因为会造成页分裂,尤其是主键索引。
  • 查询 SQL 中尽量不要使用OR关键字,可以使用多 SQL 或子查询代替。
  • 模糊查询尽量不要以%开头,如果实在要实现这个功能可以建立全文索引。
  • ⚠️编写 SQL 时一定要注意字段的数据类型,否则 MySQL 的隐式转换会导致索引失效。
  • 一定不要在编写 SQL 时让索引字段执行计算工作,尽量将计算工作放在客户端中完成。
  • 对于索引字段尽量不要使用计算类函数,一定要使用时请记得将函数计算放在=后面。
  • 多条件的查询 SQL 一定要使用联合索引中的第一个字段,否则会打破最左匹配原则。
  • 对于需要对比多个字段的查询业务时,可以拆分为连表查询,使用临时表代替。
  • 在 SQL 中不要使用反范围性的查询条件,大部分反范围性、不等性查询都会让索引失效。

哪些情况下会导致索引失效,参看 索引失效的一些场景

要查询的列,在使用的索引中已经包含,被所使用的索引覆盖,这种情况称之为索引覆盖。

参看 索引覆盖,索引下推,MRR

为什么 mysql 选择 B+Tree 作为索引的数据结构。参看索引为何要选择B+Tree?

参看 执行分析工具 - ExPlain

  • A/Atomicity:原子性

  • C/Consistency:一致性

  • I/Isolation:独立性/隔离性

  • D/Durability:持久性

  • 原子性要求事务中所有操作要么全部成功,要么全部失败,这点是基于undo-log来实现的,因为在该日志中会生成相应的反 SQL,执行失败时会利用该日志来回滚所有写入操作。

  • 持久性要求的是所有 SQL 写入的数据都必须能落入磁盘存储,确保数据不会丢失,这点则是基于redo-log实现的。

  • 隔离性的要求是一个事务不会受到另一个事务的影响,对于这点则是通过锁机制和 MVCC 机制实现的,只不过 MySQL 屏蔽了加锁和 MVCC 的细节。

  • 一致性要求数据库的整体数据变化,只能从一个一致性状态变为另一个一致性状态,其实前面的原子性、持久性、隔离性都是为了确保这点而存在的。

  • undo-log:主要记录 SQL 的撤销日志,比如目前是insert语句,就记录一条delete日志。

  • redo-log:记录当前 SQL 归属事务的状态,以及记录修改内容和修改页的位置。

  • bin-log:记录每条 SQL 操作日志,只要是用于数据的主从复制与数据恢复/备份。

redo log 保证的是数据库的crash-safe能力。采用的策略就是常说的”两阶段提交“。具体可以参看 Redo-log的两阶段提交

一条 update 的 SQL 语句是按照这样的流程来执行的:

  1. 先写一条 redo-prepare 状态的日志,再记录 undo-log 日志。
  2. 执行 SQL 语句,修改缓冲页中的数据,修改完成后表示执行成功。
  3. 记录 bin-log 日志。
  4. 将 redo-prepare 状态的日志改为 redo-commit 状态。

默认情况下,只有当第三步执行完成后才会提交事务。

一旦发生系统故障(不管是宕机、断电、重启等等),都可以配套使用 redo log 与 binlog 做数据修复。

binlog状态 redo log 状态 对策
有记录 commit 事务已经正常完成
有记录 prepare 在binlog写完、提交事务之前发生故障。此时数据完整。恢复策略:提交事务
无记录 prepare 在binglog写完之前发生故障。恢复策略:回滚
无记录 无记录 在写redo log之前发生故障。恢复策略:回滚

①生效范围不同,Redo-log 是 InnoDB 专享的,Bin-log 是所有引擎通用的。

②写入方式不同,Redo-log 是用两个文件循环写,而 Bin-log 是不断创建新文件追加写。

③文件格式不同,Redo-log 中记录的都是变更后的数据,而 Bin-log 会记录变更 SQL 语句。

④使用场景不同,Redo-log 主要实现故障情况下的数据恢复,Bin-log 则用于数据灾备、同步。

参考 脏读、幻读、不可重复读问题

脏读:指一个事务读到了其他事务还未提交的数据,也就是当前事务读到的数据,由于还未提交,因此有可能会回滚。

幻读:另外一个事务在第一个事务要处理的目标数据范围之内新增了数据,然后先于第一个事务提交造成的问题。

幻读是对自己来说的,比如,事务 A 在对表中多行数据进行修改,将性别「男、女」改为「0、1」,此时事务 B 又插入了一条性别为男的数据, 当事务 A 提交后,再次查询表时,会发现表中依旧存在一条性别为男的数据。

不可重复读:指在一个事务中,多次读取同一数据,先后读取到的数据不一致。

事务 A 执行下单业务时,因为添加物流信息的时候出错了,导致整个事务回滚,事务回滚完成后,事务 A 就结束了。但事务 B 却并未结束,在事务 B 中,在事务 A 执行时读取了一次剩余库存,然后在事务 A 回滚后又读取了一次剩余库存,仔细想想:B 事务第一次读到的剩余库存是扣减之后的,第二次读到 的剩余库存则是扣减之前的(因为 A 事务回滚又加回去了),导致两次读取的数据不一致,这就是不可重复读。

可以参考 RU(Read Uncommitted)读未提交级别的实现

  1. Read uncommitted/RU:读未提交,处于该隔离级别的数据库,脏读、不可重复读、幻读问题都有可能发生。

  2. Read committed/RC:读已提交,处于该隔离级别的数据库,解决了脏读问题,不可重复读、幻读问题依旧存在。

  3. Repeatable read/RR:可重复读,处于该隔离级别的数据库,解决了脏读、不可重复读问题,幻读问题依旧存在。

  4. Serializable:序列化/串行化,处于该隔离级别的数据库,解决了脏读、不可重复读、幻读问题都不存在。

上述四个级别,越靠后并发控制度越高,也就是在多线程并发操作的情况下,出现问题的几率越小,但对应的也性能越差,MySQL 的事务隔离级别, 默认为第三级别:Repeatable read可重复读。

共享锁又被称之为 S 锁,它是Shared Lock的简称,不同事务之间不会排斥,可以同时获取锁并执行,这里所谓的不会排斥,仅仅只是指不会排斥其他事务来读数据,但其他事务尝试写数据时,就会出现排斥性。

排他锁也被称之为独占锁,当一个线程获取到独占锁后,会排斥其他线程,如若其他线程也想对共享资源/同一数据进行操作,必须等到当前线程释放锁并竞争到锁资源才行。

排他锁并不是只能用于写操作,对于一个读操作,也可以手动的指定为获取排他锁,当一个事务在读数据时,获取了排他锁,那当其他事务来读、写同一数据时,都会被排斥。

参看 间隙锁(Gap Lock)

关于死锁问题,可以参考 死锁检测算法 - wait-for graph

  • 合理的设计索引结构,使业务 SQL 在执行时能通过索引定位到具体的几行数据,减小锁的粒度。
  • 业务允许的情况下,也可以将隔离级别调低,因为级别越低,锁的限制会越小。
  • 调整业务 SQL 的逻辑顺序,较大、耗时较长的事务尽量放在特定时间去执行(如凌晨对账等)。
  • 尽可能的拆分业务的粒度,一个业务组成的大事务,尽量拆成多个小事务,缩短一个事务持有锁的时间。
  • 如果没有强制性要求,就尽量不要手动在事务中获取排他锁,否则会造成一些不必要的锁出现,增大产生死锁的几率。

简单来说,就是在业务允许的情况下,尽量缩短一个事务持有锁的时间、减小锁的粒度以及锁的数量。

数据库事务的隔离级别,由低到高依次为 Read uncommitted 、Read committed、Repeatable read 、Serializable。