一、InnoDB支持事务

二、InnoDB支持外键,不支持FULLTEXT索引,InnoDB不保存表的行数。清空整个表时,InnoDB是一行一行的删除,效率非常慢。MyISAM则会重建表。

三、对于自增长的字段,InnoDB中必须包含只有该字段的索引,但是MyISAM中可以和其他字段一起建立联合索引。

四、InnoDB适合频繁修改以及涉及到安全性较高的应用(链式操作)。MyISAM适合查询以及插入为主的应用。

 

关于Mysql数据库默认的存储引擎:

MyISAM和InnoDB是MySQL的两种存储引擎。如果是默认安装,那就应该是InnoDB,你可以在my.ini文件中找到default-storage-engine=INNODB;当然你可以在建表时指定相应的存储引擎。通过show create table xx 可以看见相应信息。

 

其他一些说法:

现在一般都是选用innodb了,主要是myisam的全表锁,读写串行问题,并发效率锁表(大量update就不行了),效率低myisam对于读写密集型应用一般是不会去选用的。

另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”.

1
2
3
4
5
6
7
每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。
 
MyISAM表格可以被压缩,而且它们支持全文搜索。不支持事务,而且也不支持外键。如果事物回滚将造成不完全回滚,不具有原子性。在进行updata时进行表锁,并发量相对较小。如果执行大量的SELECT,MyISAM是更好的选择。
 
MyISAM的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少。能加载更多索引,而Innodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成Innodb比MyISAM体积庞大不小
 
MyISAM缓存在内存的是索引,不是数据。而InnoDB缓存在内存的是数据,相对来说,服务器内存越大,InnoDB发挥的优势越大。

 

 

下面是具体的锁:

表锁:直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许。

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定,对BDB表进行页级锁定,对InnoDB表进行行级锁定。
对WRITE,MySQL使用的表锁定方法原理如下:
如果在表上没有锁,在它上面放一个写锁。
否则,把锁定请求放在写锁定队列中。 

对READ,MySQL使用的锁定方法原理如下:
如果在表上没有写锁定,把一个读锁定放在它上面         
否则,把锁请求放在读锁定队列中。 

InnoDB使用行锁定,BDB使用页锁定。对于这两种存储引擎,都可能存在死锁。这是因为,在SQL语句处理期间,InnoDB自动获得行锁定和BDB获得页锁定,而不是在事务启动时获得。 
行级锁定的优点:
·         当在许多线程中访问不同的行时只存在少量锁定冲突。
·         回滚时只有少量的更改。
·         可以长时间锁定单一的行。

行级锁定的缺点:
·         比页级或表级锁定占用更多的内存。
·         当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
·         如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。
·         用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。
 
1
2
3
4
5
6
如果想要在一个表上做大量的 INSERT 和 SELECT 操作,但是并行的插入却不可能时,可以将记录插入到临时表中,然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现:
 
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;
 

  

三种锁各有各的特点,若仅从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如WEB应用;行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

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

     MyISAM表的读和写是串行的,即在进行读操作时不能进行写操作,反之也是一样。但在一定条件下MyISAM表也支持查询和插入的操作的并发进行,其机制是通过控制一个系统变量(concurrent_insert)来进行的,当其值设置为0时,不允许并发插入;当其值设置为1 时,如果MyISAM表中没有空洞(即表中没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录;当其值设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

     MyISAM锁调度是如何实现的呢,这也是一个很关键的问题。例如,当一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,此时MySQL将会如优先处理进程呢?通过研究表明,写进程将先获得锁(即使读请求先到锁等待队列)。但这也造成一个很大的缺陷,即大量的写操作会造成查询操作很难获得读锁,从而可能造成永远阻塞。所幸我们可以通过一些设置来调节MyISAM的调度行为。我们可通过指定参数low-priority-updates,使MyISAM默认引擎给予读请求以优先的权利,设置其值为1(set low_priority_updates=1),使优先级降低。

     InnoDB锁与MyISAM锁的最大不同在于:一是支持事务(TRANCSACTION),二是采用了行级锁。我们知道事务是由一组SQL语句组成的逻辑处理单元,其有四个属性(简称ACID属性),分别为:

原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全部执行,要么全都不执行;
一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态;
隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行;
持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

InnoDB有两种模式的行锁:

1)共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
    ( Select * from table_name where ......lock in share mode)

2)排他锁:允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和 排他写锁。(select * from table_name where.....for update)
    为了允许行锁和表锁共存,实现多粒度锁机制;同时还有两种内部使用的意向锁(都是表锁),分别为意向共享锁和意向排他锁。
    InnoDB行锁是通过给索引项加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!

另外:插入,更新性能优化的几个重要参数

bulk_insert_buffer_size
批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的.适用于在一次性插入100-1000+条记录时, 提高效率.默认值是8M.可以针对数据量的大小,翻倍增加.

concurrent_insert
并发插入, 当表没有空洞(删除过记录), 在某进程获取读锁的情况下,其他进程可以在表尾部进行插入.

值可以设0不允许并发插入, 1当表没有空洞时, 执行并发插入, 2不管是否有空洞都执行并发插入.
默认是1 针对表的删除频率来设置.

delay_key_write
针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘. 值为 0不开启, 1开启. 默认开启.

delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延迟插入, 将数据先交给内存队列, 然后慢慢地插入.但是这些配置,不是所有的存储引擎都支持, 目前来看, 常用的InnoDB不支持, MyISAM支持. 根据实际情况调大, 一般默认够用了

/* ==================== MySQL InnoDB 锁表与锁行 ======================== */

由于InnoDB预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL才会执行Row lock (只锁住被选取的资料例) ,否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举个例子: 假设有个表单products ,里面有id跟name二个栏位,id是主键。

例1: (明确指定主键,并且有此笔资料,row lock)
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

例2: (明确指定主键,若查无此笔资料,无lock)
SELECT * FROM products WHERE id='-1' FOR UPDATE;

例3: (无主键,table lock)
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例4: (主键不明确,table lock)
SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例5: (主键不明确,table lock)
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况,可以利用MySQL的Command Mode ,开二个视窗来做测试。

在MySql 5.0中测试确实是这样的
另外:MyAsim 只支持表级锁,InnerDB支持行级锁
添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定,也不被其它事务修改(修改、删除)
是表级锁时,不管是否查询到记录,都会锁定表
此外,如果A与B都对表id进行查询但查询不到记录,则A与B在查询上不会进行row锁,但A与B都会获取排它锁,此时A再插入一条记录的话则会因为B已经有锁而处于等待中,此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction然后释放锁,此时A就获得了锁而插入成功