【MySQL】索引面试题

文章目录

- - 适合创建索引的情况
  - 创建索引的注意事项
  - MySQL中不适合创建索引的情况
  - 索引失效的常见情况

在这里插入图片描述

索引定义与作用
- 索引是帮助MySQL高效获取数据的有序数据结构，通过维护特定查找算法的数据结构（如B+树），以某种方式引用数据，实现高级查找算法，从而提高数据检索效率，降低数据库IO成本。
- 例如在查找年龄为45的数据时，无索引需逐条比对，有索引（如二叉树）可减少对比次数，提升效率。
二叉树与红黑树的不足
- 二叉树：普通二叉树时间复杂度不稳定，可能退化为链表（O(n)），即使相对平衡时为O(log n)，也不满足MySQL需求，所以MySQL底层未使用。
- 红黑树：节点可保证平衡，时间复杂度稳定为O(log n)，但数据量大（如1000万数据）时，因其二叉结构节点分叉有限，树会很高，查找需找很多层级，效率不高。
B树的特点
- B树是多岔路平衡查找树，每个节点可有多分支（如度数为五时，最多五个子节点），节点上最多存储一定数量的key（如四个），key有指针指向子节点数据，且存储对应数据。整体为“矮胖”结构，查找层级短，效率高。
B+树的结构与优势

在这里插入图片描述
- 结构优化：在B树基础上优化，更适合外存储索引结构（如InnoDB存储引擎默认采用）。非叶子节点只存储指针，不存储数据，存储压力低；数据只存储在叶子节点，非叶子节点主要起导航作用，便于找到叶子节点数据。
- 磁盘读写代价低：查找数据时，B树需加载路径上节点数据，B+树非叶子节点不存数据，只需通过指针导航找到叶子节点获取数据，无需额外加载非叶子节点数据，效率更高，磁盘读写代价更低。
- 查找效率稳定：所有数据在叶子节点，查找时从根节点依次对比到叶子节点获取数据，查找路径相似，效率稳定。
- 便于扫库和区间查询：叶子节点之间使用双向指针连接，进行范围查询（如查询6 - 34区间数据）时，从根节点找到起始值所在叶子节点后，可利用双向指针一次性获取区间内所有数据，无需多次从根节点查找，效率更高。

面试题回答要点
- 索引定义及作用方面：强调索引是有序数据结构，用于高效获取数据，可提高检索效率、降低IO成本（因B+树查找层级短），还能通过索引排序降低数据排序成本，间接降低CPU消耗（创建索引时已排序）。
- 索引底层数据结构方面：说明MySQL的InnoDB引擎默认采用B+树存储索引，其特点包括阶数多、路径短（性能高）、磁盘读写代价低（非叶子节点只存指针，数据在叶子节点）以及便于扫库和区间查询（叶子节点间双向链表）。

在这里插入图片描述

在MySQL中，合适地创建索引可以显著提高查询性能，但索引也并非越多越好，不当的索引可能会影响数据插入、更新和删除的效率。以下是一些适合创建索引的常见情况：

适合创建索引的情况

频繁作为查询条件的列：如果某列经常出现在WHERE子句中，作为查询条件，那么为该列创建索引可以加快查询速度。
经常用于连接（JOIN）操作的列：在多表连接查询中，连接条件列通常应该创建索引，以加速连接操作。例如，在一个订单表和用户表的连接查询中，订单表的用户ID列和用户表的主键列（通常也是用户ID）都应该创建索引，这样可以快速定位匹配的行，提高连接性能。
在分组（GROUP BY）和排序（ORDER BY）操作中使用的列：当查询需要进行分组或排序操作时，为参与分组和排序的列创建索引可以避免数据库进行额外的排序操作，从而提高查询性能。例如，在查询每个部门的员工数量并按照部门名称排序时，为部门名称列创建索引可以加速分组和排序过程。
查询返回结果集较小的列：如果一个查询返回的结果集相对较小，但查询条件涉及的列数据量较大，创建索引可以帮助快速定位到满足条件的少量行，从而提高查询效率。例如，在一个包含大量商品信息的表中，根据商品类别查询特定类别下的少数热门商品，为商品类别列创建索引可以快速筛选出相关商品。
唯一约束列：对于具有唯一性约束的列，数据库通常会自动创建唯一索引，这不仅有助于确保数据的唯一性，还能提高基于该列的查询速度，因为数据库可以直接使用索引快速定位到特定的值。例如，用户表中的用户名或邮箱列，通常需要保证唯一性，并且在用户登录或查询特定用户时，这些列经常被使用，因此创建唯一索引是很有必要的。

创建索引的注意事项

避免过度索引：每个索引都会占用一定的存储空间，并在数据插入、更新和删除时需要额外的维护成本。如果为太多列创建索引，可能会影响数据库的整体性能，特别是在写操作频繁的表上。因此，只应该为那些真正需要提高查询性能的列创建索引。
选择合适的数据类型：索引列的数据类型会影响索引的性能。尽量使用简单、占用空间小的数据类型，例如整数类型通常比字符串类型在索引查找上更高效。如果可能的话，对于字符串类型列，可以考虑使用合适的前缀索引，而不是对整个字符串创建索引，以减少索引占用的空间和提高查询性能。
复合索引的使用：在创建索引时，有时候可以考虑创建复合索引（即包含多个列的索引）。复合索引适用于经常同时根据多个列进行查询的情况。但要注意索引列的顺序，应该将选择性高（即不同值较多）的列放在前面，这样可以更快地缩小查询范围。例如，如果经常根据城市和地区查询用户信息，并且城市的选择性高于地区，那么创建索引时应该先写城市列，再写地区列。
定期维护索引：随着数据的不断更新和插入，索引可能会变得碎片化，影响查询性能。因此，需要定期对索引进行维护，例如使用OPTIMIZE TABLE语句来重建和优化索引，以确保索引的效率。

