mysql优化技术（MySQL optimization technology）.docVIP

mysql优化技术（MySQL optimization technology） The three layers of the database are mysql, Oracle, DB2, and SQL Server Client program DBMS database management system Compile, execute, cache Write the stored procedures directly (paged stored procedures / triggers) without compilation The database file - MySQL optimization is an integrated technology, mainly including: 1. table design is reasonable (accord with three normal form) Timed clear data not available SQL optimization 2. add appropriate index (index) General index primary key Unique index unique Full-text indexing 3. point table technique Logical layer 4. read / write separation 5. stored procedures Modular programming Do not compile, increase speed But the portability is not good 6. configuration optimization for MySQL Configure maximum concurrent number max-connections General web site around 1000, pay attention to memory size Configure cache size 7.mysql server hardware upgrade 8., regular cleaning does not require data, and regularly defragmentation MySQL optimization table design What kind of table is in conformity with 3NF? The normal form of table is 1NF--2NF--3NF 1NF: is an atomic constraint on attributes, and the columns of the table are atomic and can not be split The information in the column can not be decomposed: the information itself can express the meaning explicitly As long as the database is relational, it automatically meets 1NF Relational: MySQL, Oracle, DB2, infomix, sysbase Non relational: (object oriented or set) Nosql database, between relational and non relational database MongoDB (document oriented) 2NF: the record in the table is unique and satisfies 2NF The design of primary keys is usually implemented without the inclusion of business logic and is generally self increasing 3NF: do not redundant data in the table, that is, the information of the table can be pushed out, you should not design a field by yourself Anti 3NF: Table 1 to many, too cumbersome calculation, will be in one side o