文章目录

      • 1.使用union还是or
      • 2.可以为NULL字段的逻辑判断
      • 3.in和exists的选择
      • 4.if和case的使用
      • 5.删除表中重复的记录,只保留id最小的
      • 6.字符串函数
      • 7.group_concat 批量连接
      • 8.rlike 正则模糊查询
      • 9.ifnull
      • 10.日期函数
      • 11.大表分页查询
      • 12.索引不生效的经典场景
      • 13.订单最多的客户
      • 14.in查询慢的原因
      • 15.主键索引和普通索引
      • 16.where条件中有多个二级(普通)索引,如何优化?
      • 17.limit分页,普通索引转主键索引,避免回表
      • 18.where order_id = ? order by id asc limit n
      • 19.CPU飙升超过100%怎么办?

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形式:

selectemployee_id,
IF(employee_id%2=1andnamelike‘M%’,salary,IF(employee_id%2=1andnamelike‘N%’,salary/2,0))asbonusfromEmployeesorderbyemployee_id;


case形式:

selectemployee_id,
(CASE
WHENmod(employee_id,2)=1andnamelike‘M%’THENsalary
WHENmod(employee_id,2)=1andnamelike‘N%’THENsalary/2
ELSE0
END)asbonusfromEmployeesorderbyemployee_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(distinctproduct)num_sold,
group_concat(
distinctproduct
orderbyproduct
separator‘,’
)products
from
Activities
groupbysell_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
GROUPBYcustomer_number
ORDERBYCOUNT(1) DESC
LIMIT1
;

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.对于内存调优,也是一种解决方案。