mysql面试相关知识

最新推荐文章于 2024-11-01 14:31:36 发布

二十四桥明月夜夜

最新推荐文章于 2024-11-01 14:31:36 发布

阅读量531

点赞数

分类专栏：后端面试文章标签： mysql 面试数据结构

本文链接：https://blog.csdn.net/weixin_47960075/article/details/126811106

版权

后端面试专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、mysql使用到的数据结构

1、b树

b树索引是一个典型的数据结构，包含的主要有以下几个：

叶子结点：包含的条目直接指向表里的数据行。叶子结点直接彼此相连，一个叶子结点有一个指针指向下一个叶子结点。
分支节点：包含的条目指向索引里其他的分支节点或者叶子结点
根结点：一棵b树索引只有一个根节点，实际就是树最顶端的节点

基于这种树形结构，表中的每一行都会在索引上有一个对应的值。因此，查询数据库时，可根据索引值一步步定位到数据所在的行。

b树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行order by排序，但必须遵循最左原则，需要考虑以下：

查询必须从索引最左边的列开始
查询不能跳过某一索引列，需要从左到右按顺序匹配
存储引擎不能使用索引中范围条件右边的列
(总之，创建联合索引即是创建了多个联合索引，比如index(a,b,c)=>index(a)、index(a,b)、index(a,b,c)，共三个索引)

2、哈希索引

mysql目前仅有memory存储引擎和heap存储引擎支持这类索引，其中，memory存储引擎支持b树索引和hash索引，并且将hash作为默认索引

hash索引不是基于树形的数据结构来查找数据，而是通过索引列对应的哈希值获取表的记录行。哈希索引最大的优点为访问速度快，但也有以下缺点：

mysql需要读取表中索引列的值来参与散列计算，比较耗时。换而言之，相对于b树，建立hash索引会耗费更多的时间。
不能使用hash索引排序。
hash索引只能支持等值查询，如"=“、“in()”、或”<=>"。
hash索引不支持键的部分匹配，因为在计算hash值的时候是通过整个索引值来计算。

二、索引

1、索引是什么？能干什么？有什么好处？

索引相当于表的目录，在查找数据之前会先在索引中检索位置，然后定位到具体数据。索引会保存在额外的页中。
索引是mysql高效获取数据的一种数据结构

2、索引类型

普通索引

最基本的索引类型，没有任何限制，可以加快系统对数据的访问速度。

普通索引允许在定义索引的列中插入重复值和空值。

作用：加速查询！

唯一索引

不是为了提高访问速度，仅是为了避免出现重复数据。

唯一索引列的值必须唯一，允许有空值。如果是联合索引，则列值的组合必须唯一。

作用：加速查询，使列值不为空！

主键索引

创建表时必须要指定的索引，不允许重复或值为空。

作用：加速查询，使列值不为空，表中只要一个！

联合索引（组合索引）

在多个字段上建立的索引，只有在查询条件中使用了创建索引的第一个字段，索引才会被使用，必须符合最左原则！

作用：用于组合查询，效率大于索引合并！

全文索引

主要用于查找文本的关键字，不是直接与索引的值相比较。

3、索引使用的注意事项

选择唯一性索引
尽量使用数据量少的索引
尽量使用前缀索引
尽量选择区分度高的字段作为索引
尽量拓展索引，而不要新建索引
索引列不能参加计算
更新频繁的列不要创建索引
数据量小的表不用创建索引
重复数据多的字段不用创建索引(比如性别)
优先考虑对where和order by涉及的列使用索引

4、如何优化sql

负向查询条件不能使用索引，“!=”、“not in”、"not exists"不建议使用，可以用in代替
前导模糊查询"like" "%xx"不能使用索引，后导模糊查询可以使用前缀索引
数据区分度不高的字段不建议使用索引
调用函数之后的字段不能使用索引
业务大部分为单条查询，建议使用hash索引性能更好，hash时间复杂度为O(1)，b树索引的时间复杂度为O(logn)
明确只要一条查询，建议添加limit 1
如果只返回部分需要的列，不要使用select *，能够大大节省数据传输量和数据库的内存使用量

