锐客网

  1. 首页 > 睿知 > it技术 > >

mysql怎么优化数据库 mysql 数据优化( 六 )

IT技术数据库


我们加上hint给相同的查询，再次看看查询计划 。
这个时候用到了index_merge,union了三个列 。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍 。
mysql explainformat=json select /*+ index_merge(t1) */ * from t1where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {"query_block": {"select_id": 1,"cost_info": {"query_cost": "441.09"},"table": {"table_name": "t1","access_type": "index_merge","possible_keys": ["idx_rank1","idx_rank2","idx_rank3"],"key": "union(idx_rank1,idx_rank2,idx_rank3)","key_length": "5,5,5","rows_examined_per_scan": 1103,"rows_produced_per_join": 1103,"filtered": "100.00","cost_info": {"read_cost": "330.79","eval_cost": "110.30","prefix_cost": "441.09","data_read_per_join": "473K"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"],"attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))"}}}1 row in set, 1 warning (0.00 sec)
我们再看下SQL D的计划：
不加HINT，
mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {"query_block": {"select_id": 1,"cost_info": {"query_cost": "534.34"},"table": {"table_name": "t1","access_type": "ref","possible_keys": ["idx_rank1","idx_rank2","idx_rank3"],"key": "idx_rank1","used_key_parts": ["rank1"],"key_length": "5","ref": ["const"],"rows_examined_per_scan": 555,"rows_produced_per_join": 0,"filtered": "0.07","cost_info": {"read_cost": "478.84","eval_cost": "0.04","prefix_cost": "534.34","data_read_per_join": "176"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"],"attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))"}}}1 row in set, 1 warning (0.00 sec)
加了HINT，
mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {"query_block": {"select_id": 1,"cost_info": {"query_cost": "5.23"},"table": {"table_name": "t1","access_type": "index_merge","possible_keys": ["idx_rank1","idx_rank2","idx_rank3"],"key": "intersect(idx_rank1,idx_rank2,idx_rank3)","key_length": "5,5,5","rows_examined_per_scan": 1,"rows_produced_per_join": 1,"filtered": "100.00","cost_info": {"read_cost": "5.13","eval_cost": "0.10","prefix_cost": "5.23","data_read_per_join": "440"},"used_columns": ["id","rank1","rank2","log_time","prefix_uid","desc1","rank3"],"attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))"}}}1 row in set, 1 warning (0.00 sec)
对比下以上两个 ， 加了HINT的比不加HINT的cost小了100倍 。
总结下，就是说表的cardinality值影响这张的查询计划 ， 如果这个值没有正常更新的话，就需要手工加HINT了 。相信MySQL未来的版本会带来更多的HINT 。
MySQL数据库性能优化之分区分表分库分表是分散数据库压力的好方法 。
分表，最直白的意思 ， 就是将一个表结构分为多个表，然后，可以再同一个库里，也可以放到不同的库 。
当然 ， 首先要知道什么情况下，才需要分表 。个人觉得单表记录条数达到百万到千万级别时就要使用分表了 。
分表的分类
**1、纵向分表**
将本来可以在同一个表的内容，人为划分为多个表 。（所谓的本来，是指按照关系型数据库的第三范式要求，是应该在同一个表的 。）
分表理由：根据数据的活跃度进行分离，（因为不同活跃的数据，处理方式是不同的）
案例：
对于一个博客系统 ， 文章标题 ， 作者 ， 分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据 ， 我们把它叫做冷数据 。而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据 。所以，在进行数据库结构设计的时候 ， 就应该考虑分表 ， 首先是纵向分表的处理 。

推荐阅读


上一篇：服务器拒绝访问怎么办 服务器拒绝浏览请求怎么办

下一篇：电脑端看虎牙直播的网址，虎牙直播电脑版怎么看视频