MySQL问答

已于 2022-03-13 23:40:10 修改
阅读量1.2k 收藏
点赞数 1
文章标签： mysql 数据库 database
于 2022-03-13 13:10:20 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48751562/article/details/123446959
版权

文章目录

三大范式

  • 第一范式：满足原子性，字段不可再拆（视情况而定）
  • 第二范式：满足第一范式的前提下，非主键必须依赖主键（联合主键）
  • 第三范式：满足第二范式的前提下，其他非主键之间不能存在依赖关系
  • 范式优点与缺点：
    • 优：减少冗余，更新快，修改少，查询时更少的distinct
    • 缺： 因为一个表不存在冗余重复数据，查询可能造成很多关联，效率变低，可能使一些索引策略无效，范式化将列存在不同表中，这些列若在同一个表中可以是一个索引。

Innodb与Myisam区别

InnodbMyisam
行锁表锁
支持事务（4种隔离级别）
聚簇索引（必须有主键）
.ibd存储数据+索引+表结构（.frm 存储表结构（MySQL8.0时，合并在表名.ibd中））.frm存储表结构 .myi 储存索引 .myd储存数据
更新更优查询更优
InnoDB不仅缓存索引还要缓存真实数据（所以对内存要求较大）MyISAM只缓存索引，不缓存真实数据（访问快）
支持外键不支持
InnoDB5.6后支持MyISAM支持全文索引
  • MyISAM适合读多更新少的，InnoDB适合插入更新频繁的场景

自增主键的理解

  • 关键字auto_increment
  • 只能添加在primary key或unique key的列中
  • 自增约束的列的数据类型必须是整数类型
  • 一个表最多只能有一个自增长列
  • 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接
    赋值为具体值

持久化的理解

  • 在MySQL 8.0之前，Innodb不支持持久化，所以当自增主键auto_increment的值大于MAX（primary key）+1时，当MySQL重启的时候，会重置auto_increment=MAX( primary key) +1。在MySQL 5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个 计数器 来决定的，而该计数器只在 内存中维护 ，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。
  • 在8.0之后，将自增主键的计数器持久化到redo log中。每次计数器发生改变，都会将其写入到redo log中。如果数据库发生重启，InnoDB会根据redo log中的计数器信息来初始化其内存值。为了尽量减小对系统性能的影响，计数器写入到redo log中，并不会马上刷新。

为什么自增主键不连续

  • 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化
  • 事务回滚（自增值不能回退，因为并发插入数据时，回退自增ID可能造成主键冲突）
  • 唯一键冲突（由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键=现在变了的子增值+1，所以不连续）
    在这里插入图片描述

什么是索引

  • 索引是排列好的数据结构，是为了更高效得查找数据
  • 优点
    • 提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主
      要的原因
    • 在实现数据的参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度
    • 在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时
      间 ，降低了CPU的消耗
  • 缺点
    • 索引需要占用磁盘空间
    • 创建索引和维护索引要耗费时间
    • 虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引分类

按照物理实现方式分类：

  • 聚簇索引：

    • 页内记录按主键大小顺序排成单向链表
    • 页之间按照头元素主键大小排成双向链表
    • B+树的 叶子节点 存储的是完整的用户记录

  • 非聚簇索引（二级索引、辅助索引）：

    • 用数据中的一部分列充当索引（不一定是主键）,如果说想找到完整数据，则需要通过这些列找到对应的主键值，再通过主键值和回表找到对应字段。

按照功能逻辑分类：

  • 普通索引
  • 唯一索引
  • 主键索引
  • 全文索引：通过关键字的匹配来进行查询过滤（效率 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题）

按照作用字段分类：

  • 单列索引
  • 联合索引

其他的

  • 覆盖索引：索引字段包含了查询中的所有字段，以至于不需要回表操作（索引列+主键包含 SELECT 到 FROM之间查询的列 ）
  • 回表：通过索引找到主键值，再通过主键值在主键索引中找寻所需的数据
  • 索引下推：在根据索引查询过程中就根据查询条件过滤掉一些记录，减少最后的回表操作

索引底层数据结构

索引是基于不同的存储引擎实现的，MySQL上的存储引擎负责对数据的读取与写入，不同的存储引擎的存放格式不一样。

  • Innodb、Myisam索引的数据结构为B+tree。
    在这里插入图片描述

  • Memory索引的数据结构为Hash

    • hash底层是哈希表实现，等值查询，可以快速定位，一般情况效率很高，不稳定，当出现大量键重复哈希冲突，效率下降，不支持范围查询，无法用于排序分组，无法模糊查询，多列索引的最左前缀匹配原则，总要回表操作等。

在这里插入图片描述
在这里插入图片描述

B树与B+树区别？为何用B+树？

  • B+树非叶子节点（目录页）只存放索引，不存放数据

  • B树叶子节点与非叶子节点（目录页与数据页）存放数据+索引
    在这里插入图片描述

  • MySQL中采用B+树，优势在于B+树仅有叶子节点存放数据，相对于B树会显得更加 “矮胖” ，这样树的层数相对而言较少（查询次数减少），可以减少IO次数

B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树。但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

索引设计原则

适合创建索引的情况

  • 字段的数值有唯一性的限制
  • 频繁作为 WHERE 查询条件的字段
  • 经常 GROUP BY 和 ORDER BY 的列（如果待排序的列有多个，那么可以在这些列上建立 组合索引）
  • UPDATE、DELETE 的 WHERE 条件列（如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护）
  • DISTINCT 字段需要创建索引
  • 多表 JOIN 连接操作时，创建索引注意事项
    • 连接表的数量尽量不要超过 3 张
    • 对 WHERE 条件创建索引
    • 对用于连接的字段创建索引，并且数据类型需一致
  • 使用列的类型小的创建索引
  • 使用字符串前缀创建索引（没必要对完整的字符串创建索引，浪费资源）
  • 区分度高(散列性高)的列适合作为索引
  • 使用最频繁的列放到联合索引的左侧
  • 在多个字段都要创建索引的情况下，联合索引优于单值索引
  • 限制索引的数目

不适合创建索引的情况

  • 在where中使用不到的字段，不要设置索引
  • 数据量小的表最好不要使用索引
  • 有大量重复数据的列上不要建立索引
  • 避免对经常更新的表创建过多的索引
  • 不建议用无序的值作为索引
  • 删除不再使用或者很少使用的索引
  • 不要定义冗余或重复的索引（维护这些索引只会增加维护的成本，并不会对搜索有
    什么好处）

总结：
索引设计原则要求查询快，占用空间少；一般建在where条件，匹配度高的；要求基数大，区分度高，不要过大索引，尽量扩展，用联合索引，更新频繁不适合、使用短索引。

索引失效场景

  • 最佳左前缀原则：对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，后续索引将失效
  • 计算、函数、类型转换(自动或手动)导致索引失效
  • 范围条件右边的列索引失效
  • 不等于(!= 或者<>)索引失效
  • isnull可以使用索引，isnotnull无法使用索引
  • like以通配符%开头索引失效
  • 不同字符集之间索引会失效（比较之前需要进行转换，从而导致索引失效）
  • OR 前后存在非索引的列，索引失效

普通索引和唯一索引怎样选

  • 查询比较
    • 查询会以页为单位将数据页加载进内存，不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果，普通索引查到第一条记录往后遍历直到不满足条件，由于都在内存中，不需要磁盘读取那么大开销，带来的额外查询开销忽略不计，所以查询性能几乎一致
  • 更新比较

首先得先介绍一下change buffer

  • 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中 ，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
    将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了 访问这个数据页 会触发merge外，系统有 后台线程会定期 merge。在 数据库正常关闭（shutdown） 的过程中，也会执行merge操作。
    如果能够将更新操作先记录在change buffer， 减少读磁盘IO ，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够 避免占用内存 ，提高内存利用率。
  • 唯一索引由于更新时要检查唯一性，所以需要将数据页先加载进内存才能判断，此时直接操作内存，不需要操作change buffer
  • 唯一索引不能使用change buffer，只有普通索引能用，所以尽量选择普通索引
  • 写多读少，选用普通索引更好，可以利用change buffer进行性能优化减少磁盘IO，将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改.

