MySQL常考知识点

2023-09-21 11:37:25

MySQL常考知识点

索引的基本原理

索引⽤来快速地寻找那些具有特定值的记录。如果没有索引，⼀般来说执⾏查询时遍历整张表。
索引的原理：就是把⽆序的数据变成有序的查询

  1. 把创建了索引的列的内容进⾏排序
  2. 对排序结果⽣成倒排表
  3. 在倒排表内容上拼上数据地址链
  4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从⽽拿到具体数据

索引设计的原则

查询更快、占⽤空间更⼩

  1. 适合索引的列是出现在where⼦句中的列，或者连接⼦句中指定的列
  2. 基数较⼩的表，索引效果较差，没有必要在此列建⽴索引
  3. 使⽤短索引，如果对⻓字符串列进⾏索引，应该指定⼀个前缀⻓度，这样能够节省⼤量索引空间，如果搜索词超过索引前缀⻓度，则使⽤索引排除不匹配的⾏，然后检查其余⾏是否可能匹配。
  4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进⾏更新甚⾄重构，索引列越多，这个时间就会越⻓。所以只保持需要的索引有利于查询即可。
  5. 定义有外键的数据列⼀定要建⽴索引。
  6. 更新频繁字段不适合创建索引
  7. 若是不能有效区分数据的列不适合做索引列(如性别，男⼥未知，最多也就三种，区分度实在太低)
  8. 尽量的扩展索引，不要新建索引。⽐如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
  9. 对于那些查询中很少涉及的列，重复值⽐较多的列不要建⽴索引。
  10. 对于定义为text、image和bit的数据类型的列不要建⽴索引。

事务的基本特性和隔离级别

事务基本特性ACID分别是：
原⼦性： 指的是⼀个事务中的操作要么全部成功，要么全部失败。
⼀致性： 指的是数据库总是从⼀个⼀致性的状态转换到另外⼀个⼀致性的状态。⽐如A转账给B 100块钱，假设A只有90块，⽀付之前我们数据库⾥的数据都是符合约束的,但是如果事务执⾏成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这⾥我们说事务提供了⼀致性的保证
隔离性： 指的是⼀个事务的修改在最终提交前，对其他事务是不可⻅的。
持久性： 指的是⼀旦事务提交，所做的修改就会永久保存到数据库中。

隔离性有4个隔离级别，分别是：

  1. read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。⽤户本来应该读取到id=1的⽤户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。
  2. read commit 读已提交，两次读取结果不⼀致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务。⽤户开启事务读取id=1⽤户，查询到age=10，再次读取发现结果=20，在同⼀个事务⾥同⼀个查询读取到不同的结果叫做不可重复读。
  3. repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都⼀样，但是有可能产⽣幻读。
  4. serializable 串⾏，⼀般是不会使⽤的，他会给每⼀⾏读取的数据加锁，会导致⼤量超时和锁竞争
    的问题。

什么是MVCC

MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使⽤READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执⾏普通的SEELCT操作时访问记录的版本链的过程。可以使不同事务的读-写、写-读操作并发执⾏，从⽽提升系统性能。
READ COMMITTD、REPEATABLE READ这两个隔离级别的⼀个很⼤不同就是：⽣成ReadView的时机不同，READ COMMITTD在每⼀次进⾏普通SELECT操作前都会⽣成⼀个ReadView，⽽REPEATABLEREAD只在第⼀次进⾏普通SELECT操作前⽣成⼀个ReadView，之后的查询操作都重复使⽤这个ReadView就好了。

简述MyISAM和InnoDB的区别

MyISAM：

  • 不⽀持事务，但是每次查询都是原⼦的；
  • ⽀持表级锁，即每次操作是对整个表加锁；
  • 存储表的总⾏数；
  • ⼀个MYISAM表有三个⽂件：索引⽂件、表结构⽂件、数据⽂件；
  • 采⽤⾮聚集索引，索引⽂件的数据域存储指向数据⽂件的指针。辅索引与主索引基本⼀致，但是辅索引不⽤保证唯⼀性。

