MySQL数据库技术与项目应用教程（微课版）（AI助学）（第3版）课件 项目5-9 优化查询网上商城系统数据 -MES存货管理系统实现

模块概览优化检索网上商城系统数据MySQL数据库技术与项目应用05引言/FOREWORD在数据库的开发和应用中，如何方便快捷地从数据库中查询所需数据是至关重要的。然而默认情况下，数据的查询是根据搜索条件进行全表扫描，并将符合条件的记录添加到结果集。在数以千万计的数据海洋中，对全表的扫描数据优化需要花费较长的时间。若不进行查询性能的优化，必将影响应用程序的性能及用户体验，严重时将会制约应用系统的正常运行。MySQL提供的视图和索引等机制，有效地提高数据访问的效率。目录/Contents0103使用视图优化查询操作编写高效的数据查询02使用索引优化查询性能任务1使用视图优化查询操作使用SQL语句查询数据时，查询结果直接输出到客户端而不被保存。若需要多次查询相同数据时，就可以将查询的定义封装成视图，以简化查询操作。视图是虚表，存储的内容是SQL语句，其关联的数据在视图被使用时动态生成，数据随着基本表的变化而变化。视图就像一个窗口，用户只需要关心视图窗口提供的数据即可。本任务主要如何利用视图查询数据，使数据库开发人员能够有效、灵活地管理多个数据表、简化数据操作、提高数据的安全性。任务场景数据库系统的三级模式结构模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图，泛指图中的概念模式。外模式也称子模式（subschema）或用户模式，它是数据库用固话（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。内模式也称存储模式（storageschema），一个数据库只有一个内模式。它是数据物理存储和存储方式的描述，是数据在数据库内部的组织方式。视图简介视图是从数据库中一个或多个表（或视图）中导出来的表，其关联的数据由SQL语句定义。视图与表一样由行和列组成，但它不存储实际的数据内容，当使用视图查询数据时，数据库系统会从视图引用的表中提取相应的数据。视图的优点：简单性安全性逻辑数据独立性创建视图使用Navicat图形工具创建视图【例5.1】使用Navicat图形工具，创建名为view_users_orders的视图，用于查询会员的订单及订单详情。列出用户id，用户名，订单编号，下单日期，订单金额，商品id，商品名称，购买数量和单价。创建视图使用SQL语句创建视图

ORREPLACE：当指定ORREPLACE子句时，若视图存在则修改定义，否则创建新视图。

列名列表：视图自定义的列名，该列表中名称必须与视图体中SELECT语句查询的结果列一一对应，若使用与源表或视图中相同的列名，则可以省略列名列表。

select语句：视图定义的SELECT语句。

WITH[CASCADED|LOCAL]CHECKOPTION：可选参数，表示更新视图时要保证在该视图的权限范围之内。其中CASCADED是默认值，表示更新视图时要满足所有相关视图和表的条件，LOCAL表示更新视图时满足该视图本身定义的条件。CREATE[ORREPLACE]VIEW[数据库名.]视图名[(列名列表)]ASselect语句[WITH[CASCADED|LOCAL]CHECKOPTION]创建视图【例5.2】创建名为view_cart的视图，用来显示购物车信息，列出用户id、用户名、商品id、商品名称、购买数量以及商品价格。CREATEVIEWview_cart(uid,uname,gid,gname,price,num)ASSELECTu.uid,u.uname,g.gid,g.gname,g.gprice,umFROMusersuJOINcartcJOINgoodsgONu.uid=c.uidANDc.gid=g.gid;查看视图查看视图的方法与查看表的方法基本相似。在查看视图之前确定用户是否有查询视图的权限（可以查询系统数据库mysql中user表的show_view_priv列的值），默认值为“Y”，表示允许。查看数据库中的所有视图【例5.3】查看onlinedb数据库中所有的视图信息。SELECT*FROMinformation_schema.viewsWHEREtable_schema='onlinedb';查看视图使用SHOWTABLESTATUS语句查看视图。【例5.4】使用SHOWTABLESTATUS语句查看名为view_cart的视图。SHOWTABLESTATUSLIKE'view_cart'\G;SHOWTABLESTATUSLIKE'视图名';查看视图使用DESCRIBE语句查看视图。【例5.5】使用DESCRIBE语句查看名为view_cart的视图。DESCRIBEview_cart;DESCRIBE视图名通过视图查询数据【例5.6】查询用户段湘林购物车中的商品id,商品名称，价格和购买数量。【例5.7】查询用户蔡静在2021年全年的总销费金额，列名为total_2021。SELECTgid,gname,price,numFROMview_cartWHEREuname='段湘林';SELECTSUM(gprice*inum)AStotal_2021FROMview_users_ordersWHEREuname='蔡静'ANDyear(ordertime)=2021;修改视图CREATEORREPLACEVIEW语句修改视图。MySQL中，CREATEORREPLACEVIEW语句的使用非常灵活，当要操作的视图不存在时，可以新建视图；当视图已存在时，可以实现修改视图。【例5.8】修改名为view_cart的视图，在原有查询的基础上增加用户的邮箱。CREATEORREPLACEVIEWview_cart(uid,uname,uemail,gid,gname,price,num)ASSELECTu.uid,u.uname,u.uemail,g.gid,g.gname,g.gprice,umFROMusersuJOINcartcJOINgoodsgONu.uid=c.uidANDc.gid=g.gid;修改视图【例5.9】创建名为view_users的视图，列出用户id、登录名、用户名、密码和性别。CREATEORREPLACEVIEWview_usersASSELECTuid,ulogin,uname,upwd,ugenderFROMusers;修改视图ALTER语句修改视图【例5.10】使用ALTERVIEW语句实现【例5.8】。ALTER[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]VIEW视图名（column_list）ASSELECT_statement[WITH[CASCADED|LOCAL]CHECKOPTION]ALTERVIEWview_cart(uid,uname,uemail,gid,gname,price,num)ASSELECTu.uid,u.uname,u.uemail,g.gid,g.gname,g.gprice,umFROMusersuJOINcartcJOINgoodsgONu.uid=c.uidANDc.gid=g.gid;删除视图当不再需要视图时，可使用图形工具和SQL语句都可以删除视图。图形工具删除视图只需要在对象浏览器窗口右击待删除视图名，在弹出的快捷菜单中选择“删除视图”选项即可。SQL语句使用DROPVIEW语句删除视图。删除视图时，只会删除视图的定义，并不会删除视图关联的数据。【例5.11】删除视图view_users_orders。DROPVIEW[IFEXISTS]视图名DROPVIEWview_users_orders;可更新视图在MySQL中，视图不仅可以查询数据，还可以更新数据。由于视图是一张虚拟表，因此可以使用INSERT或UPDATE语句通过更新视图插入或更新相关表中的数据，还可以使用DELETE语句通过更新视图删除相关表中的记录。通过视图修改数据UPDATE视图名SET列名1=值1,列名2=值2,…,列名n=值nWHERE条件表达式可更新视图【例5.12】通过视图view_cart修改会员李小莉的邮箱为'lixiaoli@'。UPDATEview_cartSETuemail='lixiaoli@'WHEREuname='李小莉';可更新视图若视图的定义包含下情况中的任何一种，则该视图就不可更新。包含聚合函数。包含DISTINCT、UNION、ORDERBY、GROUPBY和HAVING等关键字或子句。包含子查询。由不可更新的视图导出的视图。视图对应的数据表上存在没有默认值且不为空的列，而该列没有包含在视图里。学习提示：虽然可以通过更新视图操作相关表的数据，但是限制较多。实际情况下，最好将视图仅作为查询数据的虚表，而不要通过视图更新数据。可更新视图通过视图向数据表插入数据【例5.13】通过视图view_users，向数据表users中插入一条记录。INSERT[INTO]视图名[(列名列表)]VALUES(值列表1)[,(值列表2),…,(值列表n)]INSERTINTOview_users(ulogin,uname,upwd,ugender)VALUES(,'周鹏','123','男');可更新视图通过视图删除数据表中的数据可以使用DELETE语句更新视图删除数据表中的数据。【例5.14】通过视图view_users，删除数据表users用户名为“周鹏”的记录。DELETEFROM视图名[WHERE条件表达式]

