MySQL索引详解交流学习 [复制链接]

索引是用来快速检索出具有特定值的记录。如果没有索引，数据库就必须从第一条记录开始进行全表扫描，直到找出相关的行。数据越多，检索的代价就越高，检索时如果表的列存在索引，那么MySQL就能快速到达指定位置去搜索数据文件，而不必查看所有数据。

!--more--

概述

索引依托于存储引擎的实现，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引，总索引长度至少为字节。大多数存储引擎有更高的额限制。

MySQL中索引的存储类型有两种：

BTREE和HASH

，具体和表的存储引擎相关；

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

优点

加快数据的查询速度唯一索引，可以保证数据库表中每一行数据的唯一性在实现数据的参考完整性方面，可以加速表和表之间的连接在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间

缺点

占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸（合理运用，问题不大）损耗性能（添加、修改、删除）索引需要动态地维护

分类

普通索引和唯一索引

普通索引：数据库中的基本索引类型，允许在定义索引的列中插入重复值和空值唯一索引：索引列的值必须唯一，但允许有空值，主键索引是一种特殊的唯一索引，不允许有空值（比如自增ID）

单列索引和组合索引

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引组合索引：指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用

全文索引

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

设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要。

注意事项

索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新避免对经常更新的表设计过多的索引，并且索引中的列尽可能要少，而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引时间还要短，索引可能不会产生优化效果在条件表达式中经常用到的不同值较多的列上建立索引，在不同值较少的列上不要建立索引，比如性别字段只有男和女，就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度在频繁排序或分组（即groupby或orderby操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引

使用

使用CREATETABLE创建表的时候，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。

创建表时创建索引的基本语法如下：

CREATETABLEtable_name[col_namedata_type][UNIQUE

FULLTEXT

SPATIAL][INDEX

KEY][index_name](col_name[length])[ASC

DESC]

释义

UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引INDEX和KEY为同义词，二者作用相同，用来指定创建索引col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择index_name为指定索引的名称，为可选参数，如果不指定则MySQL默认col_name为索引值length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度ASC或DESC指定升序或者降序的索引值存储

普通索引

