索引和索引优化

╰+攻爆jí腚メ 2024-04-06 14:12 111阅读 0赞

## 一、索引 ##

### 1.**什么是索引** ###

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

简单来说索引的出现就是为了提高数据的查询效率，就像书的目录一样。在书籍中，用户不必翻阅完整个书就能根据目录迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

### 2.**索引的分类** ###

#### **2.1 单列索引**：一个索引只包含单个列，但一个表中可以有多个单列索引。 ####

**2.1.1 普通索引：**MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

**2.1.2 唯一索引：**索引列中的值必须是唯一的，但是允许为空值。

**2.1.3 主键索引：**是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）

#### 2.2 组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。    ####

#### 2.3 **全文索引：**全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。 ####

#### **2.4 空间索引**：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。 ####

### 3.**索引的优势** ###

1）提高数据检索效率，降低磁盘IO成本

2）通过对数据的排序，降低排序成本

### 4.**索引的劣势** ###

1）索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。

2）需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

### 5.**什么情况下适合建立索引** ###

1）主键自动建立唯一索引

2）频繁作为查询条件的字段（where后面的字段）

3）查询中与其他表关联的字段（各种join on后面的字段）

4）单值/复合索引选择？（高并发下倾向选择复合索引）

5）查询中排序的字段

6）查询中统计或分组的字段

### 6.**什么情况下不适合建立索引** ###

1）表数据太少

2）频繁更新的字段

3）where后面用不到的字段

### 7.**什么时候会出现索引失效** ###

1）like以通配符开头('%abc')会导致索引失效，违反**最左前缀法则**

最左前缀法则：**查询从索引的最左前列开始并且不跳过索引中的列**。

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id > 50 and name = '张三'，会导致name索引失效

4）尽量使用覆盖索引，不要select \*

5）MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询。

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效

隐式转换-->函数操作

8）用or连接时会导致索引失效

## 二、索引优化 ##

### **1.索引优化规则** ###

![174eb5c0fce8157434438565855d6bb0.png][]

（1）**like语句的前导模糊查询不能使用索引**，因为页面搜索严禁左模糊或者全模糊，如果需要可以使用搜索引擎来解决。

（2）**union、in、or 都能够命中索引，建议使用 in**

union能够命中索引，并且MySQL 耗费的 CPU 最少。

in能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in。

or 新版的 MySQL 能够命中索引，查询优化耗费的 CPU 比 in多，不建议频繁用or。

补充：有些地方说在where条件中使用or，索引会失效，造成全表扫描，这是个误区：

*  ①要求where子句使用的所有字段，都必须建立索引;
 *  ②如果数据量太少，mysql制定执行计划时发现全表扫描比索引查找更快，所以会不使用索引;
 *  ③确保mysql版本5.0以上，且查询优化器开启了index\_merge\_union=on, 也就是变量optimizer\_switch里存在index\_merge\_union且为on。

（3）**负向条件查询不能使用索引**

负向条件有：!=、<>、not in、not exists、not like 等。

（4）**联合索引最左前缀原则**

*  如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c)组索引。
 *  ①建立联合索引的时候，区分度最高的字段在最左边；
 *  ②存在非等号和等号混合判断条件时，在建立索引时，把等号条件的列前置。如 where a>? and b=?，那么即使a 的区分度更高，也必须把 b 放在索引的最前列；
 *  ③最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致。

（5）**不能使用索引中范围条件右边的列(范围列可以用到索引)，范围列之后列的索引全失效**

*  范围条件有：<、<=、>、>=、between等。
 *  索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

（6）**不要在索引列上面做任何操作(计算、函数)，否则会导致索引失效而转向全表扫描**

（7）**强制类型转换会全表扫描**

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

（8）**更新十分频繁、数据区分度不高的列不宜建立索引**

*  更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。
 *  “性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。
 *  一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(\*) 来计算。

（9）**利用覆盖索引来进行查询操作，避免回表，减少select \* 的使用**

*  覆盖索引：查询的列和所建立的索引的列个数相同，字段相同。
 *  被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

（10）**索引不会包含有NULL值的列**

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null 约束以及默认值。

（11）**is null, is not null无法使用索引**

（12）**如果有order by、group by的场景，请注意利用索引的有序性**

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file\_sort 的情况，影响查询性能。

（13）**使用短索引(前缀索引)**

*  对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果该列在前10个或20个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(\*) 来计算前缀索引的区分度。
 *  但缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。
 *  不过很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

（14）**利用延迟关联或者子查询优化超多分页场景**

MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

（15）**如果明确知道只有一条结果返回，limit 1 能够提高效率**

（16）**超过三个表最好不要 join**

*  需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。
 *  例如：left join是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用right join。

（17）**单表索引建议控制在5个以内**

（18）**SQL 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好**

*  consts：单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
 *  ref：使用普通的索引(Normal Index)。
 *  range：对索引进行范围检索。
 *  当 type=index 时，索引物理文件全扫，速度非常慢。

（19）**业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引**

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

（20）**创建索引时避免以下错误观念**

索引越多越好，认为需要一个查询就建一个索引。