slq 優化 · PHP/Python/前端/Linux 等等學習筆記

[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只有一個聚簇索引，而不將所有索引都使用聚簇索引因為聚簇索引是將索引和數據都存放在葉子節點中，如果所有的索引都用聚簇索引，則每一個索引都將保存一份數據，會造成數據的冗余，在數據量很大的情況下，這種數據冗余是很消耗資源的