MySQL索引

索引是什么 优缺点

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。而且索引是一个文件，它是要占据物理空间的。

索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

MySQL有哪几种索引类型？ 从存储结构和应用层

1. 存储结构方面：
2. BTree索引（平衡树索引）：BTree索引是MySQL中最常见的索引类型。它采用BTree（平衡树）数据结构来组织索引数据，保持数据有序，并支持高效的查找和范围查询操作。BTree索引适用于主键索引、唯一索引、普通索引等情况。
3. B+Tree索引（B+树索引）：B+Tree索引是BTree索引的一种变体，常用于MyISAM存储引擎。与BTree索引相比，B+Tree索引在叶节点上存储了所有数据记录，非叶节点只存储索引字段，这样可以提高数据查询的效率。
4. Hash索引（哈希索引）：Hash索引使用哈希算法将索引值映射到一个哈希表中的槽位，而不是树结构。Hash索引适用于等值查询，但不支持范围查询和排序操作。MySQL中只有Memory存储引擎支持Hash索引。

应用层方面：

1. 主键索引（Primary Key Index）：用于唯一标识表中的每一行数据，提供快速的数据访问方式。
2. 唯一索引（Unique Index）：保证被索引的列中的值是唯一的，用于确保数据的唯一性。
3. 普通索引（Non-Unique Index）：最基本的索引类型，用于加速查询，但允许重复的索引值。
4. 全文索引（Full-Text Index）：用于全文搜索，支持高效的文本关键词搜索查询，适用于对文本字段进行搜索操作。

综上所述，MySQL的索引类型主要包括BTree索引、B+Tree索引、Hash索引等存储结构方面的类型，以及主键索引、唯一索引、普通索引、全文索引等应用层方面的类型。不同的索引类型适用于不同的数据访问场景和查询需求。

MySQL索引

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

B-Tree索引（MySQL使用B+Tree）

1. 多路搜索树：B-Tree是一种多路搜索树，每个节点可以包含多个关键字和对应的指针。这使得每次磁盘IO可以获取更多的数据，减少了IO操作次数，提高了查询效率。
2. 平衡性：B-Tree是一种自平衡的数据结构，从根节点到叶子节点的路径长度相等或相差最多为1，保持了树的平衡性。这确保了高效的查找速度，时间复杂度为O(log N)。
3. 有序性：B-Tree的所有关键字按从小到大的顺序排列，叶子节点包含所有的数据记录。这使得B-Tree支持高效的范围查询和排序操作。
4. 非叶子节点的关键字：非叶子节点的关键字个数比子节点的个数少1，这样确保了节点的平衡性和有序性。
5. 数据插入和删除：数据插入和删除操作涉及到节点的分裂和合并，以保持B-Tree的平衡性。
6. 适用于磁盘存储：B-Tree索引适用于磁盘存储，由于每个节点包含多个关键字，每次磁盘IO可以获取更多的数据，减少IO操作次数，提高查询性能。
7. 支持范围查询和排序：由于有序性，B-Tree支持高效的范围查询和排序操作。

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+tree性质：

1. 多路搜索树：B+树是一种多路搜索树，每个节点可以包含多个关键字和对应的指针。这使得每次磁盘IO可以获取更多的数据，减少了IO操作次数，提高了查询效率。
2. 平衡性：B+树是一种自平衡的数据结构，从根节点到叶子节点的路径长度相等或相差最多为1，保持了树的平衡性。这确保了高效的查找速度，时间复杂度为O(log N)。
3. 有序性：B+树的所有关键字按从小到大的顺序排列，叶子节点包含所有的数据记录，并且以链表方式连接在一起。这使得B+树支持高效的范围查询和排序操作。
4. 叶子节点包含全部数据：所有的叶子节点中包含了全部的关键字信息，及指向含这些关键字记录的指针。非叶子节点仅含其子树中的最大（或最小）关键字，它们可以看作索引部分。
5. 数据插入和删除仅在叶子节点进行：B+树中，数据对象的插入和删除操作仅在叶子节点上进行，非叶子节点只用于索引定位。这样保证了叶子节点包含全部的数据记录，避免了数据分散，提高了查询效率。
6. 适用于范围查询和排序：由于叶子节点以链表连接，B+树可以高效地执行范围查询操作，只需从起始叶子节点开始遍历链表，直到达到结束叶子节点即可获取所需数据。同时，由于有序性，B+树也支持高效的排序操作。
7. 适用于磁盘存储：B+树索引适用于磁盘存储，由于每个节点包含多个关键字，每次磁盘IO可以获取更多的数据，减少IO操作次数，提高查询性能。

• 

为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B-tree： 从两个方面来回答

• B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B(B-)树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
• 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

Hash：

