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

矫情吗；* 2024-03-01 03:15 94阅读 0赞

文章主要有以下内容：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索引失效场景，索引优化，索引原理

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关【索引失效】MySQL索引失效场景

相关 Mysql数据库索引创建、索引删除、索引失效场景详解

相关 Mysql索引失效的场景

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

相关 MySQL索引失效场景

相关 MySQL索引失效场景

相关 MySql 索引优化和避免索引失效

相关 mysql索引失效的场景

相关 MySQL-索引可能失效场景

相关【MySQL高级】（五）索引失效、索引优化