Common SQL statements for database optimization in MySQL (summary sharing)

This article brings you relevant knowledge about mysql. It mainly summarizes some common sql statements for database optimization in MySQL. The sample code in the article is explained in detail. Let’s take a look. I hope it helps everyone.

Recommended learning: mysql video tutorial

1.SHOW ENGINES

View execution engine and default engine.

2.SHOW PROCESSLIST

SHOW PROCESSLIST is very useful to view the usage of the current database connection and various status information. SHOW PROCESSLIST; Only the first 100 items are listed. If you want to list all, please use SHOW FULL PROCESSLIST;

Attribute columns and meanings:

id	An identifier, very useful when you want to kill a statement.
user	Display the current user. If you are not root, this command will only display the sql statements within your authority.
host	Shows which IP and which port this statement is sent from. Can be used to track the user who posted the problematic statement.
db	Displays which database this process is currently connected to.
command	Displays the executed command of the current connection, usually sleep, query, and connect.

state column and its meaning, the status listed by mysql:

Checking table	is Check the datasheet (this is automatic).
Closing tables	The modified data in the table is being flushed to disk, and the exhausted table is being closed. This is a quick operation, but if this is not the case, you should verify that the disk space is full or that the disk is under heavy load.
Connect Out	The replication slave server is connecting to the master server.
Copying to tmp table on disk	Since the temporary result set is larger than tmp_table_size (default 16M), the temporary table is being converted from memory storage to disk storage to save memory. .
Creating tmp table	A temporary table is being created to store part of the query results.
deleting from main table	The server is performing the first part of a multi-table delete and has just deleted the first table.

3.SHOW STATUS LIKE 'InnoDB_row_lock%'

InnoDB's row-level lock status variable.

InnoDB's row-level lock status variable not only records the number of lock waits, but also records the total lock duration, the average duration each time, and the maximum duration. In addition, there is a non- The cumulative status amount shows the number of waits currently waiting for a lock. The description of each status quantity is as follows:

• InnoDB_row_lock_current_waits: the number of locks currently waiting for;
• InnoDB_row_lock_time: the total lock time from system startup to now;
• InnoDB_row_lock_time_avg: the average time spent waiting each time;
• InnoDB_row_lock_time_max: the time spent waiting for the most common time from system startup to now;
• InnoDB_row_lock_waits: the total number of waits from system startup to now ;

For these five status variables, the more important ones are InnoDB_row_lock_time_avg (average waiting time), InnoDB_row_lock_waits (total number of waiting times) and InnoDB_row_lock_time (total waiting time). Especially when the number of waits is high and the length of each wait is not small, we need to analyze why there are so many waits in the system, and then start specifying an optimization plan based on the analysis results.

If you find that the lock contention is serious, such as the values ​​of InnoDB_row_lock_waits and InnoDB_row_lock_time_avg are relatively high, you can also set InnoDB Monitors to further observe the tables and data rows where lock conflicts occur, and analyze the reasons for the lock contention.

4.SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS command will output a lot of information currently monitored by the InnoDB monitor. Its output is a single string, without rows and columns, and the content It is divided into many small sections, each section corresponding to information about different parts of the innodb storage engine. Some of the information is very useful for innodb developers.

There is a section LATEST DETECTED DEADLOCK, which is the last recorded deadlock information, as shown in the following case:

##"(1) TRANSACTION" is displayed Information about the first transaction;
• "(1) WAITING FOR THIS LOCK TO BE GRANTED" displays the lock information that the first transaction is waiting for
• "(2) TRANSACTION" displays the second Transaction information;
• “(2) HOLDS THE LOCK(S)” displays the lock information held by the second transaction;
• “(2) WAITING FOR THIS LOCK TO BE GRANTED" displays the lock information waiting for the second transaction
• The last line indicates the processing result, such as "WE ROLL BACK TRANSACTION (2), indicating that the second transaction was rolled back.

5.SHOW INDEXS

SHOW INDEXS queries the index information in a table: SHOW INDEXES FROM table_name;

The sql to create the table is as follows:

CREATE TABLE contacts(
    contact_id INT AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL comment 'first name',
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    PRIMARY KEY(contact_id),
    UNIQUE(email),
    INDEX phone(phone) ,
    INDEX names(first_name, last_name) comment 'By first name and/or last name'
);

The stored procedure inserts fifty thousand Piece of data:

CREATE PROCEDURE zqtest ( ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	DECLARE
		j VARCHAR ( 100 ) DEFAULT 'first_name';
	DECLARE
		k VARCHAR ( 100 ) DEFAULT 'last_name';
	DECLARE
		l VARCHAR ( 100 ) DEFAULT 'email';
	DECLARE
		m VARCHAR ( 20 ) DEFAULT '11111111111';
	
	SET i = 0;
	START TRANSACTION;
	WHILE
			i < 50000 DO
		IF
			MOD ( i, 100 ) = 0 THEN
				
				SET j = CONCAT( &#39;first_name&#39;, i );
			
		END IF;
		IF
			MOD ( i, 200 ) = 0 THEN
				
				SET k = CONCAT( &#39;last_name&#39;, i );
			
		END IF;
		IF
			MOD ( i, 50 ) = 0 THEN
				
				SET m = CONCAT( &#39;&#39;, CAST( m as UNSIGNED) + i );
			
		END IF;
		INSERT INTO contacts ( first_name, last_name, email, phone )
		VALUES
			( j, k, CONCAT(l,i), m );
		
		SET i = i + 1;
		
	END WHILE;
	COMMIT;
	
END;

Use show index from contacts; and the result is as follows:

Field description:

TableTable nameNon_uniqueThe unique index is 0, and other indexes are 1. The primary key index is also the only index.Key_nameIndex name. If the names are the same, it means it is the same index and it is a joint index. Each row represents a column in the joint index.Seq_in_indexThe column sequence number in the index, starting from 1. It can also indicate the order of the column in the joint index. Column_nameIndex column name, if it is a joint index, it is the name of a certain columnCollationHow the column is stored in the index, It probably means character order. CardinalityThe number of different values ​​on an index is called "cardinality", also known as distinction degree, the larger the base, the better the index distinction. The statistics of this value are not necessarily accurate and can be corrected using ANALYZE TABLE. Sub_part prefix index. If the column is only partially indexed, the number of characters indexed. NULL if the entire column's values ​​are indexed. PackedHow keywords are compressed. NULL if not compressed. Compression generally includes compression transport protocols, compressed column solutions and compressed table solutions. NullIf the column value can contain null, YESIndex_typeIndex structure Types, common ones include FULLTEXT, HASH, BTREE, RTREEComment, Index_commentComments

6.ALTER TABLE xx ENGINE = INNODB

Rebuild the table, including the index structure. Can eliminate index page splits and disk fragmentation left when data is deleted.

7.ANALYZE TABLE

It does not rebuild the table, it just re-counts the index information of the table without modifying the data. An MDL read lock is added in this process. It can be used to correct the situation where the Cardinality of the statistical index in show index from tablename; is an abnormal data.

Recommended learning: mysql video tutorial

The above is the detailed content of Common SQL statements for database optimization in MySQL (summary sharing). For more information, please follow other related articles on the PHP Chinese website!