DELETEFROMview_usersWHEREuname='周鹏'任务场景任务2使用索引优化查询性能

索引是MySQL中的重要对象，是数据库实现数据快速定位和提高数据访问效率的关键技术。在MySQL中，所有数据类型都可以被索引。本任务将介绍与索引相关的内容，包括索引的定义和特点、索引的分类、索引的设计原则以及如何创建和管理索引。索引的定义索引（也叫做“键（key）”）是存储引擎用于快速查找记录的一种数据结构，用来快速查询数据库表中的特定记录。MySQL中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。【例5.15】查询类别编号为3的商品编号和名称。SELECTgcode,gnameFROMgoodsWHEREcid=3;索引的定义索引的优点：可以提高查询数据的速度；通过创建唯一索引，可以保证数据库表中每一行数据的唯一性；在实现数据的参考完整性方面，可以加速表和表之间的连接；在使用分组和排序子句进行数据查询时，可以减少分组和排序的时间；索引的缺点：创建和维护索引需要耗费时间，并且随着数据量的增加所耗费的时间也会增加；索引需要占用磁盘空间，；当对表中的数据进行增加、删除和修改操作时，索引也需要动态的维护，这样就降低了数据的维护速度；索引的分类索引作为一种特殊的数据结构，由MySQL的存储引擎实现，不同存储引擎支持的索引类型不同。存储引擎InnoDB的索引实现采用BTREE（平衡二叉树，B树）。索引的分类主要考虑两个维度：主键索引：由PRIMARYKEY定义的一种特殊的唯一性索引，用于根据主键自身的唯一性标识每条记录，防止添加主键索引的字段值重复或为NULL。辅助索引：主键索引的性能相对来说最好，但查询优化更多的是对辅助索引建立和维护。根据创建索引的字段个数，可以将它们分为单列索引和复合索引。辅助索引索引名称说

明普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值唯一索引唯一索引，索引列的值必须唯一，但允许有空值。全文索引全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建空间索引定义在空间数据类型上的索引类型，且索引字段不能为空创建和查看索引使用Navicat图形工具创建索引【例5.16】在Navicat中，为orders表的ocode列创建名为ix_ocode的普通索引。创建索引使用CREATE

TABLE语句创建索引CREATETABLE表名（字段定义1,

字段定义2,……

字段定义n,[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY

索引名(字段名[(长度)][ASC|DESC])[VISIBLE|INVISIBLE]）;

索引名：索引的名称，在表中索引名称必须唯一。

字段名：表示索引创建的列名，可以是多列；长度表示在字段左边多少个字符上创建索引。

UNIQUE表示唯一索引；FULLTEXT表示全文索引；SPATIAL：表示空间索引；

INDEX和KEY：表示索引关键字，只选其一即可；

ASC|DESC：分别表示升序排列和降序排列。

VISIBLE|INVISIBLE：MySQL8.0新增功能，标识索引的可见性。VISIBLE为可见，缺省该项时的默认值。当创建索引时设置为INVISIBLE时，则表示该索引为隐形索引，在数据查询时，优化器会忽略隐形索引。创建索引【例5.17】创建goods_bak表，并在gcode列上创建名为ix_gcode的唯一索引。CREATETABLEgoods_bak(gidintNOTNULLPRIMARYKEYAUTO_INCREMENT,cidint,gcodevarchar(50)NOTNULL,gnamevarchar(100)NOTNULL,gpricedecimal(10,2),gsale_qtyint,ginfovarchar(20000),#商品详情

UNIQUEINDEXix_gcode(gcode));创建索引使用ALTER

TABLE语句创建索引【例5.18】在users表的ulogin、uname和uemail三列上创建名为ix_users的复合索引。ALTERTABLE表名ADD[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]索引名(字段名[(长度)][ASC|DESC]))ALTERTABLEusersADDINDEXix_users(ulogin,uname,uemail);创建索引使用CREATEINDEX语句创建索引【例5.19】在goods_bak表的gname列上创建名为ix_gname的前缀索引，取gname列的前10个字符。【例5.20】在goods_bak表的ginfo列上创建名为ix_ft_ginfo的全文索引。CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名ON表名(字段名[(长度)][ASC|DESC])CREATEINDEXix_gnameONgoods_bak(gname(10));CREATEFULLTEXTINDEXix_ft_ginfoONgoods_bak(ginfo);查看索引信息索引创建好后，可以通过SHOWINDEXFROM/SHOWKEYSFROM语句，查看指定表的索引信息。【例5.21】使用SHOWINDEXFROM语句，查看goods_bak表的索引信息。SHOW{INDEX|KEYS}FROM表名;SHOWINDEXFROMgoods_bak;学习提示：使用SHOWCREATETABLE语句查看表定义时也可查看到索引信息。维护索引创建索引之后，对数据的添加、修改、删除等操作会使得索引页出现碎片，影响数据查询性能。为了提高查询效率，可以对索引进行相应维护，其中包括删除和修改索引。删除索引可以使用ALTERTABLE或者DROPINDEX语句删除索引使用ALTERTABLE语句删除索引【例5.22】删除goods_bak表上名为ix_ft_ginfo的索引。ALTERTABLE表名DROPINDEX索引名;ALTERTABLEgoods_bakDROPINDEXix_ft_ginfo;维护索引使用DROPINDEX语句删除索引【例5.23】删除goods_bak表中名为ix_gname的索引。DROPINDEX索引名ON表名DROPINDEXix_gnameONgoods_bak;学习提示：删除表中的列时，会删除与该列相关的索引信息。若待删除的列为索引的组成部分，则该列也会从索引中删除。若组成索引的所有列都被删除，则整个索引将被删除。维护索引修改索引随着数据的增册改会造成索引碎片的产生，当需要验证索引的有效性时，可修改索引为隐形索引。MySQL修改索引使用ALTERTABLE语句。【例5.24】设置goods_bak表中ix_gcode索引为隐形索引。ALTERTABLE表名ALTERINDEX索引名[VISIBLE|INVISIBLE];ALTERTABLEgoods_bakALTERINDEXix_gcodeINVISIBLE;索引的设计原则高效的索引有利于快速查找数据，而设计不合理的索引可能会对数据库和应用程序的性能造成障碍。因此，创建索引时应尽量考虑符合以下原则，便于提升索引的使用效率。（1）不要建立过多的索引。（2）为用于搜索、排序或分组的列创建索引，而对于用作输出显示的列则不宜创建索引。（3）使用唯一索引，并考虑数据列的基数。数据列的基数是指它所容纳的所有非重复值的个数。（4）使用短索引，应尽量选用长度较短的数据类型。（5）在设置前缀索引时，字段长度的设定需要通过一定的计算和测试以选取最合适的字符长度范围。（6）利用最左前缀。任务场景任务3编写高效的数据查询数据查询是应用系统中最频繁的操作，当要访问的数据量很大时，查询不可避免地需要筛选大量的数据，造成查询性能低下。要提高数据查询的性能，需要对查询语句进行必要的优化。本任务将从优化数据访问、分析SQL的执行计划、子查询优化、Limit查询优化及优化GROUPBY子句等方面分析查询优化的策略。优化数据访问影响查询性能的主要原因？向数据库请求不需要的数据（1）查询不需要的记录（2）多表关联时返回全部列（3）总是取出全部列查询的开销（1）响应时间（2）扫描的行数和返回的行数优化数据访问查询会员蔡静购买的所有商品信息SELECT*FROMusersjoincartUSING(uid)

joingoodsUSING(gid)WHEREuname=‘蔡静';SELECTgoods.*FROMusersjoincartUSING(uid)

joingoodsUSING(gid)WHEREuname=‘蔡静';SQL的执行计划要编写高效的查询语句，需要了解查询语句执行情况，找出查询语句执行的瓶颈，从而优化查询。EXPLAIN|DESCRIBE|DESC[ANALYZE]SELECT语句;学习提示：执行该语句，可以分析EXPLAIN后SELECT语句的执行情况，并且能够分析出所查询表的一些特征。

EXPLAIN|DESCRIBE|DESC：任选其一可以分析SELECT语句的执行情况，并且能够分析出所查询表的相关特征。DESCRIBE多用于查看表结构，这里建议使用EXPLAIN。

ANALYZE：该关键字为MySQL8.0新增。向用户详细显示查询语句执行过程中，查询的具体时间花费及原因。SQL的执行计划【例5.25】使用EXPLAIN语句分析查询users_bg表的执行计划。EXPLAINSELECT*FROMusers_bgWHEREcard='HS395964JA39';序号字段名数据类型标识主键允许空说明1idint是是否id2cardchar(25)

否登录号3genderchar(1)

否性别4ageint

