MySQL索引失效场景，索引优化，索引原理

文章主要有以下内容：MySQL性能优化、索引优化、索引失效，索引原理、索引分类。

索引是一个用于提高数据库查询速度的数据结构。不必搜索整个数据库，通过使用索引，数据库系统可以直接找到存储在磁盘上的特定行的地址。没有索引，查询大型数据库可能非常缓慢。

一 索引分类

按照字段类型分类，MySQL索引可以分为以下几种：

1. 普通索引:

 这是最基本的索引，没有任何特殊的限制。

   CREATE INDEX idx_name ON table_name(column_name);

2. 唯一索引:

    与普通索引类似，但要求索引列的所有值都是唯一的（不包括NULL值，因为在MySQL中，多个NULL值是被视为相互不同的）。

CREATE UNIQUE INDEX idx_unique_name ON table_name(column_name);

3. 主键索引:

   每个表只能有一个主键索引。

   它要求所有的主键列的值都是唯一的。

   InnoDB存储引擎的表中，数据的物理存储顺序与主键的顺序相同，也即主键索引是聚簇索引。

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

4. 全文索引:

   专门用于全文搜索。

   仅在MyISAM和InnoDB（从MySQL 5.6版本开始）存储引擎的CHAR、VARCHAR和TEXT列上支持。

   允许使用`MATCH AGAINST`语法来执行全文搜索。

CREATE FULLTEXT INDEX idx_fulltext_name ON table_name(column_name);
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('keyword');

5. 空间索引:

  专门为地理空间数据类型设计，如`GEOMETRY`、`POINT`、`LINESTRING`、`POLYGON`等。

   使用R-Tree算法。

   允许进行空间位置的查询。

CREATE SPATIAL INDEX idx_spatial_name ON table_name(column_name);SELECT * FROM table_name WHERE MBRContains(column_name, GeomFromText('Polygon(...)'));

6. 前缀索引:

   不是整列作为索引，而是使用列值的前缀部分。

   适用于字符串类型的列，尤其是当文本内容很长时。

CREATE INDEX idx_prefix_name ON table_name(column_name(10));  -- 只使用column_name的前10个字符作为索引

二 索引失效场景

索引失效指的是在进行数据库查询时，虽然存在合适的索引，但由于某种原因，查询并没有使用该索引，从而可能导致性能下降。以下是常见的索引失效的例子及代码示例：

1. 使用LIKE操作符并以通配符开头:

SELECT * FROM users WHERE name LIKE '%John';

2. 使用NOT操作符:

  SELECT * FROM users WHERE NOT age = 25;

3. 列与列的计算:

SELECT * FROM users WHERE salary - tax > 5000;

4. 使用函数处理列:

   SELECT * FROM users WHERE MONTH(birthdate) = 5;

5. 隐式类型转换:

  SELECT * FROM users WHERE age = '25'; -- age是整数列

6. 使用OR操作符连接不在同一索引中的列:

   SELECT * FROM users WHERE age = 25 OR name = 'John';

7. 单独查询复合索引的非最左部分:

   -- 假设存在复合索引(age, name)   SELECT * FROM users WHERE name = 'John';

8. 使用JOIN但未索引的列:

  SELECT * FROM users u JOIN orders o ON u.userID = o.clientID; -- 如果clientID未被索引

9. 对列使用不等式操作符:

   SELECT * FROM users WHERE age <> 25;

10. 使用DISTINCT关键字但没有相应的索引:

    SELECT DISTINCT city FROM users;

11. 对已索引列进行算数操作:

    SELECT * FROM products WHERE price * 0.9 > 100;

12. 使用IS NULL和IS NOT NULL但没有对应的索引:

    SELECT * FROM users WHERE address IS NULL;

13. 使用IN操作符的列表过大:

    SELECT * FROM users WHERE age IN (25, 26, 27, ... , 50);

14. 在JOIN操作中连接类型不匹配的列:

 SELECT * FROM users u JOIN details d ON u.id = CAST(d.user_id AS CHAR);

15. 查询范围过大，使得全表扫描更为高效:

    SELECT * FROM users WHERE age > 10;

16. 索引列上使用负操作:

 SELECT * FROM products WHERE NOT price = 100;

17. 复合索引中某一列使用范围查询:

    -- 假设存在复合索引(age, score)    SELECT * FROM users WHERE age > 25 AND score = 100;

18. 使用COALESCE函数:

 SELECT * FROM users WHERE COALESCE(name, 'Unknown') = 'John';

19. 使用UNION操作:

    SELECT name FROM users WHERE age = 25    UNION    SELECT name FROM users WHERE city = 'Paris';

20. 查询不在索引范围内的数据:

    SELECT * FROM users WHERE age = 500; -- 如果数据库中年龄不可能为500

21. 在HAVING子句中使用未被索引的列:

    SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 5;

22. 查询结果集太大，导致索引回表:

    SELECT * FROM users WHERE age BETWEEN 20 AND 30;

23. 在ORDER BY子句中使用多个未被索引的列:

    SELECT * FROM users ORDER BY age, name;

24. 使用外部参数进行查询:

    SET @name = 'John';    SELECT * FROM users WHERE name = @name;

25. 使用多个JOIN且索引未被优化:

    SELECT * FROM users u JOIN orders o ON u.id = o.user_id JOIN details d ON o.id = d.order_id;

26. 在WHERE子句中使用CASE语句:

    SELECT * FROM users WHERE (CASE WHEN age = 25 THEN 1 ELSE 0 END) = 1;

27. 对JSON类型的数据进行查询:

    SELECT * FROM users WHERE profile->'$.age' = 25;

28. 查询含有较多重复值的列:

    SELECT * FROM users WHERE gender = 'male'; -- 如果90%的用户都是male

29. 使用非确定性函数:

    SELECT * FROM users WHERE birthdate = NOW();

30. 使用存储过程中的动态SQL:

    CALL search_users('John'); -- 如果存储过程内部使用了动态SQL进行查询