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

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

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

酸奶爸爸負(fù)責(zé)的項(xiàng)目每天有1000w的pv，正常業(yè)務(wù)使用MySQL+Redis能夠抗住正常的業(yè)務(wù)訪問。訪問日志只從請(qǐng)求頭里打印了url和IP地址，但是每次調(diào)查線上問題讀日志的時(shí)候都需要用戶的地理位置信息，所以每次pv都需要記錄IP+地理位置，以方便后續(xù)閱讀日志文件。一個(gè)緊迫的需求就被提出來，通過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)求都要額外查詢一次地理位置信息，系統(tǒng)訪問量又比較大，這讓MySQL本就不富裕的性能雪上加霜。能不能使用MySQL的索引原理自己實(shí)現(xiàn)IP地理位置的查詢呢？

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

ip2region

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

• 把ip值通過ip2long 轉(zhuǎn)為長整型
• 使用二分法在 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ù)長度和數(shù)據(jù)地址
• 從數(shù)據(jù)地址讀取拿到的所得長度的字節(jié)，即是搜索結(jié)果

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

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

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

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

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

知識(shí)本身不值錢，使用知識(shí)的過程才值錢。