【mysql】知识点总结

• 1. 范式
• 2. 关键字
• 3. 事物的ACID
• 4. 引擎
• 5. 并发的问题
• 6. 事物的隔离级别
• 7. 索引
  • 7.1. 1. MyISAM
  • 7.2. 2. innoDB
    • 7.2.1. 主索引和辅助索引：
    • 7.2.2. 索引覆盖
    • 7.2.3. 添加索引的方法
    • 7.2.4. 37. 优化mysql语句的

知识点

1. 范式

1NF：符合原子特性，不可再分。这个字段只能是一个值，不能再分为多个其他的字段了

2NF：消除非候选集对码的部分函数依赖（比如姓名依赖于学号，姓名依赖于身份证号，则姓名部分依赖于（学号、身份证号），y中存在一个真子集，x依赖于y的这个真子集，则x依赖于y）

消除方法：消除复合主键，增加单列关键字。

3NF：消除非候选集对码的传递函数依赖（系主任依赖于系名，系名依赖于学号，则系主任传递依赖于学号）

消除方法：将一个实体的信息放到一个表内实现。（拆分）

事物是什么：逻辑上一组操作

2. 关键字

• GROUP BY

• HAVING: where相同，但是where是在开始执行时检测数据，对原始数据进行过滤。having是对筛选出来的结果再次进行过滤。

  where是数据表中存在的，having是针对查询出来的。
  sql标准要求，having必须引用GROUP BY中的列或用于合计函数中的列。

3. 事物的ACID

事物的ACID怎么保证：

原子性：不能只执行一部分，要么全做完，要么就都不做： undo log来回滚。
一致性：执行事物前后，数据保持一致，比如汇款前后，总额不变。
隔离性：并发的事物之间是隔离的。（加锁）
持久性：redo log。数据库发生故障，也不应该让一个事物的改动失效。

4. 引擎

MyISAM: 不支持事物和行级锁。崩溃后无法安全修复。不支持外键

innoDB：提供事物支持，可以提交和回滚。默认都是行级锁。支持外键

5. 并发的问题

• 脏读： 一个事物对数据进行修改没提交，这个数据就被另一个事物使用了。

• 幻读：一个事物对数据进行读取时，另一个事物添加了几行数据，在第一个事物的后续操作中发现多了好多不存在的数据。

• 不可重复读： 一个事物对数据进行读取，第二个事物对这个数据进行了修改，导致第一个数据第二次读的数据和之前不一样。

6. 事物的隔离级别

• 读未提交：允许读取没提交的事物
• 读已提交：
• 可重复读（innoDB默认级别）：对一个数据的多次读取结果都是一样的，除非被自己修改
• 可串行化：所有事物依次执行

7. 索引

对于MyISAM和InnoDB这两种索引的实现策略：

7.1. 1. MyISAM

索引和数据分离。

叶子节点的data部分，存放数据的地址。所以每次查找，按照B+树找到data域的值，按照这个值作为地址，来取数据

优点：更新代价小，

缺点： 依赖于有序数据。需要二次查询（回表）

7.2. 2. innoDB

聚集索引：innoDB叶子结点的数据文件本身就是索引结构。将数据和索引放到一起存储

7.2.1. 主索引和辅助索引：

建表的时候，会自动根据主键构建一颗主索引树，叶子结点的key存储的是主键key，data存储的是对应的数据。

而构建其他索引的时候，会相应建立辅助索引树。叶子结点的key是索引key，data部分存储的是主键key。这样根据辅助索引搜索需要先找到主键，再在主索引树中找到相应的data的值。

InnoDB 需要节省存储空间. 一个表中可能有多个索引，因此会存储多个索引树。

优点：速度快，叶子结点是有序的。

缺点：依赖于有序的数据，不然插入太慢。更新代价大，因为索引多。

7.2.2. 索引覆盖

要查的值正好是索引的字段。

7.2.3. 添加索引的方法

ALTER TABLE `table_name` ADD [PRIMARY KEY/ UNIQUE/ INDEX index_name/ FULLTEXT] （`column`）

