Three things you should never put in the mysql database

  • 2020-06-19 11:51:30
  • OfStack

Images, files, and base 2 data should never be placed in the mysql database.

Many people would think that since the database supports BLOB data, there is nothing wrong with stuffing the file into the BLOB field! ? No, that's not true! For the record, large fields are not easy to handle in many database languages.
There are many problems with storing files in a database:

1. The database is never read/written as fast as the file system can process it
2. Database backups become huge and time consuming
3. Access to files traverses your application layer and database layer
These last two are real killers.
So save the image thumbnails to the database? As a result, you can't use nginx or any other type of lightweight server to handle them.
Make it easy for yourself to simply store the relative path of your files on 1 disk in the database, or use a service like S3 or CDN.

Do not place short lifetime data in the mysql database

Usage statistics, measurements, GPS location data, session data, anything that is useful to you only for a short period of time, or that changes frequently. If you find yourself using a timer task to delete data from a list that is valid for an hour, day, or week, you're not finding the right way to do things. Use redis, statsd/graphite, Riak -- they are all better tools for this kind of thing. This recommendation also applies to the collection of data that has a short life span.
Sure, it's possible to plant potatoes in your back garden with a backhoe, but it's much slower to make an appointment with a backhoe and wait for it to dig a hole in your garden than it is to get a shovel out of the storage room. Choose the right tools for the task at hand.

Do not place log files in the mysql database

Putting log data in a database may seem like a good idea on the surface, and it's tempting to say, "Maybe I'll need to do complex queries about this data in the future. This is not a bad idea, but it's not a good idea to store log data and your product data in one database.
Perhaps your logging is conservative, producing only one log per web request. For every event on the entire site, this still generates a large number of database inserts, competing for the database resources your users need. If your log level is set to verbose or debug, wait until your database catches fire.
You should use 1 such as Splunk Loggly or plain text files to store your log data. It may be inconvenient to look at them this way, but not often, and sometimes you'll need to write a few lines of code to analyze the answer you want, but overall it's worth it.


Related articles: