MySQL八股文

基础语法

增删改查

# 添加
insert into `表`(`字段`) values ('值')
# 删除，truncate清全表
delete from `表` where <条件>
truncate table `表`
# 修改
update `表` set `字段` = '值' where <条件>
# 查询 后面可以使用很多的关键字辅助查询
# select - from - where - group by - having - order by
select `字段` from `表` where··· 

distinct

去重查询

select distinct `字段`, `字段` from `表`

concat

连接字段

select concat('我的名字是:',`name`) as 学生姓名 from `users`

limit

limit一般有两个参数，第一个表示返回数据的起始位，第二个表示要返回的行数。若只有一个参数，即省去了第一个参数，使用默认值0。

# 返回前5行数据（默认从第0行开始，返回后5行数据）
select * from `表` limit 5
# 从第3行开始，返回后3行数据，也就是4、5、6
select * from `表` limit 3，3

order by

排序，ASC升序（默认），DESC降序

select * from `表`
order by `字段` ASC / DESC

where

可使用的判断语句

操作符	说明
=	等于
<	小于
>	大于
!=	不等于
<=	小于等于
>=	大于等于
BETWEEN	在两个值之间
IS NULL	为 NULL 值

• and、or可用于来连接多个条件判断。优先处理and。

• in常用于匹配一组值，也可以跟一个查询语句，匹配子查询得到的一组值

  select * from `表` where `字段` in ('值'···)

• not相当于否定，可加在一般的判断语句前面表否定

通配符（模糊查询）

通配符用于过滤，但只能用于文本字段。

要使用like进行通配符匹配。就是使用两个通配符进行字符串拼接，然后进行模糊查询。

• % 可替换0或多个任意字符
• _ 只可替换1个任意字符

计算函数

函数对select后的查询字段操作即可。

函 数	说 明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

SELECT SUM(money) AS `sum` FROM `account`

group by，having

通过查询语句查找表的某个字段，然后按照字段进行分组展示。

首先可以通过where对原表数据进行筛选操作，然后使用group by对查询数据进行分组操作，分组后的数据若还需要筛选，则使用having操作。

SELECT `字段`, COUNT(*) AS 别名 FROM `表`
WHERE <对原表数据操作> 
GROUP BY `字段` 
HAVING <对分组数据操作>

子查询

就是查询语句套查询，使用子查询的结果来进行操作。

SELECT * FROM users
WHERE `name` IN (SELECT `password` FROM users)

子查询密码字段的结果，查询姓名，其中姓名和子查询的密码字段有相同值则筛选出来。

连接（join on）

内连接（inner join）

查询用户的邮箱与账户的金额，找到两个表id相同的数据进行返回。inner join来连接查询后，使用on进行条件筛选。

SELECT a.money, u.email
FROM account AS a INNER JOIN users AS u
ON a.id = u.id

自连接

表与表自身进行连接查询，相当于复制一个表，两个相同的表进行连接查询。

SELECT u1.email, u1.id
FROM users AS u1 INNER JOIN users AS u2
ON u1.id = u2.id

外连接（左外连、右外连、全外连）

左连接用户表与账户表，返回需要的字段，使用左右连接时，出来查询两个表符合的字段，还要展示左表或右表的剩余全部内容。没有字段则记为null，但一定要有。

SELECT u.id, u.name, u.password
FROM users AS u LEFT JOIN account AS a
ON a.id = u.id

组合查询

组合多个查询语句，但每个查询的返回格式必须一样，如列相同等。

SELECT `name` FROM users WHERE id = 1
UNION
SELECT `name`FROM users WHERE id = 2
UNION
SELECT `name` FROM users WHERE id = 3

三大范式

https://zhuanlan.zhihu.com/p/194270249

• 1NF

  数据库每一列（每个属性字段），都是不可分割的原子数据项。也就说字段内容可以二次拆分，那么就不满足1NF。

  比如学生课程关系表，不可以一个学生直接对应一个课程的信息集合，而是分多个条目进行记录。

• 2NF

  表中非主键字段应该和主键完全依赖，而不是部分依赖。

  当表中主键由多个字段组成时会出现该问题，部分字段只和其中一个主键有关联，比方说订单表主键由订单号 + 产品组成，一些订单具体数据信息应该单独与订单号记录，而不是混合在订单产品表中。

