记开发个人图书收藏清单小程序开发(三)DB设计

图片 4

主要是参考豆瓣的图书查询接口:

昨晚完成了Web端新增图书信息的功能,现在就差DB的具体实现了。

摘要

因为我把Book相关的信息拆分的比较多,所以更新有点小麻烦。

  1. MongoDB 适用场景简介
  2. Mongodb 性能监控与分析
  3. Mongodb 性能优化建议

返回内容如下:

首先,我需要创建一个Book Type的Matter;

关于Mongodb的几个大事件
1.根据美国数据库知识大全官网发布的DB热度排行,Mongodb的热度排名从2014年的第5名,在2015年跃升为第4名,仅次于主流DB(Oracle、MySQL、SQLServer)之后。

图片 1图片 2

然后,将图片路径保存到FileBank中,并返回FileBankID;

图片 3

 1 {
 2     "rating": {
 3         "max": 10, 
 4         "numRaters": 361, 
 5         "average": "8.8", 
 6         "min": 0
 7     }, 
 8     "subtitle": "A Handbook of Agile Software Craftsmanship", 
 9     "author": [
10         "Robert C. Martin"
11     ], 
12     "pubdate": "2008-8-11", 
13     "tags": [
14         {
15             "count": 295, 
16             "name": "编程", 
17             "title": "编程"
18         }, 
19         {
20             "count": 257, 
21             "name": "programming", 
22             "title": "programming"
23         }, 
24         {
25             "count": 150, 
26             "name": "软件开发", 
27             "title": "软件开发"
28         }, 
29         {
30             "count": 109, 
31             "name": "程序设计", 
32             "title": "程序设计"
33         }, 
34         {
35             "count": 100, 
36             "name": "计算机", 
37             "title": "计算机"
38         }, 
39         {
40             "count": 87, 
41             "name": "软件工程", 
42             "title": "软件工程"
43         }, 
44         {
45             "count": 66, 
46             "name": "敏捷开发", 
47             "title": "敏捷开发"
48         }, 
49         {
50             "count": 55, 
51             "name": "agile", 
52             "title": "agile"
53         }
54     ], 
55     "origin_title": "", 
56     "image": "https://img3.doubanio.com/view/subject/m/public/s29624974.jpg", 
57     "binding": "Paperback", 
58     "translator": [ ], 
59     "catalog": "", 
60     "pages": "464", 
61     "images": {
62         "small": "https://img3.doubanio.com/view/subject/s/public/s29624974.jpg", 
63         "large": "https://img3.doubanio.com/view/subject/l/public/s29624974.jpg", 
64         "medium": "https://img3.doubanio.com/view/subject/m/public/s29624974.jpg"
65     }, 
66     "alt": "https://book.douban.com/subject/3032825/", 
67     "id": "3032825", 
68     "publisher": "Prentice Hall", 
69     "isbn10": "0132350882", 
70     "isbn13": "9780132350884", 
71     "title": "Clean Code", 
72     "url": "https://api.douban.com/v2/book/3032825", 
73     "alt_title": "", 
74     "author_intro": "Robert C. “Uncle Bob” Martin has been a software professional since 1970 and an international software consultant since 1990. He is founder and president of Object Mentor, Inc., a team of experienced consultants who mentor their clients worldwide in the fields of C++, Java, C#, Ruby, OO, Design Patterns, UML, Agile Methodologies, and eXtreme programming.", 
75     "summary": "Even bad code can function. But if code isn’t clean, it can bring a development organization to its knees. Every year, countless hours and significant resources are lost because of poorly written code. But it doesn’t have to be that way.
76 Noted software expert Robert C. Martin presents a revolutionary paradigm with Clean Code: A Handbook of Agile Software Craftsmanship. Martin has teamed up with his colleagues from Object Mentor to distill their best agile practice of cleaning code “on the fly” into a book that will instill within you the values of a software craftsman and make you a better programmer—but only if you work at it.
77 What kind of work will you be doing? You’ll be reading code—lots of code. And you will be challenged to think about what’s right about that code, and what’s wrong with it. More importantly, you will be challenged to reassess your professional values and your commitment to your craft.
78 Clean Code is divided into three parts. The first describes the principles, patterns, and practices of writing clean code. The second part consists of several case studies of increasing complexity. Each case study is an exercise in cleaning up code—of transforming a code base that has some problems into one that is sound and efficient. The third part is the payoff: a single chapter containing a list of heuristics and “smells” gathered while creating the case studies. The result is a knowledge base that describes the way we think when we write, read, and clean code.
79 Readers will come away from this book understanding
80 How to tell the difference between good and bad code
81 How to write good code and how to transform bad code into good code
82 How to create good names, good functions, good objects, and good classes
83 How to format code for maximum readability
84 How to implement complete error handling without obscuring code logic
85 How to unit test and practice test-driven development
86 This book is a must for any developer, software engineer, project manager, team lead, or systems analyst with an interest in producing better code.", 
87     "price": "USD 49.99"
88 }

继续,插入Publisher信息(需要判断name不存在才会insert),然后返回PublisherID;

2.2015第六届中国数据库技术大会(DTCC)上,Mongodb高调宣布收购开源引擎WiredTiger,性能在3.0版本上实现了7~10倍的提升。

