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

1.MySQL索引失效的原因

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

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

16.MySQL索引有哪些设计原则?

（1）根据具体的业务选择合适的列：确保选择需要经常用于检索数据的列或者是查询时筛选条件的列来创建索引。
（2）唯一性：考虑基于查询的筛选条件和连接条件来选择索引列。
（3）短索引：索引列的长度应尽可能短，因为短索引可以提高查询性能。对于字符串列，可以使用前缀索引或全文搜索等技术。
（4）复合索引：对于多列的查询条件，使用复合索引（组合索引）以减少索引的数量。确保按照查询的顺序创建复合索引，以最大化性能提升。
（5）避免过多索引：不要创建过多的索引，因为每个索引都会占用磁盘空间和增加维护开销。

17.PGSQL和MySQL有什么区别？

（1）在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨。
（2）对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强。
（3）PG可靠性、数据一致性、完整性、稳定性要优于mysq。
（4）PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。