mysql索引有哪些缺点,有哪些类型 🛟
幸福不是什么都有,而是你想要的恰好都在身边
mysql 索引有哪些缺点
MySQL索引可以显著提高查询性能,但同时也有一些缺点。以下是MySQL索引的一些常见缺点:
- 占用额外的存储空间:索引需要占用额外的存储空间,因为它们是数据库表的另一种表示形式。
- 更新和插入操作的性能损失:当对表进行插入、更新或删除操作时,索引也需要进行相应的维护,这会导致性能损失。
- 不适用于小表:对于很小的表,全表扫描可能比使用索引更快。
- 选择性差的列不适用:如果列的值分布较为均匀,即每个值出现的频率相似,那么使用索引可能效果不佳。
索引类型
-
主键索引(Primary Key Index):
主键索引是一种唯一索引,它用于确保表中的每一行具有唯一的标识。一个表只能有一个主键索引。主键索引不允许包含NULL值。主键索引的性能通常非常高,因为数据库会优化主键索引的存储和查找。 -
唯一索引(Unique Index):
唯一索引用于确保表中某一列或多列的组合值是唯一的。与主键索引不同,唯一索引允许有多个。唯一索引可以防止插入重复数据,从而保证数据的完整性。唯一索引可以包含NULL值,但每个NULL值在唯一索引中都被视为不同的值。 -
普通索引(Regular Index):
普通索引也称为非唯一索引。它们可以提高查询性能,但不会对数据的唯一性或完整性施加任何约束。普通索引允许在同一列中有重复的值。在大多数情况下,普通索引都是B-Tree索引。 -
组合索引(Composite Index):
组合索引是指在多个列上创建的索引。组合索引可以在涉及多个列的查询中提高性能。组合索引的顺序很重要,因为它决定了如何使用索引进行查询。优化器可以在查询中使用组合索引的一部分(从左到右),但不能跳过任何列。 -
全文索引(Full-Text Index):
全文索引主要用于对大量文本数据进行搜索。它允许使用自然语言查询来搜索文本数据,例如,查找包含某个词或短语的所有记录。MySQL的MyISAM和InnoDB存储引擎支持全文索引。 -
覆盖索引(Covering Index):
覆盖索引是一种B-Tree索引,其中查询所需的所有列都包含在索引中。这样,查询可以仅使用索引来满足请求,而无需访问实际的表数据。这可以大大提高查询性能。在查询优化过程中,如果能够充分利用覆盖索引,可以避免不必要的I/O操作。 -
空间索引:空间索引用于处理地理空间数据。它允许在地理空间数据类型(如点、线和多边形)之间进行查询和比较。MySQL的MyISAM存储引擎支持空间索引,同时InnoDB从MySQL 5.7.5版本开始也支持空间索引。
这些索引类型可以根据实际需求和数据特点进行选择,以优化查询性能和确保数据完整性。
覆盖索引
覆盖索引的意思是说我查询的数据或者说列,比如主键跟覆盖索引包含的列, 这样我就不用重新回表查询另外的数据, 提高查询效率
全文索引
fulltext
是 MySQL 执行计划中的一个 type 类型,表示在全文索引中进行查询。全文索引是一种特殊的索引,可以用于快速搜索包含某个关键字的文本内容。与普通索引不同,全文索引并不是直接映射到表的某个列,而是对某个或多个列的内容进行分词和索引。
当使用 MATCH AGAINST
关键字来进行全文搜索时,MySQL 可以使用全文索引来提高查询效率。例如:
SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('MySQL fulltext search');
这个查询会使用 fulltext
类型的执行计划来进行查询,效率比使用普通索引要高。需要注意的是,全文索引只适用于较长的文本内容,对于短文本内容和数值类型的列并不适用。
MySQL 中建立全文索引需要满足以下两个条件:
-
存储引擎必须是
MyISAM
或者InnoDB
。 -
列的类型必须是
CHAR
、VARCHAR
、TEXT
或者BLOB
。
在满足以上条件后,可以使用 FULLTEXT
关键字来创建全文索引。例如,以下是在 articles
表的 title
和 body
列上创建全文索引的 SQL 语句:
CREATE FULLTEXT INDEX idx_fulltext ON articles(title, body);
注意,在使用 FULLTEXT
关键字创建全文索引时,需要指定要索引的列。在查询时,可以使用 MATCH AGAINST
关键字来进行全文搜索,例如:
SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('MySQL fulltext search');
需要注意的是,全文索引是占用磁盘空间的,需要根据实际情况来选择建立全文索引的列和表。同时,全文索引不支持复合索引,即不能将全文索引和其他类型的索引组合成一个联合索引。