MySQL表的约束和索引

最新推荐文章于 2024-08-11 22:10:49 发布

胡安民

最新推荐文章于 2024-08-11 22:10:49 发布

阅读量815

点赞数 1

分类专栏：所有文章文章标签： mysql 数据库 sql 大数据 java

本文链接：https://blog.csdn.net/weixin_45203607/article/details/120248076

版权

所有文章专栏收录该内容

618 篇文章 9 订阅

订阅专栏

MySQL表的约束和索引

主键（PRIMARY KEY）

“主键（PRIMARY KEY）”的完整称呼是“主键约束”。 MySQL主键约束是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。

选取设置主键约束的字段

主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符。主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。主键应该遵守下面的规则：

每个表只能定义一个主键。
主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
一个列名只能在复合主键列表中出现一次。
复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的。这是最小化原则。

在创建表时设置主键约束

在 CREATE TABLE 语句中，主键是通过 PRIMARY KEY 关键字来指定的。

在定义列的同时指定主键，语法规则如下：

<字段名> <数据类型> PRIMARY KEY [默认值]

【实例 1】在 test_db 数据库中创建 tb_emp 3 数据表，其主键为 id，输入的 SQL 语句和运行结果如下所示。

CREATE TABLE tb_emp3
(
     id INT(11) PRIMARY KEY,
     name VARCHAR(25),
     deptId INT(11),
     salary FLOAT
 );

查询结果

mysql> DESC tb_emp3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

在定义完所有列之后，指定主键的语法格式为：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

【实例 2】在 test_db 数据库中创建 tb_emp 4 数据表，其主键为 id，输入的 SQL 语句和运行结果如下所示。

CREATE TABLE tb_emp4
(
    id INT(11),
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    PRIMARY KEY(id)
);

查询结果

mysql> DESC tb_emp4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

在创建表时设置复合主键

复合主键由多个字段联合组成，语法规则如下：

PRIMARY KEY [字段1，字段2，…,字段n]

【实例 3】创建数据表 tb_emp5，假设表中没有主键 id，为了唯一确定一个员工，可以把 name、deptId 联合起来作为主键，输入的 SQL 语句和运行结果如下所示。

CREATE TABLE tb_emp5
   (
     id INT(11),
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    PRIMARY KEY(id,deptId)
);

查询结果

mysql> DESC tb_emp5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

在修改表时添加主键约束

在修改数据表时添加主键约束的语法规则为：

ALTER TABLE <数据表名> ADD PRIMARY KEY(<列名>);

查看 tb_emp2 数据表的表结构，如下所示。

mysql> DESC tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col3   | char(3)     | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.04 sec)

【实例 4】修改数据表 tb_emp2，将字段 id 设置为主键，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_emp2
ADD PRIMARY KEY(id);

查询结果

mysql> DESC tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col3   | char(3)     | YES  |     | NULL    |       |
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.04 sec)

外键约束（FOREIGN KEY）

MySQL外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。

外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。

外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。

外键的主要作用是保持数据的一致性、完整性。例如，部门表 tb_dept 的主键是 id，在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

选取设置 MySQL 外键约束的字段

定义一个外键时，需要遵守下列规则：

父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为父表定义主键。
主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
外键中列的数目必须和父表的主键中列的数目相同。
外键中列的数据类型必须和父表主键中对应列的数据类型相同。

在创建表时设置外键约束

在数据表中创建外键使用 FOREIGN KEY 关键字，具体的语法规则如下：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

其中： 外键名为定义的外键约束的名称，一个表中不能有相同名称的外键； 字段名表示子表需要添加外健约束的字段列； 主表名即被子表外键所依赖的表的名称； 主键列表示主表中定义的主键列或者列组合。

【实例 1】为了展现表与表之间的外键关系，本例在 test_db 数据库中创建一个部门表 tb_dept1，表结构如下表所示。

创建 tb_dept1 的 SQL 语句运行结果如下所示。

 CREATE TABLE tb_dept1
   (
     id INT(11) PRIMARY KEY,
     name VARCHAR(22) NOT NULL,
     location VARCHAR(50)
);

