MySQL数据库应用实战教程（慕课版）（第2版）-教学课件第7单元 索引

第7单元索引MySQL数据库应用实战教程（慕课版）（第2版）目录【目录】7.1索引的基本语法7.1.1创建索引7.1.2查看索引7.1.3删除索引7.2常见的查询算法7.3索引的数据结构7.3.1B-Tree数据结构7.3.2B+Tree

数据结构7.4索引实现原理7.4.1MyISAM存储引擎的索引实现7.4.2InnoDB

存储引擎的索引实现7.4.3MEMORY存储引擎的索引实现7.5索引的应用7.5.1创建表及添加索引7.5.2使用EXPLAIN语句分析索引7.5.3索引使用策略7.5.4索引应用实例目录【目录】7.6索引的类型7.6.1主键索引7.6.2普通索引7.6.3唯一索引引7.6.4单列索引和联合索引7.6.5聚簇索引和非聚簇索引7.6.6覆盖索引7.6.7重复索引等7.6.8降序索引7.6.9隐藏索引7.6.10函数索引7.7索引不能使用7.7.1前导模糊查询7.7.2比较不匹配的数据类型7.7.3使用OR连接条件表达式7.7.4条件表达式与函数7.8索引的利弊及创建原则7.9综合实训：电商平台查询索引应用7.10小结【学习导读】假设有一个电商平台，拥有大量的商品数据，用户可以根据关键字搜索商品。为了提高搜索效率，可以使用MySQL索引来加速搜索过程。通过在商品表的名称字段上创建索引，用户可以快速定位到包含特定关键字的商品。这样，用户可以更快地找到所需的商品，提升了用户体验和平台的整体性能。学习导读【学习目标】知识目标1．掌握MySQL索引的基本语法。2．掌握常见的查询算法。3．掌握MySQL索引的数据结构。4．掌握MySQL索引的实现原理。5．掌握MySQL索引的应用。6．掌握MySQL索引的类型。7．了解索引不能使用的场景。8．了解索引的利弊及建立原则。能力目标1．能够熟练使用MySQL索引来加快数据查询效率。2．能够熟练正确创建合适的索引。素质目标1．培养目标导向意识，有明确的目标和追求，能够有效地实现目标。2．培养解决问题的能力，能够分析问题并提出解决方案。学习目标思维导图7.1索引的基本语法7.1.1创建索引在创建表的时候创建索引属性名数据类型[完整性约束条件],......属性名数据类型[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[别名](属性名1[(长度)][ASC|DESC]));mysql>CREATETABLEstudent(idintprimarykey,namevarchar(255),sexvarchar(10),index(name));

