您的位置: 首页  >  IT文章  >  九、索引 一、索引是什么 二、MySQL索引结构 三、索引的特点 四、索引设计原则 五、创建索引 六、删除索引

九、索引 一、索引是什么 二、MySQL索引结构 三、索引的特点 四、索引设计原则 五、创建索引 六、删除索引

分类: IT文章 • 2023-03-13 16:31:57

  • 官网定义，索引（Index）是帮助MySQL高效获取数据的数据结构（索引是一种数据结构）。

  • 简单理解：排好序的快速查找数据结构

　　索引是对数据库表一列或者多列的值进行排序的一种数据结构，使用索引可以提高数据库中特定数据的查询速度。

九、索引 一、索引是什么 二、MySQL索引结构 三、索引的特点 四、索引设计原则 五、创建索引 六、删除索引

　　为了加快clo2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针，这样就可以用二叉查找在一定的复杂度内获得相应的数据，从而快速的检索出符合条件的记录。

　　结论:数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

　　一般service层update/delete删除的数据，在数据库层面维护一个标识位，不做物理删除，物理删除影响索引。

　　索引本身也很大，因此索引往往以索引文件的形式存储在磁盘上。

　　平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引。其中聚簇索引，次要索引，覆盖索引，复合索引。前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。

　　索引是在存储引擎中实现的。所有的存储引擎支持每个表至少 16个索引。总索引的长度至少为256字节。MySQL的存储类型有两种BTREE和HASH。

　　MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

二、MySQL索引结构

九、索引 一、索引是什么 二、MySQL索引结构 三、索引的特点 四、索引设计原则 五、创建索引 六、删除索引

　　初始化介绍

    • 一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针p1、p2、p3。

    • 真实的数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99

    • 非叶子节点只不存真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中

　　查找过程

    • 如果要查找数据29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分法查找确定29在17和35之间，锁定磁盘块1和p2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1和p2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的p2指针，通过指针加载磁盘块8到内存，发生第3次IO，同时内存中做二分查找找到29，结束查询，总计3次IO。
    • 真实的情况是，3层的b+树可表示上百万的数据，如果上百万的数据查找只需要3次IO，性能提高是巨大的，如果没有索引，每个数据项都要发生一次IO,那么总共需要上百次的IO,显然成本非常高。

三、索引的特点

3.1、索引的优点

  • 创建唯一索引，保证数据库中每条数据的唯一性。加快查询速度。

  • 实现数据的参考完整性方面，可以加速表和表之间的连接。

  • 在分组和排序查询时，可以显著减少查询中分组和排序的时间。

  • 建立索引。提高数据检索效率，降低数据库的IO成本

  • 通过索引对数据进行排序，降低数据排序成本，降低了CPU的消耗

3.2、索引的不足

  • 随着数据量增加创建、维护索引耗费时间。

  • 索引耗费磁盘空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

  • 当表中的数据进行增加，删除，修改时，索引也要动态地维护，降低了数据的维护速度。

  • 如对表进行update/Insert/delete.因为更新表时，MySQL不仅要保存数据，还要报存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

3.3、索引的分类

  1. 普通索引和唯一索引
    普通索引：是MySQL中的基本索引类型，允许在定义所以的列表中插入重复的值和空值。
    唯一索引：的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

  2. 单列索引和组合索引
    单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。
    组合索引：指在表的多个字段组合上创建的索引，只有在查询条件。
    组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可使用组合索引，而是遵循 “最左前缀”：利用索引最左边的列集来匹配行，这样的列集称为最左前缀。例如id、name、age这三个字段构成组合索引。索引行中按id/name/age的顺序存放，索引可以搜素下面的字段组合(id,name,age)、 (id,name)、(id)。如果列不构成索引最左的前缀，MySQL不能使用局部索引，如(age)或者(name,age)组合 则不能使用索引。

  3. 全文索引
    类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复 值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

  4. 空间索引
    空间索引：是对空间数据类型的字段建立索引，MySQL中的空间类型数据有4中geometry、point、linestring、polygon,MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正 规索引类似的语法创建空间索引，创建空间索引的列，必须将其声明为 NOT NULL，空间索引只能在存 储引擎为MyISAM的表中创建。

四、索引设计原则

  • 索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE的语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更改，加重了IO负担。

  • 查询中与其他表关联的字段，外键关系建立索引

  • 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段创建索引。

  • 数据量小尽量不用索引。数据重复且分布平均的字段（例如性别字段）尽量不用索引。

  • 在条件表达式中经常用到的不同值较多的列建立索引。

  • 当唯一性是某种数据本身的特征时，指定唯一的索引

  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（排序时字段的顺序最好和创建索引的顺序一致，否则会出现文件内排序）

五、创建索引

  • 创建表的时候创建索引
CREATE TABLE table_name 
    [col_name data_type],
    [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [index_name] (col_name [length]) [ASC|DESC]

    • UNIQUE、FULLTEXT、SPATIAL：为可选参数，分别表示唯一索引、全文索引、空间索引。

    • INDEX、KEY：为同义词，两者作用相同，用来指定创建索引。

    • col_name：需要创建索引的字段列，

    • index_name：索引名称。

CREATE TABLE idx_test (
  id VARCHAR (30),
  NAME VARCHAR (30),
  -- 建表时同时创建索引
-- 唯一索引
  UNIQUE INDEX idx_b_id (id),
  -- 普通索引 
  INDEX idx_b_name (NAME)
)
  • 在已经存在的表上创建索引
-- 方式一：
ALTER  TABLE  table_name  ADD  [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[LENGTH],....) [ASC|DESC]

-- 方式二:
CREATE  [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name ON table_name (col_name[LENGTH],...)     [ASC|DESC]
  •  展示某个表中所有的索引 
SHOW INDEX  FROM  TABLE_NAME;

六、删除索引

-- 方式一：
ALTER TABLE TABLE_NAME DROP  INDEX  INDEX_NAME;
-- 方式二：
DROP  INDEX  INDEX_NAME  ON  TABLE_NAME;

相关推荐