创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id，输入的 SQL 语句和运行结果如下所示。

CREATE TABLE tb_emp6
(
     id INT(11) PRIMARY KEY,
     name VARCHAR(25),
     deptId INT(11),
     salary FLOAT,
     CONSTRAINT fk_emp_dept1
     FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

查询tb_emp6表外键情况

mysql> SHOW CREATE TABLE tb_emp6;

以上语句执行成功之后，在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束，外键字段为 deptId，其依赖于表 tb_dept1 的主键 id。

提示：关联指的是关系数据库中，相关表之间的联系。它是通过相同的属性或属性组来表示的。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时会出现错误“ERROR 1005(HY000)：Can’t create table’database.tablename’(errno：150)”。

在修改表时添加外键约束

在修改数据表时添加外键约束的语法规则为：

ALTER TABLE <数据表名> ADD CONSTRAINT <索引名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

实例 2】修改数据表 tb_emp2，将字段 deptId 设置为外键，与数据表 tb_dept1 的主键 id 进行关联，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_emp2
ADD CONSTRAINT fk_tb_dept2
FOREIGN KEY(deptId)
REFERENCES tb_dept1(id);

查询tb_emp2表结构

mysql> SHOW CREATE TABLE tb_emp2;

删除外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系，MySQL 中删除外键的语法格式如下：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

【实例 3】删除数据表 tb_emp2 中的外键约束 fk_tb_dept1，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_emp2
DROP FOREIGN KEY fk_tb_dept2;

查询tb_emp2表结构

mysql> SHOW CREATE TABLE tb_emp2;

可以发现没有fk_tb_dept2的外键了

注意: 如果在删除外键时候没有此外键那么就会删除失败

唯一约束（UNIQUE KEY）

MySQL唯一约束（Unique Key）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

在创建表时设置唯一约束

在定义完列之后直接使用 UNIQUE 关键字指定唯一约束，语法规则如下：

<字段名> <数据类型> UNIQUE

【实例 1】创建数据表 tb_dept2，指定部门的名称唯一，输入的 SQL 语句和运行结果如下所示。

CREATE TABLE tb_dept2
(
     id INT(11) PRIMARY KEY,
     name VARCHAR(22) UNIQUE,
     location VARCHAR(50)
);

查询tb_dept2结构

mysql> DESC tb_dept2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | YES  | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

UNI 就是唯一约束

提示：UNIQUE 和 PRIMARY KEY 的区别：一个表可以有多个字段声明为 UNIQUE，但只能有一个 PRIMARY KEY 声明；声明为 PRIMAY KEY 的列不允许有空值，但是声明为 UNIQUE 的字段允许空值的存在。

因为是创建时候指定的所以他的唯一约束名称就是他本身

在修改表时添加唯一约束

在修改表时添加唯一约束的语法格式为：

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

【实例 2】修改数据表 tb_dept1，指定部门的名称唯一，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_dept1
ADD CONSTRAINT unique_name UNIQUE(name);

查询tb_dept1结构

mysql>  DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   | UNI | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

删除唯一约束

在 MySQL 中删除唯一约束的语法格式如下：

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

【实例 3】删除数据表 tb_dept1 中的唯一约束 unique_name，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_dept1
DROP INDEX unique_name;

查询tb_dept1结构

mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

可以发现name的key 没有UNI了

检查约束（CHECK）

MySQL检查约束（CHECK）可以通过 CREATE TABLE 或 ALTER TABLE 语句实现，根据用户实际的完整性要求来定义。它可以分别对列或表实施 CHECK 约束。

选取设置检查约束的字段

检查约束使用 CHECK 关键字，具体的语法格式如下：

CHECK <表达式>

其中： <表达式>指的就是 SQL 表达式，用于指定需要检查的限定条件。

若将 CHECK 约束子句置于表中某个列的定义之后，则这种约束也称为基于列的 CHECK 约束。

在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。MySQL 可以使用简单的表达式来实现 CHECK 约束，也允许使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

注意：若将 CHECK 约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的 CHECK 约束。该约束可以同时对表中多个列设置限定条件。

在创建表时设置检查约束

创建表时设置检查约束的语法规则如下：

【实例 1】在 test_db 数据库中创建 tb_emp7 数据表，要求 salary 字段值大于 0 且小于 10000，输入的 SQL 语句和运行结果如下所示。

CREATE TABLE tb_emp7
 (
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    CHECK(salary>0 AND salary<100)
);

我们查询下看看Check设置成功吗

mysql> SHOW CREATE TABLE tb_emp7
| tb_emp7 | CREATE TABLE `tb_emp7` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |

可以发现在tb_emp7表结构里并没有我们创建时候添加的Check语句啊,怎么回事

很明显，CHECK语句在声明中并未起到作用，为什么呢？

是因为，不同于SQL，在MYSQL中，CHECK只是一段可调用但无意义的子句。MySQL会直接忽略。 CHECK子句会被分析，但是会被忽略。

请参见“CREATE TABLE语法”：接受这些子句但又忽略子句的原因是为了提高兼容性，以便更容易地从其它SQL服务器中导入代码，并运行应用程序，创建带参考数据的表。

解决这个问题有两种办法：

如果需要设置CHECK约束的字段范围小，并且比较容易列举全部的值，就可以考虑将该字段的类型设置为枚举类型 enum()或集合类型set()
如果需要设置CHECK约束的字段范围大，列举全部值比较困难，那就只能使用触发器来代替约束实现数据的有效性了。

此外，在MYSQL中，域(Domain)与断言(Assertion)也是无法使用的，原因类似于CHECK

默认值（DEFAULT）

“默认值（Default）”的完整称呼是“默认值约束（Default Constraint）”。 MySQL 默认值约束用来指定某列的默认值。

例如女性同学较多，性别就可以默认为“女”。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。

在创建表时设置默认值约束

创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法规则如下：

<字段名> <数据类型> DEFAULT <默认值>;

【实例 1】创建数据表 tb_dept3，指定部门位置默认为 Beijing，输入的 SQL 语句和运行结果如下所示。

CREATE TABLE tb_dept3
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22),
    location VARCHAR(50) DEFAULT 'Beijing'
);