UNIQUE：唯一索引。FULLTEXT：全文索引。SPATIAL：空间索引。INDEX和KEY：用来指定某个字段为索引，使用效果一样。别名：用来给创建的索引取新名称。属性1：用来指定索引对应的字段的名称，该字段必须为前面定义好的字段。长度：指索引的长度，必须是字符串类型才可以使用。ASC：升序排列。DESC：降序排列。使用CREATEINDEX语句创建索引CREATEINDEXindex_nameONtable_name(column_list)CREATEUNIQUEINDEXindex_nameONtable_name(column_list)mysql>CREATEINDEXidx_sexONstudent(sex);7.1索引的基本语法使用ALTERTABLE语句创建索引#普通索引ALTERTABLEtable_nameADDINDEXindex_name(column_list)#唯一索引ALTERTABLEtable_nameADDUNIQUE(column_list)#主键索引ALTERTABLEtable_nameADDPRIMARYKEY(column_list)mysql>ALTERTABLEstudentADDINDEXidx_name(name);索引命名建议唯一索引以“uk_”开头，普通索引以“idx_”开头，以字段的名称或缩写作为后缀。7.1.2查看索引在MySQL中，可以使用SHOWINDEX语句来查看表的索引信息。该语句的语法格式如下。SHOWINDEXFROMtable_name;或如下:SHOWKEYSFROMtable_name;7.1索引的基本语法#查看学生表student的索引信息mysql>SHOWINDEXFROMstudent;+---------+------+---------------+-------+------------+--------+---------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|+---------+------+---------------+-------+------------+--------+---------+|student|0|PRIMARY|1|id|A|0||student|1|idx_name|1|name|A|0||student|1|idx_sex|1|sex|A|0||student|1|id_name_index|1|id|A|0|+---------+------+---------------+-------+------------+--------+---------+#查看学生表student的索引信息mysql>SHOWKEYSFROMstudent;+---------+------+---------------+-------+------------+--------+---------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|+---------+------+---------------+-------+------------+--------+---------+|student|0|PRIMARY|1|id|A|0||student|1|idx_name|1|name|A|0||student|1|idx_sex|1|sex|A|0||student|1|id_name_index|1|id|A|0|+---------+------+---------------+-------+------------+--------+---------+7.1索引的基本语法7.1.3删除索引使用DROPINDEX语句可以删除指定表中的索引。执行该语句后，相应的索引将从表中被删除。该语句的语法格式如下。DROPINDEXindex_nameONtalbe_nameALTERTABLEtable_nameDROPINDEXindex_nameALTERTABLEtable_nameDROPPRIMARYKEY实战演练——给用户表user添加和删除索引#使用用户名root和相应密码，连接本地数据库C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用数据库shopmysql>USEshop;Databasechanged7.1索引的基本语法#查询用户表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name|sex|age|password|+----+-------+------+------+----------+|3|david|女|28|111111||4|小红|女|27|123456||5|小明|男|10|123456||6|小刚|男|12|123456||7|小王|男|14|111111||8|小绿|女|34|222222||9|晓峰|男|15|333333||10|小影|女|26|444444||11|大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#在姓名name列上添加索引mysql>ALTERTABLEuserADDINDEXidx_name(name);QueryOK,0rowsaffected(0.43sec)Records:0Duplicates:0Warnings:0#在性别sex列上添加索引mysql>ALTERTABLEuserADDINDEXidx_sex(sex);QueryOK,0rowsaffected(0.34sec)Records:0Duplicates:0Warnings:07.1索引的基本语法#查询用户表user的索引mysql>SHOWINDEXFROMuser;

