MySQL索引「连环问」1、索引概述 1.1 索引的概念与作用索引是一种特殊的数据结构，用于加速数据库的查询操作。它类

1、索引概述

1.1 索引的概念与作用

索引是一种特殊的数据结构，用于加速数据库的查询操作。它类似于书的目录，可以帮助用户快速找到所需的信息。在MySQL中，索引可以建立在表的一个或多个列上，可以提高查询效率、排序效率和连接效率。

索引主要是用来提高数据检索的效率，缩短查询执行时间，提高数据库的性能。它可以加速查询速度、减少IO操作、优化数据库性能、提高数据的一致性和完整性、支持唯一性约束等。

为什么要用到索引呢？

因为在数据库中，数据量通常非常大，如果没有索引的支持，每次查询都需要遍历整个数据集，这样会导致查询速度非常慢，甚至会造成系统崩溃。而使用索引可以帮助数据库快速定位到符合条件的数据，从而提高查询效率和系统性能。

索引的作用不仅仅是提高查询效率，它还可以用来保证数据的完整性和一致性，防止数据重复和冗余，以及优化数据的空间利用和存储结构。因此，在设计数据库和编写SQL语句时，合理使用索引是非常重要的。

1.2 索引类型

1.3 索引优缺点

索引的优点：

提高查询效率（加快查询，通过索引可以快速定位到需要查询的数据，提高查询效率）
减少数据扫描：（无索引，数据库需扫描整个数据表才能找到需要的数据，而有了索引之后，可以直接访问索引，减少数据扫描的时间）
约束数据唯一性（添加唯一索引）
支持排序（支持排序操作，提高排序效率）

索引的缺点：

占用存储空间（需占用存储空间，若数据表中的数据很多，可能会占大量存储空间）（索引存放在磁盘）
降低写入性能（每写入数据都需更新索引，影响写入性能）
创建索引和维护索引要耗费时间（这种时间随着数据量的增加而增大）
维护代价高（若数据表中数据发生修改，索引也需更新）
不适用于小表（对于小表来说，使用索引可能会降低查询效率，因为索引需要占用存储空间，并且创建索引也需要时间）

综上，索引是一种提高查询效率的有效手段，但也存在一些缺点，需要在实际应用中进行权衡。

1.4 索引在项目中的使用方式

一是验证你的项目场景的真实性，二是为了作为深入发问的切入点
缓存
分布式锁
消息队列、延迟队列
……

1.5 优化器选择不使用索引的情况

在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。

对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。

1.6 怎么查看MySQL语句有没有用到索引？

使用explain命令：可以将执行计划输出到终端上，可以看到索引的使用情况
查看查询日志：使用log_queries_not_using_indexes参数开启查询日志功能，这样执行的SQL语句就会被记录下来，可以通过查看日志文件来判断是否使用了索引
使用show index命令：可看到表中的索引信息，包括索引名、列名、类型等。（show index命令通常用于查看表的索引信息，而不是查询语句的索引使用情况）

2、聚簇索引与非聚簇索引

2.1 聚簇索引、非聚簇索引

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

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

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

聚簇索引叶子节点中存放的是整张表的每一行数据记录，非聚簇索引的叶子节点存储该列对应的主键（行号）。
一张表只能有一个聚簇索引（必须有），可以有多个辅助索引。
通常情况下，聚簇索引查询只会查一次，而非聚簇索引需要回表查询多次。当然，如果是覆盖索引的话，查一次即可。（由于在 InnoDB 里面，主键索引表示的是一种数据存储结构，所以如果是基于非聚集索引来查询一条完整的记录，最终还是需要访问主键索引来检索。）（非聚簇索引可以是覆盖索引）
注意：MyISAM无论主键索引还是二级索引都是非聚簇索引（即不支持聚集索引），而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

聚簇索引的优缺点：

优点：数据访问更快，聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快；聚簇索引对于主键的排序查找和范围查找速度非常快。
缺点：插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能；更新主键的代价很高，因为将会导致被更新的行移动；二级索引访问需要两次索引查找，要回表，对于InnoDB，自适应hash索引能够减少这样的重复工作。

2.2 为什么官方建议使用自增主键作为索引？

每个表里面必须要有一个主键，如果没有主键，InnoDB 会默认选择或者添加一个隐藏列作为主键索引来存储这个表的数据行。一般情况是建议使用自增 id 作为主键。

这样的话 id 本身具有连续性使得对应的数据也会按照顺序存储在磁盘上（插入过程中可以减少页分裂和数据移动的频率），写入性能和检索性能都很高。否则，如果使用 uuid 这种随机 id，那么在频繁插入数据的时候，就会导致随机磁盘 IO，从而导致性能较低。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；