mysql面试题

1：MySQL 的逻辑架构了解吗？

第一层：客户端。图形化界面和接口之类的
二：service层。查询解析、分析、优化、缓存以及内置函数
三：innodb存储引擎。负责数据存储和读取

2：谈一谈 MySQL 的读写锁

读：共享锁：都能访问到数据，但是只能读不能修改。`select ... lock in share mode`

写：排他锁。update,delete,insert 都涉及 `select ... for update`

3：MySQL 的锁策略有什么？

首先回答不同引擎对应的是什么锁（表级和行级）

MyISAM: 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁。不会出现死锁。

MyISAM的调度：如果有读写请求同时进行的话，MYSQL将会优先执行写操作。因此可能造成查询阻塞。

---

innoDB: 行锁。行锁不影响读操作,只影响写操作。select不加锁，update, delete, insert自动给涉及到的数据加写锁。

行锁的实现方式：行锁是通过给索引上的索引项加锁来实现的

4：数据库死锁的情况以及如何解决？

当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁

现象1. 事务之间对资源访问顺序的交替。解决方法： 调整程序的逻辑

现象2. 共同修改同一记录。（用户A查询data后想修改data, 用户B此时想修改data。A对data的锁从查询的共享锁想上升到排他锁，B对A的锁是排他锁。但此时，B要等A释放共享锁，A由于B的排他锁，没办法释放共享锁）解决办法：统一使用乐观锁。或者统一使用悲观锁。

现象3. 可以直接使用超时时间，超时则自动释放。

避免死锁的办法：

1. 尽量按某种顺序访问表和行，按顺序申请锁
2. 大事物拆成小事物
3. 一个事物里尽量做到 一次性锁定所有资源
4. 降低隔离级别
5. 为表添加合理的索引。如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

5：事务是什么?

逻辑上的一组操作。

6：事务有什么特性？

ACID, 原子，一致，隔离，持久

7：MySQL 的隔离级别有哪些？

未提交读
提交读
可重复读
可串行化

8：MVCC 是什么？

多版本控制

MVCC是通过保存数据在某个时间点的快照

实现的依赖：

1. 隐藏字段 + readView + undoLog
InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。

SELECT：

    a.InnoDB只会查找版本 <= 当前事务版本的数据行。确保事务读取的行，要么是在事务开始前已经存在的.要么是事务自身插入或者修改过的.

    b. 行的删除版本要么未定义,要么 > 当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除

Delete: 

    为删除的每一行，保存当前系统的版本号。

9：谈一谈 InnoDB

支持：事物、行锁、外键。
行锁的执行方式：select不加锁，delete等加锁。实现方式是给索引上的索引项加锁来实现。
索引：数据文件本身就是索引文件。可以分成主索引和辅助索引...

10：谈一谈 MyISAM

不支持：
锁：select for lock share mode执行读锁，修改执行select for update
索引：索引和数据分离。叶子节点的data存放数据的地址

11：谈一谈 Memory

12：查询执行流程是什么？

1. 客户端发送查询给服务器。

2. 服务器检查缓存，没有命中则进入下一阶段

3. 服务端sql词法、语法分析（生成解析树、验证是否有错误关键字、顺序是否正确等）、预处理（根据mysql规则进一步检查解析树是否合法。比如数据是否存在。然后根据检查用户是否有该处理权限）、优化器生成对应执行计划

4. mysql根据优化器生成的执行计划，调用存储引擎API来执行查询

5. 返回结果

13：VARCHAR 和 CHAR 的区别？

char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足

varchar(M)每个值只占用刚好够用的字节再加上一个用来记录其长度的字节

14：DATETIME 和 TIMESTAMP 的区别？

timestamp会跟随设置的时区变化而变化，而datetime保存的是绝对值不会变化

timestamp可以自动更新：可以设置timestamp列自动以当前时间(CURRENT_TIMESTAMP)填充/更新

15：数据类型有哪些优化策略？

尽量使用类型简单的
尽量使用符合要求的容量小的数据类型
非NULL

