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进行查询

到此这篇关于MySQL索引原理、索引失效场景及索引优化的文章就介绍到这了,更多相关内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文地址：https://blog.csdn.net/forgetmiss/article/details/133280794