Mysql_性能优化

Mysql 性能优化教程

目录

目录 (1)

背景及目标 (2)

Mysql 执行优化 (2)

认识数据索引 (2)

为什么使用数据索引能提高效率 (2)

如何理解数据索引的结构 (2)

如何理解影响结果集 (3)

理解执行状态 (4)

常见分析手段 (4)

分析流程 (6)

总结 (7)

Mysql 运维优化 (9)

存储引擎类型 (9)

内存使用考量 (9)

性能与安全性考量 (9)

存储压力优化 (10)

运维监控体系 (10)

Mysql 架构优化 (11)

架构优化目标 (11)

防止单点隐患 (11)

方便系统扩容 (11)

安全可控，成本可控 (11)

分布式方案 (12)

分库&拆表方案 (12)

主从架构 (14)

故障转移处理 (15)

缓存方案 (15)

缓存结合数据库的读取 (15)

缓存结合数据库的写入 (15)

背景及目标

●厦门游家公司（https://www.wendangku.net/doc/ba8673141.html,）用于员工培训和分享。

●针对用户群为已经使用过mysql环境，并有一定开发经验的工程师

●针对高并发，海量数据的互联网环境。

●本文语言为口语，非学术标准用语。

●以实战和解决具体问题为主要目标，非应试，非常规教育。友情提醒，在校生学习

本教程可能对成绩提高有害无益。

●非技术挑战，非高端架构师培训，请高手自动忽略。

Mysql 执行优化

认识数据索引

为什么使用数据索引能提高效率

?数据索引的存储是有序的

?在有序的情况下，通过索引查询一个数据是无需遍历索引记录的

?极端情况下，数据索引的查询效率为二分法查询效率，趋近于log2(N)

如何理解数据索引的结构

?数据索引通常默认采用btree索引，（内存表也使用了hash索引）。

?单一有序排序序列是查找效率最高的（二分查找，或者说折半查找），使用树形索

引的目的是为了达到快速的更新和增删操作。

?在极端情况下（比如数据查询需求量非常大，而数据更新需求极少，实时性要求不

高，数据规模有限），直接使用单一排序序列，折半查找速度最快。

◆实战范例：ip地址反查

资源：Ip地址对应表，源数据格式为startip, endip, area

源数据条数为10万条左右，呈很大的分散性

目标：需要通过任意ip查询该ip所属地区

性能要求达到每秒1000次以上的查询效率

挑战：如使用between … and 数据库操作，无法有效使用索引。

如果每次查询请求需要遍历10万条记录，根本不行。

方法：一次性排序（只在数据准备中进行，数据可存储在内存序列）

折半查找（每次请求以折半查找方式进行）

?在进行索引分析和SQL优化时，可以将数据索引字段想象为单一有序序列，并以

此作为分析的基础。

◆实战范例：复合索引查询优化实战，同城异性列表

资源：用户表user，字段sex性别；area 地区；lastlogin 最后登录时间；其他略

目标：查找同一地区的异性，按照最后登录时间逆序

高访问量社区的高频查询，如何优化。

查询SQL: select * from user where area=’$area’and sex=’$sex’order by lastlogin desc limit 0,30;

挑战：建立复合索引并不难，area+sex+lastlogin 三个字段的复合索引,如何理

解？

首先，忘掉btree，将索引字段理解为一个排序序列。

如果只使用area会怎样？搜索会把符合area的结果全部找出来，然后在这里面遍历，选择命中sex的并排序。遍历所有area=’$area’数据！

如果使用了area+sex，略好，仍然要遍历所有area=’$area’ and sex=’$sex’数据，然后在这个基础上排序！！

Area+sex+lastlogin复合索引时（切记lastlogin在最后），该索引基于area+sex+lastlogin 三个字段合并的结果排序，该列表可以想象如下。

广州女$时间1

广州女$时间2

广州女$时间3

…

广州男

….

深圳女

….

数据库很容易命中到area+sex的边界，并且基于下边界向上追溯30条记录，搞定！在索引中迅速命中所有结果，无需二次遍历！

如何理解影响结果集

?影响结果集是数据查询优化的一个重要中间数据

◆查询条件与索引的关系决定影响结果集

如上例所示，即便查询用到了索引，但是如果查询和排序目标不能直接在索引

中命中，其可能带来较多的影响结果。而这会直接影响到查询效率

◆微秒级优化

●优化查询不能只看慢查询日志，常规来说，0.01秒以上的查询，都是不够

优化的。

●实战范例

和上案例类似，某游戏社区要显示用户动态，select * from userfeed where

uid=$uid order by lastlogin desc limit 0,30; 初期默认以uid为索引字段，

查询为命中所有uid=$uid的结果按照lastlogin排序。当用户行为非常频

繁时，该SQL索引命中影响结果集有数百乃至数千条记录。查询效率超

过0.01秒，并发较大时数据库压力较大。

解决方案：将索引改为uid+lastlogin 复合索引，索引直接命中影响结果

集30条，查询效率提高了10倍，平均在0.001秒，数据库压力骤降。

?影响结果集的常见误区

◆影响结果集并不是说数据查询出来的结果数或操作影响的结果数，而是查询条

件的索引所命中的结果数。

◆实战范例

●某游戏数据库使用了innodb，innodb是行级锁，理论上很少存在锁表情

况。出现了一个SQL语句(delete from tabname where xid=…)，这个SQL

非常用SQL，仅在特定情况下出现，每天出现频繁度不高（一天仅10次

左右），数据表容量百万级，但是这个xid未建立索引，于是悲惨的事情

发生了，当执行这条delete 的时候，真正删除的记录非常少，也许一到

两条，也许一条都没有；但是！由于这个xid未建立索引，delete操作时

遍历全表记录，全表被delete操作锁定，select操作全部被locked，由于

百万条记录遍历时间较长，期间大量select被阻塞，数据库连接过多崩溃。

这种非高发请求，操作目标很少的SQL，因未使用索引，连带导致整个

数据库的查询阻塞，需要极大提高警觉。

?总结：

◆影响结果集是搜索条件索引命中的结果集，而非输出和操作的结果集。

◆影响结果集越趋近于实际输出或操作的目标结果集，索引效率越高。

◆请注意，我这里永远不会讲关于外键和join的优化，因为在我们的体系里，

这是根本不允许的！架构优化部分会解释为什么。

