mysql 数据倾斜索引_查询数据倾斜很大的值，怎么也走索引？

最新推荐文章于 2024-08-11 00:03:50 发布

原创最新推荐文章于 2024-08-11 00:03:50 发布

· 425 阅读

1 ·

版权

文章标签：

#mysql 数据倾斜索引

在测试中发现一个数据分布严重倾斜的表，对于ID字段，小值查询能有效利用索引，但大值查询却执行全表扫描。分析表明，即使调整优化器指数成本参数，大值查询仍不走索引，这可能与数据分布和查询优化策略有关。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本帖最后由 yongzhi2008 于 2012-12-28 13:57 编辑

本来做一下直方图测试，遇到了一个想不明白的问题，求助大家。

--直方图测试，建立一个数据分布严重倾斜的表，在id，name 两个字段上分别建了索引。SQL> desc t_histogram;

Name Type Nullable Default Comments

---- ------------ -------- ------- --------

ID INTEGER Y

NAME VARCHAR2(40) Y

SQL> select name, count(*) from t_histogram group by name order by 1;

NAME COUNT(*)

---------------------------------------- ----------

haohua 1

zhang 87692

SQL> select id, count(*) from t_histogram group by id order by 1;

ID COUNT(*)

--------------------------------------- ----------

1 1

100 87692

--执行计划

SQL> show parameter optimizer_index_cost_adj;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj integer 100

SQL> analyze table T_HISTOGRAM compute statistics for table for all indexes for all indexed columns;

Table analyzed

SQL> select * from t_histogram where id =1;

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_HISTOGRAM | 1 | 8 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_HISTOGRAM_ID | 1 | | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

1 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

593 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL> set arraysize 5000;

SQL> select * from t_histogram where id =100;

87692 rows selected.

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 87692 | 685K| 462 (6)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_HISTOGRAM | 87692 | 685K| 462 (6)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_HISTOGRAM_ID | 87692 | | 280 (5)| 00:00:01 |

----------------------------------------------------------------------------------------------

1 recursive calls

0 db block gets

474 consistent gets

0 physical reads

0 redo size

442213 bytes sent via SQL*Net to client

706 bytes received via SQL*Net from client

19 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

87692 rows processed

SQL> select /*+ full(t_histogram) */ * from t_histogram where id = 100;

87692 rows selected.

---------------------------------------------------------------------------------

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 87692 | 685K| 530 (3)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| T_HISTOGRAM | 87692 | 685K| 530 (3)| 00:00:01 |

---------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

208 consistent gets

0 physical reads

0 redo size

442213 bytes sent via SQL*Net to client

706 bytes received via SQL*Net from client

19 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

87692 rows processed

数据库情况：

SQL> show parameter db_file_multiblock_read_count;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_file_multiblock_read_count integer 128

SQL> show parameter db_block_size;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

SQL> select * from v$version where rownum = 1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

mysql 数据倾斜 索引_查询数据倾斜很大的值， 怎么也走索引？

mysql 数据倾斜索引_查询数据倾斜很大的值，怎么也走索引？