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个字符作为索引
二 索引失效场景
索引失效指的是在进行数据库查询时，虽然存在合适的索引，但由于某种原因，查询并没有使用该索引，从而可能导致性能下降。以下是常见的索引失效的例子及代码示例：
使用LIKE操作符并以通配符开头:
SELECT * FROM users WHERE name LIKE ‘%John’;
使用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';
使用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进行查询
╰+攻爆jí腚メ
阳光穿透心脏的1/2处
今天药忘吃喽~
灰太狼
还没有评论，来说两句吧...