• 虽然可以快速定位，但是没有顺序，IO复杂度高；
• 基于Hash表实现，只有Memory存储引擎显式支持哈希索引 ；
• 适合等值查询，如=、in()、<=>，不支持范围查询 ；
• 因为不是按照索引值顺序存储的，就不能像B+Tree索引一样利用索引完成排序 ；
• Hash索引在查询等值时非常快 ；
• 因为Hash索引始终索引的所有列的全部内容，所以不支持部分索引列的匹配查找 ；
• 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题 。

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树： 树的高度随着数据量增加而增加，IO代价高。

讲一讲聚簇索引与非聚簇索引？

聚簇索引：

• 主键索引（Primary Key Index）：在创建表时，使用PRIMARY KEY关键字指定主键。
• 唯一索引（Unique Index）：在创建表时，使用UNIQUE关键字指定唯一索引。

非聚簇索引：

• 普通索引（Index）：在创建表时，使用INDEX关键字指定普通索引。
• 唯一索引（Unique Index）：在创建表时，使用UNIQUE关键字指定唯一索引。
• 全文索引（Full-Text Index）：在创建表时，使用FULLTEXT关键字指定全文索引。

当谈到聚簇索引和非聚簇索引的例子，我们可以以一个学生信息管理系统为例来说明它们的应用。

假设我们有一个学生信息管理系统，包含一个学生表：

学生ID	姓名	年龄	班级
1001	小明	12	三年级
1002	小红	13	四年级
1003	小刚	11	五年级
1004	小丽	14	四年级
1005	小强	12	三年级

聚簇索引的例子： 在这个学生表中，我们将学生ID设置为主键，这将自动成为聚簇索引。当新的学生记录插入表中时，根据学生ID的大小，数据行将按照学生ID的顺序在磁盘上物理存储。因此，学生表的数据行将按照学生ID进行排序。

假设现在需要查询学生ID为1003的学生信息。由于学生ID是聚簇索引，数据库可以快速定位到ID为1003的学生记录所在的位置，不需要全表扫描，大大提高了查询效率。

非聚簇索引的例子： 现在我们需要根据学生的姓名来查询学生信息。为了优化这个查询，我们在学生表的姓名字段上创建一个非聚簇索引。

非聚簇索引将学生的姓名作为索引键，而在索引的叶子节点中存储了学生姓名和对应的学生ID的指针。这样，在查询学生姓名时，数据库会先通过非聚簇索引找到对应的学生ID，然后再通过学生ID定位到实际的数据行，获取学生的详细信息。

例如，我们要查询姓名为"小强"的学生信息，数据库首先通过非聚簇索引找到学生ID为1005的指针，然后再根据指针找到实际的数据行，获取"小强"的学生信息。虽然需要两步查找，但由于非聚簇索引的存在，仍然能够高效地查询到数据，而不需要进行全表扫描。

综上所述，聚簇索引适用于频繁查询的场景，而非聚簇索引适用于频繁更新和查询的场景。在实际应用中，根据具体的数据访问模式和查询需求，选择合适的索引类型是很重要的，能够有效提高数据库的性能。

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

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

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。
回表：

简而言之，回表是非聚簇索引查找数据时需要再次回到原始数据表中查找的过程。它涉及到两次查找：首先通过非聚簇索引找到数据行的位置，然后再回到原始数据表查找实际的数据内容。这样的回表查询可能会对数据库的性能产生一定影响，特别是在大数据量和频繁查询的情况下。

如何查看Mysql语句是否使用索引

EXPLAIN SELECT * FROM 表名 WHERE 列名 = 值;

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

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率

如何创建索引？

创建索引有三种方式。

1、 在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 ( id INT auto_increment PRIMARY KEY, first_name VARCHAR (16), last_name VARCHAR (16), id_card VARCHAR (18), information text, KEY name (first_name, last_name), FULLTEXT KEY (information), UNIQUE KEY (id_card) );

2、 使用ALTER TABLE命令去增加索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

3、 使用CREATE INDEX命令创建。

CREATE INDEX index_name ON table_name (column_list);

什么情况下不走索引（索引失效）？

1、使用!= 或者 <> 导致索引失效

SELECT * FROM 学生成绩表 WHERE 数学成绩 != 90;

2、类型不一致导致的索引失效

SELECT * FROM 学生表 WHERE 年龄 = ‘18’; 18为int

3、函数导致的索引失效

SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;

如果使用函数在索引列，这是不走索引的。

4、运算符导致的索引失效

SELECT * FROM user WHERE age - 1 = 20;

如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

5、OR引起的索引失效

SELECT * FROM user WHERE name = ‘张三’ OR height = ‘175’;

OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

6、模糊搜索导致的索引失效

SELECT * FROM user WHERE name LIKE ‘%冰’;

当%放在匹配字段前是不走索引的，放在后面才会走索引。

7、NOT IN、NOT EXISTS导致索引失效