是年龄分析SQL的执行计划输出结果中各列具体说明如下。id：用于标识执行计划中查询的序号，从1开始编号。select_type：显示查询的类型table：显示查询访问的表，可以是表的名称或是表的别名。type：显示查询的关联类型，有无使用索引，也可以说是MySQL决定如何查找表中的行。possible_keys：指搜索记录时可能使用哪个索引。若值为NULL，则没有相关的索引。key：查询优化器从possible_keys中选择使用的索引。如果没有可选择的索引，值为NULL。key_len：表示MySQL选择的索引字段按字节计算的长度。ref：表示使用哪个列或常数与key记录的索引一起来查询记录。rows：查询优化器通过统计信息估算出的需要读取的行数。它不是MySQL认为最终要从表里取出的行数，而是必须读取行的平均数。filtered：估算表中符合某个条件的记录数的百分比。Extra：表示MySQL在处理查询时的额外信息。分析SQL的执行计划查询分析器的查询类型类型名说明SIMPLE表示简单查询，不包括子查询和UNION查询PRIMARY表示主查询，或者是最外层的查询语句SUBQUERY表示包含在SELECT列表中的子查询中的SELECTDERIVED表示包含在FROM子句的子查询中的SELECT，即派生表UNION在联合查询的第2个或后面的SELECTUNIONRESULT用来从UNION的匿名临时表检索结果的SELECTDEPENDENTSUBQUERY表示取决于外面的子查询中的第1个SELECTDEPENDENTUNION表示取决于外面的连接查询中的第2个或后面的SELECT分析SQL的执行计划查询分析器的关联类型关联类型说明ALL全表扫描，也就是从头到尾扫描整张表index同ALL，只是MySQL扫描的是索引表，若在Extra列中显示usingindex说明使用的是覆盖索引，只扫描索引数据range有限制地索引扫描。开始于索引中的某一点，返回匹配这个值范围的行ref索引查找，使用非唯一性索引或者唯一性索引的非唯一性前缀。索引值需跟某一个参考值进行比较index_subquery表示可以使用index_subquery替换子查询具有非唯一索引的IN子查询unique_subquery表示可以使用unique_subquery（即索引查找函数）替换IN子查询的表index_merge表示使用了索引合并优化的表ref_or_null同ref，但是添加了MySQL可以专门搜索包含NULL值的行eq_ref索引查找，使用主键或唯一性索引查找时使用。索引值需跟某一个参考值进行比较const表示最多只有一个匹配行的数据表，它将在查询开始时被读取，并在余下的查询优化中作为常量对待。const表查询速度很快，因为它们只读取一次system是const联接类型的一个特例。表仅有一行满足条件分析SQL的执行计划Extra的取值值说明Usingindex表示使用覆盖索引，以避免访问表Usingwhere表示MySQL服务器将在存储引擎检索行后再进行过滤。不是所有带where子句的查询都显示该值。通常表示该查询可受益于不同的索引Usingtemporary表示MySQL对查询结果排序时会用到一个临时表Usingfilesort表示MySQL会对使用一个外部索引排序，而不是按索引次序从表里读取行Rangecheckedforeachrecord（indexmap:N）表示没有好用的索引。N值显示在possible_keys列中索引的位图分析SQL的执行计划【例5.26】使用EXPLAIN语句中ANALYZE关键字分析执行时间。EXPLAINANALYZESELECT*FROMusers_bgWHEREcard='HS395964JA39';查询执行的精确时间在优化查询操作时，MySQL提供的profile功能会记录下每次查询需要的系统资源和精确执行时间。【例5.27】查看【例5.26】查询执行的精确时间。执行结果如下。#开启profile功能SETprofiling=1;#执行查询语句SELECT*FROMusers_bgWHEREcard='HS395964JA39';#查看语句执行的精确时间SHOWprofiles\G;mysql>SHOWprofiles\G;***************************1.row***************************Query_ID:1Duration:0.43807350Query:SELECT*FROMusers_bgWHEREcard='HS395964JA39'1rowinset,1warning(0.00sec)添加索引优化查询【例5.28】为表users_bg中card列建立索引，并再次查看执行计划和执行时间。执行结果如下。#建立索引CREATEINDEXix_cardONusers_bg(card);#查看执行计划EXPLAINSELECT*FROMusers_bgWHEREcard='HS395964JA39';#查看执行时间EXPLAINANALYZESELECT*FROMusers_bgWHEREcard='HS395964JA39';#执行查询语句SELECT*FROMusers_bgWHEREcard='HS395964JA39';#查看语句执行的精确时间SHOWprofiles\G;***************************5.row***************************Query_ID:5Duration:0.00073925Query:SELECT*FROMusers_bgWHEREcard='HS395964JA39'5rowsinset,1warning(0.00sec)Limit分页优化查询影响查询速度的原因除是否添加索引外，获取记录的页数也会影响查询性能。当要进行分页操作时，通常会使用LIMIT子句实现。但是当分页操作要求偏移量非常大的时候，代价就非常高。例如Limit500000,10这样的查询，这时MySQL需要查询500010条记录然后只返回最后10条，前面的500000条记录都将被抛弃。【例5.29】查询users_bg表中第500001-500010行共10行数据。优化前：#查询语句SELECT*FROMusers_bgLIMIT500000,10;#执行查询mysql>SELECT*FROMusers_bgLIMIT500000,10;#省略查询结果，仅查看执行精确时间mysql>SHOWprofiles\G;***************************3.row***************************Query_ID:3Duration:0.22178100Query:SELECT*FROMusers_bgLIMIT500000,103rowsinset,1warning(0.00sec)Limit分页优化查询该查询可以优化为，先按id>500000进行条件筛选，再取出大于500000的前10行记录。优化后：#查询语句SELECT*FROMusers_bgWHEREid>500000LIMIT10;mysql>SHOWprofiles\G;***************************8.row***************************Query_ID:8Duration:0.00043000Query:SELECT*FROMusers_bgWHEREid>500000LIMIT108rowsinset,1warning(0.00sec)覆盖索引优化查询SELECT查询的数据列从索引中就能够取得，不必读取数据行。即，查询列要被所建立的索引覆盖，索引的字段不仅仅包含查询的列，还包含查询条件、排序等。【例5.30】查询users_bg表中的id和card列，并按card列升序排列，返回500001-500010之间的行。优化前的执行计划和执行结果：

