Mysql性能优化初探.ppt

Mysql性能优化初探V1.0撰稿人：Lily Wu2010-10-281技术文献智网达科技版权所有Agendan数据库设计优化n查询优化n服务器端优化nmysql优化参数公式n查询缓存数据库设计优化n使表在磁盘上占据的空间尽可能小使表在磁盘上占据的空间尽可能小使用正确合适的类型，不要将数字存储为字符串。尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%。如果可能，声明列为NOTNULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免默认地在所有列上有它。复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。对于MyISAM表，如果没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定长度的行。使用samplecharacterset，例如latin1。尽量少使用utf-8，因为utf-8占用的空间是latin1的3倍。可以在不需要使用utf-8的字段上面使用latin1，例如mail，url等。数据库设计优化n使用索引应该注意使用索引应该注意 以下几点以下几点1)MySQL只会使用前缀，例如key(a,b)whereb=5将使用不到索引。2)要选择性的使用索引。在变化很少的列上使用索引并不是很好，例如性别列。3)在Unique列上定义Uniqueindex。4)避免建立使用不到的索引。5)在Btreeindex中（InnoDB使用Btree），可以在需要排序的列上建立索引。6)避免重复的索引。7)避免在已有索引的前缀上建立索引。例如：如果存在index（a，b）则去掉index（a）。8)控制单个索引的长度。使用key（name（8）在数据的前面几个字符建立索引。9)越是短的键值越好，最好使用integer。10)在查询中要使用到索引（使用explain查看），可以减少读磁盘的次数，加速读取数据。11)相近的键值比随机好。Auto_increment就比uuid好。12)Optimizetable可以压缩和排序index，注意不要频繁运行。13)Analyzetable可以更新数据。查询语句的优化n查询语句的优化是一个Casebycase的问题，不同的sql有不同的优化方案，在这里我只列出一些通用的技巧在有index的情况下，尽量保证查询使用了正确的index。可以使用EXPLAINselect查看结果，分析查询。查询时使用匹配的类型。例如select*fromawhereid=5，如果这里id是字符类型，同时有index，这条查询则使用不到index，会做全表扫描，速度会很慢。正确的应该是whereid=”5”，加上引号表明类型是字符。使用-log-slow-querieslong-query-time=2查看查询比较慢的语句。然后使用explain分析查询，做出优化。查询语句的优化nExplain看Row列检索的行数看Type列nJoin在Join表的时候使用相当类型的例，并将其索引n复杂的权限设置影响所有的查询语句。n当只要一行数据时使用LIMIT1（像只是为了判断表里是否不某种数据）n避免SELECT*服务器端优化nMySQL安装MySQL有很多发行版本，最好使用MySQLAB发布的二进制版本。也可以下载源代码进行编译安装，但是编译器和类库的一些bug可能会使编译完成的MySQL存在潜在的问题。如果安装MySQL的服务器使用的是Intel公司的处理器，可以使用intelc+编译的版本，在LinuxWorld2005的一篇PPT中提到，使用intelC+编译器编译的MySQL查询速度比正常版本快30%左右。Intelc+编译版本可以在MySQL官方网站下载。服务器端优化n服务器设置优化-character-set：如果是单一语言使用简单的characterset例如latin1。尽量少用Utf-8，utf-8占用空间较多。-memlock：锁定MySQL只能运行在内存中，避免swapping，但是如果内存不够时有可能出现错误。-max_allowed_packet：要足够大，以适应比较大的SQL查询，对性能没有太大影响，主要是避免出现packet错误。-max_connections：server允许的最大连接。太大的话会出现outofmemory。-table_cache：MySQL在同一时间保持打开的table的数量。打开table开销比较大。一般设置为512。-query_cache_size：用于缓存查询的内存大小。-datadir：mysql存放数据的根目录，和安装文件分开在不同的磁盘可以提高一点性能。mysql优化参数公式一、连接数Max_used_connections/max_connections*100%85%最大连接数占上限连接数的85左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。二、Key_buffer_size1、索引未命中缓存的概率key_cache_miss_rateKey_reads/Key_read_requests*100%key_cache_miss_rate在0.1%以下都很好（每1000个请求有一个直接读硬盘），如果key_cache_miss_rate在0.01%以下的话，key_buffer_size分配的过多，可以适当减少。2、key_blocks_*参数：Key_blocks_unused未使用的缓存簇(blocks)数Key_blocks_used曾经用到的最大的blocks数Key_blocks_unused太小要么增加key_buffer_size，要么就是过渡索引了，把缓存占满了。比较理想的设置：Key_blocks_used/(Key_blocks_unused+Key_blocks_used)*100%80%mysql优化参数公式三、临时表Created_tmp_disk_tables/Created_tmp_tables*100%=85%Open_tables/table_open_cache*100%=95%mysql优化参数公式五、进程使用情况Threadscreated表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值.六、查询缓存(querycache)query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。查询缓存碎片率=Qcache_free_blocks/Qcache_total_blocks*100%50的话说明query_cache_size可能有点小，要不就是碎片太多。查询缓存命中率=(Qcache_hits-Qcache_inserts)/Qcache_hits*100%示例服务器查询缓存碎片率20.46，查询缓存利用率62.26，查询缓存命中率1.94，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。mysql优化参数公式n排序使用情况Sort_merge_passes太大说明Sort_buffer_size肯小了n文件打开数(open_files)比较合适的设置：Open_files/open_files_limit*100%5000，最好采用InnoDB引擎表扫描率Handler_read_rnd_next/Com_select=CURDATE();4/开启查询缓存 5$today=date(Y-m-d);6$r=mysql_query(SELECT username FROM user WHERE signup_date=$today);