MySQL 视图及索引（实验报告）_视图与索引的创建和管理实验报告-CSDN博客

本文链接：https://blog.csdn.net/2301_79218588/article/details/138226784

一、实验名称： 索引

二、实验日期：2024 年4 月 26 日

三、实验目的：

掌握MySQL索引的创建及使用；

四、实验用的仪器和材料：

硬件：PC电脑一台；

配置：内存，2G及以上硬盘250G及以上

软件环境：操作系统 windows7以上

数据库环境：MySQL5.7或MySQL8.0.20

五、实验步骤和方法

【案例场景】：

图书管理系统数据库中所存放的数据随着时间的推移越来越多，而读者经常使用图书的名称对图书进行查询，发现查询速度越来越慢。此时图书管理系统管理者可以通过对图书表中的图书名称字段创建索引，来提高读者通过图书名称查询书籍的速度。

【实验目的】

理解索引的作用；
掌握创建索引的语句。

【相关知识点】

1、索引的概念及作用。

MySQL访问数据可以通过全表扫描的方式，也可以通过索引的方式。索引是从数据库中获取数据的最高效方式之一。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引实际上就是记录的关键字与其相应地址的对应表。也就是说，数据库中的索引是一个表中所包含的值的列表，其中注明了表中包含各个值的行所在的存储位置。通过索引可大大提高查询速度。在基本表上可以建立一个或多个索引。

只有在select查询语句中当where查询条件或者排序或者查询所涉及到的字段创建了索引，才能使用该索引提高查询速度。如果存在查询提速质疑，可以通过EXPLAIN命令查看优化器选择的执行计划，看是否使用到索引，如果对优化器选择的执行计划不满意，可以使用优化器提供的提示(hint)来控制最终的执行计划，强制使用索引。

MySQL的索引主要包括普通索引、唯一性索引、主键索引、全文索引、复合索引、空间索引等索引。

带索引的表在数据库中会占据更多的空间。经常有插入和删除操作的数据量少的小型表也最好不要创建索引，对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。而且不要对大型字段进行索引，这样占用的空间比较大。

数据表中由于进行插入更新数据等操作，会使索引产生碎片，查询速度降低，因而需要对索引进行维护。

通过MySQL语句创建索引

创建索引其语法格式如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL]

INDEX 索引名 [索引类型] ON 表名 ( 列名 [(长度)] [ASC | DESC])

UNIQUE表示唯一索引，可选； FULLTEXT 表示全文索引，可选；SPATIAL表示空间索引，可选；以上三种都不写，代表创建普通索引。当多个字段经常同时作为查询条件，也可以将这几个字段创建一个复合索引，依次写在括号的列名中。升序ASC也可以省略不写，但降序DESC必须要写。

3、通过MySQL语句删除索引

索引的增多，会增加系统在数据更新时花费在维护索引的时间。这时，应该删除一些不必要的索引。删除索引的语法格式如下：

Drop index < 索引名> on 数据表名

示例：

1、使用MySQL语句创建索引

【例7-1 】为书籍表BookInfo的书名Bookname字段创建普通索引IDX_BOOKNAME，IDX_BOOKNAME根据升序排序。语句如下，操作如下图7-1所示。

create index IDX_BOOKNAME on BookInfo(Bookname asc);

注意：创建索引默认是升序排序，asc可以省略不写，但降序desc不能省略。

4、使用MySQL语句查看表中所有的索引

MySQL中查看表中所有索引的语句语法如下：

show index from table_name [from db_name];

其中，table_name是要查询索引的表名，db_name是数据库名。

【例7-3】查询BookInfo表中创建的索引情况，语句如下，操作如下图7-3所示。

show index from bookinfo;

Show index语句会返回表索引信息，包含以下字段：

（1）Table：表名

（2）Non_unique：索引能不能包括重复值，不能则为0，能则为1。

（3）Key_name：索引名称，如果名字相同则表明是同一个索引，而并不是重复，有可能是复合索引。

（4）Seq_in_index：索引中的列序列号，从1开始。

（5）Column_name：创建索引的列名。

（6）Collation：列以什么方式存储在索引中。在MySQL SHOW INDEX语法中，有值’A’（升序）或NULL（无分类）。

（7）Cardinality：基数的意思，表示索引中唯一值的数目的估计值。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

（8）Sub_part：前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

（9）Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。

（10）Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。

（11）Index_type：索引类型，Mysql目前常用索引类型包括：FULLTEXT，HASH，BTREE，RTREE。

①FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

②HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

③BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口开始，依次遍历节点，获取叶子。这是MySQL里默认和最常用的索引类型。

④RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

Comment，Index_comment：注释的意思。

5、使用explain命令查看执行计划

表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

【例7-4】分别查看查询语句select * from bookinfo和select * from bookinfo where bookname='数据结构'的执行计划。命令如下，操作如下图7-4所示。

