Home » MySQL » MySQL commonly used linux command (3)

MySQL commonly used linux command (3)

1, delete the students data table in the student_course database:

RM -f student_course/students.*

2, backing up the database: (backing up the database test)

Mysqldump, -u, root, -p, test>c:test.txt,

Backup form: (backup the mytable table under the test database)

Mysqldump, -u, root, -p, test, mytable>c:test.txt,

Import backup data into the database: (back to the test database)

Mysql, -u, root, -p, test

3, create temporary tables: (create temporary table Zengchao)

Create, temporary, table, Zengchao (name, varchar (10));

4, creating tables is the first to determine whether the table has

Create, table, if, not, exists, students (...).

5, copy the structure of the table from the existing table

Create, table, table2, select * from, table1, where, 1<>1,

6, copy table

Create, table, table2, select * from, table1;

7 rename the table

Alter, table, table1, rename, as, table2,

8, modify the column type

Alter table table1 modify id int unsigned; / / modify the type of the column ID of int unsigned

Alter table table1 change ID Sid int unsigned ID; / / modify the column name for SID, but also to modify the properties of int unsigned

9, create index

Alter, table, table1, add, index, ind_id (ID);

Create, index, ind_id, on, table1 (ID);

Create unique index ind_id on table1 (ID); / / create unique index

10, delete index

Drop, index, idx_id, on, table1;

Alter, table, table1, drop, index, ind_id,

11, join characters, or multiple columns (column ID and ':' and column 'name' = '=')

Select, concat (ID, ':', name, '='), from, students,

12, limit (select 10 to 20). The first record set is numbered 0>

Select *, from, students, order, by, ID, limit, 9,10,

13, MySQL does not support the function

Transactions, views, foreign keys and reference integrity, stored procedures, and triggers

14, MySQL uses the index operator

< <, > =,, > =,, between, in, or _% without opening like

15 uses the disadvantages of index

1) slow down the rate of additions and deletions to data;

2) take up disk space;

3) increase the burden of query optimizer;

When the query optimizer generates the execution plan, the index is considered, and too many indexes increase the workload of the query optimizer, leading to the lack of an optimal query scheme;

16, analysis index efficiency

Methods: add explain to the general SQL statement;

The implications of the analysis are:

1) table: table name;

2) type: the type of connection, (ALL/Range/Ref) where ref is the most ideal;

3) possible_keys: query the index name that can be used;

4) key: the actual index used;

5) key_len: the length of the part used in the index (bytes);

6) Ref: display column name or const (do not understand what);

7) rows: displays the number of rows that MySQL will have to scan before finding the correct result;

8) extra:MySQL's suggestion;

17 uses a shorter fixed length column,

1) use a shorter data type as much as possible;

2) use the fixed length data type as much as possible;

A) instead of varchar, fixed length data processing is faster than char;

(b) for frequently modified tables, the disk is prone to fragmentation, thus affecting the overall performance of the database;

C) in case of data table collapse, using fixed length data rows of the table are more likely to use data structures. For fixed length, each recording start position is fixed record length ratio, can easily be detected, but the data for the use of variable length may not be
(d) for data tables of type MyISAM, although data columns converted to fixed length can improve performance, they occupy a large amount of space;

18, using not, null, and enum

Try to define the column as not null, which allows data to come out faster and require less space, and when querying, MySQL does not need to check for exceptions, that is, null values, thus optimizing queries;

If a column contains only a finite number of specific values, such as gender, whether valid or year of admission, in this case should be considered to convert it to the value of the enum column, faster MySQL treatment, because all of the enum value in the system is expressed in the numerical identification;

19, using optimize table

Changes to the regular table, prone to debris, to read more disk blocks in the query database, reduce the query performance. With variable length table are disk fragmentation, the problem of the BLOB data type is more prominent, because of its size change is very large. By using optimize table to defragment. Ensure that the database performance is not reduced, the effect of the optimization of debris data table. Optimize table can be used for MyISAM and BDB types of table. In fact any defragmentation method mysqldump is used to transfer data table, then use after dump file and re build data table;

20, using procedure analyse ()

You can use the procedure analyse () to display the best type of advice. It's easy to use. Add procedure analyse () to the select statement, for example:

Select * from, students, procedure, analyse ();

Select *, from, students, procedure, analyse (16256);

The second statement requires procedure analyse () not to suggest that more than 16 values are contained, or that there are more than 256 bytes of enum type, and that if there is no limit, the output can be long;

21 uses query caching

1) how the query cache works:

The first implementation of a select statement, the text content and the query results to remember the query server, stored in the cache, the next time you encounter this statement, returning directly from the cache results; when the update data table, any cache the data table query becomes invalid, and
will be discarded.
2) configure cache parameters:

Variable: query_cache _type, the query cache operation mode. There are 3 modes, 0: 1: query cache; cache, unless it begins with select sql_no_cache; 2: according to the need of caching only those with a select at the beginning of the sql_cache query; query_cache_size: set the maximum query result cache set size greater than this value will not by caching.

22, adjust the hardware

1) load more memory on the machine;

2) increase the speed of the hard disk to reduce the I/O waiting time;

The seek time is the main factor in determining performance, and the head is moved verbatim for the slowest, once the head is positioned and reads quickly from the track;

3) redistribute disk activity on different physical hard disk devices;

If possible, the busiest databases should be stored on different physical devices, which are different from different partitions using the same physical device because they will contend with the same physical resources (magnetic head).

copyright notice: please retain the article signature and link