1、SQL高级用法
MySQL 常用语句积累，提升性能
2、连表查询
2.1 常用连表查询
- 左连接 （left join)
- 右连接 (right join)
- 内连接 (join/inner join)
2.2 小表与大表连接
场景：基础表连接关系表 …
-
in后面跟的是小表，exists后面跟的是大表。
- ①当B表的数据集小于A表数据集时，用in优于exists。 (B表为tb_dept_bigdata：100条数据，A表tb_emp_bigdata：5000条数据。)
select * from tb_emp_bigdata A where A.deptno in (select B.deptno from tb_dept_bigdata B)- ②当A表的数据集小于B表的数据集时，用exists优于in。
2.3 多字段排序问题
场景：按照某一状态和时间降序排列
-- 错误：
select xx from table_name order by status,time desc;
-- 正解：
select xx from table_name order by status desc,time desc;
-- 过滤排序（某一状态排在前面，后面按照其他属性排序）2021.04.26 (万楼汇)
order by (case when relation_type=2 then 1 else 2 end),start_time desc,id desc
2.4 多字段相同（含非空）的连表查询
SELECT
a.order_id,
r.premises_id,
now( ),
0,
now( ),
2
FROM
(
SELECT
pii.province_id,
pii.city_id,
pii.area_id,
pii.premises_name,
pii.premises_type,
wli.order_id
FROM
workorder_lock_info AS wli
INNER JOIN premises_import_info AS pii ON wli.material_id = pii.material_id
AND pii.isdelete = 0
WHERE
wli.isdelete = 0
AND wli.order_status IN ( 2, 3 )
) AS a
INNER JOIN premises_result_info r ON a.province_id = r.province_id
AND a.city_id = r.city_id
AND a.area_id = r.area_id
AND a.premises_name = r.premises_name
-- 城市ID、区域ID、楼盘名称不为空，楼盘类型可能为空（为空时特殊处理：为空串或者为NULL）
AND ( a.premises_type = r.premises_type OR ( a.premises_type IS NULL AND r.premises_type IS NULL ) )
WHERE
r.isdelete = 0
GROUP BY
a.order_id
2.5 is null 与 is not null 是否走索引
2.6 单表删除，别名失效
-- 别名a 失效，需要将别名去掉
delete from table_name a where a.field_name = ''
2.7 多字段IN
-- 万楼汇查询多余重复数据 20210813
SELECT
id
FROM
premises_result_info_version a
WHERE
premises_id in (select premises_id from premises_field_source_info_new group by premises_id having count(premises_id)>50) and
(a.premises_id, a.version) IN (
SELECT
premises_id,
version
FROM
premises_result_info_version
GROUP BY
premises_id,
version
HAVING
count(*) > 1
)
AND id NOT IN (
SELECT
min(id)
FROM
premises_result_info_version
GROUP BY
premises_id,
version
HAVING
count(*) > 1
);
3、MySQL Partition
表分区：大表拆分成小表，根据列属性按行分区；分而治之的思想
对比nginx负载均衡：
3.1 hash(field)
相同的输入得到相同的输出（适用于整型）
3.2 key(field)
同hash性质一样，只不过
key是处理字符串的，比hash()多了一步从字符串中计算出一个整型在做取模操作
3.3 rang(field)
分区算法：基于某一字段分区， 比如主键1- 100w, 100w - 200w；比如城市编码
3.4 List列表值
in (list值)
3.5 Composite
组合模式，其余集中情况的组合
=> 基于group 实现partition by 功能：
-- 查询同组年龄最大的用户
select
username
,SUBSTRING_INDEX( GROUP_CONCAT(age order by age desc) ,',',1) uuid
from
(
select uuid
,username
,`password`
,age
from `user`
)b
GROUP BY uuid asc;
-- 分析： group_concat :将列值聚合成一行 可排序 order by 、查重 distinct等
参考
mysql 优化之 is null ，is not null 索引使用测试
"构建思维能力，让烦恼变成真正有效的思考" 。