MySQL:联合索引与Where子句的优化

数据库 MySQL 数据库运维
经过实践发现,不要以为WHERE中的字段顺序无所谓,可以随便放在哪,应该尽可能地第一次就过滤掉大部分无用的数据,只返回最小范围的数据。

本网站系统上线至今,数据量已经不知不觉上到500M,近8W记录了。涉及数据库操作的基本都是变得很慢了,用的人都会觉得躁火~~然后把这个情况在群里一贴,包括机器配置什么的一说,马上就有群友发话了,而且帮我确定了不是机器配置的问题,“深圳-枪手”热心人他的机器512内存过百W的数据里也跑得飞快,甚至跟那些几W块的机器一样牛(吹过头了),呵呵~~~

在群友的分析指点下,尝试把排序、条件等一个一个去除来做测试,结果发现问题就出在排序部分,去除排序的时候,执行时间由原来的48秒变成0.3x秒,这是个什么档次的变化呀~~看着这个结果我激动ing.....

于是我把涉及排序的字段组成一个联合索引alter table xx add index indexname(x1,x2,x3),经过2分钟创建新索引之后再执行同一个SQL语句,哇塞0.28S。。。。爽

于是按照同样的思路把其它几个常用的SQL作了过些优化,效果马上见效

过了30分钟再查slow sql记录文件,不好了,发现原来一个好好的SQL变得灰常慢了,神马情况?

几经分析和测试原来就是因为添加了联合索引的原因,而且这个SQL语句当中有个or,当把这个or改用union之后问题排除。

这回又得出一个心得:写SQL的时候千万别一时就手,随便写个就OK,那会为以为带来很严重的后果。

再附上一段关于Where子句的执行顺序:

在用MySQL查询数据库的时候,连接了很多个用,发现非常慢。例如:

  1. SELECT ... WHERE p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1 AND p.products_id IN (472,474) 

这样查询需要20多秒,虽然在各个字段上都建立了索引。用分析Explain SQL一分析,发现在***次分析过程中就返回了几万条数据:

WHERE p.languages_id = 1 ,然后再依次根据条件,缩小范围。

而我改变一下WHERE 字段的位置之后,速度就有了明显地提高:

  1. WHERE p.products_id IN (472,474) AND 
  2. p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1 

这样,***次的条件是p.products_id IN (472,474),它返回的结果只有不到10条,接下来还要根据其它的条件来过滤,自然在速度上有了较大的提升。

经过实践发现,不要以为WHERE中的字段顺序无所谓,可以随便放在哪,应该尽可能地***次就过滤掉大部分无用的数据,只返回最小范围的数据。

希望能帮到有同样遭遇的朋友。

 

【编辑推荐】

  1. MySQL中创建及优化索引组织结构的思路
  2. 微博 请问你是怎么优化数据库的?
  3. MySQL技巧:结合相关参数 做好Limit优化
  4. MySQL数据库的优化(下)MySQL数据库的高可用架构方案
  5. MySQL数据库的优化(上)单机MySQL数据库的优化
责任编辑:艾婧 来源: 5ishare.com
相关推荐

2010-09-26 09:50:36

SQL Where子句

2009-09-10 17:30:15

LINQ Where子

2010-09-26 14:46:19

SQL WHERE子句

2009-11-19 14:59:25

Oracle Wher

2017-07-25 12:07:14

MySQL索引SQL

2021-07-26 18:23:23

SQL策略优化

2020-10-19 19:45:58

MySQL数据库优化

2011-07-28 16:16:27

MySQL数据库索引ORDER BY

2010-06-04 11:28:05

MySQL数据库

2024-02-27 09:12:24

PostgreSQL数据库查询WHERE 子句

2018-06-07 08:54:01

MySQL性能优化索引

2010-10-12 16:44:36

MySQL语句

2018-04-09 14:25:06

数据库MySQL索引

2010-05-12 11:14:25

MySQL SQL优化

2010-05-14 17:56:16

SQL优化索引

2023-10-23 09:19:47

PawSQL数据库

2023-11-07 07:50:55

LIMIT子句下推优化

2017-09-05 12:44:15

MySQLSQL优化覆盖索引

2010-10-08 16:20:35

MySQL语句

2020-05-20 18:40:11

MySQL回表与索引数据库
点赞
收藏

51CTO技术栈公众号