数据库知识点总结

select * 为什么会很慢

网络传输，CPU cost，IO cost。

如果select * 与你想查询的字段完全一致，单单就查询来说，select * 并没有降低查询效率。但时实际使用中这种情况极少。获取了不必要的列，此时要走全表扫描，会变慢。

影响自动优化，*的话就不优化。

MySQL的默认引擎是什么

MySQL5.5以前是MyISAM，之后默认的事务引擎是InnoDB。

MyISAM InnoDB
事务 不支持 支持
行级锁 不支持 支持
锁的最小粒度 表级锁 行级锁
外键 不支持 支持
全文索引 支持 不支持FULLTEXT，但有插件可以支持
崩溃后安全恢复 不能 能
适合情况 读密集 写密集
复杂度 简单 复杂

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键

索引的优缺点，什么场景适用

优点

  1. 加快检索速度

  2. 加速表表连接

  3. 加快order by, group by

缺点

  1. 创建维护需要时间
  2. 数据量少的时候得不偿失
  3. 索引占据物理空间，聚簇索引空间更大
  4. 增删改的时候也要维护索引，数据维护速度变慢

索引分类

如何创建索引

直接创建

CREATE INDEX column_index ON table (column);

间接创建

定义主键约束或者唯一性键约束

普通索引和唯一性索引

普通索引

CREATE INDEX column_index ON table (column);

唯一性索引

保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用

CREATE UNIQUE COUSTERED INDEX column_index ON table(column)

单个索引和复合索引

单个索引

即非复合索引

复合索引

又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段

CREATE INDEX name_index ON username(first_name,last_name)

聚簇索引和非聚簇索引(聚集索引，群集索引)

聚簇索引

物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列

CREATE CLUSTERED INDEX my_column_cindex ON table(column) WITH ALLOW_DUP_ROW;(允许有重复记录的聚簇索引)

非聚簇索引

CREATE UNCLUSTERED INDEX my_column_cindex ON table(column);

什么时候索引会失效

  1. %匹配在前

  2. 使用!=,<>,not null,is null

  3. 运算，如substring,+,-

  4. 字符串不加单引号

  5. 从取范围的列的后一个关键字开始失效

  6. 用or会失效，用union和in替换则不会：

    select loc_id , loc_desc , region from location where loc_id = 10
    union
    select loc_id , loc_desc , region from location where region = “melbourne”;

    或

    select… from location where loc_in in (10,20,30);

    取代

    select loc_id , loc desc , region from location where loc_id = 10 or region = “melbourne”;

  7. 复合索引跳过了中间的列，中间索引右边的索引全部失效，左边的索引生效。

数据库除了使用索引，想优化查找效率还能怎么做

  1. 优化表结构，根据范式/反范式设计。
    • 使用可存下数据的最小的数据类型
    • 使用简单的数据类型，int要比varchar类型在MySQL处理上更简单
    • 尽量用not null
    • 少用text，非用不可最好分表（水平，垂直分表），将text字段存放到另一张表中，在需要的时候再使用联合查询，这样可提高查询主表的效率。
  2. 硬件优化
  3. 通过慢查询日志发现有效率问题的SQL

索引优化

  1. 选择索引

    • 选择合适的索引列，选择在where，group by，order by，on从句中出现的列作为索引项，对于离散度不大的列没有必要创建索引。

    • 索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )

    • 离散度越大的列放在联合索引越前面

      select count(distinct ziduan1),count(distinct ziduan2) from table_name;
      越大越离散

  2. 索引优化

    • 联合索引不要加上主键,InnoDB已经默认加了。
    • 不是越多越好

数据库范式

1NF

每个列的属性不能再分

2NF

消除了非主属性对码的部分函数依赖

3NF

消除了非主属性对码的传递函数依赖

事务的四个特性

ACID，AID服务于C

详细说说一致性

  • 一致性（Consistency） （等同于所有节点访问同一份最新的数据副本）
  • 可用性（Availability）（每次请求都能获取到非错的响应——但是不保证获取的数据为最新数据）
  • 分区容错性（Partition tolerance）

三者只能得其二。

MySQL中的锁