理解执行状态

常见分析手段

●慢查询日志，关注重点如下

?是否锁定，及锁定时间

◆如存在锁定，则该慢查询通常是因锁定因素导致，本身无需优化，需解决

锁定问题。

?影响结果集

◆如影响结果集较大，显然是索引项命中存在问题，需要认真对待。

●Explain 操作

?索引项使用

◆不建议用using index做强制索引，如未如预期使用索引，建议重新斟酌

表结构和索引设置。

?影响结果集

◆这里显示的数字不一定准确，结合之前提到对数据索引的理解来看，还记

得嘛？就把索引当作有序序列来理解，反思SQL。

●Set profiling , show profiles for query操作

?执行开销

◆注意，有问题的SQL如果重复执行，可能在缓存里，这时要注意避免缓

存影响。通过这里可以看到。

◆执行时间超过0.005秒的频繁操作SQL建议都分析一下。

◆深入理解数据库执行的过程和开销的分布

●Show processlist

?状态清单

◆Sleep 状态，通常代表资源未释放，如果是通过连接池，sleep状态应该

恒定在一定数量范围内

●实战范例：因前端数据输出时（特别是输出到用户终端）未及时关

闭数据库连接，导致因网络连接速度产生大量sleep连接，在网速出

现异常时，数据库too many connections 挂死。

●简单解读，数据查询和执行通常只需要不到0.01秒，而网络输出通

常需要1秒左右甚至更长，原本数据连接在0.01秒即可释放，但是

因为前端程序未执行close操作，直接输出结果，那么在结果未展现

在用户桌面前，该数据库连接一直维持在sleep状态！

◆Waiting for net, reading from net, writing to net

●偶尔出现无妨

●如大量出现，迅速检查数据库到前端的网络连接状态和流量

●案例: 因外挂程序，内网数据库大量读取，内网使用的百兆交换迅速

爆满，导致大量连接阻塞在waiting for net，数据库连接过多崩溃

◆Locked状态

●有更新操作锁定

●通常使用innodb可以很好的减少locked状态的产生，但是切记，更

新操作要正确使用索引，即便是低频次更新操作也不能疏忽。如上影

响结果集范例所示。

●在myisam的时代，locked是很多高并发应用的噩梦。所以mysql官

方也开始倾向于推荐innodb。

◆Copy to tmp table

●索引及现有结构无法涵盖查询条件，才会建立一个临时表来满足查询

要求，产生巨大的恐怖的i/o压力。

●很可怕的搜索语句会导致这样的情况，如果是数据分析，或者半夜的

周期数据清理任务，偶尔出现，可以允许。频繁出现务必优化之。

●Copy to tmp table 通常与连表查询有关，建议逐渐习惯不使用连表查

询。

●实战范例：

?某社区数据库阻塞，求救，经查，其服务器存在多个数据库应用

和网站，其中一个不常用的小网站数据库产生了一个恐怖的

copy to tmp table 操作，导致整个硬盘i/o和cpu压力超载。Kill

掉该操作一切恢复。

◆Sending data

●Sending data 并不是发送数据，别被这个名字所欺骗，这是从物理磁

盘获取数据的进程，如果你的影响结果集较多，那么就需要从不同的

磁盘碎片去抽取数据，

●偶尔出现该状态连接无碍。

●回到上面影响结果集的问题，一般而言，如果sending data连接过多，

通常是某查询的影响结果集过大，也就是查询的索引项不够优化。

●如果出现大量相似的SQL语句出现在show proesslist列表中，并且都

处于sending data状态，优化查询索引，记住用影响结果集的思路去

思考。

◆Freeing items

●理论上这玩意不会出现很多。偶尔出现无碍

●如果大量出现，内存，硬盘可能已经出现问题。比如硬盘满或损坏。

◆Sorting for …

●和Sending data类似，结果集过大，排序条件没有索引化，需要在内

存里排序，甚至需要创建临时结构排序。

◆其他

●还有很多状态，遇到了，去查查资料。基本上我们遇到其他状态的阻

塞较少，所以不关心。

分析流程

●基本流程

?详细了解问题状况

◆Too many connections 是常见表象，有很多种原因。

◆索引损坏的情况在innodb情况下很少出现。

◆如出现其他情况应追溯日志和错误信息。

?了解基本负载状况和运营状况

◆基本运营状况

●当前每秒读请求

●当前每秒写请求

●当前在线用户

●当前数据容量

◆基本负载情况

●学会使用这些指令

?Top

?Vmstat

?uptime

?iostat

?df

●Cpu负载构成

?特别关注i/o压力( wa%)

?多核负载分配

●内存占用

?Swap分区是否被侵占

?如Swap分区被侵占，物理内存是否较多空闲

●磁盘状态

?硬盘满和inode节点满的情况要迅速定位和迅速处理

?了解具体连接状况

◆当前连接数

●Netstat –an|grep 3306|wc –l

●Show processlist

◆当前连接分布show processlist

●前端应用请求数据库不要使用root帐号！

?Root帐号比其他普通帐号多一个连接数许可。

?前端使用普通帐号，在too many connections的时候root帐号仍

可以登录数据库查询show processlist!

?记住，前端应用程序不要设置一个不叫root的root帐号来糊弄！

非root账户是骨子里的，而不是名义上的。

●状态分布

?不同状态代表不同的问题，有不同的优化目标。

?参见如上范例。

●雷同SQL的分布

?是否较多雷同SQL出现在同一状态

◆当前是否有较多慢查询日志

●是否锁定

●影响结果集

?频繁度分析

◆写频繁度

●如果i/o压力高，优先分析写入频繁度

●Mysqlbinlog 输出最新binlog文件，编写脚本拆分

●最多写入的数据表是哪个

●最多写入的数据SQL是什么

●是否存在基于同一主键的数据内容高频重复写入？

?涉及架构优化部分，参见架构优化-缓存异步更新

◆读取频繁度

●如果cpu资源较高，而i/o压力不高，优先分析读取频繁度

●程序中在封装的db类增加抽样日志即可，抽样比例酌情考虑，以不

显著影响系统负载压力为底线。

●最多读取的数据表是哪个

●最多读取的数据SQL是什么

?该SQL进行explain 和set profiling判定

?注意判定时需要避免query cache影响

◆比如，在这个SQL末尾增加一个条件子句and 1=1 就可以