+-------+------------+----------+--------------+-------------+-----------+----------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality+-------+------------+----------+--------------+-------------+-----------+----------+|user|0|PRIMARY|1|id|A|9||user|1|idx_name|1|name|A|9||user|1|idx_sex|1|sex|A|2|+-------+------------+----------+--------------+-------------+-------v---+----------+3rowsinset(0.00sec)#查询用户表user的索引mysql>SHOWKEYSFROMuser;+-------+------------+----------+--------------+-------------+-----------+----------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality+-------+------------+----------+--------------+-------------+-----------+----------+|user|0|PRIMARY|1|id|A|9||user|1|idx_name|1|name|A|9||user|1|idx_sex|1|sex|A|2|+-------+------------+----------+--------------+-------------+-----------+----------+3rowsinset(0.00sec)7.1索引的基本语法#删除姓名name列上的索引mysql>DROPINDEXidx_nameONuser;QueryOK,0rowsaffected(0.18sec)Records:0Duplicates:0Warnings:0#删除性别sex列上的索引mysql>DROPINDEXidx_sexONuser;QueryOK,0rowsaffected(0.21sec)Records:0Duplicates:0Warnings:0#查询用户表user的索引mysql>SHOWKEYSFROMuser;+-------+-----------+----------+-------------+------------+----------+----------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality+-------+-----------+----------+-------------+------------+----------+----------+|user|0|PRIMARY|1|id|A|9|+-------+-----------+----------+-------------+------------+----------+----------+1rowinset(0.00sec)mysql>7.2常见的查询算法1．顺序查找算法顺序查找算法比较好理解，就是按顺序逐个查找。例如，有一组数据“2、3、5、7、8、9、10、16、21、25、30”，要从这组数据里查找“8”这个数据，使用顺序查找算法，就需要逐个比对数据，直到找到要查找的数据。2．二分查找算法二分查找算法比顺序查找算法效率高，它的查找原理是从要查找的数据的中间元素开始。例如，有一组数据“2、3、5、7、8、9、10、16、21、25、30”，要从这组数据里查找“8”这个数据，使用二分查找算法，从中间元素“9”开始比对，以“9”为中心把数据分成两部分“2、3、5、7、8”和“10、16、21、25、30”；“8”是小于“9”的，所以在“2、3、5、7、8”这组数据里继续查找，同样使用二分查找算法再将其分成两部分“2、3”和“7、8”；“8”是大于“5”的，所以在“7、8”里查找数据……这样不断将数据分成两部分，在其中的一部分里查找。7.2常见的查询算法3．二叉树查找算法二叉树具有如下特点：每个节点最多有两棵子树，节点的度最大为2；左子树和右子树是有顺序的，顺序不能颠倒；即使某节点只有一棵子树，也要区分左右子树。4．哈希查找算法哈希查找算法是将数据值通过哈希函数创建一个哈希表，如果要查询某个数据值，也需要先通过哈希函数生成一个值，再到哈希表里去查询，如果查询到则返回数值。将通过哈希函数重新生成的数据值存放到哈希表中时很有可能重复，从而产生冲突，冲突越少，查询得越快，如果没有冲突，则它的时间复杂度是O(1)。7.3索引的数据结构7.3.1B-Tree数据结构B-Tree数据结构称为平衡多路搜索树，它基于在二叉树的基础上采用多叉树、再使用平衡二叉树的思想。之所以采用多路搜索树，是因为这样可以减小树的深度，提高查询速度。B-Tree数据结构如图7.2所示。有一个根节点，根节点只有一条记录和两棵子树，如50就是根节点，下面有两棵子树。方框里的数字是key，线是指针，指针指向子树。所有的叶子节点必须在同一层，也就是说它们具有相同的深度；每个叶子节点至少包含一个key和两个指针，最多包含2d－1个key和2d个指针，叶子节点的指针均为NULL。每个非叶子节点包含n－1个key和n个指针，其中d≤n≤2d。在一个节点中，第n棵子树中的所有key，小于这个节点中的第n个key，大于第n－1个key，例如15、45这棵子树，它的子树有3个取值范围：0～15、16～45、46以上。图7.2B-Tree数据结构7.3索引的数据结构B-Tree数据结构是先从根节点进行二分查找，如果小于根节点，就从左子树开始，从相应区间指针指向的节点递归进行查找；如果大于根节点，就从右子树递归进行查找，直到找到满足条件的节点，才返回相应的data。叶子节点具有相同的深度，叶子节点的指针为空。所有索引元素不重复。节点中的数据索引从左到右递增。7.3索引的数据结构7.3.2B+Tree数据结构B+Tree数据结构是B-Tree数据结构的变种，它也是一种平衡多路搜索树，MySQL普遍使用B+Tree数据结构实现索引。B+Tree数据结构如图7.3所示。非叶子节点不存储data，只存储索引（冗余），可以存储更多的索引。叶子节点包含所有索引字段。叶子节点用指针连接，以提高区间访问的性能。图7.3B+Tree数据结构B+Tree数据结构的特点：step01step02B-Tree数据结构和B+Tree数据结构的优点：支持快速的插入、删除和查询操作，时间复杂度通常为O(logn)。适用于大型数据集和高并发的数据库环境。对数据的插入和删除操作具有较好的平衡性，避免了频繁的数据迁移。7.3索引的数据结构B-Tree索引结构和B+Tree索引结构主要区别：step03B-Tree索引结构和B+Tree索引结构B+Tree索引结构的叶子节点存储数据，非叶子节点不存储数据B+Tree索引结构的叶子节点存放表所有数据，非叶子节点作为冗余索引B+Tree索引结构的叶子节点带有指针，可以快速范围遍历查找B-Tree索引结构的节点都存储数据，没有冗余节点索引，并且叶子节点没有指针B+Tree索引结构由于只有叶子节点存放数据，所以用最小深度可以存放更多的节点数据7.4索引实现原理7.4.1MyISAM存储引擎的索引实现1．主索引主索引是以主键或者唯一标识生成的索引，在创建表的时候，如果没有指定主索引，存储引擎会自动维护一个主索引，它的每个节点是不允许重复的，就如同数据行的主键。MyISAM主索引以主键为核心、采用B+Tree数据结构来构建索引树，叶子节点存放数据存储的地址，通过地址可以快速找到相应的行数据。图7.4MyISAM主索引7.4索引实现原理2．辅索引辅索引就是以其他列构建的索引，而非以主键构建的索引。它与主索引的区别就在于它允许重复，而主索引是不允许重复的。与主索引一样，辅索引在索引树的叶子节点存放地址，通过地址找到数据。图7.5MyISAM辅索引7.4索引实现原理7.4.2InnoDB存储引擎的索引实现1．主索引主索引是以主键或者唯一列来构建的索引，如果没有指定主键或者唯一列，MySQL自己维护一套主索引，它是不允许重复的。例如：有一个学生表，它有主键id列、姓名name列、性别sex列、年龄age列，那么它的主索引如图7.6所示。图7.6InnoDB主索引7.4索引实现原理2．二级索引InnoDB二级索引是以其他列而非主键构建的索引，所以它是允许重复的。它的叶子节点存放主索引的值，通过主索引再直接找到数据，这样就不用在每棵索引树上存储数据，能够节省存储空间，同时便于维护。图7.7InnoDB二级索引7.4索引实现原理7.4.3MEMORY存储引擎的索引实现MEMORY存储引擎又称为HEAP存储引擎，用于创建特殊用途的表且内容存储在内存中。将数据存储在内存中，能够实现快速访问和低延迟。它的默认索引方式采用哈希索引的方式来构建。哈希索引通过哈希函数计算出结果，然后将该结果随机地存放到磁盘中。通过哈希函数，对某列要加索引重新计算出一个结果值随机记录到磁盘中，如果计算出的结果值没有重复的，那么它的时间复杂度就是O(1)。随着数据量的增大，通过哈希函数计算出的结果值很可能重复，这样查询效率会逐渐下降，但是它的检索速度也是非常快的。图7.8哈希索引7.5索引的应用7.5.1创建表及添加索引表7.1student表创建一个学生表student，它有主键id列、姓名name列、性别sex列、年龄age列、年级grade列、班级class列、学号num列，如表7.1所示，然后给id、name、sex、age每列单独添加索引。idnamesexagegradeclassnum1刘明男19高三6班30012吴倩女18高三6班30023张欣男17高二1班20014孙晓女17高二1班20025赵英俊男16高一2班10016柳师师女17高一2班10027.5索引的应用实战演练——创建学生表student并添加索引#使用用户名root和相应密码，连接本地数据库C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure#使用数据库shop.mysql>USEshop;Databasechanged#删除student表mysql>DROPTABLEstudent;QueryOK,0rowsaffected(0.15sec)#创建student表mysql>CREATETABLEstudent(idintnotnullAUTO_INCREMENT,namevarchar(255),sexvarchar(5),agevarchar(5),gradevarchar(10),classvarchar(10),numint,primarykey(id));QueryOK,0rowsaffected(0.42sec)7.5索引的应用#插入数据mysql>insertintostudentvalues(1,'刘明','男','19','高三','6班',3001),(2,'吴倩','女','18','高三','6班',3002),(3,'张欣','男','17','高二','1班',2001),

(4,'孙晓','女','17','高二','1班',2002),(5,'赵英俊','男','16','高一','2班',1001),(6,'柳师师','女','17','高一','2班',1002);QueryOK,1rowaffected(0.00sec)#查询数据mysql>SELECT*FROMstudent;+----+--------+------+------+-------+-------+------+|id|name|sex|age|grade|class|num|+----+--------+------+------+-------+-------+------+|1|刘明|男|19|高三|6班|3001||2|吴倩|女|18|高三|6班|3002||3|张欣|男|17|高二|1班|2001||4|孙晓|女|17|高二|1班|2002||5|赵英俊|男|16|高一|2班|1001||6|柳师师|女|17|高一|2班|1002|+----+--------+------+------+-------+-------+------+6rowsinset(0.00sec)#给主键id列添加唯一索引mysql>ALTERTABLEstudentADDUNIQUEuk_id(id);

QueryOK,0rowsaffected(0.70sec)Records:0Duplicates:0Warnings:0#给姓名name列添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_name(name);QueryOK,0rowsaffected(0.30sec)Records:0Duplicates:0Warnings:0#给性别sex列添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_sex(sex);QueryOK,0rowsaffected(0.63sec)Records:0Duplicates:0Warnings:0#给年龄age列添加普通索引mysql>ALTERTABLEstudentADDINDEXidx_age(age);QueryOK,0rowsaffected(0.42sec)Records:0Duplicates:0Warnings:07.5索引的应用#查看索引mysql>SHOWINDEXFROMstudent;+--------+----------+---------+-------------+----------+---------+-----------+|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality+--------+----------+---------+-------------+----------+---------+-----------+|student|0|PRIMARY|1|id|A|0||student|0|uk_id|1|id|A|0||student|1|idx_name|1|name|A|||student|1|idx_sex|1|sex|A|||student|1|idx_age|1|age|A||+--------+---------+---------+-----------+----------+---------+-----------+mysql>7.5索引的应用7.5.2使用EXPLAIN语句分析索引在student表的id、name、sex、age这4列上分别添加索引后，怎么知道使用哪个索引呢？可以使用EXPLAIN语句来分析索引。实战演练——用EXPLAIN语句分析student表的索引#使用用户名root和相应密码，连接本地数据库C:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用数据库shopmysql>USEshop;Databasechanged#查询学生表studentmysql>SELECT*FROMstudent;+----+----------+------+------+--------+-------+-----+|id|name|sex|age|grade|class|num|+----+----------+------+------+--------+-------+-----+|1|刘明|男|19|高三|6班|3001||2|吴倩|女|18|高三|6班|3002||3|张欣|男|17|高二|1班|2001||4|孙晓|女|17|高二|1班|2002||5|赵英俊|男|16|高一|2班|1001||6|柳师师|女|17|高一|2班|1002|+----+----------+------+------+--------+-------+-----+6rowsinset(0.00sec)7.5索引的应用#查询姓名为刘明、性别为男的学生mysql>SELECT*FROMstudentWHEREname='刘明'andsex='男';+----+-------+------+------+-------+-------+------+|id|name|sex|age|grade|class|num|+----+-------+------+------+-------+-------+------+|1|刘明|男|19|高三|6班|3001|+----+-------+------+------+-------+-------+------+1rowinset(0.09sec)#分析索引使用情况mysql>EXPLAINSELECT*FROMstudentWHEREname='刘明'andsex='男'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_name,idx_sexkey:idx_namekey_len:1023ref:constrows:1filtered:50.00Extra:Usingwhere1rowinset,1warning(0.00sec)mysql>7.5索引的应用EXPLAIN语句用于分析SQL语句的查询情况，返回结果包含id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered、Extra等字段，字段说明如表7.2所示。表7.2EXPLAIN语句分析SQL语句的字段说明序号字段名称字段说明1id用来标识SELECT语句的编号，有多条SELECT语句就会有多个id，编号从1开始递增2select_typeSIMPLE：简单查询，不包含子查询和UNION查询；PRIMARY：主查询，也就是最外层的SELECT查询；SUBQUERY：子查询中的第一个SELECT查询，不依赖于外部查询的结果集；UNION：UNION查询中的第二个或随后的SELECT查询，不依赖于外部查询的结果集；UNION

