MySQL database optimization summary, mysql database Summary

Source: Internet
Author: User

MySQL database optimization summary, mysql database Summary

I. Considerations for MySQL database Optimization

1. Database Design (rational table design) Three Paradigm (Standard Model)

The three paradigms include:

Paradigm 1: 1NF is a constraint on the atomicity of attributes. It requires attributes to be atomic and cannot be decomposed. (As long as it is a relational database)

Second paradigm: 2NF is the uniqueness constraint of a record. A record must have a unique identifier, that is, the uniqueness of an object. (Set a primary key)

The third paradigm: 3NF is a constraint on field redundancy. That is, no field can be derived from other fields, and no redundant fields are required. (Through the table foreign key)

Inverse Paradigm (appropriate redundancy): Improves query efficiency. PS: redundancy should be placed on tables with as few records as possible to avoid space waste ..

2. SQL statement Optimization

3. Configure database parameters (Cache size)

4. Proper hardware resources and Operating Systems

 

Ii. SQL statement optimization steps

1. Run the show status command to check the execution efficiency of various SQL statements.

The show status command displays the current status of your MySQL database. You are concerned about the data statements with "Com _" headers.

Display the current MySQL status on the console:

Show status like "Com %"; <=> show session status like "Com % ";

Displays the status of the database from the start to the current time:

Show global status like "Com % ";

The number of times the database is connected:

Show status like "Connections ";

Display the time when the server is working:

Show status like "Uptime ";

Display the number of slow queries (10 s by default ):

Show status like "slow_queries ";

Show slow query time:

Show variables like "long_query_time"

Set the slow query time (2 s ):

Set long_query_time = 2;

2. Locate SQL statements with low execution efficiency

MySQL Databases Support recording slow query statements into logs for analysis by programmers (by default, the log function is not enabled ). Start: Go to the MySQL installation directory bin.

Mysqld.exe -- slow-query-log. // use the log function to enable MySQL.

3. Analyze the execution of low-efficiency SQL statements through explain

Explain is a very important tool. This analysis tool can analyze SQL statements to predict SQL Execution efficiency.

4. Identify the problem and provide corresponding optimization measures

 

Iii. Types of SQL statements

Ddl (database definition language): create, alter, drop

Dml (Database Operation Language): insert, delete, update

Select

Dtl (Database Transaction language): commit, rollback, savepoint

Dcl (Database Control Language): grant, revoke

 

Iv. Comparison between the database storage engine MyISAM and InnoDB

1. MyISAM neither supports foreign keys nor transactions. All InnoDB support.

2. If the storage engine is MyISAM, there are three files after creating a table:

*. Frm (record table structure), *. myd (record data), *. myI (record index)

InnoDB only corresponds to one file *. frm. Data is stored in the ibdata1 file.

3. Regularly clean up the MyISAM storage engine database:

Run the command: optimize table name;

 

V. Adding indexes to SQL statement Optimization

Indexing Principle: For the MyISAM storage engine, the indexing is added to the. myI file. The database system also maintains data structures that meet specific search algorithms. These data structures reference (point to) data in some way, so that advanced search algorithms can be implemented on these data structures. This data structure is an index. The query speed becomes faster, but the cost of deleting, modifying, and adding table data is reduced.

Index type:

1. Primary Key Index. The Primary key automatically comes with an index for the Primary index (type: Primary ).

2. UNIQUE index (UNIQUE ). Uniqueness and Index

3. Common Index)

4. Full text index (FULLTEXT ). Only supported by the MyISAM storage engine

5. Composite Index (multiple columns are joined together ). Create index name on table name (column 1, column 2 );

Create an index:

1. create [UNIQUE/FULLTEXT] index name on table name (column name ...);

2. alter table name add index name (column name ...);

3. if you add a primary key index: alter table name add primary key (column name );

Delete An index:

1. drop index name on table name;

2. alter table Name drop index name;

3. if you delete the primary key index: alter table Name drop primary key;

View All indexes of a table:

1. show indexes from table name;

2. show keys from table name;

3. desc table name;

!!! PS !!! The following list queries do not use indexes:

1. If there is or in the query condition, it will not be used even if there is an index (or command should be used less ).

2. For tables that use multi-column indexes, only the leftmost column can use the index, and other columns will not use the index.

3. For like queries, if "% aaa" is used, indexes are not used, and "aaa %" is used.
4. If the column type is a string, you must reference the data in quotation marks in the condition. Otherwise, the index will not be used.

5. If MySQL estimates that using full table scan is faster than using indexes, no indexes will be used.

View the index usage:

Show status like "Handler_read % ";

Note: Handler_read_key: the higher the value, the better. The higher the value indicates the number of times the index is queried. Handler_read_rnd_next: a higher value indicates inefficient query.

 

6. explain SQL Statement Analysis

Example: explain select * from emp where empno = 2000 \ G;

The following information is generated:

Select_type: indicates the query type.

Table: Which table to query.

Type: indicates the table connection type.

Possible_keys: indicates the index type that may be used during query.

Key: indicates the actual index type.

Key_len: the length of the index field.

Rows: Number of scanned rows.

Extra: Description and description of execution.

Three types:

1. ALL: full table scan, usually not good. Eg: explain select * from emp \ G;

2. system: The table has only one row, which is a special case of the const connection type.

3. const: The table can have at most one matching row.

Extra analysis:

Notables: The table does not exist.

Using filesort: When the Query contains the order by operation, and the index cannot be used for sorting.

Using temporary: temporary tables must be used for some operations, such as group by and order.

Using where: you do not need to read all the information in the table. You can obtain the required information only through the index.

 

7. Common SQL Optimization

1. Insert data in large batches:

For the MyISAM storage engine:

Alter table name disable keys; // avoid creating a large number of Indexes

Loading data;

Alter table name enable keys;

For the InnoDB Storage engine:

1. Sort the data to be imported by primary key

2. set unique_checks = 0; Disable uniqueness Verification

3. set autocommit = 0; disable automatic submission

2. Optimize the group by statement

By default, MySQL sorts all group by columns, which is similar to the order by column specified in the query. If the query contains group by but you want to avoid consumption of the query results, you can use order by null to disable sorting.

Eg: select * from dept group by ename order by null;

3. If indexes are used in query statements containing or, indexes must be used for each condition column between or. If no indexes exist, you should consider adding indexes.

4. In applications with high precision requirements, we recommend that you use decimal to store values without float to ensure accuracy of the results.

5. For MyISAM storage engine databases, if you often delete and modify records, you must regularly execute the optimize table name and perform table fragmentation.

6. Select the minimum storage type (timestamp: 4 bytes, datetime: 8 bytes) based on actual needs ).

 

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.