mysql sql优化java面试

转载

编程梦想编织者 2024-10-05 18:04:14

文章标签 mysql sql优化java面试 mysql 数据数据库 sql 文章分类 MySQL 数据库

数据库的面试

一.关系型数据库和非关系型数据库区别
二.数据库存储引擎
三.事务的四个特性
四.oracle和myql的区别(只列举了一部分)
五.char和varchar的区别
六.不同索引类型的区别
七.选择索引的原则
八.导致SQL执行慢的原因
九.物理备份的方式
十.有哪些备份类型
十一.Mysql备份工具
十二.备份策略
十三.mysql优化:性能优化,安全优化(应用加固,系统加固)
十四.Mysql脏数据的概念?如何避免脏数据的产生?
十五.Mysql的bin-log日志的作用?里边有什么内容
十六.Mysql的慢查询日志
十七.GTID的工作原理
十八.读写分离的好处
十九.Mysql主从,master宕机,如何进行切换?
二十.主从复制的作用?
二十一.主从复制的原理
二十三.Mysql有哪些数据类型(列举一部分)
二十四.Mysql数据库cpu飙升到500%的话,你怎么做?
二十五.Mysql有关权限的表格都有哪些

一.关系型数据库和非关系型数据库区别

关系型数据库:
1.关系型数据库在存储数据时实际就是采用的一张二维表(比如和excel表格一样)
2.市场占有量较大的是Mysql和Oracle数据库,而互联网场景最常用的是Mysql数据库,就连阿里都换成了Mysql数据库
3.通过sql结构化查询语言来存取,管理关系型数据库的数据
4. 关系型数据库在保持数据安全和数据一致性方面很强，遵循 ACID 理论 ACID指的的事务的4大特性
5.存放在磁盘
优点:
1.易于维护:都是使用表结构,格式一致
2.使用方便:SQL语言通用,可用于复杂查询
3.复杂操作:支持SQL,可用于一个表以及多个表之间非常复杂的查询
缺点:
1.读写能力较差,尤其是海量数据的高效率读写
2.固定的表结构,灵活度稍欠
3.高并发读写需求,传统关系型数据库来说,硬盘I/O是一个很大的瓶颈

非关系型数据库
1.NoSQL 数据库不是否定关系型数据库，而是作为关系数据库的一个重要补充。
2.NoSQL 数据库为了灵活及高性能、高并发而生，忽略影响高性能、高并发的功能。
3.在NoSQL 数据库领域，当今的最典型产品为 Redis（持久化缓存）、MongoDB、Memcached（纯内存）等。
4.NoSQL 数据库没有标准的查询语言（SQL），通常使用数据接口或者查询API。
优点:
1.格式灵活:存储数据的格式可以使key,value形式,文档形式,图片形式等
2.速度快:nosql可以使用硬盘或者随机存储器作为载体,比如内存,而关系型数据库只能使用硬盘
3.高扩展性
4.成本低:nosql数据库部署简单,基本都是开源软件
缺点:
1.不支持sql语言,学习和使用成本高
2.无事务处理
3.数据结构相对复杂,复杂查询方面稍欠

非关系型数据库中的Redis:

支持内存缓存，这个功能相当于 Memcached。
支持持久化存储，这个功能相当于 MemcacheDB。
数据类型更丰富。比其他 Key-Value 库功能更强。
支持主从集群，分布式。

二.数据库存储引擎

这里只说InnoDB和MyISAM
1.InnoDB存储引擎:默认引擎,最常用的InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键
InnoDB特点:支持事务处理,支持外键,支持崩溃修复和并发控制. 如果需要对事物的完整性要求较高(比如银行),要求实现并发控制(比如售票),那么选择InnoDB有很大的优势,如果需要频繁更新,删除操作的数据库,也选择InnoDB,因为支持事务的提交(commit)和回滚(rollback)

2.MyISAM存储引擎
MyISAM基于ISAM存储引擎,并对其进行扩展,她是在WEB,数据仓储和其他应用环境下最常用的数据存储之一,又有较高的插入,查询速度,但不支持事务
MyISAM特点:插入速度快,空间和内存使用比较低,如果表主要是用于插入新记录和读出记录,那么选择MyiSAM能实现处理高效率,如果应用的完整性,并发性要求比较低,也可以使用

如何选择引擎：如果要提供提交、回滚、并要求实现并发控制，InnoDB是一个好的选择；如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

三.事务的四个特性