避免从query cache中获取数据，而得到真实的执行状态分

析。

●是否存在同一个查询短期内频繁出现的情况

?涉及前端缓存优化

?抓大放小，解决显著问题

◆不苛求解决所有优化问题，但是应以保证线上服务稳定可靠为目标。

◆解决与评估要同时进行，新的策略或解决方案务必经过评估后上线。

总结

●要学会怎样分析问题，而不是单纯拍脑袋优化

●慢查询只是最基础的东西，要学会优化0.01秒的查询请求。

●当发生连接阻塞时，不同状态的阻塞有不同的原因，要找到原因，如果不对症下药，

就会南辕北辙

?范例：如果本身系统内存已经超载，已经使用到了swap，而还在考虑加大缓

存来优化查询，那就是自寻死路了。

●监测与跟踪要经常做，而不是出问题才做

?读取频繁度抽样监测

◆全监测不要搞，i/o吓死人。

◆按照一个抽样比例抽样即可。

◆针对抽样中发现的问题，可以按照特定SQL在特定时间内监测一段全查

询记录，但仍要考虑i/o影响。

?写入频繁度监测

◆基于binlog解开即可，可定时或不定时分析。

?微慢查询抽样监测

◆高并发情况下，查询请求时间超过0.01秒甚至0.005秒的，建议酌情抽样

记录。

?连接数预警监测

◆连接数超过特定阈值的情况下，虽然数据库没有崩溃，建议记录相关连接

状态。

●学会通过数据和监控发现问题，分析问题，而后解决问题顺理成章。特别是要学会

在日常监控中发现隐患，而不是问题爆发了才去处理和解决。

Mysql 运维优化

存储引擎类型

●Myisam 速度快，响应快。表级锁是致命问题。

●Innodb 目前主流存储引擎

?行级锁

◆务必注意影响结果集的定义是什么

◆行级锁会带来更新的额外开销，但是通常情况下是值得的。

?事务提交

◆对i/o效率提升的考虑

◆对安全性的考虑

●HEAP 内存引擎

?频繁更新和海量读取情况下仍会存在锁定状况

内存使用考量

●理论上，内存越大，越多数据读取发生在内存，效率越高

●要考虑到现实的硬件资源和瓶颈分布

●学会理解热点数据，并将热点数据尽可能内存化

?所谓热点数据，就是最多被访问的数据。

?通常数据库访问是不平均的，少数数据被频繁读写，而更多数据鲜有读写。

?学会制定不同的热点数据规则，并测算指标。

◆热点数据规模，理论上，热点数据越少越好，这样可以更好的满足业务的

增长趋势。

◆响应满足度，对响应的满足率越高越好。

◆比如依据最后更新时间，总访问量，回访次数等指标定义热点数据，并测

算不同定义模式下的热点数据规模

性能与安全性考量

●数据提交方式

?innodb_flush_log_at_trx_commit = 1 每次自动提交，安全性高，i/o压力大

?innodb_flush_log_at_trx_commit = 2 每秒自动提交，安全性略有影响，i/o承载

强。

●日志同步

?Sync-binlog =1 每条自动更新，安全性高，i/o压力大

?Sync-binlog = 0 根据缓存设置情况自动更新，存在丢失数据和同步延迟风险，

i/o承载力强。

●性能与安全本身存在相悖的情况，需要在业务诉求层面决定取舍

?学会区分什么场合侧重性能，什么场合侧重安全

?学会将不同安全等级的数据库用不同策略管理

存储压力优化

●顺序读写性能远高于随机读写

●日志类数据可以使用顺序读写方式进行

●将顺序写数据和随机读写数据分成不同的物理磁盘，有助于i/o压力的疏解，前提

是，你确信你的i/o压力主要来自于可顺序写操作（因随机读写干扰导致不能顺序

写，但是确实可以用顺序写方式进行的i/o操作）。

运维监控体系

●系统监控

?服务器资源监控

◆Cpu, 内存，硬盘空间，i/o压力

◆设置阈值报警

?服务器流量监控

◆外网流量，内网流量

◆设置阈值报警

?连接状态监控

◆Show processlist 设置阈值，每分钟监测，超过阈值记录

●应用监控

?慢查询监控

◆慢查询日志

◆如果存在多台数据库服务器，应有汇总查阅机制。

?请求错误监控

◆高频繁应用中，会出现偶发性数据库连接错误或执行错误，将错误信息记

录到日志，查看每日的比例变化。

◆偶发性错误，如果数量极少，可以不用处理，但是需时常监控其趋势。

◆会存在恶意输入内容，输入边界限定缺乏导致执行出错，需基于此防止恶

意入侵探测行为。

?微慢查询监控

◆高并发环境里，超过0.01秒的查询请求都应该关注一下。

?频繁度监控

◆写操作，基于binlog，定期分析。

◆读操作，在前端db封装代码中增加抽样日志，并输出执行时间。

◆分析请求频繁度是开发架构进一步优化的基础

◆最好的优化就是减少请求次数！

●总结：

?监控与数据分析是一切优化的基础。

?没有运营数据监测就不要妄谈优化！

?监控要注意不要产生太多额外的负载，不要因监控带来太多额外系统开销

Mysql 架构优化

架构优化目标

防止单点隐患

●所谓单点隐患，就是某台设备出现故障，会导致整体系统的不可用，这个设备就是

单点隐患。

●理解连带效应，所谓连带效应，就是一种问题会引发另一种故障，举例而言，

memcache+mysql是一种常见缓存组合，在前端压力很大时，如果memcache崩溃，理论上数据会通过mysql读取，不存在系统不可用情况，但是mysql无法对抗如此

大的压力冲击，会因此连带崩溃。因A系统问题导致B系统崩溃的连带问题，在

运维过程中会频繁出现。

?实战范例：在mysql连接不及时释放的应用环境里，当网络环境异常（同机

房友邻服务器遭受拒绝服务攻击，出口阻塞），网络延迟加剧，空连接数急剧

增加，导致数据库连接过多崩溃。

?实战范例2：前端代码通常我们封装mysql_connect和memcache_connect，

二者的顺序不同，会产生不同的连带效应。如果mysql_connect在前，那么一

旦memcache连接阻塞，会连带mysql空连接过多崩溃。

?连带效应是常见的系统崩溃，日常分析崩溃原因的时候需要认真考虑连带效应

的影响，头疼医头，脚疼医脚是不行的。

方便系统扩容

●数据容量增加后，要考虑能够将数据分布到不同的服务器上。

●请求压力增加时，要考虑将请求压力分布到不同服务器上。

●扩容设计时需要考虑防止单点隐患。

安全可控，成本可控

●数据安全，业务安全

●人力资源成本>带宽流量成本>硬件成本

?成本与流量的关系曲线应低于线性增长（流量为横轴，成本为纵轴）。

?规模优势

●本教程仅就与数据库有关部分讨论，与数据库无关部门请自行参阅其他学习资料。

分布式方案

分库&拆表方案

●基本认识

?用分库&拆表是解决数据库容量问题的唯一途径。

?分库&拆表也是解决性能压力的最优选择。

?分库–不同的数据表放到不同的数据库服务器中（也可能是虚拟服务器）

?拆表–一张数据表拆成多张数据表，可能位于同一台服务器，也可能位于多台服

务器（含虚拟服务器）。

●去关联化原则

?摘除数据表之间的关联，是分库的基础工作。

?摘除关联的目的是，当数据表分布到不同服务器时，查询请求容易分发和处理。

?学会理解反范式数据结构设计，所谓反范式，第一要点是不用外键，不允许Join

操作，不允许任何需要跨越两个表的查询请求。第二要点是适度冗余减少查询请求，比如说，信息表，fromuid, touid, message字段外，还需要一个fromuname字段记录

用户名，这样查询者通过touid查询后，能够立即得到发信人的用户名，而无需进

行另一个数据表的查询。

?去关联化处理会带来额外的考虑，比如说，某一个数据表内容的修改，对另一个数

据表的影响。这一点需要在程序或其他途径去考虑。

●分库方案

?安全性拆分

◆将高安全性数据与低安全性数据分库，这样的好处第一是便于维护，第二是高

安全性数据的数据库参数配置可以以安全优先，而低安全性数据的参数配置以

性能优先。参见运维优化相关部分。

?顺序写数据与随机读写数据分库

◆顺序数据与随机数据区分存储地址，保证物理i/o优化。这个实话说，我只听

说了概念，还没学会怎么实践。

?基于业务逻辑拆分

◆根据数据表的内容构成，业务逻辑拆分，便于日常维护和前端调用。

◆基于业务逻辑拆分，可以减少前端应用请求发送到不同数据库服务器的频次，

从而减少链接开销。

◆基于业务逻辑拆分，可保留部分数据关联，前端web工程师可在限度范围内

执行关联查询。

?基于负载压力拆分

◆基于负载压力对数据结构拆分，便于直接将负载分担给不同的服务器。

◆基于负载压力拆分，可能拆分后的数据库包含不同业务类型的数据表，日常维

护会有一定的烦恼。

●分表方案

?数据量过大或者访问压力过大的数据表需要切分

?忙闲分表

◆单数据表字段过多，可将频繁更新的整数数据与非频繁更新的字符串数据切分

◆范例user表，个人简介，地址，QQ号，联系方式，头像这些字段为字符

串类型，更新请求少；最后登录时间，在线时常，访问次数，信件数这些字

段为整数型字段，更新频繁，可以将后面这些更新频繁的字段独立拆出一张数

据表，表内容变少，索引结构变少，读写请求变快。

?横向切表

◆等分切表，如哈希切表或其他基于对某数字取余的切表。等分切表的优点是负

载很方便的分布到不同服务器；缺点是当容量继续增加时无法方便的扩容，需

要重新进行数据的切分或转表。而且一些关键主键不易处理。

◆递增切表，比如每1kw用户开一个新表，优点是可以适应数据的自增趋势；

缺点是往往新数据负载高，压力分配不平均。

◆日期切表，适用于日志记录式数据，优缺点等同于递增切表。

◆个人倾向于递增切表，具体根据应用场景决定。

?热点数据分表

◆将数据量较大的数据表中将读写频繁的数据抽取出来，形成热点数据表。通常

一个庞大数据表经常被读写的内容往往具有一定的集中性，如果这些集中数据

单独处理，就会极大减少整体系统的负载。

◆热点数据表与旧有数据关系

●可以是一张冗余表，即该表数据丢失不会妨碍使用，因源数据仍存在于旧

有结构中。优点是安全性高，维护方便，缺点是写压力不能分担，仍需要

同步写回原系统。

●可以是非冗余表，即热点数据的内容原有结构不再保存，优点是读写效率

全部优化；缺点是当热点数据发生变化时，维护量较大。

●具体方案选择需要根据读写比例决定，在读频率远高于写频率情况下，优

先考虑冗余表方案。

◆热点数据表可以用单独的优化的硬件存储，比如昂贵的闪存卡或大内存系统。

◆热点数据表的重要指标

●热点数据的定义需要根据业务模式自行制定策略，常见策略为，按照最新

的操作时间；按照内容丰富度等等。

●数据规模，比如从1000万条数据，抽取出100万条热点数据。

●热点命中率，比如查询10次，多少次命中在热点数据内。

●理论上，数据规模越小，热点命中率越高，说明效果越好。需要根据业务

自行评估。

◆热点数据表的动态维护

●加载热点数据方案选择

?定时从旧有数据结构中按照新的策略获取

?在从旧有数据结构读取时动态加载到热点数据

●剔除热点数据方案选择

?基于特定策略，定时将热点数据中访问频次较少的数据剔除

?如热点数据是冗余表，则直接删除即可，如不是冗余表，需要回写给

旧有数据结构。

◆通常，热点数据往往是基于缓存或者key-value 方案冗余存储，所以这里提到

的热点数据表，其实更多是理解思路，用到的场合可能并不多….

●表结构设计

?查询冗余表设计

◆涉及分表操作后，一些常见的索引查询可能需要跨表，带来不必要的麻烦。确

认查询请求远大于写入请求时，应设置便于查询项的冗余表。

◆实战范例，

●用户分表，将用户库分成若干数据表

●基于用户名的查询和基于uid的查询都是高并发请求。

●用户分表基于uid分成数据表，同时基于用户名做对应冗余表。

◆冗余表要点

●数据一致性，简单说，同增，同删，同更新。

●可以做全冗余，或者只做主键关联的冗余，比如通过用户名查询uid，再

基于uid查询源表。

?中间数据表

