Summary of regular MySQL analysis and table Optimization Methods

Periodic analysis table

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]

This statement is used to analyze and store the table's keyword distribution. During the analysis, a read lock is used to lock the table. This applies to MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to myisamchk-.

MySQL uses the distribution of stored keywords to determine the order in which tables are joined when you perform union on objects other than constants.

Mysql> analyze table;
+ -------- + --------- + ---------- + ----------------------------- +
| Table | Op | Msg_type | Msg_text |
+ -------- + --------- + ---------- + ----------------------------- +
| Test. a | analyze | status | Table is already up to date |
+ -------- + --------- + ---------- + ----------------------------- +
1 row in set (0.00 sec)

Regular Checklist

Check table tbl_name [, tbl_name] [option]

Option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
Check whether one or more tables have errors. Check table is useful for MyISAM and InnoDB tables. For MyISAM tables, keyword statistics are updated.

Mysql> check table;
+ -------- + ------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ -------- + ------- + ---------- +
| Test. a | check | status | OK |
+ -------- + ------- + ---------- +
1 row in set (0.00 sec)
Check table can also CHECK whether the view has an error. For example, the referenced TABLE in the view definition does not exist.
We create a view for table a above.

Mysql> create view a_view as select * from;
Query OK, 0 rows affected (0.02 sec)

CHECK the view and CHECK whether the view is correct.

Mysql> check table a_view;
+ ------------- + ------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ ------------- + ------- + ---------- +
| Test. a_view | check | status | OK |
+ ------------- + ------- + ---------- +
1 row in set (0.00 sec)
Delete the table on which the view depends

Mysql> drop table;
Query OK, 0 rows affected (0.01 sec)
CHECK the view and find an error.

Mysql> check table a_view \ G;
* *************************** 1. row ***************************
Table: test. a_view
Op: check
Msg_type: Error
Msg_text: Table 'test. A' doesn' t exist
* *************************** 2. row ***************************
Table: test. a_view
Op: check
Msg_type: Error
Msg_text: View 'test. a_view' references invalid table (s) or column (s) or function (s) or definer/invoker of view lack rights to use them
* *************************** 3. row ***************************
Table: test. a_view
Op: check
Msg_type: error
Msg_text: upt
3 rows in set (0.00 sec)

