【面试高高手】—— MySQL（14题）

文章目录

• • 1.MySQL索引失效的原因
  • 2.MySQL中的锁有哪些？
  • 3.MySQL有哪些引擎？他们具有的特点？
  • 4.你知道的索引的类型有哪些？
  • 5.如何设计海量的数据存储系统？
  • 6.联合索引和单列索引的区别？
  • 7. 你是如何优化SQL的？
  • 8. 除了常规的SQL优化，你在实际场景中还是用到了什么优化手段？
  • 9. 死锁你知道怎么用吗？好处是什么？
  • 10. MySQL日志类型有哪些?
  • 11.MySQL支持事务吗？说说你对事务的理解？
  • 12. 说下事务的四大特性是什么？
  • 13. 你知道事务的隔离级别吗？
  • 14.事务并发会出现哪些问题？
  • 15.请你详细说下Btree和B+tree的区别？

1.MySQL索引失效的原因

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
2.对于聚合索引，如果使用的索引不是第一个，则不会走索引。
3.like查询以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.使用函数或表达式： 如果在查询中使用了函数或表达式来操作字段，那么索引可能会失效。例如，在WHERE子句中使用LOWER(column)函数会导致索引失效。
6.列类型不匹配： 当索引列和查询条件中的数据类型不匹配时，索引可能会失效。例如，将字符串类型的列与数字进行比较。

2.MySQL中的锁有哪些？

以InnoDB存储引擎作为基础。

• 共享锁（Shared Lock，也称为读锁）：
  意义：允许多个事务同时获取共享锁，用于防止其他事务获得排他锁，以确保数据读取的一致性。
  示例：一个事务获取了一行数据的共享锁，其他事务也可以获取相同行的共享锁，但不能获取排他锁。
  使用：

SELECT * FROM your_table WHERE some_column = 'some_value' FOR SHARE;

• 排他锁（Exclusive Lock，也称为写锁）：
  意义：排他锁是一种独占锁，只允许一个事务获得，用于确保在一个事务修改数据时，其他事务不能同时读取或修改相同的数据。
  示例：一个事务获取了一行数据的排他锁，其他事务无法同时获取相同行的排他锁或共享锁。
  使用：

SELECT * FROM your_table WHERE some_column = 'some_value' FOR UPDATE;

• 意向共享锁（Intention Shared Lock）：
  意义：用于表示一个事务打算在某个数据资源上获取共享锁，但事务还没有实际获取共享锁。意向共享锁通常是表级别的。
  示例：当一个事务打算在某个表上获取共享锁时，会在表级别设置意向共享锁，其他事务可以继续在表上设置共享锁。
  使用：
  意向锁通常不需要手动设置，MySQL会自动管理它们。当事务要获得某行的共享锁或排他锁时，会在表级别设置相应的意向锁。

• 意向排他锁（Intention Exclusive Lock）：
  意义：用于表示一个事务打算在某个数据资源上获取排他锁，但事务还没有实际获取排他锁。意向排他锁通常是表级别的。
  示例：当一个事务打算在某个表上获取排他锁时，会在表级别设置意向排他锁，其他事务可以继续在表上设置共享锁或意向共享锁。

• 表锁（Table Lock）：
  意义：锁定整个表，用于控制对整个表的访问。表锁一般不建议在高并发环境下使用，因为它会阻塞其他事务对表的访问。
  示例：一个事务获得了对整个表的排他锁，其他事务无法同时访问相同表。
  使用：表锁通常在表级别自动管理，但也可以通过手动方式获取。以使用LOCK TABLES语句来获取表级别的锁。

LOCK TABLES your_table WRITE; -- 获取表的排他锁
-- 执行对表的操作
UNLOCK TABLES; -- 释放锁

• 行级锁（Row-level Lock）：
  意义：锁定表中的单个行记录，允许多个事务同时操作不同的行记录，提高并发性。
  示例：一个事务获得了某行的排他锁，其他事务可以获得相同表中其他行的排他锁或共享锁。
  使用：行级锁是在事务中自动管理的，通常不需要手动设置。当你执行对某行的更新或删除操作时，MySQL会自动为该行设置排他锁，以确保其他事务不能同时修改。

