# 索引 ## 什么是索引 索引：索引的出现其实就是为了提高数据查询的效率，就像书的目录一样 MySQL 在查询方面主要就是**两种方式：** 1. 全表扫描（一个一个挨个找） 2. 根据索引检索 ## 索引操作 **1、创建索引** ``` create index 索引名 on 表名(列名); # 也可以有多个列名构成索引（即，组合索引） ``` **2、删除索引** ``` drop index 索引名 on 表名; ``` ## 怎么查看一个SQL语句是否使用了索引进行检索 在 SQL 语句前，添加 explian 关键字 - 当 `type=ALL` 时，表示使用 全表查询（未使用索引） - 当 `type=RES`时，表示使用索引 ## 索引有哪些类别 - 按「数据结构」分类：**B+tree索引、Hash索引、Full-text索引**。 - 按「物理存储」分类：**聚簇索引（主键索引）、二级索引（辅助索引）**。 - 按「字段特性」分类：**主键索引、唯一索引、普通索引、前缀索引**。 - 按「字段个数」分类：**单列索引、联合索引**。 ## 索引区分度 索引区分度表示某个字段不同值的个数占整个表的比例，**建立联合索引时，要把区分度大的字段排在前面**。 ## 建立索引的注意事项 - 索引不是越多越好，虽然索引会提高 select 效率，但是也降低了insert以及update的效率 - 数据量小的表不需要建立索引，会增加额外的索引开销 - 不经常使用的列不要建立索引 - 频繁更新的列不要建立索引，会影响更新的效率 ## 索引的优缺点 优点： - 使用索引可以**大大加快数据的检索速度**（大大减少检索的数据量），这也是创建索引的最主要的原因。 - 但是注意使用索引不一定能够提高查询性能，因为如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。其余大多数情况下，索引查询比全表扫描要快。 - 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 缺点： - 空间消耗，一个索引对应的就是一棵 B+树，每一个节点都是一个 16KB 大小的页。**占用的空间较大**。 - 创建索引和维护索引需要耗费许多时间，当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。 ## 什么时候需要创建索引 - 频繁用于查询的列需要创建索引 - 大表：对于很大的表，建立索引可以提高查询速度 - 唯一性要求：主键列和唯一性约束的列会自动创建唯一索引，但如果查询中经常包含唯一性条件，可以额外创建唯一性索引。 - 连接表的外键列：为外键列创建索引可以提高连接的效率。 - 频繁使用排序和分组的列：如果某列经常用于 ORDER BY 或 GROUP BY 子句可以创建索引 ## 什么时候不需要创建索引 - 小表：在小表上，索引不会带来显著的性能提升，还会增加维护开销。 - 经常进行更新的字段不需要创建索引 - 很少用于查询的字段 - 字段中存在大量重复数据，不需要创建索引，比如性别字段 ## 索引优化的方法 - 前缀索引优化：使用某个字段中字符串的前几个字符建立索引，从而减小索引字段大小 - 覆盖索引优化：从二级索引中可以查询得到记录，避免回表 - 主键索引最好是自增的；这样每次插入一条新记录，都是追加操作，不需要重新移动数据，而使用非自增主键会导致插入主键的索引值是随机的，这可能会插入到现有数据页的某个位置，导致其他数据的移动，造成页分裂。 - 避免过多的索引 ## 什么是数据库的索引？为什么要使用索引？ 数据库索引是一种特殊的数据结构，由表中的一列或多列组合而成，类似书籍目录，可帮助数据库系统快速定位和访问表中的数据。使用索引的原因主要有： - **提升查询效率**：避免全表扫描，快速定位所需数据行，尤其在大型表和复杂查询中效果显著。 - **确保数据唯一**：创建唯一索引可保证列中数据值的唯一性，维护数据完整性。 - **助力排序分组**：利用索引的顺序性，快速完成排序和分组操作，无需额外排序处理。 - **优化连接操作**：在多表连接查询时，帮助快速定位连接条件中涉及列的值，加快连接速度。 ## MySQL为什么使用B+树来作索引？ #### 磁盘读写代价更低 只有非叶子节点存储指针，叶子结点全部用来存储数据，磁盘存储压力小 #### 查找效率更稳定 每个数据查找路径都差不多，需要到根节点去查询，而且它阶数更多，路径更短（矮胖树） #### 便于扫库和区间查询 它的叶子节点是一个双向链表 ![image.png](http://cdn.kamacoder.com/674ff38ad9f08-php99pa0T.png) ## 说一下索引失效的场景？ - **使用左或者左右模糊匹配**：比如`LIKE '%abc'` 这样的查询会导致索引失效。 - **在索引列上使用函数或表达式**： 索引列上参与计算，索引失效 ``` SELECT * FROM table WHERE UPPER(column) = 'VALUE'; ``` - 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。 - 违背最左匹配原则，索引失效 - **数据分布不均匀：** 如果数据分布不均匀，例如某个索引列的大多数值都相同，选择性降低，导致索引失效。 - **隐式类型转换：** 如果查询中的条件涉及到隐式类型转换，例如将字符串与数字比较，索引可能无法被使用。 ## 解释一下数据库的索引覆盖和回表？ - **索引覆盖**指的是查询所需的所有列都能**通过索引直接获取**，无需访问数据表中的数据行。 - **回表**是指**查询需要的列不在索引中**，必须通过索引获取的数据行的ID，再去查询实际的数据表获取完整数据。