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://chengdu.cdxwcx.cn/article/jjjpop.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

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

其他资讯

linux播放停止命令 linux暂停命令 pause

c语言函数4个数最大值 c语言四个数最大值

linux终端命令怎么打 linux命令行终端

哪种无线路由器穿墙效果好哪种路由器的穿墙效果好

linux查询vip命令 linux查看vip

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

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

其他资讯

linux播放停止命令 linux暂停命令 pause

c语言函数4个数最大值 c语言四个数最大值

linux终端命令怎么打 linux命令行终端

哪种无线路由器穿墙效果好 哪种路由器的穿墙效果好

linux查询vip命令 linux查看vip

成都网站建设设计将想法与焦点和您一起共享

哪种无线路由器穿墙效果好哪种路由器的穿墙效果好