#查询语句SELECTid,cardFROMusers_bgWHEREid>500000ORDERBYcardLIMIT10;mysql>SHOWprofiles\G;***************************10.row***************************Query_ID:10Duration:0.25617600Query:SELECTid,cardFROMusers_bgWHEREid>500000ORDERBYcardLIMIT1010rowsinset,1warning(0.00sec)覆盖索引优化查询为card列建立索引，并查看查询的执行计划。优化前的执行计划和执行结果：

#建立索引CREATEINDEXix_cardONusers_bg(card);mysql>SHOWprofiles\G;***************************12.row***************************Query_ID:12Duration:0.00108500Query:SELECTid,cardFROMusers_bgWHEREid>500000ORDERBYcardLIMIT1012rowsinset,1warning(0.00sec)覆盖索引优化查询【例5.31】查询users_bg表中的card，age列，并按card列排序，返回1000行数据。在表中card列上建立有名为ix_card的索引。查询分析计算如下。SELECTcard,ageFROMusers_bgORDERBYcardLIMIT1000;覆盖索引优化查询删除原ix_card索引，并建立在cardt和age列上的复合索引。查询的执行分析如下。#删除ix_card索引DROPINDEXix_cardONusers_bg;#创建ix_mix_card的复合索引CREATEINDEXix_mix_cardONusers_bg(card,age);覆盖索引优化查询从查询执行分析时间可以看，优化器选用了ix_card_age的索引，且查询实际返回的时间要明显快于优化前的数据。对比优化前后执行的精确时间如下。mysql>SHOWprofiles\G;***************************1.row***************************Query_ID:1Duration:0.09659725Query:SELECTcard,ageFROMusers_bgORDERBYcardLIMIT1000***************************2.row***************************Query_ID:2Duration:0.00093375Query:SELECTcard,ageFROMusers_bgORDERBYcardLIMIT10002rowsinset,1warning(0.00sec)项目实训实践任务（1）创建索引、查看索引和维护索引。（2）创建视图、管理和维护视图以及使用可更新视图。（3）写出各种高效数据查询的方法。实践内容（1）使用Navicat图形工具在onlinedb.goodstype表的tName列上创建一个为IX_tName的普通索引名。（2）使用SQL语句在onlinedb.goods表的gdCode和gdName列上创建一个名为IX_gdCN的复合索引。（3）分别使用SHOWCREATETABLE命令和SHOWINDEXFROM/SHOWKEYSFROM命令查看（2）中所创建的索引IX_gdCN的相关信息。（4）使用SQL语句删除（1）和（2）创建的索引。AI助学：性能优化助教应用场景：随着用户量和交易数据的激增，应用程序会出现页面加载缓慢，响应超时等情况。作为数据库管理员不仅要解决“系统卡顿”问题，还要能找到问题的根源。本节将使用AI辅助你可视化索引的底层结构，解析晦涩的查询执行计划，以帮助你通过添加合适的索引，提升应用程序的响应速度。AI助学：性能优化助教索引原理的可视化【例5.1】使用蚂蚁灵光大模型，分析B+树索引的基本原理。提示词：我是一名数据库初学者。请用通俗易懂的语言解释MySQL中B+树索引的工作原理，并绘制一个3层B+树3路结构，存储关键字1-15，分析查找元素7所需的IO次数，并说明它是如何减少磁盘IO次数的。AI助学：性能优化助教【例5.2】给定正确B+树结构，使用蚂蚁灵光大模型分析相同内容。修改【例5.1】提示词：我是一名数据库初学者。请用通俗易懂的语言解释MySQL中B+树索引的工作原理，图5-2是3路3层B+树结构示例，请用通俗易懂的案例阐述B+树的工作原理，并在该图中分析查找元素0007所需的io次数，并说明它是如何减少磁盘IO次数的。AI助学：性能优化助教AI辅助优化查询性能【例5-3】Chat2DB优化查询示例选择Chat2DB操作区中待分析的SQL语句，单击快速工具栏中“SQL优化”按钮。您的关注是我们前行的动力KeepongoingnevergiveupPPT模板下载：/moban/行业PPT模板：/hangye/节日PPT模板：/jieri/PPT素材下载：/sucai/PPT背景图片：/beijing/PPT图表下载：/tubiao/优秀PPT下载：/xiazai/PPT教程：/powerpoint/Word教程：/word/Excel教程：/excel/资料下载：/ziliao/PPT课件下载：/kejian/范文下载：/fanwen/试卷下载：/shiti/教案下载：/jiaoan/PPT论坛：

