索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
索引类似于书籍的目录，可以减少扫描的数据量，提高查询效率。

• 如果查询的时候,没有使用索引,就会全表扫描,时间复杂度为o(n)
• 如果用到索引，就会基于二分思想查找数据,此时查找时间复杂度为o(logn)

创建索引的sql语句:
第一种:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...) [INDEX_TYPE] [COMMENT 'string'];
第二种:
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name (column1, column2, ...);

1.mysql索引的分类

按照数据结构分:

• b+树索引:InnoDB,MyISAM,Memory都支持。数据全部存储在叶子节点,非叶子节点都只存储键值，因此查询任何一条数据所需的磁盘 I/O 次数都是稳定且较少的（通常为 2-4 次）。除非特别说明，通常所说的索引默认就是指 B+Tree 索引。
• hash索引:基于哈希表实现，只有精确匹配所有列的查询才有效。 对于等值查询(=， IN())非常快，时间复杂度为o(1),Memory 引擎默认支持，InnoDB 引擎支持自适应哈希索引（Adaptive Hash Index），但这是内部自动管理的，用户无法手动创建。
• Full-Text索引：倒排索引。是“关键词 -> 文档列表”的映射。被“倒”了过来。用于查找文本中的关键词，类似于搜索引擎。MyISAM 和 InnoDB（5.6+版本）都支持。针对大文本字段（如文章内容）进行关键词搜索。

按照索引物理存储方式存储:

• 聚簇索引:b+树的叶子节点存储整行数据，InnoDB存储引擎支持，当表创建主键时，根据主键创建聚簇索引，没有主键是时，选择第一个非空且唯一(UNIQUE NOT NULL)的索引创建作为聚簇索引。如果上面都不满足，InnoDB会创建一个rowId字段作为聚簇索引。主键索引一个表只能创建一个。
• 非聚簇索引:b+树的叶子节点存储当前行对应的主键值，InnoDB和MYISAM存储引擎都支持，通常需要回表查询。除了主键索引外的所有索引（如普通索引、唯一索引等）都是二级索引。

按字段特性划分：

• 主键索引：指聚簇索引,全部表唯一，非空且唯一.
• 唯一索引:索引值唯一可以为空
• 普通索引:最基本的索引类型，没有任何唯一性的限制，仅用于加速查询。
• 前缀索引:不是一种独立的索引类型，而是一种技巧。当索引很长的字符列（如 VARCHAR(255)）时，可以只对列的前面一部分字符建立索引，以节省空间。CREATE INDEX idx_name ON table_name (column_name(10)); -- 只对前10个字符索引

按照按索引列个数划分

• 单列索引:只包含一个列的索引。
• 符合索引:包含两个或更多个列的索引。
  重要原则：最左前缀原则（Leftmost Prefix Principle）。索引可以用于查询中只使用了索引最左边连续列的情况。

2.InnoDB索引与MyISAM索引实现的区别是什么？

MYISAM索引存储方式都是非聚簇索引，InooDB有一个聚簇索引
MYISAM数据存储在.MYD文件时,索引存储在.MYI文件中,b+树的叶子节点直接存储的是.MYD文件中某一行数据的地址,查询非常快
InooDB存储引擎支持聚簇索引,索引和数据存储在一起,存储在.idb文件中，使用主键查询时可以直接查询到数据,非主键索引查询需要先查询到主键值，在拿着主键值到主键索引中查找，回表查询没有MYISAM索引效率高,InooDB必须有主键索引。

3.聚簇索引与非聚簇索引b+数实现的区别:

聚簇索引:
特点:

• 索引和数据保存在同一个B+树中
• 非叶子节点记录的是主键加页号
• 叶子节点记录每完整数据
• 页内数据是按照主键大小排序的，通过单链表连接。
• 页与页之间通过双向链表连接,按照页中记录的主键大小排序。

优点:

• 数据访问快,因为索引和数据保存在用一个b+树里.
• 对于主键的范围查找和排序查找非常快
• 按照聚簇索引排列排列,查询显示一定范围的数据,由于数据都是紧密相连,数据库可以从更少的数据块中提取数据,节省了IO操作。

缺点:

• 插入速度严重依赖于插入顺序，如果按照主键的顺序查询时最快的，否则可能出现页分裂，一般设置主键时自增。
• 更新主机的代价大，一般会导致被更新的行移动。一般限制主键为不可修改的.

