以下查询在MySQL 5.0.67中执行0.3秒,在MySQL 5.1.56中执行3.0秒。为什么在更高的版本中会慢十倍?
(数据库是从5.0.67导出的,并导入到5.1.56中,因此结构相同。)
SET @num :=0, @current_shop_id := NULL, @current_product_id := NULL;
#this query limits the results of the query within it by row number (so that only 10 products get displayed per store)
SELECT * FROM (
#this query adds row numbers to the query within it
SELECT *, @num := IF( @current_shop_id = shop_id, IF(@current_product_id=product_id,@num,@num+1), 0) AS row_number, @current_shop_id := shop_id AS shop_dummy, @current_product_id := product_id AS product_dummy FROM (
SELECT shop, shops.shop_id AS
shop_id, p1.product_id AS product_id
FROM products p1
INNER JOIN sex ON
sex.product_id=p1.product_id AND
sex.sex=0 AND
sex.date >= (SUBDATE(NOW(),INTERVAL 7 DAY)) INNER JOIN
shops ON
shops.shop_id = p1.shop_id
ORDER BY shop
) AS testtable
) AS rowed_results WHERE
rowed_results.row_number>=0 AND
rowed_results.row_number<(0+10)在5.0.67中解释此查询的计划
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5433 Using where
2 DERIVED <derived3> ALL NULL NULL NULL NULL 5433
3 DERIVED sex ALL product_id_2,product_id NULL NULL NULL 379571 Using where; Using temporary; Using filesort
3 DERIVED p1 ref PRIMARY,shop_id,shop_id_2,product_id,shop_id_3 product_id 4 mydatabase.sex.product_id 1
3 DERIVED shops eq_ref PRIMARY PRIMARY 4 mydatabase.p1.shop_id 1 在5.1.56中解释此查询的计划
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 491 Using where
2 DERIVED <derived3> ALL NULL NULL NULL NULL 491
3 DERIVED shops ALL PRIMARY NULL NULL NULL 163 Using filesort
3 DERIVED p1 ref PRIMARY,shop_id,shop_id_2,product_id,shop_id_3 shop_id_2 4 mydatabase.shops.shop_id 41
3 DERIVED sex eq_ref product_id_2,product_id product_id_2 5 mydatabase.p1.product_id 1 Using where索引
5.0.67的产品表
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 502437 product_id
shop_id UNIQUE 502437 shop_id
link
title_2 UNIQUE 502437 title
image
brand INDEX 38649 brand
title INDEX 251218 title
date INDEX 125609 date
shop_id_2 INDEX 87 shop_id
product_id INDEX 502437 product_id
date
shop_id_3 INDEX 125609 shop_id
date
sale_date INDEX 187 sale_date5.1.61的产品表
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No product_id 493078 A
Edit Drop shop_id BTREE Yes No shop_id 0 A
link 493078 A
Edit Drop title_2 BTREE Yes No title 0 A
image 493078 A
Edit Drop brand BTREE No No brand 123269 A YES
Edit Drop title BTREE No No title 493078 A
Edit Drop date BTREE No No date 41089 A
Edit Drop shop_id_2 BTREE No No shop_id 12026 A
Edit Drop product_id BTREE No No product_id 493078 A
date 493078 A
Edit Drop shop_id_3 BTREE No No shop_id 12026 A
date 49307 A
Edit Drop sale_date BTREE No No sale_date 5940 A5.0.67商店表
Keyname Type Cardinality Action Field
PRIMARY PRIMARY 163 shop_id5.161商店排行榜
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No shop_id 163 A5.0.67的性别表
Keyname Type Cardinality Action Field
product_id_2 UNIQUE 506094 product_id
sex
product_id INDEX 506094 product_id5.1.61的性别表
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop product_id_2 BTREE Yes No product_id 0 A
sex 496732 A
Edit Drop product_id BTREE No No product_id 496732 AMySQL 5.1.61的my.cnf文件
[mysqladmin]
user=username
[mysqld]
basedir=/opt/bitnami/mysql
datadir=/opt/bitnami/mysql/data
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
character-set-server=UTF8
collation-server=utf8_general_ci
max_allowed_packet=16M
wait_timeout = 120
long_query_time = 1
log_slow_queries
log_queries_not_using_indexes
query_cache_limit=2M
query_cache_type=1
query_cache_size=8M
innodb_additional_mem_pool_size=8M
innodb_buffer_pool_size=16M
#innodb_log_file_size=128M
#tmp_table_size=64M
#max_connections = 2500
#max_user_connections = 2500
#innodb_flush_method=O_DIRECT
#key_buffer_size=64M
[mysqld_safe]
mysqld=mysqld.bin
[client]
default-character-set=UTF8
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid-file=/opt/bitnami/mysql/tmp/manager.pid
default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin我注意到,如果删除用于添加行号的外部查询,时间差将从0.7秒降至0.3秒。
SELECT shop, shops.shop_id AS
shop_id, p1.product_id AS product_id
FROM products p1
INNER JOIN sex ON
sex.product_id=p1.product_id AND
sex.sex=0 AND
sex.date >= (SUBDATE(NOW(),INTERVAL 7 DAY)) INNER JOIN
shops ON
shops.shop_id = p1.shop_id
ORDER BY shop发布于 2012-11-06 02:42:19
新数据库中的一些索引显示基数为0,而旧数据库中的索引显示一个很大的值。这可能意味着新数据库对索引的效率有一个错误的认识。分析该表将更新有关该表所持有的值的信息,以便数据库知道要选择哪个索引。
跑
ANALYZE TABLE table_name你拥有的每一张桌子。或者,您可以使用一条命令分析所有表:
ANALYZE TABLE products, shops, ...参考文献:http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html
发布于 2012-11-05 21:55:54
尝试放入一些使用索引(http://dev.mysql.com/doc/refman/5.1/en/index-hints.html),并尝试找到正确的索引使用顺序。在大型查询中,mysql通常很难找到正确的索引。
https://stackoverflow.com/questions/13224177
复制相似问题