【学习笔记】MySQL的常见问题-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43849468/article/details/124625424

本文详细探讨了MySQL中如何查询男女比例、避免SQL索引失效的场景，包括联合索引、函数使用、LIKE操作、类型转换、OR条件、比较操作、isnotnull、NOTIN/NOTEXISTS和ORDER BY等问题。掌握这些关键知识点，提升数据库查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL的常见问题

奇奇怪怪的SQL

查询表中某项数据的占比

一个表中，有男生，有女生；查询男生女生的分别占比

SELECT
	(
		CASE
		WHEN sex = '男' THEN
			1
		ELSE
			0
		END
	) gender,
	count(id) number,
	count(id)/(SELECT count(id) from user) proportion
FROM user GROUP BY sex

函数语法

case when

SELECT
		CASE
	WHEN STATUS = '1' THEN
		'状态1'
	WHEN STATUS = '2' THEN
		'状态2'
	WHEN STATUS = '3' THEN
		'状态3'
	WHEN STATUS = '0' THEN
		'状态4'
	ELSE
		'状态5'
	END AS '状态' FROM table_name;

必备知识

索引失效场景

联合索引不满足最左匹配原则
- 联合索引遵从最左匹配原则，顾名思义，在联合索引中，最左侧的字段优先匹配。因此，在创建联合索引时，where子句中使用最频繁的字段放在组合索引的最左侧。
  
  而在查询时，要想让查询条件走索引，则需满足：最左边的字段要出现在查询条件中。
**使用了select ***
- 第二种索引失效场景：在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引

索引列参与了运算

select * from t_user where id + 1 = 2 ;

//索引列参与了运算，会导致全表扫描，索引失效

索引列参使用了函数
- ```
explain select * from t_user where SUBSTR(id_no,1,3) = '100';
```
  索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引索引失效。同时，还伴随着性能问题。

Like的错误使用

方式一：like ‘%abc’；
方式二：like ‘abc%’；
方式三：like ‘%abc%’；

占位符出现在首部，导致无法走索引。

类型隐式转换
- ```
explain select * from t_user where id_no = 1002;
```
  原因分析：这里假设id_no字段的类型是varchar，但在SQL中使用了int类型，导致全表扫描。参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。
OR操作
- ```
explain select * from t_user where id = 2 or username = 'Tom2';
```
- id建了索引，username没有建索引
- 单独使用username字段作为条件很显然是全表扫描，既然已经进行了全表扫描了，前面id的条件再走一次索引反而是浪费了。所以，在使用or关键字时，切记两个条件都要添加索引，否则会导致索引失效。
- ```
//如果or两边同时使用“>”和“<”，则索引也会失效：

explain select * from t_user where id  > 1 or id  < 80;
```
- 查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效。
两列作比较
- ```
explain select * from t_user where id > age;
```
- 两列数据做比较，即便两列都创建了索引，索引也会失效。
is not null
- 查询条件使用is null时正常走索引，使用is not null时，不走索引。
- ```
explain select * from t_user where id_no is not null;
```
not in和not exists

- **查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效**。
- **查询条件使用not exists时，索引失效**。

order by导致索引失效
- 当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证。
参数不同导致索引失效
- 当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。
其他

动态获取表的结构属性

```
show full columns from tableName
```

SELECT
		COLUMN_NAME 字段名,
		column_comment 字段说明,
		column_type 字段类型,
		column_key 约束
	FROM
		information_schema. COLUMNS
	WHERE
		table_schema = '数据库名'
	AND table_name = '表名';