举例详解 MySQL/MariaDB 数据库优化
看到有人说数据库的性能不好，占用太多资源，同时也看到有人回答，增加数据库缓存就好了。
当然这么说也不是完全不对，但大多数时候，增加缓存并不是解决问题的有效方法，只是一种治标不治本的方法。优化数据库最根本的方法是要优化数据库的 TABLE INDEX 和 QUERY 。如果不能对 TABLE 的 INDEX 和查询 QUERY 做优化，增加缓存并不会有太大帮助。
如果数据库的数据量不大，几千或是几万条数据，可能优化不优化并没有太多的感觉，但当数据库的数据达到了百万数量级，那么优化后的效果是明显不同的。
这里用个实例说明如何对 TABLE 的 INDEX 和查询 QUERY 做优化，就以我上一篇博客做为例子。
让你拥有一个自己的 IP 归属地查询数据库
https://techotok.com/get-your-own-ip-location-search-database/
先说说优化结果，优化前，搜索一个 IP 平均需要3秒左右，优化后，搜索一个 IP 平均不到1/100秒。也就是说，如果一分钟有100个 IP 搜索，优化前需要300秒，也就是5分钟（高资源占用，部分搜索用户都在等待中），优化后就算以百分之一秒来算，总共也只需要1秒，这是你加多少缓存也带不来的效果。
其实在论坛里问数据库的优化是没有用的，数据库的优化都是根据数据库里的数据结构和查询条件来精细优化的，没有什么一两句话就能告诉你如何优化数据库的。
下面具体讨论一下优化过程
那个贴子里提到了 IP 归属地的数据库，这个数据库里有将近330万数据，里面的数据大概像下面这个样子。
+----+----------------+--------------+--------------+------------+--------------------+
| id | ip_range_start | ip_range_end | country_code | city | state2 |
+----+----------------+--------------+--------------+------------+--------------------+
| 1 | 1.0.0.0 | 1.0.0.255 | AU | Queensland | South Brisbane |
| 2 | 1.0.1.0 | 1.0.3.255 | CN | Fujian | Wenquan |
| 3 | 1.0.4.0 | 1.0.7.255 | AU | Victoria | Narre Warren |
| 4 | 1.0.8.0 | 1.0.15.255 | CN | Guangdong | Guangzhou |
| 5 | 1.0.16.0 | 1.0.16.255 | JP | Tokyo | Chiyoda |
| 6 | 1.0.17.0 | 1.0.31.255 | JP | Tokyo | Shinjuku (1-chome) |
| 7 | 1.0.32.0 | 1.0.63.255 | CN | Guangdong | Guangzhou |
| 8 | 1.0.64.0 | 1.0.82.255 | JP | Hiroshima | Hiroshima |
| 9 | 1.0.83.0 | 1.0.83.255 | JP | Tottori | Kurayoshi |
| 10 | 1.0.84.0 | 1.0.85.255 | JP | Hiroshima | Hiroshima |
+----+----------------+--------------+--------------+------------+--------------------+
首先我们先查询一下下面两个 IP 的归属地
8.8.8.8 这个查询用了0.179秒
208.33.109.191 这个查询用了4.508秒
为什么两个查询会相差这么大呢？因为这个 TABLE 没有进行优化，所以查询时会做 FULL TABLE SCAN，也就是在 TABLE 里从头到尾搜寻，在前面的数据自然很快能找到，越靠后面的数据当然就要花越多的时间才能找到。
SELECT id,ip_range_start,ip_range_end, country_code,city,state2 FROM dbipcity WHERE INET_ATON('8.8.8.8') BETWEE
N INET_ATON(ip_range_start) AND INET_ATON(ip_range_end) limit 1;
+-------+----------------+--------------+--------------+------------+---------------+
| id | ip_range_start | ip_range_end | country_code | city | state2 |
+-------+----------------+--------------+--------------+------------+---------------+
| 78748 | 8.8.8.0 | 8.8.8.255 | US | California | Mountain View |
+-------+----------------+--------------+--------------+------------+---------------+
1 row in set (0.179 sec)
SELECT id,ip_range_start,ip_range_end, country_code,city,state2 FROM dbipcity WHERE INET_ATON('208.33.109.0') BETWEEN INET_ATON(ip_range_start) AND INET_ATON(ip_range_end) limit 1;
+---------+----------------+----------------+--------------+----------------+------------+
| id | ip_range_start | ip_range_end | country_code | city | state2 |
+---------+----------------+----------------+--------------+----------------+------------+
| 3000000 | 208.33.109.0 | 208.33.109.191 | US | North Carolina | Mount Airy |
+---------+----------------+----------------+--------------+----------------+------------+
1 row in set (4.508 sec)
为了能让搜索加速，就要给这个 TABLE 加上 INDEX 索引。但是 IP 地址表示都是用 x.x.x.x 的形式储存，加上 INDEX 并不会有太大的帮助，这时就要把 IP 换成整数形式，再加上 INDEX。
在 TABLE 里再加两个整数 COLUMN，用 INET_ATON 把 IP 值转换成整数，之后 TABLE 就大概变成了下面这样。
+----+----------------+--------------+---------------+-------------+--------------+------------+--------------------+
| id | ip_range_start | ip_range_end | ip_start_int | ip_end_int | country_code | city | state2 |
+----+----------------+--------------+---------------+-------------+--------------+------------+--------------------+
| 1 | 1.0.0.0 | 1.0.0.255 | 16777216 | 16777471 | AU | Queensland | South Brisbane |
| 2 | 1.0.1.0 | 1.0.3.255 | 16777472 | 16778239 | CN | Fujian | Wenquan |
| 3 | 1.0.4.0 | 1.0.7.255 | 16778240 | 16779263 | AU | Victoria | Narre Warren |
| 4 | 1.0.8.0 | 1.0.15.255 | 16779264 | 16781311 | CN | Guangdong | Guangzhou |
| 5 | 1.0.16.0 | 1.0.16.255 | 16781312 | 16781567 | JP | Tokyo | Chiyoda |
| 6 | 1.0.17.0 | 1.0.31.255 | 16781568 | 16785407 | JP | Tokyo | Shinjuku (1-chome) |
| 7 | 1.0.32.0 | 1.0.63.255 | 16785408 | 16793599 | CN | Guangdong | Guangzhou |
| 8 | 1.0.64.0 | 1.0.82.255 | 16793600 | 16798463 | JP | Hiroshima | Hiroshima |
| 9 | 1.0.83.0 | 1.0.83.255 | 16798464 | 16798719 | JP | Tottori | Kurayoshi |
| 10 | 1.0.84.0 | 1.0.85.255 | 16798720 | 16799231 | JP | Hiroshima | Hiroshima |
+----+----------------+--------------+---------------+-------------+--------------+------------+--------------------+
下一步在两个整数的 COLUMN 创建 INDEX
CREATE INDEX idx_ip_start ON IP_TABLE (ip_start_int);
CREATE INDEX idx_ip_end ON IP_TABLE (ip_end_int);
然后把 QUERY 修改一下再运行，你会发现运行时间并没有加快，这是因为 COLUMN 如果在条件 WHERE 里的 BETWEEN AND 之间，是不进行 INDEX 搜索的，还是原始的 FULL TABLE SCAN。
SELECT id,ip_range_start,ip_range_end, country_code,city,state2 FROM dbipcity WHERE INET_ATON('8.8.8.8') BETWEEN ip_start_int AND ip_start_int limit 1;
在 QUERY 里，COLUMN 要出现在运算符的左边才会用到 INDEX 搜索，所以还要对 QUERY 的语句进行修改重写。这里为了优化 QUERY，还用了一个 SUB-QUERY。
优化之后，不再是 FULL TABLE SCAN，而是用 INDEX SCAN，不管数据在 TABLE 的什么位置，所用的查询时间差不多。
8.8.8.8 这个查询只用的千分之三秒
208.33.109.191 这个查询只用了千分之四秒
SELECT id,ip_range_start,ip_range_end,ip_start_int,ip_end_int, country_code,city,state2 FROM ( SELECT * FROM dbipcity WHERE ip_end_int >= INET_ATON('8.8.8.8') LIMIT 1 ) AS candidate WHERE ip_start_int <= INET_ATON('8.8.8.8');
+-------+----------------+--------------+--------------+------------+--------------+------------+---------------+
| id | ip_range_start | ip_range_end | ip_start_int | ip_end_int | country_code | city | state2 |
+-------+----------------+--------------+--------------+------------+--------------+------------+---------------+
| 78748 | 8.8.8.0 | 8.8.8.255 | 134744064 | 134744319 | US | California | Mountain View |
+-------+----------------+--------------+--------------+------------+--------------+------------+---------------+
1 row in set (0.003 sec)
SELECT id,ip_range_start,ip_range_end,ip_start_int,ip_end_int, country_code,city,state2 FROM ( SELECT * FROM dbipcity WHERE ip_end_int >= INET_ATON('208.33.109.0') LIMIT 1 ) AS candidate WHERE ip_start_int <= INET_ATON('208.33.109.0');
+---------+----------------+----------------+--------------+------------+--------------+----------------+------------+
| id | ip_range_start | ip_range_end | ip_start_int | ip_end_int | country_code | city | state2 |
+---------+----------------+----------------+--------------+------------+--------------+----------------+------------+
| 3000000 | 208.33.109.0 | 208.33.109.191 | 3491851520 | 3491851711 | US | North Carolina | Mount Airy |
+---------+----------------+----------------+--------------+------------+--------------+----------------+------------+
1 row in set (0.004 sec)
从上面的例子可以看出优化的效果是很明显的，优化后可以用更少的资源来运行更多的数据查询。