数据库MySQL
文章平均质量分 88
总结MySQL数据库的各种知识及表的设计
程序猿ZhangSir
24届计算机专业毕业大学生
展开
-
MySQL 中COUNT(*),COUNT(1),COUNT(普通字段),COUNT(索引字段)的效率和区别?
(3)在没有非聚簇索引的情况下,COUNT(1),COUNT(*),COUNT(普通字段),COUNT(索引字段)都是通过聚簇索引来进行统计,如果字段为NULL也是统计不进去的,所以COUNT(普通字段)统计的结果一定小于等于 COUNT(1),COUNT(*),COUNT(索引字段) 的结果。执行效率 COUNT(*) = COUNT(1) > COUNT(主键字段) > COUNT(普通字段);COUNT(*)和COUNT(1)都是对所有结果进行COUNT,二者区别不大,效率没有明显差别;原创 2023-11-09 08:30:00 · 271 阅读 · 0 评论 -
数据库的多表查询该如何操作?
相信各位已经想到了吧。此外有一点需要特别注意,在我写的sql语句中,对员工表起了别名为 e ,对部门表起了别名为 d,所以,在后续条件过过滤和查询时,均需要使用表的别名来替代表明,否则会出错,因为一旦你给表起了别名,那么数据库就会有你起的别名来覆盖原本的表名,这时如果你已经起了别名,却仍使用原来的表名,则数据库会报错。但此时我们需要注意,在员工表中,有的员工是没有部门的,而在部门表中,有的部门是没有员工的,如果我们使用内连接进行查询的话,没有部门的员工和没有员工的部门的相关信息,我们都是查不到的。原创 2023-05-28 17:56:11 · 5770 阅读 · 2 评论 -
MySQL中空值和NULL的区别,开发过程中注意避坑!
在 MySQL 中,NULL 值和空值是两个不同的概念,空值就是我们常说的空字符串,用两个单引号 '' 代替即可;NULL 值在MySQL中是占用空间的,而空值则是不占用长度空间的。举个最简单的例子。如果把数据比作水果,表中的每一个空位比作一个个箱子,水果要放进箱子里存储;NULL就可以理解为空位上有一个箱子,但箱子是空的,没有存放任何水果;空值就可以理解为空位上连箱子都没有,真空状态;2. NULL和空值在表中显示的区别如下SQL创建一张 user 用户表,原创 2024-04-18 21:28:22 · 1802 阅读 · 0 评论 -
MySQL 中 JSON 字段增删改查相关函数及使用演示,简单易学,5分钟上手
JSON数据是我们在开发过程中几位常用的一种数据存储方式,主要以KV键值对的形式存储数据,在 MySQL5.7 之后,提供了JSON字段,在 MySQL8之后得到了很好的优化,下面就是JSON格式数据与的例子,以KV键值对的形式存储数据,每个键值对之间使用 "," 隔开即可。我们先准备好一张 product 商品表,字段类型如下图所示,方便一会操作展示然后在表中存放一些数据,如下图,数据均为编造,仅为演示建表SQL和数据SQL如下,想动手尝试小伙伴们可自行CV。原创 2024-04-01 14:51:11 · 1943 阅读 · 0 评论 -
数据库SQL查询语句的执行顺序是怎么样的?
通过上面我们一大堆的分析,我们可以得出以下结论,在SQL查询语句中,各个关键字以及过滤条件的执行顺序如下如下图所示。原创 2023-08-11 14:15:00 · 1624 阅读 · 0 评论 -
详谈数据库InnoDB引擎与MyISAM引擎
相信很多人在听到存储引擎这个名字的时候可能会有些疑惑,听着名字就觉得有些难,导致很多人没有兴趣了解它,那么它这究竟是个什么东西?我来说明一个场景,大家就能大概明白它是做什么用的了。我们都知道,既然是数据库,我们就是用来存储数据的,数据库中会有很多的表,那么各位有没有想过数据库底层究竟是以什么样的格式存储这些表的数据的呢?这就会用到我们存储引擎,存储引擎在以前叫做表处理器,是后来才改名叫存储引擎的,它的功能就是接收收到的指令,然后对表中的数据进行读写操作。原创 2023-08-14 08:30:00 · 326 阅读 · 0 评论 -
深入理解索引B+树的基本原理
MySQL官方给索引的定义是:索引(Index)是帮助MySQL高效获取数据的数据结构。简洁点来说,索引的本质是数据结构。此外,索引是在存储引擎中实现的,当我们的存储引擎不同时,索引底层的数据结构也不相同。同时,存储引擎中还定义了一个表中最多不能超过16个索引,但其实我们也用不到这么多,所以完全可以放心;也定义了索引的长度不能超过256个字节,其实也很大了,我们通常不会把索引定义那么大,也可以放心。原创 2023-08-15 09:00:00 · 639 阅读 · 0 评论 -
HASH索引,AVL树,B树,B+树的区别?
Hash 本身其实是一个函数,又被称为散列函数,它可以大幅提高我们对数据的检索效率。因为它是散列的,所以在存储数据的时候,它也是无序的。Hash 算法是通过某种确定性的算法(例如MD5,SHA1,SHA2,SHA3)将输入转变成输出,相同的输入结果永远会得到相同的输出。原创 2023-08-16 10:00:00 · 613 阅读 · 1 评论 -
什么是事务?如果你还只会说ACID,面试已经失败一半了!
举个栗子,A账户对B账户转账,A扣100,首先要判断A是否足够100,如果不够扣,那么这条语句执行后就是不合法的;但实际上,如果我们没有给事务添加隔离性,就等于是上述这七步操作,每一步都有可能有别人中途打断,就像上面图中的情况,最后一步B要写入磁盘了,还没来得及写呢,事务2进入先执行完了,执行完成之后恰好值为50,然后又执行事务1中的写入50操作,这样执行下来,A账户剩余100,但B账户只有五十,它们虽然都成功完成了,满足原子性,但是不满足我们刚才说的一致性,A与B之后由之前的200变成了150。原创 2023-08-17 10:45:00 · 76 阅读 · 0 评论 -
什么是 脏写,脏读,幻读,不可重复读?怎样能解决这四种问题?
我们通过如下语句先创建一个 student 学生表。我就以对学生表的操作来解释什么是脏写,脏读,幻读,不可重复读创建完成之后随便插入一条数据。原创 2023-08-18 11:30:00 · 575 阅读 · 0 评论 -
MySQL表锁,页锁,行锁,读写锁,全局锁,乐观锁,悲观锁深入了解
而且T2事务判断是否有页锁或行锁的过程是有讲究的,如果T1事务添加的是行锁,那么T2事务在判断的时候,它就要去进行全表扫描,判断每个行数据是否添加过行锁,如果这样做的话,性能是非常非常差的。而且页锁可能会产生死锁问题,假设 user 表现在有两页数据,事务A需要先处理第一页的数据再处理第二页的数据,事务B需要先处理第二页的数据再处理第一页的数据,那么事务A就会先锁定第一页,事务B就会先锁定第二页,处理完成之后,事务A等待事务B释放第二页的锁,事务B等待事务A释放第一页的锁,双方互相等待,就会造成死锁问题。原创 2023-09-04 10:04:04 · 620 阅读 · 0 评论 -
索引失效的几种情况
是数据库比较偏底层的一个东西,这里我就不详细说明了,我们在数据库执行一条SQL指令的时候,都会经过优化器,优化器会以效率最优的方式来处理我们的SQL语句,我们在做一条查询语句的时候,有可能使用索引效率会更高,有可能不使用索引效率会更高,这都是有优化器说了算的,这也就产生了索引会失效的情况,下面我就来详细说一说索引可能失效的几种情况。可以看到,在上面,我为 student 学生表建立了一个三个字段的联合索引,当执行SQL查询语句时,因为 age 字段是联合字段的第一个,底层优化器会用到该字段;原创 2023-09-06 11:00:00 · 520 阅读 · 0 评论 -
数据库 MVCC 详解,揭秘 MVCC 是如何做到可重复读的?
MVCC 英文全称叫 "Multiversion Concurrency Control",翻译过来就是 "多版本并发控制"。在 MySQL 众多存储引擎中只有 InnoDB 中实现了 MVCC 机制。原创 2023-09-15 14:06:17 · 994 阅读 · 0 评论 -
浅谈 MySQL 数据库日志有哪些?作用是什么?
数据定义语句日志:记录数据定义语句执行的元数据操作。原创 2023-09-20 10:41:55 · 634 阅读 · 0 评论 -
数据库 explain 关键字解析
有些同学可能不知道,我们在数据库中运行 SQL 语句时,底层优化器会选择最优的执行方式来执行我们的 SQL 语句,也就是说,底层执行 SQL 语句的顺序并不一定跟我们写的 SQL 语句的顺序是一致的。MySQL 中有专门优化 SELECT 语句的优化器模块,主要功能是:通过计算分析系统中收集到的统计信息,为客户端请求的 Query(查询) 提供它认为最优的执行计划。原创 2023-10-09 14:30:48 · 362 阅读 · 0 评论 -
RENAME,CHANGE,ALTER,MODIFY 四个字段的作用和区别
例如我们插入一条数据,它的某个字段长度超过了表中定义的长度,那么我们就需要将表中字段的长度变大,不能说数据舍弃不要了,这是不合理的。从作用上来讲,你可以简单的理解为 CHANGER 是 MODIY 的增强版,MODIFY 可以修改字段的长度和默认值,CHANGER 在 MODIFY 的基础上还可以修改字段的名称,即重命名字段。代码如下,个人建议采用第一种 RENAME 的方式,好记简单,第二种则是ALTER RENAME 一起使用,语法还变麻烦了。首先我们要知道一点,原创 2023-10-13 09:06:02 · 1554 阅读 · 0 评论 -
浅谈 MySQL 主从复制,优点?原理?
既然是主从复制,那么至少就应该有两台服务器,一台作为主库(Master),一台作为从库(Slave)。主数据库会将自己的DDL(数据定于语言,例如建库建表)与DML(数据操作语言,例如增删改查)操作写成二进制日志文件,发送给从库,从库在接收到二进制日志文件之后,自己在将文件中的命令执行一遍(也叫重做),将数据备份保存到自己的服务器内部,从而使自己的数据与主库的数据保持一致。同时,主库可以挂载多个从库,向多台数据库服务器发送数据文件;此外,从库也可以作为其他数据库的主库,实现链式复制。原创 2023-10-25 09:34:53 · 251 阅读 · 0 评论 -
MySQL 外连接和内连接的查询优化怎么做?
在没有任何索引的情况下,当两张表在进行连接查询操作的时候,实际上底层做的第一件事就是拿出驱动表一条记录与被驱动表的所有记录去做匹配,看是否满足条件,匹配完毕之后,再拿出驱动表的第二条记录与被驱动表的所有记录去做匹配,看是否有满足条件的记录,以此类推,直到驱动表所有记录均与被驱动表的所有记录进行过匹配并得出了满足条件的结果,两张表的连接查询的第一步连接操作就完成了。首先需要明确的一点是,内连接查询表面上是不分主表和此表的,默认来说左边的表就是驱动表,右边的表就是被驱动表。原创 2023-11-02 14:04:50 · 405 阅读 · 0 评论 -
MySQL 排序,分组,Limit的优化策略
并且,如果数据量非常大,内存中装不下,还需要多次IO操作,先读取一部分数据排序,再读取一部分数据排序,效率较低。对于数据库而言,排序,分组,去重这些操作都是比较繁琐耗费资源的,如果将所有操作全部放在数据库中,非常容易出现慢查询,因此我们可以考虑将这些操作放在程序端去做,数据库查询到数据之后,使用程序代码进行排序分组去重;Index:索引排序,就是我们给排序的字段添加了索引,因为索引本身就是有序的,所以我们在根据排序的时候就非常省时间了,不需要进行重排序,直接取出数据即可,效率很高。原创 2023-11-06 14:04:27 · 441 阅读 · 0 评论 -
MySQL覆盖索引的使用
用一句话来说,"索引中包含的列,能够满足查询的列,这就叫覆盖索引所以,覆盖索引严格意义上来说并不是一个索引,而是一种行为。回表查就是先查询非聚簇索引的B+树,通过非索引B+树查询到主键信息,然后再去聚簇索引的B+树中通过主键查询自己想要查询的信息。原创 2023-11-07 09:07:03 · 188 阅读 · 0 评论 -
索引下推(ICP)是什么意思?如何理解?
ICP 全称 Index Condition Pushdown,是MySQL5.6之后的一个新特性,它是一种在存储引擎层使用索引过滤数据的一种优化方式。原创 2023-11-13 09:43:44 · 131 阅读 · 0 评论 -
数据库表的设计——范式
范式的英文名称位 Normal Form,简称NF。在关系型数据库中,关于数据表设计的基本原则,规则称之为范式。可以简单的理解为,一张数据表的设计结构需要满足某种设计标准的级别,满足某种规则。原创 2023-11-14 08:00:00 · 765 阅读 · 1 评论 -
MySQL 锁的内存结构
在 MySQL 中,有很多种锁,例如行锁,表锁,页锁,全局锁,它们锁的粒度都不一样。更多情况下,我们只是关注这些锁的区别和作用,却很少关注它们本身,本篇我们就来简单了解一下MySQL 中锁的内存结构,锁中都包含了哪些信息。原创 2024-02-20 17:23:32 · 981 阅读 · 0 评论 -
用户,角色,权限三者的关系?让你设计用户权限系统,你会设计几张表?为什么要这样设计?
第二种方案要比第一种方案在编码层面更复杂,需要用到5张表,并需要用到多表查询,但是却非常利于系统对各个用户的权限控制,加入角色表我们可以将所有的用户划分为不同的角色,让权限关系更加清晰,当我们需要添加新的用户时,只需要让他与角色表相关联,然后就可以通过角色与权限关联表确认当前用户都具有哪些权限,程序的健壮性和可扩展性更强。在有些小的项目中,我们通常会发现关于用户权限的设计非常简单,通常都是设计一个权限表列出各种权限,然后就是用户表,用户表中设计一个权限字段关联权限表的主键,从而达到设计用户权限的目的。原创 2024-02-21 16:00:00 · 1774 阅读 · 0 评论 -
MySQL 子查询优化如何做?
子查询通常是将一个表的查询结果作为另一个表的查询条件,也就是我们常说的嵌套查询。使用子查询,我们让我们一次性完成需要很多步查询操作才能得到的结果。子查询的SQL语句通常比较长,而且执行效率普遍非常的差,是慢查询的主要贡献者,尽管它可以使用一条SQL语句达到查询效果,但实际并不是特别推荐使用,但我们在不得已的情况下也可以考虑使用子查询,那么我们就要最大程度上提高子查询的效率,这里就涉及到了子查询的优化。原创 2024-02-26 15:30:34 · 681 阅读 · 0 评论 -
数据库的索引
另外,索引是在存贮引擎中实现的,因此每种中存储引擎的索引不一定完全相同。(2)索引需要占用磁盘空间,除了数据库中表占用的磁盘空间之外,每一个索引也会占用一定的物理空间,存贮在磁盘上,如果有大量的索引,可能索引文件比数据库文件更快达到最大文件尺寸。关于数据库的索引,下面从什么情况下适合建立索引,什么情况下不适合建立索引以及索引失效的几种情况来进行说明。MySql索引包括普通索引,唯一性索引,全文索引,单列索引,多列索引和空间索引。(1)从功能逻辑上分:主要分为四种,普通索引,唯一索引,主键索引,全文索引。原创 2023-06-03 17:54:32 · 121 阅读 · 0 评论