Database — 八個 Index( 索引 ) 無法生效的 SQL 寫法

 Database — 八個 Index( 索引 ) 無法生效的 SQL 寫法


相信許多讀者一定知道 Index，也就是索引，對於資料庫的重要性，可以說一個資料庫執行效能的第一道關卡，就是將索引設定恰當。
不過相信很多朋友知道要使用索引後，執行的 SQL 語句如果包含被設定索引的欄位，則速度會變快。但可能不太清楚在什麼情況下，速度不會變快
因此，今天想跟各位朋友介紹，八個常見，但會導致 index 無法作用的 SQL 寫法，希望能幫助各位更恰當的編寫 SQL 語句:

1. 使用 NOT 、!=、not in、not like 等負向判斷詞
SELECT * FROM users WHERE name != 'John';

2. 使用函數語句包裝被索引的欄位
SELECT * FROM users WHERE DATE(created_at) = '2022-02-28';

這邊的範例使用 DATE() 函數，去轉換 created_at 這個欄位，因而導致無法透過原始資料，去觸發索引機制。
這邊要幫大家建構一個概念，索引會失效很重要的核心問題就是，『不要對欄位動手腳』，後續會用其他例子幫大家更釐清會有什麼情境。

3. 資料類型錯誤
SELECT * FROM users WHERE age = 30;

這邊假設 age 設定 varchar ，也就是字串型別，則 SQL 在判斷時，會『偷偷的』去做類別轉換，也就是對欄位動手腳，就會導致索引失效

4. 使用 『 %XXX% 』或是 『 %XXX 』
SELECT * FROM users WHERE name like '%john%';
失效的原因，是因為索引運算機制中，有部分會從字元最左開始匹配，如果用範例的搜尋模式，『 最左 』的字元會無法匹配，導致失效。

5. 使用 Null
SELECT * FROM users WHERE name IS NULL;
Null 值對於 Index 資料管理來說是比較迥異的存在，所以通常預設是不支援的，但在部份資料庫軟體中，可以另外設定讓 Null 被納入索引中

6. 錯誤的使用 or
SELECT * FROM users WHERE indexed_column > 5 or unindexed_column < 10;

上面的語句中， indexed_column 是有被加入索引的欄位，而 unindexed_column 是沒有被加入索引的欄位。如果 query 語句中，含有沒有被加入索引的欄位，則索引不會生效

7. 針對索引欄位做運算
SELECT * FROM users WHERE height - 10 > 150;
這邊可以再帶回我們上面強調的概念，對欄位動手腳的話，索引即不會生效，所以上面的例子中，對 height 做了運算，會導致索引無效。

8. 複合式 INDEX 使用順序錯誤
假設有三個欄位合起來的索引，name + height + weight
SELECT * FROM users WHERE name = 'john' and weight > 100;

上面語句的問題是，我們的索引是從 name 起頭，再來是 height，可是這個語句直接先接上了 weight，就會導致索引失效。
所以上面語句合法的索引組合有三：
name + height + weight
name + height
name

跳脫這三者的排序，則會讓索引失效喔

Reference https://medium.com/johnliu-%E7%9A%84%E8%BB%9F%E9%AB%94%E5%B7%A5%E7%A8%8B%E6%80%9D%E7%B6%AD/database-%E5%85%AB%E5%80%8B-index-%E7%B4%A2%E5%BC%95-%E7%84%A1%E6%B3%95%E7%94%9F%E6%95%88%E7%9A%84-sql-%E5%AF%AB%E6%B3%95-cdc7d2e72f51

留言

張貼留言

這個網誌中的熱門文章

Json概述以及python對json的相關操作

圖片
什麼是json： JSON(JavaScript Object Notation) 是一種輕量級的數據交換格式。易於人閱讀和編寫。同時也易於機器解析和生成。它基於JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999的一個子集。JSON採用完全獨立於語言的文本格式，但是也使用了類似於C語言家族的習慣（包括C, C++, C#, Java, JavaScript, Perl, Python等）。這些特性使JSON成為理想的數據交換語言。 JSON建構於兩種結構： “名稱/值”對的集合（A collection of name/value pairs）。不同的語言中，它被理解為對象（object），紀錄（record），結構（struct），字典（dictionary），哈希表（hash table），有鍵列表（keyed list），或者關聯數組（associative array）。 值的有序列表（An ordered list of values）。在大部分語言中，它被理解為數組（array）。 這些都是常見的數據結構。事實上大部分現代計算機語言都以某種形式支持它們。這使得一種數據格式在同樣基於這些結構的編程語言之間交換成為可能。 jso官方說 ​​明參見：http://json.org/ Python操作json的標準api庫參考：http://docs.python.org/library/json.html 對簡單數據類型的encoding 和decoding： 使用簡單的json.dumps方法對簡單數據類型進行編碼，例如： import json obj = [[ 1 , 2 , 3 ], 123 , 123.123 , 'abc' ,{ 'key1' :( 1 , 2 , 3 ), 'key2' :( 4 , 5 , 6 )}] encodedjson = json.dumps(obj) print repr (obj) print encodedjson 輸出： [[1, 2, 3], 123, 123.123, 'abc', {'ke...
閱讀完整內容