乐观锁：只在更新的时候检查数据完整性。可以使用版本号来实现。

悲观锁：屏蔽一切可能违反数据完整性的操作。

行锁：

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  • 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

间隙锁：

防止幻读。

主键索引和聚簇索引

一个表只能有一个聚簇索引

sale 部门 ：3字段 部门名字 员工ID 销售额度

SQL

统计每个部门销售额度

select 部门，sum(销售额) from table group by 部门

统计每个部门销售额度>10W的

select 部门，sum(销售额) from table group by 部门 having sum(*)>10W

为什么用B+树作为结构

  • 哈希虽然能够提供 O(1) 的单数据行操作性能，但是对于范围查询和排序却无法很好地支持，最终导致全表扫描；
  • B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O，而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O；

乐观/悲观锁

  • 乐观锁是一种思想，它其实并不是一种真正的『锁』，它会先尝试对资源进行修改，在写回时判断资源是否进行了改变，如果没有发生改变就会写回，否则就会进行重试，在整个的执行过程中其实都没有对数据库进行加锁；
  • 悲观锁就是一种真正的锁了，它会在获取资源前对资源进行加锁，确保同一时刻只有有限的线程能够访问该资源，其他想要尝试获取资源的操作都会进入等待状态，直到该线程完成了对资源的操作并且释放了锁后，其他线程才能重新操作资源；

乐观锁不会存在死锁的问题，但是由于更新后验证，所以当冲突频率和重试成本较高时更推荐使用悲观锁，而需要非常高的响应速度并且并发量非常大的时候使用乐观锁就能较好的解决问题，在这时使用悲观锁就可能出现严重的性能问题；在选择并发控制机制时，需要综合考虑上面的四个方面（冲突频率、重试成本、响应速度和并发量）进行选择。

意向锁

意向锁不会与行级的共享 / 排他锁互斥！！！意向锁和意向锁之间不互斥。

https://juejin.cn/post/6844903666332368909

获取一个行的X锁之后，会对整个表加一个意向排他锁，不让另一个事务对表加X/S锁。目的就是不用一行行检查了。

IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突

日志

参考：https://www.cnblogs.com/wy123/p/8365234.html

Error log

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQL DBA在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些警告信息或正确的信息。用于数据库优化。

Redolog

slow log：慢查询日志

可以在MySQL启动时设一个阈值，将运行时间大于（不等于）该值的所有SQL语句都记录到慢查询日志文件中。用于SQL语句优化。

log：查询日志

记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。

Undolog

Binlog

不包括select,show等操作。记录UPDATE等操作（哪怕操作本身没有成功）

视图

image-20210329140506273

缓存一致性

应该先更新数据库再删除缓存。更新数据库之后可以让缓存失效。

如果先删除缓存再更新数据库：

  • 事务B删除缓存
  • 事务A查询缓存，没有缓存，从数据库找到旧值
  • 此时事务B更新数据库
  • 事务A把旧值写到缓存，造成后面的数据有问题

那么，是不是一开始这个就不会有并发问题了？不是的，比如，一个是读操作，但是没有命中缓存，然后就到数据库中取数据，此时来了一个写操作，写完数据库后，让缓存失效，然后，之前的那个读操作再把老的数据放进去，所以，会造成脏数据。

但，这个case理论上会出现，不过，实际上出现的概率可能非常低，因为这个条件需要发生在读缓存时缓存失效，而且并发着有一个写操作。而实际上数据库的写操作会比读操作慢得多，而且还要锁表，而读操作必需在写操作前进入数据库操作，而又要晚于写操作更新缓存，所有的这些条件都具备的概率基本并不大。

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

JOIN 子句

首先，连接的结果可以在逻辑上看作是由SELECT语句指定的列组成的新表。

左连接与右连接的左右指的是以两张表中的哪一张为基准，它们都是外连接。

外连接就好像是为非基准表添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接，左表是基准表，左表的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。

这部分主要涉及的是表连接的逻辑问题，教程主讲语法的话这部分确实需要自己再去搜资料

来源：《数据库系统原理教程》，王珊，陈红编著，P86

数据库

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！