MySQL Simple optimization

1: MySQL optimization is a comprehensive technology, mainly including:    A, the design of the standard (in accordance with 3NF)     B, add the appropriate index (index) (normal, primary key index, unique index unique, full-text index)     C, sub-table technology (horizontal separation, vertical separation)     D, read/write (write Delete/update/add) separation     E, stored procedures (modular programming, can improve speed)     F, configuration optimization for MySQL (configure maximum concurrency, resize cache)     g, MySQL server hardware upgrade     H, scheduled cleanup of unwanted data, Timed defragmentation (MyISAM) 2: What kind of table is in line with the 3NF (normal form)     table paradigm, is first in line with 1NF to meet 2NF, and further meet 3NF.     2.1:1NF: The column of the table is atomic, not in decomposition, that is, the column of information powder can be decomposed, as long as the database is a relational database (MySQL, Oracle, DB2, Informix, sysbase, SQL Sever), automatically meet 1NF.         The so-called First paradigm (1NF) refers to the fact that each column of a database table is an indivisible basic data item and cannot have multiple values in the same column, that is, an attribute in an entity cannot have multiple values or cannot have duplicate properties. If duplicate attributes are present, you may need to define a new entity, which is composed of duplicate attributes, and a one-to-many relationship between the new entity and the original entity. In the first normal form (1NF), each row of a table contains only one instance of information. In short, the first paradigm is a column with no duplicates.         Database classification:        relational database: MySQL, Oracle, DB2, Informix, sysbase, SQL Sever         Non-relational database: Features: Object-oriented or set         NoSQL database: MongoDB (features document-oriented)     2.2:2 NF: The record in the table is unique and satisfies the  2NF, usually we design a primary key to implement.  &nbsP The       second paradigm (2NF) is established on the basis of the first paradigm (1NF), that is, satisfying the second paradigm (2NF) must first satisfy the first normal form (1NF). The second normal form (2NF) requires that each instance or row in a database table must be divided by a unique region. For the implementation of the distinction, it is common to add a column to the table to store unique identities for each instance. The attribute of the entity is required to be completely dependent on the primary key, in short, the second paradigm is that the non-primary attribute is dependent on the primary key. Primary key does not contain business logic     2.3:  satisfies the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a database table not contain non-primary key information already contained in other tables. In short, the third paradigm is that properties do not depend on other non-principal properties.         3NF: There is no redundancy in the table, that is, the table information, if it can be deduced, should not be a separate design to store, such as the following table is not satisfied   should be:    anti- 3NF: However, no redundant database is not necessarily the best database, sometimes in order to improve operational efficiency, it is necessary to reduce the paradigm standard, appropriate retention of redundant data. The practice is to follow the third paradigm when designing the conceptual data model, and to reduce the work of any standard into the physical data model design. Lowering the paradigm is adding fields, allowing redundancy.  3:sql Statement Optimization     PROBLEM: How to quickly position a slow statement from a large project (locate slow query)    ① First we need to understand MySQL Some of the operational states of the database are queried (for example, to know the current MySQL run time, how many times Select/update/delete is running). , current link)         Common execution commands:            show status like "uptime";        //How long does it start             show status Like "Com_select" (come_delet\com _update);    //How many queries were executed (delete, add)             Show [session | global] status like ...   If you do not write [session | Global] is the session (current) By default, the execution of the current window is removed, if you want to see all (from Mys QL launches to now, then should join global)             show status like "connections";            //attempts to link MySQL server             show status like "slow _queries ";          //Show slow queries    ② How to position slow query             By default, MySQL thinks 10 seconds is a slow query             Modify MySQL Slow query time:              &NBS P A, show variables like "long_query_time" (query default slow query time)                 B, set long_query _time=1; (Set slow query time is one second, restart MySQL will revert to 10s)             Modify command execution Terminator: Delimiter +$$ (Terminator)             If the SQL of slow query is recorded in one of our logs, by default, our MySQL will not log slow query, need to restart MySQL time, specify slow query line      The database has various data objects (tables, stored procedures, views, functions, triggers)            First turn off MySQL on startup, if slow query log is enabled, by default this file is placed in the location recorded in the My.ini file (datadir= "");            Enable slow check:--safe-mode (Safe mode)                       &nbs P Optimization issues:        1, add index (full-text index/UNIQUE index/PRIMARY key index/normal index)            ① primary key index: When a table, To set a column as the primary key, the list is the main key index             If you do not specify a primary key index when creating the table, you can also add         after creating the table.         command: ALTER TABLE name add primary key (list);                Example: CREATE TABLE ABC (ID int, name Varchare (+) NOT null default ""), (no index)               & nbsp        alter table ABC add primary key (ID); (add index)                 Database adding indexes will have some memory overhead, and three files will be generated in data:                               &NBsp       .FRM: Represents the table structure                    . MYD: Represents table Data                    . MYI: Table index         2, query index:             ①DESC table name "Disadvantage of this method: Index name cannot be displayed" &nbsp ;            ②show index (ES) from table name              ③show key From table name  

MySQL Simple optimization