START TRANSACTION;
UPDATE your_table SET some_column = 'new_value' WHERE id = 1; -- 设置行级排他锁
COMMIT; -- 释放锁

3.MySQL有哪些引擎？他们具有的特点？

1.innoDB

2.Myisam

3.Memory

区别：

innoDB跟myisam的默认索引是B+tree，Memory的索引默认为hash

InnoDB支持事务，支持外键，支持行锁，写入数据时操作快，MYSQL5.6版本以上才支持全文检索

myisam不支持事务，不支持外键，支持表锁，支持全文检索，读取速度快

memory所有的数据都保留在内存中，不需要进行磁盘的IO所以读取的速度很快，但是一旦关机的话表的结构会保留，但是数据会丢失，表支持Hash索引，因此查询效率很快。

4.你知道的索引的类型有哪些？

（1）普通索引：

• B树索引： B树（或B+树）索引是MySQL中最常用的索引类型。它适用于等值查找、范围查询和排序操作。B树索引在内部是一颗平衡树，可以高效地支持数据的插入和删除操作。
• 哈希索引： 哈希索引适用于等值查找操作，但不支持范围查询和排序。它使用哈希函数将索引列的值映射到索引桶中，因此查询时非常快速。

（2）唯一索引：唯一索引确保索引列中的值都是唯一的，不允许重复值。唯一索引可用于实施唯一性约束。
（3）主键索引：主键索引是一种特殊的唯一索引，它用于唯一标识表中的每一行。每个表只能有一个主键索引，主键索引通常与主键列一起创建。
（4）复合索引：复合索引是指将多个列组合在一起创建的索引。它适用于涉及多个列的查询，可以提高多列条件查询的性能。
（5）全文索引：全文索引用于全文搜索操作，通常用于文本字段（如文章内容或评论）。它允许你执行复杂的文本搜索，包括全文匹配和词语关联性分析。
（6）空间索引：空间索引适用于地理信息系统（GIS）应用，用于存储和查询地理空间数据（如地理坐标）。MySQL提供了特殊的空间数据类型和函数来支持空间索引。

5.如何设计海量的数据存储系统？

海量数据的解决方案：
页面上：使用缓存；页面静态化技术；
数据库层面： 分离数据库中活跃的数据； 批量读取和延迟修改；
读写分离； 使用NoSQL和Hadoop等技术；
分布式部署数据库； 应用服务和数据服务分离；
其他方面： 使用搜索引擎搜索数据库中的数据； 进行业务的拆分；
高并发情况下的解决方案： 应用程序和静态资源文件进行分离，静态资源可以使用CDN； 集群与分布式； 使用Nginx反向代理；

6.联合索引和单列索引的区别？

• 组成方式：
  • 单列索引： 单列索引仅包含单个表列。每个索引项只包含一个列的值。
  • 联合索引： 联合索引由多个表列组成，这些列按照一定的顺序形成索引。每个索引项包含多个列的值的组合。

7. 你是如何优化SQL的？

• 分析执行计划：在执行SQL查询之前，通过数据库管理工具或EXPLAIN语句来获取查询的执行计划。执行计划可以告诉你查询将如何执行，帮助你识别潜在的性能瓶颈。

• 索引优化：
  确保表中的字段经常用于过滤或排序的字段都有合适的索引。不过要注意，过多的索引也会导致性能下降。
  使用复合索引来覆盖多个查询条件。
  定期重新构建或重新组织索引，以保持索引的效率。

• 避免全表扫描：
  避免在大表上执行全表扫描，尽量使用索引或其他优化技巧来加速查询。
  使用适当的条件来限制结果集的大小，以减少全表扫描的需求。

• 优化查询语句：
  避免使用SELECT *，而是只选择需要的列。
  使用合适的WHERE子句来限制结果集的大小。
  避免嵌套子查询，如果可能的话，尽量使用连接操作。
  使用EXISTS或IN子句来代替NOT IN，因为后者通常性能较差。

• 合理使用缓存：使用数据库查询缓存（如MySQL的Query Cache）来缓存查询结果，减少重复查询数据库的次数。

• 定期维护数据库：定期清理不再使用的数据，进行数据库备份和恢复，以保持数据库的健康状态。

• 分区表：对大表进行分区，以减轻查询的压力，特别是在历史数据和活跃数据的管理上。

• 硬件升级：如果可能的话，升级数据库服务器的硬件，包括CPU、内存、磁盘等，以提高数据库性能。

• 使用数据库优化工具：许多数据库管理系统提供了优化工具，如MySQL的mysqltuner，可以帮助你分析数据库性能并提出优化建议。

• 监控和调整：定期监控数据库性能，查看慢查询日志，并根据需要调整查询和索引以解决性能问题。

• 考虑缓存层：在适当的情况下，引入缓存层（如Redis或Memcached）来减轻数据库的负担，特别是对于读密集型应用。

8. 除了常规的SQL优化，你在实际场景中还是用到了什么优化手段？

• 缓存策略：
  使用缓存来存储频繁访问的数据，以减少数据库查询次数。常见的缓存技术包括内存缓存（如Redis、Memcached）和分布式缓存（如Ehcache、Hazelcast）。使用合适的缓存失效策略，以确保缓存中的数据与数据库中的数据保持一致。
• 数据分片：
  对于大型应用，可以考虑将数据分散到多个数据库实例或分片中，以分摊负载并提高并发性能。
• 异步处理：将耗时的操作（如发送电子邮件、生成报表等）放入消息队列中，异步处理，以减轻主要应用的负担，提高响应速度。
• 负载均衡：使用负载均衡器来分发流量到多个应用服务器，以确保请求得到平均分配，提高系统的可用性和性能。
• 数据库分库分表：
  对于大型数据库，可以将数据分散到多个数据库实例或表中，以减轻单一数据库的负担，提高查询性能。
• 延迟加载：对于复杂对象关系，延迟加载可以延迟加载相关数据，只在需要时才进行加载，减少了不必要的数据查询。
• CDN（内容分发网络）：使用CDN来分发静态资源（如图片、样式表、脚本），降低服务器负载，提高资源加载速度。
• 数据库连接池：使用数据库连接池来管理数据库连接，避免频繁地创建和销毁连接，以提高数据库访问性能。
• 懒加载：对于前端界面，可以使用懒加载技术，延迟加载页面上不可见区域的内容，提高页面加载速度。
• 水平扩展：在需要时，考虑水平扩展应用服务器，添加更多的服务器以处理更多的请求。
• 合并和压缩资源：合并多个CSS和JavaScript文件，并对它们进行压缩，以减少页面加载时间。
• 性能监控和分析工具：
  使用性能监控工具（如New Relic、AppDynamics、Prometheus等）来监视应用性能，识别性能瓶颈，并采取相应的措施进行优化。

9. 死锁你知道怎么用吗？好处是什么？

10. MySQL日志类型有哪些?

重做日志（redo log）
回滚日志
二进制日志
错误日志
慢查询日志
一般查询日志
中继日志

11.MySQL支持事务吗？说说你对事务的理解？

支持！事务就是指一组操作的集合，要么全部成功，要么全部失败这就是事务。事务是在引擎层进行实现的，也就是说并不是所有的引擎都可以支持事务，MyISAM引擎就不支持事务，InnoDB支持事务的。InnoDB是MySql默认的引擎。

12. 说下事务的四大特性是什么？

• 原子性
  事务最基本的操作单元，要么全部成功，要么全部失败，不会结束在某个中间环节，事务在执行过程中发生错误，会回滚到事务开始的状态，就像这个这个事务从来没有执行过一样。
• 一致性
  指的是在一个事务执行之前和执行之后， 数据库必须处在一致性状态，如果事务成功的执行，那么系统中所有变化将正确的应用，系统处于有效状态。
• 隔离性
  指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间，由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是修改之后的状态，事务不会查看到中间状态的数据。
• 持久性
  指的是只要事务成功结束，它对数据库所做的更新就必须永久的保存下来，数据成功落盘。即使发生系统崩溃，重新启动数据库后，数据库还能恢复到事务成功结束的状态。

13. 你知道事务的隔离级别吗？

MySql的事务隔离级别一共分为4种，用来限定事务内外的哪些改变是可见的，哪些是不可见的。事务隔离级别越低，支持并发的性能越高，系统的开销越小！

• 读未提交（Read Uncommitted）：
  一个事务还未提交（commit）变更就被其它事务读取到了。这个隔离级别是很少在实际应用中看到的，因为他造成的事情很多，比如脏读，脏读就是一个事务读取到了其它事务还未提交的内容。而且这种隔离级别的性能也不比另外三种好多少，所以它是最少被实际应用的。
