mysql - 为什么这个 MySQL 查询在 MySQL 5.1.56 中这么慢？

Question

以下查询在 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_date

5.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    A

5.0.67 的商店表

 Keyname    Type    Cardinality Action  Field
PRIMARY  PRIMARY     163             shop_id

5.161 的商店表

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
 Edit    Drop   PRIMARY BTREE   Yes No  shop_id 163 A

5.0.67 的性别表

Keyname Type    Cardinality Action  Field
product_id_2     UNIQUE  506094              product_id
sex
product_id   INDEX   506094              product_id

5.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  A

MySQL 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.3 秒而不是 0.7 秒。

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

Answer 1

新数据库中的一些索引显示基数 0，而在旧数据库中它们显示大值。这可能意味着新数据库对索引的效率有错误的认识。分析表会更新有关表中值的信息，以便数据库知道选择哪个索引。

运行

ANALYZE TABLE table_name

对于您拥有的每张 table 。或者，您可以使用一条命令分析所有表:

ANALYZE TABLE products, shops, ...

引用:http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html