005-mysql Database Design Principles

Source: Internet
Author: User
Tags mysql index

1. Core Principles

Do not operate in the database;
CPU computing must be moved to the business layer;
Number of control columns (field few but good, number of fields recommended within 20);
Equilibrium paradigm and redundancy (efficiency first; often sacrificial paradigm)
Reject 3B (Deny large SQL statement: Big SQL, reject large things: big transaction, reject Mass: big batch);

2. Field class principle

Use a good value type (save space with the appropriate field type);
Convert characters into numbers (the best conversions that can be transformed, as well as saving space and improving query performance);
Avoid using null fields (Null fields are difficult to query for optimizations, NULL field indexes require extra space, invalid composite index for null fields);
Less text type (use varchar instead of the text field as much as possible);

3. Index class principle

Rational use of indexes (improve the query, slow down the update, the index must not be more the better);
The character field must have a prefix index;
Do not perform column operations in the index;
InnoDB the primary key is recommended to use the self-increment column (the primary key to establish the clustered index, the primary key should not be modified, the string should not be the master key) (Understand the InnoDB index preservation structure to know);
No foreign keys (guaranteed by the program);

4. SQL class principle

SQL statements as simple as possible (a SQL can only be in a CPU operation, large statements to split small statements, reduce lock time, a large SQL can block the entire library);
A simple transaction;
Avoid using TRIG/FUNC (triggers, functions are not replaced by client programs);
No SELECT * (Consumes Cpu,io, memory, bandwidth, this program is not extensible);
or overwrite to in (or efficiency is n level);
or rewrite to union (MySQL index merge is mentally retarded);
Select ID from t where phone = ' 159′or name = ' John ';
=
Select ID from t where phone= ' 159′
Union
Select ID from t where name= ' Jonh '
Avoid negative to%;
Use COUNT (*) with caution;
Limit efficient paging (the greater the limit, the less efficient);
Use UNION ALL instead of union (union has a de-heavy cost);
Use less connection join;
Use GROUP by;
Please use the same type comparison;
Break up batch update;

5. Performance analysis Tools

Show profile;
Mysqlsla;
Mysqldumpslow;
Explain:http://www.cnblogs.com/bjlhx/p/7567110.html
Show slow log;
Show Processlist;

005-mysql Database Design Principles

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.