论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2010-02-09 15:39 |只看该作者 |倒序浏览

本帖最后由 justin033 于 2010-04-26 23:27 编辑

一。两个表和索引的情况：

表1，l1055_subscriber，相关表情况和索引情况如下：

mysql> show table status like 'l1055_subscriber'\G
*************************** 1. row ***************************
         Name: l1055_subscriber
      Engine: MyISAM
      Version: 10
   Row_format: Dynamic
         Rows: 40000
Avg_row_length: 37
Data_length: 1518892
Max_data_length: 281474976710655
Index_length: 3528704
   Data_free: 0
Auto_increment: 40001
Create_time: 2010-02-04 15:37:57
Update_time: 2010-02-04 16:00:35
   Check_time: 2010-02-09 09:41:05
   Collation: utf8_general_ci
   Checksum: NULL
Create_options:
      Comment:
1 row in set (0.00 sec)

mysql> show index from listDB_0.l1055_subscriber\G
*************************** 1. row ***************************
   Table: l1055_subscriber
  Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: subscriber_id
Collation: A
Cardinality: 40000
Sub_part: NULL
   Packed: NULL
      Null:
  Index_type: BTREE
   Comment:
*************************** 2. row ***************************
   Table: l1055_subscriber
  Non_unique: 0
Key_name: uk_email_1055
Seq_in_index: 1
Column_name: email
Collation: A
Cardinality: 40000
Sub_part: NULL
   Packed: NULL
      Null:
  Index_type: BTREE
   Comment:
*************************** 3. row ***************************
   Table: l1055_subscriber
  Non_unique: 1
Key_name: ix_l1055_se
Seq_in_index: 1
Column_name: status_id
Collation: A
Cardinality: 1
Sub_part: NULL
   Packed: NULL
      Null: YES
  Index_type: BTREE
   Comment:
*************************** 4. row ***************************
   Table: l1055_subscriber
  Non_unique: 1
Key_name: ix_l1055_se
Seq_in_index: 2
Column_name: email
Collation: A
Cardinality: 40000
Sub_part: NULL
   Packed: NULL
      Null:
  Index_type: BTREE
   Comment:
4 rows in set (0.00 sec)

表2，l1056_subscriber，相关表情况和索引情况如下：
mysql> show table status like 'l1056_subscriber'\G
*************************** 1. row ***************************
         Name: l1056_subscriber
      Engine: MyISAM
      Version: 10
   Row_format: Dynamic
         Rows: 33180
Avg_row_length: 37
Data_length: 1238176
Max_data_length: 281474976710655
Index_length: 2105344
   Data_free: 0
Auto_increment: 33181
Create_time: 2010-02-04 17:11:13
Update_time: 2010-02-04 17:11:34
   Check_time: 2010-02-09 09:41:05
   Collation: utf8_general_ci
   Checksum: NULL
Create_options:
      Comment:
1 row in set (0.00 sec)

mysql> show index from listDB_0.l1056_subscriber\G
*************************** 1. row ***************************
   Table: l1056_subscriber
  Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: subscriber_id
Collation: A
Cardinality: 33180
Sub_part: NULL
   Packed: NULL
      Null:
  Index_type: BTREE
   Comment:
*************************** 2. row ***************************
   Table: l1056_subscriber
  Non_unique: 0
Key_name: uk_email_1056
Seq_in_index: 1
Column_name: email
Collation: A
Cardinality: 33180
Sub_part: NULL
   Packed: NULL
      Null:
  Index_type: BTREE
   Comment:
*************************** 3. row ***************************
   Table: l1056_subscriber
  Non_unique: 1
Key_name: ix_l1056_se
Seq_in_index: 1
Column_name: status_id
Collation: A
Cardinality: 1
Sub_part: NULL
   Packed: NULL
      Null: YES
  Index_type: BTREE
   Comment:
*************************** 4. row ***************************
   Table: l1056_subscriber
  Non_unique: 1
Key_name: ix_l1056_se
Seq_in_index: 2
Column_name: email
Collation: A
Cardinality: 33180
Sub_part: NULL
   Packed: NULL
      Null:
  Index_type: BTREE
   Comment:
4 rows in set (0.00 sec)

二。查询的语句

mysql> explain select subscriber_id, email from l1055_subscriber
-> where status_id=0 order by email limit 3000 offset 33000\G
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: l1055_subscriber
      type: range
possible_keys: ix_l1055_se
      key: ix_l1055_se
   key_len: 2
      ref: NULL
      rows: 39999
      Extra: Using where
1 row in set (0.00 sec)

mysql> explain select subscriber_id, email from l1056_subscriber  where status_id=0 order by email limit 3000 offset 33000\G
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: l1056_subscriber
      type: ALL
possible_keys: ix_l1056_se
      key: NULL
   key_len: NULL
      ref: NULL
      rows: 33180
      Extra: Using where; Using filesort
1 row in set (0.00 sec)

请教下，为什么这两条语句，会差这么大，多一个Using filesort。我发现l1056_subscriber的记录比业务查询的记录少，如果把limit 3000改成limit 100，explain的Extra也没了Using filesort。不知道是不是这样理解，还是索引没建好，或其它的问题呢！
mysql> explain select subscriber_id, email from l1056_subscriber  where status_id=0 order by email limit 100 offset 33000\G
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: l1056_subscriber
      type: range
possible_keys: ix_l1056_se
      key: ix_l1056_se
   key_len: 2
      ref: NULL
      rows: 33177
      Extra: Using where
1 row in set (0.00 sec)

文库|博客

80273485

白手起家

论坛徽章:: 0

2楼 [报告]

发表于 2010-02-09 16:09 |只看该作者

status_id=0 的记录数有多少呢？印象中mysql有这样一个规则，当满足你查询条件的记录数达到总记录数的一定比例是，索引是用不上的，好像是1/3？不清楚具体是多少了，期待高手来解释一下。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

80273485

白手起家

论坛徽章:: 0

3楼 [报告]

发表于 2010-02-09 16:19 |只看该作者

手册里有这样一段

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

有时MySQL不使用索引，即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下，表扫描可能会更快些，因为需要的搜索要少）。然而，如果此类查询使用LIMIT只搜索部分行，MySQL则使用索引，因为它可以更快地找到几行并在结果中返回。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

justin033

小富即安

论坛徽章:: 0

4楼 [报告]

发表于 2010-02-09 16:28 |只看该作者

本帖最后由 justin033 于 2010-02-09 16:33 编辑

回复 2# 80273485

目前两个表的记录都是status_id=0，可以看show index的Cardinality数据，这样低Cardinality没必要建索引的。但业务查询有status_id=0 order by email，所以我建了联合索引，希望能直接用索引排序。如果强制走索引的话，两个explain的情况如下：

mysql> explain select subscriber_id, email from l1056_subscriber  where status_id=0 order by email limit 3000 offset 33000\G
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: l1056_subscriber
      type: ALL
possible_keys: ix_l1056_se
      key: NULL
   key_len: NULL
      ref: NULL
      rows: 33180
      Extra: Using where; Using filesort
1 row in set (0.00 sec)

mysql> explain select subscriber_id, email from l1056_subscriber force index(ix_l1056_se)  where status_id=0 order by email limit 3000
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: l1056_subscriber
      type: ref
possible_keys: ix_l1056_se
      key: ix_l1056_se
   key_len: 2
      ref: const
      rows: 33177
      Extra: Using where
1 row in set (0.00 sec)

从分析来看，应该走索引的效果，是比较好的。。。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

80273485

白手起家

论坛徽章:: 0

5楼 [报告]

发表于 2010-02-09 16:43 |只看该作者

你第一个查询里 limit 3000 offset 33000 这样，符合了手册里说的情况，mysql优化器认为不使用速度更快。

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

justin033

小富即安

论坛徽章:: 0

6楼 [报告]

发表于 2010-02-09 17:08 |只看该作者

limit已经只retrieve3000条，实现查询出来也是180条。而且前面我测试了limit 100 offset 33000的话，就走索引了

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

cenalulu

版主

论坛徽章:: 9

7楼 [报告]

发表于 2010-02-10 10:09 |只看该作者

本帖最后由 cenalulu 于 2010-02-10 10:13 编辑

limit已经只retrieve3000条，实现查询出来也是180条。而且前面我测试了limit 100 offset 33000的话，就走索 ...
justin033 发表于 2010-02-09 17:08

limit 100 offset 33000，实际上是取了33100条，然后舍去前 33000条。。。近乎于全表顺序扫描，况且status_id=0 对于优化器来说没有任何筛选数据的价值，所以优化器就不走索引了

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

justin033

小富即安

论坛徽章:: 0

8楼 [报告]

发表于 2010-02-10 12:55 |只看该作者

本帖最后由 justin033 于 2010-02-10 15:10 编辑

limit 100 offset 33000，实际上是取了33100条，然后舍去前 33000条。。。近乎于全表顺序扫描，况 ...
cenalulu 发表于 2010-02-10 10:09

cenalulu 大哥，

取100，是正常走索引，见第一贴最后的测试。

小总结下，请指正：从几次explain的情况来看，查询的记录越接近全表记录，如总记录4W，查询的条件limit 2999 offset 37000，就走不了索引；但limit 2998 offset 37000就能走索引。如下：

mysql> explain select subscriber_id, email from l1055_subscriber where status_id=0 order by email limit 2998 offset 37000\G
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: l1055_subscriber
      type: range
possible_keys: ix_l1055_se
      key: ix_l1055_se
   key_len: 2
      ref: NULL
      rows: 39999
      Extra: Using where
1 row in set (0.00 sec)

mysql> explain select subscriber_id, email from l1055_subscriber where status_id=0 order by email limit 2999 offset 37000\G
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: l1055_subscriber
      type: ALL
possible_keys: ix_l1055_se
      key: NULL
   key_len: NULL
      ref: NULL
      rows: 40000
      Extra: Using where; Using filesort
1 row in set (0.00 sec)

mysql>

其实我公司这个业务查询有order by，所以才建联合索引防止Using filesort的。但从explain显示来看，查询的记录越接近全表记录或者查询的条件范围超过全表记录总数都是实现不了走索引并排序，也始终会出现Using filesort（见手册上order by的优化）。请指正！！！

mysql> explain select subscriber_id, email from l1055_subscriber force index(ix_l1055_se) where status_id=0 order by email limit 2999 offset 37000\G
*************************** 1. row ***************************
         id: 1
  select_type: SIMPLE
      table: l1055_subscriber
      type: ref
possible_keys: ix_l1055_se
      key: ix_l1055_se
   key_len: 2
      ref: const
      rows: 39999
      Extra: Using where
1 row in set (0.00 sec)

还有一点，强制索引后的explain效果也比较好，见上面红色部分。