Mysql索引概述（用于个人学习与回顾）

Mysql索引概述

索引介绍

• 索引是对记录集的多个字段进行排序的方法。

• 类似于书的目录

• 索引类型包括：Btree,B+tree,hash
  

索引优缺点

• 索引优点

  -通过创建唯一性索引，可以保证数据库中每一行数据的唯一性

  -可以加快数据的检索速度

• 索引缺点

  -当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，降低数据的维护速度

  -索引需要占物理空间

MySQL键值类型

键值类型

• INDEX：普通索引

• UNIQUE: 唯一索引

• FULLTEXT: 全文索引

• PRIMARY KEY: 主键

• POREIGN KEY: 外键

INDEX普通索引

• 使用说明

  -一个表中可以有多个INDEX字段

  -字段的值允许有重复，且可以赋予NULL值

  -经常把做查询条件的字段设置为INDEX字段

  -INDEX字段的key标志位MUL

• 建表指定索引字段

  -index(字段1),index(字段2)...

mysql> create table test1(    -> id char(6) not null,    -> name varchar(4) not null,    -> age int(3) not null,    -> gender enum('male','female'),    -> index(id),index(name)    -> );Query OK, 0 rows affected (0.06 sec)mysql> desc test1;+--------+-----------------------+------+-----+---------+-------+| Field  | Type                  | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+-------+| id     | char(6)               | NO   | MUL | NULL    |       || name   | varchar(4)            | NO   | MUL | NULL    |       || age    | int(3)                | NO   |     | NULL    |       || gender | enum('male','female') | YES  |     | NULL    |       |+--------+-----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)

• 在已有的表中设置index字段

  -create index 索引名 on表名（字段名）；

mysql> create index age on test1(age);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test1;+--------+-----------------------+------+-----+---------+-------+| Field  | Type                  | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+-------+| id     | char(6)               | NO   | MUL | NULL    |       || name   | varchar(4)            | NO   |     | NULL    |       || age    | int(3)                | NO   | MUL | NULL    |       || gender | enum('male','female') | YES  |     | NULL    |       |+--------+-----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)

• 删除指定表的索引字段

  -drop index 索引名 on 表名；

