mysql性能优化

MySQL优化MySQL优化方式MySQL技巧分享MySQL函数目录索引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类型的索引（唯一索引）大文本字段不建立为索引，如果要对大文本字段进行检索，可以考虑全文索引频繁更新的列不适合建立索引一般建议每条记录最好有一个能快速定位的独一无二定位的唯一标示（索引）不要过度索引，单表建立的索引不要超过5个，否则更新索引将很耗时应用优化索引建立原则(二)1.orderby字句中的字段，where子句中字段，最常用的sql语句中字段，应建立索引。2.唯一性约束，系统将默认为改字段建立索引。3.对于只是做查询用的数据库索引越多越好，但对于在线实时系统建议控制在5个以内。4.索引不仅能提高查询SQL性能，同时也可以提高带where字句的update，DeleteSQL性能。5.Decimal类型字段不要单独建立为索引，但覆盖索引可以包含这些字段。6.只有建立索引以后，表内的行才按照特地的顺序存储，按照需要可以是asc或desc方式。7.如果索引由多个字段组成将最用来查询过滤的字段放在前面可能会有更好的性能。应用优化MYSQL执行顺序(8)SELECT(9)DISTINCT(11)TOP_specificationselect_list(1)FROMleft_table(3)join_typeJOINright_table(2)ONjoin_condition(4)WHEREwhere_condition(5)GROUPBYgroup_by_list(6)WITH{CUBE|ROLLUP}(7)HAVINGhaving_condition(10)ORDERBYorder_by_list应用优化编写高效的SQL(一)能够快速缩小结果集的WHERE条件写在前面，如果有恒量条件，也尽量放在前面尽量避免使用GROUPBY、DISTINCT、OR、IN等语句的使用，避免使用联表查询和子查询，因为将使执行效率大大下降能够使用索引的字段尽量进行有效的合理排列，如果使用了联合索引，请注意提取字段的前后顺序针对索引字段使用,=,=,,=,IFNULL和BETWEEN将会使用索引，如果对某个索引字段进行LIKE查询，使用LIKE‘%abc%’不能使用索引，使用LIKE‘abc%’将能够使用索引如果在SQL里使用了MySQL部分自带函数，索引将失效，同时将无法使用MySQL的QueryCache，比如LEFT(),SUBSTR(),TO_DAYS()DATE_FORMAT(),等，如果使用了OR或IN，索引也将失效使用Explain语句来帮助改进我们的SQL语句应用优化编写高效的SQL(二)1.不要在where子句中的“=”左边进行算术或表达式运算，否则系统将可能无法正确使用索引selectidfromuinfo_jifenwherejifen/6010000;优化后:Selectidfromuinfo_jifenwherejifen600000;2.尽量不要在where条件中使用函数，否则将不能使用索引(selectuidfromimidwheredatediff(create_time,'2011-11-22')=0优化后selectuidfromimidwherecreate_time='2011-11-21‘andcreate_time‘2011-11-23’;)3.避免使用select*,只取需要的字段4.对于大数据量的查询，尽量避免在SQL语句中使用orderby字句，避免额为的开销5.如果插入的数据量很大，用selectinto替代insertinto能带来更好的性能6.采用连接操作，避免过多的子查询，产生的CPU和IO开销7.只关心需要的表和满足条件的数据8.适当使用临时表或表变量9.对于连续的数值，使用between代替in11.尽量不用触发器，10.where字句中尽量不要使用CASE条件特别是在大数据表上应用优化编写高效的SQL(三)1.更新触发器如果不是所有情况下都需要触发，应根据业务需要加上必要判断条件2.使用unionall操作代替OR操作，注意此时需要注意一点查询条件可以使用聚集索引，如果是非聚集索引将起到相反的结果3.当只要一行数据时使用LIMIT14.尽可能的使用NOTNULL填充数据库5.拆分大的DELETE或INSERT语句,使用LIMIT条件是一个好的方法while(1){//每次只做1000条mysql_query(DELETEFROMlogsWHERElog_date='2009-11-01'LIMIT1000);if(mysql_affected_rows()==0){//没得可删了，退出！break;}//每次都要休息一会儿usleep(50000);}6.批量提交SQL语句MySQL技巧分享MySQL技巧分享常用技巧使用SHOWPROCESSLIST来查看当前MySQL服务器线程执行情况，是否锁表，查看相应的SQL语句设置my.ini中的long-query-time和log-slow-queries能够记录服务器哪些SQL执行速度比较慢使用DESCTABLExxx来查看表结构，使用SHOWINDEXFROMxxx来查看表索引使用LOADDATA导入数据比INSERTINTO快多了SELECTCOUNT(*)FROMTbl在InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时，两种表的操作是一样的。MySQL技巧分享SELECT查询的速度要想使一个较慢速SELECT...WHERE更快，应首先检查是否能增加一个索引。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。为了帮助MySQL更好地优化查询，在一个装载数据后的表上运行ANALYZETABLE或myisamchk--analyze。这样为每一个索引更新指出有相同值的行的平均行数的值（当然，如果只有一个索引，这总是1。）MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOWINDEXFROMtbl_name并检查Cardinality值来检查表分析结果。myisamchk--description--verbose可以显示索引分布信息。要想根据一个索引排序一个索引和数据，使用myisamchk--sort-index--sort-records=1（如果你想要在索引1上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！MySQL技巧分享使用Explain语句来帮助改进我们的SQL语句如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。EXPLAIN的每个输出行提供一个表的相关信息，并且每个行包括下面的列:idSELECT识别符。这是SELECT的查询序列号select_typeSELECT类型table输出的行所引用的表type联接类型possible_keyspossible_keys列指出M