索引和数据库约束

索引

以汉语字典的目录页（索引）打比方，我们可以通过拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

在庞大的数据中进行检索时，合理使用索引能够极大的提升数据查询效率，这就是数据库引用索引技术的根本原因。

索引的优缺点

优点：

• 索引大大减小了服务器需要扫描的数据量

• 索引可以帮助服务器避免排序和临时表

• 索引可以将随机IO变成顺序IO

• 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。

缺点：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。

• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

• 对于非常小的表，大部分情况下简单的全表扫描更高效；

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

只为最经常查询和最经常排序的数据列建立索引

MySQL里同一个数据表里的索引总数限制为16个

索引的类型

索引分类

1. 普通索引index ：加速查找

2. 唯一索引

   • 主键索引：primary key ：加速查找+约束（不为空且唯一）

   • 唯一索引：unique：加速查找+约束 （唯一）

3. 联合索引

   • primary key(id,name)：联合主键索引

   • unique(id,name)：联合唯一索引

   • index(id,name)：联合普通索引

4. 全文索引fulltext：用于搜索很长一篇文章的时候，效果最好

5. 空间索引spatial：了解即可

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

普通索引

这是最基本的索引，它没有任何限制。普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。

它有以下几种创建方式：

• 创建索引

  CREATE INDEX indexName ON mytable(username(length));

  如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

• 修改表结构

  ALTER mytable ADD INDEX [indexName] ON (username(length))

• 创建表的时候直接指定

  CREATE TABLE mytable( 
      ID INT NOT NULL, 
      username VARCHAR(16) NOT NULL, 
      INDEX [indexName] (username(length)) 
  );

删除索引：

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

它有以下几种创建方式：

• 创建索引

  CREATE UNIQUE INDEX indexName ON mytable(username(length));

• 修改表结构

  ALTER mytable ADD UNIQUE [indexName] ON (username(length));

• 创建表的时候直接指定

  CREATE TABLE mytable( 
      ID INT NOT NULL, 
      username VARCHAR(16) NOT NULL, 
      UNIQUE [indexName] (username(length)) 
  );

  
  

主键索引

它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。

一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable( 
    ID INT NOT NULL, 
    username VARCHAR(16) NOT NULL, 
    PRIMARY KEY(ID) 
); 

当然也可以用 ALTER 命令。

与之类似的，外键索引

如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable( 
    ID INT NOT NULL, 
    username VARCHAR(16) NOT NULL, 
    city VARCHAR(50) NOT NULL, 
    age INT NOT NULL 
);

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age
​
usernname,city
​
usernname

为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引「最左前缀」的结果。

简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州";
SELECT * FROM mytable WHREE username="admin";

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州";
SELECT * FROM mytable WHREE city="郑州";

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。因为虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL的B-Tree只对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE才会使用索引。

例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州';

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

使用索引的基本准则

1. 不要过度索引。索引越多，占用空间越大，反而性能变慢

2. 只对WHERE子句中频繁使用的建立索引

3. 尽可能使用唯一索引，重复值越少，索引效果越强

4. 使用短索引，如果char(255)太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引

5. 充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将WHERE最频繁的放置在左边

6. 索引存在，如果没有满足使用原则，也会导致索引无效

数据库约束

为了防止往数据表中插入错误的数据，在关系型数据库中，定义了一些维护数据库完整性的规则，即约束。

数据的完整性是指数据的正确性和一致性，可以通过定义表时定义完整性约束，也可以通过规则，索引，触发器等。

约束分为两类：

1. 行级

2. 表级

处理机制是一样的。行级约束放在列后，表级约束放在表后，多个列共用的约束放在表后。

约束类型

关系型数据库的约束类型常见的为以下几种：

1. 主键约束（Primay Key Coustraint） 唯一性，非空性

2. 唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个

3. 检查约束 （Check Counstraint）对该列数据的范围、格式的限制（如：年龄、性别等）

