會(huì)寫(xiě)sql語(yǔ)句就可以了，為什么要了MySQL解索引的原理
2021-09-03 20:16:30

• 會(huì)寫(xiě)sql語(yǔ)句就可以了，為什么要了解MySQL索引的原理？
• curd我都會(huì)，為什么要了解undolog和redolog？
• 我又不需要修改MySQL源碼，為什么要了解MySQL的鎖？

酸奶爸爸曾經(jīng)也思考過(guò)上述問(wèn)題，MySQL常用功能我都會(huì)，為什么面試官要問(wèn)索引原理、undolog等技術(shù)細(xì)節(jié)呢？直到后來(lái)我遇到下面問(wèn)題。

酸奶爸爸負(fù)責(zé)的項(xiàng)目每天有1000w的pv，正常業(yè)務(wù)使用MySQL+Redis能夠抗住正常的業(yè)務(wù)訪問(wèn)。訪問(wèn)日志只從請(qǐng)求頭里打印了url和IP地址，但是每次調(diào)查線上問(wèn)題讀日志的時(shí)候都需要用戶(hù)的地理位置信息，所以每次pv都需要記錄IP+地理位置，以方便后續(xù)閱讀日志文件。一個(gè)緊迫的需求就被提出來(lái)，通過(guò)IP地址快速獲取地理位置。

IP地址→地理位置

網(wǎng)上有現(xiàn)成的IP段與地理位置的對(duì)應(yīng)表，如果用MySQL這樣實(shí)現(xiàn)

create table ip_addr_table
(
    ip_begin int          not null,
    ip_end   int          not null,
    addr     varchar(100) null
);

create index ip_addr_table_ip_begin_index
    on ip_addr_table (ip_begin);

create index ip_addr_table_ip_end_index
    on ip_addr_table (ip_end);
    
select addr from ip_addr_table where ip_begin<intip and ip_end>intip limit 1;

但是用MySQL實(shí)現(xiàn)，每次請(qǐng)求都要額外查詢(xún)一次地理位置信息，系統(tǒng)訪問(wèn)量又比較大，這讓MySQL本就不富裕的性能雪上加霜。能不能使用MySQL的索引原理自己實(shí)現(xiàn)IP地理位置的查詢(xún)呢？

酸奶爸爸在百度上谷歌后發(fā)現(xiàn)了這個(gè)。

ip2region

ip2region 將ip與地理位置的數(shù)據(jù)寫(xiě)入二進(jìn)制文件，并生成數(shù)據(jù)區(qū)域(data)、聚簇索引(index)與非聚簇索引(header index)。

• 把ip值通過(guò)ip2long 轉(zhuǎn)為長(zhǎng)整型
• 使用二分法在 HEADER INDEX 中搜索，比較得到對(duì)應(yīng)的 header index block
• header index block 指向 INDEX 中的一個(gè) 4K 分區(qū)，所以直接把搜索范圍降低到 4K
• 采用二分法在獲取到的 4K 分區(qū)搜索，得到對(duì)應(yīng)的 index block
• 拿到該 index block 的后面四個(gè)字節(jié)， 分別得到數(shù)據(jù)長(zhǎng)度和數(shù)據(jù)地址
• 從數(shù)據(jù)地址讀取拿到的所得長(zhǎng)度的字節(jié)，即是搜索結(jié)果

數(shù)據(jù)庫(kù)文件的結(jié)構(gòu)和原理請(qǐng)閱讀 @冬芽 的blog：“ip2region數(shù)據(jù)庫(kù)文件的結(jié)構(gòu)和原理”

其實(shí)像MySQL、Redis這些經(jīng)典軟件為我們提供了很多實(shí)用的思想，當(dāng)我們遇到類(lèi)似問(wèn)題的時(shí)候可以借鑒以下這些大神是怎么做的。

• 當(dāng)需要將大量數(shù)據(jù)寫(xiě)到不連續(xù)的空間的時(shí)候，想一想MySQL的redo log。

• 當(dāng)有數(shù)據(jù)在同一時(shí)刻既存在讀競(jìng)爭(zhēng)也存在寫(xiě)競(jìng)爭(zhēng)的時(shí)候，想一想MySQL的鎖。

• 當(dāng)有一大波數(shù)據(jù)中的一部分?jǐn)?shù)據(jù)需要淘汰的時(shí)候，想一想Redis的LFU。

知識(shí)本身不值錢(qián)，使用知識(shí)的過(guò)程才值錢(qián)。