免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 2552 | 回复: 9

两个表的天差地别的索引走向 [复制链接]

论坛徽章:
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)

论坛徽章:
0
发表于 2010-02-09 16:09 |显示全部楼层
status_id=0 的记录数有多少呢?印象中mysql有这样一个规则,当满足你查询条件的记录数达到总记录数的一定比例是,索引是用不上的,好像是1/3?不清楚具体是多少了,期待高手来解释一下。

论坛徽章:
0
发表于 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则使用索引,因为它可以更快地找到几行并在结果中返回。

论坛徽章:
0
发表于 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)


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

论坛徽章:
0
发表于 2010-02-09 16:43 |显示全部楼层
你第一个查询里 limit 3000 offset 33000 这样,符合了手册里说的情况,mysql优化器认为不使用速度更快。

论坛徽章:
0
发表于 2010-02-09 17:08 |显示全部楼层
limit已经只retrieve3000条,实现查询出来也是180条。而且前面我测试了limit 100 offset 33000的话,就走索引了

论坛徽章:
9
每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25
发表于 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 对于优化器来说没有任何筛选数据的价值,所以优化器就不走索引了

论坛徽章:
0
发表于 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效果也比较好,见上面红色部分。

论坛徽章:
9
每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00每日论坛发贴之星
日期:2016-01-04 06:20:00数据库技术版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00IT运维版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00综合交流区版块每日发帖之星
日期:2016-01-04 06:20:00数据库技术版块每周发帖之星
日期:2016-03-07 16:30:25
发表于 2010-02-10 17:43 |显示全部楼层
cenalulu 大哥,

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

小总结下,请指正:从几 ...
justin033 发表于 2010-02-10 12:55



    哈哈~取100的那个我只是拿来打比方拉,就是说:如果优化器认为你offset+limit太多,还不如直接走全表的读,而不是索引。
至于优化器对于 全表 还是 索引的取舍规则,还是得看内部代码和具体的数据情况。

现在语句使用索引的情况不可预测,还是建议直接加 force index 作为hint咯~~

论坛徽章:
0
发表于 2010-02-10 20:13 |显示全部楼层
也只能加hint了
哪位CUer介绍下优化器,哈哈
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP