High performance MySQL Notes-4th Chapter optimizing Schema and Data Types

Source: Internet
Author: User

1.Good Schema design is pretty universal, but the course MySQL have special implementation details to consider. In a nutshell, it's a good idea to keep things as small and simple as can. MySQL likes simplicity, and so would the people who has to work with your database:
try to avoid extremes in your design, such as a schema that would force enormously complex queries, or tables with OO DLEs and oodles of columns. (an oodle is somewhere between a scad and a gazillion.)
use small, simple, appropriate data types, and avoid NULL unless it's actually the right-of-the-model your data ' s RE Ality.
try to use the same data types to store similar or related values, especially if they ' ll is used in a join condition .
Watch out for variable-length strings, which might cause pessimistic full-length memory allocation for temporary tab Les and sorting.
try to use integers for identifiers if you can.
Avoid the legacy mysql-isms such as specifying precisions for floating-point numbers or display widths for integers.
Be careful with ENUM and SET. They ' re handy, but they can be abused, and they ' re tricky sometimes. BIT is the best avoided.
normalization is good, but denormalization (duplication of data, in most cases) are sometimes actually necessary and be Neficial. We'll see more examples of this in the next chapter. And precomputing, caching, or generating summary tables can also be a big win. Justin Swanhart ' s flexviews tool can help maintain summary tables. Finally, ALTER table can be painful because in most cases, it locks and rebuilds the whole TABLE. We showed a number of workarounds for specific cases; For the general case, you'll have the techniques, such as performing the ALTER on a replica and
Then promoting it to master.

High performance MySQL Notes-4th Chapter optimizing Schema and Data Types

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.