Mysql database performance optimization 1. mysql Performance Optimization

Mysql database performance optimization 1. mysql Performance Optimization

Today, database operations are increasingly becoming the performance bottleneck of the entire application, especially for Web applications. Concerning the database performance, this is not just something that DBAs need to worry about, but it is something that our programmers need to pay attention. When designing the database table structure and operating the database (especially the SQL statements used in table queries), we need to pay attention to the performance of data operations. Here, we will not talk about the optimization of many SQL statements, but only for the database with the most Web application MySQL.

Mysql performance optimization cannot be achieved overnight. It must be done step by step from all aspects, and the final performance will be greatly improved.

Mysql database Optimization Technology

Mysql optimization is a comprehensive technology, mainly including

• Table Design rationalization (compliant with 3NF)

• Add an appropriate index [four types: normal index, primary key index, unique index unique, full-text index]

• Table sharding Technology (horizontal and vertical)

• Read/write [write: update/delete/add] Separation

• Stored procedures [Modular programming, improving the speed]

• Mysql configuration optimization [configure the maximum concurrency my. ini and adjust the cache size]

• Mysql server hardware upgrade

• Clear unnecessary data at regular intervals, and sort fragments at regular intervals (MyISAM)

Database Optimization

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

① Database Design

② SQL statement Optimization

③ Database parameter configuration

④ Proper hardware resources and Operating Systems

In addition, using appropriate stored procedures can also improve performance.

This Order also shows the impact of these four operations on performance.

Database Table Design

A general understanding of the three paradigms is of great benefit to database design. In database design, in order to better apply the three paradigms, we must understand the three paradigms

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

1 paradigm: 1NF is an atomic constraint on attributes. attributes (columns) must be atomic and cannot be decomposed. (As long as relational databases meet 1NF requirements)

Second paradigm: 2NF is the uniqueness constraint on the record and requires that the record have a unique identifier, that is, the uniqueness of the object;

Third paradigm: 3NF is a constraint on field redundancy, which requires that the field is not redundant. No redundant database can be designed.

However, databases without redundancy may not be the best. Sometimes, to improve operational efficiency, we must lower the paradigm standard and properly retain redundant data. The specific approach is to follow the third paradigm in conceptual data model design, and to reduce the paradigm standard to be considered in physical data model design. Reducing the paradigm is to add fields and allow redundancy.

☞Database category

Relational Database: mysql/oracle/db2/informix/sysbase/SQL server

Non-Relational Database: (features: Object-oriented or set)

NoSql Database: MongoDB (document-oriented)

Here is an example of what is moderate redundancy or rational redundancy!

The above is the inappropriate redundancy because:

To improve the retrieval efficiency of student activity records, the unit names are redundant in the student activity records table. The unit information contains 500 records, and the student activities are recorded in

About 2 million data volume in a year. If the unit name field is not redundant in the student activity record table, it only contains three int fields and one timestamp field, which only occupies 16 bytes and is a small table. After a varchar (32) field is redundant, it is three times that of the original one, and so many I/O operations are involved in the retrieval. In addition, the number of records varies greatly from 500 to 2000000, resulting in 4000 Redundant records being updated for a new unit name. It can be seen that this redundancy is counterproductive.

The Price in the order table is a redundant field, because we can calculate the order Price from the order list, but this redundancy is reasonable and can also improve the query performance.

From the above two examples, we can draw a conclusion:

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

SQL statement Optimization

SQL optimization steps

1. Run the show status command to learn the execution frequency of various SQL statements.

2. Locate SQL statements with low execution efficiency-(select)

3. Analyze inefficient SQL statements through explain

4. Identify the problem and take corresponding optimization measures

