How to record mysql Performance query process _ PHP Tutorial-php Tutorial

Describes how to use mysql Performance query. Everything comes from an experiment. See the following example: Table: Copy the code: CREATETABLEIFNOTEXISTS 'foo' ('A' int (10) unsignedNOTNULLAUTO_INCREMENT, 'B' int (10) u everything comes from an experiment. please refer to the following example:

Table:

The code is as follows:

Create table if not exists 'foo '(
'A' int (10) unsigned not null AUTO_INCREMENT,
'B' int (10) unsigned NOT NULL,
'C' varchar (100) not null,
Primary key ('A '),
KEY 'bar' ('B', 'A ')
) ENGINE = InnoDB;

Create table if not exists 'foo2 '(
'A' int (10) unsigned not null AUTO_INCREMENT,
'B' int (10) unsigned NOT NULL,
'C' varchar (100) not null,
Primary key ('A '),
KEY 'bar' ('B', 'A ')

) ENGINE = MyISAM;

I inserted million data into two tables (the performance difference during insertion is that InnoDB is slower than MyISAM)

The code is as follows:

$ Host = '1970. 168.100.166 ';

$ DbName = 'test ';

$ User = 'root ';

$ Password = '';

$ Db = mysql_connect ($ host, $ user, $ password) or die ('Db connect failed ');

Mysql_select_db ($ dbName, $ db );

Echo '= = '. "\ r \ n ";

$ Start = microtime (true );

Mysql_query ("SELECT SQL _NO_CACHE SQL _CALC_FOUND_ROWS * FROM foo WHERE B = 1 LIMIT 1000, 10 ");

$ End = microtime (true );

Echo $ end-$ start. "\ r \ n ";

Echo '================================ MyISAM ======================= = '. "\ r \ n ";

$ Start = microtime (true );

Mysql_query ("SELECT SQL _NO_CACHE SQL _CALC_FOUND_ROWS * FROM foo2 WHERE B = 1 LIMIT 1000, 10 ");

$ End = microtime (true );

Echo $ end-$ start. "\ r \ n ";

Returned results:

There will be so many differences in one query !! InnoDB and MyISAM, and quickly analyze why.

The first step is to use explain for viewing.

No index is used on both sides. The second query queries the rows, and the MyISAM queries the rows much less than InnoDB, but the query is slower than InnoDB !! Y is a bit strange.

Okay. there is another niuke tool profile.

Specific use can refer to: http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

In simple terms:

The code is as follows:

Mysql> set profiling = 1;

Mysql> show profiles;

Mysql> show profile for query 1;

In this data, we can see that MyISAM's Sending data is much more time-consuming than InnoDB's Sending data. View mysql documentation

Http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Sending data reads the select result from the disk and returns the result to the client. This process involves a lot of IO operations. You can use show profile cpu for query XX; to view the results. it is found that the CPU_system of MyISAM is much larger than that of InnnoDB. At this point, we can conclude that MyISAM is slower than InnoDB for table queries (the difference is that only indexes can be used for queries.

Example TABLE: Code: create table if not exists 'foo' ('A' int (10) unsigned not null AUTO_INCREMENT, 'B' int (10) u...