linux下操作mysql

MyISAM結構下面的data有3種檔案（.frm,MYD,MYI）分別代表表結構，資料和索引檔案。

 

【串連】

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

也可以像windows下那樣設定環境變數然後不用輸入完整的mysql路徑了，方法如下：

vi /etc/profile

OK，重啟機器生效。（這是對所有使用者都生效的）

【操作】

a）建立視圖

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）複製表

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）重新命名表

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

 

【索引】

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      |         |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

 瞭解上面的表結構和索引我們開始下面的幾種測試。

 影響查詢速度的原因是查詢影響的行數。

【a】去除索引

 先說一個使用的小技巧，如果不記得命令的使用文法，可以通過下面的方式來查詢。

mysql> ? drop indexName: 'DROP INDEX'Description:Syntax:DROP INDEX index_name ON tbl_name /*這裡就是文法，如果不記得可以這樣進行查詢*/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，現在我們去除t1表的索引username

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

首先查詢下帶主鍵索引的id列

mysql> desc select * from t1 where id=4\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: constpossible_keys: PRIMARY /*可能用到的索引*/          key: PRIMARY /*實際用到的索引*/      key_len: 4          ref: const         rows: 1 /*存在索引所以影響的行數是1行*/        Extra:1 row in set (0.00 sec)

現在來查詢下不帶索引的username列

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 /*這裡是關鍵，沒有索引，mysql則會進行全表掃描，如果資料量非常大的情況下，效率肯定是很低的*/        Extra: Using where1 row in set (0.00 sec)

【like】

like模糊比對開頭不可以使用%，如果使用了則用不到索引。

 【and，or】

or左邊和右邊必須全部加上索引，否則查詢時候不走索引，我們來看下

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 /*實際上並沒有走索引*/      key_len: NULL          ref: NULL         rows: 7 /*所以是全表掃描的*/        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 /*走索引了*/      key_len: 32,4          ref: NULL         rows: 2 /*所以影響2行*/        Extra: Using union(username,PRIMARY); Using where1 row in set (0.00 sec)

還有一點就是如果查詢的類型和原本欄位類型不相同時也是不走索引的。

【handler_read_rnd_next】

mysql> show status like "Handler_read%";+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| Handler_read_first    | 0     || Handler_read_key      | 30    || Handler_read_next     | 14    || Handler_read_prev     | 0     || Handler_read_rnd      | 0     || Handler_read_rnd_next | 268   | /*如果這個值越高則說明很多查詢語句需要用到索引，可以結合滿查詢，explain進行分析*/+-----------------------+-------+6 rows in set (0.00 sec)