【MySQL】索引

 

目录

9.1.1  索引的含义和特点

9.1.2  索引的分类

9.1.3  索引的设计原则

9.2  创建索引

9.3  删除索引


9.1.1  索引的含义和特点

索引是一个单独的、存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针。

索引用于快速找出在某个列中有一特定值的行。不使用索引, MySQL必须从第1条记录开始读完整个表,直到找出相关的行。表越大,查询数据所花费的时间越多。如果表中查询的列有一个索引, MySQL能快速到达某个位置搜寻数据文件,而不必查看所有数据。

索引根据存储引擎的不同而有不同的最大索引(16)或索引长度(256);

存储引擎

支持的索引

MyISAM

BTREE

InnoDB

MEMORY

HASH

HEAP

 

索引的优点。

       索引的优点主要有以下几条;

(1)通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。·

(2)可以大大加快数据的查询速度,这也是创建索引的最主要的原因。

(3)在实现数据的参考完整性方面,可以加速表和表之间的连接。

(4)在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间。

 

索引的缺点。

(1)创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。

(2)索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理 ,空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。

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

 

9.1.2  索引的分类

a. 普通索引和唯一索引

       普通允许索引的列中重复或者空值;

       唯一中列值唯一,允许空值。若是组合索引,则列值的组合必唯一。主键也是特殊唯一索引,但不允许空值;

 

b. 单列索引和组合索引

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

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

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

d. 空间索引空间索引是对空间数据类型的字段建立的索引, MySOL中的空间数据类型有4种,分别是: GEOMETRY(几何), POINT, LINESTRING(线)和POLYGON(多边形). MySOL使用SPATIAL(空间)关键字进行扩展,使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MyISAM的表中创建。

 

9.1.3  索引的设计原则

索引并非越多越好。

避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。

数据量小的表最好不要使用索引。

在条件表达式中经常用到的不同值较多的列上建立检索。

当唯一性是某种数据本身的特征时,指定唯一索引。

在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引,可组合。

 

9.2  创建索引

SHOW INDEX FROM book \G 查看指定表中创建的索引;

9.2.1  创建表的时候创建索引

创建表时创建索引的基本语法格式如下:

CREATE  TABLE 

     table_name [col_name data_type]
    [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]    

    [index_name] (col_name [length])

    [ASC | DESC]

 

explain select * from book where year_publication=1990 使用EXPLAIN语句查看索引是否正在使用;

UNIQUE INDEX UniqIdx(id) 在id字段上已经建立了一个名为UniqIdx的唯一索引

INDEX SingleIdx(name(20)) 在name字段上已经建立了一个名为SingleIdx的单列索引,索引长度为20,长度越精确越快,体积越小。
INDEX MultiIdx(id, name(10), age) 在 id、name和age字段上建立了一个名为MultiIdx的组合索引。

组合索引可起几个索引的作用,但是使用时并不是随便查询哪个字段都可以使用索引,而是遵从“最左前缀”:利用索引中最左边的列集来匹配行,这样的列集称为最左前缀。例如这里由id、name和age 3个字段构成的索引,索引行中按id/name/age的顺序存放,索引可以搜索下面字段组合:(id, name, age)、(id, name)或者id。如果列不构成索引最左面的前缀,MySQL不能使用局部索引,如(age)或者(name,age)组合则不能使用索引查询。

多列索引是先按照第一列进行排序,然后在第一列排好序的基础上再对第二列排序,如果没有第一列的话,直接访问第二列,那第二列肯定是无序的,直接访问后面的列就用不到索引了。

请见原文的详细解答

 

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

1.使用ALTER TABLE语句创建索引

ALTER TABLE 表名 ADD [FULLTEXT/SPATIAL] INDEX 索引名(字段(长度));

2.使用CREATE INDEX创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

ON table_name (col_name[length],…升降序) [ASC | DESC]


9.3  删除索引

1.使用ALTER TABLE删除索引

ALTER TABLE table_name

       DROP INDEX index_name;

2.使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

 

 

 

 

展开阅读全文

没有更多推荐了,返回首页