浮动播放器wordpress,刷神马seo排名首页排名,沛县做网站,萝岗免费网站建设1、MyISAM#xff1a;默认表类型#xff0c;它是基于传统的ISAM类型#xff0c;ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写#xff0c;它是存储记录和文件的标准方法。不是事务安全的#xff0c;而且不支持外键#xff0c;如果执行大量的sel…1、MyISAM默认表类型它是基于传统的ISAM类型ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写它是存储记录和文件的标准方法。不是事务安全的而且不支持外键如果执行大量的selectinsert MyISAM比较适合。 2、InnoDB支持事务安全的引擎支持外键、行锁、事务是他的最大特点。如果有大量的update和insert建议使用InnoDB特别是针对多个并发和QPS较高的情况。 一、表锁差异 MyISAM: myisam只支持表级锁用户在操作myisam表时selectupdatedeleteinsert语句都会给表自动加锁如果加锁以后的表满足insert并发的情况下可以在表的尾部插入新的数据。也可以通过lock table命令来锁表这样操作主要是可以模仿事务但是消耗非常大一般只在实验演示中使用。 InnoDB Innodb支持事务和行级锁是innodb的最大特色。 事务的ACID属性atomicity,consistent,isolation,durable。 并发事务带来的几个问题更新丢失脏读不可重复读幻读。 事务隔离级别未提交读(Read uncommitted)已提交读(Read committed)可重复读(Repeatable read)可序列化(Serializable) 四种隔离级别的比较 读数据一致性及并发副作用 隔离级别 读数据一致性 脏读 不可重复读 幻读 为提交读(read uncommitted) 最低级别不读物理上顺坏的数据 是 是 是 已提交读(read committed) 语句级 否 是 是 可重复读(Repeatable red) 事务级 否 否 是 可序列化(Serializable) 最高级别事务级 否 否 否 查看mysql的默认事务隔离级别“show global variables like ‘tx_isolation’; ” Innodb的行锁模式有以下几种共享锁排他锁意向共享锁(表锁)意向排他锁(表锁)间隙锁。 注意当语句没有使用索引innodb不能确定操作的行这个时候就使用的意向锁也就是表锁 关于死锁 什么是死锁当两个事务都需要获得对方持有的排他锁才能完成事务这样就导致了循环锁等待也就是常见的死锁类型。 解决死锁的方法 1、 数据库参数 2、 应用中尽量约定程序读取表的顺序一样 3、 应用中处理一个表时尽量对处理的顺序排序 4、 调整事务隔离级别避免两个事务同时操作一行不存在的数据容易发生死锁 二、数据库文件差异 MyISAM myisam属于堆表 myisam在磁盘存储上有三个文件每个文件名以表名开头扩展名指出文件类型。 .frm 用于存储表的定义 .MYD 用于存放数据 .MYI 用于存放表索引 myisam表还支持三种不同的存储格式 静态表(默认但是注意数据末尾不能有空格会被去掉) 动态表 压缩表 InnoDB innodb属于索引组织表 innodb有两种存储方式共享表空间存储和多表空间存储 两种存储方式的表结构和myisam一样以表名开头扩展名是.frm。 如果使用共享表空间那么所有表的数据文件和索引文件都保存在一个表空间里一个表空间可以有多个文件通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字一般共享表空间的名字叫ibdata1-n。 如果使用多表空间那么每个表都有一个表空间文件用于存储每个表的数据和索引文件名以表名开头以.ibd为扩展名。 三、索引差异 1、关于自动增长 myisam引擎的自动增长列必须是索引如果是组合索引自动增长可以不是第一列他可以根据前面几列进行排序后递增。 innodb引擎的自动增长咧必须是索引如果是组合索引也必须是组合索引的第一列。 2、关于主键 myisam允许没有任何索引和主键的表存在 myisam的索引都是保存行的地址。 innodb引擎如果没有设定主键或者非空唯一索引就会自动生成一个6字节的主键(用户不可见) innodb的数据是主索引的一部分附加索引保存的是主索引的值。 3、关于count()函数 myisam保存有表的总行数如果select count(*) from table;会直接取出出该值 innodb没有保存表的总行数如果使用select count(*) from table就会遍历整个表消耗相当大但是在加了wehre 条件后myisam和innodb处理的方式都一样。 4、全文索引 myisam支持 FULLTEXT类型的全文索引 innodb不支持FULLTEXT类型的全文索引但是innodb可以使用sphinx插件支持全文索引并且效果更好。sphinx 是一个开源软件提供多种语言的API接口可以优化mysql的各种查询 5、delete from table 使用这条命令时innodb不会从新建立表而是一条一条的删除数据在innodb上如果要清空保存有大量数据的表最 好不要使用这个命令。(推荐使用truncate table不过需要用户有drop此表的权限) 6、索引保存位置 myisam的索引以表名.MYI文件分别保存。 innodb的索引和数据一起保存在表空间里。 四、开发的注意事项 1、可以用 show create table tablename 命令看表的引擎类型。 2、对不支持事务的表做start/commit操作没有任何效果在执行commit前已经提交。 3、可以执行以下命令来切换非事务表到事务数据不会丢失innodb表比myisam表更安全alter table tablename typeinnodb;或者使用 alter table tablename engine innodb; 4、默认innodb是开启自动提交的如果你按照myisam的使用方法来编写代码页不会存在错误只是性能会很低。如何在编写代码时候提高数据库性能呢 a、尽量将多个语句绑到一个事务中进行提交避免多次提交导致的数据库开销。 b、在一个事务获得排他锁或者意向排他锁以后如果后面还有需要处理的sql语句在这两条或者多条sql语句之间程序应尽量少的进行逻辑运算和处理减少锁的时间。 c、尽量避免死锁 d、sql语句如果有where子句一定要使用索引尽量避免获取意向排他锁。 f、针对我们自己的数据库环境日志系统是直插入不修改的所以我们使用混合引擎方式ZION_LOG_DB照旧使用myisam存储引擎只有ZION_GAME_DBZION_LOGIN_DBDAUM_BILLING使用Innodb引擎。 五、究竟该怎么选择 下面先让我们回答一些问题 ◆你的数据库有外键吗 ◆你需要事务支持吗 ◆你需要全文索引吗 ◆你经常使用什么样的查询模式 ◆你的数据有多大 myisam只有索引缓存 innodb不分索引文件数据文件 innodb buffer myisam只能管理索引在索引数据大于分配的资源时会由操作系统来cache数据文件依赖于操作系统的cache。innodb不管是索引还是数据都是自己来管理 思考上面这些问题可以让你找到合适的方向但那并不是绝对的。如果你需要事务处理或是外键那么InnoDB 可能是比较好的方式。如果你需要全文索引那么通常来说 MyISAM是好的选择因为这是系统内建的然而我们其实并不会经常地去测试两百万行记录。所以就算是慢一点我们可以通过使用Sphinx从InnoDB中获得全文索引。 数据的大小是一个影响你选择什么样存储引擎的重要因素大尺寸的数据集趋向于选择InnoDB方式因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短InnoDB可以利用事务日志进行数据恢复这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事InnoDB只需要几分钟。 操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如 COUNT() 在 MyISAM 表中会非常快而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在 MyISAM下会快一些但是updates 在InnoDB下会更快一些——尤其在并发量大的时候。 所以到底你检使用哪一个呢根据经验来看如果是一些小型的应用或项目那么MyISAM 也许会更适合。当然在大型的环境下使用 MyISAM 也会有很大成功的时候但却不总是这样的。如果你正在计划使用一个超大数据量的项目而且需要事务处理或外键支持那么你真的应该直接使用 InnoDB方式。但需要记住InnoDB 的表需要更多的内存和存储转换100GB 的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。 对于支持事务的InnoDB类型的表影响速度的主要原因是AUTOCOMMIT默认设置是打开的而且程序没有显式调用BEGIN 开始事务导致每插入一条都自动Commit严重影响了速度。可以在执行sql前调用begin多条sql形成一个事务即使autocommit打开也可以将大大提高性能。 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。 MyISAM MyISAM 是MySQL缺省存贮引擎 . 每张MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是 MYI (MYIndex) 引伸。 因为MyISAM相对简单所以在效率上要优于InnoDB..小型应用使用MyISAM是不错的选择. MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦 以下是一些细节和具体实现的差别 1.InnoDB不支持FULLTEXT类型的索引。 2.InnoDB 中不保存表的具体行数也就是说执行select count(*) from table时InnoDB要扫描一遍整个表来计算有多少行但是MyISAM只要简单的读出保存好的行数即可。注意的是当count(*)语句包含 where条件时两种表的操作是一样的。 3.对于AUTO_INCREMENT类型的字段InnoDB中必须包含只有该字段的索引但是在MyISAM表中可以和其他字段一起建立联合索引。 4.DELETE FROM table时InnoDB不会重新建立表而是一行一行的删除。 5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的解决方法是首先把InnoDB表改成MyISAM表导入数据后再改成InnoDB表但是对于使用的额外的InnoDB特性例如外键的表不适用。 另外InnoDB表的行锁也不是绝对的如果在执行一个SQL语句时MySQL不能确定要扫描的范围InnoDB表同样会锁全表例如 update table set num1 where name like “%aaa%” 任何一种表都不是万能的只用恰当的针对业务类型来选择合适的表类型才能最大的发挥MySQL的性能优势。 六、重复地总结一遍 1、MyISAM不支持事务InnoDB是事务类型的存储引擎当我们的表需要用到事务支持的时候那肯定是不能选择MyISAM了。 2、MyISAM只支持表级锁BDB支持页级锁和表级锁默认为页级锁而InnoDB支持行级锁和表级锁默认为行级锁 表级锁直接锁定整张表在锁定期间其他进程无法对该表进行写操作如果设置的是写锁那么其他进程读也不允许 MyISAM是表级锁定的存储引擎它不会出现死锁问题 对于write表锁定原理如下 如果表上没有锁在其上面放置一个写锁否则把锁定请求放在写锁队列中。 对于read表锁定原理如下 如果表上没有写锁定那么把一个读锁放在其上面否则把锁请求放在读锁定队列中 当一个锁定被释放时表可被写锁定队列中的线程得到然后才是读锁定队列中的线程。这意味着如果你在一个表上有许多更新那么你的SELECT语句将等到所有的写锁定线程执行完。 行级锁只对指定的行进行锁定其他进程还是可以对表中的其他行进行操作的。 行级锁是Mysql粒度最小的一种锁它能大大的减少数据库操作的冲突但是粒度越小实现成本也越大。 行级锁可能会导致“死锁”那到底是怎么导致的呢分析原因Mysql行级锁并不是直接锁记录而是锁索引。索引分为主键索引和非主键索引两种如果一条sql语句操作了主键索引那么Mysql就会锁定这个主键索引如果sql语句操作的是非主键索引那么Mysql会先锁定这个非主键索引再去锁定主键索引。 在UPDATE 和 DELETE操作时Mysql不仅会锁定所有WHERE 条件扫描过得索引还会锁定相邻的键值。 “死锁”举例分析 表TestID,STATE,TIME 主键索引ID 非主键索引STATE 当执行UPDATE STATE 1011 WHERE STATE1000 语句的时候会锁定STATE索引由于STATE 是非主键索引,所以Mysql还会去请求锁定ID索引 当另一个SQL语句与语句1几乎同时执行时“UPDATE STATE1010 WHERE ID1” 对于语句2 Mysql会先锁定ID索引由于语句2操作了STATE字段所以Mysql还会请求锁定STATE索引。这时。彼此锁定着对方需要的索引又都在等待对方释放锁定。所以出现了死锁的情况。 行级锁的优点 有许多线程访问不同的行时只存在少量的冲突。 回滚时只有少量的更改 可以长时间锁定单一的行 行级锁缺点 相对于页级锁和表级锁来说占用了更多的内存 当表的大部分行在使用时比页级锁和表级锁慢因为你必须获得更多的锁 当在大部分数据上经常使用GROUP BY操作肯定会比表级锁和页级锁慢。 页级锁表级锁速度快但是冲突多行级锁速度慢但冲突少页级锁就是他俩折中的一次锁定相邻的一组记录。 3、MyISAM引擎不支持外键InnoDB支持外键 4、MyISAM引擎的表在大量高并发的读写下会经常出现表损坏的情况 我们以前做的项目就遇到这个问题表的INSERT 和 UPDATE操作很频繁原来用的MyISAM引擎导致表隔三差五就损坏后来更换成了InnoDB引擎。 其他容易导致表损坏原因 服务器突然断电导致数据文件损坏强制关机mysqld未关闭情况下导致表损坏 mysqld进程在写入操作的时候被杀掉 磁盘故障 表损坏常见症状 查询表不能返回数据或返回部分数据 打开表失败 Can’t open file: ‘×××.MYI’ (errno: 145) 。 Error: Table p is marked as crashed and should be repaired 。 Incorrect key file for table: .... Try to repair it Mysql表的恢复 对于MyISAM表的恢复 可以使用Mysql自带的myisamchk工具 myisamchk -r tablename 或者 myisamchk -o tablename比前面的更保险 对表进行修复 5、对于count()查询来说MyISAM更有优势 因为MyISAM存储了表中的行数记录执行SELECT COUNT() 的时候可以直接获取到结果而InnoDB需要扫描全部数据后得到结果。 但是注意一点对于带有WHERE 条件的 SELECT COUNT()语句两种引擎的表执行过程是一样的都需要扫描全部数据后得到结果 6、 InnoDB是为处理巨大数据量时的最大性能设计它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。 7、MyISAM支持全文索引FULLTEXTInnoDB不支持 8、MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高 网上截取了前辈们测试结论 测试方法连续提交10个query 表记录总数38万 时间单位 s 引擎类型 MyISAM InnoDB 性能相差 count 0.0008357 3.0163 3609 查询主键 0.005708 0.1574 27.57 查询非主键 24.01 80.37 3.348 更新主键 0.008124 0.8183 100.7 更新非主键 0.004141 0.02625 6.338 插入 0.004188 0.3694 88.21 1加了索引以后对于MyISAM查询可以加快4 206.09733倍对InnoDB查询加快510.72921倍同时对MyISAM更新速度减慢为原来的1/2InnoDB的更 新速度减慢为原来的1/30。要看情况决定是否要加索引比如不查询的log表不要做任何的索引。 2如果你的数据量是百万级别的并且没有任何的事务处理那么用MyISAM是性能最好的选择。 3InnoDB表的大小更加的大用MyISAM可省很多的硬盘空间。 在我们测试的这个38w的表中表占用空间的情况如下 引擎类型 MyISAM InnoDB 数据 53,924 KB 58,976 KB 索引 13,640 KB 21,072 KB 占用总空间 67,564 KB 80,048 KB 另外一个176W万记录的表 表占用空间的情况如下 引擎类型 MyIsam InnorDB 数据 56,166 KB 90,736 KB 索引 67,103 KB 88,848 KB 占用总空间 123,269 KB 179,584 KB 七、性能对比 测试的版本是mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686)使用的是Innodb plugin 1.0.8官方称比built-in版本性能更好和默认的MyISAM。 测试机器是笔记本配置如下Intel 酷睿2双核 P86002G*2 DDR3 1066内存320G硬盘5400转。 测试一数据插入性能测试这里我分别对innodb_flush_log_at_trx_commit参数打开和关闭都测了了一下每次测试都是运行40s表中数字都是实际插入条数。 MyISAM Innodb (打开) Innodb (关闭) 单线程逐个插入 120000 60000 60000 4线程逐个插入 40000*4 20000*4 40000*4 单线程批量100条/次插入 3600*100 800*100 3000*100 单线程批量200条/次插入 1800*200 400*200 1600*200 可以发现批量插入的性能远高于单条插入但是一次批量的大小对性能影响不大。每条记录是否都刷新日志的参数对innodb性能的影响巨大。总体上来说MyISAM性能更优一点。这里有一点需要注意在插入测试过程中我对系统资源进行了监控发现MyISAM对系统资源占用很低但是Innodb对磁盘占用却很高应该是对事务控制多了很多需要记录的日志。 测试二数据读取性能测试。每次随机读取1000条记录反复进行读取。 MyISAM Innodb 单线程200次读取 5.7s 16.7s 4线程200次读取 12s 40.8s 可以看出MyISAM的读取性能非常恐怖性能差距在3倍的样子。 以上两个测试发现MyISAM在无事务的需求下几乎完胜但是要知道它是表锁Innodb是行锁那么在并发读写同时存在的情况下那结果会是怎么样呢 测试三两个线程并发写入2个线程并发读取。 MyISAM Innodb 逐个插入 写入40s10000*2 读取200次*214s 写入40s60000*2 读取200次*250s 批量100条/次插入 写入40s1000*100*2 读取200次*210s 写入40s1500*100*2 读取200次*250s 这下立刻显示出Innodb在并发情况下强劲的性能几乎没有什么性能衰减。而MyISAM单条插入速度变得非常慢批量插入也下降了40%性能。 总结一下在写多读少的应用中还是Innodb插入性能更稳定在并发情况下也能基本如果是对读取速度要求比较快的应用还是选MyISAM。 转载于:https://www.cnblogs.com/snake23/p/11068132.html
