面试汇总-MySQL-杂项

news/2024/3/29 22:26:34/文章来源:https://blog.csdn.net/weixin_37672801/article/details/127856993

目录

1、悲观锁和乐观锁

2、数据库关键字的执行顺序是什么?

3、SQL优化

3.1、如何进行sql优化?

3.2、常见的join算法

3.2.1、Hash Join

3.2.2、Merge Join

3.2.3、Nested Loop Join

3.3、Join前后表的数据量对查询性能有什么影响?

4、MyISAM和InnoDB存储引擎区别

5、Mysql一个表最多支持多少个索引

6、MySQL的binlog

6.1、格式

6.1.1、Statement

6.1.2、Row

6.1.3、Mixed

6.2、查看binlog日志文件(mysqlbinlog)

7、delete语句删除数据后,表数据文件大小未发生改变

8、存储过程

9、视图

10、范式

11、​​​​​​​当自增主键id达到最大值时,如果继续插入数据,会是什么结果?

12、表结构设计细节问题

13、MySQL性能知识点

13.1、Exists和in的性能对比

​​​​​​​13.2、Dependent subquery:子查询

​​​​​​​13.3、主从同步时,逻辑日志有什么缺点?


1、悲观锁和乐观锁

        悲观锁:对数据的冲突采取一种悲观的态度,始终假设数据肯定会冲突,所以在数据开始读取的时候就把数据锁定住;

  • 共享锁:读锁,允许多个并发事物读取锁定的资源,可同时读,不可写;
  • 排它锁:写锁,不允许同时写,不允许写的同时读;

        乐观锁:认为数据一般情况下不会造成冲突,在数据进行提交更新的时候,才会对数据的冲突与否进行检测,如果发现冲突了,才返回错误信息。

Mysql乐观锁实现:
        为数据表增加一个版本标识(int),当读取数据时,同时独处版本标识,数据每更新一次,版本标识加1;更新前,需将读取的版本标识与数据库当前版本标识进行对比,相等的情况下才予以更新,否则操作的就是过期数据;

Mysql悲观锁实现:
        首先需关闭mysql的自动提交属性(autocommit),在事务中,只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行,一般SELECT ... 则不受此影响。在不同的事务中同时执行包含上面两个关键字段的查询语句时,后开启的事务会被阻塞,直到先开启的事务关闭。

Java乐观锁实现:
        冲突检测和数据更新。典型的是Compare and swap(CAS),当多个线程尝试使用CAS同时更新同一个变量时,只有其中一个线程能更新变量的值,而其他线程都失败,失败的线程并不会被挂起,而是被告知这次竞争中失败,并可以再次尝试。

Java悲观锁实现:
        Synchronized。

2、数据库关键字的执行顺序是什么?

        From,on,join,where,group by,having,select,distinct,order by,limit

3、SQL优化

3.1、如何进行sql优化?

  • 避免进行全表扫描:

        在where和order by涉及的列上建立索引;

        避免在where子句中对字段进行null值判断(空值判断会放弃使用索引而导致全表扫描)

        避免在where子句中使用!=,<>操作符和来or连接条件(原因同上);

        In和not in也会导致全表扫描;

        模糊查询时的%也会导致全表扫描;

        在where子句中对字段进行表达式函数操作会导致全表扫描

  • 适量使用索引但不能滥用索引;
  • 尽量使用数字型字段而不是字符型:字符型比较时开销较大;
  • 尽可能使用varchar代替char,变长字段存储空间小,在相对较小的空间内搜索效率较高;
  • 查询时应尽量避免使用select *;
  • 避免频繁创建和删除临时表,以减少系统表资源的消耗;
  • 避免大事务操作,提高系统并发能力。

3.2、常见的join算法

3.2.1、Hash Join

        散列连接,适用于join的两个表数据量相差很大的情况;

  • 优化器使用两个表中较小的表(通常是小一点的那个表或数据源)利用连接键(JOIN KEY)在内存中建立散列表,将列数据存储到hash列表中
  • 然后扫描较大的表,同样对JOIN KEY进行HASH后探测散列表,找出与散列表匹配的行。
  • USE_HASH(table_name1 table_name2)提示来强制使用散列连接

3.2.2、Merge Join

        排序合并连接,适用于不等价关联(>,<,>=,<=,<>)HASH_JOIN_ENABLED=false没有索引且数据已经排序的情况

  • 先将关联表的关联列各自做排序,然后从各自的排序表中抽取数据,到另一个排序表中做匹配。
  • 使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接

