mysql使用时遇到问题 事务级别+常用引擎+引擎切换

一、事务隔离级别

MySQL数据库为我们提供的四种隔离级别：

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

③ Read committed (读已提交)：可避免脏读的发生。

④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

二、数据库引擎种类

（1）MyISAM存储引擎：

不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表

• 支持3种不同的存储格式，分别是：静态表；动态表；压缩表

  • 静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

  • 动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

  • 压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支

（2）InnoDB存储引擎*

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

• InnoDB存储引擎的特点：支持自动增长列，支持外键约束

(3)MEMORY存储引擎

Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。
MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围

• Hash索引优点：
  Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
  Hash索引缺点： 那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；

Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

（4）MERGE存储引擎

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

三、数据库引擎切换方式

• 1、通过命令直接修改表的存储引擎
  • 在数据多的时候比较慢，而且在修改时会影响读取性能
  • 修改表的存储引擎使用: alter table 表名 engine = 引擎类型

alter table mytest ENGINE = MyIsam;

• 2、导出，导入。
  • 这个比较容易操作，直接把导出来的sql文件给改了，然后再导回去
  • 风险较大

 22 DROP TABLE IF EXISTS `mytest`;  //在进行恢复的过程中,如果不修改原来表的名字原来的表就回被删除;
 23 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 24 /*!40101 SET character_set_client = utf8 */;
 25 CREATE TABLE `mytest` (
 26   `id` int(11) DEFAULT NULL,
 27   `name` varchar(30) DEFAULT NULL
 28 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;//这里直接修改引擎
 29 /*!40101 SET character_set_client = @saved_cs_client */;

• 3、是通过创建新表,然后重新写入数据的方式来进行
  • a.创建表,先创建一个和要操作表一样的表，然后更改存储引擎为目标引擎。
    • CREATE TABLE my_tmp_table LIKE my_table;
    • ALTER TABLE my_tmp_table ENGINE=InnoDB;
  • b.插入。为了安全和速度，最好加上事务，并限制id(主键）范围。
    • INSERT INTO my_tmp_table SELECT * FROM my_table;

mysql> create table myisam_mytest like mytest;
Query OK, 0 rows affected (0.08 sec)

mysql> alter table myisam_mytest engine=Myisam;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into myisam_mytest select * from mytest;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0