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