• 读已提交（Read committed）：
  一个事务只能看到其它事务已经提交（commited）的内容。因为它满足隔离的简单定义，所以这是大多数数据库所采用的的隔离级别，但是并不是MySql所默认的。这种隔离级别会造成不可重复读，不可重复读就是同一条SQL不能重复执行，因为两次查询出来的内容可能不一致。原因同一事务的其他实例在该实例处理其间可能会有新的commit（修改了内容），导致查询的内容不一致。
• 可重复读（Repeatable Read）：
  同一条SQL查询多次，会看到同样的数据行。这是MySql默认的隔离级别。但是这种隔离级别也会出现一些问题，比如幻读，幻读就是当用户读取某一范围内的数据时，另一个事务又在该范围内插入了新的数据，当用户再次读取该范围内的数据时就会出现“幻影”行（行数不一致）。幻读和不可重复读有些类似，都是表现出两次读取的内容不一致的状况，但是二者还是有差别的，幻读侧重的是删除和新增，不可重复读侧重的是修改，解决幻读的方法是锁住满足条件的行，而解决不可重复读的方法是锁住表。幻读在InnoDB和FaIcon存储引擎通过多版本控制（MVCC,Multiversion Concurrency Control）机制解决了该问题。
  对于快照读，InnoDB 使用 MVCC 解决幻读，对于当前读，InnoDB 通过 gap locks 或 next-key locks 解决幻读。
• 串行化（Serializable）：
• 强制事务排序，使多个事务之间不发生冲突，排着队一个个的执行。此隔离级别可以解决幻读的问题，实现的原理是在每个读的数据行上加锁，由于事务的串行化会导致大量的超时和锁竞争，效率很低。

14.事务并发会出现哪些问题？

• 脏读：
  一个事务读取到另一个未提交的数据。原因是在一个事务读取数据的前后另一个事务对该数据做了修改。
• 不可重复读：
  在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。重点是读取的数据内容不一致。
• 幻读：
  在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。重点是读取的数据的条数不一致。

15.请你详细说下Btree和B+tree的区别？

（1）在结构和数据存储方式区别：

• B树：B树中每个节点都存储数据。每个节点通常包含关键字和对应的数据项，这些数据项与存储在节点中的子树相关联。B树的所有叶子节点都包含实际数据项。

• B+树：B+树中只有叶子节点存储数据。非叶子节点（内部节点）仅包含关键字和用于导航的指针，这些指针指向叶子节点。这使得B+树的内部节点相对较小，能够容纳更多关键字，提高了树的扇出度（每个节点可以包含更多子节点）。

（2）在数据查找方式方面区别：

• B树：在B树中，查找操作通常需要遍历内部节点和叶子节点。每个内部节点都有与其子树相关的关键字范围，这样可以根据关键字的大小快速导航到正确的子树，最终找到目标数据。

• B+树：在B+树中，查找操作只需要遍历叶子节点。因为所有数据都存储在叶子节点中，所以只需在叶子节点上执行查找操作，这简化了查找过程。

（3）在范围查询和排序方面的区别：

• B树：B树可以在内部节点中存储部分数据项，这使得它更适合范围查询和排序操作，因为内部节点上的关键字可以用于优化这些操作。

• B+树：B+树在范围查询和排序操作方面通常更高效，因为这些操作只需要在叶子节点上执行。叶子节点之间通过指针连接，可以轻松实现范围查询。

（4）在插入和删除操作方面的区别：

• B树：插入和删除操作可能需要在内部节点上进行平衡调整，以确保树的平衡性。这使得B树的插入和删除操作相对较慢。

• B+树：B+树的插入和删除操作通常只涉及叶子节点，因此相对于B树，插入和删除操作更加高效。

（5）在应用场景方面的区别：

• B树：B树通常用于文件系统和数据库中，其中需要支持范围查询和排序操作，以及在内部节点中存储数据的场景。

• B+树：B+树通常用于数据库索引，其中高效的范围查询和排序非常重要。它还用于实现高度可伸缩的数据存储，因为它的内部节点相对较小，树的高度可以非常低。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！