索引的设计规范，你知道那些？

女爷i 2024-04-29 19:25 51阅读 0赞

### 1、索引原理： ###

索引是帮助MySQL高效获取数据的数据结构，注意，是帮助高性能的获取数据

索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了`加快数据库的查询速度`。

*  索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
 *  索引是一种能帮助mysql提高了查询效率的数据结构：**索引数据结构**。

索引的存储原理大致可以概括为一句话：**以空间换时间**。

数据库在未添加索引, 进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。

数据库添加了索引之后，通过索引快速找到数据在磁盘上的位置，可以快速地读取数据，而不用从头开始全表扫描。

一般来说索引本身也很大，不可能全部存储在内存中，因此`索引往往是存储在磁盘上的文件中的`（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

### 2、索引的分类： ###

**主键索引：primary key**

*  设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

**唯一索引：**

*  索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。

**复合索引：**

*  一个索引可以包含多个列，多个列共同构成一个复合索引。

**全文索引：**

*  Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
 *  全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在**Char、VarChar** 上创建。

**空间索引：**

*  MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

**前缀索引：**

*  在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

### 3、索引的优缺点： ###

**优点：**

*  大大提高数据查询速度。
 *  可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
 *  通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
 *  被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
 *  如果按照索引列的顺序进行排序，对order 不用语句来说，效率就会提高很多。

**缺点：**

*  索引会占据磁盘空间。
 *  索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
 *  维护索引需要消耗数据库资源。

**综合索引的优缺点：**

*  `数据库表中不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!`

### 4、参考的索引设计规范： ###

每个公司，都有自己的 设计规范，

尼恩这里的梳理的设计规范，可以作为大家参考。当然，如果面试的时候能讲到这个水平，已经很牛掰了。

#### 4.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)));

#### 4.2 尽量选择整型列做索引 ####

索引本身有有序的，尽量选择整型列做索引，

所以，尽量不用uuid，而是使用雪花id，页段id，等整数id去建立索引 。

> 雪花id，页段id的源码和原理，请参见尼恩的《视频第32章：超高并发、超高可用1000W级 ID组件 架构与实操》

如果避免不了，只有字符串做索引，可以选择对字符类型做 HASH ，再基于 HASH 结果做索引；

主键列数据类型最好也是整型，

避免对不规则的字符串建立主键（由于 INNODB 表即索引，所以应该避免掉。不仅仅 UUID 非有序，而是因为单个 UUID 太大）

#### 4.3 优先建立唯一性索引 ####

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。

如果使用姓名的话，可能存在同名现象，从而降低查询速度。

#### 4.4 为经常需要排序、分组和联合操作的字段建立索引 ####

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。

如果为其建立索引，可以有效地避免排序操作。

#### 4.5 为常作为查询条件的字段建立索引 ####

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。

因此，为这样的字段建立索引，可以提高整个表的查询速度。

#### 4.6 限制索引的数目 ####

索引的数目不是越多越好。

每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

修改表时，对索引的重构和更新很麻烦。

越多的索引，会使更新表变得很浪费时间。

#### 4.7 尽量使用数据量少的索引 ####

如果索引的值很长，那么查询的速度会受到影响。

例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

#### 4.9 尽量使用前缀来索引 ####

如果索引字段的值很长，最好使用值的前缀来索引。

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。

如果只检索字段的前面的若干个字符，这样可以提高检索速度。

#### 4.10 删除不再使用或者很少使用的索引 ####

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。

数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

#### 4.11 最左前缀匹配原则，非常重要的原则 ####

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，

比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

注意：=和in可以乱序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，

mysql的查询优化器会帮你优化成索引可以识别的形式

#### 4.12 尽量选择区分度高的列作为索引 ####

区分度的公式是count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，

唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，

那可能有人会问，这个比例有什么经验值吗？

使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录

#### 4.13 索引列不能参与计算，保持列“干净” ####

比如from\_unixtime(create\_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。

所以语句应该写成 create\_time = unix\_timestamp(’2014-05-29’);

#### 4.14 尽量的扩展索引，不要新建索引 ####

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

#### 4.15 考虑建立联合索引来提高查询效率 ####

当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率

注意：选择索引的最终目的是为了使查询的速度变快。

索引的设计规范，你知道那些？

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关索引的设计规范，你知道那些？

相关 python你必须知道的那些模块

相关你知道那些常用的设计模式？

相关 MySQL规范，设计规范，索引规范，开发规范，操作规范

相关 MySQL 那些常见的错误设计规范

相关 QString那些你不知道的事

相关你应该知道的那些Android小经验

相关数组，那些你不知道的事儿

相关那些你必须知道的JVM小知识~

相关你知道或不知道的那些名人名言