• 3NF

  非主键字段不能传递依赖于主键，也就是不能产生依赖传递关系。主键外的所有字段互不依赖。

  比如有ABC三个字段，A是主键，C是通过B与A主键进行关联，此时就违反了3NF。需要拆分成AB，BC两张表，消除传递依赖。

索引

索引底层数据结构

使用B树与B+树引擎的区别

• B树其所有节点都会存放 key 和 data ，但B+树只有叶子节点会存放 key 和 data ，其余内部节点存放key。也就是说B树所有节点都可以保存数据，索引很大，但B+树只有叶子节点保存数据，其内部节点相当于一个查找的索引。
• B+树的叶子节点通过链表相连，而B树叶子节点是单独的。
• B+树相当于B树检索更加稳定，B树可能没有走到叶子节点就查到数据了，而B+树数据都是存在与叶子节点上的，且叶子节点还可进行顺序查找，不用返回上一节点。B+树更便于区间查找和遍历。
• 为什么不用红黑树，因为B、B+树都是一个节点包含多个key的数据结构，它们的树高相比红黑树更低，而数据库磁盘IO次数就是由树高决定的，使用B+树结构IO效率更高。

索引类型

• 主键索引（唯一索引）

  数据表的主键就是主键索引。主键和主键索引一致，只能有一个，且主键不为空，不能重复。聚簇索引。

• 二级索引（辅助索引）

  InnoDB中非主键索引的就是二级索引，二级索引的叶子节点存储的数据是主键值，可通过二级索引确定主键的位置，也就是回表查询。

• 唯一索引

  唯一索引的属性列不可出现重复数据，但允许数据为null，一张表允许多个唯一索引。一般是为了保证数据列的唯一性，不是为了查找性能考虑。

• 普通索引

  普通索引唯一的作用就是为了快速查询数据，一张表允许多个普通索引，且允许数据重复和null。

• 前缀索引

  索引很长的字符列，导致索引很大且慢。前缀索引只适合字符串类型数据。前缀索引对文本的前几个字符创建索引，节省索引空间，提高索引效率。

• 哈希索引

  基于哈希表实现，为了精确匹配索引所有列的查询才有效。无法用于排序，不支持部分索引列的匹配查询，哈希索引始终使用索引列的全部内容计算哈希值。

• 全文索引

  全文索引主要为了检索大文本数据的关键字信息。

• 多列索引

  多个列组成的索引即联合索引。遵循最左匹配原则。

聚簇索引与非聚簇索引（InnoDB）

聚簇索引

一个表只有一个聚集索引。聚集索引就是索引结构与数据一起存放的索引。主键索引是聚集索引。B+树结构，叶子节点data存储数据。

• 优点：聚集索引查询速度很快，使用的是B+树的存储结构。
• 缺点：依赖有序数据，因为B+树数据有序，遇到无序数据我们还要先进行排序处理，而像UUID这种杂乱数据处理效率就更低了。

InnoDB默认将逐渐设置为聚簇索引，若没有定义主键，InnoDB会选择一个唯一的非空索引代替。若没有这种索引，InnoDB会隐式定义一个主键作为聚簇索引。

非聚簇索引

二级索引是非聚集索引。叶子节点存放主键值，一般是查询到主键值后对主键索引进行回表查询。叶子节点data存储指向数据的指针。

• 缺点：也依赖有序数据。且非聚集索引可能发生回表查询。

InnoDB与MyISAM

• 事务：InnoDB支持事务操作，MyISAM不支持
• 锁：InnoDB使用行级锁，MyISAM使用表级锁
• 索引：InnoDB是聚集表，MyISAM是非聚集表
• 外键：InnoDB支持外键，MyISAM不支持

最左匹配与覆盖索引

首先说说多列索引的最左匹配原则，一个多列索引（ a | b | c ），可以匹配三种查询条件，a、ab、abc，且where查询条件的先后不影响索引，只要有符合的字段就可以使用索引，因为MySQL的优化器会优化字段顺序走多列索引。而bc的查询条件是无法使用该多列索引的。

最左匹配原则会向右匹配直至遇到范围查询（>、<、between、like）。

然后说说覆盖索引，如果我们要查询的字段全部被一个索引所包含，那么该查询只需要去扫描索引树而无需回表。（回表查询就是查二级索引拿主键值，再去查主键索引拿数据）这里覆盖索引只有部分覆盖也是可以的。

所以有时会出现特殊情况，导致我们误以为最左匹配失效，例如我们给表中所有字段设置为多列索引，那么对该表的查询一定会走覆盖索引的操作，此时我们无论怎么写where条件语句，都不会全表查询，导致最左匹配看似失效了。

