MYSQL Schema Optimization

MySQL Schema Optimization:

1. Ensure the cleanliness of your database.

2. Archive old data-delete extra rows retrieved or returned from the query

3. Add an index to the data.

4, do not overuse the index, evaluate your query.

5. Compress text and BLOB data types-to save space, reduce read data from disk.

6, UTF 8 and UTF16 are slower than latin1.

7, the use of a controlled trigger.

8. Keep the minimum amount of data redundant-do not copy unnecessary data.

9. Use a linked table instead of an extension row.

10, pay attention to your data type, as far as possible to use the smallest.

11. If other data needs to be queried frequently and blob/text is not required, separate the Blob/text data from other data fields.

12, regular inspection and optimization of the table.

13, often do rewrite InnoDB table optimization.

14. Sometimes, when the column is added, the index is dropped first, and then the index is added faster.

15. Choose a different storage engine for different needs.

16. Log tables or Audit tables use the archive storage engine-write more efficiently.

17, the session data stored in the memcache, instead of MySQL-memcache can set automatic expiration, prevent MySQL to the temporary data high-cost read and write operations.

18. If the length of the string is variable, use varchar instead of char-to save space, because char is a fixed length and varchar is not (UTF8 not affected by this).

19, gradually change the schema-a small change will have a huge impact.

20. Test all schema changes in the development environment instead of mirroring the production environment.

21, do not arbitrarily change your profile, this can have a very big impact.

22, sometimes, a small amount of configuration will be better.

23, questioned the use of the common MySQL configuration file.

MYSQL Schema Optimization