The MySQL trick

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.