1 、原子性 (atomicity）:强调事务的不可分割.
2 、一致性（consistency）:事务的执行的前后数据的完整性保持一致.
3 、隔离性（isolation）:一个事务执行的过程中,不应该受到其他事务的干扰
4 、持续性（durability） :事务一旦结束,数据就要记录到永久存储器中,通常是保存进物理数据库

四.oracle和myql的区别(只列举了一部分)

1、Oracle是大型数据库，而MySQL是中小型数据库。但是MySQL是开源的，但是Oracle是收费的，而且比较贵。
2、Oracle的内存占有量非常大，而mysql非常小
3、MySQL支持主键自增长，指定主键为auto increment，插入时会自动增长。Oracle主键一般使用序列。
4、MySQL字符串可以使用双引号包起来，而Oracle只可以单引号
5、MySQL分页用limit关键字，而Oracle使用rownum字段表明位置，而且只能使用小于，不能使用大于。
6、Oracle在处理长字符串的时候,长度是小于等于4000个字节,如果要插入更长的字符串,考虑用CLOB类型,插入修改记录前要做进行修改和长度的判断,如果为空,如果长度超出返回操作处理.（CLOB类型是内置类型，它一般都作为某一行中的一列,有些数据库也有别名）
7、MySQL中0、1判断真假，Oracle中true false
8、MySQL中命令默认commit,但是Oracle需要手动提交
9、MySQL在windows环境下大小写不敏感在unix,linux环境下区分大小写，Oracle不区分

五.char和varchar的区别

char表示定长字符串，长度是固定的；如果插入数据的长度小于char的固定长度时，则用空格填充；因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间。
varchar表示可变长字符串，长度是可变的；插入的数据是多长，就按照多长来存储；varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间。
结合性能角度（char更快），节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。
（至于为什么长度不固定：十分频繁改变的column（字段）。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的）

六.不同索引类型的区别

1.普通索引(INDEX):索引列值可重复,常用于过滤数据,例如:以商品作为索引,检索种类为"手机"的商品
2.唯一索引(UNIQUE):索引列值可以为NULL,但是必须唯一,主要用于标识一列数据不允许重复的特性,相比主键索引不常用于检索的场景
3.主键索引(PRIMARY KEY):索引列值必须唯一,不能为NULL,一个表之鞥有一个主键索引,昰行的唯一标识,因而其主要用途是检索特定数据
4全文索引(FULL TEXT):给每个字段创建索引,效率较低,常用于文本中内容检索

七.选择索引的原则

1.常用于查询条件的字段适合作为索引,例如where语句和join语句中出现的列
2.唯一性太差的字段不适合作为索引,例如性别
3.更新过于频繁(更新频率远高于检索频率)的字段不适合作为索引
4.使用索引的好处是索引通过一定的算法建立了索引值与列值直接的联系,可以通过索引直接获取对应的行数据,而无需进行全表搜索,因而加快了检索速度

八.导致SQL执行慢的原因

1.硬件问题,如网络速度慢,内存不足,I/O吞吐量小,磁盘空间满了
2.没有索引或者索引失效
3.数据过多(分库分表)
4.服务器调优及各个参数设置(调整my.cnf),索引:当查询速度过慢时,可以通过建立优化查询速度,可以当做调优

九.物理备份的方式

1.完全备份
每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。
优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快
缺点：占用空间大，备份速度慢
2.增量备份
每次备份上一次备份到现在产生的新数据
只有那些在上次完全备份或者增量备份后被修改的数据才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化.
特点：因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。
3.差异备份
备份那些自从第一次次完全备份之后被修改过的所有文件，备份的时间起点是从第一次次完整备份起，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。备份数据量会越来越大。
特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。

十.有哪些备份类型

Mysql备份类型:
物理备份：直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制
1.热备(hot backup)
在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件

对应用基本无影响(但是性能还是会有下降,所以尽量不要在主上做备份,在从库上做)

2.冷备(cold backup)
备份数据文件,需要停机，是在关闭数据库的时候进行的

3.温备(warm backup)

针对myisam的备份(myisam不支持热备),备份时候实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的
对应用影响很大
通常加一个读锁

逻辑备份：
备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

十一.Mysql备份工具

1、ibbackup

官方备份工具
收费
物理备份
2、xtrabackup
开源社区备份工具
开源免费,上面的免费版本(老版本有问题,备份出来的数据可能有问题)
物理备份
3、mysqldump
官方自带备份工具开源免费
逻辑备份(速度慢)
4、mysqlbackup
mysql 官方备份工具
innodb 引擎的表mysqlbackup可以进行热备
非innodb表mysqlbackup就只能温备
物理备份，备份还原速度快
适合大规模数据使用

十二.备份策略

先设计一个场景:
1.比如数据量比较小的话可以每周做一次完备
2如果数据量比较大的话可以每周一做一次完备,周三周五的时候各做一次增备
物理备份的话我这里使用的是xtrabackup,并写一个脚本,放在计划任务中,每周一的时候进行一次完备,在周三和周五的时候分别进行一次增备
其实备份的话还得看公司的需求了

十三.mysql优化:性能优化,安全优化(应用加固,系统加固)

从两方面来说:
性能优化:
1.选取最适用的字段属性
2.给字段增加索引,加快检索效率
3.选择正确的存储引擎
4.优化查询语句

安全优化:
1.设置密码复杂度
2.更换3306端口
3.禁用远程连接

十四.Mysql脏数据的概念?如何避免脏数据的产生?

脏数据：从目标中取出的数据已经过期、错误或者没有意义，这种数据就叫做脏数据。
脏读: 在并发访问的情况下，不同的事务对相同的数据进行操作，在事务A修改数据还未提交的时候，事务B对该数据进行读取，读出了事物A修改过后的数据，但是事物A最终没有提交，这种情况就是数据库中的脏读情况(其实就是并发量大的情况下,事务可以读取到未提交读数据)

不可重复读: 事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。(两次执行同样的查询，可能会得到不一样的结果)

幻读: 当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次对该范围的数据进行插入记录时，会提示数据已存在，即产生了所谓的幻影行(也就是读取了提交的新事物,指增删操作)

避免脏数据产生:在查询的时候进行锁表, 锁就是防止其他事务访问指定的资源的手段。锁是实现并发控制的主要方法，是多个用户能够同时操纵同一个数据库中的数据而不发生数据不一致现象的重要保障。一般来说，锁可以防止脏读、不可重复读和幻觉读。

十五.Mysql的bin-log日志的作用?里边有什么内容

内容:
binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。
binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。

作用: 因为有了数据更新的binlog，所以可以用于数据丢失后的实时备份，与master/slave主从复制结合。

十六.Mysql的慢查询日志

Slow query log:慢查询日志,指导调优,定义某一个查询语句,执行时间过长,通过日志提供调优建议给开发人员

十七.GTID的工作原理

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

十八.读写分离的好处

分摊服务器压力，提高机器的系统处理效率
在写入不变，大大分摊了读取，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了。
增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务

十九.Mysql主从,master宕机,如何进行切换?

mysql sql优化java面试_mysql sql优化java面试

二十.主从复制的作用?

1.确保数据安全；做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据的丢失。
2.提升I/O性能；随着日常生产中业务量越来越大,I/O访问频率越来越高，单机无法满足，此时做多库的存储，有效降低磁盘I/O访问的频率，提高了单个设备的I/O性能。
3.读写分离，使数据库能支持更大的并发；在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。
主从复制延迟大比较慢的原因?
1.主服务器配置高,从服务器配置低
2.并发量大导致主服务器读得慢,从服务器写得慢
3.网络延迟比较高
4.从服务器的读写速度慢
5.从节点过多

从数据库的读延迟问题?如何解决
半同步复制-解决数据丢失的问题
并行复制-解决从库复制延迟的问题

二十一.主从复制的原理

实现整个主从复制，需要由slave服务器上的IO进程和Sql进程共同完成.
要实现主从复制，首先必须打开Master端的binary log（bin-log）功能，因为整个MySQL 复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己slave端完全顺序的执行中继日志中所记录的各种操作。

在主库上把数据更改（DDL DML DCL）记录到二进制日志（Binary Log）中。
备库I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。
备库SQL线程读取中继日志中的事件，将其重放到备库数据库之上。

mysql sql优化java面试_数据库_02

其实能看懂这张图就行

二十二.Mysql数据损坏如何修复?

二十三.Mysql有哪些数据类型(列举一部分)

数值类型
Tinyint
int
float
double

日期和时间类型
DATE
TIME
YEAR
DATETIME

字符串类型
cahr
varchar
text

二十四.Mysql数据库cpu飙升到500%的话,你怎么做?

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。然后kill掉这些线程(同时观察cpu使用率是否下降)

二十五.Mysql有关权限的表格都有哪些

user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。