(四) 数据库面试题

4.1 索引有哪些

MySQL的索引有两种分类方式：逻辑分类和物理分类。

按照逻辑分类：

主键索引：一张表只能有一个主键索引，不允许重复，不允许为null；

唯一索引：数据列不允许重复，允许为NULL值，一张表可有多个唯一索引，但是一个唯一索引只能包含

一列，比如身份证号码，卡号都可以作为唯一索引；

普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许NULL值插入；

全文索引：让搜索关键词更高效的一种索引；

按照物理分类：

聚集索引：一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节rowid作为聚集索引。每张表只有一个聚集索引

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引；

4.2 mysql引擎知道哪些？有什么区别？

InnoDB 存储引擎：

o 支持自增长列（auto_increment），自增长列的值不能为空，如果在使用的时候为空的话就会从现有的最大值自动+1，如果有但是比现在的还大，则就保存这个值。支持外键（foreignkey）， 支持事务，回滚以及系统崩溃的修复能力，并且支持多版本并发控制的事务安全。支持的行级锁，就是通过多版本控制来实现的乐观锁，索引使用的是B+Tree

优缺点：InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

MyISAM 存储引擎

不支持事务、支持表级锁，支持全文搜索，缓冲池只缓存索引文件，不缓存数据文件 MyISAM 存储引擎表由数据文件（MYD）和索引文件（ MYI）组成

我们项目中常用到的是innoDB，InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，但是对比Myisam的存储引擎InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

4.3 mysql如何查看索引是否生效？

1.使用SHOW indexs命令查看表的索引信息。例如，执行"SHOW INDEXES FROM table_name;"命令可以查看表的索引情况。
2.使用EXPLAIN命令查看SQL语句的执行计划。例如，执行"EXPLAIN SELECT * FROM table_name WHERE column_name = value;"命令可以查看查询语句的执行计划，从而了解索引是否被使用。
3.使用information_schema.statistics表来获取索引的统计信息。例如，执行"SELECT * FROM information_schema.statistics WHERE table_schema='database_name' AND table_name='table_name';"命令可以查看表的索引统计信息。
4.使用ANALYZE TABLE语句：可以使用ANALYZE TABLE语句分析表的统计信息，包括索引的使用情况。这将更新表的统计信息，并返回一个结果集，其中包含有关索引使用情况的信息

4.4 mysql如何进行sql优化

使用EXPLAIN分析查询：
	使用EXPLAIN关键字分析您的查询，这可以帮助您理解MySQL如何执行查询，以及它是否使用了索引。
	关注type列，它显示了MySQL如何检索行。最好看到的是ref，这表示MySQL使用了索引。
-优化数据表设计：
	确保您的数据表有适当的索引。过多的索引会增加写操作的开销，但缺少索引会导致查询性能下降。
	考虑使用合适的数据类型，例如，使用INT而不是VARCHAR来存储整数。
-优化查询语句：
	减少使用SELECT *，只选择需要的列。
	避免在查询中使用函数或计算，这可能会使索引失效。
	尽量避免使用子查询，可以考虑使用JOIN。
-使用合适的索引：
	确保您的查询条件中的列有索引。
	考虑使用复合索引来覆盖多个列。
	定期检查并删除不再需要的索引。
-优化数据库配置：
	调整MySQL的配置参数，例如innodb_buffer_pool_size（用于InnoDB存储引擎的缓冲池大小）和query_cache_size（查询缓存大小），以适应您的应用需求。
-考虑硬件和存储：
	使用SSD硬盘可以提高I/O性能。
	考虑增加RAM，以便更多的数据可以被缓存。
-定期维护：
	使用工具如OPTIMIZE TABLE来定期维护您的数据表。
-考虑分区：
	如果您的表非常大，考虑使用分区来提高性能。
-使用合适的存储引擎：
	根据您的需求选择合适的存储引擎，例如InnoDB或MyISAM。
