Mysql Periodic analysis check and optimize the table method summary _mysql

Periodic analysis Table

ANALYZE [Local | No_write_to_binlog] TABLE tbl_name [, Tbl_name]

Use this statement to parse and store the keyword distribution for a table. During profiling, a table is locked with a read lock. This is useful for MyISAM, BDB and InnoDB tables. For MyISAM tables, this statement is equivalent to the use of myisamchk-a.

MySQL uses the stored keyword distribution to determine the order in which the tables are federated when you perform a union on objects other than constants.

mysql> Analyze Table A;
+--------+---------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+-----------------------------+
| TEST.A | Analyze | Status | Table is already up to date |
+--------+---------+----------+-----------------------------+
1 row in Set (0.00 sec)

Periodic checklist

CHECK TABLE tbl_name [, tbl_name] [option]

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

mysql> Check Table A;
+--------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+----------+
| TEST.A | Check | Status | OK |
+--------+-------+----------+----------+
1 row in Set (0.00 sec)
Check table can also check for errors in the view, such as a table that is referenced in the view definition does not already exist.
We create a view for table A above

Mysql> CREATE VIEW A_view as SELECT * from A;
Query OK, 0 rows affected (0.02 sec)

Then check the view and see that there is no problem

mysql> Check table A_view;
+-------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| Test.a_view | Check | Status | OK |
+-------------+-------+----------+----------+
1 row in Set (0.00 sec)
Now delete the tables that depend on the view

mysql> drop table A;
Query OK, 0 rows affected (0.01 sec)
Check the view just now and find the 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 right s to use them
3. Row ***************************
Table:test.a_view
Op:check
Msg_type:error
Msg_text:corrupt
3 Rows in Set (0.00 sec)

ERROR:
No query specified
Regular tuning Table

OPTIMIZE [Local | No_write_to_binlog] Table tbl_name [, Tbl_name]
If you have deleted a large portion of the table, or if you have made many changes to a table containing a variable-length row (a table with a varchar, blob, or text column) , you should use optimize TABLE. The deleted record is kept in the linked list, and subsequent inserts reuse the old record location. You can use the Optimize table to reuse unused space and defragment the data file.
In most settings, you do not need to run optimize TABLE at all. Even if you have a large number of updates to a variable-length row, you don't need to run it frequently, once a week or once a month, and only run on a specific table. The
OPTIMIZE table works only for MyISAM, BDB, and InnoDB tables.
for MyISAM tables, OPTIMIZE table works as follows:
If the table has been deleted or exploded, the table is repaired.
If index pages are not categorized, they are categorized.
If the table's statistics are not updated (and repair is not possible by classifying the index), update.

Mysql> OPTIMIZE Table A;
+--------+----------+----------+-----------------------------+
| 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 directly excerpted from MySQL's Chinese manual, with detailed access to MySQL's help manual, which simply points out several ways of regular optimization, which should be noted whether the Analyze,check or optimize will lock the table during execution. Therefore, be aware that these operations are performed when the database is not busy

****
Reference
"MySQL 5.1 Reference Manual"

by Chen in Zhe

Show Table Status
MySQL Official document in

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

Rows in this row are the number of rows in the table, but are actually not allowed. The MyISAM is quasi, and the other storage engines are not allowed. The exact number of rows will need to be obtained using count (*).

MySQL performs bulk deletion
when performing mass deletion, be aware of using the limit

Because if you don't use limit, deleting a lot of data is likely to cause deadlocks.

If delete's where statement is not on the index, you can first Shing the key and then delete the database based on the primary key

PS: usually update and delete when it is best to add limit to prevent misoperation

Optimize, Analyze, check, repair maintenance operations

Optimize data in the INSERT, UPDATE, delete the inevitable some data migration, paging, then there are some fragments, over time debris accumulated to affect performance, which requires the DBA regularly optimize the database to reduce fragmentation, this is through the optimize command.

such as the MyISAM table operation: Optimize table name

For the InnoDB table, the optimize operation is not supported, otherwise the prompt "table does not support optimize, doing recreate + analyze instead", of course, can also be ordered: ALTER TABLE one Type=innodb; To replace.

