【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;