Mysql索引概述（用于个人学习与回顾）
MySQL索引概述
专注于为中小企业提供网站设计、成都做网站服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业玛多免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了上1000家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。
索引介绍
索引是对记录集的多个字段进行排序的方法。
类似于书的目录
索引类型包括：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: 0
mysql> 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: 0
mysql> 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: 0 mysql> 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: 0 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)
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: gongzi Create 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=latin1 1 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: 0 mysql> show create table gongzi\G; *************************** 1. row *************************** Table: gongzi Create 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=latin1 1 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: 0 mysql> 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: 0 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)
新闻标题：Mysql索引概述（用于个人学习与回顾）
网页地址：http://6mz.cn/article/jjjpop.html