Mysql前缀索引长度确定方法

Mysql前缀索引长度确定方法,第1张

添加语法: ALTER TABLE table_name ADD KEY(column_name(prefix_length))

在MySQL中,前缀长度最大值为255字节。对于存储引擎为MyISAM或InnoDB的数据表,前缀最长为1000字节。 必须注意的是,在MySQL中,对于TEXT和BLOB这种大数据类型的字段,必须给出前缀长度(length)才能成功创建索引

如何确定前缀索引长度?

可以通过计算选择性来确定前缀索引的选择性,计算方法如下

全列选择性:

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name

某一长度前缀的选择性

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name

当前缀的选择性越接近全列选择性的时候,索引效果越好。

参考文章:

MySQL索引 *** 作命令详解

MySQL 前缀索引

https://blog.csdn.net/weixin_43935927/article/details/109491334

建立索引,要使用离散度(选择度)更高的字段。

我们先来看一个重要的属性列的 离散度,

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

数据行数相同的情况下,分子越大,列的离散度就越高。简单来说,如果列的重复值越多,离散度就越低,重复值越少,离散度就越高。

当字段值比较长的时候,建立索引会消耗很多的空间,搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。

创建一张商户表,因为地址字段比较长,在地址字段上建立前缀索引

create table shop(address varchar(120) not null)

alter table shop add key(address(12))  // 截取12个字符作为前缀索引是最优的吗?

问题是,截取多少呢?截取得多了,达不到节省索引存储空间的目的,截取得少了,重复内容太多,字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢?

先看一下字段在全部数据中的选择度计算公式:

select count(distinct address) / count(*) from shop

select count(distinct left(address, n)) / count(*) as subn from shop

count(distinct left(address,n)) / count(*) 的结果是会随着 n 的变大而变大。举个例子,现在有两个address(东大街长兴小区,东大街福乐小区),那么 distinct(address,2) <distinct(address,3)

==>所以,截取的长度越长就会越接近字段在全部数据中的选择度

==>所以,我们要权衡索引大小和查询速度。

举个例子,通过不同长度去计算,与全表的选择性对比:

    SELECT  COUNT(DISTINCT(address))/COUNT(*) sub,            -- 字段在全部数据中的选择度

    COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5,  -- 截取前5个字符的选择度

    COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7, 

    COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,

    COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10,  -- 截取前10个字符的选择度

    COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,

    COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,

    COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,

    COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15

FROM shop

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

| sub    | sub5  | sub7  | sub9  | sub10  | sub11  | sub12  | sub13  | sub15  |

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |

+--------+--------+--------+--------+--------+--------+--------+--------+--------+

可以看到在截取 11 个字段时 sub11(0.9993) 就已经很接近字段在全部数据中的选择度 sub(0.9958)了,而且长度也相较后面更短一些, 综合考虑比较合适。

ALTER TABLE shop ADD KEY (address(11))

1.索引的个数不要过多(浪费空间,更新变慢)

2.在用于 where 判断 order 排序和 join 的(on)字段上创建索引

3.区分度低的字段,例如性别,不要建索引(离散度太低,导致扫描行数过多)

4.更新频繁的值,不要作为主键或者索引(页分裂)

5.不建议用无序的值作为索引,例如身份z、UUID(在索引比较时需要转为ASCII,并且插入时可能造成页分裂)

6.若在多个字段都要创建索引的情况下,联合索引优于单值索引

7.联合索引把散列性高(区分度高)的值放在前面


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/6098770.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-03-14
下一篇 2023-03-14

发表评论

登录后才能评论

评论列表(0条)

保存