当前位置: 首页 > 科技观察

MySQL创建索引提高多表查询效率

时间:2023-03-12 20:50:02 科技观察

简化模型,假设有三张表:tblA,tblB,tblC。每个表包含三列:col1、col2、col3。不考虑表的其他属性。在不创建索引的情况下,我们使用如下语句关联三张表:SELECT*FROMtblA,5tblB,6tblC7WHEREtblA.col1=tblB.col19ANDtblA.col2=tblC.col1;使用EXPLAIN命令查看语句的处理:+-------+-----+-------------+-----+--------+------+------+------------+|表|类型|可能的键|钥匙|密钥长度|参考|行|额外|+--------+------+----------------+------+--------+------+------+------------+|tblA|ALL|NULL|NULL|NULL|NULL|1000|||tblB|ALL|NULL|空|空|空|1000|在哪里使用||表|所有|空|空|空|空|1000|在哪里使用|+------+-----+--------------+------+--------+------+------+------------+关于EXPLAIN命令中各参数的具体含义见阿里博文[http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html]querymechanism命令的查询机制可以参考MySQLmanual(7.2.1)中的A语句:按照MySQL在处理查询时读取它们的顺序输出。MySQL解析所有连接using单扫描多连接方法。这意味着MySQL从第一个表中读取一行,然后在第二个表中找到匹配的行,然后在第三个表中,依此类推。当所有的表都被处理完后,MySQL将选择的列输出,并在表列表中回溯,直到找到一个有更多匹配行的表。从此表中读取下一行,然后继续处理下一个表。上面说了,MySQL按照tblA,tblB,tblC的顺序依次读取数据。从EXPLAIN输出的信息结构来看,利用上一张表查询到的数据来查找当前表对应的内容,即利用tblA的值在tblB中查找满足条件的值,并将该值tblB的用于查找tblC中满足条件的值。而当一次查找完成后(即三张表的值都查找过一次),MySQL不会返回tblA中的下一条数据重新开始,而是继续返回tblB中的数据来看tblB是否还有其他行的值与tblA匹配,如果有,就去tblC重复这个过程。这整个过程的关键原理是:利用从上一张表中查询到的数据,找到当前表对应的内容。在了解了MySQL在执行多表查询时,是使用上一张表查询的数据来查找当前表对应内容的原理后,创建Index的目的就是告诉MySQL如何直接查找下一张表的对应数据,如何按照MySQL所需的数据顺序关联(JOIN)一个表。再举刚才的例子,tblA和tblB两张表是通过条件“tblA.col1=tblB.col1”关联起来的。我们首先得到tblA.col1,然后MySQL需要来自tblB.col1的值,所以我们为它创建INDEXtblB.col1。创建索引后,EXPLAIN前的查询命令为:+--------+------+-------------+----------+--------+-------------+-----+------------+|表|类型|可能的键|钥匙|密钥长度|参考|行|额外|+------+-----+-------------+---------+--------+----------+------+------------+|待定|所有|空|空|空|空|1000|||待定|ndx_col1|ndx_col1|5|tblA.col1|1|在哪里使用||表|所有|空|空|空|空|1000|在哪里使用|+--------+------+---------------+--------+--------+------------+------+------------+从结果可以看出,MySQL现在使用key'ndx_col1'将tblB关联到tblA。也就是说,MySQL在查找tblB中的每一行数据时,直接使用key'ndx_col1'对应的tblA.col1来查找对应的行,而不是像之前那样进行全表扫描查找。举个例子来说明用法。USING选择的参数要求每个表通用,每个表中的值不重复,保证索引最高。join(PRIMARY)中的PRIMARY参数是索引的名称。在表的属性中,作为索引,需要选择PK属性作为参数,即PrimaryKey。勾选telnum为主键,需要把Defaultvalue中默认的NULL去掉,PRIMARY_KEY不允许NULL值。为每张表创建Index值后,EXPLAIN的输出为:对于MySQL来说,无论多么复杂的查询,只需要按照每次EXPLAIN显示的顺序关联两张表的内容即可。创建索引是为了让MySQL可以利用已经找到的内容,快速找到下一张表对应的行内容。原文链接:http://www.cnblogs.com/dwayne/archive/2012/07/06/MySQL_index_join_wayne.html【编辑推荐】MySQL源码学习:MDL字典锁MySQLCluster开发环境简明部署MySQL的四种不同查询分析MySQL集群7.2Oracle推出的MySQL内存表的特点和使用介绍