mysql面试题——索引创建与设计原则

沧浪之水12010137

已于 2024-02-27 09:41:15 修改

阅读量1k

点赞数 19

分类专栏： mysql 文章标签： mysql

于 2023-12-05 02:08:04 首次发布

本文链接：https://blog.csdn.net/weixin_44582492/article/details/134797416

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一：哪些情况适合创建索引？

字段的数值有唯一性的限制
频繁作为 WHERE 查询条件的字段
经常 GROUP BY 和 ORDER BY 的列

DISTINCT 字段需要创建索引：有时候我们需要对某个字段进行去重

 SELECT DISTINCT(student_id) FROM `student_info`;
 运行结果（600637 条记录，运行时间 0.683s ）：
 如果我们对 student_id 创建索引，再执行 SQL 语句：
 SELECT DISTINCT(student_id) FROM `student_info`;
 运行结果（600637 条记录，运行时间 0.010s ）：

多表 JOIN 连接操作时，创建索引注意事项

 首先， 连接表的数量尽量不要超过 3 张，
 其次， 对 WHERE 条件创建索引
 最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致

使用列的类型小的创建索引
使用字符串前缀创建索引
使用最频繁的列放到联合索引的左侧
在多个字段都要创建索引的情况下，联合索引优于单值索引

二：哪些情况不适合创建索引

在where中使用不到的字段，不要设置索引
. 数据量小的表最好不要使用索引
有大量重复数据的列上不要建立索引
避免对经常更新的表创建过多的索引

不建议用无序的值作为索引

例如身份证、UUID、MD5、HASH、无序长字符串等。

删除不再使用或者很少使用的索引

不要定义冗余或重复的索引
（1）冗余索引

 CREATE TABLE person_info(
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
 PRIMARY KEY (id),
 KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
 KEY idx_name (name(10))
 );

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引
（2）重复索引

CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);

我们看到，col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免

三：慢SQL的问题如何排查？

慢查询是指数据库中查询时间超过指定阈值的SQL，这个阈值根据不同的业务来说一般是不一样的，慢SQL的问题排查一般分为几个步骤：发现问题，定位问题，解决问题
发现问题
在MySQL的配置文件 my.cnf，配置启用慢SQL日志

slow_query_log = 1
slow_query_log_file =/path/to/slow-query.log
long_query_time = 1

定位问题
通过上述的日志中，我们就可以找到对应的慢SQL的具体SQL了，然后就可以进一步分析为什么这个SQL是慢SQL了，大多数情况，是可以通过执行计划分析出一条SQL的慢的原因的，大部分来说，主要是索引的问题和join的问题。
解决问题
一旦一个问题被定位到了，解决起来都比较容易。缺索引就加索引，join太多就拆分就好了。

四：SQL执行计划分析的时候，要关注哪些信息?

一个执行计划中，共有12个字段，每个字段都挺重要的，先来介绍下这12个字段
1.id：执行计划中每个操作的唯一标识符。对于一条查询语句，每个操作都有一个唯一的
id。但是在多表join的时候，一次explain中的多条记录的id是相同的。
2、select_type：操作的类型。常见的类型包括SIMPLE、PRIMARY、SUBQUERY、UNION
等。不同类型的操作会影响查询的执行效率。
3：table：当前操作所涉及的表。
4.partitions：当前操作所涉及的分区。
5.type：表示查询时使用的索引类型，包括ALL、index、range、ref、eq_ref、const
等。
6. possible_keys：表示可能被查询优化器选择使用的索引。
7. key：表示查询优化器选择使用的索引。
8. key_len：表示索引的长度。索引的长度越短，查询时的效率越高。
9. ref：用来表示哪些列或常量被用来与key列中命名的索引进行比较。
10. rows：表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。
11.filtered：表示此操作过滤掉的行数占扫描行数的百分比。该值越大，表示查询结果越准
确。
12.Extra：表示其他额外的信息，包括Using index、Using filesort、Using temporary
等。

五：执行计划中，key有值，还是很慢怎么办？

执行计划中，type=index,key=idx_abcd 很多人会认为这表示这条SQL走了索引，如果是走了索引Extra中的内容应该是Using index 而不是 using where; using index，这个执行计划表明，这个SQL确实用到了idx_abcd的这个索引树，但是他并没有直接通过索引进行匹配或者范围查询，而是扫描了整颗索引树。
所以，[type=index）意味着进行了全索引扫描，会遍历索引树来查找匹配的行，这个效率比扫表扫描快一些，但是很有限，和我们通常意义上理解的走了索引是两回事儿。遇到这种情况，大概率是因为没有遵守最左前缀匹配导致的索引失效了。
在这里插入图片描述

六：索引失效的问题如何排查?

第一步：先找到要分析的SQL语句，然后通过explain查看他的执行计划。主要关注type、key和extra这几个字段。
第二步：我们需要通过key+type+extra来判断一条SQL语句是否用到了索引。
第三步：如果通过执行计划之后，发现一条SQL没有走索引，进一步分析没有走索引的原因。以下几种情况可能会导致没走索引：

没有正确创建索引：当查询语句中的where条件中的字段，没有创建索引，或者不符合最左前缀匹配的话，就是没有正确的创建索引。
查询语句中，索引字段因为用到了函数、类型不一致等导致了索引失效
索引区分度不高：如果索引的区分度不够高，那么可能会不走索引，因为这种情况下走索引的效率并不高。
表太小：当表中的数据很小，优化器认为扫全表的成本也不高的时候，也可能不走索引

七：索引失效案例

全值匹配我最爱
最佳左前缀法则
计算、函数、类型转换(自动或手动)导致索引失效
范围条件右边的列索引失效
不等于(!= 或者<>)索引失效
is null可以使用索引，is not null无法使用索引
like以通配符%开头索引失效
OR 前后存在非索引的列，索引失效

八：什么是最佳左前缀匹配？

在MySQL中，最佳左前缀匹配是指在查询中利用索引最左边的一部分来进行匹配，如果查询条件涉及到组合索引的前几个列，MysQL 就可以利用这个复合索引来进行匹配。
举例说明：假如我们创建了一个组合索引（col1, col2, col3)
如果你的查询条件是针对 col1 、(col1, col2)或者(col1, col2, col3)，那么MySQL可以利用这个复合索引进行最左前缀匹配。
如果查询条件涉及到的列只有 col2 或者只有col3或者只有col2和col3，总之就是如果不包col1的话，那么是没有遵守最左前缀匹配，就不能利用这个索引进行最左前缀匹配。
并且，需要注意的是，最左前缀匹配和查询条件的顺序没有关系，不管你写的是 where col1 =“Holiis” and col2 ="666"还是(where col2 =“666” and col1=“Holiis”对结果都没有影响，该命中还是会命中。

九：MySQL索引一定遵循最佳左前缀匹配吗？

索引底层是一个B+树，如果是联合索引的话，在构造B+树的时候，先按照左边的key进行排序，左边的key相同时再依次按照右边的key排序。所以，在通过索引查询的时候，也需要遵守最佳左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。