mysql 定时更新索引_MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

一、索引的简介

1.索引的概念：

是数据库对象，实现数据库快速查询

2.为什么使用索引：

实现数据库快速查询，提高查询速度

3.索引的分类：

a.普通索引

最基本的索引，对字段数据的类型和值没有任何限制，数据类型可以任意，字段的值可以空，也可以重复

b.主键索引

给主键字段添加的索引

主键特点：非空且唯一

c.唯一索引

给唯一字段添加的索引

唯一索引和主键索引的区别：

唯一索引：只有唯一、可以有空值

主键索引：非空且唯一

d.全文索引

适用于在一大串文本添加的索引，只可以给字符串数据类型添加

字符串数据类型：(char varchar text)

e.空间索引

给字段的数据类型只能是空间数据类型，且该字段的值必须是非空 not null

空间数据类型：geometry point linestring polygon

f.复合索引

给多个字段添加的索引

注意：如果添加了复合索引，查询条件中只有使用了第一个字段，该索引才会被触发

例如：(id,name)只有查询条件中使用了id字段，索引才会被使用

如果查询条件中只有name字段，则索引不会被触发

二、创建索引

(1)自动创建索引

如果在创建表时，给表添加了主键约束和唯一约束，MySQL数据库会自动为主键约束和唯一约束创建对应的主键索引和唯一索引

例如：创建表index_student,并为表添加主键和唯一约束

查询表中的索引

语法：show index from 表名

--创建表index_student,并为表添加主键和唯一约束

create table index_student(

sno int(8) PRIMARY KEY auto_increment,

sname varchar(20) unique,

age int(2)

)

--查看表index_student中的索引

show INDEX from index_student

(2)手动创建索引

a.创建表时创建索引

1.创建普通索引

语法：create table 表名(

字段名1 字段类型1，

字段名2 字段类型2，

……

index | key 【索引名】【索引类型】(字段名【长度】【asc | desc】)

)descending order 降序

ascending order 升序

--创建表index_student2,给sno添加索引

create table index_student2(

sno int(8),

sname varchar(20),

age int(2),

index(sno)

)

--查看表index_student2中的索引

show INDEX from index_student2

2.唯一索引的创建

语法：create table 表名(

字段名1 字段类型1，

字段名2 字段类型2，

……

unique [ index | key ] 【索引名】【索引类型】(字段名【长度】【asc | desc】)

)

--创建表index_student3,给sname添加索引

create table index_student3(

sno int(8),

sname varchar(20),

age int(2),

unique index(sname)

)

--查看表index_student3中的索引

show INDEX from index_student3

3.主键索引的创建

语法：create table 表名(

字段名1 字段类型1，

字段名2 字段类型2，

……

primary key [ index | key ] 【索引名】【索引类型】(字段名【长度】【asc | desc】)

)

--创建表index_student4,给sname添加索引

create table index_student4(

sno int(8),

sname varchar(20),

age int(2),

primary key(sno)

)

--查看表index_student4中的索引

show INDEX from index_student4

4.全文索引的创建

注意：只能给字符串数据类型添加

语法：create table 表名(

字段名1 字段类型1，

字段名2 字段类型2，

……

fulltext [ index | key ] 【索引名】【索引类型】(字段名【长度】【asc | desc】)

)

--创建表index_student5,给sname添加索引

create table index_student5(

sno int(8),

sname varchar(20),

sinfo varchar(100),

FULLTEXT(sinfo)

)

--查看表index_student5中的索引

show INDEX from index_student5

5.空间索引的创建

注意：只能给空间数据类型添加，且该字段的值不能为空 not null

语法：create table 表名(

字段名1 字段类型1，

字段名2 字段类型2，

……

spatial [ index | key ] 【索引名】【索引类型】(字段名【长度】【asc | desc】)

)

--创建表index_student6,给sloc字段的类型是point，添加空间索引

create table index_student6(

sno int(8),

sname varchar(20),

age int(2),

sloc point not null,

spatial index(sloc)

)

--查看表index_student6中的索引

show INDEX from index_student6

6)复合索引的创建

语法：create table 表名(

字段名1 字段类型1，

字段名2 字段类型2，

……

index | key 【索引名】【索引类型】(字段名1【(长度)】【asc | desc】，字段名2【长度】【asc | desc】...)

)

--创建表index_student7,给sno和sname添加复合索引

create table index_student7(

sno int(8),

sname varchar(20),

age int(2),

index(sno,sname)

)

--查看表index_student7中的索引

show INDEX from index_student7

b.创建表后使用“create index”创建索引