图7-4查看执行计划

执行explain命令可以查看语句执行的执行计划，返回包含以下字段：

id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
select_type：查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询。以下介绍主要的查询类型：

①SIMPLE：简单的select查询，查询中不包含子查询或者union语句。

②PRIMARY：位于最外部的查询被标记为primary。

③SUBQUERY：子查询当中第一个select查询。

④DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在临时表中。

⑤UNION：若第二个select语句出现在union语句之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived。

⑥UNION RESULT：union查询的结果集。

⑦DEPENDENT UNION：当出现union查询时第二个或之后的查询，取决于外部查询。

⑧DEPENDENT SUBQUERY：子查询当中第一个select查询，取决于外部的查询。

⑨MATERIALIZED：物化子查询。

table：指表名。
partions：该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
tpye：访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：

System > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

①system：表中只有一行数据或者是空表，这是const类型的一个特例。且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index。

②const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。

③eq_ref：对于每个索引键，表中只有一条记录与之匹配。eq_ref 可用于使用 = 运算符比较的索引列。

④ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。ref_or_null：跟ref类型类似，只是增加了null值的比较。实际用的不多。

⑤index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

⑥unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。

⑦range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in，is null等的查询。

⑧index：索引全表扫描，index与ALL区别为index类型只遍历索引树，因为索引文件通常比数据文件小，所以比全表扫描要快。

⑨ALL：全表扫描，遍历全表以找到匹配的行，性能最差。

possible_keys：显示了MySQL在查找当前表中数据的时候可能使用到的索引，实际意义不大。
Key：实际使用的索引，如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中。
key_len：表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度。可以通过key_len的大小判断评估复合索引使用了哪些部分。
ref：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。
rows：这是mysql估算的需要扫描的行数（不是精确值）。这个值非常直观显示 SQL的效率好坏, 原则上rows越少越好。
filtered：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
Extra：EXPLAIN中的很多额外的信息会在 Extra 字段显示,常见的有以下几种内容:

①Using index：仅查询索引树就可以获取到所需要的数据行，而不需要读取表中实际的数据行。通常适用于select字段就是查询使用索引的一部分，即使用了覆盖索引。

②Using index condition：显示采用了Index Condition Pushdown(ICP)特性通过索引去表中获取数据。如果开启ICP特性，部分where条件部分可以下推到存储引擎通过索引进行过滤，ICP可以减少存储引擎访问基表的次数；如果没有开启ICP特性，则存储引擎根据索引需要直接访问基表获取数据并返回给server层进行where条件的过滤。

③Using where：显示MySQL通过索引条件定位之后还需要返回表中获得所需要的数据。

④Impossible WHERE：where子句的条件永远都不可能为真。

⑤Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)：在表连接过程当中，将先前表的部分数据读取到join buffer缓冲区中，然后从缓冲区中读取数据与当前表进行连接。主要有两种算法：Block Nested Loop和Batched Key Access。

⑥Using MRR：读取数据采用多范围读(Multi-Range Read)的优化策略。

⑦Using temporary：MySQL需要创建临时表来存放查询结果集。通常发生在有GROUP BY或ORDER BY子句的语句当中。

⑧Using filesort：MySQL需要对获取的数据进行额外的一次排序操作，无法通过索引的排序完成。通常发生在有ORDER BY子句的语句当中。

如果对优化器选择的执行计划不满意，可以使用优化器提供的提示(hint)来控制最终的执行计划，强制使用索引。

6、删除索引

使用MySQL语句删除索引

具体语法如下：

Drop index index_name on table_name;

其中，index_name为需要删除的索引名称，table_name为索引所在的表，其余均为关键字。

【例7-3】删除读者表Reader中的索引IDX_RNAME。

drop index IDX_RNAME on Reader;

【最佳实践】

索引能够极大地提高数据库性能，但并不是越多越好，因为索引需要占据存储空间且需要维护。数据量太小的表不需要创建索引，数据的增删改操作非常频繁的表不宜创建太多索引。需要根据需求权衡利弊，具体情况具体分析。

练习：

实验3创建的数据库cs，及在cs数据库中根据下图创建的超市管理系统中的四张表。实验6已分别向四张表插入数据。

（1）ygxx（员工信息表）

（2）bmxx（部门信息表）

（3）spxx（商品信息表）

（4）supplier（供应商信息表）

创建视图v_spxx，主要查询商品信息中所属类别为“文具”的所有商品信息。
查询视图v_spxx的所有数据。
在spxx（商品信息）表中spmc字段创建名为index_spmc的索引。
在ygxx（员工信息）表中的zw（职务）和qxjb（权限级别）的字段创建一个名为index_zq的复合索引。
在ygxx（员工信息）表中的name（姓名）创建名为index_name的唯一性索引。
查看ygxx（员工信息）表中的索引情况。
查看spxx（商品信息）表中的索引情况。
查询ygxx（员工信息）表中所有的数据，并查看其执行计划，说明查询操作的访问类型且是否使用索引。
查询ygxx（员工信息）表中姓“刘”的员工的所有数据，并查看其执行计划，说明查询操作的访问类型且是否使用索引。
查询ygxx（员工信息）表zw（职务）为主管的员工姓名，并查看其执行计划，说明查询操作的访问类型且是否使用索引。
查询员工信息表中姓名姓刘的，且所属部门不在业务部门的员工编号、员工姓名及所属部门编号，并查看其执行计划，说明查询操作的访问类型且是否使用索引。
删除spxx（商品信息）表中的index_spmc索引。

六、数据记录和计算：

指从实验中测到的数据以及计算结果。

创建视图v_spxx，主要查询商品信息中所属类别为“文具”的所有商品信息：

create view v_spxx as select * from spxx where sslb = '文具';

查询视图v_spxx的所有数据：

select * from v_spxx;

在spxx（商品信息）表中spmc字段创建名为index_spmc的索引：

create index index_spmc on spxx(spmc);

在ygxx（员工信息）表中的zw（职务）和qxjb（权限级别）的字段创建一个名为index_zq的复合索引：

create index index_zq on ygxx(zw,qxjb);

在ygxx（员工信息）表中的name（姓名）创建名为index_name的唯一性索引：

create unique index index_name on ygxx(name);

查看ygxx（员工信息）表中的索引情况：

show index from ygxx;

查看spxx（商品信息）表中的索引情况：

show index from spxx;

查询ygxx（员工信息）表中所有的数据，并查看其执行计划，说明查询操作的访问类型且是否使用索引：

explain select * from ygxx;

查询ygxx（员工信息）表中姓“刘”的员工的所有数据，并查看其执行计划，说明查询操作的访问类型且是否使用索引：

explain select * from ygxx where name like '刘%';

查询ygxx（员工信息）表zw（职务）为主管的员工姓名，并查看其执行计划，说明查询操作的访问类型且是否使用索引：

explain select name from ygxx where zw = '职务';

查询员工信息表中姓名姓刘的，且所属部门不在业务部门的员工编号、员工姓名及所属部门编号，并查看其执行计划，说明查询操作的访问类型且是否使用索引：

explain select ygbh,name,ssbmbh from ygxx where name like '刘%' and ssbmbh != (select bmbh from bmxx where bmmc = '业务');

删除spxx（商品信息）表中的index_spmc索引：

drop index index_spmc on spxx;

查询商品信息中的索引：

七、实验结果或结论：即根据实验过程中所见到的现象和测得的数据，作出结论。

索引在数据库中起到了加快数据检索速度的作用。它是一种数据结构，可以帮助数据库系统快速定位到需要查询的数据，减少了数据库的扫描操作，高了查询效率。创建索引的语可以使用SQL语言中的CREATE INDEX语句来实现。CREATE INDEX语句的基本语法如“CREATE INDEX index_name ON table_name (column1, column2, ...);”其中，index_name是索引的名称，table_name是要创建索引的表名，column1, column2, ...是要创建索引的列名。例如，如果要在名为students的表中创建一个名为idx_name的索引，索引的列为name和age，可以使用以下语句：“CREATE INDEX idx_name ON students (name, age);”这样就创建了一个名为idx_name的索引，可以加快对students表中name和age列的查询速度。

八、备注或说明：可写上实验成功或失败的原因，实验后的心得体会、建议等。

索引在数据库中起到了重要的作用，它可以提高数据库的查询效率。索引是一种数据结构，它可以帮助数据库快速定位到存储在表中的数据。通过创建索引，数据库可以避免全表扫描，而是直接根据索引定位到需要的数据，从而加快查询速度.

索引可以带来以下几个好处：提高查询速度：通过使用索引，数据库可以快速定位到满足查询条件的数据，而不需要逐行扫描整个表。加速排序：如果查询需要按照某个字段进行排序，索引可以帮助数据库快速排序，减少排序时间。加速连接操作：当进行表之间的连接操作时，索引可以加快连接的速度，提高查询效率。约束数据完整性：索引可以用于创建唯一约束或主键约束，确保表中的数据唯一性和完整性。索引也有一些缺点：占用存储空间：索引需要占用额外的存储空间来存储索引数据结构。增删改操作变慢：当对表进行增删改操作时，索引也需要进行相应的更新，这可能会导致操作变慢。维护成本高：当表中的数据发生变化时，索引也需要进行维护，这可能会增加数据库的负担。

索引在数据库中起到了提高查询效率和数据完整性的作用，但需要根据具体情况来选择合适的索引策略，以平衡查询性能和维护成本。