如果没有主键，就选择第一个不包含NULL值的唯一列作为聚簇索引的索引键（key）；

在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增id列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

2.3 mysql使用uuid做主键，当批量插入时，时间相同，怎么保证插入后再查询时这批数据能按照插入时的先后顺序排序

可以在插入时，在插入的数据中加入一个字段，比如插入时间，然后在查询时，按照插入时间进行排序即可。

2.4 普通索引上存储的是什么？

普通索引上存储的是索引建立的列的值及该值在表中的位置信息（相关记录的物理行地址，也称为簇内偏移量），而不是完整的记录。

当查询时，MySQL首先通过普通索引找到符合条件的记录的簇内偏移量，然后再通过簇内偏移量在数据页中查找对应的完整记录。

3、覆盖索引和回表

3.1 什么是覆盖索引？

InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，是指查询语句可以直接从索引中获取所需的数据，而不需要回到表中进行额外的数据获取操作（即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录）。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。另外索引覆盖只需要扫描一次索引树，不需要回表扫描聚簇索引树，所以性能比回表查询要高。

注意：覆盖索引技术最早是在InnoDB Plugin中完成并实现。这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0或以下的，InnoDB存储引擎不支持覆盖索引特性。

一个索引是不是覆盖索引，取决于是否包含了查询所需的所有列。如果一个索引包含了查询所需的所有列，那么就是覆盖索引；如果不包含所有列，就不是覆盖索引。但并不是查询包含所需的所有列就可以使用覆盖索引。

3.2 如何查看是否使用了覆盖索引？

使用 explain命令，通过查看 Extra 列可以看到Using index 的信息，证明使用了覆盖索引

3.3 覆盖索引的局限

需要注意的是，覆盖索引不是万能的，它也存在一些局限性。大型表的覆盖索引可能会占用大量磁盘空间，从而导致性能下降。此外，在使用覆盖索引时，应遵循良好的设计原则和SQL最佳实践，以充分利用这种技术带来的优势。

3.4 什么是回表？

通俗的讲就是，如果select所需获得列中有非索引列，一次索引查询不能获取所有信息，需要到表中找到相应的列的信息，这就叫回表。

而如果根据一次索引查询就能获得所有列信息，就不需要回表。

如果某个查询语句使用了非聚簇索引，但是查询的数据不是主键值，这时在非聚簇索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作 「回表」

3.5 回表对查询性能的影响是什么？

会增加额外的IO操作，导致额外的CPU和磁盘负担，以及额外的网络流量和延迟。

对于查询频繁但数据更新较少的场景，回表查询的性能影响可能不太明显，但是对于查询较慢或数据更新比较频繁的场景，回表查询的性能影响就会显著。

3.6 如何避免回表？

使用覆盖索引
使用JOIN替代子查询
- 在需要进行子查询时，可以使用JOIN操作替代，因为JOIN可以一次性将所需字段从多个表中读取出来。这样可以避免回表操作，提高查询性能。

3.7 聚簇索引需要回表吗？

由于聚簇索引存储的是整行数据，因此使用聚簇索引查询时就可以直接从索引中获取所需字段的值，无需回表获取记录。

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

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

例如，在学生表的年龄上建立了索引，当进行select age from student where age<20查询时，在索引的叶子节点上已经包含age信息，不会再次进行回表查询。

4、联合索引

4.1 什么是联合索引？

联合索引是指在一个表中，使用多个字段创建的索引，它可以提高查询的效率，减少查询的时间。

比如，将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)，创建联合索引的方式如下：

CREATE INDEX index_product_no_name 
ON product(product_no, name);

4.2 什么时候使用联合索引？

当查询语句中包含多个字段时，可以使用联合索引，以提高查询效率。

4.3 联合索引有什么需要注意的吗？

联合索引的字段顺序很重要，应该按照查询语句中使用的顺序来创建联合索引。
尽量避免在联合索引中使用多个字段，因为这样会增加索引的大小，降低查询效率。
尽量避免在联合索引中使用NULL值，因为NULL值不参与索引的查询。

4.4 为什么联合索引的字段顺序很重要

因为索引是按照字段顺序来查找数据的，如果字段顺序不正确，可能会导致查询效率降低。

使用联合索引时，存在最左匹配原则。若不遵循，索引失效。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

4.5 联合索引范围查询

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。

这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