4. 默认约束 （Default Counstraint）该数据的默认值

5. 外键约束 （Foreign Key Counstraint）需要建立两表间的关系并引用主表的列

表中的约束条件都是针对表中字段进行限制， 从而保证数据表中数据的正确性和唯一性。

主键约束

在MySQL中，为了快速查找表中的某条信息，可以通过设置主键来实现。主键约束是通过PRIMARY KEY

定义的，它可以唯一标识表中的记录，这就好比身份证可以用来标识人的身份一样。在MySQL中，主键约束

分两种，具体如下：

1. 单字段主键

   单字段主键指的是由一个字段构成的主键，其基本语法如下：

   cloumn_name data_type PRIMARY KEY;

   示例

   -- 创建一个数据表student，id为主键
   create table student(
       id int(5) primary key,
       name varchar(20),
       grade float
   );

2. 多字段主键 （复合主键）

   多字段主键指的是多个字段组合而成的主键，其基本的语法格式如下所示：

   PRIMARY KEY(COL_NAME1 , COL_NAME2 , .... COL_NAME);

   示例

   -- 创建一个数据表student，id和name为主键
   create table student(
       id int(5) ,
       name varchar(20),
       grade float
       primary key(id,name)
   );

复合主键也只能有一个

外键约束

如果表A的主键是表B中的字段，则该字段称为表B的外键；另外表A称为主表，表B称为从表。外键是用来实现参照完整性的，不同的外键约束方式将可以使两张表紧密的结合起来，特别是修改或删除的级联操作将使得日常维护更轻松。外键主要用来保证数据的完整性和一致性。其基本语法如下：

column_name data_type REFERENCES master_table_name(mas_col_name)
[
    [ON DELETE reference_option]
    [ON UPDATE reference_option]
]

master_table_name: 主表名

mas_col_name：主表的主键

data_type 要与 mas_col_name 数据类型相似，即可自动转换

reference_option为外键操作的动作，InnoDB支持5种

1. CASCADE：主从表同步，即从主表删除或者更新对应行，同时自动删除或者更新从表中匹配行

2. SET NULL：从主表删除或更新对UI哪个行，从表队形行外键设置空

3. NO ACTION：拒绝删除或者更新主表

4. RESTRICT：拒绝删除或者更新主表，这个选项是默认值

5. SET DEFAULT：从主表删除或者更新对应行，从表对应行的外键设置为默认值，InnoDB不支持这个选项

一个表允许有多个外键，且外键只适用于InnoDB表

非空约束

非空约束指的是字段的值不能为NULL，在MySQL中，非空约束是通过NOT NULL定义的，其基本语法如下：

column_name data_type NOT NULL;

示例

-- 创建一个数据表student，id为主键
create table student(
    id int(5) not null primary key,
    name varchar(20) not null ,
    grade float
);

一个表可以有多个非空约束字段

唯一约束

唯一约束用于保存数据表中字段的唯一性，类似于主键，即表中字段值不能重复出现。唯一约束是通过UNIQUE定义的，其基本语法如下：

column_name data_type UNIQUE;

示例

-- 创建一个数据表student，id为主键 ， 手机号码是唯一
create table student(
    id int(5) not null primary key,
    name varchar(20) not null ,
    phone varchar(11) UNIQUE
);

一个表可以有多个唯一约束字段

默认约束

默认约束用于给数据表中的字段指定默认值，即当在表中插入一条新纪录时，如果没有给这个字段赋值，那么，数据库系统会自动为这个字段插入默认值。默认值是通过DEFAULT关键字定义的，其基本语法如下：

column_name datatype DEFAULT value;

示例

-- grade 设置默认值
create table student(
    id int(5) not null primary key,
    name varchar(20) not null ,
    grade float DEFAULT 0
);

一个表可以有多个默认约束字段

图形化工具操作方式

在表编辑面板中选择外键选项卡即可