MYSQL索引

MYSQL索引

一、认识索引

​ 认识索引是什么东西非常关键，一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系，为了方便查找书中的内容，通过对内容建立索引形成目录。因此，首先你要明白的一点就是，索引它也是一个文件，他是要占据物理空间的。

1.索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找；

2.索引存在于磁盘中，会占据物理空间。

二、索引的类型

1.FULLTEXT

​ 即为全⽂索引，⽬前只有MyISAM引擎⽀持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使⽤，不过⽬前只有 CHAR、VARCHAR ，TEXT 列上可以创建全⽂ 索引。全⽂索引并不是和MyISAM⼀起诞⽣的，它的出现是为了解决WHERE name LIKE “%word%"这类针对⽂本的模糊查询效率较低的问题。

2.HASH

​ 由于HASH的唯⼀（⼏乎100%的唯⼀）及类似键值对的形式，很适合作为索引。HASH索引 可以⼀次定位，不需要像树形索引那样逐层查找,因此具有极⾼的效率。但是，这种⾼效是有 条件的，即只在“=”和“in”条件下⾼效，对于范围查询、排序及组合索引仍然效率不⾼

3.BTREE

​ BTREE索引就是⼀种将索引值按⼀定的算法，存⼊⼀个树形的数据结构中（⼆叉树），每次 查询都是从树的⼊⼝root开始，依次遍历node，获取leaf。这是MySQL⾥默认和最常⽤的索 引类型

三、索引种类

• 主键索引：MySQL中主键必须唯⼀且不能有空值，因此在主键上的索引也是唯⼀索引。 ⼀个表上的唯⼀索引可以有多个，但主键只有⼀个。
• 唯⼀索引：唯⼀索引顾名思义，索引必须唯⼀，唯⼀索引中允许有空值出现。
• 普通索引
• 组合索引 INDEX(A, B, C)

四、索引操作

• 创建索引

  • 创建普通索引

    CREATE INDEX index_name ON table_name(col_name);
    

  • 创建唯一索引

    CREATE UNIQUE INDEX index_name ON table_name(col_name);
    

  • 创建普通组合索引

    CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
    

  • 创建唯一组合索引

    CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
    

• 通过修改表结构创建索引

  ALTER TABLE table_name ADD INDEX index_name(col_name);
  

• 创建表时直接指定索引

  CREATE TABLE table_name (
   ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name
  (col_name)
  );
  

• 删除索引

  DROP INDEX index_name ON table_name
  

五、存储引擎中索引的实现

索引有不同的类型，存储引擎也有不同的类型，那么索引和存储引擎之间有什么关系呢？

首先得简单明白：

1. 需要知道，在MySQL中，索引是在存储引擎中实现的。
2. 不同的存储引擎可能支持不同的索引类型。
3. 不同的存储引擎对同一种索引类型可能有不同的实现方式。

• InnoDB存储引擎

特点：

• 非叶子节点 不存真实数据；有且只有叶子节点存数据。

• 叶子结点的数据组织是有序的，双向链表。

• 数据查询是从根磁盘通过二分法向下查找数据。

• B+ 索引类型：

  • 聚簇索引：主键索引
  • 非聚簇索引：只存储主键ID，有一次回表

• MyISAM 索引实现

问题：

创建组合索引时，假如（a,b,c）

(a,b,c)最左匹配原则：

• 能够用到的：
  • a; a,b,c; a,c
• 不能用到的
  • b; c; bc

B树 和 B+树 的区别（阶数 = 5）

B+树的特点

【B树&B+树的插入和删除图文详解】

B树和B+树的插入、删除图文详解 - nullzx - 博客园 (cnblogs.com)

【与B树相同点】

• 一个节点可以存储多个元素。

• 与B树一样，叶子节点是排序的。

• 每个节点中的元素，也都按照从小到大的顺序排列，即：左小右大。

• 所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。

• 根节点元素个数： 1<= k <= m-1 (m表示阶数，即：一个节点最多有多少子节点) 非根节点元素个数： m/2 <= k <= m-1

【与B树不同点】

• 叶子节点是有指针的，MySQL中采用的是双向指针。

同一层，或者说根节点到每个叶子节点的长度都相同。

• 根节点元素个数： 1<= k <= m-1 (m表示阶数，即：一个节点最多有多少子节点) 非根节点元素个数： m/2 <= k <= m-1

【与B树不同点】

• 叶子节点是有指针的，MySQL中采用的是双向指针。

• 非叶子节点的元素是与叶子节点有冗余的。