MySQL索引优化（超详细）篇章2--索引调优

1.索引失效状况

MySQL索引失效通常指的是查询语句无法有效地利用索引，而导致全表扫描或者使用了不合适的索引，从而影响查询性能。这可能是由于查询条件不符合索引的最左匹配规则、使用了函数或表达式、数据分布不均匀等原因导致的。下面是一些常见的MySQL索引失效情况：

1. 未使用索引：当查询语句中的条件没有与任何索引字段匹配时，MySQL将无法使用索引，从而执行全表扫描。例如，如果查询语句中的条件是一个不在索引列中的字段，或者没有条件限制。

   SELECT * FROM table WHERE non_indexed_column = 'value';
   

2. 未使用最左前缀：MySQL索引采用最左前缀匹配规则，如果查询条件不是索引的最左前缀，索引也将无法被利用。例如，如果索引是 (col1, col2)，而查询条件只使用了 col2，那么索引将无法被使用。

   SELECT * FROM table WHERE col2 = 'value';
   

3. 使用函数或表达式：当查询语句中使用了函数或者表达式，MySQL无法使用索引。例如，在WHERE子句中使用了函数或者对列进行了运算。

   SELECT * FROM table WHERE YEAR(date_column) = 2022;
   

4. 数据分布不均匀：如果索引列上的数据分布不均匀，MySQL可能会选择不使用索引，而进行全表扫描。这种情况通常出现在某些值的重复度较高或者数据分布不均匀的情况下。

   SELECT * FROM table WHERE indexed_column = 'value_with_low_selectivity';
   

5. 索引失效于范围查询：在某些情况下，MySQL无法有效利用索引执行范围查询，例如使用了LIKE、<>、!=等非等值比较操作符。like以通配符%开头索引失效

   SELECT * FROM table WHERE indexed_column LIKE '%value%'; 失效
   

6. is null可以使用索引，is not null无法使用索引。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;无法触发索引

7. 不等于(!= 或者<>)索引失效

SELECT * FROM financial_data  WHERE profit_amount != 0;

8. OR 前后存在非索引的列，索引失效
9. 范围条件右边的列索引失效

# 创建索引
ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_age;
ALTER TABLE student DROP INDEX idx_age_classid;
# 索引失效 范围右边的列不能使用。比如：(<) (<=) (>) (>=) 和 between 等
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
# 正确创建索引
create index idx_age_name_classId on student(age,name,classId);
# 正确sql查询语句操作
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

# 未使用到索引 因为classId字段上没有索引，所以上述查询语句没有使用索引。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

10. 使用NOT操作符：在查询语句中使用了NOT操作符，MySQL可能无法有效使用索引。这是因为NOT操作符需要对索引进行反向扫描，而不是利用索引的B+树结构进行快速定位。

    SELECT * FROM table WHERE indexed_column NOT IN ('value1', 'value2');
    

11. 隐式数据类型转换：当查询条件中的数据类型与索引列的数据类型不匹配时，MySQL可能会放弃使用索引。这通常发生在进行隐式数据类型转换时，例如将字符串与数字进行比较。

    SELECT * FROM table WHERE indexed_column = 123; -- 如果indexed_column是字符串类型的话
    

12. 索引列上存在函数：如果索引列上存在函数，MySQL无法使用索引。这种情况通常出现在对列进行了函数操作后进行查询的情况下。

    SELECT * FROM table WHERE UPPER(indexed_column) = 'VALUE';
    

13. 数据类型不匹配：索引列与查询条件的数据类型不匹配时，MySQL可能无法使用索引。例如，如果索引列是字符串类型，而查询条件是数字类型。

    SELECT * FROM table WHERE indexed_column = 123; -- 如果indexed_column是字符串类型的话
    

14. 过度索引：有时候过多的索引可能会导致MySQL优化器选择不合适的索引，从而导致索引失效。这种情况下，MySQL可能会选择扫描索引而不是使用它们。

2.性能分析

1.设置一定阈值的慢查询记录
2.找出sql,进行EXPLAIN 分析
3.调整mysql中配置文件相关参数
4.优化sql语句，避免全查或索引失效
5.定期调整索引顺序

3.表的索引信息–调整索引顺序

SHOW INDEX FROM your_table;
或者
关注Cardinality 表示索引中不同值的数量

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = '表所在数据库名称' AND TABLE_NAME = '表名'
ORDER BY CARDINALITY DESC;--降序排序

在 MySQL 中，SHOW INDEX 命令用于显示表的索引信息在 SHOW INDEX 的结果中，Cardinality 是一个重要的指标，表示索引中不重复的值的估计数量。

具体来说，Cardinality 表示索引中不同值的数量，也就是索引列中唯一值的估计数量。这个值对于查询优化非常重要，因为它可以帮助数据库优化器决定使用哪个索引来执行查询。