Analyze is used to analyze and store the distribution of the key words of the table, so that the system can obtain accurate statistics and affect the generation of the execution plan of SQL. Tables that do not have a basic change in data do not need to be analyzed frequently. However, if the data volume of the table changes significantly, and the user feels that the actual execution plan differs from the expected execution plan, performing a table analysis may help produce the expected execution plan.

Analyze Table Name

Check checks to see if there are errors in the table or view, which is useful for MyISAM and InnoDB storage engine tables. Table checking for tables in the MyISAM storage engine also updates keyword statistics

Repair optimize need to have enough hard disk space, otherwise it may damage the table, resulting in the inability to operate, it is necessary to use the Repair, note InnoDB does not support Repair operation
Generate the ID of the disorder
Method:

Use preset tables

Like ID and toid mappings.

Where the ID is fixed and the toid is random.

Then record a pointer value in Redis or memcache, pointing to the ID

When you want to get a new toid, take out the pointer value, plus 1, and then go to the preset table to get toid

Queries and Indexes
you must consider how to hit the index when querying

For example, there are several small strokes:

1 do not use an expression in an indexed column

Where MyCol *2 < 4

2 Do not use wildcard characters at the beginning of like mode

Where col_name like '%string% '

As

Where col_name like ' string% '

3 Avoid excessive use of MySQL automatic conversion type, may not use the index

Like what

SELECT * FROM Mytbl where str_col=4

But Str_col is a string, and there's actually a string change implied here

should use

SELECT * from Mytbl where str_col= ' 4 '

is the index larger than the table and you don't need to index it?
Whether

Indexes are arranged in order. So even if the index is larger than the table, it can speed up the query.

Of course, if the index is larger than the table, the first task must be to check if there is a problem with the index establishment

How char and varchar choose
Char is fixed length, varchar becomes longer
In addition to setting the data, varchar uses 12 bytes to define the actual length of the data.

Char fills the string above the vacant line in the back

MyISAM recommends using Char. There is a concept of a static table in the MyISAM. Using char is much more efficient than using varchar queries.

InnoDB recommends using varchar. Mainly from the space-saving aspects of the consideration

Multiple timestamp Set default values
There can be at most one field set in a table Current_timestamp

For the following requirements:

In a table, there are two fields, Createtime and UpdateTime.

1 when the INSERT, the SQL two fields are not set, will be set to the current time
2 when update, the two fields in SQL are not set, UpdateTime will become more current time

This kind of demand is not to be done. Because you can't avoid setting the Current_timestamp on two fields

There are several solutions:

1 use triggers.

2 Setting the default of the first timestamp to 0

3 Honestly use the timestamp in the SQL statement.

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

Query datasheet How many rows, how much capacity
Do not use SELECT COUNT (*)

Use Show table status like ' table_name ' but InnoDB's words will float around 50%, which is a preliminary estimate.

Settings for Auto_increment

1 do not set to int, please set to unsinged the range of int,auto_increment is determined according to the type
2 auto_increment data columns must be indexed and guaranteed to be unique.
3 auto_increment must have not NULL attribute
4 auto_increment can be used

UPDATE table SET seq = last_insert_id (seq-1)

What type of MySQL's field for representing time
indicates that time can be used using timestamp and datetime

DateTime represents the time from 0000-00-00:00:00 to 9999-12-31:00:00:00

Timestamp the time is 1970-01-01 08:00:01 to 2038-01-19 11:14:07

Timestamp occupies less space than datetime, and can set the time zone and other functions, so can use timestamp place to use timestamp

You can also use timestamp to set

[on UPDATE Current_timestamp]

[DEFAULT Current_timestamp]

MyISAM and InnoDB support foreign keys
MyISAM does not support foreign keys, INNODB support;

If you use the command to create a foreign key to manipulate the MyISAM table, the operation does not return a failure, but no foreign key association is established.

Add and Subtract statements to a field
Often there is a need for a field plus and minus will be used

Update table Set a = A+1

This is right.

But if you set this:

Select a From table

After taking out the data, a is 1.

Update table Set a =2

This can cause the final setting to fail if there is another transaction operation between select and update that modifies this field.