语法：create [ unique | fulltext | spatial ] index 索引名称 [索引类型] on 表名 (字段名1【长度】【asc | desc】，字段名2【(长度)】【asc | desc】...)

注意：使用 create index这种创建索引的方式不能创建主键索引

1.创建普通索引

--创建表index_student8,给表中sno添加普通索引

create table index_student8(

sno int(8),

sname varchar(20),

age int(2)

)

create index index_student8_sno on index_student8(sno)

--查看表index_student8中的索引

show INDEX from index_student8

2.创建唯一索引

--给表index_student8中sname 添加唯一索引

create unique index index_student8_sname on index_student8(sname)

--查看表index_student8中的索引

show INDEX from index_student8

3.创建全文索引 fulltext

--创建表index_student9,给表中sinfo添加全文索引

create table index_student9(

sno int(8),

sname varchar(20),

sinfo varchar(100)

)

create fulltext index index_student9_sinfo on index_student9(sinfo)

--查看表index_student9中的索引

show INDEX from index_student9

4.创建空间索引 spatial

--创建表index_student10,给表中sloc添加空间索引

create table index_student10(

sno int(8),

sname varchar(20),

age int(2),

sloc point not null

)

create spatial index index_student10_sloc on index_student10(sloc)

--查看表index_student10中的索引

show INDEX from index_student10

5.创建复合索引

--创建表index_student11,给表中sno和sname添加复合索引

create table index_student11(

sno int(8),

sname varchar(20),

age int(2)

)

create index index_student11_sno_sname on index_student11(sno,sname)

--查看表index_student11中的索引

show INDEX from index_student11

c.给已有表添加索引“alter table”

1.创建普通索引

语法：alter table 表名 add index|key 【索引名】【索引类型】(字段名[长度] 【asc|desc】)

--创建表index_student12,给表中sno添加普通索引

create table index_student12(

sno int(8),

sname varchar(20),

age int(2)

)

alter table index_student12 add index(sno)

--查看表index_student12中的索引

show INDEX from index_student12

2.创建唯一索引

语法：alter table 表名 add unique【index|key】 【索引名】【索引类型】(字段名[长度] 【asc|desc】)

--创建表index_student13,给表中sno添加唯一索引

create table index_student13(

sno int(8),

sname varchar(20),

age int(2)

)

alter table index_student13 add unique index(sname)

--查看表index_student13中的索引

show INDEX from index_student13

3.创建主键索引

语法：alter table 表名 add primary key【index|key】 【索引名】【索引类型】(字段名[长度] 【asc|desc】)

--创建表index_student14,给表中sno添加主键索引

create table index_student14(

sno int(8),

sname varchar(20),

age int(2)

)

alter table index_student14 add PRIMARY KEY(sname)

--查看表index_student14中的索引

show INDEX from index_student14

4.创建全文索引

语法：alter table 表名 add fulltext【index|key】 【索引名】【索引类型】(字段名[长度] 【asc|desc】)

--创建表index_student15,给表中sinfo添加全文索引

create table index_student15(

sno int(8),

sname varchar(20),

sinfo varchar(100)

)

alter table index_student15 add FULLTEXT(sinfo)

--查看表index_student15中的索引

show INDEX from index_student15

5.创建空间索引

语法：alter table 表名 add spatial【index|key】 【索引名】【索引类型】(字段名[长度] 【asc|desc】)

--创建表index_student16,给表中sloc添加空间索引

create table index_student16(

sno int(8),

sname varchar(20),

sloc point not null

)

alter table index_student16 add SPATIAL(sloc)

--查看表index_student16中的索引

show INDEX from index_student16

6.创建复合索引

语法：alter table 表名 add index|key 【索引名】【索引类型】(字段名1 [长度] 【asc|desc】，字段名1 [长度] 【asc|desc】...)

--创建表index_student17,给表中sno和sname添加复合索引

create table index_student17(

sno int(8),

sname varchar(20),

age int(2)

)

alter table index_student17 add INDEX(sno,sname)

--查看表index_student17中的索引

show INDEX from index_student17

三、删除索引

1.使用alter table删除

语法：alter table 表名 drop index|key 索引名称

alter table index_student17 drop key sno

2.使用drop index删除

语法：drop index 索引名称 on 表名

show INDEX from index_student16

drop INDEX sloc on index_student16

注意：使用alter table方式删除索引不能删除主键索引

删除主键索引可以使用：

alter table 表名 drop primary key

show INDEX from index_student14

--不能删除主键索引alter table index index_student14 drop index PRIMARY

--使用alter table 表名 drop primary KEY

alter table index_student14 DROP PRIMARY KEY