性能优化
最后更新于：2022-04-02 03:56:34
[TOC]
## 尽量去掉 in 与 or
```
select count(*) from stuff where id_no in('0','1')
//可以考虑将or子句分开：
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
```
## 把 in 转为 join
bad
```
SELECT
*
FROM
`im_msg_201907`
WHERE
msgstatus = 0
AND ( send_id = '6521' AND gid IN ( SELECT msgid FROM im_msg_rece_201907 WHERE RECEIVEID = '7415' ) )
OR ( send_id = '7415' AND gid IN ( SELECT msgid FROM im_msg_rece_201907 WHERE RECEIVEID = '6521' ) )
ORDER BY
send_date DESC
```
good
```
SELECT
im_msg_202103.*,
im_msg_rece_202103.RECEIVEID
FROM
`im_msg_202103`
LEFT JOIN im_msg_rece_202103 ON im_msg_rece_202103.MSGID = im_msg_202103.GID
WHERE
im_msg_202103.msgstatus = 0
AND ( im_msg_202103.send_id = '102' AND im_msg_rece_202103.RECEIVEID = '103' )
OR ( im_msg_202103.send_id = '103' AND im_msg_rece_202103.RECEIVEID = '102' )
ORDER BY
im_msg_202103.send_date DESC
```
## 用 not in 转为 left join 配合 is null
> thinkpphp 中 is null 表示为 `$where\['m\_order\_no'\] = array('exp','is null');`
## 优化**大分页**的查询(方式一)
bad
```
SELECT * FROM `year_score`
where `year` = 2017
ORDER BY id
limit (N - 1) * 20, 20;
```
good
```
# XXXX 代表已知的数据
SELECT * FROM `year_score`
where `year` = 2017 and id > XXXX
ORDER BY id limit 20;
```
## 优化**大分页**的查询(方式二)
当几百万的查询时候,优化效果明显
```
mysql > select id, name from test
where _timestamp >="2020-03-21 00: 00: 00" and
_timestamp <=2020-03-22 00: 00: 00" limit 0, 10;
(毫秒级)
mysql > select id, name from test
where _timestamp >="2020-03-21 00: 00: 00"
and _timestamp <=2020-03-22 00: 00: 00"
limit 1450000, 10;
(10.07s)
```
优化思路先把符合条件的主键找到，然后通过主键去定位符合条件的数据
1.通过索引覆盖避免了回表;2.通过主键直接定位数据的方法，省去了在数据集中查询初始位置的过程
1. 方案一:延迟关联法
```
SELECT
a.id,
a.NAME
FROM
test a,
( SELECT id FROM test
WHERE _timestamp >= "2020-03-21 00: 00: 00"
AND _timestamp <= "2020-03-22 00: 00: 00"
ORDER BY id LIMIT 1450000, 10
) b
WHERE
a.id = b = id :
```
2. 方案二: 键阈值法啊
```
SELECT
id,
NAME
FROM
test
WHERE
id >= (
SELECT id FROM test
WHERE _timestamp >= "2020-03-21 00: 00: 00"
AND _timestamp >= "2020-03-22 00: 00: 00"
LIMIT 1450000, 1
)
LIMIT 10
```
## hash(索引) 与 树(tree)
- 在单条查询记录时.hash 比 tree 快
- 排序查询(`group by` , `order by`, `< >`) tree比hash快
- InnoDB并不支持哈希索引
## 为什么InnoDB只有一个聚簇索引，而不将所有索引都使用聚簇索引
因为聚簇索引是将索引和数据都存放在叶子节点中，如果所有的索引都用聚簇索引，则每一个索引都将保存一份数据，会造成数据的冗余，在数据量很大的情况下，这种数据冗余是很消耗资源的
';