测试环境:Ubuntu14.04
DB:MariaDB10, 使用Innodb引擎
客户端:Navicat9
测试表:tb_ips_ui,数据量44w, 一个根据ip确定所在省市信息的表(用纯真IP数据库做的转换)。起止IP都转成了int保存。
DB开启慢查询(long_query_time = 1,方法参考:开启慢查询)日志后,发现不少这样的sql:
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 758334 Schema: db_scanner QC_hit: No
# Query_time: 1.454554 Lock_time: 0.000103 Rows_sent: 1 Rows_examined: 446181
# Rows_affected: 0
SET timestamp=1489721726;
Select addr,operator from tb_ips_ui where ip_start_ui <= 1038656883 and ip_end_ui >= 1038656883;
Sql查询时间达到:1.4秒,有点慢了;数据查询量446181,显然做全表查询了。
(用navicat单独做这条查询的时候速度是很快的,应用执行这个select却很慢。MariaDB使用innodb引擎做select应该是没有锁表问题的,具体原因后面再查!)
这个表是有索引的:
用Explain看看是否使用了索引:
没有用到索引,可能是我的sql 写的有问题,也可能MariaDB犯傻了。试试强制使用索引:
有效果了。type变成range,使用了index_ip_range索引,查询数rows降低不少,继续优化,使用sql_cache缓存:
要使用sql_cache,需要修改my.cnf:
这里query_cache_type=2,表示只有指定SQL_CACHE的SQL会缓存。
实际测试结果:慢查询日志已经不再显示这条sql.
后续: 查询数rows能否进一步降低?
=========================================================================
20170320 补充修正:
1. 因要查询的ip地址总是变的,所以用SQL_CACHE没意义,去掉。
2.SQL这么写,可进一步降低rows数到2。
20170322补充修正:
1. 实测,虽然rows降到2,但查询速度却很慢,个别达到0.5秒左右。修改SQL为:
sql = "Select addr,operator from tb_ips_ui where ip_start_ui <= " + str(ip_ui) + " order by ip_start_ui DESC limit 1"
只要给ip_start建立一个唯一索引就可以了,虽然rows数高,但查询速度很快。
End.