MySQL之SQL优化(三)

事务

 

1. 自动提交

对于事务类型的库表

> show variables like 'AUTOCOMMIT';

> set AUTOCOMMIT=1;

 

2. 通过修改配置文件来设置整个数据库的隔离级别，也可以通过以下语句只改变当前会话的隔离级别

> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

MySQL可识别4个ANSI定义的隔离级别，InnoDB引擎支持这个四个隔离级别。

 

3. 隐式锁定和显式锁定

a) InnoDB采用两阶段锁定协议(two-phase locking protocol)，事务执行过程中随时可执行锁定，锁定后，

执行commit或rollback时该事务中的所有锁同时被释放。

InnoDB会根据隔离级别在需要的时候自动加锁（隐式锁定）。

b) InnoDB的显式锁定语句：

select ... lock in share mode;

select ... for update;

c)MySQL也支持Lock Tables和Unlock Tables语句，这是在服务层实现的，与存储引擎无关。 

 

* 在MySQL中无论使用什么存储引擎，除了事务中禁用了AutoCommit时可以使用Lock Tables外，其他时候不要显式的执行Lock Tables语句

 

4. 多版本并发控制（MVCC）

mysql> show table status like 'album'\G

*************************** 1. row *************

Name: album //表名

Engine: InnoDB //表的存储引擎名

Version: 10 

Row_format: Compact //行的格式，对MyISAM表，可选值为Dynamic，Fixed或Compressed; Dynamic的行长度可变，一般包含可变长的字段，如varchar或blob。Fixed的行长度是固定的，只包含固定长度的列，如Char和Integer。Compressed的行只在压缩表中存在。

Rows: 541815 //表中的行数，对于MyISAM和其他一些引擎为精确值。对于InnoDB，该值是估值

Avg_row_length: 64 //平均每行包含的字节数

Data_length: 35192832 //表数据的大小，以字节为单位

Max_data_length: 0 //表数据的最大容量，该值和存储引擎有关

Index_length: 0 //索引大小，该值与存储引擎有关

Data_free: 7340032 //对于MyISAM表，表示已分配但目前没有使用的空间，该部分空间包含以前删除的行，以及后续可以被insert利用到的空间

Auto_increment: NULL //下一个auto_increment的值

Create_time: 2015-01-20 16:37:58 //表创建的时间

Update_time: NULL //表数据的最后修改时间

Check_time: NULL //使用check table命令或者myisamchk工具最后一次检查表的时间

Collation: latin1_swedish_ci //表的默认字符集和字符列排序规则

Checksum: NULL //如果启用，保存的是整个表的实时校验和

Create_options: //创建表时指定的其他选项

Comment: //其他额外信息，对于MyISAM表，保存的是表在创建时带的注释。对于InnoDB，则保存的是InnoDB表空间的剩余空间信息。如果是视图，则包含“View”的文本字样

1 row in set (0.44 sec)

 

5. InnoDB存储引擎

 InnoDB是MySQL的默认事务引擎，使用很广泛，用来处理大量的短(short-lived)事务

 

6. MyISAM存储引擎

不支持事务，行级锁和崩溃后的安全恢复

对于只读，可忍受修复操作的表，可使用MyISAM

 

7. MyISAM压缩表

如果表在创建并导入数据后，不会再进行修改操作，则该表适合使用MyISAM压缩表；

使用myisampack对MyISAM表进行压缩（也可称之为打包），压缩操作可以极大的减少磁盘空间占用，因此也可以减少磁盘I/O，从而提供性能，压缩表也支持索引，但索引也是只读的