第32期：索引设计（索引设计详细规范）

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

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)));

2. 索引列选择规范：

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

索引本身有有序的，尽量选择整型列做索引，如果避免不了字符串做索引，可以选择对字符类型做 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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！