abstract:  Seven weapons of database, It's the seven common databases that I have been working on and contacted with, Include4 Common relational databases,3 Commonly usednosql data base.
These databases are used as the storage selection of business bottom layer, Each database has its own location and characteristics, Integrated business, Have their own applicable scenarios, During specific use and operation and maintenance, There are also some special points for attention.

Seven weapons of database, It's the seven common databases that I have been working on and contacted with, Include4 Common relational databases,3 Commonly usednosql data base.

These databases are used as the storage selection of business bottom layer, Each database has its own location and characteristics, Integrated business, Have their own applicable scenarios, During specific use and operation and maintenance, There are also some special points for attention.

In this paper, in order“ Seven weapons”, Introduction and summary, I hope to help you sort out each“ arms” Characteristics and usage of, In the right scenario, Use the right weapons, Build your own data storage system.

First weapon:MySQL data base


1, Location: Open Source, Multi platform, Relational database

Most widely used at present, The most popular open source database.

2, Characteristic:

function: Support transaction, Conform to the principle of relational database, accord withACID, Support the majoritySQL Standard, Organize data in a two-dimensional table, With plug-in storage engine, Supports multiple storage engine formats

deploy: Build and install, Or binary package, according to“ Installation software- Create examples- Library table user initialization”, Database deployment can be completed quickly

Use: Use standardSQL Statement for database management, simpleSQL Statement concurrency and good performance, Opposite view, stored procedure, function, Triggers and other support are not very good

Monitor
: There are some common command display status and performance in the command line interface, In terms of graphical interface, There are many open source monitoring tools to monitor and record the status of the database, such aszabbix,nagios,cacti,lepus etc.

backups: Logical backup mysqldump/mysqldumper , Physical backup usextrabackup And other tools for backup;

High availability:MySQL High availability with multiple options, Official Foundationmaster-slave Master slave replication, New versioninnodb cluster, The third party hasMHA Equal high availability scheme;

extend:MySQL split horizon, Can be split horizontallyproxy Logical mapping and splitting in the middle, ExpandMySQL Concurrency and throughput of database.

3, Applicable scenario:

Defaultinnodb Storage engine, Support high concurrency, Most of the simpleOLTP scene;

Tokudb Storage engine, Use high concurrencyinsert Scene;

Inforbright Storage engine, Column compression andOLAP Statistics query scenario;

4, Selective attention:

UseMySQL ConductOLTP Business hours, Need to pay attention to data magnitude, If the data magnitude is too large, Horizontal split required;

If there isOLAP demand, It can be considered in combination with other architectures.

Second weapon:SQL Server data base




1, Location: business,Windows platform, Relational database

Earliest contact, Business database closely integrated with Microsoft system, belong to“ Microsoft technology system”

2, Characteristic:

function: Support transaction, Conform to the principle of relational database, accord withACID, Support the majoritySQL Standard, Organize data in a two-dimensional table

deploy: stayWindows platform, Software installation with graphical interface;

Use: stayWindows platform, UseSQL Server Mangement Studio Graphical interface for installation;

Monitor: General adoptionWindows Resource management andSQL server Graphic tools for system and database performance display;

backups: Generally, the third-party backup and recovery software is used for backup and recovery;

High availability: Through shared storage and dual hot standby, Can achieveSQL Server High availability of database;

extend: SQL Server Database cluster adopts the way of coexisting storage, Extending database cluster through hardware vertical upgrade;

3, Applicable scenario: MajorityOLTP scene( Cooperation with Microsoft system)

4, Selective attention:

SQL Server Close integration with Microsoft technology system, Most jobs, It's all done through a graphical interface, For command-lineDBA Maybe not used to it;

SQL server Double quotation marks, Case write, Management and processing of meta information, Very different from other databases, Need attention;

UseSQL Server satisfyOLTP business, It will have a better effect, But for large data volumeOLAP business, It's better to choose a special oneOLAP Framework, Don't be in the same placeSQL
Server Mix on instanceOLTP andOLAP business;

SQL server Commercial software, Copyright andlicence Authorization fee;

Third weapon:Oracle data base




1, Location:

business, Multi platform, Relational database

Most powerful, Most complex, Business database with the highest market share

2, Characteristic:


function: Support transaction, Conform to the principle of relational database, accord withACID, Support the majoritySQL Standard, Organize data in a two-dimensional table

deploy:Oracle Single instance database deployment is relatively easy, butOracle RAC Cluster environment, There are many steps and dependencies for deployment;

Use: Command line tools are commonly used, Manage various databases, It can also be usedshell Script andpython Script enhancementOracle Database management efficiency; Various management functions, They're all powerful;

Monitor:Oracle The government has a comprehensive monitoring tool, Common third-party monitoring platform, aszabbix,cacti,lepus And so on.Oracle Perfect monitoring of all indexes of database;