5、什么是最左匹配原则？

最左优先，从最左边开始任何连续的索引都能进行匹配，遇到返回查询停止匹配(>、<、between、like)
字符串索引也可以利用最左匹配原则
优点：能够显著提高查询效率，是最常见的性能优化的手段之一

6、哪些列适合创建索引？创建索引有哪些开销？

经常需要作为条件查询的列适合创建索引，并且该列也必须有一定区分度
创建索引需要维护，在插入数据的时候会重新维护各个索引树(数据页的分裂与合并)，对性能造成影响

7、索引的缺点？

对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度
索引需要占物理空间，除了数据表中数据空间之外，每一个索引还要占一定的物理空间，如果要简历聚簇索引，那么需要的空间会更大
创建索引和维护索引需要耗费大量时间，时间随数据量的增加而增加

三、为什么mysql使用b+树，不用其他数据结构？

b-tree：b树不论叶子还是非叶子结点，都会保存数据，这就导致非叶子节点中能够保存的指针数量减少，指针少的情况下要保存大量数据，只能增加树的高度，导致io操作变多，查询性能相应变低。
hash：虽然能快速定位，但是没有顺序，io复杂度高
二叉树：树的高度不均匀，不能自平衡，查询效率和数据有关，io代价也高
红黑树：虽然能自平衡，但是树的高度随着数据量增加而增高，io代价高
平衡二叉树：深度太大，io代价高

四、MyISAM和InnoDB实现b+tree索引方式的区别？

MyISAM：b+树叶子结点中的data域存放的数据记录的地址，在索引检索的时候，首先按照b+树搜索算法来搜索索引，如果指定的key存在，则取出其data域的值，然后通过该地址读取相应的数据记录，被称为非聚簇索引，索引文件和数据文件是分离开的
InnoDB：
- b+树分为主键索引(聚集索引)和辅助索引(非聚集索引)。一张表一定包含一个聚集索引构成的b+树和若干辅助索引构成的b+树
- 辅助索引并不会影响主索引，因为主索引构成的b+树是数据实际存储形式，而辅助索引只用于加速数据的查找，所以一张表中往往有多个辅助索引来提高数据库的性能
- 所有的辅助索引都会存储主键，过长的主键索引会导致辅助索引过长。且主键索引必须保证自增，否则将会出现频繁的断裂、分裂。十分低效，影响性能
- InnoDB使用辅助索引查询时，查询到指定值后悔取出该指定值下的主键值，然后取该值到主键索引表中查询相关数据，称为会标操作。如果查询的指定字段在辅助索引中，当查询到结果后直接返回，不会再回表

五、MyISAM和InnoDB区别？

对比项	MyISAM	Inodb
主外键	不支持	支持
事务	不支持	支持
锁粒度	表锁	行锁
缓存	只缓存索引，不缓存真实数据	都缓存，对内存要求较高，而且内存大小对性能有决定性影响
表空间	小	大
关注点	性能	事务

mysql5.1之前，默认使用MyISAM，由于无法使用事务，且最小锁粒度为表锁，在5.1之后替换为了InnoDB

1. 事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比 InnoDB 类型更快，但是不提供事务支持。

InnoDB：提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

2. InnoDB 支持行级锁，而 MyISAM 支持表级锁.

用户在操作myisam 表时，select，update，delete，insert 语句都会给表自动加锁，如果加锁以后的表满足 insert 并发的情况下，可以在表的尾部插入新的数据。

3. InnoDB 支持 MVCC, 而 MyISAM 不支持

4. InnoDB 支持外键，而 MyISAM 不支持

5. 表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个 6 字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

6. InnoDB 不支持全文索引，而 MyISAM 支持。

7. 可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB：免费的方案可以是拷贝数据文件、备份binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了

8. 存储结构

MyISAM：每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm 文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。

六、事务的特性

ACID原则

原子性：事务的操作要么都成功，要么都失败
一致性：事务的操作必须使数据库中的数据从一种状态转变为另一种状态
隔离性：并发操作数据库的同一张表时，多个事务会被相互隔离，不会被相互干扰
持久性：事务一旦提交，数据库的改变是永久的

