Mysql Database performance Optimization a _mysql

Today, database operations are increasingly becoming a performance bottleneck for the entire application, especially for Web applications. As for the performance of the database, this is not just something that DBAs need to worry about, and that's what we programmers need to focus on. When we design the database table structure, we need to pay attention to the performance of the data operation when we operate the database (especially the SQL statement when we check the table). Here, we don't talk too much about the optimization of SQL statements, but only for MySQL, the most web-application database.

MySQL performance optimization can not be done in one step, must take slowly, from all aspects of optimization, the final performance will have a big upgrade.

Optimization technology of MySQL Database

MySQL optimization is a comprehensive technology, mainly including

• Rationalization of the design of the table (in line with 3NF)

• Add appropriate index [four: normal index, primary key index, unique index unique, Full-text index]

• Sub-table technology (horizontal split, vertical segmentation)

• Read/write [writing: Update/delete/add] Separation

• Stored procedures [modular programming to improve speed]

• Optimize for MySQL configuration [Configure maximum concurrency number My.ini, resize cache]

MySQL server hardware upgrades

• Periodically remove unwanted data and defragment regularly (MyISAM)

Database optimization Work

For a data-centric application, the quality of the database directly affects the performance of the program, so database performance is critical. In general, to ensure the efficiency of the database, to do the following four aspects of work:

① Database Design

②sql Statement Optimization

③ Database Parameter Configuration

④ appropriate hardware resources and operating systems

In addition, the use of appropriate stored procedures can also improve performance.

This order also shows the size of the performance impact of these four jobs

database table Design

A popular understanding of three paradigms is good for database design. In the database design, in order to better apply three paradigms, it is necessary to understand the three paradigms in a popular way (pass

Vulgar understanding is a sufficient understanding, not the most scientific and accurate understanding):

The first normal paradigm: 1NF is an atomic constraint on attributes, requiring that attributes (columns) to be atomic and not to be decomposed; (as long as the relational database satisfies 1NF)

The second normal paradigm: 2NF is the uniqueness of the record constraint, requiring the record to have a unique identity, that is, the uniqueness of the entity;

Third paradigm: 3NF is a constraint on the redundancy of the field, which requires no redundancy in the field. No redundancy in database design can be done.

However, the database without redundancy is not necessarily the best database, sometimes in order to improve the efficiency of operation, it is necessary to reduce the normal standard and keep redundant data appropriately. The practice is to adhere to the third paradigm when designing the conceptual data model, and to reduce the standard of normal work into the physical data model design. The lower paradigm is adding fields, allowing redundancy.

Classification of ☞ databases

Relational database: Mysql/oracle/db2/informix/sysbase/sql server

Non-relational database: (Feature: Object-oriented or set)

NoSQL database: MongoDB (feature is document oriented)

Give examples of what is moderately redundant, or justified redundancy!

The above is an inappropriate redundancy because:

Here, in order to improve the retrieval efficiency of the student activity record, the unit name is redundant to the student activity record. Unit information has 500 records, and student activity is recorded in

There are about 2 million data in a year. If the Student Activity record table is not redundant this unit name field, which contains only three int fields and one timestamp field, occupies only 16 bytes, is a very small table. And the redundancy of a varchar (32) of the field is the original 3 times times, the corresponding retrieval of more than so much I/O. And the record number is very different, 2000000, resulting in the update of a unit name and update 4,000 redundant records. Thus, this redundancy is simply counterproductive.

The price in the order form is a redundant field, because we can count the prices of this order from the order list, but this redundancy is reasonable and can improve query performance.

A conclusion can be drawn from the above two examples:

1---N redundancy should occur on the 1 side.

SQL statement Optimization

General steps for SQL optimization

1. Learn about the execution frequency of various SQL through the show status command.

2. Locate execution less efficient SQL statement-(Focus Select)

3. Analysis of inefficient SQL through explain

4. Determine the problem and take appropriate optimization measures

--SELECT statement classification
Select
DML Data Manipulation language (insert update delete)
DTL data Things language (commit rollback savepoint)
DDL data Definition language (Create Alter drop ...)
DCL (Data Control Language) grant revoke--
the Show Status Common Command-
-query This session show sessions
status like ' com_% ';//show status Like ' Com_select '
--query global show global
status like ' com_% ';
--Grant all
privileges on *.* to ' ABC '% for a user;
Why is this authorized ' ABC '  to represent the host, see the Mysql->user table-
-RECLAIM permissions revoke all on
*.* from ' abc ' @ '% ';
--Refresh permission [also can not write]
flush privileges; 

