mysql性能优化

MySQLMySQLMySQLMySQL介绍和优化分享2009-07作者：heiyeluren(黑夜路人)博客：MySQLMySQL基本介绍基本介绍MySQLMySQL优化方式优化方式MySQLMySQL技巧分享技巧分享QQ&&AA目录索引目录索引MySQLMySQL基本介绍基本介绍什么是什么是MySQLMySQL　　　　MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL是一个小型关系型数据库管理系统，开发者为瑞典是一个小型关系型数据库管理系统，开发者为瑞典MySQLABMySQLABMySQLABMySQLABMySQLABMySQLABMySQLABMySQLAB公司。目前公司。目前MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL被广泛地应用在被广泛地应用在InternetInternetInternetInternetInternetInternetInternetInternet上的中小型网上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL作为网站数据库。作为网站数据库。MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL官方网站：官方网站：://://谁在用谁在用MySQLMySQLMySQLMySQL历史历史••19791979197919791979197919791979年，年，报表工具，数据引擎报表工具，数据引擎••19961996199619961996199619961996年，年，MySQL1.0(3.11.1)MySQL1.0(3.11.1)MySQL1.0(3.11.1)MySQL1.0(3.11.1)MySQL1.0(3.11.1)MySQL1.0(3.11.1)MySQL1.0(3.11.1)MySQL1.0(3.11.1)发布，支持发布，支持SQLSQLSQLSQLSQLSQLSQLSQL••20002000200020002000200020002000年，成立年，成立MySQLABMySQLABMySQLABMySQLABMySQLABMySQLABMySQLABMySQLAB公司公司••20082008200820082008200820082008年年11111111月，月，SunSunSunSunSunSunSunSun公司以公司以1010101010101010亿美元收购亿美元收购MySQLABMySQLABMySQLABMySQLABMySQLABMySQLABMySQLABMySQLAB公司公司••20092009200920092009200920092009年年44444444月，月，OracleOracleOracleOracleOracleOracleOracleOracle公司以公司以7474747474747474亿美元收购亿美元收购SunSunSunSunSunSunSunSun公司公司MySQLMySQL里程碑里程碑••3.11.1Firstpublicrelease3.11.1Firstpublicrelease3.11.1Firstpublicrelease3.11.1Firstpublicrelease3.11.1Firstpublicrelease3.11.1Firstpublicrelease3.11.1Firstpublicrelease3.11.1Firstpublicrelease••3.233.233.233.233.233.233.233.23集成集成BerkeleyBerkeleyBerkeleyBerkeleyBerkeleyBerkeleyBerkeleyBerkeleyDB,DB,DB,DB,DB,DB,DB,DB,支持事务，抽象出支持事务，抽象出StorageStorageStorageStorageStorageStorageStorageStorageEngineEngineEngineEngineEngineEngineEngineEngine••4.04.04.04.04.04.04.04.0集成集成InnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDB••4.14.14.14.14.14.14.14.1重大改进，子查询、重大改进，子查询、unicodeunicodeunicodeunicodeunicodeunicodeunicodeunicode、、c/sc/sc/sc/sc/sc/sc/sc/s通信协议通信协议••5.0stored5.0stored5.0stored5.0stored5.0stored5.0stored5.0stored5.0storedprocedureprocedureprocedureprocedureprocedureprocedureprocedureprocedure、、viewviewviewviewviewviewviewview、、triggerstriggerstriggerstriggerstriggerstriggerstriggerstriggers、、queryqueryqueryqueryqueryqueryqueryqueryoptimizeroptimizeroptimizeroptimizeroptimizeroptimizeroptimizeroptimizer••5.1File5.1File5.1File5.1File5.1File5.1File5.1File5.1FileNDBNDBNDBNDBNDBNDBNDBNDB、、recordrecordrecordrecordrecordrecordrecordrecordreplication......replication......replication......replication......replication......replication......replication......replication......MySQLMySQL架构架构MySQLMySQL存储引擎比较存储引擎比较MyISAMMyISAMMyISAMMyISAMMyISAMMyISAMMyISAMMyISAM特点特点特点特点特点特点特点特点MyISAMvsInnoDBMyISAMvsInnoDB••数据存储方式简单，使用数据存储方式简单，使用B+TreeB+TreeB+TreeB+TreeB+TreeB+TreeB+TreeB+Tree进行索引进行索引••使用三个文件定义一个表：使用三个文件定义一个表：.MYI.MYD..MYI.MYD..MYI.MYD..MYI.MYD..MYI.MYD..MYI.MYD..MYI.MYD..MYI.MYD.frmfrmfrmfrmfrmfrmfrmfrm••少碎片、支持大文件、能够进行索引压缩少碎片、支持大文件、能够进行索引压缩••二进制层次的文件可以移植二进制层次的文件可以移植(Linux(Linux(Linux(Linux(Linux(Linux(Linux(LinuxWindows)Windows)Windows)Windows)Windows)Windows)Windows)Windows)••访问速度飞快，是所有访问速度飞快，是所有MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL文件引擎中速度最快的文件引擎中速度最快的••不支持一些数据库特性，比如不支持一些数据库特性，比如事务、外键约束等事务、外键约束等••TablelevellockTablelevellockTablelevellockTablelevellockTablelevellockTablelevellockTablelevellockTablelevellock，性能稍差，更适合读取多的操作，性能稍差，更适合读取多的操作••表数据容量有限，一般建议单表数据量介于表数据容量有限，一般建议单表数据量介于50w50w50w50w50w50w50w50w––200w200w200w200w200w200w200w200w••增删查改以后要使用增删查改以后要使用myisamchkmyisamchkmyisamchkmyisamchkmyisamchkmyisamchkmyisamchkmyisamchk检查优化表检查优化表MyISAMMyISAMMyISAMMyISAMMyISAMMyISAMMyISAMMyISAM存储结构存储结构存储结构存储结构存储结构存储结构存储结构存储结构MyISAMvsInnoDBMyISAMvsInnoDBMyISAMMyISAMMyISAMMyISAMMyISAMMyISAMMyISAMMyISAM索引结构索引结构索引结构索引结构索引结构索引结构索引结构索引结构MyISAMvsInnoDBMyISAMvsInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDB特点特点特点特点特点特点特点特点MyISAMvsInnoDBMyISAMvsInnoDB••使用使用TableSpaceTableSpaceTableSpaceTableSpaceTableSpaceTableSpaceTableSpaceTableSpace的方式来进行数据存储的方式来进行数据存储((((((((ibdata1,ib_logfile0ibdata1,ib_logfile0ibdata1,ib_logfile0ibdata1,ib_logfile0ibdata1,ib_logfile0ibdata1,ib_logfile0ibdata1,ib_logfile0ibdata1,ib_logfile0))))))))••支持支持事务、外键约束等数据库特性事务、外键约束等数据库特性••Rowslevellock,Rowslevellock,Rowslevellock,Rowslevellock,Rowslevellock,Rowslevellock,Rowslevellock,Rowslevellock,读写性能都非常优秀读写性能都非常优秀••能够承载大数据量的存储和访问能够承载大数据量的存储和访问••拥有自己独立的缓冲池，能够缓存数据和索引拥有自己独立的缓冲池，能够缓存数据和索引••在关闭自动提交的情况下，与在关闭自动提交的情况下，与MyISAMMyISAMMyISAMMyISAMMyISAMMyISAMMyISAMMyISAM引擎速度差异不大引擎速度差异不大InnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDBInnoDB数据结构数据结构数据结构数据结构数据结构数据结构数据结构数据结构MyISAMvsInnoDBMyISAMvsInnoDBMyISAMvsInnoDBMyISAMvsInnoDB性能测试性能测试innodb_flush_log_at_trx_commit=1N/A287秒插入：100winnodb_flush_log_at_trx_commit=0innodb_buffer_pool_