共计 4030 个字符,预计需要花费 11 分钟才能阅读完成。
1.1 默认引擎 InnoDB
mysql 默认的引擎是 InnoDB,提供了对数据库的 ACID 事务的支持,并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外健约束。它的设计目的就是处理大容量数据库系统,本身就是基于Mysql后台的完整的数据库系统。MySQL 在运行时 InnoDB 会在内存中建立缓冲池,用于缓冲数据和索引。FULLTEXT类型的索引,而且没有保存表的行数,当 select count(*) from table 时需要扫描全表。
当需要使用数据库事务时,InnoDB时首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高的时候,使用 InnoDB 会提高效率。但是行级锁也不是绝对的,如果执行一个SQL语句不能确定扫描的范围,InnoDB同样会锁全表。在筛选条件里没有索引字段时也会把整个表锁住。
1.2 InnoDB 和 MyISAM 区别
- 1. InnoDB 支持 事务,外健等高级数据库功能,MyISAM 不支持。
- 2. 效率:MyISAM 在插入数据的效率是 InnoDB 远远所不及的,在删改查方面,随着InnoDB 的优化,差距在渐渐减小。(简单测试:同时插入100W条数据,MyISAM耗时38s左右,而InnoDB却耗时76分钟4s左右)
- 3. 行数查询 :InnoDB 不保存行数,也就是select的时候,要扫描全表,MyISAM只需读取保存的行数即可,这也是MyISAM快的一个因素。
- 4. 索引:InnoDB 会自动创建 Auto_increment 类型字段的索引,一般都是用于主键,即主键索引(只包含改字段)。而MyISAM 可以和其他字段创建联合索引。除此之外,MyISAM 还支持 全文索引,压缩索引,InnoDB不支持。
备注:MyISAM 的索引和数据是分开的,并且索引是有压缩的,内存使用率就对应提高了不少,能加载更多的索引。InnoDB 是索引和数据是紧密捆绑在一起的,没有使用压缩从而会造成InnoDB 和 MyISAM 体积庞大不少。 - 5. 服务器备份:InnoDB必须导出SQL来备份,LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。InnoDB是拷贝数据文件、备份 binlog,或者用 mysqldump,支持灾难恢复(仅需几分钟),MyISAM不支持,遇到数据崩溃,基本上很难恢复,所以要经常进行数据备份。(mysqldump一致性备份:单线程,适合数据量小的库,默认备份会锁表,锁库,来备份,因为没有开启事务,而使用–single-transaction这个参数之后,会开启一个事务来备份,不会锁表锁库。)
- 6. 锁的支持:MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的
1.3 使用场景建议
- 可靠性高或者要求事务处理,使用 InnoDB
- 表更新和查询都相当的频繁,并且表锁定的机会比较大的情况,使用InnoDB
- 做很多count的计算,如日志,调查的业务表,使用 MyISAM
- 插入修改不频繁,查询非常频繁的,使用 MyISAM
1.4 引擎原理分析
1.4.1 MyISAM索引
MyISAM 索引用的是 B+tree 来存储数据,MyISAM 索引的指针指向的是键值的地址,地址存储的是数据。B+tree 的数据域存储的内容为实际数据的地址,也就是说他的数据和索引是分开的,只不过是用了索引指向实际的数据。这种索引就是非聚焦索引。
检索过程:首先按照 B+tree 搜索算法搜索引擎,如果指定的 Key 存在,则取出 data 域的值,然后以 data 域的值为地址,根据data域的值去读取相应数据记录。
1.4.2 InnoDB索引
也是采用 B+tree 索引结构。InnoDB 的索引文件本身就是数据文件,即B+tree的数据域存储的就是实际的数据。这种索引就是聚焦索引。这个索引的 key 就是数据表的主键。因此InnoDB表数据文件本身就是主索引。
InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址,所以当以辅助索引查找时,会先根据辅助索引找到主键,再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键,否则会使辅助索引变得过大。
建议使用自增的字段作为主键,这样B+Tree的每一个结点都会被顺序的填满,而不会频繁的分裂调整,会有效的提升插入数据的效率。
检索过程:将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用”where id = 13”这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。
2. SQL 标准中的四种隔离级别
READ UNCOMMITED(未提交读)
事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也成为脏读(Dirty Read)。这个级别会导致很多问题,从性能上说READ UNCOMMITED 不会比其他的级别好太多,但缺乏其他级别的好多好处,除非有非常必要的理由,在实际的应用中一般很少使用READ UNCOMMITED.
READ COMMITED(提交读)
大多数数据库系统的默认隔离级别都是READ COMMITED (但是MYSQL不是)。READ COMMITED 满足前面提到的隔离性的简单定义:一个事务开始时,只能看到已经提交的事务所做的修改。换句话说,一个事务从开始到提交之前,所做的任何修改对其他事务都 是不可见的。这个级别有时候也叫做不可重复的(nonerepeatable read),因为两次执行同样的查询,可能会得到不一样的结果。
REPEATABLE READ(可重复读)
可重复读是Mysql 默认的事务隔离级别。
REPEATABLE READ (可重复读) 解决了脏读问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是,理论上,可重复读隔离级别还是无法解决另一个幻读 (PhantomRead)的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读 取该范围的记录时,会产生幻行(Phantom Row)。InnoDB和XtraDB 存储引擎通过多版并发控制(MVCC ,Multivesion Concurrency Control )解决了幻读问题。
SERIALIZABLE(可串行化)
SERIALIZABLE是最高的隔离级别。它通过强制事务串行,避免了前面说的幻读问题。简单的来说,SERIALIZABLE会在读的每一行数据上 都加上锁,所以可能导致大量的超时和锁征用问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况,才可考 虑用该级别。
3. 行锁和表锁
表锁:顾名思义就是对整张表进行加锁,同一时刻整张表所有记录都被霸占,虽然不会出现死锁问题但是锁冲突高堵塞高,并发低。
行锁:很明显只对某一行进行加锁,这样表的其余行并不会被占用,冲突低,并发高,但是死锁很可能出现。
锁冲突:就是在多个线程在对竞争资源想同时加锁,僵持不下。
死锁:就是你请求的被别人锁住,被人请求被你锁住了,对方都没法进行下去。
首先行锁是innodb默认的锁,但是在筛选条件里面没有索引字段时就会把整个表锁其他,下面会给出测试例子,最后会讲下mysql innodb引擎为啥需要使用索引来完成对行加锁。
行锁
- 读锁:允许其他线程上读锁,但是不允许上写锁
-- 先开个读锁
BEGIN;
SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_number=26911523448454 lock in SHARE MODE
-- 再开个读锁,读同一行
BEGIN;
SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_number=26911523448454 lock in SHARE MODE
-- 两个都查询出来了,但是再开个写锁就一直没有停止,说明是被锁住了
-- 结论:读锁可以共享读,其他线程不能加写锁。
- 写锁:不允许其他线程上任何锁
- 行锁必须要索引才能实现,否则会自动锁全表,两个事务可以用同一个索引
-- 下面的consumer_chain_order_number是不会重复的,但没有索引
-- 马上显示查询结果
BEGIN;
SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_number=26911523448454 for update
--一直没有结束直到等待超时
BEGIN;
SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_number=55181523448554 for update
-- 说明了默认锁全表,接下来试下有索引的字段,这个表的主键
BEGIN;
SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_id=1 for update
BEGIN;
SELECT * from tyg_consumer_chain_sell_order o where o.consumer_chain_order_id=2 for update
-- 都查询出来了结果,说明锁住了行