10 database optimization best practices for web developers

The new titanium cloud service has been accumulatively shared with you 663 Technical dry goods

Database optimization has become web Developers improve web Application performance , So as to improve the user experience . For some people , This may not sound very attractive , But the benefits are worth it . If the database is optimized correctly , You can improve performance 、 Reduce bottlenecks and save resources .

Fortunately, , Some optimization techniques are sql There is no need for dba Level ( Database administrator ) Professional knowledge of .

To help you start , We're going to look at 10 Best practice , by web How developers optimize your database . Once you understand these , You can study database optimization more deeply .

One 、 Use EXPLAIN

Use EXPLAIN Keywords are a good way to quickly repair slow queries . It lets you know how to execute queries , And help you find the potential location of query optimization .

Just in SELECT Input before query EXPLAIN keyword . This does not trigger the actual query , contrary , You will see the execution plan .

When you look at the results carefully , You will be able to identify potential bottlenecks or any other problems , If the index is missing , And reduce the number of scanning lines .

With EXPLAIN Tools , You can optimize queries , If necessary , You can even improve the table structure .

Two 、 Add an index to the search column

If you search the table by a specific column , It is likely to improve query performance by indexing this column .

By adding an index to the search column , It can reduce response time and optimize resource use . Although the index may not help all queries , But in most cases it is absolutely useful .

But remember , An indexed table takes more time to update than a table without an index . This is because the index also needs to be updated . therefore , It is wise to create indexes only for frequently searched columns , Instead of creating indexes for tables that are updated more than read .

3、 ... and 、 Use identification fields whenever possible

Use... In the table Identity Fields as primary keys have several advantages .

First , It's faster. — You can simply use integers in queries , Instead of longer string fields . This will save you some memory , Because integers can be shorter .

secondly , It's safer —— Using application data fields can cause many problems . for example , If you use a name or address as PRIMARY KEY, So if the client or user changes their name 、 Moved , Even just spelling mistakes , Then you may have problems .

therefore , In order to speed up query and improve work efficiency , You can add one in each table Identity Column , So you can use it as a PRIMARY KEY, And use AUTO_INCREMENT And a suitable type INT Variable .

Four 、 Avoid using by default NULL value

Use as much as possible NOT NULL Instead of NULL.

Generally speaking , choice NOT NULL Means faster queries , This benefits from more efficient use of indexes , It also avoids testing whether each value is NULL The need for . most important of all , You use less storage space , according to MySQL file ,NULL Columns require additional space .

Saving space is particularly important , If you use a web host for your database , Because even some of the best network host services cannot provide unlimited storage space . Now? , Saving a bit per column doesn't seem like a lot , But if you are running an e-commerce store , There are thousands of products , You will save a lot of resources .

Use NOT NULL, You will be able to process fields as you would any variable , At the same time, avoid using NULL Some edge conditions that may occur .

Of course , There are still some cases of using NULL More beneficial — But in most cases , You can use NOT NULL Achieve the same result .

5、 ... and 、 Use unbuffered mode for queries

To save time and memory , You can use unbuffered queries .

By default ,SQL Queries use buffer mode . This will increase your waiting time and consume resources , Because the result will not be returned until the query is completed — It's stored in memory . If it is a large query , And you have a huge database , Then using buffered queries will require a lot of memory .

by comparison , When using unbuffered queries , The results are not automatically stored before the query is executed . Once the first line is retrieved , You can start working on them .

But remember , When processing result sets , Unbuffered queries do not allow further queries to be issued on the same connection .

6、 ... and 、 Compact columns

Optimizing disk space is essential to keep the database engine running well . A very simple way to ensure that performance is not affected is to use small, compact columns .

therefore , You should always choose the integer type that is most useful for your application . for example , If you know that your table will not have a large number of rows , Then don't use it automatically INT A primary key — You may actually use SMALLINT even to the extent that TINYINT Benefit from .