InnoDb：

  • ⽀持ACID的事务，⽀持事务的四种隔离级别；
  • ⽀持⾏级锁及外键约束：因此可以⽀持写并发；
  • 不存储总⾏数；
  • ⼀个InnoDb引擎存储在⼀个⽂件空间（共享表空间，表⼤⼩不受操作系统控制，⼀个表可能分布在多个⽂件⾥），也有可能为多个（设置为独⽴表空，表⼤⼩受操作系统⽂件⼤⼩限制，⼀般为2G），受操作系统⽂件⼤⼩的限制；
  • 主键索引采⽤聚集索引（索引的数据域存储数据⽂件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使⽤⾃增主键，防⽌插⼊数据时，为维持B+树结构，⽂件的⼤调整。

Explain语句结果中各个字段分表表示什么

列名描述
id查询语句中每出现⼀个SELECT关键字，MySQL就会为它分配⼀个唯⼀的id值，某些⼦查询会被优化为join查询，那么出现的id会⼀样
select_typeSELECT关键字对应的那个查询的类型
table表名
partitions匹配的分区信息
type针对单表的查询⽅式（全表扫描、索引）
possible_keys可能⽤到的索引
key实际上使⽤的索引
key_len实际使⽤到的索引⻓度
ref当使⽤索引列等值查询时，与索引列进⾏等值匹配的对象信息
rows预估的需要读取的记录条数
filtered某个表经过搜索条件过滤后剩余记录条数的百分⽐
Extra⼀些额外的信息，⽐如排序等

索引覆盖是什么

索引覆盖就是⼀个SQL在执⾏时，可以利⽤索引来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL⾛完索引后不⽤回表了，所需要的字段都在当前索引的叶⼦节点上存在，可以直接作为结果返回了

最左前缀原则是什么

当⼀个SQL想要利⽤索引是，就⼀定要提供该索引所对应的字段中最左边的字段，也就是排在最前⾯的字段，⽐如针对a,b,c三个字段建⽴了⼀个联合索引，那么在写⼀个sql时就⼀定要提供a字段的条件，这样才能⽤到联合索引，这是由于在建⽴a,b,c三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去⽐较⼤⼩进⾏排序的，所以如果想要利⽤B+树进⾏快速查找也得符合这个规则

B树和B+树的区别，为什么Mysql使⽤B+树

B树的特点：

  1. 节点排序
  2. ⼀个节点了可以存多个元素，多个元素也排序了
    B+树的特点：
  3. 拥有B树的特点
  4. 叶⼦节点之间有指针
  5. ⾮叶⼦节点上的元素在叶⼦节点上都冗余了，也就是叶⼦节点中存储了所有的元素，并且排好顺序

Mysql索引使⽤的是B+树，因为索引是⽤来加快查询的，⽽B+树通过对数据进⾏排序所以是可以提⾼查询速度的，然后通过⼀个节点中可以存储多个元素，从⽽可以使得B+树的⾼度不会太⾼，在Mysql中⼀个Innodb⻚就是⼀个B+树节点，⼀个Innodb⻚默认16kb，所以⼀般情况下⼀颗两层的B+树可以存2000万⾏左右的数据，然后通过利⽤B+树叶⼦节点存储了所有数据并且进⾏了排序，并且叶⼦节点之间有指针，可以很好的⽀持全表扫描，范围查找等SQL语句。

Mysql锁有哪些，如何理解

按锁粒度分类：

  1. ⾏锁：锁某⾏数据，锁粒度最⼩，并发度⾼
  2. 表锁：锁整张表，锁粒度最⼤，并发度低
  3. 间隙锁：锁的是⼀个区间

还可以分为：

  1. 共享锁：也就是读锁，⼀个事务给某⾏数据加了读锁，其他事务也可以读，但是不能写
  2. 排它锁：也就是写锁，⼀个事务给某⾏数据加了写锁，其他事务不能读，也不能写

还可以分为：

  1. 乐观锁：并不会真正的去锁某⾏记录，⽽是通过⼀个版本号来实现的
  2. 悲观锁：上⾯所的⾏锁、表锁等都是悲观锁

在事务的隔离级别实现中，就需要利⽤锁来解决幻读

