Mysql学习7-视图与索引

视图在数据库中的作用类似于窗户，用户可以通过这个窗口看到只对自己有用的数据。既保障了数据的安全性，又大大提高了查询效率。

索引是提高数据库性能的重要方式，用来快速找出数据表中的特定记录。如果在表中查询的列有一个索引，MySQL 能快速到达一个位置去搜寻数据，而不必查看所有数据。

本文介绍这两个概念。

1. 视图

之后介绍…

2. 索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，用来快速查询数据表中是否有某一特定值的记录。其原理类似于书籍的目录，使用索引的好处是可以极大地提高数据库的查询速度。

2.1 优缺点

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

1. 顺序访问：在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。当表中有大量数据时，效率非常底下。
2. 索引访问：通过遍历索引来直接访问表中的记录行。使用这种方法的前提是对表建立了一个索引，索引存储了指定记录行的指针，根据指定的排序顺序对这些指针排序。例如，在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

根据以上描述，我们可以总结索引的优缺点

所以，索引虽然可以提高查询速度，但会影响插入记录的速度，当插入大量数据时，最好先删除表中的索引，然后插入数据，插入完成后，再创建索引。

2.2 索引类型

如果按底层实现分，MySQL 的索引可以分为 B+ 树索引和 Hash 索引两类。

• B+树索引又称为 BTREE 索引，主要采用B+ 树结构，基于这种结构构建索引，表中每一行都会在索引上有一个对应值，B+树的查找时间复杂度为 O(H)，H为树高，且可以保证树高始终处于可能的最小值。B+树可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序，主要是得益于倒数第二条特性，所有的值在倒数第二层以升序排列，且有指针连接。
• 哈希索引又称为散列索引，使用哈希表作为存储结构，哈希索引最大的特点是访问单条记录速度快，但也存在一些缺点：
  • MySQL 需要读取表中索引列的值来参与哈希计算，而哈希计算是一个比较耗时的操作，也就是说，相比 B+ 树索引，建立哈希索引需要耗费更多的时间；
  • 不能使用哈希索引排序；
  • 哈希索引只支持等值查询，比如 “=”，“IN()”，“<=>”
  • 哈希索引不支持键的部分匹配，因为在计算哈希值的时候是通过整个索引值来计算的。

如果按存储方式分，可以分为聚簇索引和非聚簇索引

• 找到了索引就找到了需要的数据，那么这个索引就是聚簇索引
• 索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

如果按逻辑区分，MySQL 的索引可以分为5类

• 普通索引：MySQL 中最基本的索引类型，没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。创建普通索引时，通常使用的关键字是 INDEX 和 KEY。比如，在 tb_student 表中的 id 字段上建立名为 index_id 的索引，语句如下

  1	CREATE INDEX index_id ON tb_student(id);

• 唯一索引：与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。唯一索引列的值必须唯一，允许有空值，如果是组合索引，则列值的组合必须唯一。创建唯一索引通常使用 UNIQUE 关键字。比如，在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下：

  1	CREATE UNIQUE INDEX index_id ON tb_student(id);

• 主键索引：专门为主键字段创建的索引，是一种特殊的唯一索引，不允许值重复或者值为空。创建主键索引通常使用 PRIMARY KEY 关键字，不能使用 CREATE INDEX 语句创建主键索引。

• 空间索引：空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。下面在 tb_student 表中的 line 字段上建立名为 index_line 的索引，SQL 语句如下，其中，tb_student 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

  1	CREATE SPATIAL INDEX index_line ON tb_student(line);

• 全文索引：用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。创建全文索引使用 FULLTEXT 关键字。在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下，其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

  1	CREATE FULLTEXT INDEX index_info ON tb_student(info);

如果按实际使用区分，索引通常被分为单列索引和组合索引

• 单列索引就是索引只包含原表的一个列，仅根据该列的值进行索引。单列索引可以是普通索引，也可以是唯一索引或全文索引。下面在 tb_student 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(4)。SQL 语句如下：

  1	CREATE INDEX index_addr ON tb_student(address(4));

  这样，查询时可以只查询 address 字段的前 4 个字符，而不需要全部查询。

• 组合索引又称为符合索引或多列索引，是将原表的多个列共同组成一个索引，可以通过这几个字段进行查询，但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用

  下面在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引，SQL 语句如下：

  1	CREATE INDEX index_na ON tb_student(name,address);

  该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。

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

每种存储引擎所支持的索引类型不一定完全相同，如下

1
2
3
4
5

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

2.3 创建索引

MySQL 提供了三种创建索引的办法。

第一种是上一小节提到的使用 CREATE INDEX 语句，语法格式如下

1

CREATE INDEX <索引名> ON <表名> (<列名> [<长度>] [ASC|DESC])

• <长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
• ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

第二种是在创建表（CREATE TABLE）的同时建立索引，只需要在建表语句中添加下面的语句即可

1
2
3
4

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…) # 表示在创建新表的同时创建该表的主键
KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…) # 创建新表的同时创建该表的索引
UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…) # 创建新表的同时创建该表的唯一性索引
FOREIGN KEY <索引名> <列名> # 创建新表的同时创建该表的外键

一个在创建表时建立普通索引的例子如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> CREATE TABLE tb_stu_info
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> INDEX(height)
    -> );
Query OK，0 rows affected (0.40 sec)

一个在创建表时建立唯一索引的例子如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

mysql> CREATE TABLE tb_stu_info2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> UNIQUE INDEX(height)
    -> );
Query OK，0 rows affected (0.40 sec)

第三种是在利用 ALTER TABLE 语句修改表的时候向该表添加索引，方法是添加下面的语句

1
2
3
4

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…) # 修改表的同时为该表添加索引
ADD PRIMARY KEY [<索引类型>] (<列名>,…) # 表示在修改表的同时为该表添加主键
ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…) # 修改表的同时为该表添加唯一性索引
ADD FOREIGN KEY [<索引名>] (<列名>,…) # 修改表的同时为该表添加外键

2.4 查看索引

索引创建完成后，使用 SHOW INDEX 语句可以查看表中创建的索引，语法格式如下

1

SHOW INDEX FROM <表名> [ FROM <数据库名>]

一个例子如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

mysql> SHOW INDEX FROM tb_stu_info2\G
*************************** 1. row ***************************
        Table: tb_stu_info2
   Non_unique: 0
     Key_name: height
 Seq_in_index: 1
  Column_name: height
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.03 sec)

其中各参数说明如下表

2.5 修改和删除索引

删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

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

删除索引使用 DROP INDEX 语句，语法格式如下

1

DROP INDEX <索引名> ON <表名>

也可以使用 ALTER TABLE 语句，方法是在该语句中添加下面的某个子句

1
2
3

DROP PRIMARY KEY #表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name #表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol # 表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

删除索引的一个例子如下

1
2
3
4
5
6
7
8
9

mysql> DROP INDEX height
    -> ON tb_stu_info;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 或者
mysql> ALTER TABLE tb_stu_info2
    -> DROP INDEX height;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.6 索引的设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。本节将介绍一些索引的设计原则。

1. 选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2. 为经常需要排序、分组和联合操作的字段建立索引：经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3. 为常作为查询条件的字段建立索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

4. 限制索引的数目：索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。

5. 尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。

6. 数据量小的表最好不要使用索引：由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

7. 尽量使用前缀来索引：如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

8. 删除不再使用或者很少使用的索引：表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

总之，选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能只拘泥于上面的准则。应该在学习和工作中不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。