website database developer


Some of the neglected aspects of using the database

website database developer

> Original photo by Bernard Hermant on Unsplash

In the past few weeks,I have been using the database,As part of my daily work,ExistingPostgres 9xDatabase migrated to the nearest version,Such aspostgres 13.As a lot of exposure I usually don't have, the application database layer I support,To make it successfully run in the migration script, so as to make them use and ensure the migration script.。But this deep dive makes me have time to understand our database and its structure.,And check some internal work of our app。So far,So far,Minority article(And a few,Stay in adjustment!),But today I want to talk about the database - Specialized relational database - From the perspective of software developers,Maybe some new and ambitious developers understand their data storage is better.。

Pick the right tool for this job

The first thing I want to talk about may be the most controversial theme in this list.,This is the right database for your app.。Now,I understand,Not everyone can choose to choose for their projectDB,But I think some most popularDBAnd their professionals/Disadvantages will help。

first,haveDBType problem:relation,Non-relational(orNoSQL)And key value(orkV)。

Authors pay attention:One thing I want to point out isSQLandNoSQLDifferent methods are wrong mistakes,Because of the relationshipDBNot necessarily useSQLAnd non-relationalDBScan useSQL。but,SQLandNoSQLAlready become a relationship and non-relational shorthand,So I will use these terms in the rest of the articles.。

relational database - Sometimes abbreviationRDBMSorSQLdatabase - Store two data:Fill the relationship between various data between the database and these bits。This allows thisSQL DBSFlexible in its inquiry,And make it easier for you to get the data you want to find,Because data is often in the form(Or standardization)OverheadNoSQL DB。Normalization reduces data redundancy and improves data integrity,Because you only need to store a little data and simply reference the location it needs。SQL DBSThe popular example isPostgreSQL,MariaDBandMicrosoft SQL Server。

NoSQLNon-relational,There are many different tastes(Too much talk about it),But it may be the most common is the document database。In addition toSQL DBSuch multiple tables,Data will be stored together as a data of the document in multiple tables.。Document is semi-structural,Because they are usually stored in some coding,Such asJSONorXML,However, the encoded data does not need to follow the consistent format.。You can store different types in a single document database(Mode)Document。Document database has flexible advantages,which isSQL DBSno,And according to the data you use,This may be an advantage。The popular document database isMongoDB,ElasticsearchandCouchbase。

(Website Database Developer)Key value storageNoSQLDatabase similar,Technical speech,Document database isKV DBSubset。but,For our purpose,We will makeKV DBSseparate,Because they provide different purposes from the point of view of the application。Key value storage is basically a hash table,And it may be very fast when you perform a lookup。That's why they are commonly used for applications.;Their reading performance is very good。However,usually,KV DBSLimited in its characteristics,therefore,Work with complex data may be uninstalled to some workSQLorNoSQLSome time and processing dense databases。Some of the most popular key shops areRedis,MemcachedConsular。

Now we have outlined the basic database type,How do we decide which one??The first thing you want to view is your data and determine five things.:

1.Which data will be stored in the database?

Will you store log files??User account?product information?jsondocument?Data types will help determineSQLorNoSQLWhether the database is the correct choice。

2.How complicated how to store data?

(Website Database Developer)Can my data can be easily normalized??If your data cannot be normalized,MaybeNoSQLThe database is the correct way。

3.How is the data developed??

Both all data units follow the same architecture??Can you meet the column type?If the data is not uniform,Then the document database may be the correct choice。

4.Requires frequency of reading or writing?

Whether this app will be read more or write more?Writing multiple applications may need to perform good databases under write overload。

5.Whether there is a database environment or business consideration?

(Website Database Developer)Do we have any contract or permit,These contracts may decide a brand or platform for a certain brand or platform.?Exiting the contract or precautions may mean useMicrosoft SQL ServerorOracleDatabase, etc.MariaDBOpen source。

finally,Which database is not I can tell you in the article。After considering all of the above factors,The development team needs a decision made by the development team.。

Management database

Asking your next question is“How do I want to manage my database?”?This seems to be a stupid problem,But useSQLQuery constructor or use full blow or use full blow or use completelySQLThe choice between queries is not a simple problem.。etc,ormWhat is it??

ORMorObject-Relational-MapperIs a method for managing objects in the database,Construct the database(surface,Column)Indicates the object of the programming language you selected。usually,Table is expressed as class,Class properties are columns,etc。useORMCan make life easier,Especially at the beginning,butORMThere are also their shortcomings。