Mysql慢查询该如何优化？

  1. 检查是否⾛了索引，如果没有则优化SQL利⽤索引
  2. 检查所利⽤的索引，是否是最优索引
  3. 检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
  4. 检查表中数据是否过多，是否应该进⾏分库分表了
  5. 检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源
更多推荐

中文读唇总动员：CNVSRC 2023 视觉语音识别挑战赛启动

由NCMMSC2023组委会发起，清华大学、北京邮电大学、海天瑞声、语音之家共同主办的CNVSRC2023中文连续视觉语音识别挑战赛即日启动，诚邀参与报名。赛事官网：http://cnceleb.org/competition视觉语音识别，也称唇语识别，是一项通过口唇动作来推断发音内容的技术。该技术在公共安全、助老助残

数学建模__动态规划

动态规划就是，将任务每一步均记录下来，以便将来重复使用时能够直接调用问题描述：给定n个物品，每个物品的重量是Wi,价值是Vi，但是背包最多能装下capacity重量的物品，问我们如何选择才能利益最大化。这里涉及到建模过程，本文章主要讲解代码实现，建模过程较为简略。使用dp[i][j]来表示在容量为j的情况下，前i件物品

华为云云耀云服务器L实例评测｜基于云耀云服务器在Docker上部署nginx服务

文章目录1、服务介绍云耀云服务器Docker介绍Docker-Compse介绍2、在云耀云服务器安装Docker3、通过Dockerrun命令运行nginx服务4、在云耀云服务器安装docker-compose5、通过docker-compose方式启动nginx服务1、服务介绍云耀云服务器云耀云服务器（HyperEl

Vue路由与nodejs环境搭建

一、Vue路由1.1SPA简介在实现路由之前我们先了解SPA是什么?SPA：单页Web应用（singlepageapplication，SPA），就是只有一个Web页面的应用。是加载单个HTML页面，并在用户与应用程序交互时动态更新该页面的Web应用程序单页面应用程序：只有第一次会加载页面,以后的每次请求,仅仅是获取必

VS Code时间轴插件：MarkWhen

文章目录简介时间格式事件格式简介MarkWhen是一款文本转时间轴的工具，非常好用，也十分炫酷。可在VSCode中搜索插件MarkWhen，点击安装，然后新建一个.mw后缀的文件，就可以使用了，下面举一个简单的例子#Holiday:blue2023-09-10:周日#Holiday9/17/2023:周日#Holida

day21算法

常见的七种查找算法：​数据结构是数据存储的方式，算法是数据计算的方式。所以在开发中，算法和数据结构息息相关。今天的讲义中会涉及部分数据结构的专业名词，如果各位铁粉有疑惑，可以先看一下哥们后面录制的数据结构，再回头看算法。1.基本查找​也叫做顺序查找​说明：顺序查找适合于存储结构为数组或者链表。基本思想：顺序查找也称为线

计算机证书java

