关于索引:第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 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！