mysql索引类型，索引创建，索引使用注意事项

爱被打了一巴掌 2022-05-25 05:22 334阅读 0赞

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以提高数据库中特定数据的查询速度。所有mysql列的类型都可以使用索引，例如：数据库中有两万条记录，现在要执行这样一个查询：select \* from table t1 where num=20000。如果没有索引，将会遍历整个表，知道num=20000，这一行数据被找到。如果在num列上创建索引，mysql不需要任何扫描，直接在索引里面找到20000，就可以查到数据，可见索引的查询效率。

索引是在存储引擎中实现的，所以不同的存储引擎，索引不一定完全相同。并且每一种存储引擎也不一定支持所有索引，mysql中索引的存储类型有两种：BTREE和HASH两种。InnoDB和MyIsam存储引擎只支持btree；memory和heap存储引擎支持hash和btree索引。

**索引的优点：**

1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。   
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。   
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。   
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

**索引的缺点：**

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。   
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。

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

**mysql索引的分类：**

**普通索引和唯一索引**

这是最基本的索引，它没有任何限制，允许定义索引的列上存在空值和重复值。

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

**单列索引和组合索引**

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

组合索引指在多个字段上创建的索引，只有查询条件使用了这些字段的左边字段，索引才会被使用，就是最左边的字段。

**全文索引**

在mysql5.6版本以前FULLTEXT索引仅可用于 MyISAM 表，在5.6之后innodb引擎也支持FULLTEXT索引。他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建。

**空间索引**

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

**索引创建的有两种，第一种是创建表的时候创建索引：**

CREATE TABLE 表名(字段名 数据类型 \[完整性约束条件\],

\[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指定升序或降序的索引值存储

**例如创建普通索引：**创建t2表并且在username字段上创建索引。

create table t2(

id  int not null,  
username varchar(255)not null,  
age int(30)not null,  
index(username)

)

EXPLAIN select \* from t2 where username='小明'

![70][]

rows=1表示只扫描了一行数据，

key\_len表示索引按字节计算出的长度

key 表示mysql实际选用的索引

**创建唯一索引的列子**：

create table t3(