MySql索引类型

2022-07-01 14:39:33 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

Mysql支持哪几种索引

索引是在MySql的存储引擎层中实现的，而不是在服务器层

从数据结构角度

1、B 树索引(O(log(n)))：关于B 树索引，可以参考 MySQL索引背后的数据结构及算法原理

BTREE在MyISAM里的形式和Innodb稍有不同

在 Innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息.

而在MyISAM里，主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.

MyISAM的B Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没啥区别，只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。 MyISAM还采用压缩机制存储索引，比如，第一个索引为“her”，第二个索引为“here”，那么第二个索引会被存储为“3,e”，这样的缺点是同一个节点中的索引只能采用顺序查找。 InnoDB的数据文件本身就是索引文件，B Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。非聚簇索引，叶子节点上的data是主键(所以聚簇索引的key，不能过长)。为什么存放的主键，而不是记录所在地址呢，理由相当简单，因为记录所在地址并不能保证一定不会变，但主键可以保证。

myisam引擎的数据在物理磁盘上是按照顺序存储的，而innodb引擎的表数据是随机分布的；

MyISAM引擎使用B Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出 MyISAM的索引文件仅仅保存数据记录的地址 。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引

同样也是一颗B Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“ 非聚集 ”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做 聚集索引 。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL 系统 会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的 辅助索引data域存储相应记录主键的值而不是地址 。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B Tree，非单调的主键会造成在插入新记录时数据文件为了维持B Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

聚簇索引的优缺点：

  • 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O.
  • 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比非聚簇索引中查找要快。
  • 使用覆盖索引扫描的查询可以直接使用页节点的主键值。

同时聚簇索引还有一些缺点：

  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。（这种情况可以用主键auto_increment自增列解决）
  • 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
  • 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引的访问需要两次索引查找，而不是一次。

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。这里做了重复的工作：两次B-Tree查找而不是一次。

在InnoDB表中按主键顺序插入行

如果正在使用InnoDB并且没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免随机的（不连续且值的分布范围都非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何的聚集特性。为了说明，我们下面举个例子：

两个表：userinfo，自增主键，userinfo_uuid：UUID主键。

下图对测试结果进行了比较：

注意到项UUID主键插入行不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长；另一方面毫无疑问是由于页分裂和碎片导致的。

这是因为主键的值是顺序的，所以InnoDB把每一条记录都存储在上一条记录的后面 。当达到页的最大填充因子后，下一条记录就会在新的页中 。一旦按照这个顺序的方式加载，主键页就会近似于被顺序的记录填满，这也正是所期望的结果（然而，二级索引页可能是不一样的）。

对比一下看看UUID举措索引的插入表数据，看看有什么不同：

因为新行的主键值不一定比之前插入的大，所以InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置 ——通常是已有数据的中间位置——并且分配空间 。这会增加很多的额外工作，并导致数据分布不够优化。下面是总结的一些缺点：

  • 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在不得不在插入新行之前先找到并从磁盘读取到内存中。这将导致大量的随机I/O。
  • 因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
  • 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

从这个案例可以看出，使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能使用单调增加的聚簇键的值来插入新行。

顺序的主键什么时候会造成更坏的结果？

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间歇性竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑下重新设计表或者应用，或者更改innodb_autoinc_lock-mode配置。如果你的服务器版本还不支持innodb_autoinc-lock_mode参数，可以升级到新版本的InnoDB，可能对这种场景工作的更好。

总结

a、关于innoDB中索引的使用

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B Tree，非单调的主键会造成在插入新记录时数据文件为了维持B Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

b、什么时候选用myisam

myisam的主键索引的叶子节点只存放数据在物理磁盘上的指针，其他次索引也是一样的； innodb的主键索引的叶子节点下面直接存放数据，其他次索引的叶子节点指向主键id； 由此可以挖掘出一个问题，就是如果Innodb有大数据列，比如 varchar(300)，这种比较多的话，那么排序的时候用主键id排序会比较慢，因为id主键下面放着所有数据列，而Myisam就不需要扫描数据列，要解决这个问题的话可以再建一个和主键id一起的联合索引；

MyISAM表索引在处理文本索引时更具优势，而INNODB表索引在其它类型上更具效率优势。比如全文索引一般在CHAR、VARCHAR或TEXT列上创建，MyISAM表支持而INNODB表不支持，常见主要针对文本进行索引。同时MySQL高并发需要事务场景时，只能使用INNODB表。