七、事务的隔离级别

事务隔离级别	symbol	更新丢失	脏读	不可重复读	幻读
读未提交	Read-UnCommitted	避免	发生	发生	发生
读已提交	Read-Committed	避免	避免	发生	发生
可重复读	Repeatable-Read	避免	避免	避免	发生
串行化	Serializable	避免	避免	避免	避免

在事务隔离的实现上，数据库会创建一个视图view，访问的时候以视图的逻辑结果为准
- 在可重复读隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图
- 在读已提交隔离级别下，这个视图是在每个sql语句开始执行时创建的
- 在读未提交隔离级别下，直接返回记录上的最新值，没有创建视图
- 在串行化隔离级别下，直接用加锁的方式来避免并行访问

并发操作事务会发生的问题

脏读：一个事务在处理过程中读取到了另一个未提交事务中的数据

某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读：查询多次，返回了不同的数据。

事务b查询了两次数据库，在第一次查询后，事务a修改了表中的数据，导致第二次查询的时候返回了不同的结果

幻读：查询多次，返回了前一次查询没有的结果

事务b查询了两次数据库，在第一次查询后，事务a在表中插入了新数据，导致第二次查询的时候，返回了前一次查询中没有的数据，就好像发生了幻觉一样

《事务隔离：为什么你改了我还看不见？》

当前mysql默认使用的为repetable-read可重复读的隔离级别来操作事务

《mysql四种隔离级别》

八、数据库的三大范式

1、列不可再分

第一范式的目标是确保每列的原子性：如果每列都是不可再分的最小数据单元(也称为最小的原子单元)，则满足第一范式

id	address
1	中国北京市
2	中国西安市
3	中国上海市

就必须改为：

id	country	city
1	中国	北京
2	中国	西安
3	中国	上海

2、每张表只描述一件事情

首先满足第一范式的基础上，并且表中非主键列不存在对主键的部分依赖。第二范式要求每个表只描述一件事情

字段	例子
订单编号	1
产品编号	001
订单日期	2020-1-1
价格	29

必须拆分为：
order表：

字段	例子
订单编号	1
订单日期	2020-1-1

product表：

字段	例子
产品编号	001
价格	29

3、存在对非主键列的传递依赖

第三范式定义：满足第二范式的基础上，表中的列不存在对非主键列的传递依赖。

除了主键订单编号外，顾客姓名依赖于非主键顾客编号

字段	例子
订单编号	001
订单日期	2020-1-1
顾客编号	a1
顾客姓名	小明

字段	例子
订单编号	001
订单日期	2020-1-1
顾客编号	a1

对于查询顾客信息的问题：应该重新查询顾客表

九、sql注入问题？

1、什么是sql注入？

一种常见的数据库攻击手段，使用恶意sql语句拼接来执行sql语句操作数据库。

比如 select * from table where id = 1 or 1 = 1 or和or后面的1=1就属于sql注入，使得我们本来只使用id=1查询的情况下，因为加入了or 1=1恶意操作下依旧可以使得数据库被攻破。

2、#{}和${}的区别：

#{}将传入的参数都当作一个字符串，会自动为传入的参数加双引号。

id = #{id}，如果传入的值为1，那么解析成id = "1"，如果传入的值为id，那么解析为 id = "id"

${}将传入的参数直接传入sql中

id = ${id}，如果传入的值为1，那么解析成id = 1。

#能够很大程度防止sql注入

$无法防止sql注入

$一般用于传入数据库对象，例如表名

一般能用#,不要使用$

Mybatis排序时使用order by动态参数需要注意，用$而不是#

字符串替换：
默认情况下，使用#{}格式的语法会导致MyBatis创建预处理语句属性并以它为背景设置安全的值（比如?）。这样做很安全，很迅速也是首选做法，有时你只是想直接在SQL语句中插入一个不改变的字符串。比如，像ORDER BY，你可以这样来使用：ORDER BY ${columnName}，这里MyBatis不会修改或转义字符串。

3、如何防止sql注入