mysql> drop index name on test1;Query OK, 0 rows affected (0.34 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test1;+--------+-----------------------+------+-----+---------+-------+| Field  | Type                  | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+-------+| id     | char(6)               | NO   | MUL | NULL    |       || name   | varchar(4)            | NO   |     | NULL    |       || age    | int(3)                | NO   |     | NULL    |       || gender | enum('male','female') | YES  |     | NULL    |       |+--------+-----------------------+------+-----+---------+-------+4 rows in set (0.00 sec)

• 查看表的索引信息

  -show index from 表名；

mysql> show index from test1\G;*************************** 1. row ***************************        Table: test1   Non_unique: 1     Key_name: id Seq_in_index: 1  Column_name: id    Collation: A  Cardinality: 0     Sub_part: NULL       Packed: NULL         Null:    Index_type: BTREE      Comment: Index_comment: *************************** 2. row ***************************        Table: test1   Non_unique: 1     Key_name: age Seq_in_index: 1  Column_name: age    Collation: A  Cardinality: 0     Sub_part: NULL       Packed: NULL         Null:    Index_type: BTREE      Comment: Index_comment: 2 rows in set (0.00 sec)

primary key主键

• 基本概念

  -一个表中只能有一个primary key 字段

  -对应的字段值不允许有重复，且不允许赋NULL值

  -如果有多个字段都作为primary key，称为复合主键，必须一起创建

  -主键字段的KEY标志是PRI

  -通常与AUTO_INCREMENT 连用

  -经常把表中能够唯一标识记录的字段设置为主键字段【记录编号字段】

• 建表的时候指定主键字段

  -primary key(字段名)

mysql> create table test2(    -> id int(3) auto_increment,    -> name varchar(4) not null,    -> age int(2) not null,    -> primary key(id)    -> );Query OK, 0 rows affected (0.05 sec)mysql> desc test2;+-------+------------+------+-----+---------+----------------+| Field | Type       | Null | Key | Default | Extra          |+-------+------------+------+-----+---------+----------------+| id    | int(3)     | NO   | PRI | NULL    | auto_increment || name  | varchar(4) | NO   |     | NULL    |                || age   | int(2)     | NO   |     | NULL    |                |+-------+------------+------+-----+---------+----------------+3 rows in set (0.00 sec

• 在已有的表中设置primary key字段

  -alter table 表名 add primary key（字段名）；

mysql> desc test2;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id    | int(3)     | NO   |     | NULL    |       || name  | varchar(4) | NO   |     | NULL    |       || age   | int(2)     | NO   |     | NULL    |       |+-------+------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> alter table test2 add primary key(name);Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test2;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id    | int(3)     | NO   |     | NULL    |       || name  | varchar(4) | NO   | PRI | NULL    |       || age   | int(2)     | NO   |     | NULL    |       |+-------+------------+------+-----+---------+-------+3 rows in set (0.00 sec)

• 移除表中的PRIMARY KEY字段

  -alter table 表名 drop primary key;(如果有auto_increment则需要先移除再移除主键，移除auto_incrememt为对该字段type重新赋值)

ysql> desc test2;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id    | int(3)     | NO   | PRI | NULL    |       || name  | varchar(4) | NO   |     | NULL    |       || age   | int(2)     | NO   |     | NULL    |       |+-------+------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> alter table test2 drop primary key;Query OK, 0 rows affected (0.31 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test2;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id    | int(3)     | NO   |     | NULL    |       || name  | varchar(4) | NO   |     | NULL    |       || age   | int(2)     | NO   |     | NULL    |       |+-------+------------+------+-----+---------+-------+3 rows in set (0.01 sec)

foreign key 外键

• 基本概念

  -让当前表字段的值在另一个表中字段值得范围内选择。

• 使用外键的条件

  -表的存储引擎必须是innodb

  -字段类型要一致

  -被参照字段必须要是索引类型的一种（primary key）

• 基本用法

mysql> create table yuangong(    -> yg_id int(4) auto_increment,    -> name char(16) not null,    -> primary key(yg_id)    -> );Query OK, 0 rows affected (0.31 sec)mysql> create table gongzi(    -> gz_id int(4) not null,    -> name char(15) not null,    -> gz float(6,2) not null default 0,    -> index(name)，    -> foreign key(gz_id) references yuangong(yg_id)    -> on update cascade on delete cascade    -> );Query OK, 0 rows affected (0.32 sec)

• 删除外键字段

  -alter table 表名 drop foreign key 约束名；

mysql> show create table gongzi\G;*************************** 1. row ***************************       Table: gongziCreate Table: CREATE TABLE `gongzi` (  `gz_id` int(4) NOT NULL,  `name` char(15) NOT NULL,  `gz` float(6,2) NOT NULL DEFAULT '0.00',  KEY `name` (`name`),  KEY `gz_id` (`gz_id`),  CONSTRAINT `gongzi_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yuangong` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.01 sec)mysql> alter table gongzi drop foreign key gongzi_ibfk_1;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show create table gongzi\G;*************************** 1. row ***************************       Table: gongziCreate Table: CREATE TABLE `gongzi` (  `gz_id` int(4) NOT NULL,  `name` char(15) NOT NULL,  `gz` float(6,2) NOT NULL DEFAULT '0.00',  KEY `name` (`name`),  KEY `gz_id` (`gz_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

UNIQUE索引

• 基本概念

  -UNIQUE表示唯一性，同一个字段可以有多个字段具有唯一性

• 创建表时指定UNIQUE索引字段

  查看新建test3表的字段结构，可发现UNIQUE字段的KEY标志为UNI；另外，由于字段name必须满足"NOT NULL"的非空约束，所以将其设置为UNIQUE后会自动变成了PRIMARY KEY主键字段：
  

mysql> create table test3(    -> id char(6),    -> name varchar(4) not null,    -> age int(3) not null,    -> unique(id),unique(name),index(age)    -> );Query OK, 0 rows affected (0.36 sec)mysql> desc  test3;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id    | char(6)    | YES  | UNI | NULL    |       || name  | varchar(4) | NO   | PRI | NULL    |       || age   | int(3)     | NO   | MUL | NULL    |       |+-------+------------+------+-----+---------+-------+3 rows in set (0.00 sec)

• 删除UNIQUE索引，在已有的表中设置UNIQUE索引字段

mysql> drop index name on test3;Query OK, 0 rows affected (0.07 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test3;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id    | char(6)    | YES  | UNI | NULL    |       || name  | varchar(4) | NO   |     | NULL    |       || age   | int(3)     | NO   | MUL | NULL    |       |+-------+------------+------+-----+---------+-------+3 rows in set (0.00 sec)

• 在已有表中建立UNIQUE索引

mysql> create unique index name on test3(name);Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test3;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id    | char(6)    | YES  | UNI | NULL    |       || name  | varchar(4) | NO   | PRI | NULL    |       || age   | int(3)     | NO   | MUL | NULL    |       |+-------+------------+------+-----+---------+-------+3 rows in set (0.00 sec)