MySQL--索引(3)

1.索引创建注意点

选择合适的字段

1.不为 NULL 的字段

        索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

2.被频繁查询的字段

        我们创建索引的字段应该是查询操作非常频繁的字段。

3.被作为条件查询的字段

        被作为 WHERE 条件查询的字段，应该被考虑建立索引。

4.被经常频繁用于连接的字段

        经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

不合适创建索引的字段

1.被频繁更新的字段应该慎重建立索引

        虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

2.不被经常查询的字段没有必要建立索引

3.尽可能的考虑建立联合索引而不是单列索引

        因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引

        冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引

        前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

使用索引一定能提高查询性能吗?

        大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

2.索引失效的因素

前四个为重点

1. 查询条件不是最左前缀：
   如果你有一个复合索引（联合索引），MySQL可以使用该索引的最左前缀来优化查询。如果查询条件没有从索引的最左边列开始，那么MySQL可能不会使用该索引。

2. 查询条件中的函数操作：
   如果在查询条件中对索引列使用了函数或表达式（如WHERE YEAR(date_column) = 2023），那么MySQL将无法使用索引，因为索引是基于列的原始值建立的，而不是基于函数的结果。

3. 隐式类型转换：
   如果查询条件中的数据类型与索引列的数据类型不匹配，并且MySQL必须进行隐式类型转换才能进行比较，那么索引可能不会被使用。例如，如果索引列是整型（INT），但查询条件中使用了字符串（'123'），则可能发生隐式类型转换。

4. LIKE语句的开头是通配符：
   当使用LIKE语句进行模糊匹配，并且匹配模式的开头是通配符（如%abc），MySQL将无法使用索引来加速查询，因为索引是基于列的固定前缀建立的。但是，如果通配符不在开头（如abc%），则索引仍然可以被使用。

5. OR条件：
   在某些情况下，如果查询条件包含OR，并且OR连接的两个条件分别针对不同的索引列，MySQL可能会选择不使用索引而执行全表扫描。这取决于查询的具体情况和MySQL的优化器决策。

6. 索引统计信息过时：
   如果MySQL的索引统计信息（如表的行数、索引的分布等）过时，那么优化器可能会基于不准确的信息来做出不使用索引的决策。在这种情况下，可以通过运行ANALYZE TABLE命令来更新统计信息。

7. 索引选择性低：
   如果索引列中的大多数值都是相同的（即索引的选择性很低），那么MySQL可能会认为使用索引的代价太高，从而选择全表扫描。

8. 查询优化器的决策：
   最终，是否使用索引取决于MySQL查询优化器的决策。在某些情况下，即使理论上可以使用索引，优化器也可能因为各种内部因素（如内存限制、查询缓存的考虑等）而选择不使用索引。

最后：了解和避免这些导致索引失效的因素，可以帮助你优化MySQL查询的性能。在实际应用中，可以通过EXPLAIN命令来查看MySQL如何执行你的查询，并检查是否使用了索引。（要使用 EXPLAIN 命令，你只需在 SQL 语句前加上 EXPLAIN 关键字。）