MySQL periodic analysis check and optimization table

Periodic analysis Table

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

This statement is used to analyze and store the keyword distribution of a table. During parsing, the table is locked with a read lock. This is useful for MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to using MYISAMCHK-A.

MySQL uses the stored keyword distribution to determine the order in which 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 to date |

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

1 row in Set (0.00 sec)

Periodic check Table

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 the MyISAM table, 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 also checks whether the view has errors, such as the table referenced in the view definition no longer exists.

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 are no problems

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 table that the view depends on

mysql> drop table A;

Query OK, 0 rows affected (0.01 sec)

Check the view just now to find out 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

Regularly optimize tables

OPTIMIZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name]

If you have deleted a large part of the table, or if you have made many changes to a table with variable-length rows (a table with varchar, blob, or text columns), you should use optimize table. The deleted records are kept in the linked list, and subsequent insert operations re-use the old record location. You can use the Optimize table to reuse unused space and defragment the data files.

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 do not need to run it frequently, once a week or once a month, only for specific tables.

OPTIMIZE table works only on MyISAM, BDB, and InnoDB tables.

For the MyISAM table, OPTIMIZE table operates as follows:

The table is repaired if the table has been deleted or the row has been exploded.

If the index pages are not categorized, they are categorized.

Updates are made if the statistics for the table are not updated (and cannot be repaired by classifying the index).

Mysql> OPTIMIZE Table A;

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

| Table | Op | Msg_type | Msg_text |

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

| TEST.A | Optimize | Status | Table is already to date |

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

1 row in Set (0.00 sec)

****

It is important to note that both Analyze,check and optimize will lock the table during execution, so be aware that these operations are performed when the database is not busy

****

Show Table Status

MySQL official documentation in

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

The rows row here is the number of tables, but is not actually allowed. MyISAM is accurate, other storage engines are not allowed. You need to use COUNT (*) to get the exact number of rows.

MySQL performs large-volume deletions

When performing large-volume deletions, be careful to use the upper limit

Because if you don't use limit, deleting large amounts of data is likely to cause deadlocks

If the WHERE statement of the delete is not on the index, you can first Brahma the key and then delete the database based on the primary key

PS: usually update and delete when it is best to add the limit to prevent accidental operation

Optimize, Analyze, check, repair maintenance operations

L optimize data in the INSERT, UPDATE, delete inevitably some data migration, paging, then there are some fragments, over time debris accumulation affect performance, which requires the DBA to periodically optimize the database to reduce fragmentation, which is through the optimize command.

For MyISAM table operation: Optimize table name

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

L Analyze is used to analyze and store the distribution of the key words of the table, so that the system obtains accurate statistics and affects the generation of SQL execution plan. It is not necessary to perform regular table analysis for tables where the data has not changed substantially. 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

L check to see if there are errors in the table or view, which is useful for MyISAM and InnoDB storage engine tables. Keyword statistics are also updated for table checking of tables in the MyISAM storage engine

L Repair optimize need to have enough hard disk space, otherwise it may destroy the table, resulting in the inability to operate, it is necessary to use the Repair, note InnoDB does not support Repair operation

Generate an ID for a disorderly order

Method:

Using preset Tables

such as the mapping of IDs and Toid

Where the ID is fixed, the toid is random.

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

To get a new toid, remove the pointer value, add 1, and then go to the preset table to get toid

Queries and Indexes

The query must take into account how to hit the index

For example, there are several small strokes:

1 do not use an expression in an indexed column

Where MyCol < 4

2 Do not use wildcards in the starting position of the like pattern%

Where col_name like '%string% '

As

Where col_name like ' string% '

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

Like what

SELECT * FROM Mytbl where str_col=4

But Str_col is a string, which in fact implies a string change.

should use

SELECT * from Mytbl where str_col= ' 4 '

The index is larger than the table, does it need to be indexed?

Whether

The 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 setting.

How char and varchar are selected

Char is fixed length, varchar becomes longer
VarChar uses 12 more bytes to define the actual length of the data, in addition to setting the data.

Char fills the string above the next free line

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

InnoDB recommends using varchar. Mainly from the aspect of space-saving considerations

Multiple timestamp setting 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 INSERT, SQL two fields are not set, will be set to the current time

2 When update occurs, neither of the two fields in SQL is set, and the updatetime is changed to a more current time

Such a demand is not to be done. Because you can't avoid setting current_timestamp on two fields

There are several workarounds:

1 Use a trigger.

2 Set the default of the first timestamp to 0

3 Honestly use timestamps in SQL statements.

Http://www.cnblogs.com/yjf512/archive/2012/11/02/2751058.html

Query data table How many rows, how much capacity

Do not use SELECT COUNT (*)

Use Show table status like ' table_name ' but the InnoDB will have about 50% floats, which is a pre-valuation

Settings for Auto_increment

1 do not set to int, set to unsinged the range of int,auto_increment is determined by type

2 auto_increment data columns must be indexed and guaranteed to be unique.

3 auto_increment must have not null property

4 auto_increment can be used

UPDATE table SET seq = last_insert_id (seq-1)

What type of MySQL field is used to represent time

Indicates that time can be used with timestamp and datetime

DateTime represents a time that can be from 0000-00-00:00:00 to 9999-12-31:00:00:00

Timestamp represents the time of 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 where possible 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 a command that creates a foreign key to myisam a table operation, the operation does not return a failure, but there is no foreign key association established.

Add a minus statement to a field

There is often a need to add or subtract a field to use

Update table Set a = A+1

This is right.

However, if you set this:

Select a From table

After extracting the data A is 1

Update table Set a =2

This can cause the final setting to fail if there are other transactional operations between select and update that modify the field.

MySQL periodic analysis check and optimization table