4.6 场景题：如果你创建一个索引的话，比如一个字一个表里面有 a b c 三个字段，对 b 和 c 这两个字段去做一个索引，顺序的话是先 b 或后 c，那这时候我需要去查 c 这个字段能用上这个索引么？

如果要对 c 查询的话他无法使用到这个索引，因为我们刚才建的那个索引它是一个复合类型的，就是你既有 b 有 c，然后你只有在要你的查询条件包含 b c 的时候你才能按最左匹配原则去进行查询，而如果你只查 c 的话是无法使用这个索引的需要为 c 单独建立索引才可以。

补充：联合索引(b,c)，如果查询条件是有c字段，是无法利用走该联合索引的，因为联合索引的b+树是先按b排序，再按c排序，所以如果查询条件只有一个c 字段，它就无法在这颗联合索引的b+树进行二分查找，这就是联合索引有最左匹配规则的来源。

5、前缀索引

5.1 什么是前缀索引

前缀索引是指对文本的前几个字符建立索引，这样建立起来的索引更小，所以查询更快（查询效率更高）。

例如，如果要查找以“apple”开头的字符串，可以使用前缀索引，它可以快速查找出所有以“apple”开头的字符串，而不需要查找整个字符串。此外，前缀索引还可以用于查找字符串的结尾部分，从而提高查询效率。

作用：减少索引占用的存储空间，提高查询效率。

5.2 写一个前缀索引语句

在创建表时，创建前缀索引的方式如下：

CREATE TABLE table_name(column_list, INDEX(column_name(length)) ); -- 方法1
CREATE INDEX index_name ON table_name(column_name(length)); --方法2

建表后，如果要创建前缀索引，可以使用这面这条命令：

CREATE INDEX index_name ON table_name(column_name(length));

index_name是索引的名称，table_name是表的名称，column_name是要创建索引的列，length是要创建索引的前缀长度。

6、最左匹配原则

6.1 什么是最左匹配原则

最左前缀原则就是最左优先，在创建多列索引时（即在联合索引中），要根据业务需求，where子句中使用最频繁的一列放在最左边。

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法用到联合索引。比如a=1 and b=2 and c>3 and d=4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a，b，d的顺序可以任意调整。但是，对于 >=、<=、BETWEEN、like 前缀匹配这四种范围查询，并不会停止匹配。

=和in可以乱序，比如a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

6.2 联合索引为什么不遵循最左匹配原则会失效？

其原因就在于联合索引的 B+ 树中的键值是排好序的。不过，这里指的排好序，其实是相对的，举个例子，有 (a, b, c) 联合索引，a 首先是排序好的，而 b 列是在 a 列排序的基础上做的排序，同样的 c 是在 a,b 有序的基础上做的排序。所以说，如果有 where a = xxx order by b = xxx 这种请求的话，是可以直接在这颗联合索引树上查出来的，不用对 b 列进行额外的排序；而如果是 where a = xxx order by c = xxx 这种请求的话，还需要额外对 c 列进行一次排序才行。另外，如果有对 a,b,c 的联合条件查询的话，并且 a 是模糊匹配或者说是范围查询的话，其实并不能完全踩中联合索引（a,b,c），a 列右边的所有列都无法使用索引进行快速定位了。所以这个时候就需要进行回表判断。也就是说数据库会首先根据索引来查找记录，然后再根据 where 条件来过滤记录。

不过在 MySQL 5.6 中支持了索引下推 ICP，数据库在取出索引的同时，会根据 where 条件直接过滤掉不满足条件的记录，减少回表次数。

6.3 哪些情况下mysql不符合最左匹配原则但走了索引？

有些情况下MySQL会不按照最左匹配原则但走了索引。这些情况包括：

查询条件包含IN或者OR操作符的情况：当查询语句中含有IN或者OR操作符时，MySQL可能会利用部分复合索引加速查询，即使查询条件不符合最左匹配原则。这可能是因为这些操作符导致了索引键的多列匹配，使得MySQL认为使用索引比执行全表扫描更加高效。
使用了覆盖索引：当查询只需要访问索引中的部分数据，即使没有使用最左边的索引列，数据库也可能选择使用该索引。这种情况下，索引可以覆盖查询所需的数据，从而减少需要访问的数据页数量，提高查询性能。
使用了聚合函数：如SUM、COUNT、AVG等。
查询中使用了order by或者group by：如果查询语句中使用了order by/group by子句，并且order by/group by子句中的列是复合索引的一部分，MySQL可能会选择使用这个索引来加速查询，即使查询条件不符合最左匹配原则。
使用了前缀索引：查询中只使用到了索引的前几个列，而没有使用到所有列，查询条件会出现不符合最左匹配原则的情况。
查询优化器的选择：MySQL的查询优化器会根据查询的统计信息和索引的选择性来决定最优的查询执行计划。有时，优化器可能会选择不相关的列进行索引匹配，以获得更快的查询性能。

需要注意的是，虽然MySQL在一些特定情况下可能会不按照最左匹配原则但走了索引，但是这并不一定会提高查询性能。

6.4 mysql不符合最左匹配原则但走了索引是为什么？

（这道题好像与上一题一样）

在某些情况下，MySQL可能会使用不符合最左匹配原则的索引，这是因为MySQL的优化器会根据查询开销和索引统计信息来做出决策。如果MySQL认为使用这个索引比全表扫描更加高效，那么它就会使用这个索引。

举个例子，如果一个表有一个复合索引包含(a,b,c)三个列，如果查询语句的条件是如下：

WHERE b = 1 AND c = 2

这个查询条件不符合最左匹配原则，因为第一个列a并没有被使用。但是如果统计信息显示，这个表的数据量很大，同时b列和c列上的数据分布很不均匀，那么MySQL可能会选择使用这个复合索引。因为即便查询条件不从最左侧开始匹配，使用这个索引也能更快地过滤出大部分数据，然后再通过其他方式来过滤数据。

总而言之，虽然不符合最左匹配原则的查询无法充分利用索引，但是MySQL的优化器在某些情况下可能会选择使用索引以提高查询性能。

7、索引下推

7.1 了解索引下推吗？

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

索引下推优化默认开启，使用SET optimizer_switch = 'index_condition_pushdown = off';可以将其关闭。

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

8、正确使用索引的一些建议

8.1 什么时候适用索引？

查询的数据量比较大，使用索引可以加快查询速度，减少数据库的扫描次数。
字段有唯一性限制的，比如商品编码；
经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
经常用于 GROUP BY（分组）和 ORDER BY（排序）的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。
被经常用于连接的字段：对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

8.2 什么时候不适合使用（创建）索引？

数据唯一性差的字段（字段中存在大量重复数据）不要使用索引，例如性别，因为这样的索引的二叉树级别少，多是平级，查询效果不如全表扫描。
频繁更新的字段不要使用索引，例如登录次数，因为频繁更新会导致索引也频繁变化，增加数据库工作量，降低效率。
字段不在WHERE语句中出现时不要添加索引，因为只有在WHERE语句中出现，MySQL才会去使用索引。（WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。）
表数据太少的时候不要使用索引，因为使用索引的效果不大。
如果MySQL估计使用全表扫描比使用索引更快，则不会使用索引。

8.3 如何选择在哪些列上建索引

一张表一般都要去建主键，所以主键索引几乎是每张表必备的。

选择性高的列，也就是重复度低的列。比如女子学校学生表中的性别列，所有数据的值都是女，这样的列就不适合建索引。比如学生表中的身份证号列，选择性就很高，就适合建索引。
经常用于查询的列（出现在where条件中的列）。不过如果不符合上一条的条件，即便是出现在where条件中也不适合建索引，甚至就不应该出现在where条中。
多表关联查询时作为关联条件的列。比如学生表中有班级ID的列用于和班级表关联查询时作为关联条件，这个列就适合建索引。
值会频繁变化的列不适合建索引。因为在数据发生变化时是需要针对索引做一些处理的，所以如果不是有非常必要的原因，不要值会频繁变化的列上建索引，会影响数据更新的性能。反过来也就是说索引要建在值比较固定不变的列上。
一张表上不要建太多的索引。和上一条的原因类似，如果一张表上的索引太多，会严重影响数据增删改的性能。也会耗费很大的磁盘空间。

8.4 选择合适的字段创建索引

不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

8.5 索引为什么会失效？（失效场景）

在索引列上做运算。之所以会导致索引失效是因为改变了索引原来的值，在树中找不到对应的数据只能全表扫描。
未遵循最左匹配原则。在组合索引里面，索引的存储结构是按照索引的列的顺序来进行存储的，因此啊，在 SQL 里面也需要按照这个顺序才能去进行逐一匹配，否则 InnoDB 无法识别索引导致索引失效。
当索引列存在隐式转换的时候。比如说索引列是字符串类型，但是在 SQL 查询里面呢，没有使用引号，那么 MySQL 会进行一个自动类型的转化，从而去导致索引失效。
在索引列使用不等于号或者 not 查询的时候，由于索引的数据检索效率非常低，因此 MySQL 引擎会判断不走索引会更合适。
使用 like 通配符去匹配后缀的时候（like%），由于这种方式不符合索引的最左匹配原则，所以呢也不会走索引。但是反过来啊，如果通配符匹配的是前缀，那么符合最左匹配原则，也就能够去走索引。
使用 'or' 关键字，在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。只有 'or' 左右查询字段都使用索引列的时候才会生效。

