- 浏览: 245785 次
- 性别:
- 来自: 武汉
文章分类
最新评论
-
shangyixue:
受益了
『Hibernate』面对几个错误的解决关键 -
lection.yu:
靠谱。。这个教程可以用
IIS6与Tomcat6 -
lenomon:
找到篇简洁的 IP地址与整数之间的转换
java字符串应用之IP地址转换成整数 -
gaolinwu:
,支持一下
J2EE 13种核心技术 -
tomcat的问题:
多谢多谢
selectmethod=cursor的含义及其使用
在MySQL中如何为连接添加索引,然后再看一个有挑战性的例子。
简单的3个表的连接
表结构很简单,3个表tblA, tblB, tblC,每个表有3个字段:col1, col2, col3。
在没有索引的情况下连接3个表
explain的结果如下:
最后,在MySQL的手册中(7.2.1):
表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接(single-sweep multi-join)的方式解决所有联接。这意味着MySQL从第一个表中读一行,然后找到在第二个表中的一个匹配行,然后在第3个表中等等。当所有的表处理完后,它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。
如手册所说的,MySQL读第一个表(tnlA),然后第二个(tblB),然后第三个(tblC),像explain中输出的一样。先前的表中的值用来查找当前表中的行。在我们的例子中,tblA中的值用来找tblB中的匹配行,然后tblB的值来找tblC的行。当一个完整的扫描结束(在表tblA,tblB,tblC中找到了结果),MySQL不会返回tblA,它到tblB中查看是否有更多的行匹配当前tblA的值。如果有,它拿出这一行,然后再在tblC中找匹配的。记住 MySQL连接的基本原则是很重要的:先前的表中的值用来查找当前表中的行。
按原理建索引
知道了MySQL使用从tblA中得到的值查找tblB中的行,我们需要怎么建索引来帮助MySQL?为此我们要知道它需要什么。考虑连接tblA和 tblB:它们通过“tblA.col1 = tblB.col1”来连接。我们已经有了tblA.col1的值,所以MySQL需要一个tblB.col1的值来完成等值操作。因此如果MySQL需要tblB.col1,我们就在tblB.col1上加索引。加了之后,这是新的explain结果:
如上,MySQL现在使用ndx_col1索引来连接tblB到tblA。就是说,当MySQL要找tblB中的行时,使用了ndx_col1索引通过 tblA.col1的值直接得到匹配的行,而不是像以前需要做表扫描。这就是为什么tblB的ref列说“tablA.col1”。tblC现在还是用表扫描,这可以通过同样的方法解决。查看MySQL的需求:从sql中连接两表的语句“tblA.col2 = tblC.col1”可以看出它需要tblC.col1因为我们已经有了tblA.col2。给这一列加上索引之后explain:
更复杂的查询
在实际中不会遇到刚才那种sql。所以你可能更想看看这样的:
乍一看是很复杂的:有4个表,有聚合函数,有9个where条件,还有一个group by。explain的伟大之处在于我们现在可以忽略这些,每次只看两个表,判断每一步MySQL需要什么。这是一个实际的查询,只是字段名有一些改动。explain的结果:
判断连接影响的主要看结果集。结果集就是查询的结果。对于连接,一个估计结果集大小的方法是把MySQL预测的读取每个表的行数相乘。作为估计,这样做比较偏向于坏的情况,因为where条件通常会减少很多的行数。但这个查询的结果集有9400万行。这就是没有索引连接很危险的原因;几千行乘几千行你就会有一个上百万的结果集了。
那么现在这个查询需要什么?从tblA和tblB开始。在sql中:
MySQL 至少需要q_num, se_num, ans中的一个。我选择在se_num和q_num上加索引因为在几乎所有其他的查询中我都会需要它们。折中是优化的一部分,多数人没有时间去为每一个查询找最优的索引方案,只能是找到一个对于大多数情况而言最优的方案。在tblB上加索引(se_num, q_num),explain的结果:
现在结果集下降了99.3%变为692280行。但为什么要停在这里?我们可以很容易的解决tblA的表扫描。因为它是第一个表,我们并不需要为连接加索引,这在tblB上已经做过了。一般来说,给第一个表加索引可以把它当成只在这一个表上查询的情况。在这个例子中很幸运,tblA是:"AND tblA.ex_id = 1001"。我们只需要加ex_id索引:
现在结果集是641行。相比开始的9400万,可以说了下降了100%。如果继续研究这个查询我们还可以去掉temp table和filesort,但现在查询已经很快了,也已经说明了如何为连接加索引。尽管最初看这个查询很麻烦,但可以看到只要每次独立的看两张表,为 MySQL的需求加索引,整个过程并不困难。
结论
为复杂的连接加索引要认识到两件事:
1. 不管sql多复杂,每次只看explain中的两个表
2. 先前表中的值已经有了,我们的工作就是通过索引帮助MySQL在当前表中使用这些值来找到匹配行
简单的3个表的连接
表结构很简单,3个表tblA, tblB, tblC,每个表有3个字段:col1, col2, col3。
在没有索引的情况下连接3个表
SELECT * FROM tblA, tblB, tblC WHERE tblA.col1 = tblB.col1 AND tblA.col2 = tblC.col1;
explain的结果如下:
Java代码 +-------+------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+------+-------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | | | tblB | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | | tblC | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | +-------+------+---------------+------+---------+------+------+-------------+ +-------+------+---------------+------+---------+------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+------+-------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | | | tblB | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | | tblC | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | +-------+------+---------------+------+---------+------+------+-------------+
最后,在MySQL的手册中(7.2.1):
表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接(single-sweep multi-join)的方式解决所有联接。这意味着MySQL从第一个表中读一行,然后找到在第二个表中的一个匹配行,然后在第3个表中等等。当所有的表处理完后,它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。
如手册所说的,MySQL读第一个表(tnlA),然后第二个(tblB),然后第三个(tblC),像explain中输出的一样。先前的表中的值用来查找当前表中的行。在我们的例子中,tblA中的值用来找tblB中的匹配行,然后tblB的值来找tblC的行。当一个完整的扫描结束(在表tblA,tblB,tblC中找到了结果),MySQL不会返回tblA,它到tblB中查看是否有更多的行匹配当前tblA的值。如果有,它拿出这一行,然后再在tblC中找匹配的。记住 MySQL连接的基本原则是很重要的:先前的表中的值用来查找当前表中的行。
按原理建索引
知道了MySQL使用从tblA中得到的值查找tblB中的行,我们需要怎么建索引来帮助MySQL?为此我们要知道它需要什么。考虑连接tblA和 tblB:它们通过“tblA.col1 = tblB.col1”来连接。我们已经有了tblA.col1的值,所以MySQL需要一个tblB.col1的值来完成等值操作。因此如果MySQL需要tblB.col1,我们就在tblB.col1上加索引。加了之后,这是新的explain结果:
Java代码 +-------+------+---------------+----------+---------+-----------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+----------+---------+-----------+------+-------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | | | tblB | ref | ndx_col1 | ndx_col1 | 5 | tblA.col1 | 1 | Using where | | tblC | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | +-------+------+---------------+----------+---------+-----------+------+-------------+ +-------+------+---------------+----------+---------+-----------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+----------+---------+-----------+------+-------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | | | tblB | ref | ndx_col1 | ndx_col1 | 5 | tblA.col1 | 1 | Using where | | tblC | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | +-------+------+---------------+----------+---------+-----------+------+-------------+
如上,MySQL现在使用ndx_col1索引来连接tblB到tblA。就是说,当MySQL要找tblB中的行时,使用了ndx_col1索引通过 tblA.col1的值直接得到匹配的行,而不是像以前需要做表扫描。这就是为什么tblB的ref列说“tablA.col1”。tblC现在还是用表扫描,这可以通过同样的方法解决。查看MySQL的需求:从sql中连接两表的语句“tblA.col2 = tblC.col1”可以看出它需要tblC.col1因为我们已经有了tblA.col2。给这一列加上索引之后explain:
Java代码 +-------+------+---------------+----------+---------+-----------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+----------+---------+-----------+------+-------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | | | tblB | ref | ndx_col1 | ndx_col1 | 5 | tblA.col1 | 1 | Using where | | tblC | ref | ndx_col1 | ndx_col1 | 5 | tblA.col2 | 1 | Using where | +-------+------+---------------+----------+---------+-----------+------+-------------+ +-------+------+---------------+----------+---------+-----------+------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+----------+---------+-----------+------+-------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1000 | | | tblB | ref | ndx_col1 | ndx_col1 | 5 | tblA.col1 | 1 | Using where | | tblC | ref | ndx_col1 | ndx_col1 | 5 | tblA.col2 | 1 | Using where | +-------+------+---------------+----------+---------+-----------+------+-------------+
更复杂的查询
在实际中不会遇到刚才那种sql。所以你可能更想看看这样的:
SELECT COUNT(tblB.a_id) as correct, tblA.type, tblA.se_type FROM tblA, tblB, tblC, tblD WHERE tblA.ex_id = tblC.ex_id AND tblC.st_ex_id = tblB.st_ex_id AND tblB.q_num = tblA.q_num AND tblB.se_num = tblA.se_num AND tblD.ex_id = tblA.ex_id AND tblD.exp <> tblB.se_num AND tblB.ans = tblA.ans AND tblA.ex_id = 1001 AND tblC.r_id = 542 GROUP BY tblA.type, tblA.se_type;
乍一看是很复杂的:有4个表,有聚合函数,有9个where条件,还有一个group by。explain的伟大之处在于我们现在可以忽略这些,每次只看两个表,判断每一步MySQL需要什么。这是一个实际的查询,只是字段名有一些改动。explain的结果:
Java代码 +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1080 | Using where; Using temporary; Using filesort | | tblB | ALL | NULL | NULL | NULL | NULL | 87189 | Using where | | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where | | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where | +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+ +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1080 | Using where; Using temporary; Using filesort | | tblB | ALL | NULL | NULL | NULL | NULL | 87189 | Using where | | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where | | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where | +-------+--------+---------------+---------+---------+---------------+-------+----------------------------------------------+
判断连接影响的主要看结果集。结果集就是查询的结果。对于连接,一个估计结果集大小的方法是把MySQL预测的读取每个表的行数相乘。作为估计,这样做比较偏向于坏的情况,因为where条件通常会减少很多的行数。但这个查询的结果集有9400万行。这就是没有索引连接很危险的原因;几千行乘几千行你就会有一个上百万的结果集了。
那么现在这个查询需要什么?从tblA和tblB开始。在sql中:
AND tblB.q_num = tblA.q_num AND tblB.se_num = tblA.se_num AND tblB.ans = tblA.ans
MySQL 至少需要q_num, se_num, ans中的一个。我选择在se_num和q_num上加索引因为在几乎所有其他的查询中我都会需要它们。折中是优化的一部分,多数人没有时间去为每一个查询找最优的索引方案,只能是找到一个对于大多数情况而言最优的方案。在tblB上加索引(se_num, q_num),explain的结果:
Java代码 +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1080 | Using where; Using temporary; Using filesort | | tblB | ref | ndx_secn_qn | ndx_secn_qn | 2 | tblA.se_num,tblA.q_num | 641 | Using where | | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where | | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where | +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ | tblA | ALL | NULL | NULL | NULL | NULL | 1080 | Using where; Using temporary; Using filesort | | tblB | ref | ndx_secn_qn | ndx_secn_qn | 2 | tblA.se_num,tblA.q_num | 641 | Using where | | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where | | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where | +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+
现在结果集下降了99.3%变为692280行。但为什么要停在这里?我们可以很容易的解决tblA的表扫描。因为它是第一个表,我们并不需要为连接加索引,这在tblB上已经做过了。一般来说,给第一个表加索引可以把它当成只在这一个表上查询的情况。在这个例子中很幸运,tblA是:"AND tblA.ex_id = 1001"。我们只需要加ex_id索引:
Java代码 +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ | tblA | ref | ndx_ex_id | ndx_ex_id | 4 | const | 1 | Using where; Using temporary; Using filesort | | tblB | ref | ndx_secn_qn | ndx_secn_qn | 2 | tblA.se_num,tblA.q_num | 641 | Using where | | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where | | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where | +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+ | tblA | ref | ndx_ex_id | ndx_ex_id | 4 | const | 1 | Using where; Using temporary; Using filesort | | tblB | ref | ndx_secn_qn | ndx_secn_qn | 2 | tblA.se_num,tblA.q_num | 641 | Using where | | tblC | eq_ref | PRIMARY | PRIMARY | 4 | tblB.st_ex_id | 1 | Using where | | tblD | eq_ref | PRIMARY | PRIMARY | 4 | tblA.ex_id | 1 | Using where | +-------+--------+---------------+-------------+---------+------------------------+------+----------------------------------------------+
现在结果集是641行。相比开始的9400万,可以说了下降了100%。如果继续研究这个查询我们还可以去掉temp table和filesort,但现在查询已经很快了,也已经说明了如何为连接加索引。尽管最初看这个查询很麻烦,但可以看到只要每次独立的看两张表,为 MySQL的需求加索引,整个过程并不困难。
结论
为复杂的连接加索引要认识到两件事:
1. 不管sql多复杂,每次只看explain中的两个表
2. 先前表中的值已经有了,我们的工作就是通过索引帮助MySQL在当前表中使用这些值来找到匹配行
发表评论
-
OERR: ORA-12519 TNS:no appropriate service handler found
2012-11-29 11:32 1251采用服务动态注册的方式,由PMON 通过SERVICE_UPD ... -
selectmethod=cursor的含义及其使用
2008-09-02 17:27 11115连接字符串中碰到的SelectMethod=cursor 关键 ... -
sqlserver2000里,nvarchar、varchar有何区别;text、ntext有何区别
2008-08-05 20:36 22351.sqlserver2000里,nvarchar、varch ... -
JDBC性能优化
2008-06-22 01:42 1661jdbc程序的性能主要由两个因素决定,一是数据库本身的性质,另 ... -
JSP 链接数据库大全
2008-06-22 01:38 1468一、jsp连接Oracle8/8i/9i数据库(用thin模 ... -
MSSQL数据库小常识-自动增长类型设置
2008-06-08 17:29 2412MSSQL 如何自动生成id号? 设计表,建立个字段,数据类 ...
相关推荐
2. **唯一索引**:确保索引列中的值是唯一的,但允许值为空。 3. **复合索引**:包含多个列的索引,可用于同时基于多个字段进行查询。 4. **聚簇索引(聚集索引)**:这是一种特殊的数据存储方式,通常与主键相关联...
可以根据报错的内容,手动地在 MySQL 数据库中进行添加或修改。 Step 5: 使用 MySQL 数据库连接工具打开转后的 MySQL 数据库 使用 MySQL 数据库连接工具打开转后的 MySQL 数据库,例如 SQLyog,可以导出库的 SQL ...
5. **Ref_or_null**: 类似于`ref`,但添加了MySQL可以专门搜索包含NULL值的行的功能。 6. **Index_merge**: 表示使用了索引合并优化方法。例如: ```sql EXPLAIN SELECT * FROM t4 WHERE id = 3952602 OR ...
在这里,`mysql-connector-java-5.1.47-bin.jar`可以被添加到DolphinScheduler的类路径中,以便系统能够连接到MySQL数据库,存储和检索任务调度的相关信息。 在集成DolphinScheduler和MySQL的过程中,你需要完成...
对于已经存在的表,可以使用NAVICAT的“对象”菜单或右键快捷菜单来添加索引。首先,打开目标表,选择“设计表”,在设计视图中,你可以点击“索引/主键”标签,然后点击“新建索引”按钮。在这里,你可以定义索引...
当开发者在Java项目中使用MySQL数据库时,必须添加MySQL的JDBC驱动(即MySQL Connector/J)作为项目的依赖。 MySQL Connector/J是MySQL官方提供的Java数据库连接器,它实现了JDBC API,使得Java程序能够与MySQL...
选择源(SQL Server)和目标(MySQL)数据库,添加需要迁移的表,设置迁移选项,如是否保留自增ID,是否清空目标表等。然后,启动数据传输过程。 5. **验证和优化**: 完成迁移后,务必验证新表的数据是否正确无误...
在Java开发中,将Java实体类转换为MySQL数据库的建表语句是一项常见的任务,它有助于快速构建数据库模型,尤其在使用ORM(对象关系映射)框架如Hibernate、MyBatis时更为便捷。本篇文章将深入探讨这个过程,并提供...
输入相关参数信息,注意选择数据库,且类型为 Mysql。 在选择数据库类型时,需要选择 Mysql,然后输入相应的连接信息,如服务器名称、用户名、密码等。接着,需要选中 ODBC Admin,创建 Mysql 的 ODBC 连接。选择 ...
添加索引的语法为:`ALTER TABLE 表名 ADD 索引类型 索引名称 (列名)`。例如,添加一个名为 `idx_height` 的索引在 `test1` 表的 `height` 列上: ``` ALTER TABLE test1 ADD INDEX idx_height (height); ``` 索引...
5. **处理增量数据**: 当Solr接收到增量数据后,它会将这些数据转化为适合索引的格式,然后添加到索引中。对于更新和删除操作,需要特别处理,确保索引中的数据与数据库保持一致。 6. **监控和优化**: 为了确保系统...
安装完成后,我们可以通过以下步骤在VS中建立MySQL连接: 1. **创建新的数据连接**:在VS的“服务器资源管理器”中,右键点击“数据连接”,选择“添加连接”。在弹出的“添加连接”对话框中,选择“MySQL Data ...
MySQL数据库在日常使用中会遇到各种问题,其中索引优化是提升数据库性能的关键环节。本文将探讨MySQL的一些常见问题,特别是与索引相关的优化策略。 1. 关于MySQL `count(distinct)` 的逻辑bug 在执行`count...
1. **配置SOLR核心**:在SOLR的`solrconfig.xml`配置文件中,启用DIH,并添加相应的DataConfig标签,指定数据源类型为JDBC(Java Database Connectivity)。 2. **配置数据源连接**:在`data-config.xml`文件中,...
为了方便用户管理和操作MySQL数据库,出现了各种MySQL连接工具,本压缩包提供的“MySQL连接工具”就是这样一款实用的应用。 首先,MySQL连接工具的主要功能包括: 1. **数据库连接**:它允许用户通过输入服务器...
- 通过`ALTER TABLE`语句添加索引。 **示例:** ```sql CREATE INDEX index_name ON tbl_name (index_col_name); ``` **查看和删除索引:** - 使用`SHOW INDEX FROM 表名;`查看索引。 - 使用`DROP INDEX 索引名 ON...
在这个场景中,我们讨论的是如何结合Lucene和MySQL来实现一个Java应用程序,该程序能够从MySQL数据库中提取数据,创建索引,并进行高效的搜索。 首先,我们需要理解Lucene的工作原理。Lucene通过分析文本,将文档...
本文将详细介绍如何使用Python3连接MySQL数据库,并针对过程中可能遇到的一些常见问题提供解决方案。 #### 一、SQL简介与分类 SQL(Structured Query Language)是一种用于管理关系数据库的标准语言。尽管它不是一...
### SqlServer转MySQL:导入表结构与数据 在IT领域,数据库迁移是一项常见的任务,特别是在不同的数据库管理系统(DBMS)之间进行迁移时更是如此。本文将详细介绍如何将Microsoft SqlServer中的数据迁移到MySQL...
SphinxSE是Sphinx为MySQL提供的一个存储引擎,它允许我们在MySQL中直接查询Sphinx的索引。安装SphinxSE后,我们可以在MySQL中像操作普通表一样使用Sphinx的全文索引,这极大地方便了数据库查询和管理。 在搭建...