MySQL服务器版本5.5,我正在使用mysql工作台,下面是我的查询,它需要13分钟才能执行,请建议我为所涉及的所有列创建索引的最佳方法.
Mysql server version 5.5 and I am using mysql workbench below is my query it is taking 13 min to execute, please suggestion the best method I have created the indexes for all the columns which are involved.
推荐答案您主要需要在联接和where子句中使用的列上建立索引.其他索引不会为您的select语句增加价值,也不会降低插入和更新的速度.
You mainly need indexes on columns that are used in joins and in your where clause. Other indexes don't add value for your select statements and slow down your inserts and updates.
在这种情况下,您将在函数中使用列值.因此,无法有效使用索引.
In this case, you're using the column values in functions. Due to this the indexes cannot be used efficiently.
这样的表达式效率很低:
An expression like this is very inefficient:
DATE_FORMAT(ACT.TRANDATE,'%Y-%m-%d')<='2013-05-09'由于所有TRANDATES都转换为其值的字符串表示形式,因此会导致很多字符串转换.这些值需要临时存储并且不进行索引,因此除转换外,不再使用ACT.TRANDATE上的任何索引.这可能会导致在您的解释计划末尾相当昂贵的使用连接缓冲区".
It causes a lot of string conversions, because all TRANDATES are converted to a string representation of their value. These values need to be temporarily stored and are not indexed, so apart from the conversion, any index on ACT.TRANDATE is no longer used. That is probably causing the rather expensive 'Using join buffer' at the end of your explain plan.
而是将字符串"2013-05-09"转换为日期值,并将该值用作查询中的常量或参数.
Rather convert the string '2013-05-09' to a date value and use this value as a constant in or parameter for your query.
另一件事是,不是为单独的列创建单独的索引,而是为在where和/或联接中使用的一组列创建一个索引.例如这部分:
Another thing to do, is create not separate indexes for separate columns, but one index for a group of columns that is used in a where and/or join. For instance this part:
AD.ENTITY = 'APPLICANT' AND AD.ENTITYCODE = AP.CODE AND AD.VILLAGE_CODE = VI.CODE在ENTITY,ENTITYCODE和VILLAGE_CODE列上同时具有一个索引要比为它们中的每一个都拥有单独的索引更有效.并且可能也包括其他列.
Having one index on the columns ENTITY, ENTITYCODE, and VILLAGE_CODE together would be more efficient than having a separate index for each of them. And it may help to include the other columns as well.
最后:如果保证一个列或多个列的组合是唯一的,请添加唯一索引.选择时速度稍快.
And last: If a column or combination of columns is guaranteed to be unique, ad a unique index. It is slightly faster in selects.
一般建议:不要将旧的联接语法与ansi联接混合使用.它使您的查询难以阅读.
A general advise: Don't mix old join syntax with ansi joins. It makes your query hard to read.
这些提示(除了最后一个提示)应该可以加快查询速度,但是它仍然很慢,具体取决于数据量,硬件和负载.
These hints (apart from the last one) should speed up your query, but it can still be slow, depending on the amount of data, the hardware and the load.
更多推荐
MySQL查询需要更多时间执行?
发布评论