数据库SQL索引性能优化的一些技巧

目录

索引

聚集（clustered）索引，也叫聚簇索引

查看SQL语句索引使用的情况

建议创建索引的情况：

索引失效的情况

回表

避免回表：宽索引

宽索引缺点

总结

---

 

索引

聚集（clustered）索引，也叫聚簇索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同。

由于物理排序只能有一种方式，所以一个表中只能拥有一个聚集索引。也就是MYSQL里的主键。


非聚集索引：

非聚集索引保存的数据都会存储主键值，然后通过主键值，来回表查找相应的数据

查看SQL语句索引使用的情况

EXPLAIN 【SQL语句】

例如：

建议创建索引的情况：

1. 字段的数值有唯一性的限制，比如用户名，可以直接创建唯一性索引，或者主键索引
2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
3. 需要经常 GROUP BY 和 ORDER BY 的列（注意联合索引的顺序）
4. DISTINCT 字段需要创建索引
5. 多表 JOIN 连接操作时，使用有索引的字段作为ｗｈｅｒｅ条件

注意：

频繁更新的字段不一定要创建索引，因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

重复率高、筛选性差的字段不要建立索引。

 

索引失效的情况

1. 如果索引进行了表达式计算，则会失效，比如：WHERE条件 id+1 = 1001
2. 如果对索引使用函数，也会造成失效
3. 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效，因为索引并不存储空值，建议给字段设置默认值
4. 当我们使用 LIKE 进行模糊查询的时候，后面不能是 %，即ｌｉｋｅ　语句不能用％开头
5. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效

　　　因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效，将两个OR字段都建立索引，才可以避免全表扫描

注意

在使用联合索引的时候要注意最左原则，一条 SQL 语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则就会失效

 

回表

回表指的就是数据库根据索引找到了数据行之后，还需要通过主键再次到数据表中读取数据的情况。

比如where条件有索引字段，select却需要没有索引的字段，所以需要回表

避免回表：宽索引

索引片就是 SQL 查询语句在执行中需要扫描的一个索引片段，根据索引片中包含的匹配列的数量不同，将索引分成窄索引（比如包含索引列数为 1 或 2）和宽索引（包含的索引列数大于 2）。

如果索引片越宽，那么需要顺序扫描的索引页就越多；如果索引片越窄，就会减少索引访问的开销。

三星索引标准：
１　　在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列；
２　　将 GROUP BY 和 ORDER BY 中的列加入到索引中；
３　　将 SELECT 字段中剩余的列加入到索引片中。

也就是说，将 SELECT 中的字段都保存在索引中就可以避免回表的情况，从而提升查询效率。

宽索引缺点

１、索引片变宽，每个页能够存储的索引数据就会变少，增加了页加载的数量。如果数据量很大，索引所需要的磁盘空间可能会成为一个问题，对缓冲池所需空间的压力也会增加。
２、增加了索引维护的成本。添加一条记录的，就需要在每一个索引上都添加相应的行（存储对应的主键值）。而且数据的更新不一定马上回写到磁盘上，也会造成缓冲池中的空间占用过多，脏页过多的情况。

总结

理所当然，需要在索引效率和索引维护之间进行权衡。

宽索引，好处就是不需要进行回表查询，减少了磁盘 I/O 的次数，弊端就是会造成频繁的页分裂和页合并，对于数据的插入和更新来说，效率会降低不少。

1. 一张表的索引个数不宜过多，尽可能在原有的索引片上增加索引，也就是采用复合索引的方式，而不是新建一个新的索引。
2. 需要控制索引列的数量，除非 SELECT 中的非条件列数少，并且该字段会经常使用到。
3. 索引导致占用空间多，尽量采用数值类型替代字符类型，尽量避免用字符类型做主键，同时针对字符字段最好只建前缀索引