前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL联表查询的索引使用

MySQL联表查询的索引使用

作者头像
十毛
发布2019-03-27 14:58:00
11.3K0
发布2019-03-27 14:58:00
举报
文章被收录于专栏:用户1337634的专栏

项目中一般使用的都是单表查询,但是在一些业务场景下,偶尔会选择联表查询,一直对联表查询时如何使用索引一直感到很好奇。正好近期项目中遇到一个问题,联表查询时,没有建立索引,耗时居然达到了可耻的10分钟,所以趁机了解了一下。

表数据

一共3张表knowledge, knowledge_question, knowledge_answer,数据在6000~10000之间。

代码语言:javascript
复制
knowledge: 6126
knowledge_question:9647
knowledge_answer:8267

执行的语句:

代码语言:javascript
复制
SELECT DISTINCT(k.base_id) FROM knowledge AS k 
LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id 
LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id 
WHERE k.update_time>'2019-01-01 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00'

没有索引(只有主键)

代码语言:javascript
复制
mysql > SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time>'2019-01-01 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+---------+
| base_id |
+---------+
|     159 |
...
|     413 |
|     414 |
+---------+
145 rows in set, 3 warnings (9 min 26.57 sec)

执行时间约10分钟,查看执行计划如下:

代码语言:javascript
复制
mysql > explain SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time>'2019-01-01 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
|  1 | SIMPLE      | k     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 6238 |   100.00 | Using temporary                                              |
|  1 | SIMPLE      | q     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9540 |   100.00 | Using where; Distinct; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8410 |   100.00 | Using where; Distinct; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
3 rows in set, 4 warnings (0.00 sec)

全部都是全表扫描,根据MySQL联表查询的算法Nested-Loop Join,MySQL查询的结果集是3张表的笛卡尔积,所以效率特别低。

JOIN字段建立索引

代码语言:javascript
复制
explain SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time>'2019-01-01 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+------------------------------+
|  1 | SIMPLE      | k     | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL              | 6444 |    33.33 | Using where; Using temporary |
|  1 | SIMPLE      | a     | NULL       | ref  | idx_kid       | idx_kid | 4       | knowledge_base.k.id |    1 |    33.33 | Using where; Distinct        |
|  1 | SIMPLE      | q     | NULL       | ref  | idx_kid       | idx_kid | 4       | knowledge_base.k.id |    1 |    33.33 | Using where; Distinct        |
+----+-------------+-------+------------+------+---------------+---------+---------+-------------------+------+----------+------------------------------+
代码语言:javascript
复制
执行结果
+---------+
| base_id |
+---------+
|     159 |
...
|     413 |
|     414 |
+---------+
145 rows in set (0.02 sec)

耗时变成20毫秒

Where条件建立索引

给Where条件建立索引,并不一定会使用。 比如:在表knowledge的字段update上建立索引idx_time

代码语言:javascript
复制
MySQL [knowledge_base]> alter table knowledge add index idx_time(update_time);

MySQL [knowledge_base]> explain SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time>'2019-01-03 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+----+-------------+-------+------------+------+------------------+---------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys    | key     | key_len | ref               | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+------------------+---------+---------+-------------------+------+----------+------------------------------+
|  1 | SIMPLE      | k     | NULL       | ALL  | PRIMARY,idx_time | NULL    | NULL    | NULL              | 6444 |    19.01 | Using where; Using temporary |
|  1 | SIMPLE      | a     | NULL       | ref  | idx_kid          | idx_kid | 4       | knowledge_base.k.id |    1 |    33.33 | Using where; Distinct        |
|  1 | SIMPLE      | q     | NULL       | ref  | idx_kid          | idx_kid | 4       | knowledge_base.k.id |    1 |    33.33 | Using where; Distinct        |
+----+-------------+-------+------------+------+------------------+---------+---------+-------------------+------+----------+------------------------------+

结果执行上来看,并没有使用索引idx_time

如果where条件从k.update_time>'2019-01-03 12:00:00'修改为k.update_time='2019-01-03 12:00:00'(从>变成=

代码语言:javascript
复制
MySQL [knowledge_base]> explain SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q.knowledge_id LEFT JOIN knowledge_answer AS a ON k.id=a.knowledge_id WHERE k.update_time='2019-01-03 12:00:00' AND q.update_time>'2019-01-01 12:00:00' AND a.update_time>'2019-01-01 12:00:00';
+----+-------------+-------+------------+------+------------------+----------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys    | key      | key_len | ref               | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+------------------+----------+---------+-------------------+------+----------+-----------------------+
|  1 | SIMPLE      | k     | NULL       | ref  | PRIMARY,idx_time | idx_time | 4       | const             |    1 |   100.00 | Using temporary       |
|  1 | SIMPLE      | a     | NULL       | ref  | idx_kid          | idx_kid  | 4       | knowledge_base.k.id |    1 |    33.33 | Using where; Distinct |
|  1 | SIMPLE      | q     | NULL       | ref  | idx_kid          | idx_kid  | 4       | knowledge_base.k.id |    1 |    33.33 | Using where; Distinct |
+----+-------------+-------+------------+------+------------------+----------+---------+-------------------+------+----------+-----------------------+

则会使用索引idx_time

继续试验发现,如果在knowledge_questionknowledge_answer表上的字段update_time上建立索引,有时候会较大幅度的改变执行计划。 所以说,检查SQL语句是否用到索引,一定要用explain查看执行计划,MySQL优化器做了太多的工作了。

其他知识点

在建立索引的时候,会遇到Table Metadata Lock的问题,可以先show processlist,找到占用表锁的连接,然后kill

代码语言:javascript
复制
MySQL [(none)]> show processlist;
+---------+-----------+----------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id      | User      | Host                 | db           | Command | Time | State        | Info                                                                                                 |
+---------+-----------+----------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 3468722 | Aics_user | 10.219.153.217:46574 | knowledge_base | Query   |   94 | Sending data | SELECT DISTINCT(k.base_id) FROM knowledge AS k LEFT JOIN knowledge_question AS q ON k.id=q |

MySQL [(none)]> kill 3468722 

结论

  • 关联字段一定要添加索引
  • where条件的索引建立,一定要查看explain,mysql的工作方式经常跟我们想的不一样
  • 增加慢查询日志(dba呢?)

参考

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2019.01.12 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 表数据
  • 没有索引(只有主键)
  • JOIN字段建立索引
  • Where条件建立索引
  • 其他知识点
  • 结论
  • 参考
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档