MySql复杂查询优化分析(附测试数据表生成代码)

MySql自连接查询分析(附测试数据表生成代码)

在MySql中，对于同样的查询结果（目的），使用不同的查询语句所需要得时间开销是不同的

场景1 查找新用户

1.1 场景描述

有这样一个数据表，记录用户的每一次使用信息（表中数据可通过文章后面附录提供的python程序添加）

现在需要在这个表中找到在某一天(2020-06-01)新增的用户列表，即当且仅当他最早的logTime处于2020-06-01 00:00:00和2020-06-02 00:00:00之间

下面有两种查找思路

1.2 先查找出所有用户的第一次使用时间，再筛选出第一次使用日期在这一天的用户

查找所有用户的第一次使用时间

SELECT uid,MIN(logTime) FROM t1 GROUP BY uid

再从中筛选出第一次使用时间在这一天的

SELECT a.uid,a.mt FROM 
(SELECT uid,MIN(logTime) AS mt FROM t1 GROUP BY uid) AS a 
WHERE a.mt BETWEEN "2020-06-01" AND "2020-06-02"

1.3 先查找出当天产生了使用信息的所有用户列表，再从中筛选出新用户

查找当天产生了使用信息的所有用户：

SELECT uid, logTime FROM t1 WHERE logTime BETWEEN "2020-06-01" AND "2020-06-02" GROUP BY uid

再从中筛选出新用户

SELECT a.uid,a.mt FROM
((SELECT uid,MIN(logTime) AS mt FROM t1 GROUP BY uid) AS a inner JOIN 
(SELECT uid FROM t1 WHERE logTime BETWEEN "2020-06-01" AND "2020-06-02" GROUP BY uid) AS b ON 
a.=b.uid) WHERE a.mt BETWEEN "2020-06-01" AND "2020-06-02"

1.4 初步分析与实验结果

从逻辑上看，第一种查找思路首先需要查找出所有用户的第一次使用时间，因此需要扫描所有的行（131183行）

而第二种首先通过时间条件筛选掉了所有没有在这一天产生使用记录的用户，因此只需要扫描这151行记录

那么应该是第一种查找思路的效率要更高一些，但实验结果却是第一种查找思路要更快

经过多次实验，第一种思路查找所耗费的平均时间在0.36s左右，第二种在0.42s左右

1.5 原因分析与论证

先论证以下两种解释

①数据库服务端自动对查询过程进行了优化

②第二种查找思路的查询语句较为复杂，数据库解析查询语句的时间要比执行查询过程的时间要长得多

先尝试对②进行论证，通过逐步增加表中的数据量，对比两种查找思路所耗费的时间，得下表

总记录行数	2020.6.1当天使用用户数	结果行数	思路1平均耗费时间（s）	思路2平均耗费时间（s）
131183	151	46	0.36	0.42
191127	208	27	0.45	0.54
243886	351	131	0.59	0.70
442953	461	131	1.06	1.30

可以看到随着表格规模的增大，差距仍在增大，解释②不成立

下面将使用explain语句进行优化分析

1.6 explain语句

explain语句可以对查询语句进行预分析

explain SELECT uid,col1 FROM t1 WHERE col1<10

可以显示语句预计要查找的表格和要扫描的行数等信息，试着对两种思路的语句进行explain

explain
SELECT a.uid,a.mt FROM (SELECT uid,MIN(logTime) AS mt FROM t1 GROUP BY uid) AS a WHERE a.mt BETWEEN "2020-06-01" AND "2020-06-02"

explain
SELECT a.uid,a.mt FROM
((SELECT uid,MIN(logTime) AS mt FROM t1 GROUP BY uid) AS a inner JOIN 
(SELECT uid FROM t1 WHERE logTime BETWEEN "2020-06-01" AND "2020-06-02" GROUP BY uid) AS b ON 
a.uid=b.uid) WHERE a.mt BETWEEN "2020-06-01" AND "2020-06-02"