索引失效的场景

• 索引列字段使用函数

• 索引列涉及表达式计算

• 模糊查询，查询条件%在右侧可以走索引，%在左侧索引失效。**%在右侧的情况下，mysql根据实际情况进行优化，判断是全表扫描还是走索引，所以在模糊查询的时候最好不要用左%，且内容要写详细点。**

  1: 微信%
  2: 微信小程序%
  3: 微信公众号%
  上面三种模糊查询条件，可能情况1会被MySQL进行优化走全表扫描，这个涉及到优化算法。而详细一点的查询条件2、3就会走索引。
  我实践后的猜测是条件目标数据占比权重过高，如果当前模糊查询条件目标数据在整个表中占比过高，这个条件的索引应该会位于B+树最上面的几层，那么遍历走到的叶子节点会比较多，索性进行全表扫描不走索引

• 索引列属性是字符串，查询条件使用数字，不走索引。底层会进行优化进行格式转换，而数字整型格式条件使用字符串确可以。建议查询条件都用引号 '条件'进行修饰。

• 查询条件中有or，不一定走索引，此时要看MySQL优化器

• in的范围查询，条件目标过多时会被优化成全表扫描，具体问题具体分析

• 正则表达式

• MySQL优化器，当全表扫描效率更高时，不走索引。一般通过EXPLAIN相关参数进行分辨。

  prossible_keys: 此处为可能运用到的索引，优化器处理前
  key: 实际用到的索引，优化器处理后

特殊情况：覆盖索引，即使是失效场景仍可以命中，其实场景很多，索引还跟数据量有关，实际中我们需要使用 EXPLAIN 对自己的SQL性能进行评估。

EXPLAIN 相关参数：https://www.jianshu.com/p/8fab76bbf448

show index from `表名`

我们还可以通过以上语句来查看每张表索引的指标，其中 cardinality 代表 “索引基数” ，该参数涉及到优化器的索引选择，基数越大的索引性能越好。

事务

数据库事务

多个对数据库操作构成的一个逻辑整体，这就是数据库事务，这个逻辑整体的所有操作，要么全部成功，要么全部不成功。

ACID

• 原子性：原子性表示事务是最小的执行单位，这个整体只能同时完成操作，不能只完成部分操作。
• 一致性：执行事务前后，数据整体要保持一致，也就是把事务当作一个整体，比如进行转账操作，无论操作是否成功，账户之间的操作的金额整体是不变的。
• 隔离性：并发访问数据库时，一个事务的执行不受其他事务干扰，事务之间时相互隔离的。
• 持久性：一个事务执行提交后，它在数据库中的改变是永久的，数据库发生故障也不会影响数据。

并发事务带来的问题

多个事务并发运行，经常会有多个用户操作同一数据，并发是不可避的，但会导致一些问题，一般是因为并发破坏了事务的隔离性才导致以下问题的发生。

• 脏读：一个事务A正在访问数据并进行修改，但修改还未提交到数据库中，此时另一个事务B访问该数据并使用，由于这个数据还未提交，事务A后续可能对事务进行回滚操作，那么数据就没有进行修改，事务B读取到的数据就是脏数据。

• 丢失修改：事务A读取数据时，同时另一个事务B也访问该数据，此时事务A先修改数据，然后事务B也修改数据，最后事务B的修改操作会覆盖事务A的修改操作。

• 不可重复读：事务A对同一数据进行多次读取操作，在A进行多次读取之间，有事务B对该数据进行了修改操作，导致事务A同一数据的多次读取结果不一致。

• 幻读：事务A对多条数据进行读取操作，但中途事务B执行了插入或删除操作，随后事务A再次对多条数据进行读取，发现和前一次读取结果不一致。

事务的隔离级别

隔离级别依次递增

• 读取未提交：允许读取未提交的数据，所有并发问题都会出现。
• 读取已提交：允许事务读取已提交的数据，也就是说一个事务所做的修改在提交前对其他事务是不可见的。可以阻止脏读的发生。
• 可重复读：保证在同一个事务中，多次读取同一数据是一致的，除非事务本身进行修改操作。可阻止脏读与不可重复读。
• 可串行化：完全服从ACID的隔离级别。强制事务串行执行，多个事务间互不干扰，不会出现并发相关问题。该隔离级别需要进行加锁操作，以确保同时间只有一个事务执行，也就是串行执行。