backups: Support cold and hot backup, Can use exp/imp , expdp/impdp Etc. for logical backup and recovery, PowerfulRMAN Tools for professional physical hot backup and recovery;

High availability:Oracle High availability schema for databases, It can use the third-party dual computer hot standby software, CombinationOracle Single instance implementation; have access toOracle
Dataguard, Realizationmaster andstandby Backup; have access to Oracle RAC Cluster implementation of instance level high availability and load balancing, UseASM High availability for storage levels;

extend: BecauseOracle Cluster adopts the way of shared storage, Generally, upgrading can only be done through vertical hardware upgrading;

3, Applicable scenario: MostOLTP scene, PartOLAP

4, Selective attention:Oracle From architecture to operation and maintenance, Arguably the most difficult database, Difficult to learn and use.

Fourth weapon:Postgresql data base




1, Location: Open Source, Multi platform, Relational database, The most powerful open source database.

2, Characteristic:

function: Support transaction, Conform to the principle of relational database, accord withACID, Support the majoritySQL Standard, Organize data in a two-dimensional table;

deploy: postgresql Need to be readyPython Such environment, Then compile and install the software, Initialize database, Boot instance, The whole deployment process is relatively clear;

Use: postgresql Database can be managed by command line, Or throughpgadmin Graphical tools for management; Various management functions, It's all powerful;

Monitor: You can view various performance views and status views on the command line; Relative to other databases, There is not a good graphic monitoring tool and platform;

backups: Support cold and hot backup, Can use COPY Command for logical export and import; usepgdump andpgrestore Physical backup and recovery;

High availability:postgresql Official support master-standby copy; It can also be used.Slony-I Database synchronization by third-party components;

extend:postgresql It can be realized by modifying the source codepostgres-XC Achieve horizontal expansion;

3, Applicable scenario:

MostOLTP scene, PartOLAP

Some information suitable for the current Internet needs, For example, geographic location information processing;

withpostgresql As the underlying databasegreenplum data warehouse, It's mainstream.MPP data warehouse;

Be based onpostgresql OfTimeScaleDB, It is one of the most popular time series databases;

4, Selective attention:

Postgresql Framework, Difficulty in using, Functionality betweenOracle Database andMySQL Between databases, But driven by its open source, Good development in all aspects;

Postgresql At present, there is no mainstream and easy-to-use monitoring platform, This ispostgresql A deficiency of database at present. The fifth weapon:Mongodb data base




1, Location: Open Source, Multi platform, Document typenosql data base

Very mainstream document typenosql data base,“ Most like relational database”, Locate in“ flexible” Ofnosql data base

2, Characteristic:

function
: Data file storage format isBSON, Mode freedom, The overall architecture corresponds to the relational database, High availability and scalability, With plug-in storage engine, The new version defaults towritedtiger Storage engine;

deploy: Simple deployment, Download a software, Set up the configuration file to start the service;

Use: I won't support itSQL Sentence, Use andSQL Correspondingjson Method management database;

Monitor: Rich monitoring and performance commands, The government has a relatively complete graphic monitoring system, But need to buy;

backups
: Support cold and hot backup, have access tomongoexport/mongimport Make a logical backup, You can also use theoplog Ofmongodump/mongorestore Physical hot backup;

High availability:MongoDB master-slave Master slave replication: staymaster Node addition --master parameter, Add from database
-slave and-source parameter, You can synchronize, This is not recommended at present;

ReplicaSets Replica set, staymongodb 1.6 after, New
replicaset, Home to failover and auto repair member nodes, eachDB Consistent data, This is recommended; Can test read-write separation and failover;

extend:mongodb Horizontal splitting of massive data, Store data insharding On each node, Build a distributed cluster.Sharding Architecture by Many at the bottommongodb
Shared Server,config Split configuration library horizontallyconfig server, Front-end routing route
process, Three parts.Sharding The bottom layer of the cluster can bemongodb Single instance, Can also be highly availablereplicaSet Replica set.

3, Applicable scenario:

Website background database:mongodb It's very suitable to be honest, Update and query, And real-time replication and high scalability, Suitable for quick update and iteration, Many changes in requirements, Object oriented website application;

Small file system: aboutjson file, binary data, Suitable for usemongodb Store and query

Log analysis system: For log files with large amount of data,IM Session message logging, Suitable for usemongodb To save and query;

Cache system:mongodb The database also uses a lot of memory, Reasonable design, It can also be used as a caching system; But at present, the cache system uses more schemes memcached andredis.

4, Selective attention:

Mongodb Unsuitable scene:

Highly transactional system: TraditionalOLTP business,mongodb, And othersnosql, Not very good for transactional support;

Traditional statistical analysis application: TraditionalOLAP business, Need highly optimized query method,mongodb Bad support;

UseSQL Convenient business:mongodb yesjson Query by type, Although flexible, But it's better to use it.SQL convenient, If business and fitSQL, It's not appropriatemongodb 了.

The sixth weapon:Redis data base




1, Location:

