快捷導(dǎo)航

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

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

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

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

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

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

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

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

前綴索引和索引的選擇性

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

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

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

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

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

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

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

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

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

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

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

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

您可能感興趣的文章: