14094人阅读
MariaDB（16）
原文链接: 原文日期: Created 2010; Refreshed Oct, 2012, Jan, 2014&翻译人员: 翻译日期: 日MySQL 内存分配—— 快速设置方案如果仅使用MyISAM存储引擎,设置 key_buffer_size为可用内存的20%,(再加上设置 innodb_buffer_pool_size = 0 )&如果仅使用InnoDB存储引擎,设置 innodb_buffer_pool_size为可用内存的 70%, (设置 key_buffer_size = 10M,很小但不是0.)&调优mysql的实践经验:首先拷贝 my.cnf / my.ini 文件副本.根据使用的存储引擎及可用内存,设置 key_buffer_size 和innodb_buffer_pool_size.慢查询(Slow queries)的修正一般是通过添加索引(indexes),改变表结构(schema),改变 SELECT 语句 来实现,而不是通过数据库调优.不要随便设置查询缓存(Query cache),除非你真正掌握它的优缺点以及适用场景.不要改变其他的参数,除非你遇到了相应的问题(如最大连接数问题, max connections).确保修改的是 [mysqld] 这一节下的内容,而不是其他部分.&下面向您展示一些实际的细节. (本文不涉及 NDB Cluster)&什么是索引缓存(key_buffer)?MyISAM引擎的缓存分为两部分.索引块(Index blocks,每个1 KB,BTree结构、存放于 .MYI 文件) 缓存到 “key buffer” 中.&数据块缓存(Data block caching, 存放于 .MYD 文件中)交给操作系统负责, 所以确保留下了适量的空闲内存(给操作系统).&警告: 某些类型的操作系统总是报告说内存使用超过90%,虽然实际上还有很多的空闲内存.&SHOW GLOBAL STATUS LIKE 'Key%'; 执行后计算 Key_read_requests / Key_reads 的值, 如果比值较大(比如大于10), 那么 key_buffer 就足够了.&什么是缓存池(buffer_pool)?InnoDB将所有缓存都放在 “buffer pool” 中, 缓存池的大小通过 innodb_buffer_pool_size 控制. 包含被打开表(open tables)中的 16KB一块的数据/索引块,此外还有一些附加开销.&MySQL 5.5(以及带插件的 5.1版本)允许您指定 块大小(block size)为 8 KB或4 KB. MySQL 5.5可以有多个缓冲池,因为每个缓存池有一个互斥锁, 所以设置多个池可以缓解一些互斥锁瓶颈.&另一种计算缓存大小的方法将主缓存(main cache)设置为最小值; 如果同一台机器上有许多其他应用在跑, 并且/或者RAM内存小于2GB, 那么可以这样指定.&SHOW TABLE STATUS; &显示各个数据库中所有表的状态.&计算所有MyISAM表的 Index_length 值的总和. 让 key_buffer_size 小于等于这个和值.&计算所有 InnoDB表 Data_length + Index_length 值的总和. 设置 innodb_buffer_pool_size 为不超过总和值的110%.&如果有内存交换(swapping发生),需要将两个参数适量地按减小一些.&执行下面的SQL语句查看适合的参数值. (如果有很多表,可能耗时几分钟.)SELECT
ROUND(SUM(data_length) /, 1) AS &Data MB&,
ROUND(SUM(index_length)/, 1) AS &Index MB&,
ROUND(SUM(data_length + index_length)/, 1) AS &Total MB&,
COUNT(*) &Num Tables&
INFORMATION_SCHEMA.TABLES
table_schema not in (&information_schema&, &performance_schema&)
互斥锁瓶颈MySQL 是单核CPU时代设计的,且可以很容易移植到不同的硬件体系架构中. 不幸的是,这导致了对连结锁(interlock)操作的凌乱. 在几个重要的流程中存在少量(非常少)的“互斥(mutexes)”. 包括:&MyISAM的 key_buffer&查询缓存(Query Cache)&InnoDB的buffer_pool随着多核CPU的盛行,互斥问题引起了MySQL的性能问题. 一般来说,CPU超过 4~8 核越多,则MySQL变得越慢,而不会更快. MySQL 5.5 中 InnoDB 的增强版 Percona XtraDB 对多核CPU的支持要好很多; 实际的限制大致是32核, CPU核心超过这个数后性能会达到瓶颈 ,但不再下降. MySQL 5.6版声称最多可以支持48核.&超线程和多核CPU简单的处理方式:&禁用超线程(HyperThreading)&停用超过8个核心以上的部分超线程这里主要是指以前的超线程技术,因此此部分可能不一定正确.&超线程适合拿来做营销宣传,但对(专用应用的)性能极不友好. 有两个处理单元在共享同一个物理缓存. 如果这两个线程在做同样的事情,缓存会相当高效. 如果这俩线程在干不同的事,他们会相互妨碍到另一个(超)线程的缓存项.&总的来说MySQL在多核处理上并不占优势. 所以,如果禁用超线程(HT),剩下的核心将会运行得更快一点.&32位操作系统和MySQL(译者注: 肯定64位的MySQL在 32位OS上跑不起来...)首先,操作系统(以及硬件?) 会限制进程不能使用4GB RAM中的全部,如果有 4G内存的话. 如果物理 RAM 超过 4 GB, 超过的部分在32位操作系统中不可访问,也是不可用的.其次,操作系统可能会限制单个进程最大使用多少内存.例如:FreeBSD的 maxdsiz ,默认为512 MB.&示例:$ ulimit -a
max memory size (kbytes, -m) 524288因此,确定了 mysqld有多少可用内存, 就可以设置为 20% ~ 70%,但需要适当的减少一些.&如果系统报错,例如 [ERROR] /usr/libexec/mysqld: Out of memory (Needed xxx bytes) , 可能是MySQL申请了超过操作系统允许的内存范围. 需要减小缓存设置.&64位OS与32位MySQL64位操作系统不受4 GB内存的限制,但32位MySQL依然受这个限制.&如果你有 4 GB以上的内存,那么可以设置:&key_buffer_size = 20%(所有RAM的),但不要超过3 GB.buffer_pool = 3G当然最好的办法是将MySQL换成64位版本.64位OS与64位MySQL只使用MyISAM引擎: (5.0.52 ~ 5.1.23之前的)key_buffer_size有 4GB的硬性限制. 详情请参考& & &&& & & & & & & & & & & & &在更高版本中,设置 key_buffer_size 为 20%的RAM. 在(my.cnf / my.ini)中加上 innodb_buffer_pool_size = 0.&只使用InnoDB引擎: 设置 innodb_buffer_pool_size = 70%的RAM. 如果内存很大,并使用 5.5(及以上)版本,可以考虑使用 多个缓存池. 推荐设置 1 - 16 个 &innodb_buffer_pool_instances, 每个都不小于1 GB. (很抱歉,没有最优设置为多少个的具体参考指标;但应该不能设置太多).&与此同时,设置 key_buffer_size = 20M(很小,但不是零)&如果你在数据库中混合使用多个引擎,将两个值都降低一些.最大连接数,线程栈(max_connections,thread_stack)每个“线程”都要占用一定的内存. 通常为 200 KB左右; 因此 100个线程大概就是 20 MB. 如果设置 max_connections = 1000,那大概就需要 200 MB,或者更多. 同时连接数太大可能会引起其他某些问题,这点需要注意.&在5.6(或 MariaDB5.5)中,可以选择线程池与 max_connections 交互. 这是一个高级话题.&线程栈溢出很少出现. 如果确实发生了,可以设置: thread_stack = 256K&table_cache(table_open_cache)(某些版本中名字不一样).&操作系统对单个进程能打开的文件数有限制. 打开每个表需要 1-3个文件. 每个表分区(PARTITION)等价于一个表. 在分区表上的多数操作都会打开所有的分区.&在 *nix中, ulimit 显示文件限制是多少. 最大值一般是上万,但有可能被设置为 1024. 这就限制了只能打开300个左右的表. &(这一段是有争议的.) 另一方面,表缓存(过去?)的实现方式很低效 —— 查找通过线性扫描来完成. 因此,设置 table_cache 为几千确实会使得 mysql变慢. (基准测试也证明了这一点.)&你可以通过 &SHOW GLOBAL STATUS; 查看系统的性能信息, 并计算 每秒打开数(opens/second): Opened_files /Uptime , 如果这个值较大,例如大于 5, 那么应该加大 table_ 如果很小,比如是 1,通过减小 table_cache 值,可能会对性能有所改善.&查询缓存(Query Cache)简短的回答: 设置 query_cache_type = OFF 及 query_cache_size = 0QC(Query Cache)实际上是将 SELECT语句与结果集(resultsets)进行散列映射.&详细的回答…… 关于“查询缓存”有许多种观点; 其中许多是负面的.新手警告! QC与key_buffer和buffer_pool完全无关.&当命中时, QC速度快如闪电. 要创建一个运行快1000倍的基准测试并不难.&在QC中只有一个互斥锁(译者注: 锁越少,就是锁钥匙越少,高并发时就会激烈竞争/等待).&除非将QC设置为OFF与0,否则每次查询都会去对比一遍.真相,互斥锁会发生碰撞,即使 query_cache_type = DEMAND (2).真相,互斥锁会发生碰撞,即便设置了 SQL_NO_CACHE.查询语句只要变了一点点(即使多了个空格)都可能导致在QC中生成多个不同的缓存项.“修改”是代价高昂与频繁的:&在一个表中发生任何 write 事件, QC中对应到这个表的所有条目都会被清除.&即便在只读从服务器(readonly Slave)上也是这样.清除使用的是线性算法来执行,所以QC较大(比如200MB)则会导致速度明显地变慢.&要查看QC的执行效率如何,执行 SHOW GLOBAL STATUS LIKE 'Qc%'; 然后计算read的命中率: Qcache_hits / Qcache_inserts, 如果大于5,则 QC的效率还不错.&如果QC适合你的应用,那么我推荐:query_cache_size = 不超过50M&query_cache_type = DEMAND &在所有 SELECT 语句中指明 SQL_CACHE 或 SQL_NO_CACHE, 根据哪些查询可能会从QC缓存中命中.thread_cache_size这是一个很小的调优项. 设置为 0 会降低线程(连接)创建的速度. 设置为较小的值(比如 10) 是比较好的. 该选项对RAM没有多少影响.&它是服务器额外保持的线程数量,不会影响实际线程数; 起限制作用的是 max_connections.&二进制日志如果为 复制(replication) 或 时间点恢复(point-in-time recovery) 启用二进制日志(通过 og_bin开启), 则服务器将一直记录二进制日志(binary logs). 也就是说,可能慢慢地占用磁盘. 建议设置expire_logs_days = 14 ,只保留14天的日志记录.swappinessRHEL,非常英明地,允许用户自己控制 OS 如何进行预先内存交换分配. 总的来说这是很好的策略,但对MySQL来说则是一个灾难. &(感觉翻译的有点不流畅，本段原文为: RHEL, in its infinite wisdom, decided to let you control how aggressively the OS will preemptively swap RAM. This is good in general, but lousy for MySQL)MySQL期望相当稳定的内存分配 —— 缓存(大部分)是预先分配的; 线程(大都)是限制数量的. 任何内存交换都可能极大地损害MySQL的性能.&设置很高的swappiness值,会丢失一些内存,因为操作系统试图为以后的分配保留大量的自由空间(MySQL一般是不需要的).&设置swappiness = 0,不交换,在内存不足时操作系统可能会崩溃,. 我宁愿MySQL一卡一卡的,也不希望他崩了.&对于MySQL-only(专用)服务器, 中间数(比如5 ?)可能是一个很好的值.NUMAOK,是时候了解一些CPU管理内存的架构了. 我们先看 (Non-Uniform Memory Access, 非统一内存寻址). 每个CPU(或多路服务器中的每个socket(CPU插座)) 都挂载有一部分内存. 这使得访问本地(local) RAM 非常快, 而访问挂载在其他 CPU下的RAM要慢上数十个周期.&接着看操作系统. 在(RHEL ?)很多情形下,有两个行为:&OS分配的内存固定到 “first(第一个)” CPU名下.&接着分配的其他内存也默认分配到第一个CPU名下,直到它满了.&现在问题来了.&OS与MySQL分配完了第一个 CPU的所有RAM.&MySQL分配了第二个 CPU的部分内存.&操作系统OS还需要分配一些其他内存.&Ouch —— 一个CPU需要分配内存,但自己名下控制的RAM已经耗尽了,所以它将MySQL的部分内存置换出去. 渣渣！&可能的解决方案:配置BIOS内存分配为 “interleave”(交错). 这将防止过早交换(premature swapping),代价是有一半左右的 RAM 访问要跨CPU(off-CPU). 嗯,不论如何访问的代价都较大, 如果真的要使用所有内存的话.&整体性能损失/收益:几个百分点.&大内存分页(huge pages)这里有另一个硬件性能陷阱.&CPU访问RAM,特别是将64位地址映射到某个地方, 比如 128 GB 或“真实”的RAM,会使用TLB. (TLB =Translation Lookaside Buffer,旁路转换缓冲.) TLB是硬件实现的内存关联查找表; 将64位的虚拟地址转换到实际的物理地址.&因为TLB是一个小的,虚拟寻址的缓存,有时会发生 “misses”(未命中),那就会进入物理RAM来查找. 这是两次查找是很费时的操作,所以应该避免.&通常,内存被 “分页” 为 4 KB一页,TLB实际上将高位的(64 - 12)位映射到一个特定页面. 而低12位通过虚地址转换得到完整的地址.&例如,128 GB的RAM按 4 KB分页需要 32M(3200万个) page-table条目. 这太大了, 远远超过TLB的容量. 所以陷入了“Huge page”的骗局.&随着硬件与操作系统的支持,使部分RAM成为巨型页面成为可能 ,比如说4 MB(而不是4 KB). 这使得TLB条目剧减,对这部分RAM来说分页单元是4 MB. 因此,巨大的页面相当于是不分页的(non-pagable).&现在内存被分为 pagable 和 non pagable 两部分; 哪些部分 non pagable 是合理的? 在MySQL中, innodb_buffer_pool 就是一个完美的使用者. 通过正确地配置这些,InnoDB能跑得更快一点:&启用 Huge pages通知操作系统分配适当的数量(和 buffer_pool 个数一致)&通知MySQL使用huge pages&该帖包含有很多需要关注点以及如何设置的细节.&整体性能收益:几个百分点. Yawn.&MEMORY引擎(ENGINE=MEMORY)这是一个不常用的存储引擎,算是MyISAM和InnoDB的替代品. 其数据不是持久的,所以其应用范围相当有限. 内存表的大小受限于 max_heap_table_size ,默认值是16 MB. 我提起它,以防你将此值修改得太大;这会偷偷地占用可用的RAM.如何设置变量(VARIABLEs)在文本文件my.cnf中(Windows上是my.ini),添加一行,例如innodb_buffer_pool_size = 5G即: 变量名,等号“=”,变量的值. 有些值允许缩写,如M代表 million(1048576),G代表billion.&要让服务器看到这些设置,必须将其放到配置文件的 “[mysqld]”节下.对 my.cnf 或 my.ini的设置不会立即生效,需要你重启服务器.&大多数的设置可以通过 root 账号登陆后在线修改 &(其他 SUPER权限账号也可以),例如:SET @@global.key_buffer_size = ;注意:此处不允许设置 M 或 G 等单位.查看全局变量的设置信息：mysql& SHOW GLOBAL VARIABLES LIKE &key_buffer_size&;
+-----------------+----------+
| Variable_name
+-----------------+----------+
| key_buffer_size |
+-----------------+----------+注意,这部分设置MySQL会向下取整,对齐到一定的数字.你可能需要修改两个地方(执行SET 并修改my.cnf),以使修改立即生效,并且下次重启后依然是同样的值(不管是手动,还是其他原因重新启动)Web服务器像Apache这样的web服务器使用多线程来处理. 如果每个线程打开一个 MySQL连接,可能会超过允许的最大连接数. 确保将web服务器的 MaxClients (或类似参数) 设置为一个合理的值(如50以下).&工具MySQLTuner&TUNING-PRIMER&上面是几个对内存设置建议的工具. 其中有一个误导性条目:Maximum possible memory usage: 31.3G (266% of installed RAM)可能使用的内存最大值为: 31.3G (可能是物理内存的 266%)不要让它吓到你,这些工具使用的公式过于保守了. 他们假设所有 max_connections 都在使用并且处于活跃状态,并正在执行一些内存密集型的工作.Total fragmented tables: 23有碎片的tables: 23 个这意味着 OPTIMIZE TABLE 可能会有作用. 我建议对表设置高百分比的 “free space”(见SHOW TABLE STATUS) 或者你知道对什么表做了大量的删除/更新操作. 不过,不必费心频繁地对table进行OPTIMIZE 优化整理. 一个月一次可能就够了.&文章修改记录2010创建;2012年10月更新,2014年1月更新;更深入的文章:通过
联系作者 ——里克·詹姆斯&里克·詹姆斯的MySQL相关文档提示,调试、howto、优化相关等等……&&(Rules of Thumb -- lots of tips)&&&&-- includes differences between them&&&plus other insights into the mysteries of INDEXing&&for time series&&-- a common, poorly performing, plus an alternative&&(efficient searching on Latitude + Longitude)&&&-- how to optimize the schema and code for such&&&&(On beyond ORDER BY RAND())&&&&&(with Percona XtraDB Cluster / MariaDB)&&&
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：3332797次
积分：19167
积分：19167
排名：第466名
原创：99篇
译文：165篇
评论：633条
阅读：68242本文讲的是mysql大内存高性能优化方案，
8G内存下MySQL的优化
按照下面的设置试试看：
key_buffer = 3840M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 32M
8G内存下MySQL的优化
按照下面的设置试试看：
key_buffer = 3840M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 256M
thread_cache_size = 32
query_cache_size = 256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
其中key_buffer_size 上限是4G，不能再多了
但是实际的为了使MySql的性能最优化，内存的分配是需要进行调试的
mysql大内存高性能优化
服务器硬件对MySQL性能的影响
1、磁盘寻道能力（磁盘I/O）,以目前高转速SCSI硬盘(7200转/秒)为例，这种硬盘理论上每秒寻道7200次，这是物理特性决定的，没有办法改变。 MySQL每秒钟都在进行大量、复杂的查询操作，对磁盘的读写量可想而知。所以，通常认为磁盘I/O是制约MySQL性能的最大因素之一，对于日均访问量 在100万PV以上的Discuz!论坛，由于磁盘I/O的制约，MySQL的性能会非常低下！解决这一制约因素可以考虑以下几种解决方案：
使用RAID-0+1磁盘阵列，注意不要尝试使用RAID-5，MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。
2、CPU 对于MySQL应用，推荐使用S.M.P.架构的多路对称CPU，例如：可以使用两颗Intel Xeon 3.6GHz的CPU，现在我较推荐用4U的服务器来专门做数据库服务器，不仅仅是针对于mysql。
3、物理内存对于一台使用MySQL的Database Server来说，服务器内存建议不要小于2GB，推荐使用4GB以上的物理内存，不过内存对于现在的服务器而言可以说是一个可以忽略的问题，工作中遇到了高端服务器基本上内存都超过了32G。
MySQL自身因素
当解决了上述服务器硬件制约因素后，让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件 my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。由于my.cnf文件的优化设置是与服务器硬件配置息息相关的，因而我们指定一个假想的服务器硬件环境：
CPU：2颗Intel Xeon 2.4GHz
内存：4GB DDR
硬盘：SCSI 73GB(很常见的2U服务器)。
下面，我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明：
以下只列出my.cnf文件中[mysqld]段落中的内容，其他段落内容对MySQL运行性能影响甚微，因而姑
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-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 = 4M
thread_stack = 256K
table_cache = 128K
sort_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 = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
# 指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的 情况；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓 冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。
tmp_table_size = 256M
max_connections = 768
#指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示，则需要增大该参数值。
max_connect_errors =
wait_timeout = 10
#指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量*2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4*2=8
skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！
table_cache=1024
#物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1
innodb_log_buffer_size=2M
innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几,建议用默认一般为8
key_buffer_size=256M
#默认为218，调到128最佳
tmp_table_size=64M
#默认为16M，调到64-256最挂
read_buffer_size=4M
#默认为64K
read_rnd_buffer_size=16M
#默认为256K
sort_buffer_size=32M
#默认为256K
thread_cache_size=120
query_cache_size=32M
值得注意的是：很多情况需要具体情况具体分析。
如果Key_reads太大，则应该把my.cnf中Key_buffer_size变大，保持Key_reads/Key_read_requests至少1/100以上，越小越好。
提升性能的建议
1.如果opened_tables太大,应该把my.cnf中的table_cache变大
2.如果Key_reads太大,则应该把my.cnf中key_buffer_size变大.可以用Key_reads/Key_read_requests出cache失败率
3.如果Handler_read_rnd太大,则你写的SQL语句里很多查询都是要扫描整个表,而没有发挥键的作用
4.如果Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率
5.如果Created_tmp_disk_tables太大,就要增加my.cnf中tmp_table_size的值,用基于内存的临时表代替基于磁盘的
除了机器优化我们的sql语句也可以优化
1. 为查询缓存优化你的查询
大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。
这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例：
// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date &= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date &= '$today'");
上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。
2. EXPLAIN 你的 SELECT 查询
使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。
挑一个你的SELECT语句（推荐挑选那个最复杂的，有多表联接的），把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后，你会看到一张表格。下面的这个示例中，我们忘记加上了group_id索引，并且有表联接：
当我们为 group_id 字段加上索引后：
我们可以看到，前一个结果显示搜索了 7883 行，而后一个只是搜索了两个表的 9 和 16 行。查看rows列可以让我们找到潜在的性能问题。
3. 当只要一行数据时使用 LIMIT 1
当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
下面的示例，只是为了找一下是否有“中国”的用户，很明显，后面的会比前面的更有效率。（请注意，第一条中是Select *，第二条是Select 1）
// 没有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) & 0) {
// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) & 0) {
4. 为搜索字段建索引
索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。
从上图你可以看到那个搜索字串 “last_name LIKE ‘a%'”，一个是建了索引，一个是没有索引，性能差了4倍左右。
另外，你应该也需要知道什么样的搜索是不能使用正常的索引的。例如，当你需要在一篇大的中搜索一个词时，如： “WHERE post_content LIKE ‘%apple%'”，索引可能是没有意义的。你可能需要使用MySQL全文索引 或是自己做一个索引（比如说：搜索关键词或是Tag什么的）
5. 在Join表的时候使用相当类型的例，并将其索引
如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。
而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）
// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");
// 两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。
6. 千万不要 ORDER BY RAND()
想打乱返回的数据行？随机挑一个数据？真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。
如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）
下面的示例是随机挑一条记录
// 千万不要这样做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 这要会更好：
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
7. 避免 SELECT *
从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。
所以，你应该养成一个需要什么就取什么的好的习惯。
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
8. 永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。
就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。
9. 使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项变得相当的完美。
如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。
MySQL也有一个“建议”（见第十条）告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时，这个建议会告诉你把其改成 ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。
10. 从 PROCEDURE ANALYSE() 取得建议
PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。
例如，如果你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成 ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。
在phpmyadmin里，你可以在查看表时，点击 “Propose table structure” 来查看这些建议
一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。一定要记住，你才是最终做决定的人。
11. 尽可能的使用 NOT NULL
除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。
首先，问问你自己“Empty”和“NULL”有多大的区别（如果是INT，那就是0和NULL）？如果你觉得它们之间没有什么区别，那么你就不要使用NULL。（你知道吗？在 Oracle 里，NULL 和 Empty 的字符串是一样的！)
不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。
下面摘自MySQL自己的：
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
12. Prepared Statements
Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。
Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击。当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候，这样的问题会好一些。
在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。
虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势，所以这会使得网络传输非常有效率。
当然，也有一些情况下，我们需要避免使用Prepared Statements，因为其不支持查询缓存。但据说版本5.1后支持了。
在PHP中要使用prepared statements，你可以查看其使用手册：mysqli 扩展 或是使用数据库抽象层，如： PDO.
// 创建 prepared statement
if ($stmt = $mysqli-&prepare("SELECT username FROM user WHERE state=?")) {
// 绑定参数
$stmt-&bind_param("s", $state);
$stmt-&execute();
// 绑定结果
$stmt-&bind_result($username);
// 移动游标
$stmt-&fetch();
printf("%s is from %sn", $username, $state);
$stmt-&close();
13. 无缓冲的查询
正常的情况下，当你在当你在你的脚本中执行一个SQL语句的时候，你的程序会停在那里直到没这个SQL语句返回，然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。
关于这个事情，在PHP的文档中有一个非常不错的说明： mysql_unbuffered_query() 函数：
“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed.”
上面那句话翻译过来是说，mysql_unbuffered_query() 发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。
然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且， mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以，是否使用无缓冲的查询你需要仔细考虑。
14. 把IP地址存成 UNSIGNED INT
很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。
我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。
而你的查询，你可以使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串IP。在PHP中，也有这样的函数 ip2long() 和 long2ip()。
1 $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
15. 固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。
使用“垂直分割”技术（见下一条），你可以分割你的表成为两个一个是定长的，一个则是不定长的。
16. 垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过一张表有100多个字段，很恐怖）
示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。
示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户 ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。
另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。
17. 拆分大的 DELETE 或 INSERT 语句
如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。
如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你泊WEB服务Crash，还可能会让你的整台服务器马上?炝恕?/p&
所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。下面是一个示例：
while (1) {
//每次只做1000条
mysql_query("DELETE FROM logs WHERE log_date &= '' LIMIT 1000");
if (mysql_affected_rows() == 0) {
// 没得可删了，退出！
// 每次都要休息一会儿
usleep(50000);
18. 越小的列会越快
对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。
参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。
如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。
当然，你也需要留够足够的扩展空间，不然，你日后来干这个事，你会死的很难看，参看Slashdot的例子（ 日），一个简单的ALTER TABLE语句花了3个多小时，因为里面有一千六百万条数据。
19. 选择正确的存储引擎
在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。
下面是MySQL的手册
* target=”_blank”MyISAM Storage Engine
* InnoDB Storage Engine
20. 使用一个对象关系映射器（Object Relational Mapper）
使用 ORM (Object Relational Mapper)，你能够获得可靠的性能增涨。一个ORM可以做的所有事情，也能被手动的编写出来。但是，这需要一个高级专家。
ORM 的最重要的是“Lazy Loading”，也就是说，只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作用，因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。
ORM 还可以把你的SQL语句打包成一个事务，这会比单独执行他们快得多得多。
目前，个人最喜欢的PHP的ORM是：Doctrine。
21. 小心“永久链接”
“永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了，它会永远处在连接的状态，就算是数据库操作已经结束了。而且，自从我们的Apache开始重用它的子进程后——也就是说，下一次的HTTP请求会重用Apache的子进程，并重用相同的 MySQL 链接。
以上是云栖社区小编为您精心准备的的内容，在云栖社区的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
mysql性能优化方案、mysql 性能优化、mysql数据库性能优化、mysql高性能优化、mysql高性能优化 pdf，以便于您获取更多的相关知识。
6款热门基础云产品6个月免费体验；2款产品1年体验;1款产品2年体验
稳定可靠、可弹性伸缩的在线数据库服务，全球最受欢迎的开源数据库之一
弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率
开发者常用软件，超百款实用软件一站式提供
云栖社区()为您免费提供相关信息，包括
的信息，还有mysql性能优化方案、mysql 性能优化、mysql数据库性能优化、mysql高性能优化、mysql高性能优化 pdf等
，所有相关内容均不代表云栖社区的意见！