RESULT：UNION查询的结果集；DEPENDENTUNION：UNION查询中的第二个SELECT查询，依赖于外部查询；DEPENDENTSUBQUERY：子查询中的第一个SELECT查询，依赖于外部查询3table查询所使用的表名，它可以是实际的表名，也可以是表的别名；当有UNION（不是UNIONALL）时，UNIONRESULT的table字段的值为<union1,2>，1和2表示参与UNION的SELECT行id；NULL，MySQL能够在优化阶段分解查询语句，在执行阶段不用再访问表或索引4type扫描类型，可以决定查询速度，下面按最优到最差排序。system＞const＞eq_ref＞ref＞range＞index＞ALLsystem：表仅有一行；const：用于常数值比较主键时，当查询的表仅有一行时，使用system；eq_ref：使用主键索引或者唯一索引进行两个表关联查询，得到唯一满足的数据。ref：使用普通索引进行两个表关联查询。range：检索给定范围的行；index：全表扫描索引树的节点；ALL：从头到尾全表扫描7.5索引的应用序号字段名称字段说明5possible_

keys可能用到的索引，最终只能使用一个索引。如果为NULL，说明没有可用的索引6key实际用到的索引，只能使用一个索引。如果为NULL，则没有使用索引7key_len用到的索引长度，在不损失精确性的情况下，长度越短越好。key_len计算规则如下。（1）字符串：char(n)和varchar(n)，n均代表字符数，而不是字节数。如果是UTF-8，一个数字或字母占1个字节，一个汉字占3个字节；（2）char(n)：如果存汉字长度就是3n个字节；（3）varchar(n)：如果存汉字则长度是3n+2个字节，加的2个字节用来存储字符串长度，因为varchar是变长字符串类型；（4）数值类型：tinyint，1个字节；smallint，2个字节；int，4个字节；bigint，8个字节；（5）时间类型：date，3个字节；timestamp，4个字节；datetime：8个字节；（6）如果字段允许为NULL，需要1个字节记录是否为NULL8ref连接查询时表与表之间的连接关系，显示在key字段记录的索引中，表查找值所用到的列或常量，常见的有const（常量）、字段名9rows扫描的行数，估计要读取并检测的行数，注意这个不是结果集里的行数10filtered一个百分比值，rows*filtered/100可以估算出将要和EXPLAIN语句中前一个表进行连接的行数（前一个表指EXPLAIN语句中的id值比当前表id值小的表）11Extra附加信息，查看索引的使用情况。Usingindex：使用索引，没有查询数据表、只用索引表完成查询，也叫覆盖索引。如果同时出现Usingwhere，代表使用索引来查找记录，但是需要查询到数据表；Usingwhere：条件查询，使用WHERE语句来处理结果，并且查询的列未被索引覆盖；Usingindexcondition：查询的列不完全被索引覆盖，WHERE条件中是一个前导列的范围；Usingfilesort：文件排序是一种排序策略，使用ORDERBY排序语句会出现该信息；Usingtemporary：为了得到结果，使用了临时表，出现这种情况一般要进行优化；Selecttablesoptimizedaway：使用某些聚合函数（比如MAX()、MIN()）来访问存在索引的某个字段7.5索引的应用1．id查询编号id是用来标识SELECT语句的编号，有多条SELECT语句就会有多个id，但是如果查询的SELECT语句的select_type和table相同，其id也不会自增，因为它们被认为是一条语句。#id自增语句mysql>EXPLAINSELECT*FROMstudentWHEREid=(SELECTidFROMstudentWHEREid=3)\G***************************1.row***************************id:1select_type:PRIMARYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL***************************2.row***************************id:2select_type:SUBQUERYtable:studentpartitions:NULLtype:const7.5索引的应用possible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex2rowsinset,1warning(0.01sec)2rowsinset,1warning(0.08sec)#id不自增语句mysql>EXPLAINSELECT*FROMstudentWHEREidin(SELECTidFROMstudentWHEREid=3)\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL***************************2.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex2rowsinset,1warning(0.00sec)7.5索引的应用2．select_type查询类型SIMPLE为简单查询，不包含子查询；PRIMARY为主查询，也就是最外层的SELECT查询；SUBQUERY为子查询中的第一个SELECT查询，不依赖于外部查询的结果集；UNION为UNION查询中的第二个或随后的SELECT查询，不依赖于外部查询的结果集；UNIONRESULT为UNION查询的结果集；DEPENDENTUNION为UNION查询中的第二个SELECT查询，依赖于外部查询。mysql>EXPLAINSELECT*FROMstudent\G1rowinset,1warning(0.00sec)***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（1）简单查询SIMPLE7.5索引的应用mysql>EXPLAINSELECT*FROMstudentWHEREid=(SELECTidFROMstudentWHEREid=3)\G***************************1.row***************************id:1select_type:PRIMARYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL***************************2.row***************************id:2select_type:SUBQUERYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex2rowsinset,1warning(0.00sec)（2）主查询PRIMARY、子查询SUBQUERY7.5索引的应用mysql>EXPLAINSELECT*FROMstudentWHEREid=2UNIONSELECT*FROMstudentWHEREid=3\G***************************1.row***************************id:1select_type:PRIMARYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1

