mysql 大数据分页查询优化
应用场景;
当有一张表的数据非常大,需要使用到分页查询,分页查询在100w条后查询效率非常低;
解决方案:
1、业务层解决:只允许用户翻页一百页以内,十条一页;
2、使用where id > 5000000 limit 10; 要求数据完整性;但可以考虑每次查询时得知已查的最后一条id。
3、延迟查询:select table.id,table.name left join (select id from table limit 5000000,10) as tmp on table.id = tmp.id;id为索引;先通过索引查处500w条后的id,速度贼快。然后通过索引id来查询出对应的每一条数据;数独也贼快;但通过关联表在海量数据中查询会消耗一些时间;但比之前是提升了一倍多的数度;
开启慢查询日志:
在mysql中代码改配置:
开启慢查询日志:查询目前状态 show variables like '%query%';修改两个配置:set global slow_query_log = on; set session long_query_time = 2; slow_query_log_file配置缓存日志的路径
日志一条数据
# Time: 170520 1:01:04 # User@Host: root[root] @ localhost [] Id: 4405 # Query_time: 9.141422 Lock_time: 0.000117 Rows_sent: 1128 Rows_examined: 2007809 SET timestamp=1495213264; select * from jacky order by sid,score limit 1128;
查看一条语句执行效率
开启:set profiling = 1;
查询:show profiles;
查看某条详情:show profile for query num
variables中保存了所有mysql的系统配置; 可以通过show variables like "%xxx%';查询
show variables like '%query%'; #查看关于query的配置
show variables like 'version'; #查看mysql版本
explain 分析sql语句
explain select * from ......
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+ | 1 | SIMPLE | jacky | range | sid_type | sid_type | 4 | NULL | 208168 | Using index condition | +----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+
select_type : simple
tables : 表名
type : 这列比较重要;表示使用了哪种类型;有没有使用索引;从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys : 提示使用哪个索引会在该表中找到行,不太重要
keys : MYSQL使用的索引,简单且重要
key_len : MYSQL使用的索引长度
ref : ref列显示使用哪个列或常数与key一起从表中选择行。没明白
rows : 比较重要;表示该查询所遍历的行数;越小越好;
Extra : 该列包含MySQL解决查询的详细信息。一次条件查询中改参数为:User where
尽量注意:type、rows、keys、extra这几个参数;