-- Select statement classification SelectDml data operation language (insert update delete) dtl data thing language (commit rollback savepoint) Ddl Data Definition Language (create alter drop ..) dcl (Data Control Language) grant revoke -- common command of Show status -- query the Show session status like 'com _ % 'of this session '; // show session status like 'com _ select' -- Query global Show global status like 'com _ % '; -- grant all privileges on * to a user *. * to 'abc' @ '%'; -- why is 'abc' authorization used to indicate Username '@' indicating host, check the mysql-> user table to get the -- revoke permission revoke all on *. * from 'abc' @ '%'; -- refresh the permission [You can also leave it blank] flush privileges;

SQL statement Optimization-show Parameters

After the MySQL client is successfully connected, you can use the show [session | global] status command to provide server status information. The session indicates the statistical results of the current connection, and the global indicates the statistical results since the last time the database was started. The default value is session level.

The following example:

Show status like 'com _ % ';

Com_XXX indicates the number of times the XXX statement is executed.

NOTE: With these parameters Com_select, Com_insert, Com_update, and Com_delete, you can easily see whether the current database application is dominated by insert update or query operations, and the approximate execution ratio of various types of SQL statements.

There are also several common parameters to help you understand the basic information of the database.

Connections: number of attempts to connect to the MySQL server

Uptime: the server's working time (unit: seconds)

Slow_queries: the number of slow queries. The default value is 10 s)

show status like 'Connections'show status like 'Uptime'show status like 'Slow_queries' 

How to query the slow query time of mysql

Show variables like 'long_query_time'; 

Modify mysql slow query time

set long_query_time=2 

SQL statement Optimization-locate slow queries

The problem is: how to quickly locate slow statements from a large project (locate slow queries)

First, we know how to query the running status of the mysql database (for example, how many times does mysql run at the current time/total times?

Select/update/delete ../current connection)

To facilitate testing, we construct a large table (4 million)-> use the stored procedure to build

By default, mysql considers 10 seconds as a slow query.

Modify the slow query of mysql.

Show variables like 'long _ query_time '; // the current slow query time set long_query_time = 1; // you can modify the slow query time.

To create a large table, you must record it in a large table. Otherwise, the test result is significantly different from the actual one. Create:

Create table dept (/* Department TABLE */deptno mediumint unsigned not null default 0,/* No. */dname VARCHAR (20) not null default "", /* name */loc VARCHAR (13) not null default ""/* location */) ENGINE = MyISAM default charset = utf8; create table emp (empno mediumint unsigned not null default 0,/* No. */ename VARCHAR (20) not null default "",/* name */job VARCHAR (9) not null default "",/* Work */mgr mediumint unsigned not null default 0,/* Superior Number */hiredate date not null, /* Start time */sal DECIMAL (7,2) not null,/* salary */comm DECIMAL (7,2) not null, /* bonus */deptno mediumint unsigned not null default 0/* Department No. */) ENGINE = MyISAM default charset = utf8; create table salgrade (grade mediumint unsigned not null default 0, losal DECIMAL (17,2) not null, hisal DECIMAL (17,2) not null) ENGINE = MyISAM default charset = utf8;

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);INSERT INTO salgrade VALUES (5,3001,9999); 

To ensure that the stored procedure can be normally executed, we need to modify the command execution Terminator delimiter $
Creates a function that returns a random string of the specified length.

Create function rand_string (n INT) returns varchar (255) # This function returns a string begin # chars_str defines a variable chars_str. The type is varchar (100) and the default value is 'hangzhou '; declare chars_str varchar (100) default 'default'; 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; end

Create a stored procedure