ERROR:
No query specified
Regular optimization table

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
If you have deleted a majority of the tables, or if you have made many changes to the tables that contain variable-length rows (tables that contain VARCHAR, BLOB, or TEXT columns, optimize table should be used. The deleted records are kept in the Link List. Subsequent INSERT operations will reuse the old record location. You can use optimize table to reuse unused space and organize data file fragments.
In most settings, you do not need to run optimize table. Even if you have made a large number of updates to a variable-length row, you do not need to run it frequently, once a week or once a month, and only run on a specific table.
Optimize table only applies to MyISAM, BDB, and InnoDB tables.
For MyISAM tables, OPTIMIZE tables are operated as follows:
If the table has been deleted or broken down, the table is repaired.
If the index page is not classified, the page is classified.
If the statistical data of the table is not updated (and cannot be repaired by classifying the index), update the table.

Mysql> OPTIMIZE table;
+ -------- + ---------- + ----------------------------- +
| Table | Op | Msg_type | Msg_text |
+ -------- + ---------- + ----------------------------- +
| Test. a | optimize | status | Table is already up to date |
+ -------- + ---------- + ----------------------------- +
1 row in set (0.00 sec)

****
Some of the above paragraphs are taken directly from the MySQL Chinese manual. For details, you can directly view the MySQL help manual. Here we just briefly point out several regular optimization methods. Note that whether it is ANALYZE, CHECK or OPTIMIZE will lock the table during execution, so please note that these operations should be executed when the database is not busy

****
Reference
MySQL 5.1 Reference Manual

By Chen Yu

Show table status
The official mysql documentation is available

Http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Here, the rows row is the number of rows in the table, but it is actually inaccurate. Myisam is accurate, and other storage engines are inaccurate. You need to use count (*) to obtain the correct number of rows.

Mysql executes batch Deletion
Use limit When performing mass deletion.

If limit is not used, deleting a large amount of data may lead to deadlocks.

If the where statement of the delete statement is not indexed, You can first find the primary key and then delete the database based on the primary key.

Ps: it is best to add limit 1 in the update and delete operations to prevent misoperation.

Maintenance operations for optimize, Analyze, check, and repair

When optimize data is being inserted, updated, and deleted, it is inevitable that some data will be migrated by page. Some fragments will appear later, and the accumulation of fragments will affect performance over time, this requires the DBA to regularly optimize the database to reduce fragments, and the optimize command is used.

For example, for MyisAM tables: optimize table name

The optimize operation is not supported for InnoDB tables. Otherwise, the prompt "Table does not support optimize, doing recreate + analyze instead" is displayed. You can also run the command: alter table one type = innodb;.

Analyze is used to Analyze and store the distribution of table keywords, so that the system can obtain accurate statistics and affect the generation of SQL Execution plans. For tables with basically no data changes, you do not need to perform regular table analysis. However, if the data volume of a table changes significantly, you may find that executing a table analysis may help to generate an expected execution plan when the actual execution plan is different from the Expected One.

Analyze table name

Check whether there is an error in the checklist or view, which is useful for tables of the MyISAM and InnoDB Storage engines. Checks the tables of the MyISAM storage engine, and updates the keyword statistics at the same time.

Repair optimize requires sufficient hard disk space. Otherwise, the table may be damaged and cannot be operated. Use repair. Note that INNODB does not support repair.
Generate a disordered id
Method:

Use a preset table

For example, id and toid ing

The id is fixed, and the toid is random.

Record a pointer value in redis or memcache, pointing to the id

When you want to obtain a new toid, retrieve the pointer value, add 1, and then obtain the toid from the preset table.

Query and Index
When querying, you must consider how to hit the index.

For example, there are several tips:

1. Do not use expressions in index Columns

Where mycol * 2 <4

2. Do not use the wildcard % at the beginning of the like mode.

Where col_name like '% string %'

Worse

Where col_name like 'string %'

3. Avoid using mysql to convert data types too much, and index may not be used.

For example

Select * from mytbl where str_col = 4

But str_col is a string, which actually implies a string change.

Should be used

Select * from mytbl where str_col = '4'

Do I not need to create an index if the index is bigger than the table?
No

Indexes are arranged in order. Therefore, even if the index is larger than the table, the query speed can be accelerated.

Of course, if the index is bigger than the table, the first task must be to check whether there is a problem with the index creation.

How to Select Char and varchar
Char is fixed length, varchar is Variable Length
In addition to setting data, varchar also uses one or two bytes to define the actual data length.

Char will fill in the character string above the blank line

Char is recommended for myisam. Myisam has the concept of a static table. Char is much more efficient than varchar.

We recommend that you use varchar for innodb. Mainly from the space saving aspect

Set default values for multiple TimeStamp
A table can have at most one field set CURRENT_TIMESTAMP

For the following requirements:

A table has two fields: createtime and updatetime.

1. When an insert operation is performed, if neither of the SQL fields is set, it is set to the current time.
2. When update is performed, if neither of the SQL fields is set, updatetime changes to the current time.

Such requirements cannot be met. Because you cannot avoid setting CURRENT_TIMESTAMP on two fields

There are several solutions:

1. Use a trigger.

2. Set the default value of the first timestamp to 0.

3. Use timestamps in SQL statements.

Http://www.jb51.net/article/31872.htm

Queries the number of rows and capacity of a data table.
Do not use select count (*)

Use show table status like 'table _ name', but innodb will fluctuate around 50%, which is an estimated value.

AUTO_INCREMENT settings

1. Do not set it to int. Set it to unsinged int. The auto_increment range is determined based on the type.
2 The auto_increment data column must have an index and be unique.
3 auto_increment must have the not null attribute
4 auto_increment can be used

UPDATE table SET seq = LAST_INSERT_ID (seq-1)

What type of time field is used for mysql?
Timestamp and datetime can be used for time.

Datetime indicates the time range from 0000-00-00:00:00 to 9999-12-31: 00: 00: 00.

Timestamp indicates the time range from 08:00:01 to 11:14:07.

Timestamp occupies less space than datetime, And you can set time zone and other functions. Therefore, try to use timestamp wherever possible.

You can also set timestamp

[On update CURRENT_TIMESTAMP]

[DEFAULT CURRENT_TIMESTAMP]

Myisam and innodb support foreign keys
Myisam does not support foreign keys, and innodb does;

If you use the command to create a foreign key to operate on the myisam table, the operation will not return a failure, but it is created without a foreign key Association.

Add or subtract a field
It is often necessary to add or subtract a field.

Update table set a = a + 1

This is correct.

However, if this is set:

Select a from table

After data is retrieved, a is 1.

Update table set a = 2

In this case, if other transaction operations between select and update modify this field, the final setting may fail.