限制:

• 只有InnoDB存储引擎支持聚簇索引,MYISAM不支持。
• 每张表只能有一个存储引擎，因为数据存储的物理存储排序只能有一种。
• 如果表没有定义主键，会选择第一个非空且唯一的字段作为主键，如果没有这样的列,InnoDB存储引擎会隐式生成一个rowId列作为主键，永无无法直接使用,服务器内部可直接使用。

非聚簇索引:

聚簇索引 ，只能在搜索条件是 主键值 时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建 非聚簇索引 。
非聚簇索引数据和索引是分开存储的，访问时需要回表，访问速度相对聚簇索引比较慢。
非聚簇索引数据页中存储的是当前字段的值和当前行的主键id值,页和页之间是通过当前字段值排序的。
一张表可以有多个非聚簇索引。

4.平衡二叉树，红黑树，b+树,b树的区别和应用场景?

平衡二叉树

• 左右平衡
• 左子树与右子树相差一会发生自旋
• 每个节点记录一个数据

平衡二叉树也叫平衡二叉搜索树（Self-balancing binary search tree）又被称为AVL树， 可以保证查询效率较高。
特点:

• 它是一棵空树或者左右节点高度差别不超过1
• 左右子树也是一颗平衡二叉树

缺点:为了维护二叉树的高度平衡，增加和删除操作需要进行频繁的自旋操作，树的维护成本高.
应用场景:适合多读少写的场景。例如，一些内存中的查找表，对查询响应时间有极苛刻要求的场景。但由于维护开销大，在实际应用中不如红黑树普遍。

平衡二叉树存在的问题:
当数据存储在磁盘上,在存储大量数据的时候，查询的时候不能一次将数据都加载到内存中，只能一个节点一个节点加载（一个节点一次io操作）,·并且每次加载只能加载一行数据，所以作为mysql索引增删改查效率低下。

红黑树
特点:

• 两次旋转达到平衡
• 分为红黑节点
• 一种近似平衡的二叉搜索树。它放弃了严格的平衡，转而追求大致平衡。这使得它在插入和删除节点时所需的旋转操作比AVL树少得多。

缺点:

• 由于不是严格平衡的，查询效率相对平衡二叉树较低
  
  非常适合需要频繁插入、删除的内存查找场景：
• java中的hashmap
• Epoll 事件块的内核管理

不能作为索引底层数据结构的原因(和平衡二叉树类似):

• 红黑树树太高,磁盘io次数太多,查询时间过长，用户体验差
• 无法高效的进行范围查询,红黑树节点是离散的，要执行范围查询,必须不停的进行中序遍历，期间会不停的进行磁盘io操作，效率低下,b+树的所有节点都在叶子层，并且用指针串成了一个有序；链表，范围查询的时候,只需要找到下限，然后沿着链表顺序扫描即可，这个过程大部分是顺序磁盘io，效率非常高.

b树和b+树的差异

• 数据存储位置:b树每个节点都存储数据(整行数据),b+树只有叶子节点存储数据(整行数据)，非叶子节点只存储索引值.
• 叶子节点结构:b树的叶子节点之间是不相连的,b+树叶子节点之间是通过头尾指针相连形成一个有序的双向链表
• 键的重复性:b树每个键在其所在的节点是唯一的，且与数据直接关联,b+树非叶子节点的键会在叶子节点中重复出现,叶子节点包含键和全部数据

b树与b+树性能的差异:

• 对于单条数据查询,b树不稳定,最好的时间复杂度是o(1)，最差遍历到叶子节点。b+树非常稳点，单条数据查询都需要遍历到叶子节点。
• 范围查询,b树效率低,需要进行复杂的中序遍历,可能设计多次的回溯和随机磁盘IO操作。b+树只需要找到链表的起点，即可遍历链表，I/O效率非常高
• 空间利用率:b树非叶子节点也存储数据,相同阶数下，树的高度更高,b+树非叶子节点不存储数据,只存储键值，每个节点能存储更多的数据，树更加的矮胖，磁盘I/O操作更少。

5.什么是自适应哈希索引？