◆为了减少会涉及大规模影响结果集的表数据操作，比如count，sum操作。应

将一些统计类数据通过中间数据表保存。

◆中间数据表应能通过源数据表恢复。

◆实战范例：

●论坛板块的发帖量，回帖量，每日新增数据等

●网站每日新增用户数等。

●后台可以通过源数据表更新该数字。

?历史数据表

◆历史数据表对应于热点数据表，将需求较少又不能丢弃的数据存入，仅在少数

情况下被访问。

主从架构

●基本认识

?读写分离对负载的减轻远远不如分库分表来的直接。

?写压力会传递给从表，只读从库一样有写压力，一样会产生读写锁！

?一主多从结构下，主库是单点隐患，很难解决（如主库当机，从库可以响应读写，

但是无法自动担当主库的分发功能）

?主从延迟也是重大问题。一旦有较大写入问题，如表结构更新，主从会产生巨大延

迟。

●应用场景

?在线热备

?异地分布

◆写分布，读统一。

◆仍然困难重重，受限于网络环境问题巨多！

?自动障碍转移

◆主崩溃，从自动接管

?个人建议，负载均衡主要使用分库方案，主从主要用于热备和障碍转移。

●潜在优化点

?为了减少写压力，有些人建议主不建索引提升i/o性能，从建立索引满足查询要求。

个人认为这样维护较为麻烦。而且从本身会继承主的i/o压力，因此优化价值有限。

该思路特此分享，不做推荐。

故障转移处理

●要点

?程序与数据库的连接，基于虚地址而非真实ip，由负载均衡系统监控。

?保持主从结构的简单化，否则很难做到故障点摘除。

●思考方式

?遍历对服务器集群的任何一台服务器，前端web，中间件，监控，缓存，db等等，

假设该服务器出现故障，系统是否会出现异常？用户访问是否会出现异常。

?目标：任意一台服务器崩溃，负载和数据操作均会很短时间内自动转移到其他服务

器，不会影响业务的正常进行。不会造成恶性的数据丢失。（哪些是可以丢失的，

哪些是不能丢失的）

缓存方案

缓存结合数据库的读取

●Memcached是最常用的缓存系统

●Mysql 最新版本已经开始支持memcache插件，但据牛人分析，尚不成熟，暂不推荐。

●数据读取

?并不是所有数据都适合被缓存，也并不是进入了缓存就意味着效率提升。

?命中率是第一要评估的数据。

?如何评估进入缓存的数据规模，以及命中率优化，是非常需要细心分析的。

●实景分析：前端请求先连接缓存，缓存未命中连接数据库，进行查询，未命

中状态比单纯连接数据库查询多了一次连接和查询的操作；如果缓存命中率很

低，则这个额外的操作非但不能提高查询效率，反而为系统带来了额外的负载

和复杂性，得不偿失。

?相关评估类似于热点数据表的介绍。

?善于利用内存，请注意数据存储的格式及压缩算法。

●Key-value 方案繁多，本培训文档暂不展开。

缓存结合数据库的写入

●利用缓存不但可以减少数据读取请求，还可以减少数据库写入i/o压力

●缓存实时更新，数据库异步更新

?缓存实时更新数据，并将更新记录写入队列

?可以使用类似mq的队列产品，自行建立队列请注意使用increment来维持队列序

号。

?不建议使用get 后处理数据再set的方式维护队列

●测试范例：

●范例1

$var=Memcache_get($memcon,”var”);

memcache_set($memcon,”var”,$var);

这样一个脚本，使用apache ab去跑，100个并发，跑10000次，然后输出缓存存取的数据，很遗憾，并不是1000，而是5000多，6000多这样的数字，

中间的数字全在get & set的过程中丢掉了。

原因，读写间隔中其他并发写入，导致数据丢失。

●范例2

用memcache_increment来做这个操作，同样跑测试

会得到完整的10000，一条数据不会丢。

●结论：用increment存储队列编号，用标记+编号作为key存储队列内容。

?后台基于缓存队列读取更新数据并更新数据库

●基于队列读取后可以合并更新

●更新合并率是重要指标

●实战范例：

某论坛热门贴，前端不断有views=views+1数据更新请求。

缓存实时更新该状态

后台任务对数据库做异步更新时，假设执行周期是5分钟，那么五分钟可能会

接收到这样的请求多达数十次乃至数百次，合并更新后只执行一次update即

可。

类似操作还包括游戏打怪，生命和经验的变化；个人主页访问次数的变化等。

?异步更新风险

●前后端同时写，可能导致覆盖风险。

●使用后端异步更新，则前端应用程序就不要写数据库，否则可能造成写入

冲突。一种兼容的解决方案是，前端和后端不要写相同的字段。

●实战范例：

用户在线上时，后台异步更新用户状态。

管理员后台屏蔽用户是直接更新数据库。

结果管理员屏蔽某用户操作完成后，因该用户在线有操作，后台异步更新

程序再次基于缓存更新用户状态，用户状态被复活，屏蔽失效。

●缓存数据丢失或服务崩溃可能导致数据丢失风险。

●如缓存中间出现故障，则缓存队列数据不会回写到数据库，而用户会认为

已经完成，此时会带来比较明显的用户体验问题。

●一个不彻底的解决方案是，确保高安全性，高重要性数据实时数据更新，

而低安全性数据通过缓存异步回写方式完成。此外，使用相对数值操作而

不是绝对数值操作更安全。

?范例：支付信息，道具的购买与获得，一旦丢失会对用户造成极大的

伤害。而经验值，访问数字，如果只丢失了很少时间的内容，用户还

是可以容忍的。

?范例：如果使用Views=Views+…的操作，一旦出现数据格式错误，

从binlog中反推是可以进行数据还原，但是如果使用Views=特定值

的操作，一旦缓存中数据有错误，则直接被赋予了一个错误数据，无

法回溯！

●异步更新如出现队列阻塞可能导致数据丢失风险。

●异步更新通常是使用缓存队列后，在后台由cron或其他守护进程写入数

如果队列生成的速度>后台更新写入数据库的速度，就会产生阻塞，导致数据越累计越多，数据库响应迟缓，而缓存队列无法迅速执行，导致溢出或者过期失效。

sql语句(mysql优化)绝对经典