那么除了这些场景以外，对于多表连接查询的场景里面，连接顺序也会影响到索引的一个使用，不过最终是否会走索引，我们可以使用 explain 命令去查看当前的 SQL 执行计划，然后我们可以针对性的去进行一个调优，从而去提升 SQL 的一个执行效率。

8.6 隐式转换造成的索引失效

MySQL索引隐式转换是指在查询过程中，MySQL自动将查询条件中的类型转换为索引列的类型，以便匹配索引并提高查询效率。

在mysql查询中，当查询条件左右两侧类型不匹配的时候会发生隐式转换，可能导致查询无法使用索引。

9、索引优化

9.1 有什么优化索引的方法？

常见的有：前缀索引优化；覆盖索引优化；主键索引最好是自增的；防止索引失效。

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。不过，前缀索引有一定的局限性，例如：
- order by 就无法使用前缀索引；
- 无法把前缀索引用作覆盖索引；
使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

详见：索引常见面试题 | 小林coding (xiaolincoding.com)

10、其他

10.1 模糊查询（%abc 和 abc%）是否用到索引

like %abc 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。eg：
```
select * from company where reverse(companyName) like reverse('%江南皮革厂');
```
like abc% 索引有效。
like %abc% 索引失效，也无法使用反向索引。

10.2 SQL表里面有学生姓名，语文成绩、数学成绩、英语成绩，查询平均分最高的三个同学？如何优化？

可以使用以下SQL查询语句来查询平均分最高的三个同学：

SELECT name, AVG(chinese+math+english) AS avg_score
FROM student_table
GROUP BY name
ORDER BY avg_score DESC
LIMIT 3;

其中，student_table是学生信息表的名字，包含了学生姓名、语文成绩、数学成绩和英语成绩。GROUP BY用来分组计算每个学生的平均分，ORDER BY按平均分从高到低排序，LIMIT 3限制只返回前三名。

为了优化查询，可以考虑以下几点：

索引优化：在学生信息表中，可以给姓名、语文、数学、英语成绩等字段添加索引，提高查询速度。
数据库分区：将学生信息表分成多个分区，根据分区的划分方式，将同一批次或同一年级的学生放在同一个分区，可以提高查询速度。
数据库缓存：将经常被查询的数据缓存在内存中，每次查询时先从缓存中读取数据，可以减少对数据库的访问，提高查询速度。

10.3 A、B、C三列怎么设计索引

索引的设计需要根据具体的查询需求和数据特征来考虑，这里给出几种常见的情况。

如果列A、B、C都有高频的查询，且经常需要组合条件查询，可以考虑创建一个联合索引（也称为复合索引）来覆盖A、B、C三列。

 CREATE INDEX idx_abc ON table_name (A, B, C);

这样的索引可以支持对A、B、C三列的单列查询、组合条件查询以及ORDER BY操作的快速执行，但是需要注意的是，联合索引的可行性需要根据具体的数据量和查询情况来评估。

如果列A经常被用于WHERE条件的过滤查询，列B经常用于ORDER BY操作，可以采用下面的方式来设计索引：

 CREATE INDEX idx_a ON table_name (A);
 CREATE INDEX idx_b ON table_name (B);

这样的索引可以快速执行基于列A的查询过滤、基于列B的ORDER BY操作，但是需要注意的是，索引的设计应该避免在维护索引的成本和查询效率之间取得平衡，必须根据实际情况进行权衡和调整。

如果列A的值重复较少，B和C上的查询较为频繁，可以采用以下设计方式来创建索引：

 CREATE INDEX idx_b ON table_name (B);
 CREATE INDEX idx_c ON table_name (C);

这样可以在查询时快速定位B和C的值，从而提高查询效率。

需要注意的是，索引的创建需要根据具体的数据和查询需求来进行，索引数量和索引字段的选择应该根据实际情况来评估。(PS：另外可以说说索引设计的注意事项)

10.4 普通索引和唯一索引怎么选？

查询
- 当普通索引为条件时查询到数据会一直扫描，直到碰到下一个记录不满足条件为止
- 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

但是这个造成的性能的差异是很小的，因为mysql每一次从磁盘上加载一个页的数据大小是16kb，在内存中查询数据是很快的，所以不会产生很大的数据的差异。