image
通过前面一些关于索引设计的相关介绍与示例，相信大家已经对索引设计这块有了一些零碎的认识，那本篇来做下总结，给出一个索引设计的详细规范。
-
索引命名规范：
单值索引，建议以 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)));
-
索引列选择规范：
索引列的字段类型：
索引本身有有序的，尽量选择整型列做索引，如果避免不了字符串做索引，可以选择对字符类型做 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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
网友评论