MySQL statement optimization

I. General steps to optimize SQL

A) through show STATUS; commands to understand the frequency of execution of various SQL
SHOW [session| GLOBAL] STATUS;
SESSION (default) indicates the current connection
GLOBAL indicates that the database has been started since
SHOW GLOBAL STATUS like ' com_% '

b) Check the number of additions and deletions since this login
SHOW STATUS like ' com_select% '
SHOW STATUS like ' com_update% '
SHOW STATUS like ' com_insert% '
SHOW STATUS like ' com_delete% '

c) for the InnoDB of the deletion and modification
SHOW STATUS like ' innodb_rows% '

d) Number of seconds the server has been working
SHOW STATUS like ' uptime% '

e) Slow Query

See if slow queries are turned on
Show VARIABLES like '%slow% ' (slow_query_log means turn on or off)
View time definitions for slow queries
SHOW VARIABLES like '%long% '
To view the number of slow queries
SHOW STATUS like ' slow_queries '
SQL for slow queries is logged in the slow query log
Parsing SQL that runs slowly
EXPLAIN SQL
DESC SQL
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | Simple | Book | All | NULL | NULL | NULL | NULL | 3 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
Select_type: Query type
All: This is a simple single-table query
PRIMARY: This query uses the primary key
UNION: Multi-table Query
Type
All: Data obtained through full-table scan
Possible_keys: The index that may be used
Key: The index used in this session
Rows: The number of rows scanned, the smaller the query, the higher the efficiency
Extra: Description and description of the implementation

Second, the problem of indexing

A) The table data and indexes of the MyISAM storage engine are separate, each of which is a single file. The data and indexes of the INNODB storage engine are stored in the same tablespace, but can consist of multiple files.

You can index a portion of a column:

CREATE INDEX index_name on table_name (column (4))

b) Which fields are suitable for indexing

Where condition, group by condition having conditional order by condition

c) where the index will be used

For multi-column indexes, the leftmost column is used in the query condition, and the index is generally used

CREATE INDEX index_name on table_name (Company_id,moneys);

WHERE company_id=123 will use the index

WHERE moneys=22 does not use the index

If the columns in the condition before or are indexed and the subsequent columns are not indexed, then the indexes involved are not used. Only the fields before or after are indexed, and the query may use the index

If the column type is a string, but the number is treated as a condition, the index is not used

The higher the value of the handler_read_rnd_next, the less efficient the query and the index remediation should be established. SHOW STATUS like ' handler_read% '

Third, table optimization

CHECK TABLE table_name; parse the table for errors, such as when a table is deleted, dependent on his view will be invalid

OPTIMIZE TABLE table_name; If you have deleted a large part of the table, or if you have made multiple changes to a table with variable-length rows, you need to do a regular optimization. This command merges the space fragments in the table and is valid for MyISAM InnoDB

Iv. optimization of Common SQL

When the amount of data is relatively large, importing and exporting the table will consume system resources and should use infile and outfile
? Load Data View Help
LOAD DATA INFILE ' data.txt ' into TABLE db2.my_table quick Import
Example: Load data infile ' e:/sql.txt ' into table book (bookname,publisher,author,price,ptime,pic,detail);

If you have an index, you can close the index, import the data, and then open the index so that the import is faster disable keys/enable KEYS closes the non-unique index of the open table and is not valid for InnoDB
ALTER TABLE table_name DISABLE KEYS
Import data
ALTER TABLE table_name ENABLE KEYS

Turn off unique indexes to improve import efficiency (provided that data is not duplicated)
SET unique_checks=0 off Unique checksum
SET Unique_checks=1 Open Unique checksum

Auto-commit can be turned off for InnoDB tables to improve import efficiency (because each import line checks whether or not to commit)
SET autocommit=0 turn off auto-commit
SET autocommit=0 Open Auto-commit

? OutFile

SELECT * FROM table_name to OUTFILE ' file_name ' quick export

Group BY is grouped by default in ascending order by grouping column, if you do not want to sort you can use ORDER by NULL to suppress sorting

Try to avoid using subqueries and join queries instead, because subqueries do not use indexes

MySQL statement optimization