如果 Cardinality 的值较高，意味着索引列中的值大部分都是唯一的，这通常对于过滤数据很有帮助，因为它可以帮助数据库更快地定位到所需的行。相反，如果 Cardinality 的值较低，意味着索引列中的值有很多重复，这可能会导致查询优化器选择不使用索引或者选择其他更适合的索引。

在优化查询性能时，你可以根据 Cardinality 的值来评估索引的选择和效率，以确保查询能够以最佳的方式执行。

通过调整索引的顺序，你可以最大程度地利用索引的性能优势，从而提高查询的效率。但需要注意的是，索引的选择和顺序也受到其他因素的影响，如查询的频率、数据分布等，因此需要综合考虑来进行优化。

4.删除冗余索引

select * from sys.schema_redundant_indexes;
查询出的冗余索引 根据字段sql_drop_index 去删除索引
sql_drop_index字段中给出了 具体删除索引的sql语句
1.删除不必要的索引： 删除那些很少被使用或者几乎没有提升查询性能的索引。这样可以减少数据库的存储空间占用，并提高写操作的性能。

2.合并重叠索引： 如果存在涵盖相同列或者重叠的索引，考虑将它们合并成一个索引。这样可以减少索引的数量，并且提高查询的效率。

3.优化索引设计： 对于频繁查询的列，考虑创建适当的复合索引以满足查询需求。同时，避免创建过多的单列索引，以免造成冗余。

4.定期监控和优化索引： 定期监控数据库的索引使用情况，并根据实际情况进行调整和优化。随着数据库中数据的增长和查询模式的变化，索引的需求也会发生变化，因此需要不断进行优化和调整。
索引1：(name, email)
索引2：(name, phone)
1.可以创建复合 name, email，phone
2.删除未使用的索引

5.最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。
MySQL 的多列索引（也称为联合索引）允许你基于表中的多个列创建一个单一的索引。然而，当使用多列索引进行查询时，查询条件中的列必须遵循索引中列的顺序，以便索引能够高效地工作。

5.1下面是一个实际的例子来说明这个概念：

假设我们有一个名为 orders 的表，
其中包含以下字段：order_id, customer_id, order_date, amount。

我们可以为这个表创建一个多列索引，基于 customer_id, order_date 和 amount 字段：

CREATE INDEX idx_customer_order_amount ON orders(customer_id, order_date, amount);

现在，让我们看几个查询例子来说明索引的使用：

1.完全使用索引的查询：
当查询条件包含索引中的所有列，并且顺序与索引中的列顺序一致时，索引会被充分使用。

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-01-01' AND amount > 100;

在这个查询中，我们使用了 customer_id, order_date 和 amount 字段，并且它们的顺序与索引 idx_customer_order_amount 中的顺序一致。因此，这个查询能够高效地使用索引。

2.部分使用索引的查询：
如果查询条件只包含索引中的部分列，并且这些列的顺序与索引中的顺序一致，那么索引仍然会被使用，但是效率可能不如完全使用索引的查询。

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-01-01';

在这个查询中，我们使用了 customer_id 和 order_date 字段，这两个字段在索引 idx_customer_order_amount 中是连续的，因此索引仍然会被使用。

3.跳过索引中列的查询：
如果查询条件跳过了索引中的某个列，那么索引后面的列就无法被使用。

SELECT * FROM orders WHERE order_date = '2023-01-01' AND amount > 100;

在这个查询中，我们跳过了 customer_id 字段，直接使用了 order_date 和 amount 字段。由于 order_date 不是索引的第一个字段，MySQL 无法使用这个多列索引来优化查询，即使 order_date 和 amount 在索引中。

4.未使用索引的查询：
如果查询条件中没有使用索引中的第一个字段，那么多列索引将不会被使用。

SELECT * FROM orders WHERE amount > 100;

在这个查询中，我们只使用了 amount 字段，而 amount 并不是索引 idx_customer_order_amount 的第一个字段。因此，这个查询不会使用多列索引。

通过上面的例子，你可以看到在使用多列索引时，查询条件中列的顺序和索引中列的顺序是多么重要。为了最大化索引的效率，你应该尽量使查询条件中的列顺序与索引中的列顺序相匹配，并且尽量使用索引中的第一个字段。

6.数据长度和索引长度占用空间比较

在 MySQL 表中，数据长度和索引长度取决于表的结构、列的数据类型以及创建的索引类型和大小。通常情况下，索引长度会比数据长度要小，因为索引只是表中数据的一种排序和快速检索方式，它不包含完整的行数据。

数据长度是指表中所有列的数据总长度，包括所有的行。而索引长度则是指索引所占用的存储空间大小。

当然，并不是所有情况下索引长度都比数据长度小，有时候索引长度会随着索引的复杂度增加而增加，特别是在对大量数据或者复杂数据类型进行索引时。

总的来说，索引长度一般是比数据长度要小的，因为索引通常只包含索引列的值和一些额外的元数据，而不是完整的行数据。