filtered:100.00Extra:NULL***************************2.row***************************（3）UNION查询，在UNION之后的SELECT查询，不依赖于外部查询。7.5索引的应用id:2select_type:UNIONtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL***************************3.row***************************id:3select_type:UNIONRESULTtable:<union1,2>partitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Usingtemporary3rowsinset,1warning(0.01sec)mysql>7.5索引的应用mysql>EXPLAINSELECT*FROMstudentWHEREidIN(SELECTidFROMstudentWHEREid=2UNIONSELECTidFROMstudentWHEREid=3)\G***************************1.row***************************id:1select_type:PRIMARYtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:Usingwhere***************************2.row***************************id:2select_type:DEPENDENTSUBQUERYtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex***************************3.row***************************（4）DEPENDENTUNION查询、DEPENDENTSUBQUERY查询。7.5索引的应用id:3select_type:DEPENDENTUNIONtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:Usingindex***************************4.row***************************id:4select_type:UNIONRESULTtable:<union2,3>partitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Usingtemporary4rowsinset,1warning(0.00sec)7.5索引的应用3．table表名table是查询的表名。它可以是实际的表名，也可以是表的别名，还可以为NULL。#实际表名mysql>EXPLAINSELECT*FROMstudent\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)#表的别名mysql>EXPLAINSELECT*FROMstudentastemp\G***************************1.row***************************7.5索引的应用id:1select_type:SIMPLEtable:temppartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)#表名为NULLmysql>EXPLAINSELECT1\G***************************1.row***************************id:1select_type:SIMPLEtable:NULLpartitions:NULLtype:NULLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Notablesused1rowinset,1warning(0.00sec)7.5索引的应用4．type扫描类型type是用在查询过程中的扫描类型，不同的扫描类型可以决定查询的速度。mysql>EXPLAINSELECT*FROMstudent\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（1）type=ALL，扫描全表数据，速度最慢。7.5索引的应用mysql>EXPLAINSELECTnameFROMstudent\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:indexpossible_keys:NULLkey:idx_namekey_len:1023ref:NULLrows:6filtered:100.00Extra:Usingindex1rowinset,1warning(0.00sec)（2）type=index，扫描索引所有节点。7.5索引的应用mysql>EXPLAINSELECTnameFROMstudentWHEREid>2\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:rangepossible_keys:PRIMARY,uk_id,idx_namekey:PRIMARYkey_len:4ref:NULLrows:4filtered:100.00Extra:Usingwhere1rowinset,1warning(0.01sec)（3）type=range，扫描给定范围的行。7.5索引的应用mysql>EXPLAINSELECT*FROMstudentWHEREname='小明'\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:refpossible_keys:idx_namekey:idx_namekey_len:1023ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)（4）type=ref，非唯一索引扫描，返回匹配某个单独值的所有行。7.5索引的应用mysql>EXPLAINSELECT*FROMstudentWHEREidIN(SELECTidFROMstudent)\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:ALLpossible_keys:PRIMARY,uk_idkey:NULLkey_len:NULLref:NULLrows:6filtered:100.00Extra:NULL***************************2.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:eq_refpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:shop.student.idrows:1filtered:100.00Extra:Usingindex2rowsinset,1warning(0.00sec)（5）type=eq_ref，唯一索引扫描，只能返回满足条件的一行数据。7.5索引的应用mysql>EXPLAINSELECT*FROMstudentWHEREid=1\G***************************1.row***************************id:1select_type:SIMPLEtable:studentpartitions:NULLtype:constpossible_keys:PRIMARY,uk_idkey:PRIMARYkey_len:4ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warn