一条sql查询语句执行过程

  1. 首先由连接器进行身份验证，权限管理
  2. 若开启了缓存，会检查缓存是否有该sql对应结果（缓存存储形式key-vlaue，key是执行的sql，value是对应的值）若开启缓存又有该sql的映射，将结果直接返回；
  3. 分析器进行词法语法分析
  4. 优化器会生成执行计划、选择索引等操作，选取最优执行方案
  5. 然后来到执行器，打开表调用存储引擎接口，逐行判断是否满足查询条件，满足放到结果集，最终返回给客户端；若用到索引，筛选行也会根据索引筛选。
    在这里插入图片描述
    在这里插入图片描述

两阶段提交

  1. 引擎先根据筛选条件筛选对应的行返回给执行器（若对应的行在内存直接返回，否则先去磁盘读取再返回）
  2. 执行器执行相关更新操作然后调用引擎接口写回更新后数据
  3. 引擎将新数据更新到内存，将更新操作记录到redolog，redolog处于prepare，告知执行器执行完，可提交事务
  4. 执行器生成该操作的binlog 并将binlog写入磁盘
  5. 执行器调用引擎事务提交接口，引擎把刚写入的redolog改为commit状态，更新完成。
    在这里插入图片描述

事务ACID实现原理

  • 原子性：事务要么全部成功，要么全部失败，它由undo log实现
  • 一致性：满足约束条件
  • 持久性：事务提交之后，能对数据进行永久性的修改，它由redo log实现
  • 隔离性：保证事务的独立，事务的结果只有提交了其他事务才可见，它由锁与MVCC实现

MVCC原理

MVCC多版本并发控制，顾名思义， M V CC 是通过数据行的多个版本管理来实现数据库的 并发控制 。这项技术使得在 Inno D B 的事务隔离级别下执行 一致性读 操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。

MVCC原理 ReadView

ReadView能帮助我们解决行的可见性问题

什么ReadView？

ReadView 是事务在使用MVCC机制是进行快照读操作是产生的读视图。当事务启动时，会产生数据库系统当前的一个快照，Innodb为每个事务构造了一个数组，用于记录并维护当前活跃的ID（活跃的：开启了但是还没提交）

ReadView结构

在这里插入图片描述

ReadView规则

  • 如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值大于或等于ReadView中的 low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
  • 如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和low_limit_id之间，则需要判断一下trx_id属性值是不是在 trx_ids 列表中。
    • 如果在，说明创建 R e ad V i e w 时生成该版本的事务还是活跃的，该版本不可以被访问。
    • 如果不在，说明创建 R e ad V i e w 时生成该版本的事务已经被提交，该版本可以被访问。

MVCC 整体操作流程

  1. 首先获取事务自己的版本号，也就是事务 ID
  2. 获取 ReadView
  3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较
  4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照
  5. 最后返回符合规则的数据
    在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次
    Read View 。
    当隔离级别为可重复读（REPEATABLE READ）的时候，就避免了不可重复读，这是因为一个事务只在第一次 select 的时候会
    获取一次ReadView ，而后面所有的 select 都会复用这个 ReadView
半打电话
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
MySQL问答系列之什么情况下会用到临时表
09-09
MySQL在很多情况下都会用到临时表，下面这篇文章主要给大家介绍了关于MySQL在什么情况下会用到临时表的相关资料，文中介绍的非常详细，需要的朋友可以参考借鉴，下面随着小编来一起学习学习吧
MySQL 的自增主键浅谈
hdxx2022的博客
03-02 716
这些都是在当时工程上做出的一些选择，如果严格地按照关系型数据库的设计规范，MySQL 最初的设计造成问题的概率也比较低，只有当被删除的主键被外部系统引用时才会影响数据的一致性，但是今天使用方式的不同却增加出错的可能性，而 MySQL 也在 8.0 中持久化了 AUTO_INCREMENT 以避免该问题的出现。因为重启了 MySQL 的实例，所以内存中的 AUTO_INCREMENT 计数器会被重置成表中的最大值，当我们再向表中插入新的 trades 记录时会重新使用 10 作为主键，主键也就不是单调的了。
表中有自增长主键时，插入数据的方法
lykio_881210的博客
05-10 9541
数据库的表中有自增长主键时（如图所示），我们该怎么插入数据呢？方式一：    当需要插入的数据比较少时，即可以通过一条一条的插入时，我们可以指定该列（id）的值，但是新插入的值不能和已有的值重复，而且必须大于其中最大的一个值方式二：    当需要插入的数据量比较大时，需要时通过储存过程或者其他方式来批量生成数据插入时，显然方式一的方法不可行。那现在又该怎么办呢？    有两种解决方法：    1）...
mysql 添加自动增量_mysql-向现有列添加自动增量,而不会影响当...
weixin_29658015的博客
02-07 107
我想将现有MyISAM1数据库表中的列类型更改为AUTO_INCREMENT.该数据库当前有数千条记录,其中列值非常重要.至关重要的是,当前值不受影响.我想从最新的最高值开始递增.我在phpmyadmin(由phpmyadmin生成的sql)中尝试了此操作,但出现错误.ALTER TABLE `myTable` CHANGE `myCol1` `myCol1` INT(11) NOT NULL A...
mysql索引的数据结构
small_engineer的博客
03-28 6407
为什么使用索引 我们假如不使用索引的话，就像我们左边的这样，造成全文索引 加入索引的话，像我们右边的这样，那么它的速度就会快上很多。 打个比方，假如我们需要查字典的话，索引就像我们的目录一样，没有索引，我们就只能一页一页去找。而加上索引之后，我们可以根据目录来快速查找我们所需要的东西 对字段Col2添加了索引，就相当于在硬盘上为col 2维护了一个索引的数据结构，即这个二叉搜索树。二叉搜索树的每个结点存储的是（K，V)结构，key是Col 2，value是该key所在行的文件指针(地址)。比如:该二
MYSQL问答
weixin_45669016的博客
07-09 118
1、如何选择数据库？ 在MYSQL中，当我们需要在一个数据库下创建基本表时，此时我们可以使用下列语句调用相关数据库： USE <数据库名称>； 如调用ST数据库。 USE ST; 注意：USE语句并不返回任何结果。依赖于使用的客户机，显示某种形式的通知。 2、当不知道DBMS中可以使用的数据库名称时怎么办？ 在MYSQL中可以使用SHOW命令来显示相关信息。即调用下列语句： SHOW DATABASES; 注意：SHOW DATABASES；返回可用数据库的一个列表。包含在这个列表中的可能
Mysql问答
qq_58829088的博客
04-28 788
1.分别写出启动、连接、停止和卸载MySql服务(服务名为MySql)的语句? 考生答案： net start mysql mysql -u root -p net stop mysql mysql -remove 得分：10 分； 2.请用 create index 语句在数据库 db_test 的表 content 中，根据留言标题列的前三个 字符采用默认的索引类型创建一个升序索引 index_subject。 考生答案： mysql>USE db_test ; Database changed
php mysql问答系统_PHP+MYSQL问答系统V3.7
weixin_39925031的博客
01-19 185
经过7个月的漫长等待，whatsns系列之问答系统(前身ask2问答系统)终于迎来3.7版本，此次更新将颠覆以往任何一次发布和更新。ask2问答系统 v3.7 更新日志1 彻底重写代码，采用目前流行的php框架CI开发，完全支持php5.3.4-php7.2。2 新增邀请注册功能，用户可以分享邀请注册地址，小伙伴注册成功后默认会自动关注你，你还可以获得额外的财富值积分点数。3 网页细节调整，修复低...
MySQL问答题第二节
weixin_67655632的博客
07-19 198
1.MySQL的RPM安装通常分为不同的包，包括Server、Common、Client、Devel、Libs、Libs-compat、Test、Source，请写出上述每个包的功能。4.在windows上安装MySQL之后，mysysqld服务器程序、mysql客户机程序、my.init配置文件、mysql系统数据库、二进制日志文件分别保存在什么位置？2.MySQL除了RPM安装之外，在Linux平台上还有那种形式的安装，请给出简单的说明。...
php+mysql问答系统_WeCenter一个开源免费的php+mysql社区问答系统
weixin_42168265的博客
01-28 396
前言之前学习Lucene和ElasticSearch的时候经常逛逛Elastic的中文社区，发现社区做的蛮不错的，风格和ui都比较清新，今天突然看到了一个站和其风格类似，我就肯定是个开源的产品二次开发的，后来发现了WeCenter，一个很不错的社区问答系统简介WeCenter 是一款知识型的社交化开源社区程序，专注于企业和行业社区内容的整理、归类、检索和再发行。为什么选择WeCenter管理中心，...
MySQL问答系列之如何避免ibdata1文件大小暴涨
09-09
MySql innodb如果是共享表空间，ibdata1文件会越来越大，所以下面这篇文章主要给大家介绍了关于MySQL问答系列之如何避免ibdata1文件大小暴涨的相关资料，文中通过示例代码介绍的非常详细，需要的朋友可以参考下
linux运维学习笔记：MySQL运维基础面试问答题42题.pdf
08-18
linux运维学习笔记：MySQL运维基础面试问答题42题
MySQL深入理解MVCC机制(详解)
m0_60500421的博客
05-08 1057
深入理解mvcc机制
java项目之智慧图书管理系统设计与实现(springboot+vue+mysql)
sinat_26552841的博客
05-08 486
智慧图书管理系统设计与实现的主要使用者分为： 管理员权限操作的功能包括对注册读者信息的管理，对图书，对图书留言，对图书借阅记录，对论坛帖子等信息的管理。 读者权限操作的功能包括借阅图书，对图书进行留言或收藏图书，查看论坛帖子信息，查看借阅的图书等。
MYSQL变量的定义与使用
2301_81019087的博客
05-10 468
3.通过变量进行实际操作。
mybatis 跨库查询 mysql
zzhongcy的专栏
05-11 376
跨库，表关联的查询，实现起来很简单：selecta.uidfrom只要在表的前边加上库名即可。
js遇到需要正则匹配来修改img标签+清除行内样式
最新发布
阿乐的博客
05-14 216
遇到特殊的情况就是自带样式，那么方法一就不能用了，因为只会算是从原有字符串上面多加字符串。这个正则表达式就是匹配所有的。//踩坑完毕，可以直接使用。第二行代码按自己需要改改。
MySQL INTO OUTFILE 报错 Failed to open stream: Permission denied
唐大帅的编程之路
05-08 321
MySQL INTO OUTFILE 报错 Failed to open stream: Permission denied
Python，MySQL实现问答系统
05-11
要实现一个问答系统，我们需要以下步骤： 1. 收集问题和答案数据集，可以手动收集或使用已有的数据集。 2. 设计数据库结构，存储问题和答案数据。 3. 使用Python连接MySQL数据库，创建表格并插入数据。 4. 编写Python代码，实现用户输入问题后，从数据库中查询匹配的答案并输出。 下面是一个简单的Python代码示例，实现上述功能： ``` python import mysql.connector # 连接MySQL数据库 mydb = mysql.connector.connect( host="localhost", user="root", password="password", database="qa_system" ) # 创建问题和答案表格 mycursor = mydb.cursor() mycursor.execute("CREATE TABLE questions (id INT AUTO_INCREMENT PRIMARY KEY, question VARCHAR(255), answer VARCHAR(255))") # 插入数据 sql = "INSERT INTO questions (question, answer) VALUES (%s, %s)" val = ("What is Python?", "Python is a programming language.") mycursor.execute(sql, val) mydb.commit() # 查询答案 question = input("What is your question? ") mycursor.execute("SELECT answer FROM questions WHERE question LIKE '%" + question + "%'") result = mycursor.fetchone() if result: print(result[0]) else: print("Sorry, I don't know the answer to that question.") ``` 这只是一个简单的示例，实际应用中还需要考虑更多因素，如数据清洗、文本匹配算法等。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值