MySQL2介绍及性能优化分析

MySQL介绍及性能优化MySQL基本介绍MySQL优化方式MySQL技巧分享Q&A目录索引什么是MySQLMySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQLAB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。MySQL官方网站：历史1979年，报表工具，数据引擎1996年，MySQL1.0(3.11.1)发布，支持SQL2000年，成立MySQLAB公司2008年1月，Sun公司以10亿美元收购MySQLAB公司2009年4月，Oracle公司以74亿美元收购Sun公司MySQL里程碑3.11.1Firstpublicrelease3.23集成BerkeleyDB,支持事务，抽象出StorageEngine4.0集成InnoDB4.1重大改进，子查询、unicode、c/s通信协议5.0storedprocedure、view、triggers、queryoptimizer5.1FileNDB、recordreplication......MySQL历史1979年，报表工具，数据引擎1996年，MySQL1.0(3.11.1)发布，支持SQL2000年，成立MySQLAB公司2008年1月，Sun公司以10亿美元收购MySQLAB公司2009年4月，Oracle公司以74亿美元收购Sun公司MySQL存储引擎MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。Merge：允许MySQLDBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。CREATETABLE`t1`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`log`varchar(45),PRIMARYKEY(`id`))ENGINE=MyISAM;CREATETABLE`t2`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`log`varchar(45),PRIMARYKEY(`id`))ENGINE=MyISAM;CREATETABLE`t`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`log`varchar(45)NOTNULL,PRIMARYKEY(`id`))ENGINE=MERGEUNION=(t1,t2)INSERT_METHOD=LAST;MERGE特点1、InnoDB存储引擎InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。MySQL4.1以后的版本中，InnoDB可以将每个表的数据和索引存放在单独的文件中。这样在复制备份崩溃恢复等操作中有明显优势。可以通过在my.cnf中增加innodb_file_per_table来开启这个功能。如下：[mysqld]innodb_file_per_tableInnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLEREAD(可重复读)，并且通过间隙锁(next-keylocking)策略防止幻读的出现。(事务和事务隔离级别是另一个大题目，各自网补吧)。InnoDB是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能。不过它的二级索引(secondaryindex，非主键索引)中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此表上的索引较多的话，主键应当尽可能的小。InnoDB的存储格式是平台独立的，可以将数据和索引文件从Intel平台复制到SunSPARC平台或其他平台。InnoDB通过一些机制和工具支持真正的热备份，MySQL的其他存储引擎不支持热备份。CREATETABLE`local_car_type`(`car_id`tinyint(4)NOTNULLAUTO_INCREMENTCOMMENT'车类型id',`car_type`varchar(16)DEFAULTNULLCOMMENT'类型名称',`describe`varchar(64)DEFAULTNULLCOMMENT'描述',`state`tinyint(4)DEFAULTNULLCOMMENT'状态',`car_name`varchar(100)DEFAULTNULL,`partner_id`int(11)DEFAULTNULLCOMMENT'partner_platforrm外键',`car_level`int(11)DEFAULT'1'COMMENT'级别越大级别越高享受优惠越大',PRIMARYKEY(`car_id`))ENGINE=FEDERATEDDEFAULTCHARSET=utf8CONNECTION='mysql://yijiayou:[e-mail]:3306/yijiayou/car_type';MERGE特点MyISAM特点MyISAMvsInnoDB•数据存储方式简单，使用B+Tree进行索引•使用三个文件定义一个表：.MYI.MYD.frm•少碎片、支持大文件、能够进行索引压缩•二进制层次的文件可以移植(LinuxWindows)•访问速度飞快，是所有MySQL文件引擎中速度最快的•不支持一些数据库特性，比如事务、外键约束等•Tablelevellock，性能稍差，更适合读取多的操作•表数据容量有限，一般建议单表数据量介于50w–200wMyISAMvsInnoDBMyISAM索引结构MyISAMvsInnoDBMyISAM存储结构MyISAMvsInnoDBInnoDB特点•使用TableSpace的方式来进行数据存储(ibdata1,ib_logfile0)•支持事务、外键约束等数据库特性•Rowslevellock,读写性能都非常优秀•能够承载大数据量的存储和访问•拥有自己独立的缓冲池，能够缓存数据和索引•在关闭自动提交的情况下，与MyISAM引擎速度差异不大InnoDB数据结构MyISAMvsInnoDBMyISAMvsInnoDB性能测试数据量/单位:万MyISAMInnoDB备注:my.cnf特殊选项插入：1w3秒219秒innodb_flush_log_at_trx_commit=1插入：10w29秒2092秒innodb_flush_log_at_trx_commit=1插入：100w287秒N/Ainnodb_flush_log_at_trx_commit=1插入：1w3秒3秒innodb_flush_log_at_trx_commit=0插入：10w30秒29秒innodb_flush_log_at_trx_commit=0插入：100w273秒423秒innodb_flush_log_at_trx_commit=0插入：1wN/A3秒innodb_flush_log_at_trx_commit=0innodb_buffer_pool_size=256M插入：10WN/A26秒innodb_flush_log_at_trx_commit=0innodb_buffer_pool_size=256M插入：100WN/A379秒innodb_flush_log_at_trx_commit=0innodb_buffer_pool_size=256MMyISAMvsInnoDB性能测试测试结果可以看出在MySQL5.0里面，MyISAM和InnoDB存储引擎性能差别并不是很大，针对InnoDB来说，影响性能的主要是innodb_flush_log_at_trx_commit这个选项，如果设置为1的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0效率能够看到明显提升，当然，同样你可以SQL中提交“SETAUTOCOMMIT=0”来设置达到好的性能。同时也可以看出值得使用InnoDB来替代MyISAM引擎来进行开发，毕竟InnoDB有多数据库特性、更良好的数据存储性能和查询性能如果innodb_flush_log_at_trx_commit设置为0，logbuffer将每秒一次地写入logfile中，并且logfile的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。如果innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把logbuffer的数据写入logfile，并且flush(刷到磁盘)中去.如果innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把logbuffer的数据写入logfile.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次flush(刷到磁盘)操作。由于进程调度策略问题,这个“每秒执行一次flush(刷到磁盘)操作”并不是保证100%的“每秒”。sync_binlogsync_binlog的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binarylog。当sync_binlog=N(N0)，MySQL在每写N次二进制日志binarylog时，会使用fdatasync()函数将它的写二进制日志binarylog同步到磁盘中去。注:如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。MySQL优化方式系统优化：硬件、架构服务优化应用优化MySQL优化方式影响性能的因素应用程序查询事务管理数据库设计数据分布网络操作系统硬件使用好的硬件，更快的硬盘、大内存、多核CPU，专业的存储服务器（NAS、SAN）设计合理架构，如果MySQL访问频繁，考虑Master/Slave读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助MySQL缓解访问压力系统优化配置合理的MySQL服务器，尽量在应用本身达到一个MySQL最合理的使用针对MyISAM或InnoDB不同引擎进行不同定制性配置针对不同的应用情况进行合理配置针对my.cnf进行配置，后面设置是针对内存为2G的服务器进行的合理设置服务优化MySQL配置原则服务优