HackMD
SQL
先條件後排序
用結果去排序
sql index差異
https://oldmo860617.medium.com/以-postgressql-為例了解資料庫的-query-plans-abd8b5f54c66
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
postgresql分析工具 要錢
pganalyze
query plain
https://oldmo860617.medium.com/以-postgressql-為例了解資料庫的-query-plans-abd8b5f54c66
explain
https://pevenc12.github.io/posts/postgresql-explain-explained-scan/
https://pevenc12.github.io/posts/postgresql-explain-explained-join/
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
排序
https://airbyte.com/blog/postgresql-query-plans-for-sorting-data
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
where結果很大 且要排序
當 WHERE 條件的結果集很大，而 ORDER BY 的列無法直接用索引排序時，PostgreSQL 可能執行全表掃描或排序，導致性能問題，尤其是「Rows Removed by Filter」很高。以下是解決方法
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
order by + limit
小心選錯index
https://pganalyze.com/blog/5mins-postgres-planner-order-by-limit
縮小排除法原則
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
join index
排序
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
like
postgresql
前綴
CREATE INDEX idx_name_booking_like
ON postgres_air.booking (email varchar_pattern_ops);
varchar_pattern_ops
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
排序
https://www.cnblogs.com/thomasbc/p/15572368.html
刪除大量會有問題
https://www.cnblogs.com/wjq310/p/14216684.html
事務id 大寫寫入
https://blog.sentry.io/transaction-id-wraparound-in-postgres/
注意
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
索引整理
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
@>
https://stackoverflow.com/questions/36985926/what-does-the-operator-in-postgres-do
in 排序
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
in, where加上order by field
只查詢月份
EXTRACT(MONTH FROM birthday)
lock查詢
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM
pg_catalog.pg_locks blocked_locks
JOIN
pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN
pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN
pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE
blocked_locks.granted = false;
正規
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-TABLE
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
view
除了一班的
可以選擇建立條件
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
window
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
WINDOW function 也可以拿來做移動平均值或累計值的計算
- The image was uploaded to a note which you don't have access to
- The note which the image was originally uploaded to has been deleted
中位數
游標
行鎖
union
去重跟沒去重複
sql 阿里雲
https://www.alibabacloud.com/help/zh/log-service/latest/unnest-clause
index(重點)
https://ithelp.ithome.com.tw/articles/10230211
覆蓋索引
https://ithelp.ithome.com.tw/articles/10230245
複合索引
https://ithelp.ithome.com.tw/articles/10230278
ICP
https://ithelp.ithome.com.tw/articles/10230362
coalesce
類似is_null的
Explain
排序要小心
orderBy 如果同一區間會隨機
https://www.jianshu.com/p/9d5becb03577
排序優化
https://cloud.tencent.com/developer/article/1502417?from=article.detail.1505335
優化
https://cloud.tencent.com/developer/article/1505335?from=article.detail.1868851
exists vs in
外大內小 用in
外小內大 用 exists
一樣用 join
https://cloud.tencent.com/developer/article/1868851
臨時表
https://segmentfault.com/q/1010000013095609
子查詢優化
https://blog.csdn.net/dxyzhbb/article/details/106803069
sql count
https://bbs.huaweicloud.com/blogs/323789
GROUP_CONCAT
https://ithelp.ithome.com.tw/articles/10132553
function
https://zhuanlan.zhihu.com/p/101891364
data function
https://zhuanlan.zhihu.com/p/105375801
where 1=1
https://zhuanlan.zhihu.com/p/282817046
where or on
防 sql 注入
1. 过滤特殊字符
2. 过滤数据库关键字
3. 验证数据类型及格式
4. 使用预编译模式，绑定变量
約束
check
https://stackoverflow.com/questions/38223158/laravel-migration-adding-check-constraints-in-table
如果現有資料違反會有error
運算子
day name
DAYNAME 可以顯示星期幾之類的很多可以用
要轉中文要 set @@ 改變語言那樣
涵式
FIND_IN_SET 找array自動對應key
https://blog.johnsonlu.org/mysql-find-in-set/
mysql如何查询每个用户消费金额最大的记录
https://learnku.com/mysql/t/63401
https://learnku.com/laravel/t/59320
最新的唯一数据
https://learnku.com/mysql/t/68543t
mid
可以用month或字串去擷取
按照姓氏 排序 left + field
左邊第一個字然後按照filed排
按照順序由1開始
如果不再後面的就是0
排序按照大到小
自連結
https://www.bilibili.com/video/BV1ht411J7ik?p=7&spm_id_from=pageDriver
表
sql
找某某同一個甚麼
一開始有先限定了
記得要排除原本的
例子二
找後盾人 同一年出生的
一樣一開始有先限定誰
min max
查詢最好用子查詢
如果直接select min(a) 會只有一筆
FIND_IN_SET
https://blog.johnsonlu.org/mysql-find-in-set/
if
if 可以讓else不更新
等於欄位名稱就好
Distinct
https://ithelp.ithome.com.tw/articles/10259378
即使加了括號，還是會作用到兩個欄位
SELECT (DISTINCT supplier_name), order_status FROM orders;
那有沒有其他方式可以針對一個欄位去重，但同時還是可以單純顯示其他欄位？這時候可以用 GROUP BY，或說一開始的情境用GROUP BY也可以！
SELECT `supplier_name`, `order_status` FROM orders
WHERE `order_status` = 2
GROUP BY `supplier_name`;
如何讓 query 區分大小寫
解法：Binary
在要區分大小寫的條件欄位前加上 BINARY 就可以解決
SELECT * FROM records WHERE BINARY code = 'F23B';
執行結果如下，因為沒有完全匹配的紀錄，所以撈出 0 筆資料
mysql> select * from users where binary code = 'F23B';
Empty set (0.00 sec)
在 Laravel 中就會是這樣下：
$code = "F23B";
$records = Record::whereRaw('BINARY `code` = ?', $code)->get();
子查詢
每一個子查詢都是一個Select指令，必須用小括弧包起來，能夠針對不同資料表進行查詢。
如果SQL查詢指令內有子查詢，首先處理的是子查詢條件，然後再依子查詢取得的條件值來處理主查詢，然後就去得最後的查詢結果。
這邊注意 是先跑主表 select … 然後where的時候才先執行括號內
- 子查詢要在括號()中。
- 通常子查詢SELECT只會取得單一欄位的值，以便主查詢的欄位進行比較運算。
- 如需排序，子查詢不能使用ORDER BY，只能使用GROUP BY 子句。
- 如果子查詢可以取得多筆資料，在主查詢需使用IN邏輯運算子。
多列多行
多行
表子查詢
function
https://learnku.com/articles/68574