Open Source,Linux platform,key-value Key value typeNosql data base

Simple and stable, Very mainstream, Full datain-momory, Locate in“ fast” Key value typenosql data base

2, Characteristic:

function: Very fast command execution, Read and write performance up to10 ten thousand/ second; The data structure iskey-value Dictionary like functions, Can key expire- cache, Publish subscribe- Message system, Simple things function;

deploy:
Using download software media, How to compile and install, Database deployment can be completed quickly; Service startupredis-server, You can use the default configuration, Operation parameter configuration, Profile launch, Three ways;redis stayLinux Good platform support, No officialWindows Edition, Microsoft maintains a branch;

Use: useredis-cli Client connection, Simple set ,get,del Data management;
In single instanceredis On the basis of, Data persistence, Master slave replication, High availability and distributed functions;

Monitor: There are some common command display status and performance in the command line interface, In terms of graphical interface, There are open source monitoring tools to monitor and record the status of the database, such ascachecloud;

backups: Direct backup to physical asking priceRDB Persistence, Be based onAOF Real time of logAOF Persistence

High availability: Official redis sentinel Sentry high availability cluster

extend: Official distribution slot based redis cluster Distributed cluster

3, Applicable scenario:

cache

Basic message queuing system

Leaderboard system

Counter use

Social networking likes, Fans, Pull down refresh and other applications;

4, Selective attention:

Redis Usage scenarios of, yesredis Appropriate problem solving, There are also problems that are not suitable for solution.

From the perspective of data scale, Small data scale useredis More appropriate, Large scale use of big dataredis Inappropriate;( Big data scale, To some extent, Can useSSDB Replaceredis Use);

From the perspective of data hot and cold, Thermal data is suitable forredis in, Cold data is not suitable forredis in.

第七种武器:Hbase数据库




1,定位:开源,Linux平台,列存储nosql数据库

可用于海量数据存储,与Hadoop生态圈结合,定位于“大”的列存储nosql数据库

2,特点:


功能:命令执行速度非常看,读写性能可达10万/秒;数据结构是key-value类似字典的功能,可以键过期-缓存,发布订阅-消息系统,简单的事物功能;

部署
:相对其他数据库,hbase的部署比较复杂,依赖Hadoop,zookeeper等组件,Hbase集群包括一个mater节点,多个regionServer,zookeeper管理所有regionServer,需要依次部署Hadoop,zookeeper之后,再部署HBASE集群;

使用:用redis-cli客户端连接,一般用简单的 set ,get,del 进行数据管理;
在单实例redis的基础上,进行可以数据持久化,主从复制,高可用和分布式等功能;

监控:在命令行界面有一些常用的命令显示状态和性能,在图形界面方面,有开源监控工具来监控和记录数据库的状态,比如cachecloud;

备份:Hbase一般用作海量数据的仓库,本身通过多层副本来保证数据安全性,不用进行专门的备份

高可用:HBASE集群基于Hadoop,需要依次部署Hadoop单机模式,集群模式,HA模式,通过Hadoop HA实现高可用;

扩展:HBASE以集群形式,依次是单机模式,伪分布模式,完全分布模式,底层基于HDFS,zookeeper可以很好地进行扩展;

3,适用场景:

两大用途:

用于简单数据写入和海量,结构简单数据查询的业务场景;

用于成为其他数据库备份和下沉的数据库;

4,选择注意:

Hbase不适合的场景:对数据分析需求高,需要能够用sql或者简单的MapReduce实现分析需求的业务场景,不适合用Hbase;

单表数据量,不超过千万时,使用Hbase,体现不出Hbase的优势,而且会比较慢,不适合用Hbase.


通过对上面数据库“七种”武器的描述,也可以看到目前常用数据库的使用脉络和选择顺序,对应一个业务,可以优先选择最流行的开源数据库——MySQL;如果出于稳定和商业版考虑,可以选择Oracle数据库,或者SQL
Server数据库(与Windows体系结合);如果想用开源,有想要有足够的功能来应对各种场景,可以使用
postgresql数据库.这四种数据库,都是关系型数据库,可以很好地满足大多数业务场景,解决通用性问题.


对于一些特殊性问题,尤其是想要在扩展性方面有比较高的要求,可以考虑nosql数据库.Mongodb数据库,介于关系型数据库和非关系型数据库之间,兼具两者的特点,是非常流行的文档型nosql数据库;redis定位于内存型键值nosql数据库;hbase是海量文件存储的列式nosql数据库.根据合适的业务场景,选择适合的nosql数据库,可以对某一类,或某几类业务问题有很好的解决,可以作为关系型数据库的一种补充.

换个角度,MySQL,Oracle,SQL
Server,Postgresql,mongodb这五种数据库,也是DB-Engines排行榜上最流行的排名前五的五种数据库,从使用量和受欢迎程度,也可以看出这些数据库使用的广泛性.




原文发布时间为:2018-01-24

本文作者:赵飞祥