mysql基础

1.内存表和临时表区别

内存表，就是放在内存中的表，所使用内存的大小可通过My.cnf中的max_heap_table_size指定，如max_heap_table_size=1024M，内存表与临时表并不相同，临时表也是存放在内存中，临时表最大所需内存需要通过tmp_table_size =128M设定。当数据超过临时表的最大值设定时，自动转为磁盘表，此时因需要进行IO操作，性能会大大下降，而内存表不会，内存表满后，会提示数据满错误。

临时表和内存表都可以人工创建，但临时表更多的作用是系统自己创建后，组织数据以提升性能，如子查询，临时表在多个连接之间不能共享。

为什么是max_heap_table_size呢?

官方解释:

The MEMORY storage engine creates tables with contents that are stored in memory.Formerly, these were known as HEAP tables. MEMORY is the preferred term, althoughHEAP remains supported for backward compatibility.

Each MEMORY table is associated with one disk file. The filename begins with the table name and has an extension of .frm to indicate that it stores the table definition. 

内存表的表结构是存在磁盘上的.

实验：
临时表
mysql> create temporary table tmp1(id int not null);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp1;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+----------------------------------------------------------------------------------------------+
| tmp1   | CREATE TEMPORARY TABLE `tmp1` ( `id` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8    |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

内存表
mysql> create table tmp2(id int not null) engine=memory;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tmp2;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| tmp2   | CREATE TABLE `tmp2` (
   `id` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看出来临时表和内存表的ENGINE 不同，临时表默认的是MyISAM,而内存表是MEMORY .去数据库目录查看，发现tmp2.frm而没有tmp1表的任何文件。看来实际情况是符合官方解释的。


那么速度方面呢（即MyISAM和MEMORY之间的区别）？
实验开始：
实现手段：对基于2张千万级别的表做一些OLAP切分操作，中间表的建立使用2种不同的方式。最后把中间表的数据按照要求取出，插入到结果表中
实验目的;测试临时内存表和临时表的速度
1.中间表的建立使用Create temporary table type = heap 即 把中间表建立成临时内存表 
2.中间表直接使用Create temporary table建立 

实验结果：
临时内存表： 1小时
1 2008-09-25 11:03:48
1 2008-09-25 12:03:39
临时表：1小时17分钟
2 2008-09-25 12:25:28
2 2008-09-25 13:42:37
由此发现MEMORY比MyISAM快大概20％。
接着查找官方手册：
As indicated by the name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However,when the server shuts down, all rows stored in MEMORY tables are lost. The tables themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.
memory表在重启后,表结构仍然会保存,但是数据丢失.
默认使用hash索引,只适用于= 情况
可以看出来MEMORY确实是very fast，and very useful for creating temporary tables .把临时表和内存表放在一起使用确实会快不少 ：create table tmp2(id int not null) engine memory;

内存表的建立还有一些限制条件：
MEMORY tables cannot contain        BLOB or TEXT columns. HEAP不支持BLOB/TEXT列。    
The server needs sufficient memory to maintain all   MEMORY tables that are in use at the same time. 在同一时间需要足够的内存.
To free memory used by a MEMORY table when   you no longer require its contents, you should execute DELETE or TRUNCATE TABLE, or remove the table altogether using DROP        TABLE.为了释放内存，你应该执行DELETE FROM heap_table或DROP TABLE heap_table。

2.有一个需求:只希望表中有3000条数据,怎么实现?

创建触发器

用CT脚本定时删除.

3.alter table的内部执行过程:

ALTER TABLE通过制作原来表的一个临时副本来工作。修改在副本上施行，然后原来的表被删除并且重新命名一个新的。这样做使得所有的修改自动地转向到新表，没 有任何失败的修改。当ALTER TABLE正在执行时，原来的表可被其他客户读取。更新和写入表被延迟到新表准备好了为止。

4.mysql表示每个月第一天凌晨2点:

mysql> select date_add( date_sub(current_date(),interval  day(current_date()-1) day),interval 2 hour);
+-----------------------------------------------------------------------------------------+
| date_add( date_sub(current_date(),interval  day(current_date()-1) day),interval 2 hour) |
+-----------------------------------------------------------------------------------------+
| 2013-10-01 02:00:00                                                                     |
+-----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

5.mysql设置时区为本地时区:

 show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | PDT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

在mysqld中加入:

time-zone  =+8:00 //注意在配置文件中是中划线,不是下划线

 show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | PDT    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

6.mysql一些时间函数:

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2013-10-07     |
+----------------+
1 row in set (0.00 sec)
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2013-10-07 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:54:52  |
+-----------+
1 row in set (0.00 sec)
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:54:57       |
+----------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-10-07 15:55:04 |
+---------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2013-10-07 15:55:10 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curtimestamp();   //没有这个函数
ERROR 1305 (42000): FUNCTION curtimestamp does not exist

7.