3.2.3、Nested Loop Join

        嵌套循环连接,适用于驱动表的记录集比较小(<10000)且内表关联列存在索引的情况。JOIN的顺序很重要,驱动表的记录集一定要小,返回结果集的响应时间是最快的。

  • USE_NL(table_name1 table_name2)可强制CBO 执行嵌套循环连接。

3.3、Join前后表的数据量对查询性能有什么影响?

  • 小表驱动大表,小表在前可提高执行效率。

    左连接的驱动表就是左边的那个表,右连接的驱动表就是右边的那个表。

    解释1:通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。

    解释2:驱动表查询出数据需要一条一条的加入到join_buffer中,这需要IO操作,比较耗时,因此如果驱动表比较小,那么效率就高,这是小表驱动大表的一个主要原因

  • 如果内表走索引,性能高,但是如果内表是二级索引,效率也低,因为要回表查主键。
  • 如果内表不走索引,为全表查询,此时小表驱动大表、大表驱动小表性能相差不大。此时,查询成本为双循环。

4、MyISAM和InnoDB存储引擎区别

MyISAM专注性能,InnoDb专注事务。两者最大的区别就是InnoDb支持事务和行锁。

其他:

  • MyISAM为非聚集索引,索引和数据文件分离,索引保存的是数据文件的指针;

        InnoDB为聚集索引,索引和数据文件绑定在一起,必须有主键。

  • MyISAM的存储文件分别为frm(表结构)、MYD(数据文件)、MYI(索引文件);

        InnoDB的存储文件为frm(表结构)、ibd(表的数据和索引)。

  • MyISAM保存了表的总行数、InnoDB未保存表的总行数。
  • 系统崩溃后,MyISAM恢复数据较困难。

一般来说,如果需要事务支持、大量的update或delete操作,则选择InnoDB。

5、Mysql一个表最多支持多少个索引

  • Innodb:

        最多1017列,最多创建64个二级索引,单个索引最多包含16列。

  • Mysiam:

        最多4096列,最多创建64个二级索引,单个索引最多包含16列。

6、MySQL的binlog

        binlog是记录所有数据库表结构变更(例如CREATE、ALTER TABLE…)以及表数据修改(INSERT、UPDATE、DELETE…)的二进制日志。

6.1、格式

6.1.1、Statement

基于SQL语句的复制,每一条会修改数据的SQL都会记录在binlog日志中。

  • 优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。
  • 缺点:必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同的结果。

6.1.2、Row

不记录sql语句及上下文信息,仅保存哪条记录被修改了。

  • 优点:日志会很清楚地记录下每一行数据修改的细节。
  • 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容。

6.1.3、Mixed

        一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。

6.2、查看binlog日志文件(mysqlbinlog)

  • Statement:mysqlbinlog mysql-bin.000001 
  • Row:mysqlbinlog -vv mysql-bin.000001 

7、delete语句删除数据后,表数据文件大小未发生改变

  • drop table table_name:删除表结构和数据,立刻释放磁盘空间。
  • truncate table table_name:删除表数据,立刻释放磁盘空间。
  • delete from table_name:删除全表数据,MyISAM立刻释放磁盘空间,InnoDb不释放空间。
  • delete from table_name where:带条件删除数据,均不释放磁盘空间。
  • delete后执行optimize table table_name,可立即释放磁盘空间。

        delete操作后,如果磁盘空间未被释放,会在下次增加数据时继续使用。

8、存储过程

        存储过程是能完成一定操作的一组SQL语句。

优势:

        一般的SQL语句每一次使用都需要进行编译,而存储过程只在创建时进行编译,之后执行可直接使用,进而提高数据库的执行速度。

        封装复杂的数据库操作。

        可以重复使用,减少数据库开发人员的工作量。

        安全性高,可设定指定用户使用权限。

9、视图

Select *from (select *from table1 where age = 11) where height > 170

视图可以理解成虚拟的表,是由数据库中实际的表通过select查询得来。比如,上面所示的SQL语句中,红色字体就可以抽象成一个视图。

通过:create view age_11 as select *from table1 where age = 11,就得到了一个名称为age_11的视图,在后续的操作中就可以直接使用age_11视图(select *from age_11 where height >170)。

10、​​​​​​​范式

