Operate MySql in Linux

The data in the MyISAM structure contains three types of files (. frm, MYD, and myi), which respectively represent the table structure, data, and index files.

 

[Connection]

cd /usr/local/webdev/mysql/bin/mysql -uroot -proot

You can also set the environment variables in windows without entering the complete MySQL path. The method is as follows:

vi /etc/profile

OK, restart the machine to take effect. (This is effective for all users)

Operation]

A) create a view

mysql> create view v_t1 as select * from t1 where id=3;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1             || v_t1           |+----------------+2 rows in set (0.00 sec)

B) copy a table

mysql> create table t2 like t1;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+----------------+| Tables_in_test |+----------------+| t1             || t2             || v_t1           |+----------------+3 rows in set (0.00 sec)

mysql> insert into t2 select * from t1;Query OK, 7 rows affected (0.00 sec)Records: 7  Duplicates: 0  Warnings: 0

C) rename a table

mysql> rename table t2 to t1;Query OK, 0 rows affected (0.00 sec)

 

[Index]

mysql> desc t1;+----------+------------------+------+-----+---------+----------------+| Field    | Type             | Null | Key | Default | Extra          |+----------+------------------+------+-----+---------+----------------+| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(30)      | NO   | MUL |         |                |+----------+------------------+------+-----+---------+----------------+

mysql> show index from t1;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| t1    |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         || t1    |          1 | username |            1 | username    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Learn about the table structure and indexes above. Let's start the following tests.

The query speed is affected by the number of rows affected by the query.

[A] index Removal

First, let's talk about the tips. If you don't remember the syntax used by the command, you can use the following method to query.

Mysql>? Drop indexname: 'drop Index' Description: Syntax: drop index index_name on tbl_name/* The syntax is as follows, if you do not remember, you can perform this query */drop index drops the index named index_name from the table tbl_name.this statement is mapped to an alter table statement to drop the index. see [help alter table]. URL: http://dev.mysql.com/doc/refman/5.0/en/drop-index.html

OK. Now we have removed the index username of table T1.

mysql> drop index username on t1;Query OK, 7 rows affected (0.09 sec)Records: 7  Duplicates: 0  Warnings: 0

First, query the ID column with the primary key index

Mysql> DESC select * from T1 where id = 4 \ G *************************** 1. row ************************** ID: 1 select_type: simple table: T1 type: constpossible_keys: Primary/* possible indexes */key: Primary/* actually used indexes */key_len: 4 Ref: const rows: 1/* The number of rows affected by an index is 1 */extra: 1 row in SET (0.00 Sec)

Query the username column without Indexes

Mysql> DESC select * from T1 where username = "user4" \ G ************************* ** 1. row ************************** ID: 1 select_type: simple table: T1 type: allpossible_keys: NULL key: NULL key_len: NULL Ref: NULL rows: 7/* Here is the key. Without an index, MySQL performs a full table scan. If the data volume is very large, efficiency must be very low */extra: Using where1 row in SET (0.00 Sec)

[Like]

% Cannot be used at the beginning of like fuzzy match. If % is used, no index is used.

[And, or]

Or you must add indexes on both the left and right; otherwise, no indexes will be taken during the query. Let's take a look.

mysql> show index from t1;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| t1    |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+1 row in set (0.00 sec)

Mysql> explain select * from T1 where username = "user5" or ID = 4 \ G ********************* * ***** 1. row ************************** ID: 1 select_type: simple table: T1 type: allpossible_keys: primary key: NULL/* does not actually go through the Index */key_len: NULL Ref: NULL rows: 7/* so it is a full table scan */extra: using where1 row in SET (0.00 Sec)

mysql> alter table t1 add index (username);Query OK, 7 rows affected (0.01 sec)Records: 7  Duplicates: 0  Warnings: 0

Mysql> explain select * from T1 where username = "user5" or ID = 4 \ G ********************* * ***** 1. row ************************** ID: 1 select_type: simple table: T1 type: index_mergepossible_keys: primary, username key: username, primary/* indexed */key_len: 32, 4 Ref: NULL rows: 2/*, SO 2 rows are affected */extra: using Union (username, primary); Using where1 row in SET (0.00 Sec)

Another point is that if the query type is different from the original field type, no index is required.

[Handler_read_rnd_next]

Mysql> show status like "handler_read % "; + metric + ------- + | variable_name | value | + metric + ------- + | handler_read_first | 0 | handler_read_key | 30 | handler_read_next | 14 | handler_read_prev | 0 | metric | 0 | | handler_read_rnd_next | 268 |/* If the value is higher, indexes are required for many query statements, you can use full query and explain for analysis */+ --------------------- + ------- + 6 rows in SET (0.00 Sec)