豆瓣图书查询返回内容

 1 CREATE PROCEDURE [base].[Publisher#Insert](@json nvarchar(max), @id int out)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         declare    @name nvarchar(100);
 8         select    @name=Publisher from openjson(@json, '$') with (Publisher nvarchar(100))
 9 
10         -- insert Publisher
11         insert    base._Publisher(Name)select @name
12         where    not exists(select 1 from base._Publisher p where p.Name=@name);
13 
14         select    @id=ID from base.Publisher#Raw() where Name=@name;
15 ...
16 END

Mongodb 适用场景简介

 

 

适用场景

刚才出去拿纱窗了,等晚上设计DB的时候继续更新。

继续,插入Binding信息(也需要判断name不存在才insert),返回BindingID;

  1. 实时的CRU操作,如网站、论坛等实时数据存储
  2. 高伸缩性,可以分布式集群,动态增删节点
  3. 存储大尺寸、低价值数据
  4. 缓存
  5. BSON结构对象存储
    不适用场景
  6. 高度事务性操作,如银行或会计系统
  7. 传统商业智能应用,如提供高度优化的查询方式
  8. 需要SQL的问题
  9. 重要数据,关系型数据

 

 1 CREATE PROCEDURE [base].[Binding#Insert](@json nvarchar(max), @id int out)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         declare    @name nvarchar(100);
 8         select    @name=Binding from openjson(@json, '$') with (Binding nvarchar(100))
 9 
10         -- insert Binding
11         insert    base._Binding(Name)select @name
12         where    not exists(select 1 from base._Binding p where p.Name=@name);
13 
14         select    @id=ID from base.Binding#Raw() where Name=@name;
15 
16 ...
17 END

Mongodb 性能监控与分析

基于豆瓣API获取的Response信息,所以抽取如下信息:

 

mongostat

豆瓣图书API信息
字段名称 豆瓣字段 字段描述
Title title  
SubTitle subtitle  
Authors author  
Translator translator  
ISBN13 isbn13  
ISBN10 isbn10  
AuthorIntro author_intro  
Summary summary  
Publisher publisher  
Binding binding  
OriginTitle origin_title  
Pages pages  
ImageUrl image  
Pubdate pubdate  
Catalog catalog  
Tags tags  

继续,插入Book信息;

  1. faults/s:每秒访问失败数,即数据被交换出物理内存,放到SWAP。
    若过高(一般超过100),则意味着内存不足。
    vmstat & iostat & iotop
    图片 4
    si:每秒从磁盘读入虚拟内存的大小,若大于0,表示物理内存不足。
    so:每秒虚拟内存写入磁盘的大小,若大于0,同上。

 

继续,插入BookInfo的信息;

mongostat

 

继续,插入BookNbr信息;

  1. idx miss %:BTree 树未命中的比例,即索引不命中所占百分比。
    若过高,则意味着索引建立或使用不合理。
    db.serverStatus()
    indexCounters” : {
    “btree” : {
    “accesses” : 2821726, #索引被访问数
    “hits” : 2821725, #索引命中数
    “misses” : 1, #索引偏差数
    “resets” : 0, #复位数
    “missRatio” : 3.543930204420982e-7 #未命中率
    }

 

继续,插入BookSupplement信息;

mongostat

 

继续,插入BookTag信息;

  1. locked
    %:全局写入锁占用了机器多少时间。当发生全局写入锁时,所有查询操作都将等待,直到写入锁解除。
    若过高(一般超过50%),则意味着程序存在问题。
    db.currentOp()
    {
    “inprog” : [ ],
    “fsyncLock” : 1,
    #为1表示MongoDB的fsync进程(负责将写入改变同步到磁盘)不允许其他进程执行写数据操作
    “info” : “use db.fsyncUnlock() to terminate the fsync write/snapshot
    lock”
    }

 

 1 CREATE PROCEDURE [base].[BookTag#Insert](@json nvarchar(max), @bookID bigint)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         -- insert Tag
 8         insert    base._Tag(Name)select value
 9         from    openjson(@json, '$.Tags') x
10         where    not exists(select 1 from base._Tag p where p.Name=x.value);
11 
12         insert    base._BookTag(BookID, TagID) select @bookID, x.ID
13         from    openjson(@json, '$.Tags') j join base.Tag#Raw() x on x.Name=j.value
14 
15 ...
16 END

mongostat

 

 

  1. q r|w :等待处理的查询请求队列大小。
    若过高,则意味着查询会过慢。
    db.serverStatus()
    “currentQueue” : {
    “total” : 1024, #当前需要执行的队列
    “readers” : 256, #读队列
    “writers” : 768 #写队列
    }

 

继续,插入BookAuthor信息;

mongostat

 

 1 CREATE PROCEDURE [base].[BookAuthor#Insert](@json nvarchar(max), @bookID bigint)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         -- insert Author
 8         insert    base._Author(Name)select value
 9         from    openjson(@json, '$.Authors') x
10         where    not exists(select 1 from base._Author p where p.Name=x.value);
11 
12         insert    base._BookAuthor(BookID, AuthorID) select @bookID, x.ID
13         from    openjson(@json, '$.Authors') j join base.Author#Raw() x on x.Name=j.value
14 
15 ...
16 END
  1. conn :当前连接数。
    高并发下,若连接数上不去,则意味着Linux系统内核需要调优。
    db.serverStatus()
    “connections” : {
    “current” : 3, #当前连接数
    “available” : 19997 #可用连接数
    }
You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图