规范的数据库设计应该遵守的规则和指导方法。

  • 第一范式:属性不可分割,每个字段都应该是不可拆分的
  • 第二范式:主键约束,要求数据库的每一个实例或者行必须可以被唯一的区分,即能根据主键值获取到唯一的其他属性列数据。
  • 第三范式:外键约束,要求表中不能有其他表中存在的、存储相同信息的非主键字段

11、​​​​​​​当自增主键id达到最大值时,如果继续插入数据,会是什么结果?

        再次插入时,主键自增ID为最大id,报主键冲突的错误。

12、表结构设计细节问题

  • 在对unsigned修饰的整型字段进行减法操作时,如果计算的结果超出了范围(小于0或者大于最大整型),mysql就会报错。可通过设置数据库参数sql_mode = NO_UNSIGNED_SUBTRACTION允许相减的结果为signed来解决这个问题。
  • 用自增整型做主键时,一律使用BIGINT,而不是INT。减少后期数据量大时表结构调整。
  • MySQL8.0版本前,整型自增不持久化,可能存在回溯问题。

        正常情况下,自增主键为1,2,3,删除3后,下一个肯定为4,主键不回溯;

        回溯情况下,自增主键为1,2,3,删除3后,数据库重启,那下一个主键值为3。

  • 不要使用Float,Double两种浮点类型,后续MySQL不再支持。
  • 如果想要存储emoji表情,最好把列字符集设置为UTF8MB4。
  • 密码存储,加密时,推荐使用动态盐值 + 非固定加密算法的方式加密。
  • TIMESTAMP和DATETIME:推荐使用DATETIME

        TIMESTAMP优点是带有时区属性,缺点是它的最大值2038年已经快要到了。此外TIEMSTAMP需要通过时区计算时间,调用底层函数__tz_convert时会加锁,高并发访问时,性能也会有问题。

  • 表结构设计时,推荐为每一个核心业务表,设计一个last_modify_date字段记录最后修改时间。
  • 核心业务表,一定不要用自增键做主键。

        问题:回溯、分布式环境下存在主键不唯一,公开主键值,容易泄露数据。

  • Innodb_autoinc_lock_mode控制自增锁持有的时间。

        值为1:每条SQL结束后释放自增锁;

        值为2:每次自增释放自增锁。

        举例:如果一条Insert语句插入了10条数据,如果mode值为1,那么只需要进行一次获取/释放锁的操作;如果mode值为2,那么久需要进行10次获取/释放锁的操作。

  • UUID(无序):时间低位(12位) + 时间中位(16位) + 时间高位(32位) + UUID版本号(4位);

        UUID_TO_BIN:将时间高位放在前面,解决了UUID插入时乱序问题。二进制存储,精简了存储空间。

        BIN_TO_UUID:将二进制值反转为UUID字符串。

  • 尽量减少数据表的数据冗余,冗余数据可能会增加维护难度。
  • 页:页大小为16K,一个个页组成了每张表的表空间。

        一个页中存放的记录数越多,数据库性能越好。页存放在磁盘上,MySQL数据库要先将磁盘中的页读取到内存缓冲池,然后以页为单位来读取和管理数据。页中存放数据越多,可减少磁盘IO,从而提升性能。

  • 页压缩技术,可将16K的页压缩成8K或者4K,以减少磁盘IO时的请求,从而提升数据库的整体性能。

        ROW_FORMAT=COMPRESSED:启动页压缩;

        KEY_BLOCK_SIZE=8:压缩大小。

        COMPRESS页压缩:对性能不敏感,只对存储空间有要求(比如日志表、监控表等)。

        TPC压缩:对存储有压缩需求,又希望不影响性能,推荐使用TPC压缩。

13、MySQL性能知识点

13.1、Exists和in的性能对比

        性能的差异在于驱动表的不同。

        如果主查询中的表较大且又有索引,子查询得出的结果集记录较少时,应该用in;反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。

  • in的执行顺序:

        select * from A where A.ID in(select B.ID from B )

        首先执行一次子查询,子查询先产生结果集;

        然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出。

  • exists的执行顺序:

        select * from A  where exists(select 1 from B where A.ID=B.ID)

        首先执行一次外部查询;

        对于外部查询中的每一行分别执行一次子查询,而且每次执行子查询时都会引用外部查询中当前行的值;

​​​​​​​13.2、Dependent subquery:子查询

        当执行计划中有DEPENDENT SUBQUERY时,表示这是一个依赖子查询,执行速度通常特别慢,需要手动转化成两张表的关联查询:Join。