ormsUniversal。They must be able to handle any type of query for any type of data,therefore,They tend to optimize excessive flexibility。useORMQuery data,Especially in a large mass production environment,May lead to delay,Dead lock,Lock, etc.,Leading your app to wear to stop。I have seen someormsDo some very weird things,Such as multiple nested transactions and things。But they still have their position。Early development,Prototype design and low volume database access is usedORMGood use case。

or,You can simply write the originalSQL。Although this is likely to provide you with the most performance,But it may also be a more difficult way to access data.。also,MostORMSdifferent,It automatically escapes special characters and quotes strings,If you have your handle(Especially the data provided by the user)Be less careful,writeRAW SQLThe application can be opened toSQLinjection attack。

(Website Database Developer)somewhere in the middle isSQLConstructor。They provide someORMS(SQLinjection relief,More common query methods)some of the same tools,without someORMSExpenses brought to the table。They are basically used to constructSQLString builder for statements。they are inORMSandDIY SQLprovide intermediate ground between。


One of the most overlooked and important aspects of understanding databases is indexing。Indexes provide a way for databases to,to quickly look up values in a table without iterating over all rows in the table。Index lookups as the database needs to be updated,Compromises Storage space required for trade-offs increases holding indexes and increases write time。SQL DBMost tables in have a primary key that is automatically indexed by the database;also,You can specify additional columns in the table to be indexed。

Suppose you are using the user table,and want to retrieve the user's email when the username is entered。Our table might look like this:

id*| username | email -------------------------------------------------

1 | user_ted |

2 | user_jill |

3 | user_annie |

under these circumstances,IDis our primary key,so automatically has an index associated with it。but,Your users will not submit their line IDs,They will submit their username。There are only three rows in the table,Indexes are not that big in terms of performance,But when the table grows to100what happens when you run? 1,000? 10,000?when table is added to table,Every query of user email needs to be longer and more extended。By adding an index to the username column,We can reduce this query time to an almost constant time(oro(1))。same,This requires some disk space and write performance,But since we'll be looking for users more often,And our eclectic is a very nice guy,As long as we're not taken away。Taken away would be to add indexes we don't need,like an index on an email,Because we never looked up by email address。By adding email index,All we're going to do is add more time to insert and use disk space unnecessarily。


When you have an application that will have long connections to the database,Can periodically check if the database is still available。According to the database driver,language and other factors,There may be ways to do this easily,such as checking open connections,But sometimes it's easiest to runDBInquire。However,What your running doubt is depends on your goals。

Target:I want to make sure all my tables are there

OK,I understand the desire to ensure your database is in a usable state。but,I do not recommend this is a routine health check。I would perform such verification steps on application startup。for a,Getting a list of tables will put a read load on the database。it may be small,but if you have multiple copies of the application,Run the same health check concurrently,then the performance impact will be large。but,if you're really cold doing this,then it's important to remember that you may see a performance hit。

Target:I want to get database statistics

I like this。Get statistics from database,as read/write load,will be something that the app can consume after logging in。In the event of exceeding the database availability state,There aren't many apps themselves,but may help when troubleshooting。

Target:I just want to make sureDBYesUP

This is probably the most basic and logical health check,at least for me。andDBperformance or validation or whatever,Every other tool can handle every other tool。Checking for base availability is really the only thing your app cares about works,So keeping it simple is the best way in my opinion。So what kind of query should you use?All right,What you definitely don't want to do is something like this:

SELECT * FROM 'Customers';

No no no no。stop。

Yes,this will checkDBdoes it exist。But it will also give you a list of all clients,This is your customer base growth,This means that your health checks will also grow in execution time。Do not do this。This is the real thing I saw recently,so yes,it did happen。on the contrary,The best and simplest query is this:


(Website Database Developer)it's basically the same

def my_function(): return

Simple,and get the job done。You really can't get better than this。


It's just a little bit of everything that works with the database,But I hope it provides some insight into some oft-overlooked areas。please remember:The database will usually be the performance bottleneck in your application,So please dispose of them properly,and always looking for a faster way。

(Website Database Developer)Is there something I'm missing?do you want to see my cover?Let me know in the comments!

(This article was translated by Wen Shuqiwu fromundefinedarticle《DB 101: Databases for Developers》,Please indicate the source,Original link: