分享每一天-Mysql索引设计原则

Mysql索引设计原则

Mysql 1758 0 创建时间：2021-05-21 10:46:33

mysql索引设计原则

## 索引设计原则
#### 选择唯一性索引

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

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

- 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建⽴立索引，可以提⾼高整个表的查询速度

#### 限制索引的数目

- 索引的数目不是越多越好
- 每个索引都需要占⽤用磁盘空间，索引越多，需要的磁盘空间就越大
- 修改表时，对索引的重构和更新很麻烦
- 越多的索引，会使更新表变得很浪费时间

#### 尽量使用数据量少的索引

- 如果索引的值很长，那么查询的速度会受到影响
> - 例如：对一个char(100)类型的字段进行全文检索需要的时间肯定比对char(10)类型的字段需要的时间更多

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

- 经常需要order by、group by、distinct和union等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效的避免排序操作

#### 尽量使用前缀索引

- 如果索引字段的值很长，最好使用值的前缀来索引
> - 例如：text和blog类型的字段，进行全文检索会浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度

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

- 表中数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理理员应当定期找出这些索引，将他们删除，从而减少索引对更新操作的影响

#### 小表不应建议索引(超过200w数据的表，建立索引)

- 包含大量的列并且不需要搜索非空值的时候可以考虑不建索引

#### 经常被用来过滤记录的字段

- primary key 字段，系统自动创建主键的索引
- unique key 字段，系统自动创建对应的索引
- foreign key 约束所定义的作为外键的字段
- 在查询中用来连接表的字段
- 经常用来作为排序(order by的字段)基准的字段

#### 索引会占⽤用磁盘空间，创建不不必要的索引只会形成浪费

#### 索引的创建必须考虑数据的操作方式

- 内容很少变动，经常被查询，为它多创建几个索引无所谓
- 经常性，例行性变动的表而言，则需要谨慎地创建确实必要的索引

## 复合索引和单一索引

- 复合索引是指多字段联合索引，查询时经常需要这几个字段组合一起为条件再查询

- 唯一索引主要是用主键ID索引，存储的结构顺序与物流结构一直

#### 添加和删除索引的情况

- 表的主键、外键必须有索引
- 数据量超过300w的表应该有索引
- 经常与其他表进行连接的表，在连接字段上应该建立索引
- 经常出现在where子句中的字段，特别是大表的字段，应该建立索引；
- 索引应该建立选择性高的字段上；
- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
- 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

> - 正确选择复合索引中主列列字段，一般是选择性较好的字段
- 复合索引的几个字段是否经常同时以AND方式出现在where子句中？单字段查询是否极少甚⾄至没有？
- 如果是，则可以建立复合索引；否则考虑单字段索引；
- 如果复合索引中包含的字段经单独出现在where子句中，则分解为多个单字段索引？
- 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
- 如果既有单字段索引，又有这几个字段是哪个的复合索引，一般可以删除复合索引；

#### 频繁进行数据操作的表，不要建立太多的索引

#### 删除无用的索引，避免对执行计划造成负面影响

## 索引类型
探讨索引设计和优化原则之前，先给大家熟悉一下索引类型：

- 主键索引PRIMARY KEY：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。
- 唯一索引UNIQUE：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 创建命令：ALTER TABLE table_name ADD UNIQUE (column);
- 普通索引INDEX：最基本的索引，它没有任何限制。
- 创建命令：ALTER TABLE table_name ADD INDEX index_name (column);
- 组合索引 INDEX：组合索引，即一个索引包含多个列。多用于避免回表查询。
- 创建命令：ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);
- 全文索引 FULLTEXT：全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。
- 创建命令：ALTER TABLE table_name ADD FULLTEXT (column);
- 读到就是赚到，溪源这里再赠送一条删除索引命令：索引一经创建不能修改，如果要修改索引，只能删除重建。
- 删除索引命令：DROP INDEX index_name ON table_name；

#### 优化原则
上面说完索引的设计原则，那么我们下面探讨一下索引的优化原则吧！

1. 联合索引，遵循最左前缀匹配原则：

另外需要注意大家特别容易误解的点：最左匹配原则并不是指查询条件的顺序，而是指查询条件中是否包含索引最左列字段；

2. 隐式转换：
 数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
 
3. 索引列不能参与计算，保持列“干净”：
  比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)；
  
4. 范围条件存在多个索引时，查询可以命中索引：
  范围条件有：<、<=、>、>=、between等。
  范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，并且索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引；
  如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引；
  
5. 利用覆盖索引进行查询，避免回表：
 被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。就是平时我们谈论是否select *

Mysql索引设计原则

商品推荐

广告推荐

推荐文章