1.数据库概念
什么是数据库
数据库(Database) 是保存有组织的数据的容器(通常是一个文件或一组文件),是通过数据库管理系统(DataBase- Management System,DBMS) 创建和操纵的容器。DBMS 的主要目标是提供一种可以方便、高效地存取数据库信息的途径。
数据库类型 | 定义 | 优点 | 缺点 | 常见 |
---|---|---|---|---|
关系型数据库 | 建立在关系模型基础上,由多张能互相连接的__表__组成的数据库 | 1)使用表结构,格式一致,易于维护;2)使用SQL语句,可用于复杂查询;3)数据存储在磁盘中,安全性高 | 1)读写性能比较差;2)建立在关系模型上,不可避免空间浪费;3)固定的表结构,灵活度较低 | MySQL,Microsoft SQL Server,Oracle,PostgreSQL 等 |
非关系型数据库 | 非关系型数据库又被称为 NoSQL(Not Only SQL ),通常指数据以__对象__的形式存储在数据库中,而对象之间的关系通过每个对象自身的属性来决定。 | 1) 存储数据的格式可以是key-value、文档、图片等形式,应用场景更广泛;2)可轻松进行海量数据的维护和处理;3)具有可扩展、高并发、高稳定性、成本低的优势;4)可以实现数据的分布式处理 | 1)不提供 SQL 支持;2)无事务处理,无法保证数据的完整性和安全性;3)功能没有关系型数据库完善 | Neo4j,Redis,MongoDB 等 |
SQL是结构化查询语言(Structured Query Language)的缩写,是一种数据库查询语言,用于存取数据、查询、更新和管理关系数据库系统。
为什么要使用数据库
数据保存方式 | 优点 | 缺点 |
---|---|---|
数据保存在内存中 | 存取速度快 | 数据无法永久保存 |
数据保存在文件中 | 数据可永久保存 | 查询数据不方便;操作速度比内存操作慢,频繁的IO操作 |
数据保存在数据库 | 数据可永久保存且数据安全性高;使用SQL语句,查询方便效率高;便于数据管理,智能化数据分析 | 数据库移植不方便;不支持集群;不擅长业务逻辑的处理 |
SQL与MySQL的区别
SQL 和 MySQL 是 DBMS 中最令人困惑的两个术语,二者之间存在本质上的区别。
- SQL 是一种结构化查询语言,用于在数据库上执行各种操作,但MySQL是一个 关系数据库管理系统(RDBMS),使用 SQL 执行所有数据库操作。
- SQL 用于访问,更新和操作数据库中的数据,用户使用时需要学习该语言,然后编写查询,而 MySQL 是一个软件,会为用户提供一个界面,只需单击一些按钮即可用于执行各种数据库操作。
- 由于 MySQL 是一个软件,所以它会定期获得各种更新,但在 SQL 中,命令总是相同的。
数据库三大范式
数据库范式是设计数据库时,需要遵循的一些规范。各种范式是条件递增的联系,越高的范式数据库冗余越小。常用的数据库三大范式为:
- 第一范式(1NF):每个列都不可以再拆分,强调的是列的原子性。第一范式要求数据库中的表都是二维表。
- 第二范式(2NF):在第一范式的基础上,一个表必须有一个主键,非主键列完全依赖 于主键,而不能是依赖于主键的一部分。
- 第三范式(3NF):在第二范式的基础上,非主键列只依赖(直接依赖)于主键,不依赖于其他非主键。
例子
-
第一范式(1NF):列的原子性
上表中的家庭信息不满子列的原子性,若要满足第一范式,调整如下图
调整后每个列都不可再分,故此时满足第一范式 -
第二范式(2NF):确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关
如上图,同一订单号存在不同产品号,同一产平号存在不同订单号。根据主键的定义,这里订单号和产品号为该表的主键,同时产品数量、产品折扣和产品价格都与订单号、产品号相关,但是订单金额和订单时间仅与订单号相关。故不满足第二范式的要求,调整如下图:记得去重
* 第三范式(3NF):确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
上表中,学号是主键,所有列完全依赖于学号,但“班主任性别”和“班主任年龄”与学号是间接相关,与班主任姓名直接相关,故不满足第三范式要求,调整如下图:记得去重
数据库泄露的含义
数据库连接泄露指的是如果在某次使用或者某段程序中没有正确地关闭 Connection、Statement 和 ResultSet 资源,那么每次执行都会留下一些没有关闭的连接,这些连接失去了引用而不能得到重新使用,因此就造成了数据库连接的泄漏。数据库连接的资源是宝贵而且是有限的,如果在某段使用频率很高的代码中出现这种泄漏,那么数据库连接资源将被耗尽,影响系统的正常运转。
触发器
触发器(trigger)是与表相关的数据库对象,是用户定义在关系表上的一类由事件驱动的特殊的存储过程,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据库的完整性。
- 数据库触发器(Database Trigger)是数据库中的一种特殊对象,其作用类似于 “监听器” ,当满足某些条件时自动触发事件或执行某些动作。
- 当数据库中指定的 INSERT、UPDATE 或 DELETE 操作在表中进行时,触发器就自动执行。触发器可以在数据被修改之前(Before Trigger)或之后(After Trigger)执行。
- 在触发器中可以对数据进行检查、约束条件检查、自定义规则的执行、日志记录等操作。触发器通常用于数据一致性的保证、日志记录、自动化维护数据的完整性等。
- 在数据库开发中,触发器是非常重要的一种技术,通过触发器技术,可以保证数据的一致性和完整性,提高数据库应用的性能和可靠性。
使用场景
- 可以通过数据库中的相关表实现级联更改;
- 实时监控某张表中的某个字段的更改,并需要做出相应的处理。
索引
索引的优缺点
索引的优点
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
- 可以加快数据的检索速度,这也是创建索引的主要原因
- 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义
- 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统性能
索引的缺点
- 时间上,创建和维护索引都要耗费时间,这种时间随着数据量的增加而增加,具体地,当对表中的数据进行增加,删除和修改时,索引也要动态的维护,这样就降低了数据的维护速度
- 空间上,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大
索引为什么可以加快查询速度?
因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据
同时,由于索引底层实现的有序性,使得在进行数据查询时,能够避免在磁盘不同扇区的随机寻址
使用索引后能够通过磁盘预读使得在磁盘上对数据的访问大致呈顺序的寻址。这本质上是依据局部性原理所实现的
总结:
索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变成顺序I/O(局部性原理)
索引的数据结构
数据库索引根据结构分类,主要有B树索引,Hash索引和位图索引三种
B树索引
B树索引,又称平衡树索引,是MySQL数据库中使用最频繁的索引类型,MySQL,Oracle和SQL server数据库默认的都是B树索引(实际上用B+树实现,因为在查看表索引时,MySQL 一律打印 BTREE,所以简称为 B 树索引)
B 树索引以树结构组织,它有一个或者多个分支结点,分支结点又指向单级的叶结点。其中,分支结点用于遍历树,叶结点则保存真正的值和位置信息。
B+ 树是在 B 树基础上的一种优化,使其更适合实现外存储索引结构。
一棵 m 阶 B-Tree 的特性如下:
- 每个结点最多 m 个子结点;
- 除了根结点和叶子结点外,每个结点最少有 m/2(向上取整)个子结点;
- 所有的叶子结点都位于同一层;
- 每个结点都包含 k 个元素(关键字),这里 m/2≤k<m,这里 m/2 向下取整;
- 每个节点中的元素(关键字)从小到大排列;
- 每个元素子左结点的值,都小于或等于该元素,右结点的值都大于或等于该元素。
数据库以 B-Tree的数据结构存储数据的图示如下:
B+ Tree与B-Tree的结构很像,但是也有自己的特性: - 所有的非叶子结点只存储 关键字信息;
- 所有具体数据都存在叶子结点中;
- 所有的叶子结点中包含了全部元素的信息;
- 所有叶子节点之间都有一个链指针。
数据库以 B+ Tree的数据结构存储数据的图示如下:
Hash索引
哈希索引采用一定的哈希算法(常见哈希算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的 Hash 值,与这条数据的行指针一并存入 Hash 表的对应位置,如果发生Hash 碰撞(两个不同关键字的 Hash 值相同),则在对应 Hash 键下以链表形式 存储。
检索时不需要类似 B+ 树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快,平均检索时间为O(1)
。
位图索引
B 树索引擅长于处理包含许多不同值的列,但是在处理基数较小的列时会变得很难使用。如果用户查询的列的基数非常的小,即只有几个固定值,如性别、婚姻状况、行政区等等,要么不使用索引,查询时一行行扫描所有记录,要么考虑建立位图索引。
位图索引为存储在某列中的每个值生成一个位图。例如针对表中婚姻状况这一列,生成的位图索引大致如下所示:
Value / Row ID | 1 | 2 | 3 | 4 | 5 | 6 | …… |
---|---|---|---|---|---|---|---|
未婚 | 1 | 1 | 0 | 0 | 0 | 0 | |
已婚 | 0 | 0 | 0 | 1 | 1 | 1 | |
离婚 | 0 | 0 | 1 | 0 | 0 | 0 |
对于婚姻状况这一列,索引包含3个位图,即生成有3个向量,分别属于每一个取值,每个位图为每一个人(行)都分配了0/1
值(每一行有且仅有一个1
),未婚为 110000……,已婚为 000111……,离婚为 001000……。
当进行数据查找时,只要查找相关位图中的所有1
值即可(可根据查询需求进行与、或运算)。
例如, Oracle 用户可以通过为create index
语句简单地添加关键词 bitmap 生成位图:
CREATE BITMAP INDEX acc_marital_idx ON account (marital_cd);
除了上述提及的,位图索引适合只有几个固定值的列,还需注意 ,位图索引适合静态数据,而不适合索引频繁更新的列。
使用B+树的好处
- 由于 B+ 树的内部结点只存放键,不存放值,因此,一次读取,可以在同一内存页中获取更多的键,有利于更快地缩小查找范围。
- B+ 树的叶结点由一条链相连,因此当需要进行一次 全数据遍历 的时候,B+ 树只需要使用 O(logN) 时间找到最小结点,然后通过链进行 O(N) 的顺序遍历即可;或者,在找 大于某个关键字或者小于某个关键字的数据 的时候,B+ 树只需要找到该关键字然后沿着链表遍历即可。
Hash索引和B+树索引的区别
- Hash 索引进行等值查询更快(一般情况下),但是却无法进行范围查询;
- Hash 索引不支持使用索引进行排序;
- Hash 索引不支持模糊查询以及多列索引的最左前缀匹配,原理也是因为 Hash 函数的不可预测;
- Hash 索引任何时候都避免不了回表查询数据,而 B+ 树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询;
- Hash 索引虽然在等值查询上较快,但是不稳定,性能不可预测,当某个键值存在大量重复的时候,发生 Hash 碰撞,此时效率可能极差;而 B+ 树的查询效率比较稳定,对于所有的查询都是从根结点到叶子结点,且树的高度较低。
什么是前缀索引
有时需要索引很长的字符列,它会使索引变大并且变慢,一个策略就是索引开始的几个字符,而不是全部值,即被称为前缀索引,以节约空间并得到好的性能。使用前缀索引的前提是此前缀的标识度高,比如密码就适合建立前缀索引,因为密码几乎各不相同。
前缀索引需要的空间变小,但也会降低选择性。索引选择性(INDEX SELECTIVITY)是不重复的索引值(也叫基数)和表中所有行数(T)的比值,数值范围为 1/T ~1。高选择性的索引有好处,因为在查找匹配的时候可以过滤掉更多的行,唯一索引的选择率为 1,为最佳值。对于前缀索引而言,前缀越长往往会得到好的选择性,但是短的前缀会节约空间,所以实操的难度在于前缀截取长度的抉择,可以通过调试查看不同前缀长度的平均匹配度,来选择截取长度。
什么是最左前缀匹配原则
在 MySQL 建立联合索引(多列索引)时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。例如有一个 3 列索引(a,b,c),则已经对(a)、(a,b)、(a,b,c)上建立了索引。所以在创建多列索引时,要根据业务需求,where 子句中 使用最频繁 的一列放在最左边。
根据最左前缀匹配原则,MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如采用查询条件where a = 1 and b = 2 and c > 3 and d = 4
时,如果建立(a,b,c,d)顺序的索引,d 是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,并且 where 子句中 a、b、d 的顺序可以任意调整。
如果建立的索引顺序是(a,b),那么根据最左前缀匹配原则,直接采用查询条件 where b = 1 是无法利用到索引的。
添加索引的原则
索引虽好,但也不是无限制使用的,以下为添加索引时需要遵循的几项建议性原则:
- 在查询中很少使用或者参考的列不要创建索引。由于这些列很少使用到,增加索引反而会降低系统的维护速度和增大空间需求。
- 只有很少数据值的列也不应该增加索引。由于这些列的取值很少,区分度太低,例如人事表中的性别,在查询时,需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
- 定义为
text、image 和 bit
数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。 - 当修改性能远远大于检索性能时,不应该创建索引。这时因为,二者是相互矛盾的,当增加索引时,会提高检索性能,但是会降低修改性能。
- 定义有外键的数据列一定要创建索引。
什么是聚簇索引
聚簇索引(聚集索引)
首先并不是一种索引类型,而是一种数据存储方式。具体的,聚簇索引指将数据存储和索引放到一起,找到索引也就找到了数据。
MySQL 里只有 INNODB 表支持聚簇索引,INNODB 表数据本身就是聚簇索引,非叶子节点按照主键顺序存放,叶子节点存放主键以及对应的行记录。所以对 INNODB 表进行全表顺序扫描会非常快。
特点
- 因为索引和数据存放在一起,所以具有更高的检索效率;
- 相比于非聚簇索引,聚簇索引可以减少磁盘的 IO 次数;
- 表的物理存储依据聚簇索引的结构,所以一个数据表只能有一个聚簇索引,但可以拥有多个非聚簇索引;
- 一般而言,会在频繁使用、排序的字段上创建聚簇索引。
非聚簇索引
除了聚簇索引以外的其他索引,均称之为非聚簇索引。非聚簇索引也是 B 树结构,与聚簇索引的存储结构不同之处在于,非聚簇索引中不存储真正的数据行,只包含一个指向数据行的指针。
就简单的 SQL 查询来看,分为SELECT
和WHERE
两个部分,索引的创建也是以此为根据的,分为 复合索引和覆盖索引。
锁
锁的分类
重数据库系统的角度,锁模式可分为以下6种类型:
共享锁(S):又叫读锁.可以并发读取数据,但不能修改数据.也就是说当数据资源上存在共享锁时,所有的事务都不能对数据进行修改,直到数据读取完成,共享锁释放
排他锁(X):又叫独占锁,写锁.对数据资源进行增删改操作时,不允许其他事务操作这块资源,直到排他锁被释放,从而防止同时对同一资源进行多重操作
SELECT子句顺序
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
LIMIT | 要检索的行数 | 否 |
ON
和WHERE
在SQL中都用于设置条件,但它们在连接(JOIN)操作中的作用是不同的。
- ON:在
JOIN
操作中,ON
关键字用于设置连接条件 - WHERE:
WHERE关
键字用于过滤结果集,即只返回满足特定条件的行。
总的来说,ON 关键字定义了如何连接两个表,而 WHERE 关键字定义了如何过滤这些连接后的结果。在某些情况下,使用 ON 和 WHERE 可能会得到相同的结果,但在其他情况下,结果可能会有所不同,特别是在执行外连接(如 LEFT JOIN 或 RIGHT JOIN)时。
- 内连接(INNER JOIN)只返回两个表中都有匹配的行。
- 左连接(LEFT JOIN)返回左表的所有行,如果右表中没有匹配的行,则结果集中的对应列将为 NULL。
- WHERE 子句返回满足特定条件的行
第二范式与第三范式的区别
单主键必然满足第二范式,第三范式要在第二主键的基础上要求非主键相互独立
写得很好!
谢谢您,祝您新年快乐!