16：索引有什么作用？

17：谈一谈 MySQL 的 B-Tree 索引

Mysql索引使用的数据结构主要有BTree（B+）索引（其他情况可以使用） 和 哈希索引（单条记录时可以使用）

18：了解 Hash 索引吗？

19：什么是自适应哈希索引？

经常访问的二级索引自动变成hash索引

20 ：什么是空间索引？

21：什么是全文索引？

22：什么是聚簇索引？

23：什么是覆盖索引？

要查找的值正好是索引数据。不用再回表了

24：你知道哪些索引使用原则？

25：索引失效的情况有哪些？

26：如何定位低效 SQL？

27：SHOW PROFILE 的作用？

28：trace 是干什么的？

29：EXPLAIN 的字段有哪些，具有什么含义？

30：有哪些优化 SQL 的策略？

31：MySQL 主从复制的作用？

32: 悲观锁和乐观锁

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。实现方式：依靠数据库的锁机制for update

乐观锁：本身是不加锁的。只是在更新的时候判断其他线程是否更新了这个数据。

假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。当某个线程查询数据时，将该数据的版本号一起查出来；当该线程更新数据时，判断当前版本号与之前读取的版本号是否一致，如果一致才进行操作。

乐观锁如何检查是否违反数据完整性：通过版本号或时间戳来表示。

33. 索引建立原则

    1 最左前缀匹配原则
    mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a = 1 and b = 2 and c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，d是用不到索引的

    2 =和in可以乱序
    比如a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
    3 索引列不能参与计算，保持列“干净”

    优化方法：

    匹配全值: 对索引中所有列都指定具体

    其他优化方法

34. 分布式锁

Mysql的

乐观锁：维持表的版本号，在提交的时候再判断是否有冲突。有冲突则操作失败。

悲观锁：假定回发生冲突，屏蔽一切可能违反数据完整性的操作。实现方式：依靠数据库的锁机制for update。

Redis的

使用setNX，增加一个key, 只有在key不存在时，才能set成功。

分布式锁的安全问题

GC的stop the world: GC时间超过锁的超时时间。这样当client1 获取锁之后，经历一个长时间gc,锁自动释放，然后client2获得了这个锁。等到client1从阻塞中醒来，释放了client2的锁。（解决办法：可以考虑乐观锁）

长时间的网络IO：锁的过期时间应该大于所有网络调用的时间之和。

日志

redo log: 物理日志，在某个数据页上做了什么修改。崩溃时重新执行。刷盘时机与存储方式？（可以在执行时不断写入，也可以提交时再写入）

binlog: 逻辑日志，只要有数据的更新就记录下来。用来同步数据，保证一致性。除此之外还能进行数据备份
事物执行时，先写到binlog cache，提交时，再把binlog cache写到binlog文件中。一个事物的binlog不能拆开

undo log: 原子性保证：异常发生时需要对已经执行的操作进行回滚。所有进行的修改都记录到日志中。

36. order dy与索引

order by有两种实现方法：1. 利用有序索引实现 2. 算出结果再实现

order by在where语句中，利用索引无需排序。desc倒序

执行顺序：select -> from -> join -> on -> where -> group by -> having->order by（分好组后找到固定的分组）

7.2.4. 37. 优化mysql语句的

1. 注意书写时的某些顺序

多表关联的顺序：mysql优化器会自己优化：当然，优化器也会考虑数据是否在缓存中，如果数据不在磁盘中，读取数据后还需要将二进制数据解析成数据行。

from 后面的表关联从右往左解析：数据量小的表放到右边关联

优先选择结果集最小的那张表作为驱动表和其他表链接。

怎么选择结果最小的表？ 根据where中的查询条件估算：如果where中用到了索引（走索引了），那可根据索引估算。 例如c.id>15， 直到最大id为20，那就等于5个，b.class = '1001'，直到class的分布范围是10%，那就能估算出一共多少个（10/10 =10个）

select a.id, b.name, c.address
from A a
    inner join B b on a.bid = b.id
    inner join C c on a.cid = c.id
    inner join D d on a.did = c.id
