19.MySQL整改-MyISAM存储引擎变更为INNODB

MySQL的MyISAM存储引擎改为INNODB存储引擎

一、存储引擎变更背景

1.否支持行级锁

  MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

         表级锁： MySQL 中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

         行级锁： MySQL 中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB 存储引擎的锁的算法有三种：

Record lock：记录锁，单个行记录上的锁

Gap lock：间隙锁，锁定一个范围，不包括记录本身

Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身

2.是否支持事务

  MyISAM 不提供事务支持。

  InnoDB 提供事务支持，具有提交(commit)和回滚(rollback)事务的能力。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

3.是否支持外键

  MyISAM 不支持，而 InnoDB 支持。

4.是否支持数据库异常崩溃后的安全恢复

  MyISAM 不支持，而 InnoDB 支持。

  使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

二、存储引擎变更

1.非INNODB存储表的检查

select table_schema,table_name,engine,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH,

INDEX_LENGTH

from information_schema.tables where table_schema not in('mysql','sys','information_schema','PERFORMANCE_SCHEMA')

and engine <> 'InnoDB' 

order by table_schema,TABLE_ROWS desc;

发现有一个表示MyISAM存储引擎的。

 

2.表结构检查

mysql> show create table my_test1;

+--------------+-----------------------------------------------------------------------------------+

| Table    | Create Table                                                                     |

+----------+----------------------------------------------------------------------------------------+

| my_test1 | CREATE TABLE `my_test1` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(50) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8                                                            |

+----------+------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

3.改变表的存储结构

mysql> alter table my_test1 engine=innodb;

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

4.检查表的存储引擎

可以发现表已经变成Innodb存储引擎。且数据条数正常。

 

5.检查是否支持事务

mysql> select * from my_test1;

+------+-------+

| id   | name  |

+------+-------+

|    1 | xsq1  |

|    2 | xsq22 |

|    3 | xsq3  |

|    4 | xsq4  |

|    5 | xsq5  |

+------+-------+

5 rows in set (0.00 sec)

mysql> insert into my_test1 values(6,'xsq6');

Query OK, 1 row affected (0.00 sec)

mysql> select * from my_test1;

+------+-------+

| id   | name  |

+------+-------+

|    1 | xsq1  |

|    2 | xsq22 |

|    3 | xsq3  |

|    4 | xsq4  |

|    5 | xsq5  |

|    6 | xsq6  |

+------+-------+

6 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from my_test1;

+------+-------+

| id   | name  |

+------+-------+

|    1 | xsq1  |

|    2 | xsq22 |

|    3 | xsq3  |

|    4 | xsq4  |

|    5 | xsq5  |

+------+-------+

5 rows in set (0.00 sec)

可以看到回滚后插入的数据就没有了，但是如果是MyISAM存储引擎，可以插入，修改，删除，但是不支持提交和回滚。默认直接提交。