<bdo id='EoWcU'></bdo><ul id='EoWcU'></ul>
<tfoot id='EoWcU'></tfoot>
      <i id='EoWcU'><tr id='EoWcU'><dt id='EoWcU'><q id='EoWcU'><span id='EoWcU'><b id='EoWcU'><form id='EoWcU'><ins id='EoWcU'></ins><ul id='EoWcU'></ul><sub id='EoWcU'></sub></form><legend id='EoWcU'></legend><bdo id='EoWcU'><pre id='EoWcU'><center id='EoWcU'></center></pre></bdo></b><th id='EoWcU'></th></span></q></dt></tr></i><div id='EoWcU'><tfoot id='EoWcU'></tfoot><dl id='EoWcU'><fieldset id='EoWcU'></fieldset></dl></div>

        <small id='EoWcU'></small><noframes id='EoWcU'>

        <legend id='EoWcU'><style id='EoWcU'><dir id='EoWcU'><q id='EoWcU'></q></dir></style></legend>

        使用基数较低的索引有意义吗?

        Does it make sense to use an index that will have a low cardinality?(使用基数较低的索引有意义吗?)
          <tbody id='Tzy1u'></tbody>

            <tfoot id='Tzy1u'></tfoot>

            • <i id='Tzy1u'><tr id='Tzy1u'><dt id='Tzy1u'><q id='Tzy1u'><span id='Tzy1u'><b id='Tzy1u'><form id='Tzy1u'><ins id='Tzy1u'></ins><ul id='Tzy1u'></ul><sub id='Tzy1u'></sub></form><legend id='Tzy1u'></legend><bdo id='Tzy1u'><pre id='Tzy1u'><center id='Tzy1u'></center></pre></bdo></b><th id='Tzy1u'></th></span></q></dt></tr></i><div id='Tzy1u'><tfoot id='Tzy1u'></tfoot><dl id='Tzy1u'><fieldset id='Tzy1u'></fieldset></dl></div>

            • <legend id='Tzy1u'><style id='Tzy1u'><dir id='Tzy1u'><q id='Tzy1u'></q></dir></style></legend>

              <small id='Tzy1u'></small><noframes id='Tzy1u'>

                  <bdo id='Tzy1u'></bdo><ul id='Tzy1u'></ul>
                  本文介绍了使用基数较低的索引有意义吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我主要是一名 Actionscript 开发人员,绝不是 SQL 专家,但有时我必须开发简单的服务器端内容.所以,我想我应该向更有经验的人询问标题中的问题.

                  I'm mainly an Actionscript developer and by no means an expert in SQL, but from time to time I have to develop simple server side stuff. So, I thought I'd ask more experienced people about the question in the title.

                  我的理解是,通过在包含几个不同值的列中设置索引,您不会获得太多收益.我有一个包含布尔值的列(实际上它是一个小整数,但我将它用作标志),并且该列用于我拥有的大多数查询的 WHERE 子句中.在理论上的平均"情况下,记录的一半值为 1,另一半为 0.因此,在这种情况下,数据库引擎可以避免全表扫描,但无论如何都必须读取大量行(总行数/2).

                  My understanding is that you don't gain much by setting an index in a column that will hold few distinct values. I have a column that holds a boolean value (actually it's a small int, but I'm using it as a flag), and this column is used in the WHERE clauses of most of the queries I have. In a theoretical "average" case, half of the records' values will be 1 and the other half, 0. So, in this scenario, the database engine could avoid a full table scan, but will have to read a lot of rows anyway (total rows/2).

                  那么,我应该将此列作为索引吗?

                  So, should I make this column an index?

                  作为记录,我使用的是 Mysql 5,但我更感兴趣的是它为什么这样做/没有意义索引我知道将具有低基数的列.

                  For the record, I'm using Mysql 5, but I'm more interested in a general rationale on why it does / does not make sense indexing a column that I know that will have a low cardinality.

                  提前致谢.

                  推荐答案

                  如果出现以下情况,索引甚至可以帮助处理低基数字段:

                  An index can help even on low cardinality fields if:

                  1. 当其中一个可能值与其他值相比非常不常见时,您会搜索它.

                  1. When one of possible values is very infrequent compared to the other values and you search for it.

                  例如,很少有色盲女性,所以这个查询:

                  For instance, there are very few color blind women, so this query:

                  SELECT  *
                  FROM    color_blind_people
                  WHERE   gender = 'F'
                  

                  最有可能受益于 gender 的索引.

                  would most probably benefit from an index on gender.

                  当值倾向于按表格顺序分组时:

                  When the values tend to be grouped in the table order:

                  SELECT  *
                  FROM    records_from_2008
                  WHERE   year = 2010
                  LIMIT 1
                  

                  虽然这里只有 3 个不同的年份,但较早年份的记录很可能首先添加,因此在返回第一个 2010 之前必须扫描非常多的记录如果不是索引,则记录.

                  Though there are only 3 distinct years here, records with earlier years are most probably added first so very many records would have to be scanned prior to returning the first 2010 record if not for the index.

                  当你需要ORDER BY/LIMIT时:

                  SELECT  *
                  FROM    people
                  ORDER BY
                          gender, id
                  LIMIT 1
                  

                  如果没有索引,则需要 filesort.虽然它对 LIMIT 做了一些优化,但它仍然需要全表扫描.

                  Without the index, a filesort would be required. Though it's somewhat optimized do to the LIMIT, it would still need a full table scan.

                  当索引覆盖查询中使用的所有字段时:

                  When the index covers all fields used in the query:

                  CREATE INDEX (low_cardinality_record, value)
                  
                  SELECT  SUM(value)
                  FROM    mytable
                  WHERE   low_cardinality_record = 3
                  

                • 当你需要DISTINCT时:

                  SELECT  DISTINCT color
                  FROM    tshirts
                  

                  MySQL 将使用INDEX FOR GROUP-BY,如果你的颜色很少,即使有数百万条记录,这个查询也是即时的.

                  MySQL will use INDEX FOR GROUP-BY, and if you have few colors, this query will be instant even with millions of records.

                  这是一个场景示例,当低基数字段上的索引比高基数字段上的索引效率更高.

                  This is an example of a scenario when the index on a low cardinality field is more efficient than that on a high cardinality field.

                  请注意,如果 DML 性能没有太大问题,那么创建索引是安全的.

                  Note that if DML performance is not much on an issue, then it's safe to create the index.

                  如果优化器认为索引效率低下,则不会使用该索引.

                  If optimizer thinks that the index is inefficient, the index just will not be used.

                  这篇关于使用基数较低的索引有意义吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!
                • 相关文档推荐

                  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)(超出最大存储过程、函数、触发器或视图嵌套级别(限制 32))
                  How to View Oracle Stored Procedure using SQLPlus?(如何使用 SQLPlus 查看 Oracle 存储过程?)
                  How to debug stored procedure in VS 2015?(如何在 VS 2015 中调试存储过程?)
                  Set the variable result, from query(设置变量结果,来自查询)
                  What is dynamic SQL?(什么是动态 SQL?)
                  Mysql - How to quit/exit from stored procedure(Mysql - 如何退出/退出存储过程)
                • <legend id='hmfJY'><style id='hmfJY'><dir id='hmfJY'><q id='hmfJY'></q></dir></style></legend>
                      <bdo id='hmfJY'></bdo><ul id='hmfJY'></ul>

                          <tfoot id='hmfJY'></tfoot>

                            <i id='hmfJY'><tr id='hmfJY'><dt id='hmfJY'><q id='hmfJY'><span id='hmfJY'><b id='hmfJY'><form id='hmfJY'><ins id='hmfJY'></ins><ul id='hmfJY'></ul><sub id='hmfJY'></sub></form><legend id='hmfJY'></legend><bdo id='hmfJY'><pre id='hmfJY'><center id='hmfJY'></center></pre></bdo></b><th id='hmfJY'></th></span></q></dt></tr></i><div id='hmfJY'><tfoot id='hmfJY'></tfoot><dl id='hmfJY'><fieldset id='hmfJY'></fieldset></dl></div>

                            <small id='hmfJY'></small><noframes id='hmfJY'>

                              <tbody id='hmfJY'></tbody>