abstract :  Seven weapons of database , It's the seven common databases that I have been working on and contacted with , include 4 Common relational databases ,3 Species commonly used nosql database .
These databases are used as the storage selection of business bottom layer , Each database has its own location and characteristics , Combined 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 , include 4 Common relational databases ,3 Species commonly used nosql database .

These databases are used as the storage selection of business bottom layer , Each database has its own location and characteristics , Combined 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 database


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

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

2, characteristic :

function : Support transactions , Conform to the principle of relational database , accord with ACID, Support majority SQL 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 “ Install software - Create instance - Library table user initialization ”, Database deployment can be completed quickly

use : Use standard SQL Statement for database management , simple SQL Statement concurrency and good performance , To 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 as zabbix,nagios,cacti,lepus etc.

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

High availability :MySQL High availability with multiple options , Official Foundation master-slave Master-slave replication , New version of innodb cluster, The third party has MHA Equal high availability scheme ;

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

3, Applicable scenarios :

default innodb Storage engine , Support high concurrency , Most of the simple OLTP scene ;

Tokudb Storage engine , Use high concurrency insert Scene of ;

Inforbright Storage engine , Column compression and OLAP Statistics query scenario ;

4, Select note :

use MySQL conduct OLTP Business time , Need to pay attention to data magnitude , If the data magnitude is too large , Horizontal split required ;

If any OLAP demand , It can be considered in combination with other architectures .

Second weapon :SQL Server database




1, location : business ,Windows platform , Relational database

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

2, characteristic :

function : Support transactions , Conform to the principle of relational database , accord with ACID, Support majority SQL standard , Organize data in a two-dimensional table

deploy : stay Windows platform , Software installation with graphical interface ;

use : stay Windows platform , use SQL Server Mangement Studio Graphical interface for installation ;

monitor : General pass Windows Resource management and SQL 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 be realized SQL 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 scenarios : majority OLTP scene ( Cooperation with Microsoft system )

4, Select note :

SQL Server Close integration with Microsoft technology system , Most jobs , It's all done through a graphical interface , For those who are accustomed to using the command line DBA Maybe not used to it ;

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

use SQL Server satisfy OLTP business , It will have a better effect , But for large data volume OLAP business , It's better to choose a special one OLAP framework , Don't be in the same place SQL
Server Mix on instance OLTP and OLAP business ;

SQL server Commercial software , Copyright and licence Authorization fee ;

Third weapon :Oracle database




1, location :

business , Multi platform , Relational database

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

2, characteristic :


function : Support transactions , Conform to the principle of relational database , accord with ACID, Support majority SQL standard , Organize data in a two-dimensional table

deploy :Oracle Single instance database deployment is relatively easy , but Oracle 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 used shell Scripts and python Script improvement Oracle Database management efficiency ; Various management functions , They're all powerful ;

monitor :Oracle The government has a comprehensive monitoring tool , Common third-party monitoring platform , as zabbix,cacti,lepus Everything is right Oracle Perfect monitoring of all indexes of database ;

backups : Support cold and hot backup , It can be used exp/imp , expdp/impdp Etc. for logical backup and recovery , Powerful RMAN 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 , combination Oracle Single instance implementation ; have access to Oracle
Dataguard, realization master and standby Backup of ; have access to Oracle RAC High availability and load balancing at instance level by cluster , use ASM High availability for storage levels ;

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

3, Applicable scenarios : most OLTP scene , part OLAP

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

Fourth weapon :Postgresql database




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

2, characteristic :

function : Support transactions , Conform to the principle of relational database , accord with ACID, Support majority SQL standard , Organize data in a two-dimensional table ;

deploy : postgresql Need to be ready Python Isoenvironment , Then compile and install the software , Initialize database , Start instance , The whole deployment process is relatively clear ;

use : postgresql Database can be managed by command line , Or through pgadmin Graphical tools for management ; Various management functions , They're 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 , It can be used COPY Command for logical export and import ; use pgdump and pgrestore 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 code postgres-XC Achieve horizontal expansion ;

3, Applicable scenarios :

most OLTP scene , part OLAP

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

with postgresql As the underlying database greenplum data warehouse , It's mainstream MPP data warehouse ;

be based on postgresql Of TimeScaleDB, It is one of the most popular time series databases ;

4, Select note :

Postgresql Architecture of , Difficulty in use , Functionality between Oracle Database and MySQL 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 is postgresql A deficiency of database at present . The fifth weapon :Mongodb database




1, location : Open Source , Multi platform , Document type nosql database

Very mainstream document type nosql database ,“ Most like relational database ”, Positioning at “ flexible ” Of nosql database

2, characteristic :

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

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

use : I won't support it SQL sentence , Use and SQL Corresponding json 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 to mongoexport/mongimport Make a logical backup , You can also use the oplog Of mongodump/mongorestore Physical hot backup ;

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

ReplicaSets Copy set , stay mongodb 1.6 after , New
replicaset, Home to failover and auto repair member nodes , each DB Consistent data , This is recommended ; Can test read-write separation and failover ;

extend :mongodb Horizontal splitting of massive data , Store data in sharding On each node , Build a distributed cluster .Sharding Architecture by Bottom multiple mongodb
Shared Server,config Split configuration library horizontally config server, Front end routing route
process, Three parts .Sharding The bottom layer of the cluster can be mongodb Single instance , Can also be highly available replicaSet Copy set .

3, Applicable scenarios :

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 : about json file , binary data , Suitable for use mongodb Store and query

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

Cache system :mongodb Databases also use 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 and redis.

4, Select note :

Mongodb Unsuitable scene :

Highly transactional system : Traditional OLTP business ,mongodb, Even others nosql, Not very good for transactional support ;

Traditional statistical analysis application : Traditional OLAP business , Need highly optimized query method ,mongodb Poor support ;

use SQL Convenient business :mongodb yes json Query by type , Although flexible , But it's better to use SQL convenient , If business and fit SQL, It's not appropriate mongodb 了 .

The sixth weapon :Redis database




1, location :

Open Source ,Linux platform ,key-value Key type Nosql database

Simple and stable , Very mainstream , Full data in-momory, Positioning at “ fast ” Key value type of nosql database

2, characteristic :

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

deploy :
Using download software media , How to compile and install , Database deployment can be completed quickly ; Service start redis-server, You can use the default configuration , Operation parameter configuration , Profile launch , Three ways ;redis stay Linux Good platform support , Not officially Windows edition , Microsoft maintains a branch ;

use : use redis-cli Client connection , Simple set ,get,del Data management ;
In single instance redis Based on , 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 as cachecloud;

backups : Direct backup to physical asking price RDB Persistence , be based on AOF Real time of log AOF Persistence

High availability : Official redis sentinel Sentry high availability cluster

extend : Official distribution slot based redis cluster Distributed cluster

3, Applicable scenarios :

cache

Basic message queuing system

Leaderboard system

Counter use

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

4, Select note :

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

From the perspective of data scale , Small data scale use redis More appropriate , Large scale use of big data redis inappropriate ;( Big data scale , To some extent , It can be used SSDB replace redis use );

From the perspective of data hot and cold , Thermal data is suitable for redis in , Cold data is not suitable for redis 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

本文作者:赵飞祥