自适应哈希索引InnoDB引擎上的一个特殊功能,如果某些索引被频繁的用等值查询(=,in),会在内存中基于b+树索引创建一个hash索引，这些等值查询效率就非常高了，时间复杂度为从o(logn)到o(1);
这是服务器内部的一个完全自动的行为，用户无权控制.

6.为什么官方建议使用自增长主键作为索引？（说一下自增主键和字符串类型主键的区别和影响）

• 自增索引能够维持底层数据顺序写入，避免出现频繁的页分裂现象.
• 读取可以有B+树的二分查找定位
• 支持范围查询，范围数据自带顺序，只需要定位到范围查询的起始位置，就可以直接顺序的遍历链表.

字符串无法满足上述两种情况

7.使用int自增主键后 最大id是10，删除id 10和9，再添加一条记录，最后添加的id是几？删除后重启mysql然后添加一条记录最后id是几？

删除之后:

• 没有重启时11,自增id依赖于内存中一个计数器
• 重启之后时9，计数器会被清空,mysql会重新初始化这个计数器,查询出当前表最大id,并加一,赋值为9

8.索引的优缺点?

优点:

聚簇索引:

• 顺序读写
• 范围查找高效
• 范围查找自带顺序

非聚簇索引

• 范围，排序，分组查询返回行id,排序分组后,再回表查询完整数据，有可能顺序读写
• 索引覆盖不需要回表操作

索引的缺点

• 空间上的代价:.
  每次创建索引都需要建立一颗b+树，每一个节点都是数据页，占用16kb的存储空间,一颗b+树会有很多的节点，占用很大的存储空间

• 时间上的代价:
  每次对表中数据进行增删改操作，都需要修改每个b+树索引,增删改操作可能会改变节点的顺序，出现记录移位，页分裂，页回收等情况，存储引擎需要花费时间去为维护.

9 使用索引一定会提升效率吗?

不一定

• 对于少量数据,全表扫描也很快
• 唯一索引会影响插入速度，当插入一条数据时，唯一索引会进行唯一性校验,会扫描b+树,但建议使用。
• 索引过多会影响增删改效率

10.crud是聚簇索引和非聚簇索引区别?

• 聚簇索引在插入要比非聚簇索引慢的多，因为聚簇索引要保证数据唯一.
• 聚簇索引在范围查询,排序分组操作效率高，因为是有序的.非聚簇所引需要进行回表操作，需要两次索引扫描,第一次找到主键，在根据主键到聚簇索引查找到整行数据。

11.非聚簇索引为什么不存数据地址值而存储主键？

因为聚簇索引有可能因为增删改操作引起分页和数据重排导致数据移动，地址发生变化。

12. 什么是回表操作？

id age name sex
age -> index
select * from user where age >20 ;
第一次 取回id，第二次（回表）根据id拿到完整数据
select * from user where age >20 ;

13.什么是索引覆盖?

指一次查询只需要通过扫描索引就可以获取到所需要数据，不需要进行回表操作。
查询时的select子句和where子句中所需的字段都包含在索引里.

例:
创建一个复合索引 idx_city_name_age，它包含了三个字段：(city, name, age)。

SELECT name, age FROM users WHERE city = '北京';

数据库的执行过程变成了：

索引查找：在 idx_city_name_age 索引中快速找到所有 city = ‘北京’ 的记录。

提取数据：由于这个索引的叶子节点已经包含了查询所需要的所有数据（city、name、age，以及必然包含的主键 id），数据库无需回表，直接从索引中取得 name 和 age 的值并返回。

整个过程都在索引中完成，速度非常快，因为索引通常比整个数据行小得多，可以被更多地缓存到内存中，并且顺序 I/O 更多。

14.什么时候适合创建索引，什么时候不适合创建索引？

适合创建索引:

• 频繁作为where条件语句查询字段
• 作为多表关联的字段需要创建索引
• 排序字段可以创建索引
• 分组字段可以创建所引(分组的前提是排序)
• 统计字段可以简历索引(如count(),max())

不适合创建索引:

• 频繁更新的字段不适合创建索引
• 数据量非常少的表不适合创建索引
• 参与mysql函数计算的列不适合创建索引

创建索引时避免有如下极端误解：
1）宁滥勿缺。认为一个查询就需要建一个索引。
2）宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
3）抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

15.什么是索引下推?

