会写sql语句就可以了，为什么要了MySQL解索引的原理
2021-09-03 20:16:30

• 会写sql语句就可以了，为什么要了解MySQL索引的原理？
• curd我都会，为什么要了解undolog和redolog？
• 我又不需要修改MySQL源码，为什么要了解MySQL的锁？

酸奶爸爸曾经也思考过上述问题，MySQL常用功能我都会，为什么面试官要问索引原理、undolog等技术细节呢？直到后来我遇到下面问题。

酸奶爸爸负责的项目每天有1000w的pv，正常业务使用MySQL+Redis能够抗住正常的业务访问。访问日志只从请求头里打印了url和IP地址，但是每次调查线上问题读日志的时候都需要用户的地理位置信息，所以每次pv都需要记录IP+地理位置，以方便后续阅读日志文件。一个紧迫的需求就被提出来，通过IP地址快速获取地理位置。

IP地址→地理位置

网上有现成的IP段与地理位置的对应表，如果用MySQL这样实现

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实现，每次请求都要额外查询一次地理位置信息，系统访问量又比较大，这让MySQL本就不富裕的性能雪上加霜。能不能使用MySQL的索引原理自己实现IP地理位置的查询呢？

酸奶爸爸在百度上谷歌后发现了这个。

ip2region

ip2region 将ip与地理位置的数据写入二进制文件，并生成数据区域(data)、聚簇索引(index)与非聚簇索引(header index)。

• 把ip值通过ip2long 转为长整型
• 使用二分法在 HEADER INDEX 中搜索，比较得到对应的 header index block
• header index block 指向 INDEX 中的一个 4K 分区，所以直接把搜索范围降低到 4K
• 采用二分法在获取到的 4K 分区搜索，得到对应的 index block
• 拿到该 index block 的后面四个字节， 分别得到数据长度和数据地址
• 从数据地址读取拿到的所得长度的字节，即是搜索结果

数据库文件的结构和原理请阅读 @冬芽 的blog：“ip2region数据库文件的结构和原理”

其实像MySQL、Redis这些经典软件为我们提供了很多实用的思想，当我们遇到类似问题的时候可以借鉴以下这些大神是怎么做的。

• 当需要将大量数据写到不连续的空间的时候，想一想MySQL的redo log。

• 当有数据在同一时刻既存在读竞争也存在写竞争的时候，想一想MySQL的锁。

• 当有一大波数据中的一部分数据需要淘汰的时候，想一想Redis的LFU。

知识本身不值钱，使用知识的过程才值钱。