索引失效&子查询-慢查询的起因

原文地址: https://blog.csdn.net/baidu_zhongce/article/details/46845157

目前，Mysql作为常用的数据库，在各类业务中被广泛运用。但是其设计并非是十全十美，一些我们认为“高效“的语句，在执行时会花费大量的时间，导致页面出现超时错误。

这里举一些简单的bad case，希望大家以后处理慢sql时能有所参考。（不想研究分析过程的，请移步总结）

 

子查询是一个坑

业务中，我们定义了两张表：用户参加项目表（user_project）和用户公会情况表（union_user）。user_project表中有user_id和project_id字段来标记用户参与项目的对应关系，同时这两个字段都为索引。而union_user表中有user_id和union_id字段来标记用户属于哪个公会，这两字段也全为索引。

那么假设我们需要查询一个“某公会的用户参与了哪一些项目“时，自然而然会想到用这样的语句进行查询：

 

1. SELECT * FROM user_project

2. WHERE user_id in

3. (SELECT user_id FROM union_user WHERE union_id=1)

​​​​​​

在我们的想象中，查询应该是这样的：

 

同时，查询中用到的键都建立了索引，所以应该相当快吧！？

但是事实上，这条语句执行的效率相当之差，执行一次花费的时间在10s以上。那么问题出在哪里呢？

我们使用测试库来分析一下这条语句：

 

可以发现user_project没有使用user_id这个键进行查询，遍历了表中所有的4条记录。

索引为啥没有起效呢？这里就要介绍两个知识点了：

• 第一个为Mysql在处理所有的查询的时候都强行转换为联接来执行，将每个查询包括多表中关联匹配，关联子查询，union，甚至单表的的查询都处理为联接，接着Mysql执行联接，把每个联接在处理为一个嵌套循环;

• 第二个知识点：在Mysql在处理子查询的时候，会将子查询改写，Mysql将会扫描外查询中的所有数据，每条数据都将会传到子查询中进行关联，子查询不能首先被执行，如果外表很大的话，那么性能上将会出现问题。

事实上，我们的查询语句被Mysql转化成如下格式进行执行：

 

1. SELECT * FROM `user_project`

2. WHERE EXISTS

3. (SELECT * FROM union_user

4. where union_id=1

5. and union_user.user_id=user_project.user_id)

​​​​​​

 

分析其执行过程，可以发现是不会经过user_id索引的：

 

如何避免子查询的这一问题呢？直接使用联表查询即可：

 

1. SELECT * FROM `user_project`

2.  INNER JOIN union_user using (user_id)

3.  where union_id=1

 ​​​​​​

 

分析该语句，发现有效使用了user_id的索引，查询遍历的行数大大降低。

索引失效的一些case

1.           使用OR

SQL如下：

 

SELECT * FROM `user_project` where id=1 or user_id=1

虽然查询所用的字段都是索引，但是最终mysql并没有使用其中任何一个。

 

2.           like查询是以%开头

SQL如下：

 

SELECT * FROM `user` WHERE `user_name` LIKE 'abc%'

 

能够正确使用索引：

 

SQL修改为：

 

SELECT * FROM `user` WHERE `user_name` LIKE '%abc'

查询时就会不使用索引

 

3.           查询字符型不用引号

如果代码使用拼串去写语句，同时根据数据的类型决定是否加上引号，那么会构造出这样的语句：

 

SELECT* FROM `user` WHERE user_name=123

 

而这样的语句实际上是不走索引的：

 

所以建议还是使用bind来进行参数传递，预防注入的同时，避免这种坑爹问题。

4.           多列索引不查询前一部分

表中的有一个多列索引status，用到了status和score两个字段：

 

如果只查询status，那么索引是有效的

 

但是只查询score这一字段，不去查status，那么这个多列索引并不会起效：

总结

总结一下以上的坑：

• 尽量使用联表查询来替代子查询，因为使用子查询会使索引失效；
• 查询条件中谨慎使用or；
• 多列索引设置时请考虑好使用场景，如果有只查其中部分字段的情况，该索引可能不会生效；
• 查询字符型时请加上引号，确保使用索引；
• 以%开头的like查询不会走索引。