MySQL 隔離數(shù)據(jù)列和前綴索引的使用總結(jié)

更新時間：2021年05月14日 09:47:40 作者：島上碼農(nóng)

正確地創(chuàng)建和使用索引對于查詢性能十分重要。由于存在很多種特殊場景的優(yōu)化和行為，因此有很多種方式去有效選擇和使用索引。因此，決定如何使用索引這一項技能是需要經(jīng)驗和時間的積累去培養(yǎng)的。以下會介紹一些如何有效使用索引的方法。

隔離數(shù)據(jù)列

通常，我們會發(fā)現(xiàn)查詢語句會妨礙MySQL使用索引。除非在查詢語句中列是獨立的，否則MySQL不會使用這些列的索引?！案綦x”的意思是索引列不應該成為表達式的一部分或者在一個查詢函數(shù)體中。例如下面的例子就不會命中actor_id這個索引。

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

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

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

前綴索引和索引的選擇性

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

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

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

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

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

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

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

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

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

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`

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

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

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

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

您可能感興趣的文章: