优化MySQL IP地址查询性能：300万条数据查询耗时4.5秒求助

Great question—4.5 seconds for a 3M-row IP lookup is definitely too slow, and there are several actionable fixes to get this down to milliseconds. Let's walk through the most effective ones:

• Add a Targeted B-Tree Index (The #1 Fix)
  The root cause here is almost certainly a missing or inefficient index, forcing MySQL to do a full table scan of 3M rows every time. For IP geolocation tables (where ranges are non-overlapping and ordered), the optimal index is on ip_from. This lets MySQL quickly find the largest ip_from value that's less than or equal to your converted IP integer—since each IP only fits into one range, this is exactly the row you need.

  Run this to create the index:

  CREATE INDEX idx_ip_from ON ip2location(ip_from);
  

  You can also optimize your query to leverage this index even better by sorting and limiting early, which reduces the work MySQL has to do:

  $n_ip = ip2long("106.87.84.64");
  $res = mysqli_query($link, "SELECT * FROM `ip2location` 
                              WHERE `ip_from` <= '$n_ip' 
                              ORDER BY `ip_from` DESC 
                              LIMIT 1");
  

  (Note: For valid IP geolocation data, the returned row's ip_to will always be >= $n_ip, so you can skip redundant checks unless your data has gaps.)

• Fix Column Data Types for IPv4
  Make sure ip_from and ip_to are using INT UNSIGNED instead of regular signed INT. IPv4 addresses converted with ip2long() can be as large as 4294967295, which exceeds the max value of a signed INT (2147483647). Using unsigned integers avoids overflow issues and makes indexes more efficient by eliminating unnecessary sign bits.

  Update the columns if needed:

  ALTER TABLE ip2location MODIFY COLUMN ip_from INT UNSIGNED NOT NULL;
  ALTER TABLE ip2location MODIFY COLUMN ip_to INT UNSIGNED NOT NULL;
  

• Cache Frequent Lookups
  Most IP queries are for repeat visitors or common IP ranges. Adding a caching layer will drastically reduce database load and speed up responses. Use something like Redis, Memcached, or PHP's APCu to store results for a day or more:

  $n_ip = ip2long("106.87.84.64");
  $cacheKey = "ip_geo_" . $n_ip;
  $geoData = apcu_fetch($cacheKey);
  
  if (!$geoData) {
      $res = mysqli_query($link, "SELECT * FROM `ip2location` WHERE `ip_from` <= '$n_ip' ORDER BY `ip_from` DESC LIMIT 1");
      $geoData = mysqli_fetch_assoc($res);
      apcu_store($cacheKey, $geoData, 86400); // Cache for 24 hours
  }
  

• Use a Covering Index (If You Don't Need All Columns)
  If you only need specific columns (e.g., country_code, city) instead of SELECT *, create a covering index that includes those columns. This lets MySQL retrieve all needed data directly from the index without accessing the main table, cutting down on disk I/O:

  CREATE INDEX idx_ip_from_geo ON ip2location(ip_from, country_code, city);
  

  Then adjust your query to select only those columns:

  $res = mysqli_query($link, "SELECT country_code, city FROM `ip2location` WHERE `ip_from` <= '$n_ip' ORDER BY `ip_from` DESC LIMIT 1");
  

• Tweak MySQL's Memory Configuration
  If you're using InnoDB (the default for modern MySQL), ensure the innodb_buffer_pool_size is set large enough to cache your entire ip2location table and its indexes. For a 3M-row table, the index alone is ~12MB, and the full table is likely under 200MB. Setting the buffer pool to 512MB or 1GB (depending on your server's RAM) will let MySQL keep most data in memory, eliminating slow disk reads.

  Edit your my.cnf/my.ini:

  innodb_buffer_pool_size = 1G
  

  Restart MySQL for changes to take effect.

• Consider Range Partitioning (For Very Large Datasets)
  If your table grows beyond 10M rows, range partitioning on ip_from can help. Split the table into partitions based on IP ranges (e.g., A/B class IP blocks), so MySQL only scans the partition that could contain your target IP:

  ALTER TABLE ip2location 
  PARTITION BY RANGE (ip_from) (
      PARTITION p0 VALUES LESS THAN (16777216), -- 0.0.0.0 to 15.255.255.255
      PARTITION p1 VALUES LESS THAN (33554432), -- 16.0.0.0 to 31.255.255.255
      PARTITION p_last VALUES LESS THAN MAXVALUE
  );
  

After implementing the first two fixes (index + data type), you should see query times drop to well under 10ms. Caching will make repeated lookups almost instantaneous.

内容的提问来源于stack exchange，提问作者c00000fd