索引与执行计划

文章未经允许,不可转载
这是一个系列,更多内容请阅读数据分析师-SQL系列

写在前面

当数据越来越多,查询越来越慢,也越来越担心部署的数据清晰和分析的脚本,可以搜一下如何改善查询速度,最常见的答案就是建立索引。很可能这不是你需要担心的问题,而是由DBA来操心,~_~,本文适用于需要操心这么方面事情的数据分析师。

本文不讨论索引背后的数学与计算机知识,从“有效利用索引可以提高查询速度”这个事实出发,讨论2个问题

  • 如何建立索引和利用索引
  • 如何判断确实使用了索引

建立索引

以单列索引为例

1
create index idx_name  on table_name (column_name)

解释:在table_name表的column_name列上,创建索引,索引名称为idx_name

创建索引之前默念一句话:业务优化是顶级优化,先了解需求,再动手创建索引
业务需求,需要创建几个索引,并不是业务需求中的所有字段都来一遍索引。邮件里的需求一大堆,梳理业务需求后,或者业务人员沟通后,可能一个索引解决了

业务梳理完毕后,对单列建立索引,还是创建联合索引,心中自然有数

利用索引

从一个常见的误区开始说起,假设test_table中只存有2018年的数据

1
create index idx_date_time on test_table (date_time)

有一个date_time列,数据类型为datetime,对这列进行创建索引,然后执行如下sql进行查询

1
2
3
select count(distinct user_id) as user_cnt
from test_table
where month(date_time) = 1

解释:查询test_table表中,日期在1月份的用户数
右边的列用了函数,是不会用到这列的索引的,执行这个sql达不到预期的速度

创建索引后,如果想利用索引,需要记住几个原则

  • 最左前缀匹配原则
  • 警惕隐式转换
  • ….

这些内容,网上很多,有些文章写的很好,这里不再赘述

有个问题需要思考一下,这么多原则,根本就不知道这条sql都遵守了没有,心里也有没底的时候。这个时候,引出一个问题,如何判断这个查询的sql真的用了索引

执行计划

explain终于闪亮登场了,其实explain没什么太多要讲的,但是我想多说几句话,我看过很多写索引相关的文章,也有讲执行计划的文章,写的都很好。但是很少看到有讲索引和执行计划的文章。索引和执行计划一起讲,可以捅破一层窗户纸,如果只学一个,在实践中总有力不从心的时候。
把这些知识,用实践当中遇到的问题串联起来,学起来也不会觉得突兀

1
2
3
explain select count(distinct user_id) as user_cnt
from test_table
where month(date_time) = 1

结合explain的知识,看看和下面的语句有啥不同,有不同的话,研究一下就行了,explain的用处也就达到了,特别是key_len的计算方法很有意思,可以深究一下,文章结尾也会推荐一些资料。

1
2
3
4
explain select count(distinct user_id) as user_cnt
from test_table
where date_time >= '2018-01-01'
and date_time < '2018-02-01'

写在后面

本文只是串联一下思路,具体的内容,网上真心有太多的好文章,欢迎读者推荐哈

如果有一天这些文章在网络上不存在了,如果我的博客还在,这些文章对应的链接将换成我的云笔记外链

您的支持将鼓励我继续创作!