SQL 什么时候需要创建索引？

前言

带着问题去想 什么时候需要用索引？

什么时候需要创建索引

• 经常用于where中的查询条件的字段，如果给这个字段加上索引，那么久可以提高整个表的查询速度。如果查询的不止一个字段，那可以建立联合索引
• 经常用于 GROUP BY 或 ORDER BY 的字段，这样就不需要查询后  再进行一次排序了，建立索引之后数据在B+ Tree 中的记录都是排序好的
• 想把一个字段进行一些唯一的限制。

什么时候不需要创建索引

where 、GROUP BY 、 ORDER BY 里用不到的字段，就可以i不去给他创建索引。因为索引是会占用内存空间的。

字段中存在大量重复的数据，不需要创建索引。比如 男女 、 很少的状态字段。因为可以在记录中分布的很均匀，那么无论搜索哪个值，都有可能会得到一半的数据。在这种情况下 ，不如不要索引，MYSQL查询时有一个查询优化器，查询优化器在发现某个值出现在表中数据行占百分比过多时，它一般会忽略索引，进行全表扫描。

视 实际查询数据量决定，当我们查询数据过少时，查询压力没有那么大，也就不需要使用索引。我之前做过一些千万级数据筛查，有的情况下就算有索引 还是无法命中，需要全表扫描。

经常会更新的字段最好不要创建索引，索引字段频繁的修改会影响B+Tree的有序性。要频繁的重建索引，这个过程会影响到数据库性能

有什么优化索引的方法呢

前缀索引优化

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

假设我们只需要查询商品的名称、价格，有什么方式可以避免回表呢？

我们可以建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

所以，使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。

主键索引最好是自增的

我们在建表的时候，都会默认将主键索引设置为自增的，具体为什么要这样做呢？又什么好处？

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

因此，在使用 InnoDB 存储引擎时，如果没有特别的业务需求，建议使用自增字段作为主键。

另外，主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

索引最好设置为 NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

• 第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

• 第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么中至少会用 1 字节空间存储 NULL 值列表。

防止索引失效

用上了索引并不意味着查询的时候会使用到索引，所以我们心里要清楚有哪些情况会导致索引失效，从而避免写出索引失效的查询语句，否则这样的查询效率是很低的。

简单举几个常见索引失效的例子

• 使用 like 模糊匹配查询时，like %xx  或 like %xx% 会导致索引失效 (like xx%不会) 
• 在查询时对索引列做了计算、函数操作时 会造成索引失效
• 联合索引要能正确遵循最左匹配的原则，按照最左优先的方式进行索引的匹配，否则也会造成索引的失效
• 查询时，or前是 索引字段 or后是非索引列，也会导致这个索引列失效

我们怎么知道select语句到底有没有命中索引呢？

在查询的语句前跟上  explain 即可。下文详细讲述 explain如何使用

总结

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

• 需要占用物理空间，数量越大，占用空间越大；
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
• 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

所以，索引不是万能钥匙，它也是根据场景来使用的。

我认为 一切的数据库优化操作，都是基于数据量决定的，如果你这个表 数据量根本到不了一个很大的量级，那么我认为也没什么大必要做数据库优化，不妨把精力放在优化其他可能影响性能的操作上！