​​​​​​​13.3、主从同步时,逻辑日志有什么缺点?

        事务不能太大,否则会导致二进制文件很大,事务提交慢。

        把大事务拆成小事务。

以上内容为个人学习理解,如有问题,欢迎在评论区指出。

部分内容截取自网络,如有侵权,联系作者删除。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.luyixian.cn/news_show_38719.aspx

如若内容造成侵权/违法违规/事实不符,请联系dt猫网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

JavaScript_BOM

JavaScript_BOM 学习路线&#xff1a;JavaScript_BOM->Window对象->confirm()、setInterval()、setTimeout()->History、Location->闪烁的灯泡 概述 BOM&#xff1a;Browser Object Model 浏览器对象模型。也就是 JavaScript 将浏览器的各个组成部分封装为对象。…

【Shell 脚本速成】06、Shell 数组详解

目录 一、数组介绍 二、数组定义 三、数组赋值方式 四、数组取值 案例演示 五、关联数组 5.1 定义管理数组 5.2 关联数组赋值 5.3 管理数组取值 5.4 综合案例 有这样一个现实问题&#xff1a;一个班级学员信息系统&#xff0c;要求存储学员ID、NAME、SCORE、AGE、GE…

文本检测与识别技术的应用现状及产业案例

文本检测与识别技术的产业应用现状 文本是人类文明的视觉和物理载体&#xff0c;文本的检测和识别使视觉与对其内容的理解进一步联系起来。在这一部分中&#xff0c;我们列出并分析了对提高我们的生产力和生活质量产生或将产生重大影响的最杰出的项目。 自动数据录入&#xff…

鼠标监视 | 拖拽方块 | Vue

title: 拖拽功能 tags: Vue categories: JavaScript abbrlink: 18a433ce date: 2022-11-26 21:14:19 效果 代码 <!DOCTYPE html> <html lang"en"><head><meta charset"UTF-8"><meta http-equiv"X-UA-Compatible" co…

RabbitMQ 快速入门-消息的收发

RabbitMQ 快速入门-消息的收发准备工作一、Connection 方式1. 生产者测试类2. 消费者测试类注意二、RabbitTemplate 方式1. 生产者测试类2. 创建队列3. 消费者注意准备工作 推荐创建两个 SpringBoot 项目&#xff0c;一个作为生产者&#xff0c;另一个作为消费者 也可使用 Ma…

[附源码]SSM计算机毕业设计校园征兵及退役复原管理系统JAVA

项目运行 环境配置&#xff1a; Jdk1.8 Tomcat7.0 Mysql HBuilderX&#xff08;Webstorm也行&#xff09; Eclispe&#xff08;IntelliJ IDEA,Eclispe,MyEclispe,Sts都支持&#xff09;。 项目技术&#xff1a; SSM mybatis Maven Vue 等等组成&#xff0c;B/S模式 M…

若依、多选框前后端处理,MyBatis处理多对多关系

背景 很经典的CRUD&#xff0c;整理下笔记。 后端 实体类 /*** 专业方向主键&#xff0c;用于下拉框搜索*/private Long disasterTypeId;/*** 专业方向*/private List<DisasterType> disasterType;业务层 /*** 新增专家信息库** param expertInfo 专家信息库* return 结…

gcc: error trying to exec ‘cc1plus‘: execvp: no such file or directory

该问题是缺少gcc文件&#xff0c;或者gcc与g版本不匹配问题 问题来源1&#xff1a;系统文件的缺失或者不匹配 按照如下方法测试 安装主要是利用apt-get安装&#xff0c;如果没有root权限的话&#xff0c;只能下载源码进行编译安装&#xff0c;然后添加路径环境&#xff0c;安…

Java基础概念-03-字面量

在有些资料中&#xff0c;会把字面量叫做&#xff0c;常量&#xff0c;或字面值常量&#xff0c;但最正确的叫法还是&#xff0c;字面量 小数在 Java 中也称为浮点数 下面是课本中的介绍&#xff1a; 常量是指在程序运行过程中&#xff0c;其值不能被修改的量&#xff0c;Java…

核心解读 - 2022版智慧城市数字孪生标准化白皮书

核心解读 - 2022版智慧城市数字孪生标准化白皮书前言&#xff1a;城市数字孪生基本概念一、城市数字孪生概述1、城市数字孪生内涵及概念模型2、城市数字孪生典型特征3、城市数字孪生相关方4、城市数字孪生技术参考架构5、城市数字孪生关键技术二、智慧城市数字孪生发展现状1、政…

