数据库优化

数据库优化对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。一般来说，要保证数据库的效率，要做好以下四个方面的工作：数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统，这个顺序也表现了这四个工作对性能影响的大小。一、合理的硬件资源和操作系统如果你的机器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql读写分离如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。二、数据库参数配置最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大innodb_additional_mem_pool_size=64Minnodb_buffer_pool_size=5G对于myisam，需要调整key_buffer_size当然调整参数还是要看状态，用showstatus语句可以看到当前状态，以决定改调整哪些参数Cretated_tmp_disk_tables增加tmp_table_sizeHandler_read_key高表示索引正确Handler_read_rnd高表示索引不正确Key_reads/Key_read_requests应小于0.01计算缓存损失率，增加Key_buffer_sizeOpentables/Open_tables增加table_cacheselect_full_join没有实用索引的链接的数量。如果不为0，应该检查索引。select_range_check如果不为0，该检查表索引。sort_merge_passes排序算法已经执行的合并的数量。如果该值较大，应增加sort_buffer_sizetable_locks_waited不能立即获得的表的锁的次数，如果该值较高，应优化查询Threads_created创建用来处理连接的线程数。如果Threads_created较大，要增加thread_cache_size值。缓存访问率的计算方法Threads_created/Connections。例：当解决了上述服务器硬件制约因素后，让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。由于my.cnf文件的优化设置是与服务器硬件配置息息相关的，因而我们指定一个假想的服务器硬件环境：CPU：2颗IntelXeon2.4GHz内存：4GBDDR硬盘：SCSI73GB(很常见的2U服务器)。下面，我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明：以下只列出my.cnf文件中[mysqld]段落中的内容，其他段落内容对MySQL运行性能影响甚微，因而姑且忽略。[mysqld]port=3306serverid=1socket=/tmp/mysql.sockskip-locking#避免MySQL的外部锁定，减少出错几率增强稳定性。skip-name-resolve#禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！back_log=384#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。key_buffer_size=256M#key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！max_allowed_packet=4Mthread_stack=256Ktable_cache=128Ksort_buffer_size=6M#查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100×6＝600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。read_buffer_size=4M#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。join_buffer_size=8M#联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。myisam_sort_buffer_size=64Mtable_cache=512thread_cache_size=64query_cache_size=64M#指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。tmp_table_size=256Mmax_connections=768#指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现TooManyConnections的错误提示，则需要增大该参数值。max_connect_errors=10000000wait_timeout=10#指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。thread_concurrency=8#该参数取值为服务器逻辑CPU数量*2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4*2=8skip-networking#开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！table_cache=1024#物理内存越大,设置就越大.默认为2402,调到512-1024最佳innodb_additional_mem_pool_size=4M#默认为2Minnodb_flush_log_at_trx_commit=1#设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1innodb_log_buffer_size=2M#默认为1Minnodb_thread_concurrency=8#你的服务器CPU有几个就设置为几,建议用默认一般为8key_buffer_size=256M#默认为218，调到128最佳tmp_table_size=64M#默认为16M，调到64-256最挂read_buffer_size=4M#默认为64Kread_rnd_buffer_size=16M#默认为256Ksort_buffer_size=32M#默认为256Kthread_cache_size=120#默认为60query_cache_size=32M值得注意的是：很多情况需要具体情况具体分析。如果Key_reads太大，则应该把my.cnf中Key_buffer_size变大，保持Key_reads/Key_read_requests至少1/100以上，越小越好。三、数据库设计用尽量少的存储空间来存数一个字段的数据;能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);IP地址最好使用int类型;固定长度的类型最好使用char,例如：邮编;能使用tinyint就不要使用smallint,int;最好给每个字段一个默认值,最好不能为null;MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。•用好数值类型tinyint(1Byte)smallint(2Byte)mediumint(3Byte)int(4Byte)bigint(8Byte)•字符转化为数字(用int而不是char(15)存储ip)•避免使用NULL字段NULL字段很难查询优化；NULL字段的索引需要额外空间；NULL字段的复合索引无效；•少用text/blobvarchar的性能会比text高很多；实在避免不了blob，请拆表；•文件、图片等大文件用文件系统存储，不用数据库•控制列数量字段少而精，字段数建议在20以内；•控制单表数据量int型不超过1000w，含char则不超过500w；合理分表；限制单库表数量在300以内；•平衡范式与冗余效率优先；往往牺牲范式；•不在数据库做运算cpu计算务必移至业务层；•数据库引擎的选择四、sql语句的优化1)创建索引2)使用连接（JOIN）来代替子查询(Sub-Queries)3)like语句操作一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like“%aaa%”不会使用索引而like“aaa%”可以使用索引。4)不要在列上进行运算select*fromuserswhereYEAR(adddate)2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成select*fromuserswhereadddate‘2007-01-01';5)不使用NOTIN和操作NOTIN和操作都不会使用索引将进行全表扫描。NOTIN可以NOTEXISTS代替，id3则可使用id3orid3来代替。6)不用select*消耗cpu，io，内存，带宽；这种程序不具有扩展性；7)OR改写为IN()8)limit高效分页limit越大，效率越低selectidfromtlimit10000,10;=selectidfromtwhereid10000limit10;9)使用loaddata导数据loaddata比insert快约20倍；10）使用性能分析工具showprofile;mysqlsla;mysqldumpslow;explain;showslowlog;showprocesslist;Mysql索引概述所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。在MySQL5.1中，对于MyISAM和InnoDB表，前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量，而CREATETABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyIS