第32期:索引设计(索引设计详细规范)

爱可生云数据库
English

通过前面一些关于索引设计的相关介绍与示例,相信大家已经对索引设计这块有了一些零碎的认识,那本篇来做下总结,给出一个索引设计的详细规范。

  1. 索引命名规范:

单值索引,建议以 idx_ 为开头,字母全部小写。

例如:alter table t1 add key idx_r1(r1);

组合索引,建议以 dx_multi_ 开头,字母全部小写。

例如:alter table t1 add key idx_multi_1(r1,r2,r3) ;

唯一索引,建议以 udx_ 为开头,字母全部小写;如果是多值唯一索引,则命名方式类似 udx_multi_1 等。

例如:
alter table t1 add unique key udx_f1(r1);
或者
alter table t1 add key udx_multi_1(r1,r2,r3);

全文索引,建议以 ft_ 开头,字母全部小写,并且建议默认用 ngram 插件。

例如:alter table t1 add fulltext ft_r1(r1) with parser ngram;

前缀索引,建议以 idx_ 开头,以 _prefix 结尾。

例如: alter table t1 add key idx_r1_prefix(r1(10));

函数索引,建议以 idx_func_ 开头,字母全部小写。

例如: alter table t1 add key idx_func_r1((mod(r1,4)));
  1. 索引列选择规范:

    ##### 索引列的字段类型:

索引本身有有序的,尽量选择整型列做索引,如果避免不了字符串做索引,可以选择对字符类型做 HASH ,再基于 HASH 结果做索引;

主键列数据类型最好也是整型,避免对不规则的字符串建立主键(比如在探讨主键时提到的 UUID ,由于 INNODB 表即索引,所以应该避免掉。并不是由于 UUID 非有序,而是因为单个 UUID 太大)

##### 索引列的字符长度:

尽量控制索引的字符长度。比如字段 r1 varchar(200), 如果仅前10个字符频繁变化,后面的字符接近于静态数据,可以基于前10个字符建立前缀索引。大对象字段仅支持建立前缀索引。

alter table t1 add key idx_r1_prefix(r1(10));

##### 索引列的可选择性:

基于可选择性较高的字段建立索引,对可选择性较低的字段建立索引可能适得其反;这条规则也适用于组合索引,多个字段一起建立组合索引,按照索引键选择性高低来决定组合索引的顺序。举个例子:

表 t1 有1000条记录,字段 r1 有800条不同的值,字段 r2 有500条不同的值,字段 r3 有100条不同的值。那组合索引的顺序应该是(r1,r2,r3)。

##### 单张表索引个数控制:

虽然 MySQL 单表最多支持64个索引,但是关于具体索引个数,最好越少越好。比如可以根据表结构和业务访问模型来分别对待,不同的表可以有不同数量的索引。

##### 频繁更新的列不建议有索引:

不建议对频繁更新的列建立索引。对这样的列建立索引,在数据频繁更新时,会同时更新此列对应的索引键值分布。比如货物的库存数量属性,会经常更新,应该避免建立索引,一般数据访问请求中,商品 ID 是必要过滤条件,库存数量只做展示即可。

##### 函数索引:

非必要条件不建立函数索引,除非基于这列必须做函数检索。

比如列 r1 ,基于 mod(r1,4) 的运算比基于 r1 的运算要频繁的多,则有必要针对列r1建立一个函数索引。

##### 重复索引:

索引都是基于最左列扫描找到入口,再继续遍历,非必要条件下,避免以最左列开始的多个索引同时存在。

比如列 r1 被三个索引包含,可能会想到如下语句:

select * from t1 where r1 = 2 and r2 = 2 and r3 = 2;
   
select * from t1 where r1 = 2;
   
select * from t1 where r1 = 2 and r2 = 2;

如果上面这三条语句频繁运行,则只需建立一个以 r1 开头的组合索引即可,即索引 idx_multi_1 .

例如:
   
alter table t1 add key idx_multi_1(r1,r2,r3);

但是如果下面语句也频繁运行:

select * from t1 where r1 = 1 and r4 = 1 and r5 = 1;
  
select * from t1 where r1 = 1 and r4 = 1;

则需要再建一个以字段 r1 开头的,以字段 r4,r5 结尾的组合索引。

例如:
   
alter table t1 add key idx_multi_2(r1,r4,r5);

再比说过滤条件(r1,r2,r3) 比过滤条件 (r1,r4,r5)的使用频率高,则可以考虑合并这两个组合索引为一个大的组合索引。(r1,r2,r3,r4,r5)。

例如:

alter table t1 drop key idx_multi_1, drop key idx_multi_2, add key idx_multi_1(r1,r2,r3,r4,r5);

基于表查询语句反向索引建立:

例如以下语句:

select * from t1 where r1 = 2;

select * from t1 where r2 = 2;

select * from t1 where r3 = 2;

select  * from t1 where r4 = 2;

select * from t1 where r1 = 2 or r2 = 2 or r3 = 2 or r4 = 2;

select * from t1 where r2 = 2 or r4 = 2;

select * from t1 where r2 = 2 and r3 = 2;

select * from t1 where r3 = 2 or r4 = 2;

select * from t1 where r1 = 2 and r2  = 2 and r3  = 2 and r4 = 2;

上面这些 SQL 过滤字段局限在字段 r1,r2,r3,r4 四个,而且不固定,这时候可以单独每个列建立索引,由 MySQL 来决定用哪个索引或者说多个索引一起用。

例如:

alter table t1 add key idx_r1(r1),
add key idx_r2(r2), 
add key idx_r3 (r3), 
add key idx_r4(r4);

##### 避免基于小表加索引:

比如表 t1 记录数可能永远只有1000行,那除了主键外,完全没有必要建立其他索引。

例如:

 select * from t1 where r1 = 10;

上面语句非常简单,走索引速度可能还没有顺序扫表来的快。

##### 索引顺序的指定:

一般场景若非强制需求,默认按照索引键值升序,除非查询过滤条件指定排序。

比如查询语句:select r1,r2,r3 from t1  order by r1 desc, r2 asc, r3 desc

如果按照默认方式建立索引,针对这条 SQL 序开销又很大。此时可以指定字段顺序建立索引。

例如:

alter table t1 add key idx_multi_sort_1(r1 desc, r2 asc, r3 desc);

关于索引的设计这块,到本章就已完结,如果在读完索引设计相关篇章后还有疑问的,欢迎私信或者留言。


关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!

阅读 376

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

242 声望
144 粉丝
0 条评论
你知道吗?

中国领先的企业数据处理技术整体解决方案提供商,开源数据库领域优秀企业。为大型行业用户的特定场景提...

242 声望
144 粉丝
文章目录
宣传栏