二级MySQL数据库程序设计（六）

港控/mmm° 2023-07-09 15:25 13阅读 0赞

**本博客为《全国计算机等级考试二级MySQL数据库程序设计教程》读书笔记，请勿转载用于其他商业用途。**

**课程目录**  
[第1章 数据库的基本概念与设计方法][1_]  
[第2章 MySQL简介][2_ MySQL]  
[第3章 数据库和表][3_]  
[第4章 表数据的基本操作][4_]  
[第5章 数据库的查询][5_]  
[第6章 索引][6_]  
[第7章 视图][7_]  
第8章 数据完整性约束与表维护语句  
第9章 触发器  
第10章 事件  
第11章 存储过程与存储函数  
第12章 访问控制与安全管理  
第13章 备份与恢复  
第14章 PHP和MySQL数据库编程  
第15章 开发实例

### 文章目录 ###

*  本章学习流程图
 *  本章学习大纲
 *  6.1 索引简介
 *  6.2 索引的存储于分类
 *  6.3 创建索引
 *  6.4 查看索引
 *  6.5 删除索引
 *  6.6 索引进阶

# 本章学习流程图 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5_size_16_color_FFFFFF_t_70]

# 本章学习大纲 #

索引是MySQL中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。本章主要介绍索引的基本知识，以及在MySQL中通过使用SQL语句创建、查看和删除索引的方法。

# 6.1 索引简介 #

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

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

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

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

**注意：访问数据库表的两种方式，顺序访问和索引访问的区别。**

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

# 6.2 索引的存储于分类 #

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

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

*  叶子结点
 *  分支节点
 *  根节点

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

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

（2）哈希索引

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

哈希索引（HASH）也称为散列索引或HASH索引。MySQL目前仅有MEMORY存储引擎和HEAP存储引擎支持这类索引。其中MEMORY存储引擎可以支持B-树索引和HASH索引，且将HASH索引当成默认索引。HASH索引不是基于树形的数据结构查找数据，而是根据索引对应列的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但是也存在一些缺点：

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

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

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

（2）唯一性索引  
不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据创建索引的时候就应该用关键字`UNIQUE`把它定义为一个唯一索引。创建唯一索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。

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

（4）空间索引  
主要用于地理空间数据类型GEOMETRY。

（5）全文索引  
只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。

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

（1）单列索引  
索引只包含原表的一个列。

（2）组合索引  
也称为符合索引或多列索引，相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。

注意，一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询进度。

# 6.3 创建索引 #

MySQL提供了3种创建索引的方法：

❶ 使用CREATE INDEX语句  
可以使用专门用于创建索引的CREATE INDEX语句在一个已有的表上创建索引，但该语句不能创建主键。

**语法格式：**

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

**语法说明：**

*  <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称时唯一的。
 *  <表名>：指定要创建索引的表名。
 *  <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在JOIN子句和WHERE子句里经常出现的列作为索引。  
    ①<列名>：指定要创建索引的列名。通常可以考虑将查询语句中的JOIN子句和WHERE子句里经常出现的列作为索引。  
    ②<长度>：可选项。指定使用列前的length个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小。在某些情况下，只能对列的前缀进行索引。  
    ③ASC|DESC：可选项。

**【例6-1】查询数据库 my\_test 中表 students 中，根据学生姓名前三个字符，采用默认的索引类型，创建一个升序索引 index\_student。**

mysql> create index index_student
        -> on students(student_name(3) asc);
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    33
    Current database: my_test
    
    Query OK, 0 rows affected (2.44 sec)
    Records: 0  Duplicates: 0  Warnings: 0

语句执行成功后，采用SHOW INDEX命令显示已经创建的索引。

执行完后，可以看到已经建立了基于student\_name的索引 index\_student。（在Key\_name列）

**【例6-2】查询数据库 my\_test 中表 students 中，根据 student\_name 和 student\_id 采用 BTREE 的索引类型，创建一个复合索引 index\_stud。**

❷ 使用CREATE TABLE语句  
索引页可以在创建表的同时创建。在`CREATE TABLE`语句语法添加以下语句：

*  **语法格式：**

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,...)

在`CREATE TABLE`语句中添加词句，表示在创建新表的同时创建该表的**主键**。

*  **语法格式：**

KEY | INDEX [索引名] [索引类型] (<列名>,...)

在`CREATE TABLE`语句中添加词句，表示在创建新表的同时创建该表的**索引**。

*  **语法格式：**

UNIQUE [INDEX | KEY]
    [<索引名>][<索引类型>](<列名>,...)

在`CREATE TABLE`语句中添加词句，表示在创建新表的同时创建该表的**唯一性索引**。

*  **语法格式：**

FOREIGN KEY <索引名> <列名>

在`CREATE TABLE`语句中添加词句，表示在创建新表的同时创建该表的**外键**。

**【例6-3】在已有的数据库 my\_test 上新建一个课程信息表包含下列字段：课程号、课程名称、上课教室和任课老师姓名。要求在创建该表的同时，将课程号作为主键，并且给课程名称创建索引。**

mysql> use my_test;
    Database changed
    mysql> create table course
        -> (
        -> course_id int not null,
        -> course_name char(50) not null,
        -> course_place char(50) not null,
        -> course_teacher char(50) not null,
        -> primary key(course_id),              /*主键*/
        -> index index_course(course_name)      /*课程名创建索引*/
        -> );
    Query OK, 0 rows affected (1.33 sec)

**注意：MySQL可以在一个表上同时创建多索引，使用PIRMARY KEY的列必须是一个具有NOT NULL属性的列。**

❸ 使用ALTER TABLE语句  
`CREATE INDEX`语句可以在一个已有的表上创建索引，`ALTER TABLE`语句也可以在一个已有表上创建索引。在使用`ALTER TABLE`修改表的同时，可以向已有的表添加索引，具体做法是在`ALTER TABLE`语句中添加以下语法成分的某一项或几项。

*  **语法格式：**

ADD INDEX [<索引名>] [<索引类型>] (<列名>,...)

在`ALTER TABLE`语句中添加此语法成分，表示在修改表的同时为该表**添加索引**。

*  **语法格式：**

ADD PRIMARY KEY [索引类型] (<列名>,...)

在`ALTER TABLE`语句中添加此语法成分，表示在修改表的同时为该表**添加主键**。

*  **语法格式：**

ADD UNIQUE [INDEX | KEY] [<索引名>][<索引类型>](<列名>,...)

在`ALTER TABLE`语句中添加此语法成分，表示在修改表的同时为该表**添加唯一性索引**。

*  **语法格式：**

ADD FOREIGN KEY <索引名> <列名>

在`ALTER TABLE`语句中添加此语法成分，表示在修改表的同时为该表**添加外键**。

**【例6-4】在已有的数据库 my\_test 表 course 中，为courese\_place字段添加一个非唯一的索引，取名为 index\_place。**

mysql> alter table course
        -> add index index_place(course_place);
    Query OK, 0 rows affected (1.17 sec)
    Records: 0  Duplicates: 0  Warnings: 0

# 6.4 查看索引 #

在MySQL中，如果要查看已经创建的索引情况，可以使用`SHOW INDEX`语句查看表中创建的索引。

**语法格式：**

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

**语法说明：**

*  <表名>：要显示索引的表。
 *  <数据库名>：要显示的表所在的数据库。

**【例6-5】显示数据库 my\_test 中的表 course 的索引情况。**

该语句会返回一张表，包含Table等几个字段，说明如下：

*  Table：表的名称
 *  Non\_unique：用于显示，该索引是否不是唯一索引。如果不是唯一索引，则该列的显示值为1；如果是唯一索引，该列的显示值为0。
 *  Key\_name：索引的名称。
 *  Seq\_in\_index：索引中的序列号，从1开始技术。
 *  Column\_name：列名称。
 *  Collation：显示列以何种顺序存储在索引中。在MySQL中，升序显示值’A’（升序），如果显示为NULL则表示无分类。
 *  Cardinality：显示索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即时对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
 *  Sub\_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
 *  Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。
 *  Index\_type：显示索引使用的类型和方法（BTREE, FULLTEXT, HASH, RTREE）。
 *  Comment：显示评注。

# 6.5 删除索引 #

当一个索引不在需要时，可以使用`DROP INDEX`语句或者`ALTER TABLE`语句来对索引进行删除。

❶ 使用DROP INDEX语句

**语法格式：**

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

**语法说明：**

*  <索引名>：要删除的索引名。
 *  <表名>：指定该索引所在的表名。

**【例6-6】删除例6-4中创建的索引 index\_place。**

mysql> drop index index_place on course;
    Query OK, 0 rows affected (0.43 sec)
    Records: 0  Duplicates: 0  Warnings: 0

❷ 使用ALTER TABLE语句  
根据`ALTER TABLE`语句的语法可知，该语句也可以用于删除索引。具体使用方法是将ALTER TABLE语句的语法中部分指定为以下子句中的某一项。

*  DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
 *  DROP INDEX index\_name：表示删除名为index\_name的索引。
 *  DROP FOREIGN KEY fk\_symbol：表示删除外键。

**【例6-6】删除数据库 my\_test 的表 students 的索引 index\_students。**

mysql> alter table students
        -> drop index index_students;

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

# 6.6 索引进阶 #

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

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

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

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

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

*  对于那些在查询中很少使用或参考的列不应该创建索引。一是不能提高查询速度，二是降低了系统维护速度，增大了空间需求。
 *  对于那些之只有很少数据值的列也不应该创建索引。
 *  对于那些定义为TEXT, IMAGE和BIT数据类型的列不应该创建索引。因为这些列的数据量要么相当大，要么取值很少。
 *  当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能时相互矛盾的，创建索引会提高检索性能，降低修改性能，减少索引会提高修改性能，降低检索性能。

[1_]: https://chrishany.blog.csdn.net/article/details/104432053
[2_ MySQL]: https://chrishany.blog.csdn.net/article/details/104435219
[3_]: https://chrishany.blog.csdn.net/article/details/104454873
[4_]: https://chrishany.blog.csdn.net/article/details/104473980
[5_]: https://chrishany.blog.csdn.net/article/details/104503929
[6_]: https://chrishany.blog.csdn.net/article/details/104571638
[7_]: https://chrishany.blog.csdn.net/article/details/104628640
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200229115302643.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzQyNTc0MDY5,size_16,color_FFFFFF,t_70