Skip to content

Latest commit

 

History

History
354 lines (172 loc) · 9.47 KB

数据库八股(1).md

File metadata and controls

354 lines (172 loc) · 9.47 KB
Raw

数据库

范式

第一范式：原子性：不可再分

第二范式：每列都和主键相关

第三范式：与主键直接相关

char：剩余空间会浪费，不分，255

varchar：剩余空间可以分给别人，65535

join：2标均符合条件的行

left/right

select：建立连接， 验证用户， 保存权限， 查询缓存， sql解析（语法语义），执行sql

drop/truncate：不可rollback。整表/整行

delete：可以rollback

死锁：互斥，不可抢占，循环等待，保持等待

mysql死锁：update扫整表，整表行加锁

防锁：

1.检测超时，超时就回滚

2.开启主动死锁检测

事务：

事务是数据库并发控制的基本单位

ACID

automicity原子性 不可分undo log

consistency一致性 从一致到一致通过其他三个aid来保证

isolation隔离性：数据提交前不可见。mvcc或锁

durability持久性 系统崩溃修改数据也不丢失redo log

并发事务：

幻读：幻读是针对数据插入操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，让用户感觉感觉出现了幻觉，这就叫幻读。

脏读：读到未提交数据

不可重复读：不同时刻读到不同数据

可重复读：从一而终均相同

mysql事务隔离级别

读未提交：未提交即可见：幻读，脏读，不可重复读

读提交：提交后才可见：幻读，不可重复读。实现：每次提交都生成一个新的readview

可重复读：事务执行时看到的数据不变：幻读

串行化：有读写锁：均不发生

读未提交，每次读到的都是最新的数据，也不管数据行所在的事务是否提交。实现也很简单，只需要每次都读 undo 日志版本链的链表头（最新的快照）就行了。

与读未提交不同，读提交和可重复读隔离级别都是基于 MVCC 的 read view 实现的，反过来说, MVCC 也只会出现在这两个隔离级别里。

读已提交隔离级别，每次执行普通 select，都会重新生成一个新的 read view，然后拿着这个最新的 read view到某行数据的版本链上挨个遍历，找到第一个合适的数据。这样就能做到每次都读到其他事务最新已提交的数据。

可重复读隔离级别下的事务只会在第一次执行普通 select时生成read view，后续不管执行几次普通 select，都会复用这个 read view。这样就能保持每次读的时候都是在同一标准下进行读取，那读到的数据也会是一样的。

串行化目的就是让并发事务看起来就像单线程执行一样，那实现也很简单，和读未提交隔离级别一样，串行化隔离界别下事务只读 undo 日志链的链表头，也就是最新版本的快照，并且就算是普通 select，也会在版本链的最新快照上加入读锁。这样其他事务想写，也得等这个读锁释放掉才行。所有对这行数据进行操作的事务，都老老实实地阻塞等待加锁，一个接一个进行处理，从效果上看就跟单线程处理一样。

MVCC：多版本并发控制

#当前读（锁）和快照读（mvcc）可以解决幻读

当前读：读最新的

一致性读：

锁定读

快照读：

利用mvcc解决幻读

索引

b+树:lögn

非叶子：存索引

叶子：叶子内按主键顺序存储数据指针，叶子间链表

b树:logd_N

全存关键字+索引

b/b+对比：

单点：b>b+

增删：b+>b

范围查询：b+>b>hash

索引类型

单值索引：单个列

  • 建表时，加上 key(列名) 指定
  • 单独创建，create index 索引名 on 表名(列名)
  • 单独创建，alter table 表名 add index 索引名(列名)

唯一索引：索引列值唯一

  • 建表时，加上 unique(列名) 指定
  • 单独创建，create unique index idx 表名(列名) on 表名(列名)
  • 单独创建，alter table 表名 add unique 索引名(列名)

主键索引：会自动建立索引，innodb为聚簇索引

  • 建表时，加上 primary key(列名) 指定

复合索引：包含多个列

  • 建表时，加上 key(列名列表) 指定
  • 单独创建，create index 索引名 on 表名(列名列表)
  • 单独创建，alter table 表名 add index 索引名(列名列表)

前缀索引：对前几个字符建立索引。提升查询效率，减少索引储存空间

  • 单独创建，alter table 表名 add 索引名(column_name(索引长度))
聚簇索引和非聚簇索引

聚簇索引：innodb

​ 根据主键列存储，叶子结点就是数据

​ 决定存储方式

非聚簇索引：mysiam 叶子结点是索引或者指针（没有直接找到数据）

