MySQL索引优化，MySQL索引失效的场景（超详细！）

目录

1.不遵循最左前缀原则

2.在索引列上使用（计算，函数，or，类型转换），都是导致失效而导致全表扫描

3.范围查询

4.Select  * 导致回表查询

5.不等空值还有or导致索引失效

6.like %写在左边导致索引失效

7.字符串不加单引号也会导致索引失效

8.SQL优化

1.字段的选择

2.创建索引，并使用索引

3.避免走回表，尽量走覆盖索引

4.遵循最左前缀原则

9.索引技巧

---

---

Mysql的锁有哪些?超详细Mysql的锁介绍，表锁，行锁，间隙锁，临键锁，意向锁，元数据锁。Mysql数据库索引有哪些？从数据结构角度，从物理存储角度，从逻辑角度来分析

Mysql的锁有哪些?超详细Mysql的锁介绍，表锁，行锁，间隙锁，临键锁，意向锁，元数据锁。Mysql数据库索引有哪些？从数据结构角度，从物理存储角度，从逻辑角度来分析

Mysql的锁有哪些?超详细Mysql的锁介绍，表锁，行锁，间隙锁，临键锁，意向锁，元数据锁。Mysql数据库索引有哪些？从数据结构角度，从物理存储角度，从逻辑角度来分析

下面的所以都是以复合索引（b,c,d）为前提

1.不遵循最左前缀原则

EXPLAIN SELECT * FROM ind WHERE c=3 AND d=4 AND b=2
EXPLAIN SELECT * FROM ind WHERE  d=4 AND c=3 AND b=2

这两个都会生效，因为Mysq底层会优化为最适合的sql

EXPLAIN SELECT * FROM ind WHERE  b=2  AND d=5 

 也会生效

EXPLAIN SELECT * FROM ind WHERE  c=3 AND d=2

 索引失效，原因是少了b索引，

总结：使用联合索引，只要最左边的索引还在，那索引就不会失效，

如果少了中间的，那么只会生效左边的，

2.在索引列上使用（计算，函数，or，类型转换），都是导致失效而导致全表扫描

使用索引得保证有序性，一旦使用了计算，函数，就会不可避免依次进行计算，也就会全表扫描

3.范围查询

当联合查询使用了范围查询，那么右边的字段就会失效

EXPLAIN SELECT * FROM ind WHERE  b=2 AND c>3 AND d=5

EXPLAIN SELECT * FROM ind WHERE  b>2 AND c=3 AND d=5

 可以看到，当最左边的索引使用范围查询时，右边的索引全部会失效

4.Select  * 导致回表查询

聚簇索引和非聚簇索引，

聚簇索引的B+树子节点包含主键和所有数据

非聚簇索引的B+树子节点包含索引和主键

覆盖索引就是在返回的字段中包含非索引字段会导致，当查询不到想要的字段就会去聚簇索引里面找数据，

在返回列里需要什么字段就写什么字段，最好与查询的条件相符，就是走覆盖查询

5.不等空值还有or导致索引失效

！= ，is null ，or都会导致索引失效，全表扫描

6.like %写在左边导致索引失效

like "%张"会导致索引失效，

会根据首字母来排序进行查询

7.字符串不加单引号也会导致索引失效

隐式转换

会对每一个都进行转换

而如果是 int类型加上了单引号，会自动转换为数字类型，不会导致索引失效

EXPLAIN SELECT * FROM ind WHERE  b='2'

8.SQL优化

索引优化可以从以下几点

1.字段的选择

选择合适的字段效率会更高

2.创建索引，并使用索引

3.避免走回表，尽量走覆盖索引

需要什么字段就返回什么字段，避免使用 * 作为返回列 

4.遵循最左前缀原则

详细请回看 

1.不遵循最左前缀原则

1. **使用索引：**适当的索引能够极大地提升查询性能。特别是对于经常出现在WHERE子句、JOIN操作中或者需要排序和分组的字段，应该创建索引。但要注意，索引并非越多越好，过多的索引会增加写入时的开销并占用更多的存储空间。

2. **避免全表扫描：**全表扫描往往效率低下，尽量通过优化查询和使用索引避免全表扫描。

3. **合理使用JOIN：**JOIN操作通常会导致性能下降。尽量减少JOIN的数量，特别是避免在大表上做JOIN操作。

4. **减少子查询：**子查询可能会导致SQL性能下降，如果可能，尝试将子查询改写为JOIN。

5. **优化分页查询：**对于分页查询（例如使用LIMIT和OFFSET），尽量避免大的OFFSET值，因为数据库需要扫描过所有的前置记录。

6. **合理使用LIKE操作符：**如果使用LIKE操作符，尽量避免在开始位置使用%，因为这将无法利用索引。

7. **减少返回的数据量：**只选择你需要的列，而不是使用SELECT *。同时，使用LIMIT限制返回的行数。

8. **使用预编译SQL和批处理：**预编译SQL可以降低数据库解析和编译SQL的次数，提高性能。批处理可以减少数据库交互的次数。

9. **定期进行数据库维护：**定期对数据库进行优化和维护，如更新统计信息，重建索引，清理过时数据等。

10. **使用EXPLAIN分析查询：**MySQL的EXPLAIN命令可以帮助你理解MySQL如何执行你的SQL，从而找到可能的性能瓶颈。

---

9.索引技巧

可以使用md5加密代替索引

比如给地址加索引

将地址加密为md5，并创建索引

MD5哈希值作为索引性能好的原因主要有两个：

1. **固定长度：**MD5哈希值的长度是固定的，总是32个字符（或者说128位）。这意味着数据库可以在存储和检索这些索引时使用恒定的空间，这有助于提高效率。而如果索引的长度不一致，数据库可能需要花费更多的时间来处理索引，特别是在处理长索引时。

2. **高唯一性：**MD5哈希函数有一个很好的特性，那就是即使输入的差异非常小，产生的哈希值也会有很大的不同。这使得MD5哈希值在大多数情况下都是唯一的。因此，MD5哈希值作为索引时，能够高效地区分和定位数据，提高查询性能。

然而，需要注意的是，虽然MD5哈希值作为索引性能好，但由于存在哈希冲突的可能性，所以在处理涉及数据安全性的场景时（例如密码存储等），不推荐使用MD5哈希值作为索引。此外，MD5也存在被破解的风险，因此在需要高安全性的场景中，建议使用更为安全的哈希函数，如SHA-256。

不锁表的情况下创建索引 这就意味着在创建索引的过程中，表上的读写操作不会被阻止，也就是所谓的"不锁表" 

 ##ALGORITHM=INPLACE, LOCK=NONE;


ALTER TABLE table_name ADD INDEX index_name (column_name), ALGORITHM=INPLACE, LOCK=NONE;