MySQL 索引有哪几种?详解(主键、唯一、普通、组合,全文、前缀索引)
1.1索引是怎样分类的?
逻辑应用维度
-
主键索引:主键索引是一种特殊的唯一索引,不允许有空值(唯一索引允许值为空)。
-
普通索引或者单列索引:每个索引只包含单个列,一个表可以有多个单列索引。
-
多列索引(复合索引、联合索引):复合索引指多个字段上创建的索引,只有在查
询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合 -
唯一索引:表中字段的值不能出现重复的,但允许为null值。
-
空间索引:空间索引是对空间数据类型的字段建立的索引,空间索引只能在存储引擎为 MYISAM 的表中创建。
物理存储维度
-
聚簇索引(clustered in dex ):在 InnoDB 中一张表只有一个聚簇索引(一般可以理解为主键索引),索引和数据是在一起的。
-
非聚簇索引(non-clustered index):也 叫
辅 助 索 引
( s e co nd a ry in d ex)非聚簇索引是索引和数据分离的。
数据存储结构维度
-
B+树索引。(最重要,innodb默认)
-
Hash 索引。
-
Full-Text 全文索引。
-
R-Tree 索引。
1.2如何创建索引
1.2.1 普通索引
普通索引为 MySQL 中的基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
创建普通索引的方式如下:
CREATE TABLE tablename (…,INDEX[索引名字](字段名));
CREATE INDEX 索引的名字 ON tablename(字段名);
ALTER TABLE table_name ADD INDEX index_name (column_name);
1.2.2 唯一索引
唯一索引与普通索引类似,只是索引列中的值必须是唯一的,但是允许为空值。
CREATE TABLE tablename (…,UNIQUE[索引名字](字段名));
CREATE UNIQUE INDEX 索引的名字 ON tablename(字段名);
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);
1.2.3 主键索引
主键索引是一种特殊的唯一索引,索引列中的值必须是唯一的,并且不允许有空值。
CREATE TABLE table_name (…,PRIMARY KEY (字段名))
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
1.2.4 组合索引
组合索引又称为复合索引
,在表中的多个字段组合
上创建的索引,组合索引的使用,需要遵循最左前缀原则(最左匹配原则
)。一般情况下,建议使用组合索引代替单列索引(主键索引除外)
CREATE TABLE table_name (…,INDEX index_name (column1,column2,…))
CREATE INDEX index_name ON table_name (column1,column2,…))
ALTER TABLE table_name ADD INDEX index_name (column1,column2,…);
说明:
-
复合索引的字段是有顺序的,在查询使用时要按照定义时索引字段的顺序使用,例如select * from test where name=xxx and phone=xxx;匹配(name,age)组合索引,不匹配(age,name)索引。
-
如果表已经建立了(col1,col2),就没有必要再单独建立(col1);
1.2.5 全文索引
只能在文本类型CHAR,VARCHAR,TEXT
类型字段上创建全文索引。字段长度比较大时,如果创建普通索引,在进行 like 模糊查询时效率比较低,这时可以创建全文索引。
数据量大或者并发度高的时候一般是用专业的工具 lucene,es,solr。
CREATE TABLE table_name (…,FULLTEXT KEY index_name (column)); CREATE FULLTEXT INDEX index_name ON table_name (column);
ALTER TABLE `t_fulltext` ADD FULLTEXT INDEX`idx_content`(`content`);
1.2.6 前缀索引
在文本类型如 CHAR,VARCHAR,TEXT
类列上创建索引时,可以指定索引列的长度
,但是数值类型不能指定。
ALTER TABLE table_name ADD INDEX index_name (column1(length));