MySQL performance optimization common commands

MySQL performance optimization common commands:

one: When the MySQL program is found to run slowly, after troubleshooting the SQL host problem, you can try to schema,table, and SQL to further examine;

1:mysql> Show full processlist;

+----+------+-----------+------+---------+------+-------+-----------------------+-----------+---------------+

| Id | User | Host | db | Command | Time | State | Info | rows_sent | rows_examined |
+----+------+-----------+------+---------+------+-------+-----------------------+-----------+---------------+
| 52 | Root | localhost | Test | Query | 0 | init | Show Full Processlist | 0 | 0 |
+----+------+-----------+------+---------+------+-------+-----------------------+-----------+---------------+
1 row in Set (0.00 sec)

This command is used to view the links currently connected to the MySQL server, as well as what actions are being done by DML,DDL;

2: Identify time-consuming query statements: (Show status for database run-time statistics, show variables for querying database settings)

Mysql> Show status like ' slow_queries ';//Query the number of slow queries;

Mysql> Show variables like ' long_query_time ';//view the time for slow query settings, default 10s

mysql> Set long_query_time = 1;// setting slow query time

3: Generate a Hop execution plan for SQL statements;

Mysql> Explain select * from Userinfo\g;
1. Row ***************************
Id:1
Select_type:simple
Table:userinfo
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
Rows:5
Extra:null
1 row in Set (0.00 sec)

mysql> explain select * from userinfo where usersn = 1;

+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | Simple | UserInfo | Const | PRIMARY | PRIMARY | 4 | Const | 1 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in Set (0.00 sec)

4: Query the statement when the table is built:

Mysql> Show CREATE TABLE userinfo\g;
1. Row ***************************
Table:userinfo
Create table:create Table ' userinfo ' (
' USERSN ' int (ten) unsigned not NULL auto_increment,
' Usernick ' varchar () not NULL,
' Lastlogintime ' timestamp not NULL the DEFAULT current_timestamp on UPDATE current_timestamp,
PRIMARY KEY (' USERSN ')
) Engine=innodb auto_increment=6 DEFAULT charset=latin1
1 row in Set (0.00 sec)

5: View the status of the table:

Mysql> Show Table status like ' UserInfo ' \g;
1. Row ***************************
Name:userinfo
Engine:innodb
Version:10
Row_format:compact
Rows:5
avg_row_length:3276
data_length:16384
max_data_length:0
index_length:0
data_free:0
Auto_increment:6
Create_time:2015-05-15 15:36:02
Update_time:null
Check_time:null
Collation:latin1_swedish_ci
Checksum:null
Create_options:
Comment:
1 row in Set (0.00 sec)

Two

Summarize some of the analysis commands:

1, explain: Explain the SQL execution plan, the SQL behind does not execute

2. Explain partitions: execution plan for viewing tables with partitions

3, explain extended: pending verification

4, show warnings:

5. Show CREATE TABLE: View the detailed creation statement of the table to facilitate the user to optimize the table

6. Show indexes: All indexes of the watch, show indexes from TABLE_NAME, can also get the same information from the Information_schema.statistics table. The cardinality column is important to represent the amount of data.

7. Show Tables Status: View the underlying size of the database table and the table structure, as well as get information from the underlying table from the Information_schema.tables table.

8. Show [Global|session]status: You can view the current internal state information of the MySQL server. Various metrics that can help to load the MySQL server. The default is session. With Information_schema.global_status and Information_schema.session_status

9. Show [global|session] variables: View the values of the current MySQL system variables, some of which can affect how SQL statements are executed. with Information_schema.global_variables and information_schema.session_variables;

10. Information_schema: The number of tables included is related to the version of MySQL.

-------------------------

Third, index

1. Data integrity: Ensure data uniqueness through primary key and unique key

Primary key (primary key): Each table can have only one, the primary key is not NULL, and the auto_increment column is defined, then this column must be part of the primary key.

Unique key: Multiple unique keys can exist in a table, each key can be null, i.e. null! =null

2. Index Terms:

Indexing technology:

Index implementation:

Index Type:

3. Create a single-column index: It is important to note that multiple indexes can be created on a single column, but this results in a performance overhead.

ALTER TABLE table_name ADD primary Key|index index_name (coumn_name);

4, when there are multiple indexes, how to determine which index to use more efficient?

The more unique values are compared by the unique values in the index and the total number of rows in the index, the more the query results are obtained with fewer reads when using this index.

Disable optimizer settings: SET @ @session. optimizer_switch= ' Index_merge_intersection=off ';

5, in the like query (%oo) will not go index

6, function index is not supported. In addition to using functions on the index, the execution plan does not go through the index.

7. Unique index: Provide data integrity, ensure that any value in the column appears once, tell the optimizer, the given record at most only one result returned, avoid the additional index scan, whether an additional index scan, you can use the following statement to view:

Flush status;

Show session status like ' Handler_read_next ';

Select name from student where Name= ' Randy ';

Show session status like ' Handler_read_next ';

8. Sort results: If you do not sort by indexed fields, MySQL uses the internal file sort algorithm to sort the returned rows in the specified order. Sorting by using indexed fields will eliminate the categorization process.

9. Combined index: The order in which indexes are executed is determined by the cardinality of each field in the combined index to determine which combination index to use. Sometimes it is better to perform more efficiently by swapping the order of the combined index columns. The leftmost column of the primary possibly combined index.

The combined index column is not too wide, ref indicates that several combined index fields are used, and Key_len represents the length of the indexed field

10. Query prompt:

Use query hints to modify the execution plan of the query.

Straight_join: Enforces link operations in the order in which they are associated, regardless of whether the execution plan is optimal.

11. Index Hint:

Index hints are used by the tables in the link to define the use, ignore, and force class tables for each table

use [Index|key] [for (join|order_by|group_by)] [index_list]--allows the optimizer to prefer the specified index scan

Ignore [Index|key] [for (join|order_by|group_by)] [index_list]

Force [Index|key] [for (join|order_by|group_by)] [index_list]--let optimizer prefer index scan instead of full table scan

12. Impact of index DML operations: Performance that affects write operations. repeating indexes,

13. Use Show Index_staistics to view the usage of the index to determine which indexes are not being used. There is no index footprint used, which affects write performance,

14. DDL: The table is locked when the table index is modified. Affects the size of the storage space.

15. Coverage Index :

MySQL performance optimization common commands