where a.id in (1,2,3,4,5) and b.class = '1001' and c.id>15 and d.age < 30;

select a.id, b.name, c.address
from A a, B b, C c, D d
where a.bid = b.id
    and c on a.cid = c.id
    and b on a.cid = d.id
    and a.id in (1,2,3,4,5,6)
    and c.id > 15
    and d.age < 30;

where条件解析从下往上。筛选出小量数据的条件放到where语句的最左边

2. 减少回表

最好在查询里只包含索引列，而不用select*这种
并且，尽量使用索引：

尽量使用索引的方法：

1. 字段要独立出现：比如下面两条SQL语句在语义上相同，但是第一条会使用主键索引而第二条不会。
select * from user where id = 20-1; 
select * from user where id+1 = 20;

2. 对于建立的符合索引，注意最左匹配原则：
(first_name,last_name); 使用first_name可以走索引，但是last_name不走索引

3. 对join语句匹配关系（on）涉及的字段最好能是索引

4. like查询，不能以通配符开头
select * from article where title like '%mysql%'; // 不用索引
select * from article where title like 'mysql%'; // 可用索引

3. 慢查询解决办法

慢查询是满查询日志，记录所有的响应时间超过阈值的语句。（long_query_time = 10 默认)
需要手动设置启动慢查询日志，启动的话回对性能带来一定的影响

explain可以获得mysql中sql语句中的执行计划。语句是否使用了关联查询，是否使用了索引等。
在查询语句前加explain

-- explain出来的参数 --
table:
type:
all: full table scan
index
range:索引范围扫描
rows：检查出来的返回请求数据的行数

4. explain的使用

在查询语句前加explain

-- explain出来的参数 --
table:
type:
all: full table scan
index
range:索引范围扫描
rows：检查出来的返回请求数据的行数

5. 数据类型的选择

尽可能选择小的数据类型和指定短的长度。尽可能使用 not null

varchar和char：varchar变长，varchar用一个字节表示这个字符串的目前长度。在char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足。

varchar和char的区别：

• 存储空间规则不同：CHAR平均占用的空间多于VARCHAR，因此使用varchar需要最小化处理数据行的总量和磁盘io有好处。
• 存储最大容量不同：对 char 来说，最多能存放的字符个数 255，和编码无关。 而 varchar 呢，最多能存放 65532个字符。

对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，因此使用varchar更好

6. 可以在设计时预留一些字段防止后续需要改进

38. Redis 缓存雪崩、击穿、穿透

缓存雪崩：redis key大面积同时失效，查询直接全部落库，太大的QPS直接打DB去mysql会直接打挂（但是可以实现分库和分表）

解决办法：1. 把每个Key的失效时间都加个随机值
2. 如果Redis是集群部署，将热点数据均匀分布在不同的Redis库中 3. 设置热点数据永远不过期，有更新操作就更新缓存就好了

缓存穿透：指缓存和数据库中都没有的数据，而用户不断发起请求

解决办法：1. 接口层增加校验，比如用户鉴权校验，参数做校验，不合法的参数直接代码Return，比如：id 做基础校验，id <=0的直接拦截等。
（小点的单机系统，基本上用postman就能搞死，比如我自己买的阿里云服务）/数据库id都是大于0的，我一直用小于0的参数去请求你，每次都能绕开Redis直接打到数据库，数据库也查不到，每次都这样，并发高点就容易崩掉了。

2. 将对应Key的Value对写为null、位置错误、稍后重试这样的值具体取啥问产品

3. 网关层Nginx本渣我也记得有配置项，可以让运维大大对单个IP每秒访问次数超出阈值的IP都拉黑。

4. Redis还有一个高级用法布隆过滤器（Bloom Filter）这个也能很好的防止缓存穿透。不存在你return就好了，存在你就去查了DB刷新KV再return。

缓存击穿： 一个Key非常热点，在不停的扛着大并发，大并发集中对这一个点进行访问，当这个Key在失效的瞬间，持续的大并发就穿破缓存，直接请求数据库，