-监控和日志：
	使用监控工具来跟踪查询的性能，找出需要优化的查询。
	使用慢查询日志来查找执行时间较长的查询。

接下来就是对sql语句中的全匹配，模糊查询，非空判断，大于小于操作符这一系列的的操作进行优化就行

4.5 什么是存储过程？什么是视图？

存储过程：存储程序是被存储在服务器中的组合SQL语句，经编译创建并保存在数据库中，用户可通过存储过程的名字调用执行。存储过程核心思想就是数据库SQL语言层面的封装与重用性。使用存储过程可以较少应用系统的业务复杂性，但是会增加数据库服务器系统的负荷，所以在使用时需要综合业务考虑。

视图：视图本身是一张虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，获取的数据是MySQL从其它表中生成的，视图和表在同一个命名空间（因为表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图）。视图查询数据相对安全，视图可以隐藏一些数据和结构，只让用户看见权限内的数据，使复杂的查询易于理解和使用。

4.6 mysql如何进行行转列，列转行

mysql中行转列我们用的是内部函数实现过，函数是cast when then else end 这种

列转行使用 union 关键字 把多个select语句结果捏合到一个结果来实现

4.7 分库分表如何分，数据库集群如何管理？

分库分表可以采用垂直拆分和水平拆分

垂直拆分：是将单表，或者是有关联的表放在一个数据库，把原有的一个数据库拆分成若干个数据库。

水平拆分：是将一个很大的表，通过取模，按照日期范围等等拆分成若干个表.

数据库如果搭建集群可以使用mycat数据库中间件进行管理数据库集群，这里我之前看过，可以修改mycat的配置文件有3个文件，分别是

schema.xml，配置逻辑库表，分片和读写分离

rule.xml，具体的分片规则和分片算法

server.xml，配置默认的数据库和用户，表权限

4.8 mysql隐藏字段

mysql中，会默认在我们的表后面添加三个隐藏字段

1、db_row_id:行id。mysql的索引数据结构为B+数，要求每个表必须要有一个主键。如果没有设置主键，会自动寻找第一个不包含NULL值的唯一索引作为主键。
2、db_tax_id:事务id，记录的是当前事务所在的insert或者update语句操作的事务Id(delete语句是update的特殊执行)
3、db_roll_ptr:回滚指针，通过它可以将不同的版本串联起来，形成版本链。

4.9 使用索引一定能提高查询的性能吗

索引就是通过事先排好序，从而在查找时可以应用二分查找等高效率的算法速度会快

通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的I* NSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%。
• 基于非唯一性索引的检索。

4.10 索引什么时候创建，需要注意什么？

1.索引是有大量数据的时候才建立的，没有大量数据反而会浪费时间，因为索引是使用二叉树建立.
2.当一个系统查询比较频繁，而新建，修改等操作比较少时，可以创建索引，这样查询的速度会比以前快很多，同时也带来弊端，就是新建或修改等操作时，比没有索引或没有建立覆盖索引时的要慢。
3.索引并不是越多越好，太多索引会占用很多的索引表空间，甚至比存储一条记录更多。

注意：
非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

4.11 常见的数据库有哪些？

MySQL：关系数据库，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
Oracle：甲骨文公司的一款非关系数据库，系统可移植性好。它是一种高效率、可靠性好的、适应高吞吐量的数据库解决方案。
SqlServer：微软的，有图形化用户界面，使系统管理和数据库管理更加直观、简单
Redis：Key-Value数据库，并提供多种语言的API
MongoDB：是一个基于分布式文件存储的数据库，介于关系数据库和非关系数据库之间的产品，是非关系数据库当中功能最丰富，最像关系数据库的。
HBase是一个分布式的、面向列的开源数据库，一个结构化数据的分布式存储系统”。

4.12 Hash索引和B+树所有有什么区别或者说优劣呢?

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.

.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据

• hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.(这个是主要区别)
• hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差
• 而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低

4.13 超大分页怎么处理

超大的分页一般从两个方向上来解决.

数据库层面：这也是我们主要集中关注的(虽然收效没那么大),比如从数据库查询年龄大于20岁的取一百万条，可以使用子查询，在子查询中判断完之后，在外层使用in 的方式查询，这样虽然load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. ,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.

需求的角度：减少这种请求….主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击。

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可

4.14 什么是Mysql的binlog?

MySQL的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL 执行过的所有语句。MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 有三种格式，各有优缺点：
statement： 基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。
row： 基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。
mixed： 混合模式，根据语句来选用是 statement 还是 row 模式。

4.15 什么是MVCC？MVCC 的实现原理？

MVCC， 即多版本并发控制。MVCC 的实现，是通过保存数据在某个时间点的快照来实现的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的

InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。
MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性。通过MVCC，保证了事务 ACID 中的 I（隔离性）特性。

4.16 统计过慢查询吗？对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的我都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。慢查询的优化首先要搞明白慢的原因是什么？ 是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

4.17 数据库索引什么时候会失效？

1. 查询中带有or关键字：如果查询条件中包含or，即使其中有部分条件带索引，数据库也不会使用索引。因此，尽量避免在查询条件中使用or。
2. 模糊查询中like以%开头：如果在模糊查询中使用like语句，并且以%开头，那么索引也会失效。
3. 字符类型查询时不带引号：当进行字符类型的查询时，如果没有带上引号，那么索引也会失效。
4. 索引字段参与计算或使用函数：如果索引字段参与了计算或者使用了函数，那么索引也会失效。
5. 违背最左前缀原则：在联合索引中，如果查询的条件没有包含联合索引的最左列，那么索引也会失效。
6. 不同字段值对比：如果查询的条件是两个字段的值进行对比，而不是同一个字段的值进行对比，那么索引也会失效。

4.18 Mysql隔离级别有哪些？

MySQL支持的四种事务隔离级别如下：

• 读未提交（READ UNCOMMITTED）：这是最低的隔离级别，允许事务读取尚未提交的其他事务数据，可能导致脏读问题。
• 读已提交（READ COMMITTED）：只允许事务读取已经提交的修改，可以防止脏读，但可能出现不可重复读的情况。
• 可重复读（REPEATABLE READ）：在这个级别中，事务开始时所做的任何查询，在整个事务期间都会返回相同的结果，解决了不可重复读问题，但幻读仍有可能发生。
• 串行化（SERIALIZABLE）：最高级别的隔离，通过完全串行执行事务来避免并发问题，包括脏读、不可重复读和幻读，但这也会导致性能显著下降。

每种隔离级别对系统性能的影响是不同的，选择适当的隔离级别时需要在数据一致性和系统性能之间做出权衡。

4.19 为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B-tree： 从两个方面来回答

• B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
• 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash：

• 虽然可以快速定位，但是没有顺序，IO复杂度高；

• 基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；

• 适合等值查询，如=、in()、<=>，不支持范围查询 ；

• 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；

• Hash索引在查询等值时非常快 ；

• 因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；

• 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 。

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树： 树的高度随着数据量增加而增加，IO代价高。

4.20 讲一讲聚簇索引与非聚簇索引？

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。

而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

• 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
• 对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。
• 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可
• 注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

4.21 非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。

4.22 联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。因为MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

4.23 讲一讲MySQL的最左前缀原则?

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。 mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

4.24 了解索引下推吗？

MySQL 5.6引入了索引下推优化。默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

• 有了索引下推优化，可以在减少回表次数
• 在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下：

在 people_table中有一个二级索引(zipcode，lastname，address)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

• 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件
• 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。

4.25 MySQL的binlog有有几种录入格式?分别有什么区别?

有三种格式,statement,row和mixed.

• statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.
• row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。
• mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row. 此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录.