sql语句（mysql优化）绝对经典 误区1：count(1)和count(primary_key) 优于count(*) 很多人为了统计记录条数，就使用count(1) 和count(primary_key) 而不是count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对count(*) 计数操作做了一些特别的优化。 误区2：count(column) 和count(*) 是一样的 这个误区甚至在很多的资深工程师或者是DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。count(column) 是表示结果集中有多少个column字段不为空的记录，count(*) 是表示整个结果集有多少条记录 误区3：select a,b from … 比select a,b,c from …可以让数据库访问更少的数据量 这个误区主要存在于大量的开发人员中，主要原因是对数据库的存储原理不是太了解。实际上，大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作block 或者page)为单位，一般为4KB，8KB… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。 所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取a,b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。(覆盖索引) 误区4：order by 一定需要排序操作 我们知道索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回，因为数据库知道数据已经满足我们的排序需求了。实际上，利用索引来优化有排序需求的SQL，是一个非常重要的优化手段。延伸阅读：MySQL ORDER BY 的实现分析，MySQL 中GROUP BY 基本实现原理以及MySQL DISTINCT 的基本实现原理。（order by null）

优化mysql数据库性能

为了提高性能建议作如下优化修改： 优化mysql数据库性能的参数： (1)、max_connections： 允许的同时客户的数量。增加该值增加mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到too many connections错误。默认数值是16384，请根据实际情况设置此参数。 (2)、key_buffer_size： 索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是10M，请根据实际情况设置此参数。 (3)、sort_buffer： 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是256K，请根据实际情况设置此参数。 4)、table_cache： 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是256，，请根据实际情况设置此参数。 (5)、thread_cache_size： 可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能修改这个变量值。通过比较connections 和threads_created 状态的变量，可以看到这个变量的作用。默认数值是8，请根据实际情况设置此参数。 注：以上参数的调整可以通过修改C:\AppServ\MySQL\my.ini 文件并重启mysql 实现。这是一个比较谨慎的工作，上面的结果只供参考，请根据具体主机的硬件情况（特别是内存大小）进一步修改。 优化配置文件： C:\zxin10\Was\tomcat\conf\ server.xml (6)、在server.xml中修改标红相关参数。 (7)、

mysql服务性能优化my_cnf配置说明详解16G内存

mysql服务性能优化—https://www.wendangku.net/doc/ba8673141.html,f配置说明详解 （16G内存） MYSQL服务器https://www.wendangku.net/doc/ba8673141.html,f配置文档详解 硬件：内存16G [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit = 10240 back_log = 600 #在MYSQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。 max_connections = 3000 #MySQL允许最大的进程连接数，如果经常出现Too Many Connections的错误提示，则需要增大此值。 max_connect_errors = 6000 #设置每个主机的连接请求异常中断的最大次数，当超过该次数，MYSQL服务器将禁止host 的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。 table_cache = 614 #指示表调整缓冲区大小。# table_cache 参数设置表高速缓存的数目。每个连接进来，都会至少打开一个表缓存。#因此， table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个#并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的查询#的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。 # 当 Mysql 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果#还

MySQL5.1性能优化方案

MySQL5.1性能优化方案 1.平台数据库 1.1.操作系统 Red Hat Enterprise Linux Server release 5.4 (Tikanga) ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped 32位Linux服务器，单独作为MySQL服务器使用。 1.2.M ySQL 系统使用的是MySQL5.1，最新的MySQL5.5较之老版本有了大幅改进。主要体现在以下几个方面： 1）默认存储引擎更改为InnoDB InnoDB作为成熟、高效的事务引擎，目前已经广泛使用，但MySQL5.1之前的版本默认引擎均为MyISAM，此次MySQL5.5终于将默认数据库存储引擎改为InnoDB，并且引进了Innodb plugin 1.0.7。此次更新对数据库的好处是显而易见的：InnoDB的数据恢复时间从过去的一个甚至几个小时，缩短到几分钟(InnoDB plugin 1.0.7，InnoDB plugin 1.1，恢复时采用红-黑树)。InnoDB Plugin 支持数据压缩存储，节约存储，提高内存命中率，并且支持adaptive flush checkpoint, 可以在某些场合避免数据库出现突发性能瓶颈。 Multi Rollback Segments：原来InnoDB只有一个Segment，同时只支持1023的并发。现已扩充到128个Segments，从而解决了高并发的限制。 2）多核性能提升

mysql性能优化-慢查询分析、优化索引和配置

mysql性能优化-慢查询分析、优化索引和配置目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) max_connections 2) back_log 3) interactive_timeout 4) key_buffer_size 5) query_cache_size 6) record_buffer_size 7) read_rnd_buffer_size 8) sort_buffer_size 9) join_buffer_size 10) table_cache 11) max_heap_table_size 12) tmp_table_size

13) thread_cache_size 14) thread_concurrency 15) wait_timeout 一、优化概述 MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。 除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。 二、查询与索引优化分析 在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。 1 性能瓶颈定位 Show命令 我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈： Mysql> show status ——显示状态信息（扩展show status like ‘XXX’） Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’） Mysql> show innodb status ——显示InnoDB存储引擎的状态 Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

MySQL大数据量的查询提高性能优化

最近一段时间参与的项目要操作百万级数据量的数据，普通SQL查询效率呈直线下降，而且如果where中的查询条件较多时，其查询速度简直无法容忍。之前数据量小的时候，查询语句的好坏不会对执行时间有什么明显的影响，所以忽略了许多细节性的问题。 经测试对一个包含400多万条记录的表执行一条件查询，其查询时间竟然高达40几秒，相信这么高的查询延时，任何用户都会抓狂。因此如何提高sql语句查询效率，显得十分重要。以下是结合网上流传比较广泛的几个查询语句优化方法： 基本原则：数据量大的时候，应尽量避免全表扫描，应考虑在where 及order by 涉及的列上建立索引，建索引可以大大加快数据的检索速度。但是，有些情况索引是不会起效的，因此，需要下面的做法进行优化： 1、应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 2、应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 3、尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20 4、下面的查询也将导致全表扫描：

千万级的mysql数据库与优化方法

千万级的mysql数据库与优化方法 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。 2.应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： Sql代码 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： Sql代码 3.应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 4.应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：Sql代码 可以这样查询： Sql代码 5.in 和not in 也要慎用，否则会导致全表扫描，如： 对于连续的数值，能用between 就不要用in 了： 6.下面的查询也将导致全表扫描： Sql代码