覆盖索引：不需要查到具体内存地址，索引已包含内容

最左优先原则

使用联合索引（复合索引）时，按照最左优先进行索引匹配

索引缺陷：

时间：增删操作时需要额外保存索引列数据

空间：保存索引表

什么时候更适合使用索引：
  1. 频繁的查询操作：
    • 对于那些被频繁查询的字段，尤其是在 SELECT 语句的 WHERE 子句中经常出现的字段，使用索引可以显著提高查询性能。
  2. 大数据集：
    • 在大数据集中，扫描整个表的开销可能非常高。索引可以帮助快速定位数据，减少扫描的行数。
  3. 排序和分组操作：
    • 如果查询中包含 ORDER BY 或 GROUP BY 操作，在这些列上创建索引可以帮助加快排序和分组的速度。
  4. JOIN操作：
    • 在涉及多个表的 JOIN 操作中，通常需要对连接字段创建索引，以加快连接速度。
  5. 唯一性约束：
    • 索引可以用于强制执行唯一性约束（如主键和唯一键），确保数据完整性。
  6. 覆盖索引：
    • 当索引包含了查询所需的所有列时，数据库可以直接从索引中获取数据，而无需访问实际的表数据。这种索引称为覆盖索引，可以极大提高查询效率。
  7. 全文搜索：
    • 对于需要全文搜索的字段（如文本字段），可以考虑使用全文索引类型（如MySQL中的FULLTEXT索引），以提高搜索效率。
什么时候不使用：

稀疏表

经常增删改

数据重复高

索引失效：

违背最左原则

索引参与计算

like查询以%开头

使用字符串时未加引号

查询条件带or而非所有条件都有索引

全表扫描快于索引

什么时候用联合索引

  1. 多列查询：
    • 当查询条件中经常涉及多个列时，可以考虑使用联合索引。例如，如果查询经常使用 WHERE column1 = ? AND column2 = ?，那么在 column1 和 column2 上创建联合索引可以提高查询效率。
  2. 排序和分组：
    • 如果查询中有 ORDER BY 和 GROUP BY 涉及多个列，创建联合索引可以帮助加快排序和分组操作。例如，ORDER BY column1, column2 可以通过在 column1 和 column2 上的联合索引来加速。
  3. 覆盖索引：
    • 当一个联合索引包含了查询所需的所有列时，该索引可以成为覆盖索引，从而避免访问表数据。例如，SELECT column1, column2 FROM table WHERE column1 = ? 可以通过在 column1 和 column2 上的联合索引来提高性能。
  4. 联合主键或唯一约束：
    • 在需要保证多个列组合唯一的情况下（如联合主键），使用联合索引是必要的。

锁

乐观锁/悲观锁：允许加锁读/不允许

全局锁：

数据库进入只读状态

表级锁

元数据锁：crud操作用锁

意向锁：（意向共享锁，意向独占锁）select不需要锁，用mvcc即可

AUTO-INC锁：用于实现主键赋值的自增

行级锁

记录锁：锁一条记录。（排他锁，共享锁）

间隙锁：存在于可重复读隔离级别。解决幻读问题，不互斥

​ 包括：next-key lock临键锁（保护间隙临界区）

​ 插入意向锁（申请访问间隙临界区）

执行引擎

innodb：acid

myisam：不支持事务，行级锁，外键约束

memery：数据在内存中，速度快，不安全

innodb vs myisam：

innodb并发度更高

innodb有恢复日志

myisam查询性能强

innodb索引为堆表，myisam为索引组织表

innodb为聚簇索引，myisam为非聚簇索引

日志

undolog：

回滚日志。实现原子性，mvcc的回滚

trx_id找到被修改事务

Roll_pointer实现回滚

#innodb通过readview+undo log实现mvcc

记录修改前

redolog：

物理日志，记录页面修改，执行事务也会产生。实现持久性。用于掉电故障恢复。

#WAL：写操作不立刻写到磁盘，先记入日志（redolog），保证crash-safe

记录修改后

binlog：

归档日志。用于数据备份，主从复制。

三种格式： Statement:记录修改数据的sql。动态函数不一致问题

Row:记录最终修改出的数据。数据量太庞大问题

mixed：自动切换

两阶段提交：

主从环境不一致：主-redo log，从-binlog

优化

慢查询原因：

1.mysql版本老

2.索引不够

3.查询语句复杂

4.数据库设计问题

5.数据库服务器过载

6.数据库统计信息问题

如何优化

思路：索引，表结构，语句，存储引擎