1、什么是索引

索引是对数据库表中一或多个列的值进行排序的结构，是帮助数据库高效查询的数据结构。

2、索引的优缺点

索引的优点：

  • 索引可以将随机I/O变成顺序I/O。
  • 索引可以帮助服务器避免排序和临时表。
  • 索引大大减少了服务器需要扫描的数据量，从而加快检索速度。
  • 支持行级锁的数据库，如InnoDB会在访问行的时候加锁。使用索引可以减少访问行数，从而减少锁的竞争提高并发。
  • 唯一索引是可以确保每一行的数据唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

索引的缺点：

  • 创建和维护索引要耗费时间，这会随着数据量的增加而增加。
  • 写操作（INSERT/UPDATE/DELETE）时很可能需要更新索引，导致数据库的写操作性能降低。

  • 索引需要占用额外的空间，除了数据表占据空间之外，每一个索引还要占一定的物理空间，如果要建立组合索引那么需要的空间就会更大。

    3、何时使用索引

    适宜索引：

  • 表的数据量比较大。

  • 表经常进行SELECT查询。
  • 列名经常出现 WHERE 或连接（JOIN）条件中。

不宜索引：

  • 频繁写操作（INSERT/UPDATE/DELETE）需要更新索引空间
  • 非常小的表 - 对于非常小的表，大部分情况下简单的全表扫描更高效
  • 列明不经常出现在WHERE或连接（JOIN）条件中索引就会经常不命中，没有意义，还增加空间开销

  • 对于特大型表，建立和使用索引的代价将随之增加。可以考虑使用分区技术或 NoSQL。

    4、索引的类型

    主流的关系型数据库一般都支持以下索引类型
    从逻辑类型上划分：

  • 唯一索引：索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

  • 主键索引：一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建
  • 普通索引：最基础的索引，没有任何限制
  • 组合索引：多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。

从物理存储上划分：

  • 聚集索引：表中各行的物理顺序与键值逻辑（索引）顺序相同，每个表只有一个。

  • 非聚集索引：非聚集索引指定表的逻辑顺序，也可以视为二级索引。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于 249 个。

    5、索引的数据结构

    主流数据库的索引一般使用数据结构为：B树、B+树。
    B树
    一棵M阶的B-Tree满足以下条件：

  • 每个节点至多有M个孩子

  • 除根节点和叶节点外，其他每个节点至少有M/2个孩子
  • 根节点至少有两个孩子（除非该树仅包含一个节点）
  • 所以叶节点在同一层，叶节点不包含任何关键字信息
  • 有K个关键字的非节点恰好包含K+1个孩子

对于任意节点，其内部的关键字Key是升序排列的。每个节点中都包含了Data。
B-TREE.png
对于每个节点，主要包含一个关键字数组key[]，一个指针数组（指向儿子)song[]。

在B-Tree内，查找的流程是：

  • 使用顺序查找（数组长度较短时）或折半查找方法查找key[]数组，若查找到关键字K，则返回该节点的地址及K在key[]中的位置。
  • 否则可确定K在某个key[i] 和key[i+1]之间，则从song[i]所指的子节点继续查找，直到在某节点中查找成功
  • 或直至找到叶节点且叶节点中的查找仍不成功时，查找过程失败。

B+树
B+Tree是B-Tree的变种：

  • 每个节点的指针上限为2d而不是2d+1（d为节点的出度）
  • 非叶子节点不存储data，只存储key，叶子节点不存储指针

B_TREE.png
由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。

带有顺序访问指针的 B+Tree
一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行优化，增加了顺序访问指针
带有顺序访问指针的B+Tree.png
在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了到有顺序访问指针的B+Tree。

这个优化的目的是为了提高区间访问的性能，例如上图中如果要查询key为从 18 到 49 的所有数据记录，当找到 18 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提高了区间查询效率。

B 树 vs B+ 树
B+数更适合外部存储（一般指磁盘存储）由于内节点（非叶子节点）不存储Data，所以一个节点可以存储更多的内节点，每个节点了个索引的范围更大更精确。也就是说使用 B+树单次磁盘I/O的信息量相比较 B 树更大，I/O效率更高。

MySQL是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以 B+ 数对索引列上的区间范围查询很友好。而 B树每个节点的key和data在一起，无法进行区间查询。

Hash
Hash 索引只有精确匹配索引所有列的查询才有效。
对于每一行数据，对所有的索引列计算一个HashCode。哈希索引将所以的HashCode存储在索引中，用时在Hash表中保存指向每个数据行的指针。
哈希结构索引的优点：

  1.   - 因为索引数据结构紧凑，所以查询速度非常快。

