MySQL索引入门实例

MySQL索引入门实例

索引的含义和特点：

索引是创建在表上的，是对数据库中一列或者多列的值进行排序的一种结构，索引可以提高查询速度。

 

通过索引，查询数据时可以不必完全记录所有的信息，而只是查询索引列。否则，数据库系统将每条记录的所有信息进行匹配。就相当于是新华字典的音序表。如果没有这个音序表那么我们在查一个字的时候需要在整本字典上查找，是相当耗时耗力的，有了这个音序表，那么再查这个字只需要在音序表上查到他的索引，根据索引直接去找这个字，速度就相当快了。索引就是这个意思。

运用技巧：

索引可以提高查询速度，但是会影响插入记录的速度。因为向有索引的表中插入记录时，数据库系统会自动按照索引进行排序。这样就会降低插入记录的速度，所以在插入大量的数据时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

 

    MySQL的索引包括普通索引，唯一索引，全文索引，单列索引，多列索引，和空间索引等。

1.普通索引:在创建普通索引时，不附加任何限制条件，可以创建在任何数据类型中。

2.唯一索引:使用unique参数可以设置索引为唯一性索引，唯一性索引在创建时限制该索引的值必须是唯一的。

3.全文索引:使用fulltext参数可以设置索引为全文索引。全文索引的类型可以创建在char,varchar或text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引提高查询速度，只有myisam存储引擎支持全文检索。

4.单列索引:在表中的单个字段上创建的索引。单列索引只根据该字段进行索引，可以是普通索引，唯一性索引，还可以是全文索引，必须保证该字段只对应一个字段。

5.多列索引:在表中的多个字段上创建的索引。该索引创建对应多个字段，可以通过这几个字段进行查询，但是只有在查询时使用了这些字段中第一个字段时，索引才会被使用。

6.空间索引:使用spatial参数可以设置索引为空间索引，空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括geometry，point，linestring,polygon等，只有myisam存储引擎支持空间检索，而且索引的字段不能为空值。

    索引的设计原则：

1.选择唯一性索引

2.为经常需要排序，分组和联合操作的字段建立索引

3.为常作为查询条件的字段建立索引

4.限制索引的数目

5.尽量使用数据量少的索引

6.尽量使用前缀来索引

7.删除不再使用或很少使用的索引

MySQL索引的创建、删除

#创建普通索引  创建普通索引时不需要加任何unique fulltext spatial参数

create table index1 (id int,name varchar(20),sex boolean,index(id));

#查看表结构

show create table index1;

#使用explain语句查看索引是否被使用

explain select * from index1 where id = 1;

 

#创建唯一索引

create table index2 (id int unique,name varchar(20), unique index index2_id(id asc));

#查看表结构

show create TABLE index2;

explain select * from index2 where id = 1;

 

#创建全文索引

create table index3 (id int, info varchar(20),fulltext index index3_info(info))engine = MyISAM;

#查看表结构

show create TABLE index3;

 

#创建单列索引

create table index4 (id int,subject varchar(30),index index4_st(subject(10)));

#查看表结构

show create TABLE index4;

 

#创建多列索引     查询过程中，只有使用了这些字段中的第一个字段时，索引才会被使用，如果不用第一个字段查询，该索引不会启用

create table index5(id int ,name varchar(32),sex varchar(10),index index5_duo(name(10),sex(10)));

#查看表结构

show create TABLE index5;

 

#创建空间索引   创建空间索引时必须使用spatial参数来设置，表的存储引擎必须是myisam,而且索引字段必须有非空约束

create table index6(id int ,space geometry not null,spatial index index6_sp(space))ENGINE MyISAM;

#查看表结构

show create TABLE index6;

 

#在已存在的表上创建普通索引

create index index7_id on student(stuID);

#查看表结构

show create TABLE student;

 

#在已存在的表上创建唯一性索引

create unique INDEX index8_id on borrow(borrowID);

#查看表结构

show create TABLE borrow;

 

#在已存在的表上创建全文索引

create FULLTEXT index index9_info on index3(info);

#查看表结构

show create TABLE index3;

 

#在已存在的表上创建单列索引

create index index10_aut on book(author(4));

#查看表结构

show create TABLE index3;

 

#在已存在的表上创建多列索引

create index index11_duo on borrow(T_time,B_time);

#查看表结构

show create TABLE index3;

 

#在已存在的表上创建空间索引

create SPATIAL index index12_sp on index6(space);

#查看表结构

show create TABLE index6;

 

#用alter table 语句来创建普通索引

alter table index1 add index index1_altername(name(10));

#查看表结构

show create TABLE index1;

 

#用alter table 语句来创建唯一

alter table index2 add unique index2_altername(name(10));

#查看表结构

show create TABLE index2;

 

#用alter table语句创建全文索引

alter table index3 add FULLTEXT index3_alterquan(info);

#查看表结构

show create TABLE index3;

 

#用alter  table 语句创建单列索引

alter table index4 add index index4_alterdan(subject);

 

#用alter table语句创建多列索引

alter table borrow add index index4_alterduo(T_time,B_time);

 

#用alter table语句创建空间索引

alter table index6 add SPATIAL index index5_alterspace(space);

 

#删除索引

show create TABLE index6;

#drop index 索引名 on 表名；

drop index index5_alterspace on index6;