SQL statement Optimization-show parameters

After the MySQL client connection succeeds, you can provide server status information by using the show [Session|global] Status command. The session represents the statistical results of the current connection, and global represents the statistical results from the date the database was last started. The default is the session level.

The following example:

Show status like ' com_% ';

Where com_xxx represents the number of times the XXX statement was executed.

Note: Com_select,com_insert,com_update,com_delete through these parameters, it is easy to know whether the current database application is based on insert update or query operations, as well as the amount of SQL roughly executed.

There are also several commonly used parameters to facilitate users to understand the basic situation of the database.

Connections: Number of attempts to connect to the MySQL server

Uptime: Server working time (in seconds)

Slow_queries: Number of slow queries (default is slow query time 10s)

Show status like ' Connections ' show
status like ' Uptime '

How to query MySQL for slow query time

Modify MySQL Slow query time

SQL statement optimization-Locating a slow query

The question is: how to quickly locate execution-slow statements from a large project. (Locate the slow query)

First of all, we understand how the MySQL database running some of the status of the query (such as to know the current MySQL run time/total number of executions)

Select/update/delete.. /Current connection)

To facilitate testing, we built a large table (4 million)-> using stored procedures to build

By default, MySQL thinks 10 seconds is a slow query.

Modify MySQL's slow query.

Show variables like ' long_query_time '; Can display the current slow query time

The construction of large table-> large table records have requirements, records are different to use, or test results and real difference between the big. Create:

