DB:实战 Force Index() 强制 MariaDB使用索引解决慢查询问题

版权声明:本文为博主原创文章,未经博主允许不得转载,除非给本座进贡糖葫芦~ https://blog.csdn.net/kimqcn4/article/details/62886167

测试环境: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.

展开阅读全文

没有更多推荐了,返回首页