利润importjava.util.Scanner;publicclassTest{publicstaticvoidmain(String[]args){//profit代表输入的利润，bonus代表奖金额doubleprofit=0,bonus=0;System.out.print("请输入本年利润（万）：");Sc

Git 的基本概念和使用方式

Git是一种分布式版本控制系统，它可以帮助开发者管理代码，跟踪代码的变化并协作开发。以下是Git的一些基本概念和使用方式：1.仓库（repository）：存放代码的地方，包括本地仓库和远程仓库。2.分支（branch）：用来指代不同代码的开发路径，可以独立开发、测试和合并。3.提交（commit）：每次修改代码时，需

selenium自动化测试-登录网站用户

昨天学习了selenium自动化测试工具的入门，知道了Selenium是用于自动化控制浏览器做各种操作，打开网页，点击按钮，输入表单等等。今天学习通过selenium自动化测试工具自动登录某网站用户操作。第一步：确定目标网址比如：天天基金网站登录页面"https://login.1234567.com.cn/login

SpringBoot中pom.xml配置详解

🔔简介pom.xml是Maven项目的核心配置文件，用于管理项目的依赖、插件、构建配置等。在SpringBoot项目中，pom.xml文件也扮演着重要的角色。在pom.xml文件中，可以配置项目的资源文件。在SpringBoot的pom.xml文件中，通常会有一个标签文件，用于指定资源文件的路径和名称。🔔基本配置�

关于时空数据的培训 GAN：实用指南（第 01/3 部分）

第1部分：深入了解GAN训练中最臭名昭著的不稳定性。一、说明GAN是迄今为止最受欢迎的深度生成模型，主要是因为它们最近在图像生成任务上产生了令人难以置信的结果。然而，GAN并不容易训练，因为它们的基本设计引入了无数的不稳定性。如果你尝试过用MNIST以外的任何东西训练GAN，你很快就会意识到，所有关于训练他们的痛苦（以

热文推荐
  1. blender怎么设置中文界面

    你们知道Blender软件是什么吗？你知道blender怎么设置中文界面吗？Blender是个GNU的3D绘图软件，建模、算图、动画等功能都相当的完整，可以说已经具有了一般商业软件的规模。Blender大部分的功能都有热键，操作起来相当地轻快；而由于几乎所有的功能按钮鼠标移上去一段时间都会出现详细说明，也多少弥补了操作

  2. Blender Morph Targets

    推荐：用NSDT编辑器快速搭建可编程3D场景在Blender中，MorphTarget被称为ShapeKey，即形状键，是将网格从一种形状变形为另一种形状的工具。每个对象都被分配了一个基本形状，然后可以有许多可以变形的形状键。形状键通常用于面部动画和肌肉，但在动画中也有很多用途。本文包含了你需要了解的有关Blender

  3. 基于协同过滤算法的旅游推荐系统

    博主主页：猫头鹰源码博主简介：Java领域优质创作者、CSDN博客专家、公司架构师、全网粉丝5万+、专注Java技术领域和毕业设计项目实战主要内容：毕业设计(Javaweb项目|小程序等)、简历模板、学习资料、面试题库、技术咨询文末联系获取项目介绍：该系统基于springboot技术，数据层为MyBatis，mysql

  4. python-爬虫-requests

    安装模块pipinstallrequests在jupyternotebook里使用Shift+Tab查看requestsrequests库的主要方法方法解释requests.requset()构造一个请求，支持以下各种方法requests.get()获取HTML的主要方法requests.head()获取HTML头部信

  5. ffmpeg 特效 转场 放大缩小

    案例ffmpeg\-iinput.mp4\-iimage1.png\-iimage2.png\-filter_complex\[1:v]scale=100:100[img1];\[2:v]scale=1280:720[img2];\[0:v][img1]overlay=(main_w-overlay_w)/2:(mai

  6. A Span-based Multi-Modal Attention Network for joint entity-relationextraction

    原文链接：https://www.sciencedirect.com/science/article/pii/S0950705122013247?via%3DihubKnowledge-BasedSystems2023介绍作者认为当前基于span的关系提取方法都太关注于span内部的语义，忽略了span与span之间以

  7. MySQL 深度分页性能急剧下降，该如何优化？

    1、背景mysql使用select*limitoffset,rows分页在深度分页的情况下。性能急剧下降。例如：select*的情况下直接⽤limit600000，10扫描的是约60万条数据，并且是需要回表60W次，也就是说⼤部分性能都耗在随机访问上，到头来只⽤到10条数据(总共取600010条数据只留10条记录)2、

  8. vue3 - 项目集成vue-i18n国际化和Element Plus 国际化

    GitHubDemo地址在线预览vue3-项目集成vue-i18n国际化和ElementPlus国际化[GitHubDemo地址](https://github.com/iotjin/jh-vue3-admin)[在线预览](https://iotjin.github.io/jh-vue3-admin)一、vue-i1

  9. DIY 一个汽车方向盘游戏外设（MMOS OSW DIY）

    OSW-MMOS直驱方向盘DIY过程记录-简书(jianshu.com)DIY一个汽车方向盘游戏外设（MMOSOSWDIY）首先讲一下这个直驱系统大概的框架，首先是电脑，电脑里装MMOS的软件(这个软件国内高手把它汉化了的)，电脑通过USB线，连接一个stm32板，这个stm32板上刷MMOS的固件，这个STM32板再