Mysql之面试题之索引

最新推荐文章于 2025-05-08 21:19:17 发布

原创

最新推荐文章于 2025-05-08 21:19:17 发布 · 1.5k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #java #面试 #索引 #B+Tree

文章目录

前言
一、索引
二、实际使用：增删改查索引（重要）
三、分析索引合理性-explain:（很重要）
四、面试题
总结

前言

提示：这里可以添加本文要记录的大概内容：
写这个MySQL的人也是牛逼，这就是一个Excel表格由之前的直接可视化界面操作被改成通过命令操作。

简而言之就是：在数据库系统中，我们考虑的主要构成包括库(数据库)、表、行(记录)、索引这四类对象。每类对象上都可以进行增加、删除、修改、查询等操作。然而，数据库系统设计和优化的重点通常集中在索引的管理、事务的处理以及锁的控制上。这些机制确保了数据库在执行操作时，不仅遵循ACID原则，即原子性、一致性、隔离性、持久性，也保证了数据处理的高效率。在日常应用中，这意味着要把页面中的数据变化高效且安全地同步到数据库

一、索引

简述：一个空间和时间博弈的结果：其实这里有句话：
哪有什么岁月静好，只不过是有人在替你负重前行：
1、想用索引，先得有索引，有索引就得建索引并时实更新索引。
增删改数据的这个过程中就在更新索，这样其实就降低了增删改数据的性能，进而提升了查询的效率：
所以当表的查询相对较少，增删改相对较多时候，索引不建议多建。更具业务情况来
一般公司建立索引为10个：建议使用联合索引

作用：主要作用（重要）

提升查询性能：这是索引最基本也是最重要的作用，通过将数据索引化来避免全表扫描，从而快速定位到所需的数据。
减少回表操作：在拥有覆盖索引的情况下，查询可以直接在索引中获取所需的所有列的值，无需访问数据表本身，这降低了磁盘I/O操作和查询成本。
提升排序效率：索引本质上是排序的数据结构，当查询包含ORDER BY子句时，索引可以极大加快排序的速度。
辅助锁机制：尤其是在行级锁定中，索引可以帮助数据库更精确地锁定需要修改的记录，避免不必要的锁定扩展，从而提高并发性能。

作用：更细分了没啥用，想看看看（不重要）

提高查询效率：索引最主要的功能是加速查询操作，类似于书籍的目录，可以快速找到数据的位置而不必全表扫描。
增强排序和分组速度：当对数据进行排序（ORDER BY）或分组（GROUP BY）操作时，索引可以显著减少数据排序和分组的时间，因为索引中的数据已经是有序的。
加速表连接：在使用JOIN语句连接多个表时，对参与连接的列创建索引可以提高连接的效率。
实现数据的唯一性约束：通过在表上创建唯一性索引（UNIQUE INDEX），可以保证列中的数据不出现重复值，从而维护数据的唯一性。
优化条件过滤：索引可以帮助数据库更快地筛选满足特定条件的记录，尤其当这些条件针对索引列时。
辅助计算：在一些数据库引擎中，如InnoDB，索引可以用来直接完成一些COUNT()、MIN()和MAX()等聚合函数的快速计算，因为索引存储的是有序数据。
减少I/O成本：读取索引通常比读取整个表消耗的I/O要少，特别是在索引能够被覆盖的查询中，数据库可以只读取索引而避免读取数据行，减少了磁盘I/O操作。
改善锁性能：对于支持行级锁的存储引擎（如 InnoDB），索引可以减少锁定需要的行数目，从而提高并发访问性能。

简言之，索引是MySQL提高数据库性能和维护数据完整性的重要工具。不过，也要注意索引不是万能的，不恰当的索引会额外增加更新、删除和插入操作的开销，占用更多存储空间，并且可能对数据库性能产生负面影响。因此，如何根据应用场景合理设计和使用索引是数据库优化的重要方面。

索引分类：有兴趣就看看没兴趣就直接跳过（不重要）

提示：索引可以按照不同的维度被分类，包括按照数据结构、按照功能等来进行分类

功能分类

1、主键索引（PRIMARY KEY）：

用于唯一标识表中的每一行，并且不允许NULL值。

每个表只能有一个主键索引。

InnoDB存储引擎中的主键索引也是聚簇索引，数据实际存储在主键索引的叶子节点上。

使用场景：用于快速查找或排序基于主键的记录，外键关联等。

2、唯一索引（UNIQUE INDEX）：

确保某列（或多列组合）的值唯一。

允许有空值，但空值只能有一个。

使用场景：主要用于实施业务层面的数据唯一性，如用户邮箱、身份证号码等。

3、普通索引（INDEX）：

基础的索引类型，没有任何约束。

使用场景：加快对数据的检索速度，适用于常作为查询条件的列。

4、全文索引（FULLTEXT INDEX）：

专门用于全文搜索，支持非精确匹配上的文本搜索，如MATCH AGAINST查询。

在InnoDB和MyISAM存储引擎上可用。

使用场景：常用于文本数据的搜索引擎功能，如搜索文章、评论等。

5、空间索引（SPATIAL INDEX）：

用于地理数据存储，基于R-Tree数据结构。

仅MyISAM存储引擎支持。

使用场景：使用地理数据类型的列，如GIS地理位置查询。

7、索引前缀（PREFIX INDEX）：

对文本类型列只索引开始的部分字符。

使用场景：适用于长文本字段，能够节省索引空间，提高索引效率。

8、复合索引（COMPOSITE INDEX）：

基于多个列构建的索引，可以覆盖多个列。

MySQL在查询时可以利用组合索引的最左前缀规则。

使用场景：适合跨多个列进行查询的场景。

9、单一索引（SINGLE-COLUMN INDEX）：

指基于单一列构建的索引。

使用场景：适用于只涉及单一列的查询优化。

特殊说明：

Clustered与Nonclustered：这是索引的另一种分类方式。聚簇索引（如InnoDB的主键索引）中数据记录就存放在索引结构中，而非聚簇索引（如主键索引以外的其他索引）则不是。
锁定策略：某些索引如通过主键或唯一索引的搜索，可能对性能有更好的优化，尤其是在并发事务多的场景中，主键查找通常拥有更优的锁定性能。
选择合适的索引列：创建索引时需要考虑查询模式，选择性高的列（即列中不重复值多的列）作为索引通常更有效。

总而言之，正确使用索引可以大幅提升数据库性能。但是，索引不是免费的午餐，它们在写操作时会带来额外的开销，因为索引本身也需要更新。因此，设计索引应该根据应用的查询模式，避免过度索引，使得维护成本和性能损耗最小化。