mysql起别名会影响索引不_mysql – 允许在HAVING子句中使用别名的性能影响

仅关注该特定查询,并在下面加载示例数据.这确实解决了其他一些查询,例如其他人提到的count(distinct …).

HAVING中的别名似乎略微超出其替代品(取决于查询).

这使用一个预先存在的表,其中大约有500万行通过这个answer快速创建,需要3到5分钟.

结果结构：

CREATE TABLE `ratings` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`thing` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5046214 DEFAULT CHARSET=utf8;

但是使用INNODB代替.由于范围预留插入,创建预期的INNODB间隙异常.只是说,但没有区别. 470万行.

修改表以接近Tim的假定模式.

rename table ratings to students; -- not exactly instanteous (a COPY)

alter table students add column camId int; -- get it near Tim's schema

-- don't add the `camId` index yet

以下将需要一段时间.以块的形式一次又一次地运行它,否则您的连接可能会超时.超时是由于更新语句中没有LIMIT子句的500万行.注意,我们有一个LIMIT子句.

所以我们正在进行五十万行迭代.将列设置为a

随机数在1到20之间

update students set camId=floor(rand()*20+1) where camId is null limit 500000; -- well that took a while (no surprise)

继续运行上述内容,直到没有camId为空.

我跑了10次(整个过程需要7到10分钟)

select camId,count(*) from students

group by camId order by 1 ;

1 235641

2 236060

3 236249

4 235736

5 236333

6 235540

7 235870

8 236815

9 235950

10 235594

11 236504

12 236483

13 235656

14 236264

15 236050

16 236176

17 236097

18 235239

19 235556

20 234779

select count(*) from students;

-- 4.7 Million rows

创建一个有用的索引(当然插入后).

create index `ix_stu_cam` on students(camId); -- takes 45 seconds

ANALYZE TABLE students; -- update the stats: http://dev.mysql.com/doc/refman/5.7/en/analyze-table.html

-- the above is fine, takes 1 second

创建校园表.

create table campus

( camID int auto_increment primary key,

camName varchar(100) not null

);

insert campus(camName) values

('one'),('2'),('3'),('4'),('5'),

('6'),('7'),('8'),('9'),('ten'),

('etc'),('etc'),('etc'),('etc'),('etc'),

('etc'),('etc'),('etc'),('etc'),('twenty');

-- ok 20 of them

运行两个查询：

SELECT students.camID, campus.camName, COUNT(students.id) as studentCount

FROM students

JOIN campus

ON campus.camID = students.camID

GROUP BY students.camID, campus.camName

HAVING COUNT(students.id) > 3

ORDER BY studentCount;

-- run it many many times, back to back, 5.50 seconds, 20 rows of output

和

SELECT students.camID, campus.camName, COUNT(students.id) as studentCount

FROM students

JOIN campus

ON campus.camID = students.camID

GROUP BY students.camID, campus.camName

HAVING studentCount > 3

ORDER BY studentCount;

-- run it many many times, back to back, 5.50 seconds, 20 rows of output

所以时间是一样的.跑了十几次.

两者的EXPLAIN输出相同

+----+-------------+----------+------+---------------+------------+---------+----------------------+--------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------+------+---------------+------------+---------+----------------------+--------+---------------------------------+

| 1 | SIMPLE | campus | ALL | PRIMARY | NULL | NULL | NULL | 20 | Using temporary; Using filesort |

| 1 | SIMPLE | students | ref | ix_stu_cam | ix_stu_cam | 5 | bigtest.campus.camID | 123766 | Using index |

+----+-------------+----------+------+---------------+------------+---------+----------------------+--------+---------------------------------+

使用AVG()函数,使用以下两个查询中的别名(具有相同的EXPLAIN输出),性能提高了12％.

SELECT students.camID, campus.camName, avg(students.id) as studentAvg

FROM students

JOIN campus

ON campus.camID = students.camID

GROUP BY students.camID, campus.camName

HAVING avg(students.id) > 2200000

ORDER BY students.camID;

-- avg time 7.5

explain

SELECT students.camID, campus.camName, avg(students.id) as studentAvg

FROM students

JOIN campus

ON campus.camID = students.camID

GROUP BY students.camID, campus.camName

HAVING studentAvg > 2200000

ORDER BY students.camID;

-- avg time 6.5

最后,DISTINCT：

SELECT students.camID, count(distinct students.id) as studentDistinct

FROM students

JOIN campus

ON campus.camID = students.camID

GROUP BY students.camID

HAVING count(distinct students.id) > 1000000

ORDER BY students.camID; -- 10.6 10.84 12.1 11.49 10.1 9.97 10.27 11.53 9.84 9.98

-- 9.9

SELECT students.camID, count(distinct students.id) as studentDistinct

FROM students

JOIN campus

ON campus.camID = students.camID

GROUP BY students.camID

HAVING studentDistinct > 1000000

ORDER BY students.camID; -- 6.81 6.55 6.75 6.31 7.11 6.36 6.55

-- 6.45

使用相同的EXPLAIN输出,其中的别名持续运行速度提高35％.见下文.因此,相同的Explain输出已经显示两次,不会产生相同的性能,但作为一般线索.

+----+-------------+----------+-------+---------------+------------+---------+----------------------+--------+----------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+----------+-------+---------------+------------+---------+----------------------+--------+----------------------------------------------+

| 1 | SIMPLE | campus | index | PRIMARY | PRIMARY | 4 | NULL | 20 | Using index; Using temporary; Using filesort |

| 1 | SIMPLE | students | ref | ix_stu_cam | ix_stu_cam | 5 | bigtest.campus.camID | 123766 | Using index |

+----+-------------+----------+-------+---------------+------------+---------+----------------------+--------+----------------------------------------------+

优化器似乎有利于此时的别名,特别是对于DISTINCT.