隔離數據列

通常，我們會發現查詢語句會妨礙MySQL使用索引。除非在查詢語句中列是獨立的，否則MySQL不會使用這些列的索引。“隔離”的意思是索引列不應該成為表達式的一部分或者在一個查詢函數體中。例如下面的例子就不會命中actor_id這個索引。

SELECT `actor_id` FROM `actor` WHERE `actor_id` + 1 = 2;

對于人來說，很容易知道查詢條件實際是actor_id = 4，但是MySQL不會這么處理，因此養成簡化WHERE判決條件的習慣，這意味著索引列獨立地在比較操作符的一側。下面是另外一個普遍錯誤的案例：

SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) = 10;

前綴索引和索引的選擇性

有時候需要在很長字符的列上建立索引，但這樣會導致索引占據的空間很大且查詢變慢。一個策略是使用哈希索引模擬，但有時候這未必是足夠好，這個時候該怎么做？

通常是可以將索引列前面的部分字符建立索引來替換全字段索引提高性能和節省空間。但這種方式會使得選擇性變差。索引的選擇性是指獨立的索引值篩選出的數據占整個數據集合的比例。高選擇性的索引可以讓MySQL過濾掉更多無關的數據。例如，一個唯一索引的選擇性是1。 列的前綴通常在選擇性方面已經能夠提供足夠好的性能。如果使用BLOB或TEXT或非常長的VARCHAR字段列，你必須定義前綴索引，以為MySQL不允許做全長度索引。

你需要在使用更長的前綴以獲得更好的選擇性和足夠短的前綴以節省存儲空間之間平衡。為了確定一個合適的前綴長度，查找出最高頻的值，然后和最頻繁的前綴進行比較。例如以城市數據表為例，我們可以使用如下的語句統計：

SELECT COUNT(*) as cnt, `name` FROM `common_city` GROUP BY `name` ORDER BY cnt DESC LIMIT 10

可以看到這些城市名稱出現的次數比較多。現在我們可以使用1個字的前綴查找最為頻繁的城市名稱前綴。

SELECT COUNT(*) as cnt, LEFT(`name`, 1) as pref FROM `common_city` GROUP BY pref ORDER BY cnt DESC LIMIT 10

可以看到1個字找出來的數據集更多了，這會導致獨立選中的機會越少，因此需要調整一下前綴的長度。例如調到3個字。

SELECT COUNT(*) as cnt, LEFT(`name`, 3) as pref FROM `common_city` GROUP BY pref ORDER BY cnt DESC LIMIT 10

可以看到這和全長度的相差不多，那實際三個字的前綴就夠了（原文使用的是英文城市數據表，字符會更多）。另外一種方式是使用不同長度的前綴數量與全字段數量的比例評估多少合適。例如：

SELECT 
  COUNT(DISTINCT LEFT(`name`, 1)) / COUNT(`name`) as pref1, 
  COUNT(DISTINCT LEFT(`name`, 2)) / COUNT(`name`) as pref2, 
  COUNT(DISTINCT LEFT(`name`, 3)) / COUNT(`name`) as pref3, 
  COUNT(DISTINCT LEFT(`name`, 4)) / COUNT(`name`) as pref4 
FROM `common_city`

數值越接近于1效果越好，但是也可以看到，隨著前綴長度的加長改善的空間越小。只看平均值并不是一個好主意，還需要檢查一下最壞情況。也許會覺得3-4個字足夠了，但是如果數據分布很不均勻，那可能會存在陷阱。因此還需要檢查一下前綴少的是不是存在一個前綴對應的數據與其他相比極其多的情況。最后可以給指定的列加前綴索引。

ALTER TABLE `common_city` ADD KEY (name(3));

前綴索引在節省空間和提高效率方面表現不錯，但是也有缺陷，那就是在ORDER BY和GROUP BY上無法使用索引（實際驗證在MySQL 5.7以上版本也有用）。另外一種常見的場景是在較長的十六進制字符串中，例如存儲的sessionId，取前8位前綴做索引將過濾很多無關數據，效果很好。

以上就是MySQL 隔離數據列和前綴索引的使用總結的詳細內容，更多關于MySQL 隔離數據列和前綴索引的資料請關注腳本之家其它相關文章！