MySQL的存储引擎,MyISAM和InnoDB
1、MySQL默认存储引擎的变迁
在MySQL 5.1之前的版本中,默认的搜索引擎是MyISAM,从MySQL 5.5之后的版本中,默认的搜索引擎变更为InnoDB。
2、MyISAM与InnoDB存储引擎的主要特点
注意:非聚集索引和聚集索引的区别,非聚集索引需要去两个文件文件联合查询数据(MYI和MYD),聚集索引只需要在一个文件中查找数据(IDB);
- MyISAM存储引擎索引文件和数据文件是分离的(非聚集)
存储引擎是myisam, 在data目录下会看到3类文件:.frm、.myi、.myd
(1)*.frm–表定义,是描述表结构的文件。
(2)*.MYD–"D"数据信息文件,是表的数据文件。
(3)*.MYI–"I"索引信息文件,是表数据文件中任何索引的数据树
MyISAM存储引擎的特点是:表锁、不支持事务和全文索引,适合一些CMS内容管理系统作为后台数据库使用,但是使用大并发、重负荷生产系统上,表锁结构的特性就显得力不从心;
例子:select * from where Col1 = 30;
看Col1 是否是索引字段,如果是首先执行的是MYI(表索引文件)文件,拿它去根节点查找位置,从根节点锁定范围后再从子节点查找位置(也可能就存在根节点的位置),在把查到的节点位置元素取出它的data值,data值存的是元素的磁盘文件地址,根据磁盘文件地址就可以快速查到到该元素在磁盘中的数据,其首先执行的是MYI文件,再执行MYD(表的数据文件)文件。
-
InnoDB存储引擎索引实现(聚集)
表数据文件本身就是按B+Tree组织的一个索引结构文件;
聚集索引-叶节点包含了完整的数据记录;
为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?(mysql在开发InnoDB表底层存储引擎的时候,这张表的数据必须要有B+树的聚集索引来组织数据,必须要有主键来组织整个表数据;用整型占用空间小,其次是整型好比较大小,假如用UUID非自增(字符型)不好比较大小;根据B+树结构自增的话每次插入都会从右边增加,不自增如从中间插入后,还需要去平衡B+树结构)
为什么非主键索引结构子节点存储的是主键值?(一致性和节省存储空间)
存储引擎是InnoDB, 在data目录下会看到2类文件:.frm、.ibd
(1)*.frm–表结构的文件;
(2)*.ibd–表数据文件;
InnoDB存储引擎的特点是:行锁、支持事务操作、支持外键、不支持FULLTEXT类型的索引(5.6.4以后版本开始支持FULLTEXT类型的索引)。InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。InnoDB是为处理巨大量时拥有最大性能而设计的。它的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
注意:
InnoDB表的行锁也不是绝对的,假如在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “a%”
。
两种类型最主要的差别就是InnoDB支持事务处理与外键和行级锁。而MyISAM不支持。
3、MyISAM与InnoDB性能测试
随着CPU核数的增加,InnoDB的吞吐量反而越好,而MyISAM,其吞吐量几乎没有什么变化,显然,MyISAM的表锁定机制降低了读和写的吞吐量。
4、事务支持与否
MyISAM是一种非事务性的引擎,使得MyISAM引擎的MySQL可以提供高速存储和检索,以及全文搜索能力,适合数据仓库等查询频繁的应用;
InnoDB是事务安全的;
事务是一种高级的处理方式,如在一些列增删改中只要哪个出错还可以回滚还原,而MyISAM就不可以了。
5、MyISAM与InnoDB表锁和行锁的解释
MySQL表锁:它会锁定整张表,如果当前有用户正在执行写操作并且获取了写锁,这可能导致整张表被锁定,阻塞其他用户的读写操作。如果用户执行的是读操作,则会获取读锁,此时其他用户的并发读操作将被接受,写操作会被阻塞。
InnoDB行锁:通过给索引加锁来实现的,即只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。在InnoDB两个事务发生死锁的时候,会计算出每个事务影响的行数,然后回滚行数少的那个事务。当锁定的场景中不涉及Innodb的时候,InnoDB是检测不到的。只能依靠锁定超时来解决。
共享锁(Table Read Lock)和排它锁(Table Write Lock)。也就是说对表进行读操作时,它不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写操作;而对表的写操作,则会阻塞其他用户对同一表的读和写操作。
区别:表锁的优点在于开销小、加锁快、无死锁,缺点是锁的颗粒度大,发生锁冲突的概率较高,并发能力较弱,行锁则相反。
6、是否保存数据库表中表的具体行数
InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table
时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。
7、如何选择
-
MyISAM适合场景:
(1)做很多count 的计算;
(2)插入不频繁,查询非常频繁,如果执行大量的SELECT,MyISAM是更好的选择;
(3)没有事务。
-
InnoDB适合场景:
(1)可靠性要求比较高,或者要求事务;
(2)如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表;
-
区别:
(1)清空整个表是,或者DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的 删除;MyISAM则会重建表。
(2)对于自增长的字段(如:主键自增),InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立联合索引。
注意:创建每个表格的代码是相同的,除了最后的 TYPE参数,这一参数用来指定数据引擎。
MySQL事务
1、什么事事务?
事务:是数据库操作的最小工作单元,是作为单个逻辑工作单元执行的一系列操作;这些操作作为一个整体一起向系统提交,要么都执行、要么都不执行;事务是一组不可再分割的操作集合(工作逻辑单元);
2、事务的简单操作
显式启动事务语句,begin或者start transaction;
提交commit;
回滚rollback;
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
3、事务的四大特性
-
原子性:
一个事务(transaction)中的所有操作,要么全部成功,要么全部不成功,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
-
一致性:
事务完成前和完成后,对数据库来说状态是没有改变的。一致性要在隔离状态下才能保证。
-
隔离性:
隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。即该事务提交前对其他事物都不可见。通过锁或者MVCC实现,MVCC(多版本并发控制)在可重复读的位置举例介绍。
-
持久性:
事务一旦被提交了,对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
4、事务日志
-
重做日志(redo log):
每次操作都在内存中操作,并写入重做日志中记录。一旦操作完成则表示事务提交。一段时间后,根据日志内容将操作写入硬盘。
-
撤销日志(undo log):
保留每一次操作前的状态。以便恢复。
-
日志组:
日志也是文件,文件中只记录操作,不记录对应的数据;多个日志文件构成日志组;日志文件是轮流使用。一个记录满以后自动记录到下个文件中。记录满的日志文件会将操作同步到硬盘。然后清空日志。
5、事务的隔离等级
mysql默认的隔离等级是可重复读,如果想要在mysql启动时就修改mysql的隔离等级,需要修改配置文件,在[mysqld]中添加如下内容:
[mysqld]
transaction-isolation = READ-COMMITTED
#查看当前事务的隔离级别
mysql>select @@tx_isolation\G;
-
脏读:
事务A读取了事务B更新、未提交的数据,然后B回滚操作,那么A读取到的数据是脏数据(没有用的数据)。
-
幻读:
同一事务中,两次按相同条件查询到的记录不一样。造成幻读的原因在于事务处理没有结束时,其他事务对同一数据集合增加或删除了记录。在mysql中MVCC在一定程度上解决了幻读。
-
不可重复读:
事务 B 在事务A多次读取的过程中,对数据作了更新操作并提交,导致事务A两次读取同一数据不一致。主要针对数据更新的。
注意:不可重复读和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。
MySQL的索引
1、索引本质:索引是帮助mysql高效获取数据的排好序的数据结构;
2、索引的类型
普通索引(INDEX):最基本的索引,没有任何限制
唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
主键索引(PRIMARY):它 是一种特殊的唯一索引,不允许有空值。
全文索引(FULLTEXT ):仅可用于 MyISAM 表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时好空间。
组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。
2、索引数据结构:
-
二叉树(右边的数据大于左边的数据)
-
红黑树
-
Hash表
执行过程:对索引元素的值做一次hash运算得到一个散列值,根据散列值定位到这一行的元素,理解为散列值就是磁盘地址;反列:select * from t where t.Col2 > 89; 如该条SQL,hash索引对于范围查找是没有用的,原因是数据没有排序,在散乱的数据的去查找范围还是要进行一次全表扫描;极少的情况用hash索引。
-
B-Tree(增加横向节点的数量)key-value键值对
叶节点具有相同的深度,叶节点的指针为空;
所有索引元素不重复;
节点中的数据索引从左到右递增排序;
-
B+Tree(B-Tree变种)
非叶子节点不存储data,只存储索引(冗余),可放更多索引;
叶子节点包含所有索引字段;
叶子节点用指针连接(双向指针),提高区间访问的性能,从左到右依次递增;
区别:在相同高度的情况下,B+树的数据存储量远大于B树的数据存储量;
B树叶节点没有指针,每次查找都要从头结点开始,B+树叶节点是双向指针,并且从左到右逐渐递增,也可以很好的用于范围查找;
注意:MySQL数据库不用二叉树、红黑树的原因是数据量过大的时候,树的高度过高,节点过多查询次数增加;
MySQL优化
1、选取合适的字段属性
数据库中表越小查询越快,选取合适的字段长度;尽可能的把字段设置为not null,在查询时就减少null的比较。
2、使用连接(JOIN)来代替子查询是(sub-Queries)
3、使用联合(union)来代替手动创建的临时表
4、使用索引
5、使用外键
锁定表的方法可以维护数据的完整性,但是它却不能保证数据的关联性。这个时候就可以使用外键。
6、优化SQL
在建有索引的字段上尽量不要使用函数进行操作;
查询时尽量避免全表扫描;
··············································································