标签归档:mysql

mysql临时表

在sql优化的时候,当有多级子查询等复杂的sql时,建议先生成临时表,再做数据查询,

mysql的临时表建立:

1,创建临时表:

CREATE temporary table tmp_test (
      name varchar(10),
      value int
  )

2,直接将查询结果导入临时表

3,另外还可以在内存中建立临时表:
1CREATE TEMPORARY TABLE tmp_table (
 name VARCHAR(10) NOT NULL,
 value INTEGER NOT NULL
 ) TYPE = HEAP

但是我开始时测试不成功,提示 TYPE=HEAP 语法错误;type改为
engine='heap'
后测试通过;这个是mysql的版本问题,在mysql5.1以后使用engine,type仍然在MySQL 5.1中被支持,现在engine是首选的术语。

engine=‘heap’ 是将存储引擎设置为:memory存储引擎,提供“内存中”表,这样速度更快

查看创建的表的引擎等信息:

show create table tablename;

临时表在一次连接内有效,断开连接数据库自动删除临时表

 

MySQL服务器使用内部临时表

在某些情况下,mysql服务器会自动创建内部临时表。查看查询语句的执行计划,如果extra列显示“using temporary”即使用了内部临时表。内部临时表的创建条件:

*  group by 和 order by中的列不相同

*  order by的列不是引用from 表列表中 的第一表

*  group by的列不是引用from 表列表中 的第一表

*  使用了sql_small_result选项

*  含有distinct 的 order by语句

初始创建内部myisam临时表的条件:

*  表中存在text、blob列

*  在group by中的 列 有超过512字节

*  在distinct查询中的 列 有超过512字节

*  在union、union all联合查询中,select 列 列表中的 列 有超过512字节的

 

参考:高手详解SQL性能优化十条经验

MySQL临时表

mysql存储引擎和表类型

mysql事务隔离机制

四种事务隔离级别:(写锁/共享锁;读锁/排它锁)

1. 未提交读(Read uncommitted)。

写操作加写锁,读操作不加锁。禁止第一类丢失更新,但是会出现所有其他数据并发问题。

2.提交读(Read committed)。

写操作加写锁,读操作加读锁。禁止第一类丢失更新和脏读。这是大部分关系数据库的默认 隔离级别。

3.可重复读(Read repeatable)。, 对于读操作加读锁到事务结束,其他事务的更新操作只能等到事务结束之后进行。和提交 读的区别在于,

提交读的读操作是加读锁到本次读操作结束,可重复读的锁粒度更大。禁止两类丢失更新,禁止脏读和不可 重复度,但是可能出现幻读.

4.序列化(Serializable)。

读操作加表级读锁至事务结束。可以禁止幻读。会导致大量超时和锁竞争现象

mysql设置隔离级别:

1.全局修改,修改mysql.ini配置文件,在最后加上
1 #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
2 [mysqld]
3 transaction-isolation = REPEATABLE-READ
这里全局默认是REPEATABLE-READ,其实MySQL本来默认也是这个级别

命令修改:

set global transaction solation level repeatable read;

2.对当前session修改,在登录mysql客户端后,执行命令:

set session transaction solation level repeatable read;

查看当前隔离级别:

select @@global.tx_isolation;

或者:

select @@tx_isolation;

mysql加锁:关于锁的描述见:数据库的几个概念

1,加共享锁(s锁):

select * from tb_test where id=9 lock in share mode

2,加排它锁(x锁):

select * from tb_test where id=1 for update;

当指定了明确的id时候,会加行锁;

当没有指定明确id时,将会加表锁,如:

锁状态监控:
1
mysql> show status like 'Innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 665452 |
| Innodb_row_lock_time_avg | 24646 |
| Innodb_row_lock_time_max | 51839 |
| Innodb_row_lock_waits | 27 |
+-------------------------------+--------+

解释如下:
Innodb_row_lock_current_waits:当前等待锁的数量
Innodb_row_lock_time:系统启动到现在、锁定的总时间长度
Innodb_row_lock_time_avg:每次平均锁定的时间
Innodb_row_lock_time_max:最长一次锁定时间
Innodb_row_lock_waits:系统启动到现在、总共锁定次数

参考:MySQL数据库事务隔离级别(Transaction Isolation Level)

MySQL InnoDB 锁机制详解