c、该如何选用两个存储引擎呢

此处参考链接：MySQL中MyISAM与InnoDB区别及选择 因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。 如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。 两种类型都有自己优缺点，选择那个完全要看自己的实际类弄。

2、hash索引： a 仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询 b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引 c 只有Memory存储引擎显示支持hash索引

（1）Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。 由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。 （2）Hash 索引无法被用来避免数据的排序操作。 由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算； （3）Hash 索引不能利用部分索引键查询。 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。 （4）Hash 索引在任何时候都不能避免表扫描。 前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。 （5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

hash值即为通过特定算法由指定列数据计算出来，磁盘地址即为所在数据行存储在硬盘上的地址（也有可能是其他存储地址，其实MEMORY会将hash表导入内存）。

这样，当我们进行WHERE age = 18 时，会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。

所以，每次查询时都要遍历hash表，直到找到对应的hash值，如（4），数据量大了之后，hash表也会变得庞大起来，性能下降，遍历耗时增加，如（5）。

InnoDB存储引擎有一个特别的功能，叫自适应哈希索引。当InnoDB注意到一些索引被很频繁的访问的时候，会在B-Tree索引的顶端为这些值建立起内存中的索引。这个过程是自动的，既不能控制，也不能配置它。

3、FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。

  1. –创建article表
  2. CREATE TABLE article (
  3. id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  4. title VARCHAR(200),
  5. content TEXT,
  6. FULLTEXT (title, content) –在title和content列上创建全文索引
  7. );

例如，我们想要在article表的title和content列中全文检索指定的查询字符串，可以如下编写SQL语句：

代码语言：javascript复制
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。 如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的，如果没有异步IO处理，进程将被挟持，很浪费时间。 （1）创建表的适合添加全文索引

代码语言：javascript复制
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

（2）修改表结构添加全文索引

代码语言：javascript复制
ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

代码语言：javascript复制
CREATE FULLTEXT INDEX index_content ON article(content)

4、R-Tree索引（空间索引）（用于对GIS数据类型创建SPATIAL索引）

索引

MyISAM引擎

InnoDB引擎

Memory引擎

B-Tree索引

支持

支持

支持

HASH索引

不支持

不支持

支持

R-Tree索引

支持

不支持

不支持

Full-text索引

支持

暂不支持(现在支持)

不支持

从物理存储角度

1、聚集索引（clustered index）

2、非聚集索引（non-clustered index）

聚集索引:

一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。 聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。 　　　

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此 类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节 省成本。 　　　

当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

非聚集索引:

非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。记录的物理顺序与逻辑顺序没有必然的联系

索引是通过B-Tree的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

备注：每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚簇索引。聚集索引一张表只能创建一个，非聚集索引一张表可以创建多个，在mysql中InnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键（Primary Key）进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。

非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。 如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。

从逻辑角度

1、普通索引或者单列索引

是最基本的索引，它没有任何限制。它有以下几种创建方式： （1）直接创建索引

代码语言：javascript复制
CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

代码语言：javascript复制
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

代码语言：javascript复制
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

（4）删除索引

代码语言：javascript复制
DROP INDEX index_name ON table

2、唯一索引或者非唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： （1）创建唯一索引

代码语言：javascript复制
CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

代码语言：javascript复制
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

代码语言：javascript复制
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3、主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在建表的时候同时创建主键索引：

代码语言：javascript复制
CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) NOT NULL , PRIMARY KEY (`id`) );

4、多列索引（组合索引）：

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

代码语言：javascript复制
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5、空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

代码语言：javascript复制
CREATE TABLE table_name[col_name data type] [unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

1、unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；

2、index和key为同义词，两者作用相同，用来指定创建索引

3、col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；

4、index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；

5、length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

6、asc或desc指定升序或降序的索引值存储

缺点

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。 2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

注意事项

使用索引时，有以下一些技巧和注意事项： 1.索引不会包含有null值的列 只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。 2.使用短索引 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 3.索引列排序 查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 4.like语句操作 一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “�a%” 不会使用索引而like “aaa%”可以使用索引。 5.不要在列上进行运算 这将导致索引失效而进行全表扫描，例如

代码语言：javascript复制
SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6.不使用not in和<>操作

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/131185.html原文链接：https://javaforall.cn

云数据库SQLServer 全文检索 数据库 大数据 数据结构

0 人点赞