MySQL性能优化

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引擎（可为快速创建定制的插件式存储引擎提供帮助）。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有多数据库特性、更良好的数据存储性能和查询性能MySQL优化方式系统优化：硬件、架构服务优化应用优化MySQL优化方式影响性能的因素应用程序查询事务管理数据库设计数据分布网络操作系统硬件使用好的硬件，更快的硬盘、大内存、多核CPU，专业的存储服务器（NAS、SAN）设计合理架构，如果MySQL访问频繁，考虑Master/Slave读写分离；数据库分表、数据库切片（分布式），也考虑使用相应缓存服务帮助MySQL缓解访问压力系统优化配置合理的MySQL服务器，尽量在应用本身达到一个MySQL最合理的使用针对MyISAM或InnoDB不同引擎进行不同定制性配置针对不同的应用情况进行合理配置针对my.cnf进行配置，后面设置是针对内存为2G的服务器进行的合理设置服务优化MySQL配置原则服务优化公共选项选项缺省值推荐值说明max_connections1001024MySQL服务器同时处理的数据库连接的最大数量query_cache_size0(不打开）16M查询缓存区的最大长度，按照当前需求，一倍一倍增加，本选项比较重要sort_buffer_size512K16M每个线程的排序缓存大小，一般按照内存可以设置为2M以上，推荐是16M，该选项对排序orderby，groupby起作用record_buffer128K16M每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区，可以设置为2M以上table_cache64512为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。服务优化MyISAM选项选项缺省值推荐值说明key_buffer_size8M256M用来存放索引区块的缓存值,建议128M以上，不要大于内存的30%read_buffer_size128K16M用来做MyISAM表全表扫描的缓冲大小.为从数据表顺序读取数据的读操作保留的缓存区的长度myisam_sort_buffer_size16M128M设置,恢复,修改表的时候使用的缓冲大小，值不要设的太大服务优化InnoDB选项选项缺省值推荐值说明innodb_buffer_pool_size32M1GInnoDB使用一个缓冲池来保存索引和原始数据,这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少，一般是内存的一半，不超过2G，否则系统会崩溃，这个参数非常重要innodb_additional_mem_pool_size2M128MInnoDB用来保存metadata信息,如果内存是4G，最好本值超过200Minnodb_flush_log_at_trx_commit100代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘;1为执行完没执行一条SQL马上commit;2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.对速度影响比较大，同时也关系数据完整性innodb_log_file_size8M256M在日志组中每个日志文件的大小,一般是innodb_buffer_pool_size的25%，官方推荐是innodb_buffer_pool_size的40-50%,设置大一点来避免在日志文件覆写上不必要的缓冲池刷新行为innodb_log_buffer_size128K8M用来缓冲日志数据的缓冲区的大小.推荐是8M，官方推荐该值小于16M，最好是1M-8M之间设计合理的数据表结构：适当的数据冗余对数据表建立合适有效的数据库索引数据查询：编写简洁高效的SQL语句应用优化应用优化方式应用优化表结构设计原则选择合适的数据类型：如果能够定长尽量定长使用ENUM而不是VARCHAR,ENUM类型是非常快和紧凑的，在实际上，其保存的是TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。不要使用无法加索引的类型作为关键字段，比如text类型为了避免联表查询，有时候可以适当的数据冗余，比如邮箱、姓名这些不容易更改的数据选择合适的表引擎，有时候MyISAM适合，有时候InnoDB适合为保证查询性能，最好每个表都建立有auto_increment字段，建立合适的数据库索引最好给每个字段都设定default值应用优化索引建立原则（一）一般针对数据分散的关键字进行建立索引，比如ID、QQ，像性别、状态值等等建立索引没有意义字段唯一，最少，不可为null对大数据量表建立聚集索引，避免更新操作带来的碎片。尽量使用短索引，一般对int、char/varchar、date/time等类型的字段建立索引需要的时候建立联合索引，但是要注意查询SQL语句的编写谨慎建立unique类型的索引（唯一索引）大文本字段不建立为索引，如果要对大文本字段进行检索，可以考虑全文索引频繁更新的列不适合建立索引应用优化索引建立原则（二）orderby字句中的字段，where子句中字段，最常用的sql语句中字段，应建立索引。唯一性约束，系统将默认为改字段建立索引。对于只是做查询用的数据库索引越多越好，但对于在线实时系统建议控制在5个以内。索引不仅能提高查询SQL性能，同时也可以提高带where字句的update，DeleteSQL性能。Decimal类型字段不要单独建立为索引，但覆盖索引可以包含这些字段。只有建立索引以后，表内的行才按照特地的顺序存储，按照需要可以是asc或desc方式。如果索引由多个字段组成将最用来查询过滤的字段放在前面可能会有更好的性能。应用优化MSSQL执行顺序(8)SELECT(9)DISTINCT(11)TOP_specificationselect_list(1)FROMleft_table