Create procedure insert_emp (in start int (10), in max_num int (10) begindeclare I int default 0; # set autocommit = 0 set autocommit to 0 set autocommit = 0; repeatset I = I + 1; insert into emp values (start + I), rand_string (6), 'salesman', 0001, curdate (), 2000,400, rand ()); until I = max_numend repeat; commit; end # call the previously written function, with 1800000 records starting from 100001 to call insert_emp (records );

In this case, if the execution time of a statement exceeds 1 second, the statistics will be collected.

If you record slow query SQL statements to one of our logs

By default, mysql of earlier versions does not record slow queries. You must specify slow queries when starting 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 [earlier versions of mysql5.0 can be specified in my. ini]

This slow query log is stored in the data directory [mysql installation directory/data/In mysql5.0]. You need to view the log in mysql5.5.19.

My. ini datadir = "C:/Documents and Settings/All Users/Application Data/MySQL Server 5.5/Data/" to confirm.

In mysql5.6, slow query of records is started by default. The directory of my. ini is C: \ ProgramData \ MySQL Server 5.6, which has a configuration item

Slow-query-log = 1

There are two ways to start slow queries for mysql5.5

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

You can also configure it in the my. ini file:

[mysqld]# The TCP/IP Port the MySQL Server will listen onport=3306slow-query-log 

Locate SQL statements with low execution efficiency through slow query logs. The slow query log records all SQL statements whose execution time exceeds long_query_time.

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, 'r & D department ', '123456' on the 5th floor of kanghesheng building); INSERT into dept (deptno, dname, loc) values (2, 'product ', '1234568'); INSERT into dept (deptno, dname, loc) values '); UPDATE emp set deptno = 1 where empno = 100002;

* ** Test statement ** [the record of the emp table can be 3600000, and the effect is obviously slow]

Select * from emp where empno = (select empno from emp where ename = 'r & D authorization ')

If order by e. empno is attached, the speed will be slower, sometimes more than 1 min.

Test statement

select * from emp e,dept d where e.empno=100002 and e.deptno=d.deptno; 

View slow query logs: by default, the host-name-slow.log in the data DIRECTORY data. For earlier mysql versions, you must use-log-slow-queries [= file_name] When enabling mysql.

SQL statement Optimization-explain Analysis

Explain select * from emp where ename=“wsrcla”

The following information is generated:

Select_type: indicates the query type.

Table: The table of the output result set.

Type: indicates the table connection type.

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

Key: indicates the index actually used

Key_len: Index Field Length

Rows: number of rows scanned (Estimated number of rows)

Extra: Description and description of execution

Explain select * from emp where ename = 'jkloip'

If you want to test the Extra filesort, you can modify the preceding statement.

explain select * from emp order by ename\G 

Explanations

Id

SELECT identifier. This is the serial number of the SELECT query.

Id Example

SELECT * FROM emp WHERE empno = 1 and ename = (SELECT ename FROM emp WHERE empno = 100001) \G; 

Select_type

PRIMARY: the oldest query in the subquery

SUBQUERY: The first select in the subquery layer. The results do not depend on external queries.

Dependent subquery: The first select in the subquery layer, depending on external queries

In the UNION statement, all the SELECT statements after the second SELECT statement starts,

SIMPLE

UNION RESULT UNION

Table

Display the name of the table in the Database accessed in this step

Type

Table Access Method

ALL:

SELECT * FROM emp \G

Full table scan is usually not good

SELECT * FROM (SELECT * FROM emp WHERE empno = 1) a ;

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

Const: The table can have at most one matching row.

Possible_keys

The index that can be used by this query. If no index is displayed, null is displayed.

Key

Mysql selects the index from Possible_keys

Rows

Estimate the number of rows in the result set

Extra

Query details

No tables: the Query statement uses from dual or does not contain any FROM clause.

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

Impossible WHERE noticed after reading const tables: MYSQL Query Optimizer

It is impossible to collect statistical information.

Using temporary: some operations must use temporary tables, common group by; ORDER

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

The above section describes Mysql database performance optimization. I will continue to introduce mysql database performance optimization in the next article. I hope you will continue to pay attention to the latest content on this site!

Articles you may be interested in:

• MySQL Performance Optimization Path-modify the configuration file my. cnf
• Thread_cache and table_cache for MySQL performance optimization configuration parameters
• Analysis of max_connections configuration parameters for MySQL Performance Optimization
• Analysis of table_cache configuration parameters for MySQL Performance Optimization
• Rational configuration suggestions for Open_Table parameters for MySQL Performance Optimization
• Php performance optimization techniques for importing large amounts of data to mysql
• MySQL order by performance optimization example
• Mysql database performance optimization 2