mysql面试题自用

-ttting--

已于 2024-07-19 10:20:52 修改

阅读量542

点赞数 6

文章标签： mysql 数据库

于 2024-07-14 19:04:46 首次发布

本文链接：https://blog.csdn.net/weixin_67392961/article/details/140413829

版权

事物

一组操作的集合，不可分割的工作单位，事物会把所有的操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功，要么同时失败回滚。

ACID

原子性（Automicity）：事物是不可分割的最小操作单元，要么全部成功，要么全部失败回滚
一致性（consistency）：事务完成时，必须使所有的数据都保持一致状态，比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（isolation）：数据库系统提供的隔离机制，保证事物在不受外部并发操作的影响的独立环境下运行。
持久性（durability）：事物一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

脏读：一个事物读取到另一个事物还未提交的数据。
不可重复读：两次读的不一样。在事物A两次读取之间，事物B修改了数据。
幻读：读的时候没有，插入的时候又有了。

不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

数据库隔离级别

四个隔离级别：读未提交（Read Uncommitted）、读提交（Read Committed）、可重复读（Repeatable Read）、串行化（Serializable）。

脏读：通过“读提交”及以上级别避免，因为只有已提交的更改才对其他事务可见。
不可重复读：通过“可重复读”及以上级别避免，因为保证了在同一事务内多次读取同一数据集合时的一致性。
幻读：在大多数数据库系统中，通过“串行化”级别避免，因为事务是串行执行的，不存在并发问题。而在MySQL中，即使在“可重复读”级别下也能通过MVCC机制避免部分幻读。

事物隔离级别越高，数据越安全，但是并发性能越低。

MySQL默认使用的是“可重复读（Repeatable Read）”隔离级别。

聚集索引和非聚集索引。

聚集索引：

索引顺序和物理地址顺序一致，叶子结点存储行数据。一个表只有一个聚集索引。
聚集索引选取规则：一般是主键，不存在主键则使用第一个唯一索引，否则默认生成。

非聚集索引：

可多个。叶子结点存储的是该行对应的主键。聚集索引如果通过索引无法查找到所需的列数据，则要回表查询（二次查询）。即根据主键再去聚集索引中进行查找。

回表查询

非聚集索引中叶子结点不存储实际数据，而是存储对应的主键值。如果通过索引无法查找所需的数据，则需要根据主键值在聚集索引中查找具体数据。这个过程称为回表查询。

覆盖索引。

覆盖索引指的是想要查询的列被索引覆盖，通过索引就可以查找到所需数据，无需查找具体数据行。

索引建立的原则。

（数据量）单一表数据量过大时，应建立索引
（使用频率）索引应当建立在常作为查询条件的列中
（索引位置，覆盖索引）尽量建立联合索引，提高查询效率。（联合索引应符合最左前缀原则，即以最左边为首的所有组合）
（索引数量）索引数量不宜过多。会影响增删改的速度。

最左前缀法则

创建一个联合索引，查询就从索引的最左列开始，这个最左边的字段必须存在，并且不跳过索引中的列，如果跳过了某一列，那么后面字段的索引将失效。

索引失效场景。

索引列参与运算或者使用了函数
违反了最左前缀法则
使用了select *
字符串类型不加引号，参数类型和字段类型不匹配，导致类型发生了隐式变换。
使用or这个关键字，那它前面和后面的字段都要加索引，只要有一个没有索引，那么涉及到的索引都不会被用到。
头部模糊匹配，用like和%来进行模糊查询时，%在最左边索引就会失效。

InnoDB 和 MyIsam 引擎的区别？

事物支持：InnoDB支持事务，myISAM不支持事务，它不具备ACID特性，如果在执行一组操作时发生错误，MyISAM无法回滚已经执行的操作。
数据存储方式：MyISAM使用表级锁定，因此在进行读写操作时，会锁定整个表。这意味着在高并发的情况下，可能会导致性能瓶颈。InnoDB使用行级锁定，因此在进行读写操作时，只会锁定需要的行。这提高了并发性，允许多个事务同时处理不同的行。
外键支持：MyISAM不支持外键约束，InnoDB支持外键约束，这使得在确保数据一致性方面更加方便。

where和having的区别

where是分组之前对数据进行过滤，不满足where条件不参与分组，having是在分组之后对数据进行过滤。
where后面不可以使用聚合函数，having后面可以。

DQL执行顺序

from、where、grooup by、having、select、order by、limit

from：从哪张表查
where：筛选出要求的信息
group by：对筛选出来的信息进行分组
having：对分组后的信息进行筛选
select：根据字段查出来
order by：排序
limit：分页

SQL可以怎样优化？

1.创建表时选择合适的字段类型，例如char定长效率高，varchar可变长但是效率低。

2.不要使用Select *

3.使用union all 代替 union。union会自动去除重复项，效率低。

4.where子句中不使用表达式

5.inner join优于left join和right join（innerjoin会自动小表驱动。）

6.读操作多时，可以使用主从架构。

如何定位慢查询？

现象：页面加载过慢、接口测试响应时间长。
出现情况：在 聚合查询、多表查询、单表数据量过大、深度分页查询。

使用一些开源工具例如skywalking来监听接口的运行情况，定位到响应慢的接口。
开启mysql的慢日志。（配置文件中进行修改，设置查询语句执行的时间阈值）

MySQL 支持行锁还是表锁？分别有哪些优缺点？

MySQL 既支持行级锁（Row-level Locking），也支持表级锁（Table-level Locking）。

行锁

行锁是指对数据表中的一行记录进行锁定，其他事务需要等待该锁释放才能访问被锁定的行。行锁可以提高并发性，不同的事务可以同时锁定不同的行，从而避免了对整个表的锁定。

优点

高并发性：不同事务可以同时锁定不同行，从而提高了数据库的并发性能。
精确控制：只锁定需要操作的行，避免了无谓的锁竞争，降低了锁冲突的概率。

缺点

内存消耗：行锁需要维护每一行的锁信息，会占用一定的内存空间。
性能开销：锁管理的细粒度导致了额外的性能开销，例如死锁检测等。
锁竞争：当大量事务同时访问不同行时，仍然可能出现锁竞争问题。

表锁

表锁是指对整个数据表进行锁定，当一个事务锁定了表后，其他事务需要等待该锁释放才能访问整个表。表锁是一种较粗粒度的锁。

优点

简单：实现相对简单，不需要维护大量的锁信息。
节省内存：只需要维护表级的锁信息，节省了内存开销。

缺点

低并发性：表锁限制了并发性，当一个事务锁定了表后，其他事务无法并行访问。
锁竞争：表级锁导致多个事务之间的锁竞争增加，可能出现更多的死锁问题。
性能瓶颈：在高并发情况下，表级锁可能成为性能瓶颈，限制了系统的吞吐量。

整数类型的unsigned属性有什么用？

无符号的意思，因为它不需要存储负值，所以它可以将正整数的上限提高一倍。
例如TinyInt取值范围是-128~127，TinyInt的取值范围是0~255

char和varchar的区别

char是定长字符串，varchar是变长字符串
CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。
CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。