【存储引擎(处理表的处理器)】
1.基本操作:
-
查看所有的存储引擎
show engines; -
查看已有表的存储引擎
show create table 表名;
engine = InnoDB(默认)
支持事务和事务回滚,行级锁 -
创建表时指定存储引擎
create table 表名(…) engine=myisam[,character set utf8]; -
工作中常用的存储引擎
InnoDB / myisam
[mysqld]
defaut-storage-engine=myisam 更改默认的存储引擎
2.常用存储引擎的特点
innodb特点:
- 共享表空间
表名.frm 表结构
表名.ibd 表记录&索引信息 - 支持行级锁
myisam特点:
- 独享表空间
表名.frm 表结构
表名.myd 表记录
表名.myi 索引文件 - 支持表级锁
3.锁
-
加锁的目的:
解决客户端并发访问的冲突问题 -
锁的分类
- 锁的类型:
读锁(select)/共享锁: 加读锁后不能更改表中内容,但可以进行查询
写锁/互斥锁/排它锁 - 锁粒度
表级锁
行级锁
- 锁的类型:
4.如何决定表使用哪种存储引擎
- 执行查询操作多的表使用myisam(使用innodb浪费资源)(锁表比锁行快得多)
- 执行写操作多的表使用innodb存储引擎
5. MyIASM和Innodb两种引擎所使用的索引的数据结构是什么?
答案:都是B+树!
MyIASM引擎,B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的,只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
Innodb引擎的索引的数据结构也是B+树,只不过数据结构中存储的都是实际的数据,这种索引有被称为聚集索引。
整形比较大小比字符串快,而且省空间
附:MySQL存储引擎中的MyISAM和InnoDB区别详解
在MySQL 5.5之前,MyISAM是mysql的默认数据库引擎,其由早期的ISAM(Indexed Sequential Access Method:有索引的顺序访问方法)所改良。虽然MyISAM性能极佳,但却有一个显著的缺点: 不支持事务处理。不过,MySQL也导入了另一种数据库引擎InnoDB,以强化参考完整性与并发违规处理机制,后来就逐渐取代MyISAM。
InnoDB是MySQL的数据库引擎之一,其由Innobase oy公司所开发,2006年五月由甲骨文公司并购。与传统的ISAM、MyISAM相比,InnoDB的最大特色就是支持ACID兼容的事务功能,类似于PostgreSQL。目前InnoDB采用双轨制授权,一是GPL授权,另一是专有软件授权。具体地,MyISAM与InnoDB作为MySQL的两大存储引擎的差异主要包括:
- 存储结构:每个MyISAM在磁盘上存储成三个文件:第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义,数据文件的扩展名为.MYD (MYData),索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。
- 存储空间:MyISAM可被压缩,占据的存储空间较小,支持静态表、动态表、压缩表三种不同的存储格式。InnoDB需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
- 可移植性、备份及恢复:MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便,同时在备份和恢复时也可单独针对某个表进行操作。InnoDB免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。
- 事务支持:MyISAM强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能,具有事务提交、回滚和崩溃修复能力。
- AUTO_INCREMENT:在MyISAM中,可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,它可以根据前面几列进行排序后递增。InnoDB中必须包含只有该字段的索引,并且引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。
- 表锁差异:MyISAM只支持表级锁,用户在操作MyISAM表时,select、update、delete和insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能,但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。
- 全文索引:MyISAM支持 FULLTEXT类型的全文索引;InnoDB不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。
- 表主键:MyISAM允许没有任何索引和主键的表存在,索引都是保存行的地址。对于InnoDB,如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。
- 表的具体行数:MyISAM保存表的总行数,select count() from table;会直接取出出该值;而InnoDB没有保存表的总行数,如果使用select count() from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。
- CURD操作:在MyISAM中,如果执行大量的SELECT,MyISAM是更好的选择。对于InnoDB,如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。DELETE从性能上InnoDB更优,但DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除,在innodb上如果要清空保存有大量数据的表,最好使用truncate table这个命令。
- 外键:MyISAM不支持外键,而InnoDB支持外键。
通过上述的分析,基本上可以考虑使用InnoDB来替代MyISAM引擎了,原因是InnoDB自身很多良好的特点,比如事务支持、存储过程、视图、行级锁、外键等等。尤其在并发很多的情况下,相信InnoDB的表现肯定要比MyISAM强很多。另外,必须需要注意的是,任何一种表都不是万能的,合适的才是最好的,才能最大的发挥MySQL的性能优势。如果是不复杂的、非关键的Web应用,还是可以继续考虑MyISAM的,这个具体情况具体考虑。