Mysql入门-索引

　　本系列内容基本上是从一本叫做《Mysql入门很简单》上面整理的。如果侵犯了你的权益，告诉我，我删掉！

索引

1、索引分类
1.1 普通索引
　　在创建普通索引时，不附加任何限制条件。
　　这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。
　　建立索引以后，查询时可以通过索引进行查询。
　　例如，在student表的stu_id字段上建立一个普通索引。查询记录时，就可以根据该索引进行查询。
1.2 唯一索引
　　使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。
1.3 全文索引
　　使用FULLTEXT参数可以设置全文索引，只能创建在 CHAR、VARCHAR或TEXT类型的字段上。
　　查询数据量较大的字符串类型的字段时，使用全文索可以提高查询速度
1.4 单列索引
　　单列索引是在表中的单个字段上创建索引。只根据该字段进行索引。
　　单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要该索引只对应一个字段即可。
1.5 多列索引(组合索引)
　　多列索引是在表的多个字段上创建一个索引。
1.6 空间索引
　　使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。
　　MySQL中的空间数据类型包括GEOMETRY 和POINT、LINESTRING和POLYGON等。
　　目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。
2、创建索引的原则
2.1．选择唯一性索引
　　唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
2.2. 为经常需要排序、分组和联合操作的字段建立索引
　　经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。
2.3. 为常作为查询条件的字段建立索引
　　如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
2.4. 限制索引的数目
　　索引的数目不是越多越好。每个索引都需要占用磁盘空间，修改表时，对索引的重构和更新很麻烦。
2.5. 尽量使用数据量少的索引
　　如果索引的值很长，那么查询的速度会受到影响。
2.6. 尽量使用前缀来索引
　　如果索引字段的值很长，最好使用值的前缀来索引。
2.7. 删除不再使用或者很少使用的索引
　　表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除。
3、创建索引
3.1. 语法
CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
...
属性名 数据类型
[ UNIQUE|FULLTEXT|SPATIAL ] INDEX|KEY
[别名] (属性名1 [(长度)] [ASC|DESC])
);
注：
UNIQUE是可选参数，表示索引为唯一性索引;FULLTEXT是可选参数，表示索引为全文索引;SPATIAL也是可选参数，表示索引为空间索引;
INDEX和KEY参数用来指定字段为索引的，两者选择其中之一就可以了，作用是一样的;
"别名"是可选参数，用来给创建的索引取的新名称;
"属性1"参数指定索引对应的字段的名称，该字段必须为前面定义好的字段;
"长度"是可选参数，其指索引的长度，必须是字符串类型才可以使用;
"ASC"和"DESC"都是可选参数，"ASC"参数表示升序排列，"DESC"参数表示降序排列。
------------------
CREATE TABLE index1(id INT,
name VARCHAR(20)，
sex BOOLEAN,
INDEX(id) --普通索引
);

CREATE TABLE index2 (id INT UNIQUE,
name VARCHAR(20)，
UNIQUE INDEX index2_id(id ASC) --唯一索引
);

EXPLAIN SELECT * FROM index1 where id=1\G; --EXPLAIN查看执行计划

CREATE TABLE index3 (id INT,
info VARCHAR(20),
FULLTEXT INDEX index3_info(info) --全文索引只能创建在CHAR、VARCHAR或 TEXT类型的字段上。
)ENGINE=MyISAM; ----只有MyISAM存储引擎支持全文索引。

CREATE TABLE index4(id INT，I
subject VARCHAR(30)，
INDEX index4_st(subject(10)) --单列索引
);

CREATE TABLE index5 (id INT,
name VARCHAR(20),
sex CHAR(4),
INDEX index5_ns (name,sex) --多列索引
); --多列索引，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

CREATE TABLE index6 (id INT,
space GEOMETRY NOT NULL,
SPATIAL INDEX index6_sp (space) --空间索引
)ENGINE=MyISAM; --空间索引类型包括GEOMETRY、POINT、LINESTRING和 POLYGON类型等

4、在已存在的表上建立索引
CREATE[ UNIQUE|FULLTEXT|SPATIAL ] INDEX 索引名
ON 表名 (属性名 [(长度)] [ ASC DESC]);
---UNIQUE是可选参数，表示索引为唯一性索引;
---FULLTEXT是可选参数，表示索引为全文索引;
---SPATIAL 也是可选参数，表示索引为空间索引;
---------------------
CREATE INDEX index7_id ON example0(id); --普通索引
CREATE UNIQUE INDEX index8_id ON index8(course_id); --唯一索引
CREATE FULLTEXT INDEX index9_info ON index9(info); --全文索引
CREATE INDEX index10_addr ON index10( address(4)); --单列索引
CREATE INDEX index11_na ON index11( name, address ); --多列索引
CREATE SPATIAL INDEX index12_line ON index12( line ); --空间索引
5、ALTER TABLE添加索引
ALTER TABLE 表名 ADD [ UNIQUE|FULLTEXT|SPATIAL ] INDEX 索引名 (属性名[(长度]) [ASC IDESC];
---------------------
ALTER TABLE example0 ADD INDEX index13_name(name(20)); --普通索引
ALTER TABLE index14 ADD UNIQUE INDEX index14_id(course_id); --唯一索引
ALTER TABLE index15 ADD FULLTEXT INDEX index15_info(info); --全文索引
ALTER TABLE index16 ADD INDEX index16_addr(address(4)); --单列索引
ALTER TABLE index17 ADD INDEX index11_na(name, address); --多列索引
ALTER TABLE index18 ADD SPATIAL INDEX index18_line( line ); --空间索引
6、删除索引
DROP INDEX 索引名 ON 表名;
---------
DROP INDEX id ON index1;