MySQL 索引面试题

MySQL 索引面试题

1. 什么是索引

索引是帮助MySQL高效获取数据的数据结构

2. 索引的优点

1) 减少服务器需要扫描的数据量

2) 帮助服务器避免排序和临时表（可以用order by和group by *** 作帮助服务器避免排序和临时表）

3) 索引可以将随机IO变成顺序IO

3. 索引的缺点

1) 索引要占用磁盘空间

2) 索引会降低写入数据的速度（insert、update、delete），这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。

4. 索引类型（数据结构/存储方式区分）

Tree、Hash

5. 索引类型（功能/逻辑）

1) 普通索引

作用：仅加速查询

允许重复值和空值（null）

创建关键字：INDEX

2) 唯一索引

作用：加速查询 + 列值唯一

允许有空值（null）。如果是组合索引，则列值的组合必须唯一

创建关键字：UNIQUE

3) 主键索引

作用：加速查询 + 列值唯一

不允许空值（not null）。

创建关键字：PRIMARY

4) 空间索引

作用：空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展

主要用于地理空间数据类型 GEOMETRY，创建时必须声明为NOT NULL

只能在引擎为MyISAM表中创建

5) 全文索引

作用：主要用来查找文本中的关键字，只能在 CHAR、VARCHAR或TEXT类型的列上创建

允许在索引列中插入重复值和空值（null）

对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

创建关键字：FULLTEXT

6. 索引类型（实际使用区分）

1) 单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

2) 多列索引（也可称为组合索引、复合索引）

组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

7. 高性能索引策略

1) 独立的列

指索引列不能是表达式的一部分，也不能是函数的参数。

2) 前缀索引和索引的选择性

对于BLOB、TEXT、长的VARCHAR类型只索引开始部分的字符，可以大大节约索引空间从而提高索引效率，但是降低了索引的选择性

选择性：不重复的索引值和表总记录数的比值。选择性越高，查询效率越高。

--注意1：前缀索引只能适用于普通索引中，不能使用在unique中

--注意2：使用前缀索引后，无法在使用覆盖索引，面对查询条件，可能需要回表 *** 作。

3) 最左前缀匹配原则

多列建立组合索引时，左边连续的一个或者几个列均可使用索引

--注意1：如果最最左边的字段使用 < 或 > 则不走索引，非第一个字段使用<或>则后面的字段不使用索引。例：索引a_b_c, WHERe a=8 AND b>9 AND c=5，a和b走索引，c不走索引

4) 覆盖索引避免回表 *** 作

一个索引包含所有要查询的字段的值，查询只需扫描索引而无需回表。覆盖索引只是一种查询的一种效果

--注意：mysql只能使用B-Tree索引做覆盖索引

8. 索引失效

1) NOT IN

SELECT * FROM `table` WHERe `id` NOT IN (1,2,3);

2) LIKE （左边模糊查询时，索引会失效）

SELECt * FROM `table` WHERe `nickName` LIKE '%xxx%';

3) !=

SELECt * FROM `table` WHERe `id` != 2;

4) <>

SELECt * FROM `table` WHERe `id` <> 2;

5) OR （OR两边都查询主键索引时，才会走索引。例：`id` = 2 OR `id` = 3）

SELECt * FROM `table` WHERe `id` = 2 OR `age` = 18;

可优化为：SELECt * FROM `table` WHERe `id` = 2 UNIOn SELECt * FROM `table` WHERe `id` = 2;

6) 索引列上使用内置函数 （索引的列为函数的参数时，索引会失效）

SELECt * FROM `table` WHERe `id` FROM_UNIXTIME(`create_time`, '%Y-%m-%d') = '2021-12-07';

可优化为：SELECt * FROM `table` WHERe `create_time` = unix_timestamp('2021-12-07');

7) 隐式类型转换 （索引列为字符串类型，查询的值为数字类型时，索引会失效）

SELECt * FROM `table` WHERe `mobile` = 1866666;

8) 索引列上使用算数运算或其他表达式运算

SELECt * FROM `table` WHERe `id` + 1 = 5;

9. Mysql 慢查询如何优化

1) 检查是否走索引，如果没走索引则优化sql利用索引

2) 检查所利用的索引是否最优索引

3) 检查所查字段是否都是必须的，是否查出了过多字段，查询了多余数据

4) 检查表中数据是否过多，是否该进行分库分表，一般数据达到五六百完就可分库分表

5) 检查数据库实例所在机器性能的配置，是否太低，是否可以适当增加资源

10. 建立索引的原则

1) 唯一索引

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引

2) 前缀索引

在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度

3) 限制索引数量

每创建一个索引要占用相应的磁盘空间，索引还会降低写入速度，一张表索引应该不超过5个

4) 尽量使用组合索引

减少磁盘空间开销，可以使用覆盖索引策略，效率高

5) 排序（ORDER BY）和分组（GROUP BY）字段上建立索引

6) 索引字段尽量避免NULL，可用0或空字符串来代替

   

备注：

执行计划Extra：Using filesort 文件排序即就是没走索引

索引如果存在范围查询，那么索引有序性将无法利用。例：组合索引a_b_c

WHERe a=8 AND b BETWEEN 20 AND 30 ORDER BY C DESC

如果是最后的排序索引字段c使用范围查询，则不影响利用索引的有序性

使用单列索引排序，则不会利用到索引有序性

如果查询条件中有多个单列索引，其中某个单列索引使用范围查询，则MySQL会根据自己的策略选择一个最优索引，其他索引则会失效