MySQL Database optimization Summary

One: MySQL database optimization considerations standard

1. Database Design (reasonable table design) three paradigms (canonical mode)

The three paradigms include:

The first paradigm: 1NF is the atomicity of the attribute, requiring that the attribute be atomic and non-decomposed. (As long as the relational database is satisfied)

Second paradigm: 2NF is a unique constraint on records, requiring records to have a unique identity, that is, the uniqueness of the entity. (Set Primary key)

The third paradigm: 3NF is a constraint on the field redundancy, that is, any field cannot be derived from another field, requiring no redundancy of the field. (through the table foreign key)

Inverse paradigm (appropriate redundancy): Improve query efficiency. PS: Redundancy should be placed on the table with as few records as possible to avoid wasted space.

2.sql Statement Optimization

3. Configuration of database parameters (cache size)

4. Proper hardware resources and operating system

Two: SQL statement optimization steps

1. Understanding the execution efficiency of various SQL through the show status command

The show status command can display the current status of your MySQL database. The data statement that concerns the "com_".

Displays the current console's MySQL situation:

Show status like "com%"; <=> Show session status like "com%";

Shows how the database starts up to this point:

Show global status Like "com%";

To display the number of connected databases:

Show status like "Connections";

Show when the server has worked:

Show status like "Uptime";

Shows the number of slow queries (default is 10s):

Show status like "Slow_queries";

Show slow query time:

Show variables like "Long_query_time"

Set slow query time (2s):

set long_query_time=2;

2. Navigate to a SQL statement that performs less efficiently

The MySQL database supports logging slow query statements to the log for program Ape analysis (the log function is not started by default). Boot: Go to MySQL's installation directory under bin

Mysqld.exe--slow-query-log. Qi gong mysql in log function mode

3. Analyze the execution of inefficient SQL statements by explain

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

4. Identify the problem and provide the appropriate optimization measures

Three: Several 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

Four: Database storage engine MyISAM and InnoDB comparison

1.MyISAM does not support foreign keys and does not support transactions . InnoDB are supported.

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

*.FRM (record table structure), *.MYD (record Data), *.myi (record Index)

InnoDB corresponds to only one file *.frm. The data is stored in the Ibdata1 file.

3. For the MyISAM storage Engine database to be cleaned up periodically:

Execute command:optimize table name;

Five: SQL statement optimization Add index

How the Index works: for the MyISAM storage Engine, the index is added to the. myi file. The database system also maintains a data structure that satisfies a particular lookup algorithm that references (points to) data in some way, so that an advanced find algorithm can be implemented on those data structures. This data structure is the index. the query speed will be significantly faster, but at the expense of deleting, modifying, and adding table data.

Type of index:

1. Primary key index. PRIMARY Key Auto main index (type Primary) Primary key comes with index

2. Unique index (unique). Uniqueness is also indexed

3. Normal index

4. Full-Text indexing (fulltext). only MyISAM storage engine support

5. Composite index (Dole together). CREATE index name on table name (column 1, column 2);

To create an index:

1.create [Unique/fulltext] index name on table name (column name ...);

2.alter Table name Add index index name (column name ...) ）;

3. If you add a primary key index:ALTER TABLE name add primary key (column name);

To 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;

To view all indexes for a table:

1.show indexes from table name;

2.show keys from table name;

3.DESC table name;

！！！ Ps!!! The following table queries will not use the index:

1. If there is an or in the query condition, it will not be used even if there is an index (or instruction to be used sparingly).

2. For a table that uses multiple-column indexes, only the leftmost column can use the index, and the remaining columns will not use the index.

3. For a like query, if the query is "%AAA" and the index is not used, "aaa%" is used to the index.
4. If the column type is a string, be sure to use quotation marks in the condition to reference the data, otherwise the index will not be used.

5. If MySQL estimates that using a full table scan is faster than using an index, the index is not used.

To view the usage of the index:

Show status like "handler_read%";

Note The result: Handler_read_key: The higher the value, the better, the higher the number of times that the index is queried. Handler_read_rnd_next: The higher the value, the less efficient the query.

VI: Explain analysis of SQL statements

For example: Explain select * from EMP where empno=2000\g;

The following information is generated:

Select_type: Represents the type of query.

Table: which tables to query.

Type: Represents the connection type for the table.

Possible_keys: Represents the type of index that may be used when querying.

Key: Represents the type of index actually used.

Key_len: The field length of the index.

Rows: Number of rows scanned.

Extra: Description and description of the performance.

Three types of type:

1.ALL: Complete table scan, usually not good. Eg:explain select * from Emp\g;

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

3.const: The table has a maximum of one matching row.

Extra Analysis:

Notables: Table does not exist.

Using Filesort: When query contains an order by operation and cannot be sorted by index.

Using temporary: Some operations must use temporary tables, common group by,order by.

Using Where: Without reading all the information in the table, you can only get the information you need by indexing.

Seven: Common SQL optimizations

1. Insert data in large batches:

For the MyISAM storage engine:

ALTER TABLE name disable keys; Avoid building large numbers of indexes

Loading data;

ALTER TABLE name enable keys;

For the InnoDB storage engine:

1. Sort the data you want to import by primary key

2.set unique_checks=0; Turn off Uniqueness Checks

3.set autocommit=0; Turn off auto-commit

2. Optimizing GROUP BY statements

By default, MySQL sorts all group by columns, similar to specifying an order by column in a query. If group by is included in the query but the user wants to avoid the consumption of query results, you can use ORDER by NULL to suppress sorting

Eg: select * FROM Dept GROUP by ename ORDER by NULL;

3. If the index is used in a query statement that contains or, then each condition column between or is required to be indexed , and if there is no index, you should consider increasing the index.

4. In applications with high precision, it is recommended to use fixed-point numbers (decimal) to store numeric values without using floating-point numbers (float) to ensure the accuracy of the results.

5. For the MyISAM storage Engine database, if you often do delete and modify the record operation, you want to execute the Optimize table table name periodically , defragment the table.

6. Date type Select the minimum stored type (timestamp:4 bytes , datetime:8 bytes) According to the actual need.

MySQL Database optimization Summary