1、MySQL索引
- MySQL索引的建立對于MySQL的高效運作是很重要的,索引可以大大提高MySQL的檢索速度。
- 打個比方,如果合理的設計并使用索引的MySQL是一輛蘭博基尼的話,那麼沒有設計和使用索引的MySQL就是一個人力三輪車。
- 索引分單列索引群組合索引。單列索引,即一個索引隻包含單個列,一個表可以有多個單列索引,但這不是組合索引。組合索引,即一個索引包含多個列。
- 建立索引時,你需要確定該索引是應用在SQL查詢語句的條件(一般作為WHERE子句的條件)。
- 實際上,索引也是一張表,該表儲存了主鍵和索引字段,并指向實體表的記錄。
- 上面都在說使用索引的好處,但是過多的使用索引将會造成濫用。是以索引也會有它的缺點:雖然索引大大提高了查詢速度,同時卻會降低更新表的速度,如對表進行INSERT、UPDATE和DELETE,因為更新表時,MySQL不僅要儲存資料,還要儲存一下索引檔案。
- 建立索引會占用磁盤空間的索引檔案。
a、普通索引
- 建立索引:這是最基本的索引,它沒有任何限制。它有一下幾種建立方式。
CREATE INDEX indexName ON mytable(username(length));
- 如果是CHAR、VARCHAR類型,length可以小于字段實際長度;如果是BLOB和TEXT類型,必須指定length。
- 修改表結構(添加索引):
ALTER table tableName ADD INDEX indexName(columnName)
- 建立表的時候直接指定:
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
- 删除索引的文法:
DROP INDEX [indexName] ON mytable;
b、唯一索引
- 他與前面的普通索引類似,不同的是:索引列的值必須是唯一的,但是允許有空值。如果是組合索引,則列值的組合必須唯一。它有以下幾種建立方式。
- 建立索引:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
- 修改表結構:
ALTER table mytable ADD UNIQUE [indexName] (username(length))
- 建立表的時候直接指定:
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
c、使用ALTER指令添加和删除索引
- 有四種方式添加資料表的索引:
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):該語句添加一個主鍵,這意味着索引值必須是唯一的,且不能為NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):這條語句建立索引的值必須是唯一的(NULL可能會出現多次)
- ALTER TABLE tbl_name ADD INDEX index_name (column_list):添加普通索引,索引值可能出現多次。
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語句指定索引為FULLTEXT,用于全文索引。
- 以下執行個體為在表中添加索引:
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
- 你還可以在ALTER指令中使用DROP子句來删除索引,嘗試以下執行個體删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
d、使用ALTER指令添加和删除主鍵
- 主鍵隻能作用于一個列中,添加主鍵索引時,你需要確定該主鍵預設不為空(NOT NULL)。執行個體如下:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
- 你也可以使用ALTER指令來删除主鍵:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
- 删除主鍵隻需要指定PRIMARY KEY,但是在删除索引時,你必須要知道索引名。
e、顯示索引資訊
- 你可以使用SHOW INDEX指令來列出表中的相關的索引資訊。可以通過添加\G來格式化輸出資訊。如:
mysql> SHOW INDEX FROM table_name; \G
2、MySQL臨時表
- MySQL臨時表在我們需要儲存一些臨時資料時是非常有用的。臨時表隻在目前連接配接中可見,當關閉連接配接時,MySQL會自動删除表并釋放所有空間。
- 臨時表在MySQL3.23版本中添加,如果你的MySQL版本低于3.23版本就無法使用MySQL的臨時表。不過現在一般很少見再使用這麼低版本的MySQL資料庫服務。
- MySQL臨時表隻在目前連接配接中可見,如果你使用PHP腳本來建立MySQL臨時表,那麼每當PHP腳本執行完成之後,該臨時表就會自動銷毀。
- 如果你使用其他MySQL用戶端程式連接配接MySQL資料庫來建立臨時表,那麼隻有在關閉用戶端程式時才會銷毀臨時表,當然你也可以手動銷毀。
- 以下執行個體展示了如何是引用MySQL臨時表的簡單執行個體。以下的SQL代碼可以适用于PHP腳本的mysql_query()函數:
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
- 當你使用SHOW TABLES指令顯示資料表清單時,你将無法看到SalesSummary表。
- 如果你退出目前MySQL會話,再使用SELECT指令來讀取原先建立的臨時表資料,那你會發現資料庫中沒有該表的存在,因為在你退出時,該臨時表已經被銷毀了。
- 删除MySQL臨時表:預設情況下,當你斷開與資料庫的連接配接後,臨時表就會自動被銷毀。當然你可以在目前MySQL會話中使用DROP TABLE指令來手動删除臨時表。
- 以下是手動删除臨時表的執行個體:
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql> SELECT * FROM SalesSummary;
ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist
3、MySQL複制表
- 如果我們需要完全的複制MySQL的資料表,包括表的結構、索引、預設值等,如果僅僅使用CREATE TABLE...SELECT指令,是無法實作的。
- 本節将向大家介紹如何使用完整的複制MySQL資料表,步驟如下:
a、使用SHOW CREATE TABLE指令擷取建立資料表(CREATE TABLE)語句,該語句包含了中繼資料表的結構,索引等。
b、複制一下指令顯示的SQL語句,修改資料表名,并執行SQL語句,通過以上指令将完成的複制資料表結構。
c、如果你想複制表的内容,你就可以使用INSERT INTO ...SELECT語句來實作。
- 執行個體:嘗試複制表runnoob_tbl。
- 步驟一:擷取資料表的完整結構:
mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
ERROR:
No query specified
- 修改SQL語句的資料表名,并執行SQL語句:
mysql> CREATE TABLE `clone_tbl` (
-> `runoob_id` int(11) NOT NULL auto_increment,
-> `runoob_title` varchar(100) NOT NULL default '',
-> `runoob_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`runoob_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)
- 步驟三:執行完第二步驟後,你将在資料庫中建立新的克隆表clone_tbl。如果你想拷貝資料表的資料你可以使用INSERT INTO ...SELECT語句來實作:
mysql> INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
- 執行以上步驟後,你将完整的複制表,包括表的結構以及表的資料。
轉載于:https://blog.51cto.com/12402717/1983248