若要提高效率，可以考虑全文检索。 7.如果在where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： Sql代码 可以改为强制查询使用索引： 8.应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： 应改为: 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：Sql代码 应改为: 10.不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 12.不要写一些没有意义的查询，如需要生成一个空表结构：

Mysql千万级别数据优化方案总结

Mysql千万级别数据优化方案 目录 目录 (1) 一、目的与意义 (2) 1)说明 (2) 二、解决思路与根据（本测试表中数据在千万级别） (2) 1)建立索引 (2) 2)数据体现（主键非索引，实际测试结果其中fid建立索引） (2) 3)MySQL分页原理 (2) 4)经过实际测试当对表所有列查询时 (2) 三、总结 (3) 1)获得分页数据 (3) 2)获得总页数：创建表记录大数据表中总数通过触发器来维护 (3)

一、目的与意义 1)说明 在MySql单表中数据达到千万级别时数据的分页查询结果时间过长，对此进行优达 到最优效果，也就是时间最短；(此统计利用的jdbc连接，其中fid为该表的主键;) 二、解决思路与根据（本测试表中数据在千万级别） 1)建立索引 优点：当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜 索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记 录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是 在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索 引中的ROWID（相当于页码）快速找到表中对应的记录。 缺点：当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降 低了数据的维护速度。 2)数据体现（主键非索引，实际测试结果其中fid建立索引） 未创建索引：SELECT fid from t_history_data LIMIT 8000000,10结果：13.396s 创建索引：SELECT fid from t_history_data LIMIT 8000000,10结果：2.896s select*fromt_history_datawherefidin (任意十条数据的id )结果：0.141s 首先通过分页得到分页的数据的ID,将ID拼接成字符串利用SQL语句 select * from table where ID in （ID字符串）此语句受数据量大小的影响比较小 （如上测试）； 3)MySQL分页原理 MySQL的limit工作原理就是先读取n条记录，然后抛弃前n条，读m条想要 的，所以n越大，性能会越差。 优化前SQL: SELECT * FROM v_history_data LIMIT 5000000, 1010.961s 优化后SQL: SELECT * FROM v_history_data INNER JOIN (SELECT fid FROM t_history_data LIMIT 5000000, 10) a USING (fid)1.943s 分别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后 抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了， 然后通过member_id读取需要的列 4)经过实际测试当对表所有列查询时 select * from table 会比select （所有列名）from table 快些（以查询8000000

Mysql性能优化

MySQL性能优化 性能优化是通过某些有效的方法来提高MySQL的运行速度，减少占用的磁盘空间。性能优化包含很多方面，例如优化查询速度，优化更新速度和优化MySQL服务器等。本文介绍方法的主要有： 优化查询 优化数据库结构 优化MySQL服务器 数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能。语法：SHOW STATUE LIKE ‘value’；其中value参数是常用的几个统计参数。 Connections：连接MySQL服务器的次数 Uptime：MySQL服务器的上线时间； Slow_queries：慢查询的次数； Com_select：查询操做的次数； Com_insert：插入操作的次数； Com_delete：删除操作的次数； Com_update：更新操作的次数； 1优化查询 查询操作是最频繁的操作，提高了查询速度可以有效提高MySQL 数据库的性能。 首先要对查询语句进行分析，分析查询语句的命令是EXPLAIN语句和DESCRIBE语句。比如 EXPLAIN SELECT * FROM student \G； 索引可以快速定位表中的某条记录。使用索引也可以提高数据库查

询的速度，从而提高数据库的性能。如果不使用索引，查询语句将 表中的所有字段。这样查询的速度会很慢。如果使用了索引，查询语句只会查询索引字段。这样就减少查询的记录数，达到提高查询效率的目的。 现在看一个查询语句中没有索引的使用情况： SELECT * FROM student WHERE name = ‘张三’；这样会对student表中的所有数据都查询一下，对比一下name的字段是否是张三。 然后我们在name字段上建立一个名为index_name的索引：CREATE INDEX index_name ON student（name）； 现在name字段上面已经有索引了，再进行该select语句查询的速度就非常快了，不需要遍历整个表。 但是有些时候即使查询时使用的是索引，但索引并没有起作用。比如使用了LIKE关键字进行查询时，如果匹配字符串的第一个字符 为‘%’，索引不会被使用。如果‘%’不是在第一个位置，索引就会被使用。 另一种情况是在表的多个字段上创建一个索引，比如 CREATE INDEX index ON student（birth，department）；这样只有查询语句条件中使用字段name时，索引才会被用到。因为name字段是多列索引的第一个字段，只有查询条件中使用了name字段才会使索引index起作用。 2优化子查询 很多查询中需要使用子查询。子查询可以使查询语句很灵活，但子查询的执行效率不高。MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快。

优化MySQL数据库性能的几个好方法

1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2、使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

【黑马程序员】MySQL的性能调优一

【黑马程序员】MySQL的性能调优一 什么是MySQL，怎么安装，怎么使用，我这里不做说明了。 一、MySQL 与其他数据库的简单比较 1.1性能比较 性能方面，一直是MySQL 引以为自豪的一个特点。在权威的第三方评测机构多次测试较量各种数据库TPCC 值的过程中，MySQL 一直都有非常优异的表现，而且在其他所有商用的通用数据库管理系统中，仅仅只有Oracle 数据库能够与其一较高下。至于各种数据库详细的性能数据，我这里就不便记录，大家完全可以通过网上第三方评测机构公布的数据了解具体细节信息。 MySQL 一直以来奉行一个原则，那就是在保证足够的稳定性的前提下，尽可能的提高自身的处理能力。也就是说，在性能和功能方面，MySQL 第一考虑的要素主要还是性能，MySQL希望自己是一个在满足客户99%的功能需求的前提下，花掉剩下的大部分精力来性能努力，而不是希望自己是成为一个比其他任何数据库的功能都要强大的数据库产品。 1.2可靠性 关于可靠性的比较，并没有太多详细的评测比较数据，但是从目前业界的交流中可以了解到，几大商业厂商的数据库的可靠性肯定是没有太多值得怀疑的。但是做为开源数据库管理系统的代表，MySQL 也有非常优异的表现，而并不是像有些人心中所怀疑的那样，因为不是商业厂商所提供，就会不够稳定不够健壮。从当前最火的Facebook 这样大型的网站都是使用MySQL 数据库，就可以看出，MySQL 在稳定可靠性方面，并不会

