mysql中的索引

最新推荐文章于 2023-08-09 13:59:18 发布

原创最新推荐文章于 2023-08-09 13:59:18 发布 · 542 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

55 篇文章

订阅专栏

本文介绍了MySQL中的索引概念，包括其优点与缺点、不同类型的索引及其创建方式，并提供了索引设计的原则。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引是在存储引擎里实现的。Mysql中索引的存储类型有两种：btree和hash，具体和表的存储引擎相关，MyISAM和InnoDB存储引擎只支持btree索引，而memory/heap存储引擎可以支持btree和hash索引。

索引的优点：

（1）通过创建唯一索引，可以保证数据表中每一行数据的唯一性

（2）可以加快数据的查询速度，也是创建索引最主要的原因

（3）在实现数据的参考完整性方面，可以加速表与表之间的连接

（4）在使用分组和排序子句进行数据查询时，可以减少查询中分组和排序的时间

索引的缺点：

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

（2）索引要占据磁盘空间，每一个索引要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快到达最大文件尺寸。

（3）当对表中的数据进行增加、删除和修改时，索引也要动态的维护，会降低了数据的维护速度。

1、索引种类

（1）普通索引和唯一索引

普通索引是mysql中的基本索引类型，允许在定义索引的列中添加重复值和空值。

唯一索引，索引列的值必须是唯一的，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

（2）单列索引和组合索引

单列索引即一个索引中只包含单个列，一个表中可以有多个单列索引。

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

(3)全文索引

全文索引类型为fulltext,在定义全文索引的列上支持值的全文查找，允许在这些列上添加重复值和空值。全文索引可以在char,varchar或者text类型的列上创建。mysql中只有MyISAM存储引擎支持全文索引。

(4)空间索引

空间索引指在空间数据类型的字段上建立的索引。mysql中的空间数据类型有4种：geometry,point,linestring和polygon.mysql使用spatial关键字进行扩展。创建空间索引的列，必须将其声明为not null。mysql中只有MyISAM存储引擎支持空间索引。

设计索引的原则：

(1)索引并非创建越多越好，表中有大量的索引，不仅占用磁盘空间，而且会影响insert,update,delete等语句的性能，因为表中数据修改的同时，索引也会作调整和更新。

(2)避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

(3)数据量小的表最好不要创建索引，由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

(4)在条件表达式中经常用到的不同值较多的列上建立索引，在不同值较少的列上不要建立索引。

（5）当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需要确保定义的列的数据完整性，以提高查询速度。

（6）在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以在这些列上建立组合索引。

2、创建索引

2.1创建表时创建索引

创建索引的语法：

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指定索引的名称，为可选参数，如果不指定，默认为col_name为索引名。length为可选参数,表示索引的长度，只有字符串类型的字段才能指定索引长度。ASC和DESC指定升序或者降序的索引值存储。