CREATE Table Dept (/* Department table/*
deptno mediumint UNSIGNED NOT NULL default 0,/* number
/dname VARCHAR () NOT NULL default "", * * name
/loc VARCHAR () not NULL DEFAULT ""/* location * * * *
engine=myisam DEFAULT Charset=utf8;

CREATE TABLE emp
(empno mediumint UNSIGNED NOT NULL default 0,/* number */
ename VARCHAR () NOT NULL default "",/* name * /
Job VARCHAR (9) NOT NULL default "",/* Work/
Mgr Mediumint UNSIGNED NOT NULL default 0,/* parent number/
HireDate DATE Not null,/* entry time
/Sal decimal (7,2) not null,/* salary/
comm DECIMAL (7,2) Not null,/* dividend * * DEPTNO
Mediumint UNSIGNED not NULL default 0/* Department number * *
engine=myisam DEFAULT Charset=utf8;

CREATE TABLE salgrade
(
grade mediumint UNSIGNED NOT null DEFAULT 0,
losal DECIMAL (17,2) not NULL,
hi Sal DECIMAL (17,2) not NULL

Test data

INSERT into Salgrade VALUES (1,700,1200);
INSERT into Salgrade VALUES (2,1201,1400);
INSERT into Salgrade VALUES (3,1401,2000);
INSERT into Salgrade VALUES (4,2001,3000);

In order for the stored procedure to execute properly, we need to modify the command execution Terminator delimiter $$
Creates a function that returns a random string of a specified length

The CREATE function rand_string (n INT) 
returns varchar (255) #该函数会返回一个字符串
begin 
#chars_str定义一个变量 Chars_str, Type is varchar (100), default value ' abcdefghijklmnopqrstuvwxyzabcdefjhijklmnopqrstuvwxyz ';
DECLARE chars_str varchar () default
' abcdefghijklmnopqrstuvwxyzabcdefjhijklmnopqrstuvwxyz ';
DECLARE return_str varchar (255) default ";
declare i int default 0;
While I < n do 
set Return_str =concat (return_str,substring (Chars_str,floor (1+rand () *52), 1));
Set i = i + 1;
End While;
return return_str;

Create a stored procedure

CREATE PROCEDURE insert_emp (in Start int (a), in max_num int)
begin
Declare i int default 0; 
#set autocommit = 0 Set autocommit to 0
set autocommit = 0; 
Repeat
Set i = i + 1;
INSERT into EMP values ((start+i), rand_string (6), ' salesman ', 0001,curdate (), 2000,400,rand ());
Until I = Max_num end
repeat;
commit;
End 
#调用刚刚写好的函数, 1.8 million records, call
Insert_emp (100001,4000000) starting from number 100,001th;

When we have a statement execution time of more than 1 seconds, we will count to.

If the SQL of the slow query is recorded in one of our logs

By default, a low version of MySQL does not record a slow query, and you need to specify slow queries to log on when you start MySQL

Bin\mysqld.exe--safe-mode--slow-query-log [mysql5.5 can be specified in My.ini]

Bin\mysqld.exe–log-slow-queries=d:/abc.log [Low version mysql5.0 can be specified in My.ini]

The slow query log will be placed under the data directory [in the mysql5.0 version of the MySQL installation directory/data/], under mysql5.5.19 is required to view

My.ini's datadir= "C:/Documents and Settings/all users/application data/mysql/mysql Server 5.5/data/" to determine.

In mysql5.6, the default is to start a slow query, My.ini's directory is: C:\ProgramData\MySQL\MySQL Server 5.6, which has a configuration item

Slow-query-log=1

There are two ways to start a slow query for mysql5.5

Bin\mysqld.exe--safe-mode--slow-query-log

You can also configure the following in the My.ini file:

[Mysqld]
# The TCP/IP Port the MySQL Server would listen on
port=3306

Locate execution-less efficient SQL statements through a slow query log. The slow query log records all of the SQL statements that have been executed longer than the Long_query_time set.

Show variables like ' long_query_time ';
Set long_query_time=2;

Add data to the Dept table

DESC Dept;
ALTER Table Dept Add ID int PRIMARY key auto_increment;
CREATE PRIMARY KEY on dept (ID);
Create INDEX idx_dptno_dptname on dept (Deptno,dname);
INSERT into Dept (deptno,dname,loc) VALUES (1, ' Research and Development department ', ' Kang and Sheng Building, 5 floor 501 ');
INSERT into Dept (Deptno,dname,loc) VALUES (2, ' product Department ', ' Kang and Sheng Building, 5 floor, 502 ');
INSERT into Dept (Deptno,dname,loc) VALUES (3, ' Finance department ', ' Kang Sheng Building 5/F 503 '); UPDATE EMP set deptno=1 where empno=100002;

Test statement ***[The EMP table record can be 3600000, the effect is significantly slow]

SELECT * from emp where empno= (select empno from emp where ename= ' Research Department ')

If you bring the order by E.empno speed will be slower, sometimes to 1min more.

Test statement

To view the slow query log: Default is host-name-slow.log in Data directory. The lower version of MySQL needs to be configured by using--log-slow-queries[=file_name when MySQL is turned on

SQL statement Optimization-explain analysis problem

Explain SELECT * from emp where ename= "Wsrcla"

will produce the following information:

Select_type: Represents the type of query.

Table: Tables for output result sets

Type: Represents the connection type of a table

Possible_keys: An index that may be used to indicate a query

Key: Represents the actual index used

Key_len: Length of index field

Rows: Number of lines scanned (estimated number of rows)

Extra: Description and description of implementation

Explain select * from emp where ename= ' Jkloip '

If you want to test the extra filesort can modify the statement above

Explain detailed

Id

Select identifier. This is the query serial number for SELECT

ID sample

Select_type

PRIMARY: The outermost query in a subquery

Subquery: The first select in the inner layer of the subquery, the result is not dependent on the external query

DEPENDENT subquery: The first select in the inner layer of the subquery, dependent on the external query

The second select in the Union:union statement is followed by all the Select,

Simple

Combine results in Union result Union

Table

Show this step the table name in the database that you are accessing

Type

How to access a table

All:

SELECT * from EMP \g

Full table scans are usually bad

SELECT * FROM (SELECT * from emp WHERE empno = 1) A;

System: Table has only one row (= system table). This is a special of the const join type

Const: Table has at most one matching row

Possible_keys

The index that the query can take advantage of if no index shows null

Key

Mysql selected using index from Possible_keys

Rows

Estimated number of rows in a result set

Extra

Query Detail Information

The no Tables:query statement uses the from DUAL or does not contain any FROM clause

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

Impossible WHERE noticed after reading const TABLES:MYSQL Query Optimizer

No results can be found by collecting statistics

Using temporary: Some operations must use temporary tables, common GROUP by; ORDER BY

Using Where: Without reading all the information in the table, the required data can be obtained by index only;

The above is a small set to introduce the MySQL database performance optimization One, the next article continues to introduce MySQL database performance optimization Two, I hope you continue to pay attention to the latest content of this site!