想防止sql注入，很明显需要在传入参数上下功夫，如select * from table where id = #{id}，我们可以查看预编译的sql语句为：select * from table where id = ?，不论传入的参数是什么，参数在预编译时都会被编译为问号，执行时，替换占位符问号即可。因为sql注入只在编译过程起作用，所以这样的方式就能很好的避免了sql注入。

在mybatis中，${}会直接参与sql编译，从而导致sql注入。

防止的方式：

parameterType：映射文件中指定输入参数的类型
resultType：映射文件中指定输入结果类型
#{}

4、mybatis如何预编译sql？

底层jdbc驱动中的PreparedStatement类起到了作用，PreparedStatement是Statement的子类，创建它的对象，会编译好sql语句，比如select * from table where id = ?，这种语句，传入的参数都被预编译为问号?，提高了安全性，而且多次执行同一个sql时，可以提高效率，原因是：sql已经被编译好，再次执行时，无需再编译。

重要：接受从用户传入的内容并提供给sql语句中不变的字符串，这样做是不安全的。这会导致潜在的SQL注入攻击，因此你不应该允许用户输入这些字段，或者通常自行转义并检查。

十、什么是MVCC

《MVCC最全讲解》

十一、Mysql的各种日志

《Mysql中几种日志了解》

十二、Mysql建表的约束条件

主键约束：唯一性，非空性
唯一约束：唯一性，可以为空，但只能有一个为空
检查约束：对该列数据的返回、格式的限制
默认约束：数据的默认值
外键约束：需要建立两张表间的关系引用主表的列

十三、Mysql执行CRUD的过程

《一条 SQL 语句是如何执行的？》

《一条 SQL 语句是如何更新的？》

十四、Mysql遇到的死锁问题

《行锁功过：怎么减少行锁对性能的影响？》

十五、谈谈分库分表设计

垂直分表：垂直分表在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”，拆分是基于关系型数据库中的“列”(字段进行的)。通常情况下，某个表中的字段比较多，可以新建立一张“扩展表”，将不常使用或者长度较长的字段拆分出去放到“扩展表”中。在字段很多的情况下，拆分开确实更便于开发和维护。某种意义上，也能避免“跨页”的问题(Mysql底层都是通过“数据页”来存储的，“跨页”问题可能会造成额外的性能开销)，拆分字段的操作建议在数据库设计阶段就做好，如果是在发展过程中拆分，则需要改写以前的查询语句，会额外带来一定的成本和风险，谨慎处理
垂直分库：垂直分库在“微服务”盛行的今天已经很普及了。基本思路就是按照业务模块来划分出不同的数据库，而不是像早期一样将所有的数据表都放到同一个数据库中。系统层面的"服务化"拆分操作，能够解决业务系统层面的耦合和性能瓶颈，有利于系统的扩展维护。而数据库层面的拆分，道理也是相同的。与服务的"治理"和"降级"机制类似，我们也能对不同业务类型的数据进行"分级"管理、维护、监控、扩展等
水平分表：水平分表也称为横向分表，就是将表中不同的数据按照一定规律分布到不同的数据库表中，这些表保存在同一个数据库中，这样来降低单表数据量，优化查询性能。最常见的方式就是通过主键或者时间等字段进行Hash和取模后拆分。水平分表，能够降低单表的数据量，一定程度上可以缓解查询性能瓶颈。但本质上这些表还保存在同一个库中，索引库级别还是会有IO瓶颈，所以，一般不建议采用这种做法
水平分库：水平分库分表与上面讲到的水平分表的思想相同，唯一不同的就是将这些拆分出来表保存在不同的数据库中。这也是很多大型互联网公司所选择的做法。某种意义上来讲，有些系统中使用"冷热数据分离"(将一些较少的历史数据迁移到其他的数据库中，而在业务功能上，通常摩恩只提供热点数据的查询)，也是类似的实践。在高并发和海量数据的场景下，分库分表能够有效缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源的瓶颈。当然，投入的硬件成本也会更高，同时，这也会带来一些复杂的技术问题和挑战(例如：跨分片的复杂查询，跨分页事务)