模块概览编程操作网上商城系统数据MySQL数据库技术与项目应用06引言/FOREWORD计算机应用有科学计算、数据处理与过程控制三大主要领域。随着信息时代对数据处理的要求不断增多，数据处理在计算机应用领域中占有越来越大的比重，包括现在最流行的客户端/服务器模式（C/S）、Web模式（B/S）应用等。在网上商城系统中，为了有效地提高数据访问效率和数据安全性，网上商城系统的开发过程更加专注于业务逻辑的处理，数据库负担为系统提供数据支持的任务，把复杂逻辑的数据处理放在数据库中，即数据库编程。MySQL提供了函数、存储过程、触发器、事件等数据对象来实现复杂的数据处理逻辑。本项目在数据库编程基础上，详细介绍了MySQL中函数、存储过程、触发器、事件在数据库应用系统开发中的作用，并通过实例阐明它们的使用方法。目录/Contents0103数据库编程基础使用存储过程实现数据访问02使用存储函数实现数据访问05使用事件实现自动任务04使用触发器实现自动任务任务1数据库编程基础任何一种语言都是为了解决实际应用问题而存在的。SQL程序的流程控制及提供的系统函数能够有效解决数据库程序设计中的复杂逻辑问题。本任务在SQL程序语言基础上，详细讨论了SQL的流程控制和MySQL中常用函数的使用。任务场景SQL程序语言基础变量变量是指程序运行过程中会变化的量，MySQL支持的变量类型有3种。用户变量：这种变量用一个@字符作为前缀，在MySQL会话末端结束其定义。系统变量和服务器变量：这种变量包含了MySQL服务器的状态或属性。它们以@@字符作为前导符（例如：@@profiling）。局部变量：只用于存储过程中的变量，而且只在存储过程中有效。没有前导标识，因此定义局部变量命名时必须与数据表和数据列的名字有所区别。在MySQL8.0中变量不区分大小写，@name、@Name或@NAME都表示同一变量。SQL程序语言基础用户变量用户变量即用户定义的变量。用户变量可以被赋值，也可以在后面的其他语句中引用其值。用户变量的名称由“@”字符作为前缀标识符。用户变量使用SET命令和SELECT命令给其赋值。SET命令使用的赋值操作符是“=”或“:=”SELECT命令使用的赋值操作符只能是“:=”。【例6.1】MySQL中用户变量赋值。#语句1SET@id=10;#语句2SELECT@x1:=1,@x2:=@x1+1,@x3:=@x2+1;#语句3SELECTcnameFROMcategoryWHEREcid=1INTO@name;SELECT@name;#语句4SELECT@id:=@id+1,cnameFROMcategory;SQL程序语言基础系统变量系统变量是MySQL的一些特定的参数。当MySQL服务启动时，这些参数将被读取并配置MySQL的运行环境。系统变量使用“@@”作为前缀标识符。【例6.2】使用SHOWVARIABLES查看所有系统变量。【例6.3】设置和查看系统变量。

SHOW[GLOBAL|SESSION]VARIABLES[LIKE'匹配模式'|WHERE条件表达式];SHOWVARIABLES; #查看MySQL的所有系统变量SET@@profiling=0;

#设置系统变量，关闭profiles功能SET@@event_scheduler=0;00;

#设置系统变量，开启事件调度器SELECT@@global.version;

#查看全局变量version,获知当前mysql的版本号SQL程序语言基础局部变量局部变量一般用在SQL语句块（如存储过程的BEGIN和END）中。其作用域仅限于语句块，当语句块执行完毕后，局部变量就消失了。局部变量用DECLARE来声明，可以使用DEFAULT来设置初始值。【例6.4】定义名称为proc_add的存储过程，计算参数a，b之和。DELIMITER//#修改默认提交符为“//”CREATEPROCEDUREproc_add(aint,bint)BEGINDECLAREcintDEFAULT0;#定义局部变量变c,初始值为0SETc=a+b;SELECTcAS'Result';END//#提交SQL程序语言基础常量常量是指在程序运行过程中，值不会改变的量。一个数字，一个字母或一个字符串等都可以是一个常量。字符串常量指用单引号或双引号括起来的字符序列。数值常量数值常量可以分为整数常量和浮点数常量。整数常量即不带小数点的十进制数，例如+1453等。浮点数常量是使用小数点的数值常量，例如，-5.43、1.5E6等。日期时间常量用单引号将表示日期时间的字符串括起来就是日期时间常量。例如，'2008-05-1214:26:24:00'就是一个合法的日期时间常量。布尔值常量布尔值只包含TRUE和FALSE两个值。NULL值常量NULL值适用于各种类型，它通常用来表示“没有值”、“无数据”等意义。SQL程序语言基础运算符运算符是执行数学运算、字符串连接以及列、常量和变量之间进行比较的符号算术运算符：+、-、*、／、%赋值运算符：=、:=逻辑运算符：!（NOT）、&&（AND）、||（OR）、XOR位运算符：&、^、<<、>>、～|比较运算符：=、<>（!=）、<=>、<、<=、>、>=、ISNULLSQL的流程控制语句SQL语言中同其他语言一样有顺序结构、分支结构和循环结构等流程控制语句。通过流程控制语句来控制存储函数、存储过程等语句块的执行过程，实现数据库中较为复杂的程序逻辑。常用分支语句IF…ELSE语句CASE语句WHILE语句LOOP语句REPEAT语句条件分支语句IF…ELSE语句IF…ELSE语句只能使用在存储过程中，实现了非此即彼的逻辑IF条件表达式1THEN

语句块1;

[ELSEIF条件表达式2THEN

语句块2;]

……

[ELSE

语句块n+1;]ENDIF;其中，当“条件表达式1”的值为TRUE时，“语句块1”将被执行；若没有“条件表达式”的值为TRUE，则执行“语句块n+1”，每个语句块都可以包含一个或多个语句。条件分支语句【例6.5】查询uid为3的用户是否购买过商品（判断其是否有订单）。DELIMITER//CREATEPROCEDUREproc_orders()#定义存储过程，用于包含IF语句BEGIN DECLAREnumint;#定义局部变量 #计算订单数并存储num中 SELECTcount(*)INTOnumFROMordersWHEREuid=3; IFnum>0THEN#IF语句判断num值 SELECT'有订单'; ELSE SELECT'无订单'; ENDIF;#结束IF语句END//条件分支语句CASECASE作为分支语句放在程序体中，类似Java或C语言中的switch…case语句。CASE语句语法有两种格式：简单CASE结构和CASE搜索结构简单CASE结构CASE表达式 WHEN数值1THEN语句块1; [WHEN数值2THEN语句块2;] …… [ELSE语句块n+1;]ENDCASE;将“表达式”的值与WHEN子句后的“数值”比较，找到完全相同的项时，则执行对应的“语句块”，若未找到匹配项，则执行ELSE后的“语句块n+1”