MySQL中不适合创建索引的情况

数据量较小的表：如果表中的数据量非常小，例如只有几十条或几百条记录，全表扫描的速度可能比使用索引更快。因为使用索引需要额外的开销来维护索引结构，在数据量小时，这个开销可能超过索引带来的查询性能提升。例如，一个存储系统配置参数的小表，数据很少变动且查询频率不高，此时创建索引可能得不偿失。
频繁更新、插入或删除的列：对频繁进行写操作的列创建索引会增加数据库的维护成本。每次数据更新、插入或删除时，数据库都需要更新相关索引，这会影响写操作的性能。例如，在一个记录用户操作日志的表中，操作时间戳列经常更新，若为该列创建索引，会显著降低写操作速度。
区分度低的列：如果某列的取值非常有限，例如只有“男”“女”两种值，或者大部分值都相同，创建索引的效果可能不佳。因为索引在这种情况下无法有效缩小查询范围，数据库可能仍需扫描大量数据行。例如，一个员工表中的性别列，通常不适合创建索引（除非在特定场景下有特殊需求）。
数据行数频繁变动且分布不均匀的列：如果列的数据分布极不均匀，且数据行数经常变动，索引可能会频繁进行重新平衡和优化，这会消耗数据库资源。例如，某电商网站订单表中的订单状态列，可能大部分订单处于“已完成”状态，且新订单不断增加，状态频繁更新，此时为该列创建索引可能不利于性能优化。
长字符串列或大文本列：对于非常长的字符串列（如存储文章内容的列）或大文本列（如存储用户评论的列），创建索引会占用大量的存储空间，并且在查询时比较操作也会消耗较多资源，索引的维护成本较高。通常，只在需要根据这些列进行精确查询且查询频率较高时，才考虑创建索引，且可能更适合使用前缀索引等方式来减少索引大小。
查询中很少使用的列：如果某列在实际查询中很少被作为查询条件或连接条件使用，那么为其创建索引没有实际意义，反而会增加数据库的存储和维护负担。例如，一个产品表中有一个“产品介绍”列，该列主要用于展示产品详情，很少在查询中用到，就不需要为其创建索引。

索引失效的常见情况

使用函数或表达式对索引列进行操作：如果在查询条件中对索引列使用了函数（如SUBSTRING、DATE_FORMAT等）或表达式（如age + 1 = 30），索引可能失效。因为索引是基于列的原始值构建的，经过函数或表达式计算后，数据库无法直接使用索引进行快速定位。例如：

SELECT * FROM users WHERE SUBSTRING(name, 1, 3) = 'abc';

在这个查询中，对name列使用了SUBSTRING函数，即使name列有索引，数据库也可能无法有效利用该索引，导致查询效率降低。
2. 隐式类型转换：当查询条件中索引列的数据类型与实际传入的值的数据类型不匹配时，数据库可能会进行隐式类型转换，这可能导致索引失效。例如，如果age列是整数类型，而查询条件写成WHERE age = '30'（传入的是字符串类型），数据库会先将字符串转换为整数再进行比较，这种隐式转换可能使索引无法正常使用。
3. 对索引列进行运算：在查询条件中对索引列进行算术运算（如salary * 2 > 5000），会使索引失效。因为数据库需要先计算表达式的值，然后再进行比较，无法直接利用索引进行快速查找。
4. 使用OR连接多个条件，且其中有索引列和非索引列的条件：如果在WHERE子句中使用OR连接多个条件，并且其中既有索引列的条件又有非索引列的条件，那么索引可能失效。例如：

SELECT * FROM users WHERE age = 30 OR address = 'Beijing';

如果age列有索引，address列没有索引，这个查询可能不会使用age列的索引，因为OR操作使得数据库难以优化查询。
5. LIKE查询以通配符开头：当使用LIKE进行模糊查询时，如果通配符%在查询字符串的开头，索引可能失效。例如：

SELECT * FROM products WHERE product_name LIKE '%phone';

这种查询会导致数据库进行全表扫描，因为索引无法快速定位以通配符开头的数据。
6. 索引列参与了表达式计算或函数调用：与使用函数或表达式对索引列进行操作类似，只要索引列参与了表达式计算或函数调用，索引就可能无法正常工作。例如：

SELECT * FROM orders WHERE YEAR(order_date) = 2023;

这里对order_date列使用了YEAR函数，会使该列的索引失效。
7. IS NULL或IS NOT NULL条件对索引列的使用：在某些数据库中，对索引列使用IS NULL或IS NOT NULL条件可能导致索引失效，因为索引可能不包含空值的相关信息。不过，有些数据库对这种情况进行了优化，具体是否失效取决于数据库的实现。
8. 索引列使用不等于（!=或<>）操作符：如果在查询条件中对索引列使用不等于操作符，数据库可能认为使用索引的成本较高，而选择全表扫描，导致索引失效。例如：

SELECT * FROM employees WHERE salary!= 5000;

查询条件中的数据分布不均匀导致索引选择不佳：如果索引列的数据分布非常不均匀，例如某一列大部分值都相同，只有少数不同的值，数据库在查询时可能认为使用索引的效果不如全表扫描，从而不使用索引。这种情况相对较少见，但在某些特定的数据场景下可能发生。