mysql前綴索引
應用場景:
數據庫裡有個地址(address)字段,類型為varchar(100),業務決定了要經常根據
address來進行查詢。 www.2cto.com
確定選擇性:
Sql代碼
SELECT count(DISTINCT(address))/count(*) AS Selectivity FROM info;
+-------------+
| Selectivity |
+-------------+
| 0.8745 |
+-------------+
<address>選擇性很好,但是長度為100,對整個字段建立索引顯然不合適,可以考慮建立
前綴索引,例如<left(address,5)>,看看其選擇性: www.2cto.com
Sql代碼
SELECT count(DISTINCT(left(address,5)))/count(*) AS Selectivity FROM info;
+-------------+
| Selectivity |
+-------------+
| 0.5981 |
+-------------+
選擇性還不錯,但和0.8745相比還是太低,所以我們可以將前綴長度增加到10,再看看
選擇性: www.2cto.com
Sql代碼
SELECT count(DISTINCT(left(address,10)))/count(*) AS Selectivity FROM info;
+-------------+
| Selectivity |
+-------------+
| 0.8239 |
+-------------+
和0.8745已經很接近了,但是索引長度只有10,所以就可以決定建立前綴索引了。
前綴索引兼顧索引大小和查詢速度,但是其缺點是不能用於ORDER BY和GROUP BY
操作,也不能用於Covering index(即當索引本身包含查詢所需全部數據時,不再訪問
數據文件本身)。