前言
索引在数据库中具有重要的作用，它可以加快查询速度、提高数据库性能，以及减少资源消耗。索引是建立在表或视图上的数据结构，可以快速定位和访问数据，特别是在大型数据表中。
索引失效导致全表扫描：当查询的条件无法使用索引来加速检索，数据库可能会选择执行全表扫描操作，这会导致查询性能低下。
1、隐式的类型转换导致索引失效
假设我们有一个包含user_id和username两列的表格user_info：
user_id (int) | username (varchar) ------------------------------------- 1 | John 2 | Kate 3 | Mike
现在我们创建了一个索引在user_id列上
CREATE INDEX idx_user_id ON user_info(user_id);
如果我们执行一个查询，以user_id作为查询条件，但是以字符串形式传递该条件，这会导致隐式类型转换：
SELECT * FROM user_info WHERE user_id = '1';
尽管我们为user_id列创建了索引，但由于查询条件中的隐式类型转换，MySQL 将'1'视为一个字符串，而不是整数。这会导致索引无法生效，数据库将会执行全表扫描来搜索匹配的行。为了确保索引的有效使用，我们应该使用匹配列的正确数据类型进行查询：
SELECT * FROM user_info WHERE user_id = 1;
在此例中，将查询条件中的字符串转换为整数，使得索引能够生效并提高查询性能。因此，当涉及到隐式类型转换时，特别是在涉及索引的查询中，需要小心使用正确的数据类型，以确保索引的有效使用
2、查询条件包括or，可能导致索引失效
假设我们有一个包含category和price两列的表格product：
category (varchar) | price (int) ------------------------------------- Electronics | 100 Clothing | 50 Furniture | 200
现在我们创建了一个索引在category列上：
CREATE INDEX idx_category ON product(category);
如果我们执行一个查询，以category和price作为查询条件，并使用 OR 运算符将它们组合起来：
SELECT * FROM product WHERE category = 'Electronics' OR price > 150;
尽管我们为category列创建了索引，但是由于查询条件中存在 OR 运算符，MySQL 将无法有效使用索引。这是因为 OR 运算符要求数据库同时检索满足两个条件的数据，而无法利用单一列的索引进行快速查找。
在这种情况下，数据库将执行全表扫描来搜索匹配的行，否则将针对每个条件进行单独的索引扫描，并将结果合并起来。
为了确保索引的有效使用，我们可以考虑使用 UNION 或多个独立的查询来替代 OR 运算符，以分别针对每个条件使用索引：
SELECT * FROM product WHERE category = 'Electronics' UNION SELECT * FROM product WHERE price > 150;
我们将查询拆分为两个独立的查询，并分别使用适当的索引，以提高查询性能。因此，当查询条件中存在 OR 运算符时，我们需要小心处理，尝试使用其他查询构造方式，以确保索引的有效使用。
3、like通配符可能导致索引失效
假设我们有一个包含 name 和 age 两列的表格 users：
name (varchar) | age (int) ------------------------------- John | 25 Kate | 30 Mike | 35
现在我们创建了一个索引在 name 列上：
CREATE INDEX idx_name ON users(name);
如果我们执行一个查询，使用 LIKE 通配符搜索包含某个模式的 name：
SELECT * FROM users WHERE name LIKE '%at%';
尽管我们为 name 列创建了索引，但是由于使用了 % 通配符在 LIKE 子句中，这会导致索引无法生效。因为 % 通配符表示匹配任意数量的字符，导致数据库无法使用索引进行快速查找，而需要对每一行进行模式匹配的比较。
在这种情况下，数据库可能会选择执行全表扫描来搜索匹配的行，以满足模式的要求。
为了确保索引的有效使用，我们可以考虑使用前缀搜索或者全文索引来优化查询：
- 使用前缀搜索：如果我们只关心
name列以 “at” 开头的数据，可以改为使用前缀搜索，这样索引可以生效：
SELECT * FROM users WHERE name LIKE 'at%';
- 全文索引：如果我们需要更灵活的模式匹配功能，可以考虑使用全文索引，如 MySQL 中的全文搜索（FULLTEXT）索引或其他搜索引擎。
在使用 LIKE 通配符时，特别是在通配符前使用 % 时，需要注意可能导致索引失效，需要考虑使用其他方法优化查询。
4、查询条件不满足联合索引的最左匹配原则
假设我们有一个包含 category、price、color 三列的表格 products：
category (varchar) | price (int) | color (varchar) ------------------------------------------------------- Electronics | 100 | Red Clothing | 50 | Blue Furniture | 200 | Green
现在我们创建了一个联合索引在 category、price 两列上：
CREATE INDEX idx_category_price ON products(category, price);
如果我们执行一个查询，以 price 作为查询条件：
SELECT * FROM products WHERE price = 100;
尽管我们为 category 和 price 列创建了联合索引，但是查询条件只涉及到 price 列，不满足最左匹配原则。按照最左匹配原则，索引的左边列必须出现在查询条件中才能使用索引，而右边列可以省略。
按照mysql的索引底层是用B+树来实现的，我们可以知道只有，从上而下的查询才能使用索引这也是最左原则的底层
由于查询条件不包含 category 列，查询优化器无法利用联合索引，可能选择执行全表扫描来搜索匹配的行。
为了确保联合索引的有效使用，我们应该按照索引的顺序，保证查询条件满足最左匹配原则：
SELECT * FROM products WHERE category = 'Electronics' AND price = 100;
当使用联合索引时，需要确保查询条件满足最左匹配原则，为索引的多个列提供适当的查询条件，以避免索引失效。
5、在索引列上使用mysql的内置函数
假设有一个表 users，其中有一个索引列 name，我们想要执行一个查询，获取所有姓为 “Smith” 的用户记录。我们可能会尝试使用 MySQL 的内置函数 SUBSTRING() 来提取姓氏，然后在索引列上使用该函数进行查询，如下所示：
SELECT * FROM users WHERE SUBSTRING(name, 1, 5) = 'Smith';
然而，这种查询在索引列上使用了 SUBSTRING() 函数，这会导致索引失效。MySQL 查询优化器无法直接利用索引来定位满足条件的记录，而会执行全表扫描或使用其他索引进行查询。
为了能够有效地利用索引，应尽量避免在索引列上使用内置函数。在上述情况下，最好直接使用类似以下的查询，不使用函数：
SELECT * FROM users WHERE name LIKE 'Smith%';
6、对索引进行列运算（如，+、-、*、/）
假设有一个表 products，其中有两个列 price 和 discount，我们希望查询所有价格大于原价减去折扣后的一半的产品。我们可能会尝试在查询中对索引列进行算术运算，如下所示：
SELECT * FROM products WHERE price > (price - discount) / 2;
然而，这样的查询会导致索引失效。MySQL 查询优化器无法直接利用索引来执行列级别的算术运算，并且可能会执行全表扫描或使用其他索引进行查询。
为了有效利用索引，应尽量避免在查询条件中对索引列进行算术运算。在上述情况下，可以先计算需要的值，然后在查询中使用计算结果进行比较，如下所示：
SELECT * FROM products WHERE price > ((price - discount) / 2);
通过这种方式，在执行查询时，可以有效地利用索引，并加快查询的性能。
需要注意的是，如果在查询中使用了常量或者已经存在的列进行算术运算，MySQL 查询优化器可能会对查询进行一些优化，以提高性能。但是，在索引列上执行算术运算往往会导致索引失效，因此仍建议避免在索引列上进行列级别的计算操作。
7、索引字段上使用（！ = 或者 < >）
假设有一个表 users，其中有一个索引字段 age，我们想要查询所有年龄不等于 25 的用户记录。我们可能会尝试使用 != 操作符来进行查询，如下所示：
SELECT * FROM users WHERE age != 25;
然而，这样的查询可能会导致索引失效。因为 MySQL 的 B-Tree 索引存储的是有序的键值，它只能高效地利用索引来定位特定键值或键值范围的数据。但是，使用 != 操作符相当于一个不等条件，无法构建有效的键值范围，因此 MySQL 通常会选择不使用索引，而执行全表扫描来获取结果。
为了避免索引失效，可以使用等于操作符 = 和 NOT 运算符来取代 !=，如下所示：
SELECT * FROM users WHERE age = 25;
或者使用等价的条件：
SELECT * FROM users WHERE NOT (age = 25);
这样可以有效利用索引，提高查询性能。
需要注意的是，对于一些特殊情况，MySQL 查询优化器可能会对查询进行一些优化，以在某些情况下使用索引和避免索引失效。但是，一般情况下，使用 != 或者 <> 操作符会导致索引失效，因此最好避免在索引字段上使用这些操作符。
8、使用not in 或not exists
假设有两个表：orders 表和 order_items 表，它们之间通过订单ID进行关联。我们想要查询所有没有对应订单的订单项。
- 使用
NOT IN：
SELECT * FROM order_items WHERE order_id NOT IN (SELECT order_id FROM orders);
- 这个查询会查询
order_items表中的订单项，但是需要排除掉在orders表中存在的订单ID。在这个查询中，子查询(SELECT order_id FROM orders)会返回所有的订单ID。如果orders表中的订单ID较多，子查询的结果集会很大，导致 MySQL 在执行主查询时需要扫描大量数据，无法高效地利用索引。这可能导致索引失效，性能下降。 - 使用
NOT EXISTS：
SELECT * FROM order_items oi WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_id = oi.order_id);
- 这个查询会查询
order_items表中的订单项，并使用子查询(SELECT 1 FROM orders o WHERE o.order_id = oi.order_id)来判断是否存在对应的订单。尽管NOT EXISTS子查询只需要找到第一个匹配的结果，就可以确定不存在匹配的结果，但是如果子查询中的条件复杂或执行计划不佳，也可能导致索引失效或影响查询性能。
为了避免这种情况，可以考虑使用其他方式，比如使用 LEFT JOIN 结合 IS NULL：
SELECT oi.* FROM order_items oi LEFT JOIN orders o ON o.order_id = oi.order_id WHERE o.order_id IS NULL;
这种方式可以利用 LEFT JOIN 并检查 orders 表中的订单ID是否为 NULL，来确定哪些订单项没有对应的订单。这样就能更有效地利用索引，提高查询性能。
需要根据实际情况评估并选择最适合的查询方式来提高性能，并进行必要的索引调整和查询优化
9、order by
举个例子，假设有一个 customers 表，其中有一个索引在 last_name 列上。我们想要按照姓氏字母顺序对客户进行排序。
如果我们直接在 last_name 列上进行排序，索引可以被有效利用：
SELECT * FROM customers ORDER BY last_name;
然而，如果我们对非索引列进行排序，可能导致索引失效：
SELECT * FROM customers ORDER BY first_name;
在这个例子中，我们尝试对 first_name 列进行排序，而该列并没有索引。这可能导致无法有效利用索引，因为 MySQL 会优先使用索引的排序而不使用随机读取（全表扫描）。
排序顺序与索引的顺序不匹配也可能导致索引失效：
SELECT * FROM customers ORDER BY last_name DESC;
索引是按照升序（ASC）排列的，而我们尝试以降序（DESC）进行排序。虽然数据库可以通过排序结果的逆向扫描来实现降序排序，但这可能会导致索引失效，因为索引无法直接按照所需的顺序匹配。
为了避免索引失效，需要确保选择的排序列与索引列匹配，并尽量避免在排序中使用函数、表达式或非索引列。如果需要使用函数或表达式进行排序，可以考虑创建适当的函数索引来优化查询性能。
需要根据具体情况评估并选择最佳的查询方式，以确保索引能够有效使用，从而提高查询性能。
10、优化器选错了索引
假设有一个 orders 表，其中包含了订单的不同属性，包括订单号 order_number（主键）、客户ID customer_id、订单日期 order_date 等属性。我们要查询特定客户在某个时间范围内的订单数量。
假设我们执行以下查询：
SELECT COUNT(*) FROM orders WHERE customer_id = 12345 AND order_date BETWEEN '2022-01-01' AND '2022-12-31';
如果在 orders 表上存在 customer_id 列和 order_date 列的索引，优化器应该选择同时使用这两个索引进行查询。但是，优化器有可能错误地选择了仅使用其中一个索引。
例如，优化器可能选择了只使用 customer_id 索引，然后在结果集中进一步过滤日期范围。这将导致优化器扫描大量不符合日期过滤条件的记录，导致性能下降。
为了解决这个问题，我们可以引导优化器选择正确的索引，通过使用查询提示（query hint）或重构查询语句来指定优化器应选择的索引：
SELECT COUNT(*) FROM orders USE INDEX (customer_id, order_date) WHERE customer_id = 12345 AND order_date BETWEEN '2022-01-01' AND '2022-12-31';
在这个改进后的查询中，我们使用了 USE INDEX 查询提示来告诉优化器显式使用了包含 customer_id 和 order_date 列的联合索引。
通过引导优化器选择正确的索引，我们可以提高查询性能，避免优化器选择错误的索引导致性能下降的问题。需要注意的是，引导优化器的行为需要基于实际情况进行评估，并确保所选的索引在大多数查询场景下都是最优的选择。
