第三章 MySQL索引

MYSQL索引课程介绍本课程主要介绍了MySQL数据库的表达式运算符、MySQL函数，索引的用法、存储过程的使用，视图和触发器等方面的知识,以应用为目标，具有较强的实践性。学习对象•希望了解MySQL数据库基本概念，常见使用方法的学生、专业人士等学习目标•在完成本章的学习后，您将能够：•了解索引的作用•掌握索引的创建、删除•了解全文索引•了解索引的使用和不足目录大纲1.为什么使用索引2.普通索引3.唯一索引4.主键索引5.全文索引6.单列索引和组合索引7.使用索引8.索引的不足•索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用，但是一般来说引起最大性能差异的都是索引的正确使用。首先使用索引来获取最大的性能提高，接着再看其它的技术是否有用。•我们为什么不对数据行进行排序从而省掉索引？这样不是也能实现同样的搜索速度的改善吗？是的，如果表只有一个索引，这样做也可能达到相同的效果。但是你可能添加第二个索引，那么就无法一次使用两种不同方法对数据行进行排序了（例如，你可能希望在顾客名称上建立一个索引，在顾客ID号或电话号码上建立另外一个索引）。•把与数据行相分离的条目作为索引解决了这个问题，允许我们创建多个索引。此外，索引中的行一般也比数据行短一些。当你插入或删除新的值的时候，移动较短的索引值比移动较长数据行的排序次序更加容易。1为什么使用索引•不同的MySQL存储引擎的索引实现的具体细节信息是不同的。例如，对于MyISAM数据表，该表的数据行保存在一个数据文件中，索引值保存在索引文件中。一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。索引文件中的每个索引都包含一个排序的键记录（它用于快速地访问数据文件）数组。•BDB和InnoDB存储引擎没有使用这种方法来分离数据行和索引值，尽管它们也把索引作为排序后的值集合进行操作。在默认情况下，BDB引擎使用单个文件存储数据和索引值。InnoDB使用单个数据表空间（tablespace），在表空间中管理所有InnoDB表的数据和索引存储。我们可以把InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一个表空间文件中。1为什么使用索引•单个表查询环境下，通过减少对整个表的扫描，使用索引明显地提高了搜索的速度。当你运行涉及多表联结（jion）查询的时候，索引的价值就更高了。在单表查询中，你需要在每个数据列上检查的值的数量是表中数据行的数量。在多表查询中，这个数量可能大幅度上升，因为这个数量是这些表中数据行的数量所产生的。•为了演示索引的效果，向表student中先插入10000条数据。可以编写如下的存储过程来实现（存储过程会在以后的课程讲解）1为什么使用索引delimiter$$createprocedureinsertt()begindeclareiintdefault0;ins:loopseti=i+1;ifi10000thenleaveins;endif;insertintostudentvalues(i/2,concat(‘zyj’,'',i),’nointro’,60);endloopins;end$$1为什么使用索引如果出现Columncountdoesn'tmatchvaluecountatrow1错误，则是由于写的SQL语句里列的数目和后面的值的数目不一致，请查正修改。然后执行如下语句，调用储存过程。delimiter;callinsertt();在查找name=zyj9991的记录时，没有用索引时，MySql会扫描所有记录，即要查询10000次。查询所需时间为0.03秒。1为什么使用索引如果在name上已经建立了索引，MySql几乎无须任何扫描，即准确可找到该记录！1为什么使用索引这是最基本的索引，它没有任何限制。它有以下几种创建方式：•创建索引：CREATEINDEXindexNameONtableName(tableColumns(length));如果是CHAR,VARCHAR类型，length可以小于字段实际长度;如果是BLOB和TEXT类型，必须指定length，下同。2普通索引•修改表结构：ALTERtabletableNameADDINDEX[index_name][index_type](index_col_name,...)在列id上添加索引如果不指定索引的名字，则与列名一致。2普通索引•创建表的时候直接指定：CREATETABLEtableName([...],INDEX[indexName](tableColumns(length));2普通索引它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：•创建索引：CREATEUNIQUEINDEXindexNameONtableName(tableColumns(length))这里以前面所学的dept表为例，要注意表中的列要具有唯一值。3唯一索引•修改表结构：ALTERtabletableNameADDUNIQUEINDEX[index_name][index_type](index_col_name,...)3唯一索引•创建表的时候直接指定：CREATETABLEtableName([...],UNIQUEINDEX[indexName](tableColumns(length));createtablepers(idint(4)default5,namevarchar(30),uniqueindexvindex(name));3唯一索引•它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：createtabletestIndex(idintnotnullauto_increment,namevarchar(20)notnull,primarykey(id));在MYSQL中，当你建立主键时，索引同时也已建立起来了。不必重复设置。记住：一个表只能有一个主键。当然也可以用ALTER命令来创建主键索引。4主键索引很多互联网应用程序都提供了全文搜索功能，用户可以使用一个词或者词语片断作为查询项目来定位匹配的记录。在后台，这些程序使用在一个SELECT查询中的LIKE语句来执行这种查询，尽管这种方法可行，但对于全文查找而言，这是一种效率极端低下的方法，尤其在处理大量数据的时候。MySQL针对这一问题提供了一种基于内建的全文查找方式的解决方案。在此，开发者只需要简单地标记出需要全文查找的字段，然后使用特殊的MySQL方法在那些字段运行搜索，这不仅仅提高了性能和效率（因为MySQL对这些字段做了索引来优化搜索），而且实现了更高质量的搜索，因为MySQL使用自然语言来智能地对结果评级，以去掉不相关的项目。5全文索引全文索引在MySQL中是一个FULLTEXT类型索引。FULLTEXT索引用于MyISAM表，可以在CREATETABLE时或之后使用ALTERTABLE或CREATEINDEX在CHAR、VARCHAR或TEXT列上创建。对于大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE(或CREATEINDEX)创建索引，这将是非常快的。将数据装载到一个已经有FULLTEXT索引的表中，将是非常慢的。全文搜索通过MATCH()函数完成。5全文索引创建全文索引的过程，主要有两种办法：第一种是创建表的时候就创建全文索引，第二种类是在创建表以后再增加全文索引，通过前面的介绍我们知道后者比前者有些好处。先创建表，然后通过AlTERTABLE增加全文索引--创建数据库createdatabaseftt;--使用数据库useftt;--创建表createtablereviews(idint(5)primarykeynotnullauto_increment,datatext)ENGINE=MyISAMDEFAULTCHARSET=utf8;5全文索引往表里插入数据INSERTignoreINTOreviews(id,data)VALUES(1,'GingerboyhasanewsingleoutcalledThrowingRocks.It\'sgreat!');INSERTignoreINTOreviews(id,data)VALUES(2,'Helloall,IreallylikethenewMadonnasingle.Oneofthehottesttrackscurrentlyplaying...I\'vebeenlisteningtoitallday');INSERTignoreINTOreviews(id,data)VALUES(3,'HaveyouheardthenewbandHotterThanHell?Theyhavefivemembersandtheyburntheirinstrumentswhentheyplayinconcerts.Theseguystotallyrock!Like,awesome,dude!');5全文索引通过alter增加全文索引,altertablereviewsaddfulltextindex(data);通过match和against实现全文检索selectidfromreviewswherematch(data)against('Madonna');5全文索引搜索出包含Madonna或者instruments的所有记录的id号selectidfromreviewswherematch(data)against('+Madonna+instruments');5全文索引检索出包含Mado的所有记录的idselectidfromreviewswherematch(data)against('Mado*'inbooleanmode);5全文索引删除索引的语法：DROPINDEXindex_nameONtableName5全文索引索引分单列索引和组合索引单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引：即一个索包含多个列。为了对比两者，再建一个表：CREATETABLEmyIndex(i_testIDINTNOTNULLAUTO_INCREMENT,vc_NameVARCHAR(50)NOTNULL,vc_CityVARCHAR(50)NOTNULL,i_AgeINTNOTNULL,i_SchoolIDINTNOTNULL,PRIMARYKEY(i_testID));在这10000条记录里面7上8下地分布了5条vc_Name=erquan的记录，只不过city,age,school的组合各不相同。6单列索引和组合索引来看这条T-SQL：SELECTi_testIDFROMmyIndexWHEREvc_Name='erquan'ANDvc_City='郑州'ANDi_Age=25;首先考虑建单列索引：在vc_Name列上建立了索引。执行T-SQL时，MYSQL很快将目标锁定在了vc_Name=erquan的5条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉vc_City不等于郑州的记录，再排除i_Age不等于25的记录，最后筛选出唯一的符合条件的记录。虽然在vc_Name上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在vc_City和i_Age分别建立的单列索引的效率相似。6单列索引和组合索引为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将vc_Name,vc_City,i_Age建到一个索引里：ALTERTABLEmyIndexADDINDEXname_city_age(vc_Name(10),vc_City,i_Age);--注意了，建表时，vc_Name长度为50，这里为什么用10呢？因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引