可以看到第二种思路并没有起到缩小查找范围的目的，相比于第一种查询方式还多了一个需要全局扫描的子查询，注意到所有的查询类型（type字段）都是ALL，接下来考虑加入索引来对查询进行优化

1.7 利用索引进行查询优化

sql中的索引可以简单理解为提前将查找过程中的筛选条件按照一定的规则排列好，或者说根据筛选条件中特定的列将原始表做一个数据字典，查找的时候就可以按照该列的索引快速定位到要找的列，这里的规则一般有B-树、Hash映射和R-树，先看一个简单的列子

有一个表格t2如下:

SELECT * FROM t2

在没有创建索引前查找score小于80的记录，并用explain分析该语句

explain SELECT * FROM t2 WHERE score<80

这里的筛选条件是score，那就对score建立一个简单的B-树索引，再进行分析

CREATE INDEX score_idx ON t2(score)
explain SELECT * FROM t2 WHERE score<80

发现除了possible_keys有变化其它没什么改变，这是因为select * 查找了所有的列，将索引删除重新建一个包含所有列的新索引

drop INDEX score_idx ON t2
CREATE INDEX score_idx ON t2(score,uid,sex)
explain SELECT * FROM t2 WHERE score<80

预计扫描的行变成了3行，type也变成了range，利用索引的确达到了优化特定查询的目的，更多关于索引的细节可参考这篇博客

接下来对之前的查询建立索引来优化查询

CREATE INDEX time_uid ON t1(logTime,uid)

explain SELECT a.uid,a.mt FROM (SELECT uid,MIN(logTime) AS mt FROM t1 GROUP BY uid) AS a WHERE a.mt BETWEEN "2020-06-01" AND "2020-06-02"

explain
SELECT a.uid,a.mt FROM
((SELECT uid,MIN(logTime) AS mt FROM t1 GROUP BY uid) AS a inner JOIN 
(SELECT uid FROM t1 WHERE logTime BETWEEN "2020-06-01" AND "2020-06-02" GROUP BY uid) AS b ON 
a.uid=b.uid) WHERE a.mt BETWEEN "2020-06-01" AND "2020-06-02"

加入索引后第一种查询思路基本没有什么变化，但第二种查询思路只需要进行一次全表扫描再加上三次小范围的扫描，有了很明显的优化，执行时间也缩短了

	加入索引前的时间	加入索引后的时间
第一种	1.06	1.09
第二种	1.30	1.09

虽然还是没有通过复杂的查询语句实现加速查询的效果，但至少可以跟简单查询持平了，深层次的原因以后有时间再做研究

附录1 python随机生成表格记录代码

首先需要在数据库中新建相应的表格

import pymysql
import random
import calendar
import datetime

mysqlHost="*.*.*.*"
mysqlPort=3306
mysqlTable="t1"
mysqlDB="***"
userName="****"
userPwd="*****"

#随机生成时间字符串
def creatTimeStrByChance():
    yearNum=random.randint(2017,2020)
    monthNum=random.randint(1,12)
    dayNum=random.randint(1,calendar.monthrange(yearNum,monthNum)[1])
    hNum=random.randint(0,23)
    mNum=random.randint(0,59)
    sNum=random.randint(0,59)
    mydate=datetime.datetime(yearNum,monthNum,dayNum,hNum,mNum,sNum)
    return mydate.strftime("%Y-%m-%d %H:%M:%S")

#随机生成id
def creatUidByChance():
    uid="A"
    for i in range(3):
        uid+=("%s"%random.randint(0,9))
    return uid

conn1=pymysql.connect(mysqlHost,user=userName,passwd=userPwd,db=mysqlDB,charset='utf8',port=mysqlPort)
cur1=conn1.cursor()#游标

#插入100000条数据
for i in range(100000):
    # print(creatTimeStrByChance(),creatUidByChance())
    myqury="INSERT INTO analyse.t1(uid,logTime,col1) VALUES(\"%s\",\"%s\",%s)"%(creatUidByChance(),creatTimeStrByChance(),random.randint(0,100))
    cur1.execute(myqury)
    conn1.commit()

conn1.close()