Tuesday, May 17, 2016

programming development misc



http://www.cnblogs.com/baochuan/p/4625262.html
  有一张收藏表,里面存储的是用户和图书ID。数据量为1亿。现在要求分页获取所有用户ID(不重复),写下你的sql语句。
三种设计
 
  最容易想到的第一种分页语句是(这也是我们最容易想到的语句):
1
2
select distinct uid from 收藏表 order by uid desc limit 0, 10;
select distinct uid from 收藏表 order by uid desc limit 11, 10;
  再高级点语句,第二种($last_min_uid表示上一次读到的最后一个uid):
1
2
select distinct uid from 收藏表 order by uid desc limit 10;
select distinct uid from 收藏表 where uid < $last_min_uid order by uid desc limit 10;
  最高级的方式
1
2
select uid from 收藏表 group by uid order by uid desc limit 10;
select uid from 收藏表 group by uid having uid < $last_min_uid order by uid desc limit 10;
  以上三种方式都可以实现分页获取到用户ID列表,那么区别是什么?我现在就把每一种跟大家分析下。

  第一种在业务场景中,会出现丢数据的情况。——这是比较严重的情况,不予采纳。
  具体的业务场景是这样的:当你读取第5页的时候,前四页的用户id列表中,假如有一页的用户ID从库中删除掉,那么你这时读到的第5页(limit 51, 10),就是原来的第6页,你会把1页的用户ID丢失掉。

  第二种的第二条语句,通过explain分析,实际并没有命中唯一索引,而只是命中了一般索引,数据查询范围在7百万级别,故explain建议我们使用group by。——这个查询会有严重的性能问题
+----+--------------+---------------+-------+-------------------------------------------------------------+-------------+----------+-------+------------+------------------------------------------------------------------------+
| id   | select_type | table           | type  | possible_keys                                                      | key           | key_len | ref     | rows        | Extra                                                                               |
+----+--------------+---------------+-------+-------------------------------------------------------------+---------------------+---------+------+---------+---------------------------------------------------------------------+
| 1    | SIMPLE      | ubook_room | range | uid_book_id                                                       | uid_status  | 4          | NULL | 7066423  | Using where; Using index for group-by; Using temporary; Using filesort |
+----+--------------+---------------+-------+-------------------------------------------------------------+-------------+----------+-------+------------+------------------------------------------------------------------------+
  
  第三种explain分析,数据查询范围在12万级别(跟第二种相差一个数量级),查询性能高
+----+---------------+------------+-------+-----------------+-----------------+---------+----------+----------+-------------+
| id   | select_type  | table       | type  | possible_keys  | key               | key_len | ref       | rows      | Extra         |
+----+---------------+------------+-------+-----------------+-----------------+---------+----------+----------+-------------+
| 1    | SIMPLE       | 收藏表      | index | NULL             | uid_book_id   | 12         | NULL  | 121719   | Using index | 

Labels

Review (572) System Design (334) System Design - Review (198) Java (189) Coding (75) Interview-System Design (65) Interview (63) Book Notes (59) Coding - Review (59) to-do (45) Linux (43) Knowledge (39) Interview-Java (35) Knowledge - Review (32) Database (31) Design Patterns (31) Big Data (29) Product Architecture (28) MultiThread (27) Soft Skills (27) Concurrency (26) Cracking Code Interview (26) Miscs (25) Distributed (24) OOD Design (24) Google (23) Career (22) Interview - Review (21) Java - Code (21) Operating System (21) Interview Q&A (20) System Design - Practice (20) Tips (19) Algorithm (17) Company - Facebook (17) Security (17) How to Ace Interview (16) Brain Teaser (14) Linux - Shell (14) Redis (14) Testing (14) Tools (14) Code Quality (13) Search (13) Spark (13) Spring (13) Company - LinkedIn (12) How to (12) Interview-Database (12) Interview-Operating System (12) Solr (12) Architecture Principles (11) Resource (10) Amazon (9) Cache (9) Git (9) Interview - MultiThread (9) Scalability (9) Trouble Shooting (9) Web Dev (9) Architecture Model (8) Better Programmer (8) Cassandra (8) Company - Uber (8) Java67 (8) Math (8) OO Design principles (8) SOLID (8) Design (7) Interview Corner (7) JVM (7) Java Basics (7) Kafka (7) Mac (7) Machine Learning (7) NoSQL (7) C++ (6) Chrome (6) File System (6) Highscalability (6) How to Better (6) Network (6) Restful (6) CareerCup (5) Code Review (5) Hash (5) How to Interview (5) JDK Source Code (5) JavaScript (5) Leetcode (5) Must Known (5) Python (5)

Popular Posts