--这句作用是,如果customer1存在就删除DROPTABLEIFEXISTScustomer1;CREATETABLE`customer1`(`customer_id`bigint(20)NOTNULLCOMMENT客户ID,`customer_name`varchar(30)DEFAULTNULLCOMMENT客户姓名,INDEX`idx_customer_id`(`customer_id`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT=客户表;

测试

--查看当前表的索引情况SHOWINDEXFROMcustomer1;--使用EXPLAIN分析SQL语句是否使用了索引EXPLAINSELECT*FROMcustomer1WHEREcustomer_id=1;

释义

EXPLAIN语法下章会详细讲解,本章重心是索引

select_type：指定所使用的SELECT查询类型，这里值为SIMPLE，表示简单的SELECT，不使用UNION或者子查询。其他取值有PRIMARY、UNION、SUBQUERY、等table：指定数据库读取的数据表的名字，它们按照被读取的先后顺序排列type：指定了本数据表与其他数据表之间的关联关系，其它取值有system、const、eq_ref、ref、range、index和Allpossible_keys：MySQL在搜索数据记录时可选用的各个索引key：MySQL使用的实际索引key_len：给出了索引按字节计算的长度，key_len数值越小，表示越快ref：提供了关联关系中另外一个数据表里的数据列的名字rows：指MySQL执行查询时预计从当前数据表中读出的数据行数Extra：提供了与关联操作有关的信息

SHOWINDEXFROM语法

table：表示创建索引的表Non_unique：表示索引不是一个唯一索引，1表示非唯一索引，0表示唯一索引Key_name：表示索引的名称Seq_in_index：表示该字段在索引中的位置，单列索引改值该值为1，组合索引为每个字段在索引中定义的顺序Column_name：表示定义索引的列字段Sub_part：表示索引的长度Null：表示该字段是否能为空值Index_type：表示索引类型

当

possible_keys

与

key

都为

idx_customer_id

，说明查询时使用了索引

唯一索引

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引，前面两个例子中创建的索引都是单列索引，比如：

DROPTABLEIFEXISTScustomer1;CREATETABLE`customer1`(`customer_id`BIGINT(20)NOTNULLCOMMENT客户ID,`customer_name`VARCHAR(30)DEFAULTNULLCOMMENT客户姓名,UNIQUEINDEX`idx_customer_id`(`customer_id`)USINGBTREE)ENGINE=INNODBDEFAULTCHARSET=utf8mb4COMMENT=客户表;

这样就代表在表的

customer_id

字段上创建了一个名为

idx_customer_id

的唯一索引

组合索引

组合索引是在多个字段上创建一个索引，比如：

DROPTABLEIFEXISTScustomer1;CREATETABLE`customer1`(`customer_id`BIGINT(20)NOTNULLCOMMENT客户ID,`customer_name`VARCHAR(30)DEFAULTNULLCOMMENT客户姓名,INDEX`idx_group_customer`(`customer_id`,`customer_name`)USINGBTREE)ENGINE=INNODBDEFAULTCHARSET=utf8mb4COMMENT=客户表;SHOWINDEXFROMcustomer1;

这就为

customer_id、customer_name

两个字段成功创建了一个名为

idx_group_customer

的组合索引，通过

SHOWINDEXFROMcustomer1;

将会看到两条记录（附图）

全文索引

全文索引可以对全文进行搜索，只有MyISAM存储引擎支持全文索引，并且只为CHAR、VARCHAR和TEXT列，索引总是对整个列进行，不支持局部索引，比如：

DROPTABLEIFEXISTScustomer1;CREATETABLE`customer1`(`customer_id`BIGINT(20)NOTNULLCOMMENT客户ID,`customer_name`VARCHAR()DEFAULTNULLCOMMENT客户姓名,FULLTEXTINDEX`idx_fulltext_customer_name`(`customer_name`))ENGINE=MyISAMDEFAULTCHARSET=utf8mb4COMMENT=客户表;SHOWINDEXFROMcustomer1;

因为默认的存储引擎为

InnoDB

，而全文索引只支持

MyISAM

，所以这里创建表的时候要手动指定一下引擎。

看到这么创建，就在info字段上成功建立了一个名为

idx_fulltext_customer_name

的FULLTEXT全文索引，全文索引非常适合大型数据库，而对于小的数据集，它的用处可能比较小

在已经存在的表上创建索引

在已经存在的表上创建索引，可以使用ALTERTABLE语句或者CREATEINDEX语句，所以，分别讲解一下如何使用ALTERTABLE和CREATEINDEX语句在已知的表字段上创建索引。

ALTERTABLE语法

ALTERTABLE创建索引的基本语法为：

ALTERTABLEtable_nameADD[UNIQUE

FUUTEXT

SPATIAL][INDEX

KEY][index_name](col_name[length],...)[ASC

DESC]

普通索引

ALTERTABLEcustomer1ADDINDEXidx_customer_id(`customer_id`);ALTERTABLEcustomer1ADDINDEXidx_customer_id(customer_name(50));

意思是查询的时候，只需要检索前面50个字符。这里专门提一下，对字符串类型的字段进行索引，如果可以尽可能的指定一个前缀长度，例如，一个CHAR()的列，如果在前10个或者前30个字符内，多数值是唯一的，则不需要对整个列进行索引，短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作。

唯一索引

ALTERTABLEcustomer1ADDUNIQUEINDEX`idx_customer_id`(`customer_id`);

组合索引

ALTERTABLEcustomer1ADDINDEX`idx_group_customer`(`customer_id`,`customer_name`);

CREATETABLE语法

CREATEINDEX语句可以在已经存在的表上添加索引，MySQL中CREATEINDEX被映射到一个ALTERTABLE语句上，基本语法结构为：

CREATE[UNIQUE

FULLTEXT

SPATIAL]INDEXindex_nameONtable_name(col_name[length],...)[ASC

DESC]

看到和ALTERINDEX语句的语法基本一样，下面把

customer1

表删除了再创建，所有字段都没有索引，用CREATEINDEX语句创建一次索引：

CREATEINDEXidx_customer_idONcustomer1(`customer_id`);CREATEUNIQUEINDEXidx_customer_idONcustomer1(`customer_id`);CREATEINDEXidx_group_customerONcustomer1(`customer_id`,`customer_name`);

删除索引

最后一项工作就是删除索引了，可以使用

ALTERTABLE和DROPINDEX

删除索引。

ALTERTABLE语法

ALTERTABLE的基本语法为：

ALTERTABLEtable_nameDROPEXISTSindex_name;ALTERTABLEtable_nameDROPINDEXIFEXISTSindex_name;

建议大家使用第二条

DROPINDEX语法

DROPINDEX的基本语法为：

DROPINDEXindex_nameONtable_nameDROPINDEXIFEXISTSindex_nameONtable_name

建议大家使用第二条

注意一个细节，删除表中的列时，如果要删除的列为整个索引的组成部分，则该列也会从索引中删除；如果组成索引的所有列都被删除，则整个索引将被删除

架构群：733148