MySQL 专栏面试题-1
1.1 MySQL索引使用有哪些注意事项呢？
可以从三个维度回答这个问题：✨索引哪些情况会失效，索引不适合哪些场景，索引一些规则特性。
1.1.1 索引哪些情况会失效
InnoDB引擎里面有两种索引类型，一种是主键索引、一种是普通索引。
InnoDB用了B+树的结构来存储索引数据。
当使用索引列进行数据查询的时候，最终会到主键索引树中查询对应的数据行进行返回。
理论上来说，使用索引列查询，就能很好的提升查询效率，但是不规范的使用会导致索引失效，从而无法发挥索引本身的价值。
导致索引失效的情况有很多：
1、在索引列上做运算，比如使用函数，Mysql在生成执行计划的时候，它是根据统计信息来判断是否要使用索引的。
而在索引列上加函数运算，导致Mysql无法识别索引列，也就不会再走索引了。
不过从Mysql8开始，增加了函数索引可以解决这个问题。
2、在一个由多列构成的组合索引中，需要按照最左匹配法则，也就是从索引的最左列开始顺序检索，否则不会走索引。
在组合索引中，索引的存储结构是按照索引列的顺序来存储的，因此在sql中也只有按照这个顺序才能进行逐一匹配。
否则InnoDB无法识别索引导致索引失效。
3、当索引列存在隐式转化的时候， 比如索引列是字符串类型，但是在sql查询中没有使用引号。
那么Mysql会自动进行类型转化，从而导致索引失效。
4、在索引列使用不等于号、not查询的时候，由于索引数据的检索效率非常低，因此Mysql引擎会判断不走索引。
5、使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。
但是反过来，如果通配符匹配的是前缀xxx%，符合最左匹配，也会走索引。
6、使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是索引列的时候，才会生效。
除了这些场景以外，对于多表连接查询的场景中，连接顺序也会影响索引的使用。
不过最终是否走索引，我们可以使用explain命令来查看sql的执行计划，然后针对性的进行调优即可。
1.1.2 索引不适合哪些场景
1、数据量少的不适合加索引
2、更新比较频繁的也不适合加索引
3、区分度低的字段不适合加索引（如性别）
1.1.3 索引的一些潜规则
1、覆盖索引
覆盖索引是一种数据库查询优化技术，它允许数据库系统在查询过程中只访问索引本身，而不需要访问数据表本身。当查询条件中涉及的列完全包含在索引中时，就发生了覆盖索引。这意味着，如果查询结果中的所有列都可以在索引中找到，那么数据库不需要读取任何数据行，直接从索引中获取所需数据。
覆盖索引的实现通常涉及多列索引，其中查询条件中的列作为索引的一部分。例如，如果一个查询只需要访问表中的两个列，而这两个列恰好是创建索引时指定的字段，那么这个查询就可以使用覆盖索引。覆盖索引可以减少数据库的IO操作，提高查询效率。
在Innodb存储引擎中，辅助索引的叶子节点可能包含主键列的信息，因此对于包含主键的查询，主键索引通常可以覆盖查询。例如，如果一个查询使用了一个包含多个列的索引，并且查询条件中的列都与这个索引相关，那么这个查询就可以利用覆盖索引，避免访问数据行。
覆盖索引是优化数据库查询性能的一个重要手段，它可以通过减少数据访问来提高查询速度。然而，它也可能导致一些性能问题，如当查询条件中的列不完全包含在索引中时，或者当查询涉及的列顺序与索引创建时的顺序不一致时，覆盖索引可能无法发挥作用。因此，在设计索引时，应该根据业务需求和查询模式来决定哪些列应该被包含在索引中，以及索引的顺序如何设置。
总结来说，覆盖索引是一种减少数据访问的优化技术，它通过确保查询条件中的列都在索引中来提高查询效率。正确使用覆盖索引可以显著提升数据库的性能，但也需要考虑查询的完整性和索引的正确性。
2、回表
什么是回表查询？
通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。
InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了主键，则PK就是聚集索引；
（2）如果表没有定义主键，则第一个非空唯一索引（not NULL unique）列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
先创建一张表，sql 语句如下：
然后，我们再执行下面的 SQL 语句，插入几条测试数据。
假设，现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。
但当我们使用 k 这个索引来查询 k = 2 的记录时就要用到回表。select * from xttblog where k = 2; 原因是通过 k 这个普通索引查询方式，则需要先搜索 k 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。
我这里表里的数据量比较少，如果数据量大的话，你能很明显的看出两次查询所用的时间，很明显使用主键查询效率更高。
使用聚集索引（主键或第一个唯一索引）就不会回表，普通索引就会回表。
3、索引数据结构（B+树）
B+ 树索引是数据库系统中最为常见的一种索引数据结构，几乎所有的关系型数据库都支持它。
B+树索引的特点是： 基于磁盘的平衡树，但树非常矮，通常为 3~4 层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3、4 次 I/O。
又因为现在的固态硬盘每秒能执行至少 10000 次 I/O ，所以查询一条数据，哪怕全部在磁盘上，也只需要 0.003 ~ 0.004 秒。另外，因为 B+ 树矮，在做排序时，也只需要比较 3~4 次就能定位数据需要插入的位置，排序效率非常不错。
高度为 3 的 B+ 树索引大约可以存在4300万的数据，那么如果高度为4的话，可以存储几十亿的数据，这在绝大部分场景下都是可以涵盖的。
4、最左前缀原则
最左前缀原则（Leftmost Prefix Rule）是索引在数据库查询中的一种使用规则。它指的是在使用复合索引时，索引的最左边的连续几个列会被用于查询过滤条件的匹配。
最左前缀原则的好处包括：
提高查询性能：通过使用索引的最左前缀，可以最大限度地减少索引扫描的数据量，提高查询的效率和响应时间。
减少索引占用空间：在某些情况下，使用最左前缀原则可以减少创建多个索引的需求，节省磁盘空间和索引维护的开销。
5、索引下推
索引下推主要是减少了不必要的回表操作。对于查找出来的数据，先过滤掉不符合条件的，其余的再去主键索引树上查找。