线性表的顺序实现【C语言版的真代码】

顺序表线性表顺序表顺序表的概念及其结构顺序表基本操作顺序表的初始化顺序表的插入顺序表的删除顺序表的查找线性表 线性表&#xff1a;一个线性表是含n个数据元素的有限序列。 它的逻辑结构要求是线性的&#xff0c;但其存储结构并没有做要求&#xff0c;即逻辑结构类似于如…

m基于NSGAII优化算法的微网系统的多目标优化规划matlab仿真

目录 1.算法描述 2.仿真效果预览 3.MATLAB核心程序 4.完整MATLAB 1.算法描述 NSGA-II是基于的非支配排序的方法,在NSGA上进行改进&#xff0c;也是多目标进化优化领域一个里程碑式的一个算法。 NSGA-Ⅱ算法是 Srinivas 和 Deb 于 2000 年在 NSGA 的基础上提出的&#xff0c…

预约陪诊系统开发,跨省就医也能省时省力

就医陪护服务这几年一直受到人们的好评&#xff0c;有了预约陪诊系统开发之后一些无法居家照顾老人的子女可以通过就医陪护为老人预约服务&#xff0c;预约陪诊平台的出现还让陪诊员有了正规的接单平台&#xff0c;不仅方便了人们下单找就医陪诊员还可以对陪诊人员实行正规的管…

解决nginx: [emerg] unknown directive “stream“ in /etc/nginx/nginx.conf问题

文章目录1.未报错时nginx配置&#xff1a;2.报错时nginx配置&#xff1a;3.增加配置报错&#xff1a;4.增加配置位置如下&#xff1a;5.解决办法&#xff1a;6.测试&#xff1a;nginx -t1.未报错时nginx配置&#xff1a; #user nginx; user root; worker_processes auto;er…

群晖外网访问终极解决方法:IPV6+阿里云ddns+ddnsto

写在前面的话 受够了群晖的quickconnet的小水管了&#xff0c;急需一个新的解决方法&#xff0c;这是后发现移动没有公网IP&#xff0c;只有ipv6&#xff08;公网的&#xff09;&#xff0c;时候有小伙伴要问&#xff0c;要是没有ipv6就没办法访问群晖了吗&#xff1f; 不&…

微信截图无法发送,也发不出电脑上的图片

微信截图无法发送&#xff0c;也发不出电脑上的图片 现象 今天微信突然出现这个问题&#xff0c;怎么改设置都调不好&#xff0c;卸载重装都不行&#xff0c;最后发现&#xff0c;微信的消息目录中&#xff0c;一些文件无法删除&#xff0c;提示“文件或目录损坏且无法读取”…

TinyML:是否是FPGA在人工智能方面的最佳应用?

TinyML 也是机器学习的一种&#xff0c;他的特点就是缩小深度学习网络可以在微型硬件中使用&#xff0c;主要应用在智能设备上。超低功耗嵌入式设备正在“入侵”我们的世界&#xff0c;借助新的嵌入式机器学习框架&#xff0c;它们将进一步推动人工智能驱动的物联网设备的普及。…

sipp: bind_local;watchdog timer trip

文章目录作为服务端时&#xff0c;source ip 随机的问题命令示例bind_localwatchdog_minor_maxtriggers作为服务端时&#xff0c;source ip 随机的问题 https://sipp.sourceforge.net/doc/reference.html https://github.com/SIPp/sipp/issues/83 https://github.com/SIPp/sip…

虹科分享 | 网络流量监控 | 使用 ntopng 收件人和端点进行灵活的警报处理

在之前&#xff0c;ntopng引擎对所有警报的配置是单一的&#xff1a;进入偏好页面并指定警报的发送地点。但这是不理想的&#xff0c;原因有很多&#xff1a;包括不可能在不同的渠道向不同的收件人发送警报&#xff0c;或有选择地决定何时发送警报。 出于这个原因&#xff0c;…

北大惠普金融指数-匹配企业绿色创新指数2011-2020年:企业名称、年份、行业分类等多指标数据

1、数据来源&#xff1a;北京大学数字金融中心、国家统计局、国家专利产权局等部门公开数据 2、时间跨度&#xff1a;2011-2020年 3、区域范围&#xff1a;全国 4、指标说明&#xff1a; 中国内地31个省&#xff08;直辖市、自治区&#xff0c;简称“省”&#xff09;、337…