about DATE and DATETIME So it is with —— If you don't need time in particular , Just use DATE that will do .DATETIME The data type occupies 8 Bytes , and DATE Occupy only 3 Bytes — So this can save 5 Bytes .

7、 ... and 、 Keep the table static ( Fixed length )

Another way to optimize database performance is to use static tables .

This means that your table should not include any variable length columns , Such as TEXT or BLOB. You can use CHAR、VARCHAR、BINARY and VARBINARY Column , But you need to fill them to match the specified column width .

It is beneficial to use a fixed length table , Because they're faster , Easier to cache . besides , Static tables are more secure —— They are easier to rebuild after a crash .

however , In some cases, static tables may require more disk space than dynamic tables — Especially when you use CHAR and VARCHAR Column time . But the performance improvement may exceed any focus on disk space .

8、 ... and 、 Install the object relationship mapper (ORM)

Use ORM It is another good way to optimize the working mode of database .

First ,ORM Help eliminate human error factors , Because it does a lot of things for you . You don't have to write so much code yourself , and ORM Will handle many repetitive tasks for you , Thus reducing your workload .

ORM It can also improve the security of the system , Because preparing and cleaning up queries makes SQL Injection becomes more difficult .

most important of all ,ORM Entities will be cached in memory , This reduces database and CPU The load of .

Of course ,ORM It has its advantages and disadvantages , It may not be completely suitable for your use . But there are some simple ways to avoid abusing objects - Relation mapping . Besides , There are other performance tuning and optimization plug-ins that may be more suitable for you .

Nine 、 Batch execution DELETE and UPDATE Inquire about

Delete and update data —— Especially in very large tables —— It could be complicated . It can take a lot of time , And both commands are executed as a single transaction . It means , If there is any interruption , The entire transaction must be rolled back , This may be more time-consuming .

however , If you follow the batch run DELETE and UPDATE Good practice of inquiry , You will be able to save time by increasing concurrency and reducing bottlenecks .

If the number of rows deleted and updated at one time is small , You can perform other queries when submitting the batch to disk . And any rollback you may need to do will take less time .

Ten 、 Use program analysis () Get more tips

The last best practice for optimizing a database is to use built-in functionality :PROCEDURE ANALYSE(). More specifically , If you add this command to a SQL In the sentence , It will look at the columns , Then recommend the best data type and data length .

After importing the new data into the table —— Or even check the existing table for inconsistencies —— This can be particularly useful .

If you implement these recommendations , You may save some space . However , please remember , These are just suggestions , You must really consider whether they are suitable for your particular purpose .

Conclusion

Database optimization is a bit tricky , But avoiding it completely will be good for you web Applications have a huge impact , Cause performance problems .

If you follow this 10 individual web Best practices for developer database optimization , You will begin to improve user experience and resource management .

Once these methods are implemented , Please learn more about tips on how to improve database performance .

Link to the original text ：https://dzone.com/articles/10-database-optimization-best-practices-for-web-de

Previous technical dry goods

· Ten thousand words long text ： Cloud Architecture Design Principles | attach PDF download

· Ten thousand words long text | Use RBAC Restricted pair Kubernetes Access to resources

· Ten thousand words long text | oriented k8s Programming , How to write a Operator

· Terraform actual combat | Ten thousand words long text

· Ten thousand words long text | stay Kubernetes Best practices for deploying highly available applications ！

· CephFS Performance benchmarking and cluster optimization | Ten thousand word summary

· Low code development , Development by the whole people , Eliminate professional programmers ！

· Domestic mainstream public cloud VPC Use comparison and summary

· Ceph OSD Troubleshooting | Ten thousand words experience summary

· The lifelong growth of O & M people , Start with inventory management | Ten thousand words long text ！

· OpenStack And ZStack Depth contrast ： framework 、 Deploy 、 Computing storage and networking 、 Operation and maintenance monitoring, etc

· IT Hybrid cloud strategy ： What is it? 、 Why? , How to build ？

Share

Poke at