以上语句执行成功之后，表 tb_dept3 上的字段 location 拥有了一个默认值 Beijing，新插入的记录如果没有指定部门位置，则默认都为 Beijing。

在修改表时添加默认值约束

修改表时添加默认值约束的语法规则如下：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

【实例 2】修改数据表 tb_dept3，将部门位置的默认值修改为 Shanghai，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_dept3
CHANGE COLUMN location
location VARCHAR(50) DEFAULT 'Shanghai';

删除默认值约束

修改表时删除默认值约束的语法规则如下：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

【实例 3】修改数据表 tb_dept3，将部门位置的默认值约束删除，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_dept3
CHANGE COLUMN location
location VARCHAR(50) DEFAULT NULL;

非空约束（NOT NULL）

MySQL 非空约束（NOT NULL）可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。在表中某个列的定义后加上关键字 NOT NULL 作为限定词，来约束该列的取值不能为空。

非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

在创建表时设置非空约束

创建表时可以使用 NOT NULL 关键字设置非空约束，具体的语法规则如下：

<字段名> <数据类型> NOT NULL;

【实例 1】创建数据表 tb_dept4，指定部门名称不能为空，输入的 SQL 语句和运行结果如下所示。

CREATE TABLE tb_dept4
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) NOT NULL,
    location VARCHAR(50)
 );

在修改表时添加非空约束

修改表时设置非空约束的语法规则如下：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

【实例 1】修改数据表 tb_dept4，指定部门位置不能为空，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_dept4
CHANGE COLUMN location
location VARCHAR(50) NOT NULL;

删除非空约束

修改表时删除非空约束的语法规则如下：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

【实例 2】修改数据表 tb_dept4，将部门位置的非空约束删除，输入的 SQL 语句和运行结果如下所示。

ALTER TABLE tb_dept4
CHANGE COLUMN location
location VARCHAR(50) NULL;

查看表中的约束

在 MySQL 中可以使用 SHOW CREATE TABLE 语句来查看表中的约束。

查看数据表中的约束语法格式如下：

SHOW CREATE TABLE <数据表名>;

【实例】创建数据表 tb_emp8 并指定 id 为主键约束，name 为唯一约束，deptId 为非空约束和外键约束，然后查看表中的约束，输入SQL语句运行结果如下。

CREATE TABLE tb_emp8
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) UNIQUE,
    deptId INT(11) NOT NULL,
    salary FLOAT DEFAULT 0,
    CHECK(salary>0),
    FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
 );

查询结构 (注意有的命令行中\G无效如果使用了\G那么结尾就不要加(;)分号)

mysql> SHOW CREATE TABLE tb_emp8 \G
*************************** 1. row ***************************
       Table: tb_emp8
Create Table: CREATE TABLE `tb_emp8` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `deptId` int(11) NOT NULL,
  `salary` float DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `deptId` (`deptId`),
  CONSTRAINT `tb_emp8_ibfk_1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.19 sec)

索引简介

索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。

2、索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

为什么要使用索引

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1) 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2) 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

索引的分类

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。

1) B-树索引

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：

叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

查询必须从索引的最左边的列开始。
查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。

2) 哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B- 树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B- 树索引来说，建立哈希索引会耗费更多的时间。
不能使用 HASH 索引排序。
HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

存储引擎支持的索引

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

1) 普通索引 NORMAL

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

2) 唯一性索引 UNIQUE

唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。

创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。

3) 主键索引 PRIMARY

主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。

注意：主键是数据库考察的重点。注意每个表只能有一个主键。

4) 空间索引 SPATIAL

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

5) 全文索引 FULLTEXT

全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

单列索引就是索引只包含原表的一个列。
组合索引也称为复合索引或多列索引，相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

使用复合索引注意事项 :

创建的时候什么顺序查询的时候就什么顺序这样效率最高
任何情况下必须使用最左边的索引

为了提高索引的应用性能，MySQL中的索引可以根据具体应用采用不同的索引策略。这些索引策略所对应的索引类型有聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引等。

索引的使用原则和注意事项

虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

注意：索引可以在一些情况下加速查询，但是在某些情况下，会降低效率。

索引只是提高效率的一个因素，因此在建立索引的时候应该遵循以下原则：

在经常需要搜索的列上建立索引，可以加快搜索的速度。
在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度。

与此对应，在某些应用场合下建立索引不能提高 MySQL 的工作效率，甚至在一定程度上还带来负面效应，降低了数据库的工作效率，一般来说不适合创建索引的环境如下：

对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。
对于那些只有很少数据值的列也不应该创建索引。因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。
对于那些定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引。因为这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能是互相矛盾的。当创建索引时，会提高检索性能，降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
不会出现在where条件中的字段不该建立索引。

增删查改-索引

创建了唯一约束的时候,就会自动创建唯一索引

在定义主键约束的时候自动定义主键索引

而我们一般所谓的添加索引其实就是普通索引加快查询的速度

索引有3种创建方式但是一般常用的就1种吃遍天

给已存在的表创建索引

创建普通索引

语法:

CREATE INDEX indexName ON table_name (column_name)

实例:

CREATE INDEX index1 ON tb_emp7 (name);

组合索引

CREATE INDEX index2 ON tb_emp7 (deptId,salary);

创建唯一索引

语法:

CREATE UNIQUE  INDEX indexName ON table_name(column_name)

实例:

CREATE  UNIQUE INDEX index1 ON tb_emp7(name);

组合索引

CREATE UNIQUE INDEX index2 ON tb_emp7 (deptId,salary);

创建全文索引

作用于CHAR、VARCHAR、TEXT数据类型的列。

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。
从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。
本文使用的MySQL 版本是5.7.22，InnoDB数据库引擎。

修改MySQL配置文件

[mysqld] 
ngram_token_size=2

或者在没有进入mysql里面的时候直接(前提必须设置好mysql环境变量)

mysqld --ngram_token_size=2

ngram全文解析器

ngram就是一段文字里面连续的n个字的序列。ngram全文解析器能够对文本进行分词，每个单词是连续的n个字的序列。例如，用ngram全文解析器对“生日快乐”进行分词:

n=1: '生', '日', '快', '乐' 
n=2: '生日', '日快', '快乐' 
n=3: '生日快', '日快乐' 
n=4: '生日快乐'

MySQL 中使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为1。在默认值是2的情况下，搜索单字是得不到任何结果的。因为中文单词最少是两个汉字，推荐使用默认值2。

语法:

CREATE FULLTEXT INDEX indexName ON articles (column_name) WITH PARSER ngram;

实例:

CREATE FULLTEXT INDEX index1 ON tb_emp7(name) WITH PARSER ngram;

组合索引

CREATE FULLTEXT INDEX index2 ON tb_emp7 (name,sex) WITH PARSER ngram;

注意如果出现 1167 - The used storage engine can’t index column ‘name’

那么就是你name列的长度是0 在设置全文索引的时候列的类型长度不能为0

创建空间索引

使用SPATIAL关键字。要求，创建空间索引的列，必须将其声明为NOT NULL。

字段类型必须是几何类型

Point （简单点）
linestring （简单线）
polygon （简单面）
MULITIPOINT:多点
MULITILINESTRING:多线
MUILITIPOLYGON:多面
GEOMETRYCOLLECTION:任何几何集合
Geometry():接收一个字符串返回一个几何对象
ASText():把存储的几何对象格式化为字符串

语法:

CREATE SPATIAL  INDEX indexName ON table_name(column_name)

实例:

CREATE SPATIAL INDEX index1 ON tb_emp7(name);

组合索引

空间索引一个字段只允许一个空间索引不允许组合空间索引

创建表的时候也直接指定

语法:

CREATE TABLE table_name(  
............, 
[索引类型] INDEX(column_name ) 
);

普通索引直接INDEX(column_name ) 就行其他的索引都需要加上索引类型

查看索引

show  index  from  table_name;
show  index  from  table_name\G

/G就是将结果格式化好看点结尾不带;

注意: /G在某些工具中有时候不好使

删除索引

删除表的时候全部索引将同时被删除删除列的时候他的对应索引也将被删除

如果是组合索引删除组合索引中的某一列那么这个组合索引将被同时删除

语法:

drop index 索引名  on  表名 ;

修改索引

片转存中…(img-BGZP0jEE-1631415292076)]

创建空间索引

使用SPATIAL关键字。要求，创建空间索引的列，必须将其声明为NOT NULL。

字段类型必须是几何类型

Point （简单点）
linestring （简单线）
polygon （简单面）
MULITIPOINT:多点
MULITILINESTRING:多线
MUILITIPOLYGON:多面
GEOMETRYCOLLECTION:任何几何集合
Geometry():接收一个字符串返回一个几何对象
ASText():把存储的几何对象格式化为字符串

语法:

CREATE SPATIAL  INDEX indexName ON table_name(column_name)

实例:

CREATE SPATIAL INDEX index1 ON tb_emp7(name);

组合索引

空间索引一个字段只允许一个空间索引不允许组合空间索引

创建表的时候也直接指定

语法:

CREATE TABLE table_name(  
............, 
[索引类型] INDEX(column_name ) 
);

普通索引直接INDEX(column_name ) 就行其他的索引都需要加上索引类型

查看索引

show  index  from  table_name;
show  index  from  table_name\G

/G就是将结果格式化好看点结尾不带;

注意: /G在某些工具中有时候不好使

删除索引

删除表的时候全部索引将同时被删除删除列的时候他的对应索引也将被删除

如果是组合索引删除组合索引中的某一列那么这个组合索引将被同时删除

语法:

drop index 索引名  on  表名 ;

修改索引

在 MySQL中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作

点赞 -收藏加 -关注便于以后复习和收到最新内容有其他问题在评论区讨论-或者私信我-收到会在第一时间回复感谢,配合,希望我的努力对你有帮助^_^

胡安民

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录