MYSQL sql的技巧与避坑

文章目录

1.使用union还是or

由于sql中只能用到一个index，如果or的两边是同一个字段，可以用上索引，如果是不同的字段，势必有一个字段全表扫描。union替代or可以使不同字段都用上索引，但是union会对数据进行去重和排序，性能不一定比用or好。但是如果是没有去重要求的数据，可以union all，不去重且不排序，性能就比较好了。

2.可以为NULL字段的逻辑判断

MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

比如查询other_id不为2的记录，other_id可以为NULL，那么where other_id != 2查询出来的记录可能比实际少，因为NULL值记录是UNKNOWN的，不进入ResultSet, 应该使用other_id != 2 or other_id is NULL。

3.in和exists的选择

查询A表中a_id等于B表中b_id的所有A记录。

in 的原理：先执行in中的子查询，再与外表做笛卡尔积。in的子查询只会执行一次，便把结果集缓存到内存中
select * from A where id in (select id from B)

等价于：先select id from B; 再 select id from A where A.id = B.id;

in 只能针对主查询使用索引，not in 则不会使用任何索引。

exists 的原理：遍历外表中的数据，每一个id带入exists子查询中看是否成立，假设A有n条记录，相当与执行了n次exists子查询。exists直返会true和false，因此select 1就可以。

等价于：
select id from A; 再select id from B where B.id = A.id;

exists 会针对子查询的表使用索引;not exists 会对主子查询都会使用索引;

总结：

• in适合外表数据量远大于内表的情况。
• exists适合外表数据远小于内表的情况。
• not exists由于可以用上索引，不管哪种情况，都比not in要快。

总体来说，小表驱动大表，in的时候内表是驱动表，exists的时候外表是驱动表

补充：
如果是括号内查询语句耗时，且查询结果集数量小时，用in合适，如果查询语句简单并且查询结果集大时，用exist合适。

4.if和case的使用

写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字是以’M’开头，那么他的奖金是他工资的100%，如果一个雇员的id是奇数并且他的名字是以’N’开头的，那么他的奖金是50%，否则奖金为0。（%2或mod判断奇偶数）

if形式：

select employee_id,
IF(employee_id%2 = 1 and name like ‘M%’, salary, IF(employee_id%2 = 1 and name like ‘N%’, salary / 2, 0)) as bonus from Employees order by employee_id;

case形式：

select employee_id,
(CASE
WHEN mod(employee_id,2) = 1 and name like ‘M%’ THEN salary
WHEN mod(employee_id,2) = 1 and name like ‘N%’ THEN salary / 2
ELSE 0
END) as bonus from Employees order by employee_id;

5.删除表中重复的记录，只保留id最小的

写一个SQL删除语句，将email重复的记录删除，只保留id最小的

DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id

6.字符串函数

1.concat() 将多个字符串拼接
2.left(str, length) 从左开始截取字符
3.right(str, length) 从右开始截取字符
4.upper(str) 所有字符转成大写
5.lower(str) 所有字符转成小写
6.substring(str, begin, end) 或 substring(str, begin)

7.group_concat 批量连接

表 Activities：

±------------±--------+
| 列名 | 类型 |
±------------±--------+
| sell_date | date |
| product | varchar |
±------------±--------+
此表没有主键，它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。每个日期的销售产品名称应按词典序排列。返回按 sell_date 排序的结果表。

解：
select
sell_date,
count(distinct product) num_sold,
group_concat(
distinct product
order by product
separator ‘,’
) products
from
Activities
group by sell_date

8.rlike 正则模糊查询

如果表中有多个值以空格隔开，找出包含以’NM’开头值的记录
解：select * from a where value rlike ‘^NM|.* \sNM’;

9.ifnull

Employee 表：
±------------±-----+
| Column Name | Type |
±------------±-----+
| id | int |
| salary | int |
±------------±-----+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

解：

SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary

10.日期函数

1.DATE(date)

返回指定日期/时间表达式的日期部分或将文本转为日期格式
示例：

select date(“2022-3-15”);2022-03-15

select date(‘2022-4-15 12:30:48’);2022-04-15

2.YEAR(date)

返回指定日期的年份(范围在1000到9999)
示例：

select year(‘2022-4-15 12:30:48’);2022

类似的，MONTH(date)返回指定日期的月份(范围在1到12）；DAY(date)返回指定日期的日(范围在1到31)；HOUR(datetime)返回指定时间的小时(范围在0-23)；minute(datetime)返回指定时间的分(范围在0-59)；second(datetime)返回指定时间的秒(范围在0-59)。

3.对日期进行加减运算

ADDDATE(date,interval expr type)
DATE_ADD(date,interval expr type)
SUBDATE(date,interval expr type)
DATE_SUB(date,interval expr type)

其中，date是一个datetime或date值；expr是对date进行加减法的一个表达式字符串或一个数字；type指明表达式expr应该如何被解释，是减去1天还是一年等。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zdn6WrD1-1689158507889)(evernotecid://CF273F2A-DFE6-4438-B24E-1642BCBC2DAC/appyinxiangcom/39628498/ENResource/p113)]

示例：
select adddate(‘2022-4-1’,interval 5 day);2022-04-06

select adddate(‘2022-4-15 13:30:28’,interval ‘3 1:2’ day_minute);2022-04-18 14:32:28

4.DATE_FORMAT(date, format)

根据format字符串格式化date值，常用于获取日期的年月日和时间

在format字符串中可用标志符
%M 月名字(january……december)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(sun……sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(jan……dec)
%j 一年中的天数(001……366)
%h 十二时制的小时(00……12)
%k 二十四时制的小时(0……23)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [ap]m)
%s 秒(00……59) %p am或pm
%w 一个星期中的天数(0=sunday ……6=saturday ）
%u 一年中的周数(1……53)

示例：

select date_format(‘2022-4-15 13:30:28’,‘%Y-%m-%d’);2022-04-15

5.CURDATE()

以’yyyy-mm-dd’或yyyymmdd格式返回当前日期值(根据返回值所处上下文是字符串或数字)
示例：

select curdate();2022-04-15

select curdate()+0;20220415

6.CURTIME()

以’hh:mm:ss’或hhmmss格式返回当前时间值(根据返回值所处上下文是字符串或数字)
示例：
select curtime();22:26:44

7.NOW()

以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回当前日期时间(根据返回值所处上下文是字符串或数字
示例：

select now();2022-04-15 22:28:33

8.TIMESTAMPDIFF(type,expr1,expr2)

返回起始日expr1和结束日expr2之间的时间差整数。

时间差的单位由type指定：
second 秒
minute 分
hour 时
day 天
month 月
year 年

示例：

select timestampdiff(day,‘2022-4-1’,‘2022-4-15’) 14

9.DATEDIFF(date1, date2)
返回 date1 - date2 的天数

10.UNIX_TIMESTAMP([date])

返回一个unix时间戳(从’1970-01-01 00:00:00’开始的秒数,date默认值为当前时间)
示例：

select unix_timestamp(‘2022-4-15’);1649952000

11.FROM_UNIXTIME(unix_timestamp)

以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回时间戳的值(根据返回值所处上下文是字符串或数字)

示例：

select from_unixtime(1649952001);2022-04-15 00:00:01

11.大表分页查询

https://blog.csdn.net/w907645377/article/details/122234432

12.索引不生效的经典场景

特别容易忽视的：
1.where使用的索引与order by使用的索引一致或为联合索引，order by才可能用上索引
2.不同表utf8和ut8mb4字段连接
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dHcEzou9-1689158507889)(evernotecid://CF273F2A-DFE6-4438-B24E-1642BCBC2DAC/appyinxiangcom/39628498/ENResource/p114)]

13.订单最多的客户

表: Orders

±----------------±---------+
| Column Name | Type |
±----------------±---------+
| order_number | int |
| customer_number | int |
±----------------±---------+
Order_number是该表的主键。
此表包含关于订单ID和客户ID的信息。

编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。

解：
SELECT
customer_number
FROM
orders
GROUP BY customer_number
ORDER BY COUNT(1) DESC
LIMIT 1
;

14.in查询慢的原因

见下面的链接：
https://blog.csdn.net/w907645377/article/details/123129383

15.主键索引和普通索引

• 主键索引的优先级高于普通索引
• 主键索引及其他唯一索引，使用in可能导致索引失效，具体看14
• 一条查询语句只能用上一个普通索引
• order by 主键 可以搭配 where 任意普通索引 使用
• 如果order by 普通索引一 搭配 where 普通索引二使用，可以考虑建立 普通索引一和普通索引二的联合索引

16.where条件中有多个二级（普通）索引，如何优化？

where条件中的多个索引来自同一张表：
可以考虑联合索引，mysql自身也会mergeIndex，但这意味着索引设计不合理

where条件中的多个索引来自不同表：
比如a join b，或 a exists b等，可以考虑b表使用子查询，将非连接列的索引使用上，得到的子查询结果c，再与a表进行连接，这样可以用上尽量多的索引

17.limit分页，普通索引转主键索引，避免回表

比如：我们要分页查询状态为5的订单

一般写法：
select * from order where status = 5 limit 20000,20;
缺点：查询订单表的所有字段，通过status查询到主键id后，需要回表，主要问题是limit比较大，limit的原理是查出20000 + 20条，然后扔掉前20000条，这就意味着要回表20020次， 十分耗时

主键索引优化写法：
select * from order o1, (select id from order where status = 5 limit 20000, 20) o2 where o1.id = o2.id;
优点是不用回表了，但是依然有扫描20000 + 20条

更优化的写法，使用id分页，加一个查询条件 id >= minId，这次的最后一条id + 1，作为下一次分页的minId
这个链接也有类似介绍
https://blog.csdn.net/S_ZaiJiangHu/article/details/125821319

18.where order_id = ? order by id asc limit n

这条sql，看上去应该会使用order_id的索引，实际上当n很小的时候，优化器考虑扫码id索引树（全表扫描）可能比使用order_id的索引更快，最终使用了id索引。
优化：
1.强制索引
2.where order_id = ? order by (id+0) asc limit n; 骗过优化器，使用order_id的索引

19.CPU飙升超过100%怎么办？

首先，我们要对问题定位而不是盲目的开启什么 慢日志，在并发量大并且大量SQL性能低的情况下，开启慢日志无意是将MySQL推向崩溃的边缘。

1.不推荐在这种CPU使用过高的情况下进行慢日志的开启。因为大量的请求，如果真是慢日志问题会发生日志磁盘写入，性能贼低。
2.直接通过MySQL show processlist命令查看，基本能清晰的定位出部分查询问题严重的SQL语句，kill 掉这些线程(同时观察 cpu 使用率是否下降)， 一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。
3.再则一定要使用缓存系统，降低对MySQL的查询频次。
4.对于内存调优，也是一种解决方案。