MySql学习笔记----索引,外键

索引

索引简介

索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。

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

通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表。

索引有两种存储类型，包括B型树（BTREE）索引和哈希（HASH）索引。InnoDB和MyISAM存储引擎支持BTREE索引，MEMORY存储引擎支持HASH索引和BTREE索引，默认为前者。

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了 。

索引的分类

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

1．普通索引
在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。例如，在student表的stu_id字段上建立一个普通索引。查询记录时，就可以根据该索引进行查询。

2．唯一性索引
使用UNIQUE参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。例如，在student表的stu_name字段中创建唯一性索引，那么stu_name字段的值就必需是唯一的。通过唯一性索引，可以更快速地确定某条记录。主键就是一种特殊唯一性索引。

3．全文索引(存储引擎必须是MyISAM类型)
使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，student表的information字段是TEXT类型，该字段包含了很多的文字信息。在information字段上建立全文索引后，可以提高查询information字段的速度。MySQL数据库从3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文检索。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

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

5．多列索引
多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的id、name和sex字段上建立一个多列索引，那么，只有查询条件使用了id字段时该索引才会被使用。

6．空间索引
使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

创建

建表时：

普通索引

CREATE TABLE index1 (id INT ,
name VARCHAR(20) ,
sex BOOLEAN ,
INDEX ( id)
);
SHOW CREATE TABLE语句查看表的结构
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)
);

单列索引

CREATE TABLE index4 (id INT ,
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 [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );

CREATE INDEX index7_id ON example0 ( id ) ;

单列索引
下面在index10表中的address字段上建立名为index10_addr的单列索引。address字段的数据类型为VARCHAR(20)，索引的数据类型为CHAR(4)。

CREATE INDEX index10_addr ON index10( address(4) ) ;
这样，查询时可以只查询address字段的前4个字符，而不需要全部查询。

用ALTER TABLE语句来创建索引

ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名（属性名 [ (长度) ] [ ASC | DESC]）;

ALTER TABLE example0 ADD INDEX index13_name ( name(20) ) ;

删除

一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除

DROP INDEX 索引名 ON 表名 ;
DROP INDEX id ON index1 ;

外键

一、基本概念

1、MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。
2、外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。
3、如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。
4、外键的使用条件
① 两个表必须是InnoDB表，MyISAM表暂时不支持外键
② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；
5、外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

创建

FOREIGN KEY (当前表的字段名)… REFERENCES 参照表 (参照表的字段名)

create table busi_table(
busi_id char(13) not null primary key,
busi_name char(13) not null,
foreign key(repo_id) references repo_table(repo_id));

增加级联操作

alter table 子表 add foreign key(dage_id) references 父表(id) on delete cascade on update cascade;

关键字 含义
CASCADE 删除包含与已删除键值有参照关系的所有记录
SET NULL 修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT　NULL的字段)
RESTRICT 拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的设置)
NO ACTION 啥也不做

删除外键

alter table 子表 drop foreign key key-id;

参考文献《MySQL入门很简单》