比我们的商业厂商的产品有太多逊色。而且排在全球前10 位的大型网站里面，大部分都有部分业务是运行在MySQL数据库环境上，如Yahoo，Google 等。 总的来说，MySQL 数据库在发展过程中一直有自己的三个原则：简单、高效、可靠。从上面的简单比较中，我们也可以看出，在MySQL 自己的所有三个原则上面，没有哪一项是做得不好的。而且，虽然功能并不是MySQL 自身所追求的三个原则之一，但是考虑到当前用户量的急剧增长，用户需求越来越越多样化，MySQL 也不得不在功能方面做出大量的努力，来不断满足客户的新需求。比如最近版本中出现的Eent Scheduler （类似于Oracle 的Job 功能），Partition 功能，自主研发的Maria 存储引擎在功能方面的扩展，Falcon 存储引擎对事务的支持等等，都证明了MySQL 在功能方面也开始了不懈的努力。 任何一种产品，都不可能是完美的，也不可能适用于所有用户。我们只有衡量了每一种产品的各种特性之后，从中选择出一种最适合于自身的产品。 二、MySQL 的主要适用场景 据说目前MySQL 用户已经达千万级别了，其中不乏企业级用户。可以说是目前最为流行的开源数据库管理系统软件了。任何产品都不可能是万能的，也不可能适用于所有的应用场景。 那么MySQL 到底在什么场景下适用什么场景下不适用呢？ 1、Web 网站系统 Web 站点，是MySQL 最大的客户群，也是MySQL 发展史上最为重要的支撑力量，这一点在最开始的MySQL Server 简介部分就已经说明过。 MySQL 之所以能成为Web 站点开发者们最青睐的数据库管理系统，是因为

MySQL数据库性能(SQL)优化方案-期末论文

高级数据库技术——期末论文 基于SQL查询的MySQL数据库性能优化研究 ：XX 学号:2014XXXXX 学院：计算机学院

摘要： 查询是数据库系统中最基本也是最常用的一种操作，是否具有较快的执行速度，已成为数据库用户和设计者极其关心的问题。在研究开源数据库管理系统MySQL 查询优化技术的基础上，主要结合传统SQL操作优化、深度分析 MySQL 源代码、现代数据库发展几方面进行诸如参数调优，MySQL关联查询，重写相关规则等容展开优化分析研究。 关键词：查询优化，查询重用，查询重写，计划优化

一、传统SQL查询优化操作 1.选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2.使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，

万字总结：学习MySQL优化原理,这一篇就够了!

万字总结：学习MySQL优化原理，这一篇就够了！ Java开源分享 说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。 MySQL逻辑架构 如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。 MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。 MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。 MySQL查询过程

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。 当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？ MySQL查询过程 客户端/服务端通信协议 MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。 客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。 与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。 查询缓存

mysql的性能检查和调优

一、识别有问题的查询语句 processlist命令的输出结果显示了有哪些线程在运行，可以帮助识别出有问题的查询语句，两种方式使用这个命令。 1. 进入m ysql/bin目录下输入mysqladmin processlist; 2. 启动m ysql，输入show processlist; 如果有SUPER 权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。 得到数据形式如下（只截取了三条）： mysql> show processlist; +-----+-------------+--------------------+-------+---------+-------+----------------------------------+---------- | Id | User | Host | db | Command | Tim e| State | Info +-----+-------------+--------------------+-------+---------+-------+----------------------------------+---------- |207|root |192.168.0.20:51718 |m ytest | Sleep | 5 | | NULL |208|root |192.168.0.20:51719 |m ytest | Sleep | 5 | | NULL |220|root |192.168.0.20:51731 |m ytest |Query | 84 | Locked | select bookname,culture,value,type from book where id=001 先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql 语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。tim e列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to t m p table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。 这个命令中最关键的就是state列，m ysql列出的状态主要有以下几种： Checking table 正在检查数据表（这是自动的）。 Closing tables 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。 Connect Out 复制从服务器正在连接主服务器。 Copying to tmp table on disk 由于临时结果集大于t m p_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。 Creating t m p table 正在创建临时表以存放部分查询结果。

MySQL数据库性能(SQL)优化方案

MySQL数据库性能（SQL）优化方案本文探讨了提高MySQL 数据库性能的思路，并从8个方面给出了具体的解决方法。 1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN 来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2、使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

MySQL 数据库性能优化之表结构优化

很多人都将数据库设计范式作为数据库表结构设计“圣经”，认为只要按照这个范式需求设计，就能让设计出来的表结构足够优化，既能保证性能优异同时还能满足扩展性要求。殊不知，在N年前被奉为“圣经”的数据库设计3范式早就已经不完全适用了。这里我整理了一些比较常见的数据库表结构设计方面的优化技巧，希望对大家有用。 由于MySQL数据库是基于行(Row)存储的数据库，而数据库操作 IO 的时候是以 page(block)的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page 中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。 数据类型选择 数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能。 我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景，因为精细化的数据类型设置可能带来维护成本的提高，过度优化也可能会带来其他的问题： 1、数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。 2、字符类型：非万不得已不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。 3、时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。

mysql性能优化培训

一概述 数据库属于IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。 而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读 取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一 步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。 所以我们先从MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化 二表结构优化： 由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作IO 的时候是以page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小， 就会使每个page中可存放的数据行数增大，那么每次IO 可访问的行数也就增多 了。反过来说，处理相同行数的数据，需要访问的page 就会减少，也就是IO 操 作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中 存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数 据命中的几率，也就是缓存命中率。 优化原则：使数据量最小，性能的瓶颈在于磁盘性能，数据量越小，磁盘读取数据的速度就越快，同时，在较小的列上建立索引，索引文件占用的资源也会更少。 1.尽可能地使用最有效(最小)的数据类型。 MySQL有很多节省磁盘空间和内存的专业化类型。尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间 要少25% 2.尽量使用NOT NULL NULL 类型比较特殊，SQL 难优化。虽然MySQL NULL类型和Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极 大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外 的存放空间。很多人觉得NULL 会节省一些空间，所以尽量让NULL来达到节省IO 的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带 来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所 以尽量确保DEFAULT 值不是NULL，也是一个很好的表结构设计优化习惯。 3.只创建你确实需要的索引。 索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的 列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好