哈希结构索引的缺点：

  1.   - 哈希索引不是按照索引值进行顺序存储的，所以不能用于排序。
  2.   - 哈希索引不支持部分索引匹配查询。如在数据（A,B）上建立哈希索引，如果查询只有数据列 A，无法使用索引。
  3.   - 哈希索引只支持等值比较查询，不支持范围查询，如`where > 100`。
  4.   - 哈希索引有可能出现哈希冲突，出现哈希冲突时，必须遍历链表中所有的行指针，逐条比较，直到找到符合条件的行。

6、索引策略

索引基础原则

  • 索引不是越多越好
  • 要尽量避免冗余和重复索引
  • 要考虑删除未使用的索引
  • 尽量的扩展索引，不要新建索引
  • 频繁作为WHERE过滤条件的列应考虑添加索引

独立的列
是指索引不能是表达式的一部分，也不能是函数的参数。

  1. SELECT actor_id FROM actor WHERE actor_id + 1 = 5;
  2. SELECT ... WHERE TO_DAYS(current_date) - TO_DAYS(date_col) <= 10;

前缀索引和索引选择性

  • 有时候需要索引很长的字符列，这会让索引变的大且慢。
  • 解决方法：可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降索引的选择性。
  • 索引的选择性：不重复的索引值和数据表记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。
  • 对于BLIB/TEXT/VARCHAR这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。要选择足够长的前缀以保证较高的选择性，同时又不能太长（节约空间）。 ```sql 低效示例： SELECT COUNT(*) AS cnt, city FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;

高效示例： SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10; ```

多列索引
不要为每个列创建独立索引，选择性高的列或基数大的列优先排在多列索引最前面。有时也需要考虑WHERE子句中的排序、分组和范围条件等因素，这些因也会对查询性能造成较大影响。
例：user 表name、sex、age三个列，组合成多列索引，从选择性高的角度来看：name > age > sex

聚簇索引
聚簇索引不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于实现方式。如InnoDB的聚簇索引实际是在同一个结构中保存了 B 树的索引和数据行。

聚簇表示数据行和相邻的键值紧凑的存储在一起，因为数据紧凑，所以访问快。因为无法同时把数据行存行在两个不同的地方，所以一个表只能有一个聚簇索引。

若没有定义主键，InnoDB会隐式定义一个主键来作为聚簇索引。

覆盖索引
索引包含所有需要查询字段的值。
具有以下优点：

  1.   - 对于`InnoDB`引擎，若辅助索引能够覆盖索引，则 无需访问主索引。
  2.   - 因为索引条目通常远大于数据行的大小，所以若只读取索引，能大大减少数据的访问量。
  3.   - 一些索引（`MyISAM`）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此只访问索引可以不使用系统调用（通常比较费时）。

使用索引扫描来做排序
MySQL有两种方式可以生成排序结果：通过排序操作；或者按索引顺序扫描。索引最好既满足排序，又用于查找行。这样就可以使用索引来对结果排序。

最左前缀匹配原则
MySQL 会一直向右匹配直到遇到范围查询（>、<、BETWEEN、LIKE）就会停止匹配。

索引可以简单如一个列（a）,也可以复杂如多个列（a，b，b，d）即联合索引。如果是联合索引，那么key也由多个列组成，同时索引只能用于查找key是否存在（相等），遇到范围查询（>、<、BETWEEN、LIKE左匹配）等，就不能进一步匹配了，后续退化为线性查找。因此列的排列顺序决定了可命中索引的行数。
例：如有索引（a，b，b，d）查询条件a = 1 AND b = 2 AND c > 3 AND d = 4，则会在每个节点依次命中a、b、c，无法命中d。（索引命中只会是相等的情况，不能是范围匹配）

= 和 in 可以乱序
不需要考虑=、in 等的顺序，MySQL 会自动优化这些条件的顺序，以匹配尽可能多的索引列。
例：索引（a，b，c，d）查询条件c > 3 AND b = 2 AND a = 1 AND d < 4 与 a = 1 AND c > 3 AND b = 2 AND d < 4等顺序都是可以的，MySQL 会自动优化为 a = 1 AND b = 2 AND c > 3 AND d < 4，依次命中a、b、c。

7、约束

数据可约束（CONSTRAINI）有哪些：

  • CHECK - 用于控制字段的值范围。
  • UNIQUE - 字段内容不能重复，一个表允许有多个UNIQUE约束。
  • NOT NULL - 用于控制字段的内容一定不能为空。
  • PRIMARY KEY - 数据表中对存储数据对象予以唯一和完整标识的数据列或属性的组合，它在一个表中只允许有一个，主键的取值不能为空值（NULL）。
  • FOREIGN KEY - 在一个表中存在的另一个表的主键称此表的外键。用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是指向的那个表中的值之一。