Docker容器日誌查看與清理

1. 問題 docker容器日誌導致主機磁盤空間滿了。 docker logs -f container_name 劈裡啪啦一大堆，很佔用空間，不用的日誌可以清理掉了。  2. 解決方法 2.1 找出Docker容器日誌 在linux上，容器日誌一般存放在 /var/lib/docker/containers/container_id/ 下面，查看各個日誌文件大小的腳本docker_log_size.sh，內容如下： #!/bin/sh echo "======== docker containers logs file size ========" logs=$(find /var/lib/docker/containers/ -name *-json.log) for log in $logs do ls -lh $log done # chmod +x docker_log_size.sh # ./docker_log_size.sh 2.2 清理Docker容器日誌（治標） 如果docker容器正在運行，那麼使用 rm -rf 方式刪除日誌後，通過 df -h 會發現磁盤空間並沒有釋放。 原因是在Linux或者Unix系統中，通過 rm -rf 或者文件管理器刪除文件，將會從文件系統的目錄結構上解除鏈接（unlink）。 如果文件是被打開的（有一個進程正在使用），那麼進程將仍然可以讀取該文件，磁盤空間也一直被佔用。 正確姿勢是 cat /dev/null > *-json.log ，當然你也可以通過 rm -rf 刪除後重啟docker。 接下來，提供一個日誌清理腳本 clean_docker_log.sh ，內容如下： #!/bin/sh echo "======== start clean docker containers logs ========" logs=$(find /var/lib/docker/containers/ -name *-json.log) for log in $logs d...
閱讀完整內容

利用 Keepalived 提供 VIP

Keepalived 是一種基於 VRRP 協定實現的高可靠 Web 服務方案，用於防止單點故障問題。因此一個 Web 服務運作至少會擁有兩台伺服器執行 Keepalived，一台作為 master，一台作為 backup，並提供一個虛擬 IP（VIP），master 會定期發送特定訊息給 backup 伺服器，當 backup 沒收到 master 訊息時，表示 master 已故障，這時候 backup 會接管 VIP，繼續提供服務，來確保服務的高可靠性。 VRRP VRRP（Virtual Router Redundancy Protocol，虛擬路由器備援協定），是一個提供備援路由器來解決單點故障問題的協定，該協定有兩個重要概念： * VRRP 路由器與虛擬路由器：VRRP 路由器是表示運作 VRRP 的路由器，是一個實體裝置，而虛擬路由器是指由 VRRP 建立的邏輯路由器。一組 VRRP 路由器協同運作，並一起構成一台虛擬路由器，該虛擬路由對外提供一個唯一固定的 IP 與 MAC 位址的邏輯路由器。 * 主控制路由器（master）與備援路由器（backup）：主要是在一組 VRRP 中的兩種互斥角色。一個 VRRP 群組中只能擁有一台是 master，但可以有多個 backup 路由器。 VRRP 協定使用選擇策略從路由器群組挑選一台作為 master 來負責 ARP 與轉送 IP 封包，群組中其他路由器則作為 backup 的角色處理等待狀態。當由於某種原因造成 master 故障時，backup 會在幾秒內成為 master 繼續提供服務，該階段不用改變任何 IP 與 MAC 位址。 Keepalived 節點配置 IP ADDRESS ROLE 172.16.1.101 vip 172.16.1.102 master 172.16.1.103 backup 安裝與設定 這 ubuntu 14.04 LTS Server 中已經內建了 Keepalived 可以透過 apt-get 來安裝： $ sudo apt-get install -y keepalived 也可以透過 source code 進行安裝，流程如下： $ sudo apt-get install build-essential libssl-dev $ wge...
閱讀完整內容