索引下推 是 MySQL 5.6 引入的一项优化。它的核心思想是：在存储引擎层过滤到那些不符合where条件的记录，而不是将所有索记录返回到server层在进行过滤。
简单来说，就是把一部分过滤工作“下推”到存储引擎这一层提前完成，减少不必要的回表操作和 Server 层的负载。

假设我们有一张 employees 表，并为 (last_name, first_name) 创建了一个复合索引 idx_name。
执行一个查询，查找所有姓“Wang”并且名以“W”开头的员工：
场景一:没有索引下推（MySQL 5.6 之前）:

• 存储引擎：
  根据复合索引 idx_name，定位到所有 last_name = ‘Wang’ 的记录（索引条目：(Wang, Wei) 和 (Wang, Gang)）。
  注意：此时 first_name LIKE ‘W%’ 这个条件被忽略了。存储引擎不管名是什么，只要姓是Wang，就通过索引找到它们的主键id（1 和 3）。
• 回表:
  存储引擎根据找到的主键id（1, 3），回到主键索引（聚簇索引）中进行回表操作，取出这两条记录的完整数据行。
• Server 层：
  存储引擎将这两条完整记录返回给 MySQL 的 Server 层。
  Server 层再执行 WHERE 子句中的 first_name LIKE ‘W%’ 条件进行过滤。
  最终，(Wang, Gang) 这条记录因为名是“Gang”而不是“W”开头，被过滤掉。只留下 (Wang, Wei) 这一条结果。

问题：明明索引中包含 first_name 字段，但存储引擎却无法利用它来提前过滤掉 (Wang, Gang)，导致进行了一次不必要的回表。

场景二：有索引下推（MySQL 5.6+）:

• 存储引擎：
  根据复合索引 idx_name，定位到所有 last_name = ‘Wang’ 的记录。
  关键步骤：存储引擎不会立即回表，而是会顺便检查一下索引中本来就存在的 first_name 字段，看它是否满足 first_name LIKE ‘W%’。
  索引条目 (Wang, Wei) 满足条件（Wei 以 W 开头）。
  索引条目 (Wang, Gang) 不满足条件（Gang 不以 W 开头），被立刻丢弃。
• 回表:
  存储引擎只对满足所有索引条件下推条件的记录（这里只有 (Wang, Wei)）进行回表，去取完整的记录。
• Server 层：
  存储引擎只将一条完整的记录 (id=1) 返回给 Server 层。
  Server 层再做最后的过滤（此时数据已经是对的，所以这里没有变化）。

优势：避免了一次不必要的回表操作。回表的成本很高（随机I/O），当数据量巨大且无效记录很多时，索引下推带来的性能提升是非常显著的。

16.有哪些情况会导致索引失效？

(1).对索引列使用函数或进行运算。

• 当你在索引列上使用函数、计算或表达式时，数据库无法直接使用索引的值，因为它需要先为每一行数据计算函数结果，然后才能进行比较，这相当于全表扫描。

(2).进行左或左右模糊匹配(%xxx或%xxx%)

B+树索引的结构是按照字段值的前缀（最左端）排序的。如果查询条件不是以已知开头，索引就无法用于快速定位。

• LIKE ‘abc%’：索引有效（右模糊）。因为前缀’abc’是确定的。
• LIKE ‘%abc’：索引失效（左模糊）。因为无法知道什么值会以’abc’结尾。
• LIKE ‘%abc%’：索引失效（全模糊）。同样无法用前缀定位。

(3).复合索引不遵循最左前缀原则

这是针对复合索引（多列索引）的经典陷阱。索引的键值是按照创建时列的顺序拼接的。查询条件必须从索引的最左列开始，且不能跳过中间的列。

(4).使用 OR 连接条件

如果 OR 两边的条件并非都是索引列，或者不是同一个索引，数据库通常会放弃使用索引而选择全表扫描。

(5). 索引列发生隐式类型转换
如果查询条件的数据类型与索引列的定义类型不匹配，数据库会执行隐式类型转换，这相当于在索引列上使用了函数，导致索引失效。

假设 user_id 是字符串类型（VARCHAR），但查询时使用了数字。
SELECT * FROM users WHERE user_id = 123456; -- 失效
– 数据库需要将每行的user_id字符串转换为数字，才能与123456比较