条件分支语句CASE搜索结构CASE WHEN条件表达式1THEN语句块1; [WHEN条件表达式2THEN语句块2;] …… [ELSE语句块n+1;]ENDCASE;判断WHEN子句后的“条件表达式”的值是否为TRUE，若为TRUE，则执行对应的“语句块”，若所有的“条件表达式”的值均为FALSE，则执行ELSE后的“语句块n+1”。

条件分支语句【例6.6】判断参数grade，当值为A时返回“优秀”，值为B时返回“良好”，其他值返回“一般”。DELIMITER//CREATEPROCEDUREproc_grade1(gradechar)#定义存储过程，用于包含CASE语句BEGINDECLAREresultchar(2);#定义局部变量resultCASEgradeWHEN'A'THENSETresult='优秀';WHEN'B'THENSETresult='良好';ELSESETresult='一般';ENDCASE;SELECTresult;#返回resultEND//SQL的流程控制【例6.7】使用CASE搜索结构实现【例6.6】DELIMITER//CREATEPROCEDUREproc_grade2(gradechar(1))#定义存储过程，用于包含CASE语句BEGINDECLAREresultchar(2);#定义局部变量resultCASEWHENgrade='A'THENSETresult='优秀';WHENgrade='B'THENSETresult='良好';ELSESETresult='一般';ENDCASE;SELECTresult;#返回resultEND//循环语句

WHILE语句【例6.8】使用WHILE语句，求1到100的和。[开始标签:]WHILE条件表达式DO

语句块;ENDWHILE[结束标签];DELIMITER//CREATEPROCEDUREproc_doWhile()BEGIN DECLAREiintdefault1;#定义局部变量i DECLAREsintdefault0;#定义局部变量s WHILEi<=100DO#开始循环 SETs=s+i; SETi=i+1; ENDWHILE;#结束循环 SELECTs;END//循环语句REPEAT语句[开始标签:]REPEAT

语句块; UNTIL条件表达式;ENDREPEAT[结束标签];其中，UNTIL关键字表示直到满足条件表达式时结束循环，其它参数释意同WHILE语句。学习提示：REPEAT语句是在执行循环体里的语句块后再执行“条件表达式”的比较，不管条件是否满足，循环体至少执行一次；而WHILE语句则是先执行“条件表达式”的比较，当结果为TRUE时再执行循环体中的语句块。循环语句LOOP语句【例6.9】LOOP语句示例。[开始标签:]LOOP

语句块ENDLOOP[结束标签];add_num:LOOP SETi=i+1;ENDLOOPadd_num;学习提示：（1）循环里没有跳出循环的语句，这个循环是死循环。（2）LOOP语句的循环体中没有中止循环的语句，它必须和LEAVE语句结合使用。

