Mysql两种存储引擎的优缺点

痛定思痛。 2022-06-13 06:12 210阅读 0赞

MyISAM引擎是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。MyISAM中，一个table实际保存为三个文件，.frm存储表定义，.MYD存储数据,.MYI存储索引。  NULL值被允许在索引的列中。  
  
InnoDB:这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性, 因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表,InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件，或者是独立的表空间文件),相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。  
  
区别：  
1.InnoDB不支持FULLTEXT类型的索引。  
2.InnoDB 中不保存表的具体行数，也就是说，执行select count(\*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(\*)语句包含 where条件时，两种表的操作是一样的。  
3.对于AUTO\_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。  
4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。  
另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”  
可以用 show create table tablename 命令看表的类型。  
2.1 对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交  
可以执行以下命令来切换非事务表到事务(数据不会丢失)，innodb表比myisam表更安全：  
alter table tablename type=innodb;

MyISAM存储引擎的读锁和写锁是互斥的，**读写操作是串行的**。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，**大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。**这种情况有时可能会变得非常糟糕！myisam是有读锁和写锁(2个锁都是表级别锁)。

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。什么意思呢，就是说对MyISAM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞 对同一表的写操作；而对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作。

InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候，innodb是检测不到的。只能依靠锁定超时来解决。

数据库死锁例子：

两个sesison，第一个update表t1，不要提交。第二个update表t2，不要提交。接着session1去update表t2，session2去update表t1，此时就发生死锁了。

冷备份mysql和热备份mysql：

冷备份就是直接cp所有的数据库文件。

热备份：

1）myisam引擎。1、flush tables with read lock; 2、 cp......; 3、unlock tables;

作为数据库服务器的主机，最关键的就是主机的整体IO性能，包括磁盘、内存以及各种IO相关的板卡。

在Mysql中，使用orderby 关键字，排序方式有两种：

4.1版本以前的使用的是先取出需要排序的字段和整条记录的指针，然后对需要排序的字段在指定的排序区进行排序，排序之后，再根据指针去取出相应的记录。也就是说，这种算法需要访问数据两次。从4.1版本开始，采取了改进算法。一次性将所有的符合条件的记录取出。这样减少了随机IO（之前算法排序之后去记录为随机IO）。但是这种改进的方法极大的浪费了排序的时候所使用的内存。所以为了改进性能，query语句中尽量只取出我们需要的字段。

myql的profile可以用来分析查询语句的cpu和io的使用情况。

set profiling=1
    
    select count(*) from user;
    
    show profiles;
    
    show profile for query 1;

最基本的我们可以从中看出，缓存对于mysql的性能影响。提高速度近30倍。