江苏龙网

  1. 主页 > 生活百科 > >

Java架构-MYSQL大数据量下的操作与优化( 四 )

Java架构


如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引 。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号 。
SET%20p:=%200;UPDATE%20news%20SET%20page=CEIL((p:=%20p%20+%201)%20/%20$perpage)%20ORDER%20BY%20id%20DESC;当然，也可以新增一个专用于分页的表，可以用个后台程序来维护 。
UPDATE%20pagination%20TJOIN%20(%20SELECT%20id,%20CEIL((p:=%20p%20+%201)%20/%20$perpage)%20page%20FROM%20news%20ORDER%20BY%20id)CON%20C.id%20=%20T.idSET%20T.page%20=%20C.page;现在想获取任意一页的元素就很简单了：
SELECT%20*FROM%20news%20AJOIN%20pagination%20B%20ON%20A.id=B.IDWHERE%20page=$offset;还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时 。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右 。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方) 。
CREATE%20TEMPORARY%20TABLE%20_tmp%20(KEY%20SORT(random))SELECT%20id,%20FLOOR(RAND()%20*%200x8000000)%20randomFROM%20city;%20ALTER%20TABLE%20_tmp%20ADD%20OFFSET%20INT%20UNSIGNED%20PRIMARY%20KEY%20AUTO_INCREMENT,%20DROP%20INDEX%20SORT,%20ORDER%20BY%20random;接下来就可以向下面一样执行分页查询了 。
SELECT%20*FROM%20_tmpWHERE%20OFFSET%20>=%20$offsetORDER%20BY%20OFFSETLIMIT%20$perpage;简单来说，对于分页的优化就是 。。。避免数据量大时扫描过多的记录 。
软件开发中，常用要用到分页、计算总数，数据量超过千万、上亿的时候，往往count%20的需要超过%201s%20的执行时间，甚至%203-5s，对于一个追求性能的前沿团队来说，这个不能忍啊！
那么我们再从头分析以下为什么会慢？
mysql%20会对所有符合的条件做一次扫描 。
select%20count(*)%20from%20table_a%20where%20a%20=%20'%d'%20...如果%20a=%d%20的数据有%201000W%20条，那么数据库就会扫描一次%201000W%20条数据库 。如果不带查询条件，那这种全表扫描将更可怕 。
count(*)%20和%20count(1)、count(0)
count(expr)%20为统计%20expr%20不为空的记录
count(*)%20它会计算总行数，不管你字段是否有值都会列入计算范围 。
coount(0),count(1)%20没有差别，它会计算总行数
Example%201:
mysql>%20explain%20extended%20select%20count(*)%20from%20user;...1%20row%20in%20set,%201%20warning%20(0.34%20sec)%20mysql>%20show%20warnings;+-------+------+--------------------------------------------------+|%20Level%20|%20Code%20|%20Message%20|+-------+------+--------------------------------------------------+|%20Note%20|%201003%20|%20select%20count(0)%20AS%20`count(*)`%20from%20`user`%20|Example%202:
mysql>%20select%20count(*)%20from%20login_log%20->%20;+----------+|%20count(*)%20|+----------+|%202513%20|+----------+1%20rows%20in%20set%20(0.00%20sec)%20mysql>%20select%20count(logoutTime)%20from%20login_log;+-------------------+|%20count(logoutTime)%20|+-------------------+|%20308%20|+-------------------+1%20rows%20in%20set%20(0.00%20sec)怎么解决？
MyISAM%20DB
MyISAM%20引擎很容易获得总行数的统计，查询速度变得更快 。因为%20MyISAM%20存储引擎已经存储了表的总行数 。
MyISAM%20会为每张表维护一个%20row%20count%20的计数器，每次新增加一行，这个计数器就加%201 。但是如果有查询条件，那么%20MyISAM%20也%20game%20over%20了，MyISAM%20引擎不支持条件缓存 。
On%20MyISAM,%20doing%20a%20query%20that%20does%20SELECT%20COUNT(*)%20FROM%20{some_table},%20is%20very%20fast,%20since%20MyISAM%20keeps%20the%20information%20in%20the%20index其他%20DB%20引擎
受到%20MySIAM%20DB%20的启发，我们可以手动维护总数缓存在表的索引中了 。
1、如果%20ID%20连续，且基本不会断开 。直接取最大值%20ID
2、如果表中存在连续的数字列并设为索引，那么通过页码即可计算出此字段的范围，直接作范围查询即可：
start%20=%20(page-1)*pagesize+1%20end%20=%20page*pagesize%20select%20*%20from%20table%20where%20id%20>start%20and%20id%20<=end1、涉及到总数操作，专门维护一个总数 。新增一个用户，总数值加%201,%20需要总数的时候直接拿这个总数,%20比如分页时 。如果有多个条件，那么就需要维护多个总数列 。该方案的扩展性更好，随着用户表数量增大,%20水平切分用户表，要获取用户总数，直接查询这个总数表即可 。
分页正反偏移
数据库自带的%20skip%20和%20limit%20的限制条件为我们创建了分页的查询方式，但是如果利用不对，性能会出现千倍万倍差异 。


推荐阅读


上一篇：Docker 管理员操作使用说明

下一篇：一文让你搞懂令人脑瓜疼的python编码问题