循环语句LEAVE语句用于跳出循环控制，与高级语言中的BREAK语句相似。【例6.10】修改【例6.9】，使用LEAVE语句跳出循环。LEAVE标签名;add_num:LOOP SETi=i+1; IFi=100THENLEAVEadd_num;ENDLOOPadd_num;循环语句ITERATE语句用于跳出循环，与Java或C语言中的CONTINUE语句相似。ITERATE语句只跳出当次循环，然后直接进入下一次循环。ITERATE标签名;学习提示：LEAVE语句和ITERATE语句都是用来跳出循环语句，但两者的功能是不一样的。LEAVE语句是跳出整个循环，然后执行循环外的程序语句；ITERATE语句是跳出本次循环，进入下一次循环。MySQL常用内置函数MySQL内置函数是MySQL数据库提供的内部函数。SQL语句和表达式中都可以使用这些函数。主要包括：数学函数字符串函数日期时间函数数据类型转换函数条件控制函数加密和散列函数系统信息函数JSON函数数学函数数学函数主要用于处理数字，包括整数、浮点数等。数学函数包括绝对值函数、正弦函数、余弦函数和随机函数。函数名称作用abs(x)返回x的绝对值ceil(x),ceiling(x)返回大于或等于x的最小整数floor(x)返回小于或等于x的最大整数rand()返回0～1的随机数rand(x)返回0～1的随机数，x值相同时返回的随机数相同sign(x)返回x的符号，x是负数、0、正数时分别返回-1、0和1pi()返回圆周率（3.141593）truncate(x,y)返回数值x保留到小数点后y位的值round(x)返回离x最近的整数round(x,y)返回x小数点后y位的值，但截断时要进行四舍五入pow(x,y),power(x,y)返回x的y次方sqrt(x)返回x的平方根exp(x)返回e的x次方mod(x,y)返回x除以y以后的余数【例6.11】以1为基数，每天进步0.01，计算一年后的进步有多大?数学函数函数名称作用log(x)返回自然对数（以e为底的对数）log10(x)返回以10为底的对数radians(x)将角度转换为弧度degrees(x)将弧度转换为角度sin(x)求正弦值cos(x)求余弦值tan(x)求正切值cot(x)求余切值mysql>SELECTpower((1+0.01),365)asprogress;++|progress|++|37.78343433288728|++1rowinset(0.00sec)字符串函数字符串函数主要用于处理字符串。字符串函数包括字符串长度、合并字符串、在字符串中插入子串和大小字母之间切换等函数。函数名称作用char_length(s)返回字符串s的字符数length(s)返回字符串s的长度concat(s1,s2,…)将字符串s1、s2等多个字符串合并为一个字符串concat_ws(x,s1,s2,…)同CONCAT(s1,s2,…)函数，但是每个字符串要直接加上xinsert(s1,x,len,s2)将字符串s2替换成s1的x位置开始长度为len的字符串upper(s),ucase(s)将字符串s的所有字母都变成大写字母lower(s),lcase(s)将字符串s的所有字母都变成小写字母left(s,n)返回字符串s的前n个字符right(s,n)返回字符串s的后n个字符lpad(s1,len,s2)字符串s2来填充s1的开始处，使字符串长度达到lenrpad(s1,len,s2)字符串s2来填充s1的结尾处，使字符串长度达到lenltrim(s)去掉字符串s开始处的空格rtrim(s)去掉字符串s结尾处的空格trim(s)去掉字符串s开始处和结尾处的空格【例6.12】输出合并的两个字符串，并在两个子串之间插入1个空格。数学函数函数名称作用trim(s1froms)去掉字符串s中开始处到结尾处的字符串s1repeat(s,n)将字符串s重复n次space(n)返回n个空格replace(s,s1,s2)用字符串s2替代字符串s中的字符串s1strcmp(s1,s2)比较两个字符串，若s1>s2，返回1，反之返回-1，若相等返回0substring(s,n,len)获取从字符串s中的第n个位置开始长度为len的子字符串mid(s,n,len)同SUBSTRING(s,n,len)locate(s1,s),position(s1ins)返回字符串s1在字符串s中的起始位置mysql>SELECTCONCAT('Hunan',SPACE(1),'Changsha')asstr;++|str|++|HunanChangsha|++1rowinset(0.00sec)日期时间函数日期时间函数主要用于处理日期和时间数据。日期时间函数包括获取当前日期的函数、获取当前时间的函数、计算日期的函数、计算时间的函数等函数名称作用curdate(),current_date()返回当前日期curtime(),current_time()返回当前时间now(),current_timestamp()返回当前日期和时间utc_date()返回UTC（国际协调时间）日期utc_time()返回UTC（国际协调时间）时间month(d)返回日期d中的月份值，范围1～12monthname(d)返回日期d中的月份名称，如January、February等dayname(d)返回日期d是星期几，如Monday、Tuesday等dayofweek(d)返回日期d是星期几，1表示星期日，2表示星期一等weekday(d)返回日期d是星期几，0表示星期一，1表示星期二等week(d)计算日期d是本年的第几个星期，范围是0～53dayofyear(d)计算日期d是本年的第几天dayofmonth(d)计算日期d是本月的第几天year(d)返回日期d中的年份值【例6.14】获取系统当前日期时间的年份值、月份值、日期值、小时值和分钟值。日期时间函数函数名称作用hour(t)返回时间t中的小时值minute(t)返回时间t中的分钟值second(t)返回时间t中的秒钟值date_format(d,f)按表达式f的格式显示d,f定义了日期和日间的格式，以%开头date_add(d,intervaleunit)返回指定日期d指定间隔的日期，e为间隔数，unit为日期部分SET@mydate=CURDATE();SET@mytime=CURTIME();SELECTYEAR(@mydate),MONTH(@mydate),DAYOFMONTH(@mydate),HOUR(@mytime),MINUTE(@mytime);数据类型转换函数【例6.16】数据类型转换函数示例。函数名称作用cast(xAStype)将x的值按type类型返回convert(x,type)将x的值按type类型返回convert(xUSING字符集)将经的值按指定的字符集返回SELECTCAST('2021-10-0116:50:21'asdate); #输出2021-10-01SELECTconvert('132str',SIGNED); #输出123SELECTconvert('大'USINGutf8mb4),convert('大'USINGascii);#输出大,？条件控制函数主要处理简单的逻辑判断。【例6.17】条件控制函数示例。条件控制函数函数名称作用if(expr,v1,v2)判断expr的值，为TRUE时返回v1，否则返回v2ifnull(v1,v2)判断v1的值，若不为null返回v1，否则返回v2nullif(v1,v2)比较v1与v2的值，若相等返回null，否则返回v1isnull(expr)判断expr的值，为null时返回1，否则返回0SELECTif(TRUE,'A','B'),if(FALSE,'A','B'); #输出ABSELECTifnull('A','B'),ifnull(null,'B'); #输出ABSELECTnullif('A','B'),nullif('A','A'); #输出AnullSELECTisnull(null),isnull('A'); #输出10【例6.18】查询图书类商品的名称和销售状态(sale_status)，若gishot为1，显示“热销”，否则显示为“一般”。条件控制函数Smysql>SELECTgname,if(gishot=1,'热销','一般')ASsale_status

->FROMgoodsJOINcategoryUSING(cid)

->WHEREcname='图书';+++|gname

|sale_status|+++|林清玄启悟人生系列：愿你，归来仍是少年

|一般

||平凡的世界：全三册（激励青年的不朽经典）

|热销

||曾国藩全集（全六卷绸面精装插盒珍藏版）

|一般

||中外文化文学经典系列红岩导读与赏析

|一般

|+++4rowsinset(0.00sec)加密和散列函数主要用于对存储的数据进行加密，相对于明文存储，加密后的字符串不会被管理员直接看到，以保证数据的安全性，实际应用中对于敏感数据的存储都要进行加密处理。【例6.19】加密和散列函数示例。加密和散列函数函数名称作用md5(str)使用MD5算法对str计算，返回32位的散列字符串aes_encrypt(str,key)使用密钥key对str进行加密，返回128位的二进制串aes_decrypt(str,key)使用密钥key对加密文本str进行解密sha1(str),sha(str)使用安全散列算法SHA1计算str，返回40位十六进制数字组成的字符串->convert(aes_decrypt(aes