Mysql性能优化初探

,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Mysql,性能优化初探,V1.0,撰稿人：,Lily Wu,2010-10-28,1,Agenda,数据库设计优化,查询优化,服务器端优化,mysql优化参数公式,查询缓存,2,数据库设计优化,使表在磁盘上占据的空间尽可能小,使用正确合适的类型，不要将数字存储为字符串。,尽可能地使用最有效,(,最小,),的数据类型。,MySQL,有很多节省磁盘空间和内存的专业化类型。,尽可能使用较小的整数类型使表更小。例如，,MEDIUMINT,经常比,INT,好一些，因为,MEDIUMINT,列使用的空间要少,25%,。,如果可能，声明列为,NOT NULL,。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要,NULL,，应该毫无疑问使用它，只是避免 默认地在所有列上有它。复合索引中只要有一列含有,NULL,值，那么这一列对于此复合索引就是无效的。,对于,MyISAM,表，如果没有任何变长列,(VARCHAR,、,TEXT,或,BLOB,列,),，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。即使你已经用,CREATE,选项让,VARCHAR,列,ROW_FORMAT=fixed,，也可以提示想使用固定长度的行。,使用,sample character set,，例如,latin1,。尽量少使用,utf-8,，因为,utf-8,占用的空间是,latin1,的,3,倍。可以在不需要使用,utf-8,的字段上面使用,latin1,，例如,mail,，,url,等。,3,数据库设计优化,使用索引应该注意 以下几点,1) MySQL,只会使用前缀，例如,key(a, b) where b=5,将使用不到索引。,2),要选择性的使用索引。在变化很少的列上使用索引并不是很好，例如性别列。,3),在,Unique,列上定义,Unique index,。,4),避免建立使用不到的索引。,5),在,Btree index,中（,InnoDB,使用,Btree,），可以在需要排序的列上建立索引。,6),避免重复的索引。,7),避免在已有索引的前缀上建立索引。例如：如果存在,index,（,a,，,b,）则去掉,index,（,a,）。,8),控制单个索引的长度。使用,key,（,name,（,8,）在数据的前面几个字符建立索引。,9),越是短的键值越好，最好使用,integer,。,10),在查询中要使用到索引（使用,explain,查看），可以减少读磁盘的次数，加速读取数据。,11),相近的键值比随机好。,Auto_increment,就比,uuid,好。,12) Optimize table,可以压缩和排序,index,，注意不要频繁运行。,13) Analyze table,可以更新数据。,4,查询语句的优化,查询语句的优化是一个,Case by case,的问题，不同的,sql,有不同的优化方案，在这里我只列出一些通用的技巧,在有,index,的情况下，尽量保证查询使用了正确的,index,。可以使用,EXPLAIN select ,查看结果，分析查询。,查询时使用匹配的类型。例如,select * from a where id=5,， 如果这里,id,是字符类型，同时有,index,，这条查询则使用不到,index,，会做全表扫描，速度会很慢。正确的应该是, where id=”5”,，加上引号表明类型是字符。,使用,-log-slow-queries long-query-time=2,查看查询比较慢的语句。然后使用,explain,分析查询，做出优化。,5,查询语句的优化,Explain,看,Row,列检索的行数,看,Type,列,Join,在,Join,表的时候使用相当类型的例，并将其索引,复杂的权限设置影响所有的查询语句。,当只要一行数据时使用,LIMIT 1,（像只是为了判断表里是否不某种数据）,避免,SELECT *,6,服务器端优化,MySQL,安装,MySQL,有很多发行版本，最好使用,MySQL AB,发布的二进制版本。也可以下载源代码进行编译安装，但是编译器和类库的一些,bug,可能会使编译完成的,MySQL,存在潜在的问题。,如果安装,MySQL,的服务器使用的是,Intel,公司的处理器，可以使用,intel c+,编译的版本，在,Linux World2005,的一篇,PPT,中提到，使用,intel C+,编译器编译的,MySQL,查询速度比正常版本快,30%,左右。,Intel c+,编译版本可以在,MySQL,官方网站下载。,7,服务器端优化,服务器设置优化,-character-set,：如果是单一语言使用简单的,character set,例如,latin1,。尽量少用,Utf-8,，,utf-8,占用空间较多。,-memlock,：锁定,MySQL,只能运行在内存中，避免,s,，但是如果内存不够时有可能出现错误。,-max_allowed_packet,：要足够大，以适应比较大的,SQL,查询，对性能没有太大影响，主要是避免出现,packet,错误。,-max_connections,：,server,允许的最大连接。太大的话会出现,out of memory,。,-table_cache,：,MySQL,在同一时间保持打开的,table,的数量。打开,table,开销比较大。一般设置为,512,。,-query_cache_size,： 用于缓存查询的内存大小。,-datadir,：,mysql,存放数据的根目录，和安装文件分开在不同的磁盘可以提高一点性能。,8,mysql,优化参数公式,一 、连接数,Max_used_connections / max_connections * 100% 85%,最大连接数占上限连接数的,85,左右，如果发现比例在,10%,以下，,MySQL,服务器连接数上限设置的过高了。,二、,Key_buffer_size,1,、 索引未命中缓存的概率,key_cache_miss_rate,Key_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%,9,mysql,优化参数公式,三 、临时表,Created_tmp_disk_tables / Created_tmp_tables * 100% = 85%,Open_tables / table_open_cache * 100% = 95%,10,mysql,优化参数公式,五、进程使用情况,Threads created,表示创建过的线程数，如果发现,Threads_created,值过大的话，表明,MySQL,服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中,thread_cache_size,值,.,六 、查询缓存,(query cache),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,，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。,11,mysql,优化参数公式,排序使用情况,Sort_merge_passes,太大 说明,Sort_buffer_size,肯小了,文件打开数,(open_files),比较合适的设置：,Open_files / open_ * 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);,13,