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

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

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

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

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

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

前綴索引和索引的選擇性

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

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

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

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

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

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

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

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

可以看到這和全長度的相差不多，那實(shí)際三個(gè)字的前綴就夠了（原文使用的是英文城市數(shù)據(jù)表，字符會(huì)更多）。另外一種方式是使用不同長度的前綴數(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效果越好，但是也可以看到，隨著前綴長度的加長改善的空間越小。只看平均值并不是一個(gè)好主意，還需要檢查一下最壞情況。也許會(huì)覺得3-4個(gè)字足夠了，但是如果數(shù)據(jù)分布很不均勻，那可能會(huì)存在陷阱。因此還需要檢查一下前綴少的是不是存在一個(gè)前綴對應(yīng)的數(shù)據(jù)與其他相比極其多的情況。最后可以給指定的列加前綴索引。

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

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

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