MySQL 第四章索引与完整性约束

MySQL 第四章索引与完整性约束

4.1 MySQL索引

    在MySQL中，为了更加高效的访问表中记录的内容，引入了索引。数据库的表按照索引排序，这样我们查找索引项描述的内容时先找到索引，然后按照索引即可定位数据库表的内容。

1.索引

    索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应行的位置，从而快速的找到数据。

2.索引的分类

    索引是储存在文件中的，所以索引也是要占用物理空间的，MySQL将一个表的索引都保存在同一个索引文件中，如果更新表中的一个值或者向表中添加或删除一行，MySQL会自动的更新索引，因此索引树总是和表的内容保持一致。
    索引类型分成下列几个：

• 普通索引（index）：是最基本的索引，没有唯一性之类的限制，创建普通索引的关键字是index
• 唯一性索引（unique）：唯一性索引和普通索引基本相同，但是索引列的所有值都只能出现一次，即必须是唯一的
• 主键（primary key）：主键是唯一性索引。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键，但是每个表只能有一个主键。
• 全文索引（fulltext）：全文索引只能在varchar或text类型的列上创建

3.说明

• 只有当表类型为MyISAM、InnoDB或BDB时，才可以向有null、blob和text的列中添加索引
• 一个表最多有16个索引。最大索引长度是256字节
• 对于char和varchar列，可以索引列的前缀。这样索引的速度更快并且比索引整个列需要较少的磁盘空间。
• MySQL能在多个列上创建索引。索引可以由最多15个列组成

4.2 MySQL索引创建

1.create index 语句创建

    create index 语句可以在一个已有表上创建索引，一个表可以创建多个索引，其语法如下：

create [unique | fulltext | spatial]
index 索引名 [索引类型] on 表名 (索引列名,....)
[索引选项]...

    索引列名：

列名 [(长度)] [ASC | DESC]

注意：

• 索引名：索引在一个表中名称必须是唯一的
• 索引类型：btree和hash。btree为采用二叉树方式，hash为哈希方式。
• 索引列名：创建索引的列名后的长度表示为该列内容从头开始创建索引字符的个数（例如学号为080212，即可创建索引数目5，只索引到08021）。这可使索引文件大大减少，从而节省磁盘空间，另外还可以规定索引按照升序（ASC）或降序（DESC）排列，默认为ASC。

    如果一个索引列包含多个列，中间用逗号隔开，但他们属于同一个表，这样的索引叫做符合索引。
    create index 语句并不能创建主键

例如：根据xs表的学号列上的前5个字符创建一个升序索引xh_xs

 use xscj
 create index xh_xs 
 on xs (学号(5));

例如：在xs_kc表的学号列和课程号列上建立一个符合索引xskc_in

create index xskc_in
on xs_kc(学号,课程号);

2.在建立表时创建索引

    索引也可以在创建表时一起创建，其语法格式如下：

  create table [if exists] 表名
  [ ([ 列定义 ],...| 索引定义) ]
  [ 表选项 ] [select 语句]
  primary key (列名...)

索引定义如下：

primary key [索引类型] (索引列名,....)                        / *主键*/
| {
  index | key} [索引名] [索引类型]  (索引列名,...)                   / *索引*/
| unique  [索引名] [索引类型]  (索引列名,...)                        / *唯一性索引*/
| fulltext | spatial  [索引名] (索引列名,...)                               / *全文索引*/
| foreign key  [索引名] (索引列名,...)  [参照性定义]                  / *外键*/

注意：

• key通常时index的同义词，在定义列的时候，可以将某列定义为primar