列的离散度和索引选择性

数据库表索引的创建对数据库操作的性能有至关重要的影响，除索引创建相关原则需要遵守之外，我们还可以借助一些指标对索引是否合理进行判断，其中就包含列的离散度和索引的选择性。

列的离散度

列的离散度计算公式如下：

count(distinct(column_name)) / count(*) -- 列的全部不同值个数 / 所有数据行行数

数据行数相同的情况下，分子越大，列的离散度就越高。简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。而重复值越多的索引反映到索引树上的结果就是要做更多的搜索，从而降低索引的效率。因此，我们在选择索引字段时，离散度越高的字段越适合作为索引字段。

索引选择性

索引的选择性，指的是不重复的索引值Cardinality（基数）和表记录数的比值。选择性是索引筛选能力的一个指标。索引的取值范围是 0—1 ，当选择性越大，索引价值也就越大。Cardinality 表示该索引列上有多少不同的记录，这个是一个预估的值，是采样得到的（由 InnoDB 触发，采样20个页，进行预估），该值越大越好，即当 Cardinality / RowNumber 越接近1越好，表示该列是高选择性的。

# 计算表索引选择性的sql
SELECT 
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  INDEX_NAME,
  CARDINALITY,
  TABLE_ROWS,
  CARDINALITY / TABLE_ROWS AS SELECTIVITY 
FROM
  information_schema.TABLES t,
  (SELECT 
    table_schema,
    table_name,
    index_name,
    cardinality 
  FROM
    information_schema.STATISTICS 
  WHERE (
      table_schema,
      table_name,
      index_name,
      seq_in_index
    ) IN 
    (SELECT 
      table_schema,
      table_name,
      index_name,
      MAX(seq_in_index) 
    FROM
      information_schema.STATISTICS 
    GROUP BY table_schema,
      table_name,
      index_name)) s 
WHERE t.table_schema = s.table_schema 
  AND t.table_name = s.table_name 
  AND t.table_rows != 0 
  AND t.table_schema NOT IN (
    'mysql',
    'performance_schema',
    'information_schema'
  ) 
  AND t.table_schema = '[table name]'
ORDER BY SELECTIVITY ;

以上sql可以计算出现有表索引的选择性，根据结果，需要斟酌其中选择性比较低的索引是否有必要创建。

前缀索引

当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。但是具体该如何截取，截取得多了，达不到节省索引存储空间的目的，截取得少了，重复内容太多，字段的散列度（选择性）会降低。 通过字段截取的离散度测算，根据测算结果可以很快得出截取长度的理论依据，离散度测算语句如下：

# column_name是需要考虑前缀索引的字段，n表示截取长度，通过变换n的大小得到一些列离散度数据，再对离散度数据进行分析，大多数情况下，离散度与n正相关
select count(distinct left(column_name, n)) / count(*) as subn from table_name;