[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只有一个聚簇索引，而不将所有索引都使用聚簇索引 因为聚簇索引是将索引和数据都存放在叶子节点中，如果所有的索引都用聚簇索引，则每一个索引都将保存一份数据，会造成数据的冗余，在数据量很大的情况下，这种数据冗余是很消耗资源的