现在MySQL 8和PostgreSQL 10已经发布，现在是重新审视两个主要的开源关系数据库如何相互竞争的好时机。

在这些版本之前，一般的看法是，虽然Postgres在功能集及血统方面更胜一筹，但MySQL在大规模并发读/写操作方面进行了更多的大规模测试。

但是随着最新版本的发布，两者之间的差距已大大缩小。

功能对比

下面看一下通常我们认为比较时髦的功能。

特征	MySQL8	PostgreSQL10
查询和分析
通用表达式(CTEs)	✔ New	✔
视窗功能	✔ New	✔
数据类型
支持JSON	✔ Improved	✔
GIS / SRS	✔ Improved	✔
全文搜索	✔	✔
扩展性
逻辑复制	✔	✔ New
半同步复制	✔	✔ New
声明式分区	✔	✔ New

过去常常说MySQL最适合在线交易，而PostgreSQL最适合分析过程，但是现在不再是如此局面。

通用表达式（CTEs）和窗口函数一直是多数人选择PostgreSQL的主要原因。但是现在，在同一个表中employees引用对表进行递归遍历boss_id，或者在排序结果中找到中间值（或50％百分位数），在MySQL上不再是问题。

PostgreSQL上的复制缺乏配置灵活性，这是Uber转向MySQL的原因。但是现在有了逻辑复制，可以通过使用更新版本的Postgres创建副本并切换到该副本来实现零停机时间升级。截断大型时序事件表中的陈旧分区也容易得多。

在功能方面，两个数据库现在彼此相同。

二者有什么区别？

现在有一个问题就是，我们到底是选择MySQL还是PostgreSQL，那选择的原因又是什么？

生态系统就是这些因素之一。MySQL拥有一个强大的生态系统，其中包含MariaDB，Percona，Galera等变体，以及InnoDB以外的其他存储引擎，但它们也可能令人不知所措。Postgres的高端选项有限，但是随着最新版本引入的新功能，这种情况将会改变。

治理是另一个因素。每个人都在担心甲骨文（或最初为SUN）收购MySQL时，他们会毁了该产品，但过去十年来情况并非如此。实际上，收购后发展加速。Postgres在工作治理和协作社区方面拥有悠久的历史。

体系结构的基本原理不会经常更改，但是值得值得我们回顾。

特征	MySQL8	PostgreSQL10
架构	单进程	多进程
并发	多线程	fork(2)
表结构	聚合索引	Heap(堆)
页面压缩	透明	TOAST
更新	就地/回滚Segments	仅追加/ Hot
垃圾回收	清除线程	自动回收进程
Transaction Log	REDO Log (WAL)	WAL
Replication Log	Separate (Binlog)	WAL

进程对比线程

当Postgres fork一个子进程来建立连接时，每个连接最多需要10 MB的空间。与MySQL的“每次连接线程”模型相比，内存压力更大，后者在64位平台上，线程的默认堆栈大小为 256KB。（当然，线程局部排序缓冲区等可以使此开销的重要性降低，即使可以忽略不计，但仍然如此。）

即使写时复制会与父进程一起保存一些共享的，不变的内存状态，但是当您有1000个以上的并发连接时，作为基于进程的体系结构的基本开销会增加负担，并且它可能是最重要的开销之一能力计划的因素。

也就是说，如果您在30台服务器上运行Rails应用程序，其中每台服务器具有16个CPU内核和32个Unicorn worker，则您有960个连接。在所有应用程序中，可能只有不到0.1％会达到这个规模，但这是需要牢记的。

聚合索引对比堆(Heap)表

聚合索引是其中行被直接嵌入主键的B树结构内的表结构。（非聚和）堆(Heap)是规则表结构，其中填充了与索引分开的数据行。

使用聚合索引时，当您通过主键查找记录时，单个I / O将检索整行，而非聚集索引始终通过遵循引用至少需要两个I / O。由于外键引用和联接将触发主键查找，因此影响可能很大，这将占查询的绝大多数。

聚合索引的理论缺点是，在使用次级索引进行查询时，遍历树节点的次数是您首先遍历次级索引，然后遍历聚合索引（也是一棵树）的两倍。

但是，考虑到现代惯例，将自动递增的整数作为主键[1]（称为代理键），几乎总是希望拥有聚合索引。如果您要执行很多操作ORDER BY id来检索最新（或最旧）的N条记录，那就更是如此，我认为这适用于大多数记录。

Postgres不支持聚和索引，而MySQL（InnoDB）不支持堆(Heap)。但是，无论哪种方式，如果您有大量内存，则差异应该很小。

页面结构和压缩

Postgres和MySQL都具有基于页面的物理存储（8KB和16KB）。

PostgreSQL物理存储简介

页面结构看起来像上图所示。它包含一些标题，我们将在这里不进行介绍，但是它们包含有关页面的元数据。标头后面的项目是一个数组标识符，由(offset, length)指向元组或数据行的对组成。请记住，在Postgres中，可以通过这种方式将同一记录的多个版本存储在同一页面中。

MySQL的表空间结构与Oracle的表空间结构相似，它具有段，范围，页和行的多个层次结构层。

它还为UNDO提供了一个单独的部分，称为“回退部分”。与Postgres不同，MySQL将在同一区域保留同一记录的多个版本。

在两个数据库上，一行必须适合一个页面，这意味着一行必须小于8KB。（MySQL的页面中必须至少包含2行，巧合的是16KB / 2 = 8KB）

那么当列中有一个大的JSON对象时会发生什么？

Postgres使用TOAST（专用的影子表存储）。当且仅当选择行和列时，才会拉出大对象。换句话说，大量的黑盒子不会污染您宝贵的缓存。它还支持对TOASTed对象的压缩。

由于高端SSD存储供应商Fusion-io的贡献，MySQL具有称为“ 透明页面压缩”的更高级功能。它是专门为与SSD配合使用而设计的，固态硬盘的写入量与设备的寿命直接相关。

MySQL上的压缩不仅适用于页面外的大对象，而且适用于所有页面。它是通过在稀疏文件中使用打孔来实现的，稀疏文件受ext4或btrfs等现代文件系统支持。

有关更多详细信息，请参阅：通过FusionIO上的新MariaDB页面压缩显着提高性能

更新的开销

UPDATE是经常遗漏但对性能有重大影响并且可能是最具争议的主题的另一个功能。

这也是Uber放弃Postgres的另一个原因，这激起了许多Postgres拥护者的反驳。

• MySQL可能适合Uber，但不适合您
• PostgreSQL对Uber的回应（PDF）

两者都是MVCC数据库，可保留多个版本的数据以进行隔离)。

为此，Postgres将旧数据保留在堆中直到VACUUMed，而MySQL将旧数据移动到称为回滚段的单独区域。

在Postgres上，当您尝试更新时，必须复制整行以及指向该行的索引条目。部分原因是Postgres不支持聚集索引，因此从索引引用的行的物理位置不会被逻辑键抽象出来。

为了解决此问题，Postgres使用仅堆元组（HOT）尽可能不更新索引。但是，如果更新足够频繁（或者如果一个元组很大），则元组的历史记录很容易从8KB的页面大小中流出，跨越多个页面并限制了功能的有效性。修剪和/或碎片整理的时间取决于试探法。此外，将fillfactor设置为小于100会降低空间效率—这是在表创建时就不必担心的艰难折衷。

这个限制甚至更深了。由于索引元组没有有关事务的任何信息，因此直到9.2 以前一直不可能支持仅索引扫描。它是所有主要数据库（包括MySQL，Oracle，IBM DB2和Microsoft SQL Server）支持的最古老，最重要的优化方法之一。但是即使使用最新版本，当有大量的UPDATE设置Visibility Map中的脏位时，Postgres也不能完全支持仅索引扫描，而在不需要时经常选择Seq扫描。

在MySQL上，更新发生在原地，旧行数据存放在称为回滚段的单独区域中。结果是您不需要VACUUM，提交非常快，而回滚相对较慢，这对于大多数用例来说是一个较好的折衷方案。

它也足够聪明，可以尽快清除历史记录。如果将事务的隔离级别设置为READ-COMMITTED或更低，则在语句完成时将清除历史记录。

交易历史记录的大小不会影响主页。碎片是没有问题的。因此，MySQL的整体性能更好，更可预测。

垃圾回收

Postgres上的VACUUM非常昂贵，因为它可以在主堆区域中工作，从而造成直接的资源争用。感觉就像编程语言中的垃圾回收一样-它会妨碍您并让您随意暂停。

为具有数十亿条记录的表配置自动清空仍然是一个挑战。

对MySQL的清除也可能很繁重，但是由于它在单独的回滚段中使用专用线程运行，因此不会以任何方式对读取并发产生不利影响。即使使用默认设置，膨胀的回滚段也不太可能使您减速。

一个拥有数十亿条记录的繁忙表不会导致MySQL的历史记录膨胀，并且诸如存储文件大小和查询性能之类的事情几乎是可以预测和稳定的。

日志和复制

Postgres有一个唯一的交易历史事实来源，称为Write Ahead Log（WAL）。它也用于复制，称为逻辑复制的新功能可以将二进制内容实时解码为更易消化的逻辑语句，从而可以对数据进行精细控制。

MySQL维护两个单独的日志：1. 用于崩溃恢复的InnoDB特定重做日志，以及2. 用于复制和增量备份的二进制日志。

与Oracle一样，InnoDB上的重做日志是免维护的循环缓冲区，不会随着时间的推移而增长，只能在启动时以固定大小创建。这种设计可确保在物理设备上保留连续的连续区域，从而提高性能。重做日志越大，性能越好，但要从崩溃中恢复时间。

在Postgres中添加了新的复制功能后，我称之为平局。

TL和DR

令人惊讶的是，事实证明，普遍的看法仍然成立。MySQL最适合在线交易，而PostgreSQL最适合仅追加分析过程，例如数据仓库。[2]

正如我们在本文中看到的，Postgres的绝大多数复杂性源于其仅附加的，过度冗余的堆体系结构。

Postgres的未来版本可能需要对其存储引擎进行重大改进。您不必完全相信我的话- 官方Wiki上已经讨论了它，这表明是时候从InnoDB那里获取一些好主意了。

一次又一次地说MySQL正在追赶Postgres，但是这次，潮流已经改变了。

1. 顺便说一句，UUID作为主键是一个可怕的想法-密码随机性是完全设计用来杀死参考位置的，因此会降低性能。↩︎
2. 当我说Postgres非常适合分析时，我是说真的。如果您不了解TimescaleDB，它是PostgreSQL之上的包装器，可让您每秒插入100万条记录，每服务器100+十亿行。疯狂的事情。难怪亚马逊为什么选择PostgreSQL作为Redshift的基础。

英文原文: http://suo.im/6kMihv

Oracle RDA英文全称叫做“Oracle Remote Diagnostic Agent”。它是Oracle用来收集、分析数据库信息的工具，它是用perl编写的，包含非常丰富的诊断脚本，使用它科技非常便捷的采集到Oracle数据库服务器系统配置和数据库的详细信息，运行该工具不会改变系统的任何参数。
RDA收集的相关数据非常全面，可以简化我们日常监控、分析数据库的工作。Oracle Support也建议我们在反馈相关问题时，提供RDA收集的数据，这样可以为快速解决问题提供一个有力的保证。
Oracle作为目前行业最成熟的商业数据库管理系统软件，Oracle开发出很多有助于系统调试和运维的工具。借助这些优秀的免费工具，可以大大方便我们日常运维和管理工作。
无论是作为专业DBA，还是第三方支持人员，数据库巡检都是日常工作中不可缺少的工作内容。巡检项目通常是设置好的项目内容，有时候我们还会准备一些脚本或者命令作为手机手段。
巡检项目类型也会包括从操作系统，硬性文件系统，到数据库内存配置等广泛的内容，可能大多人员都会借助于AWR报告，同时，我们还可以借助Oracle提供的RDA来完成。
RDA全称Oracle Remote Diagnostic Agent（RDA）-RDA文档索引，RDA是Oracle用来收集，分析数据库的工具，运行该工具不会改变系统的任何参数，RDA收集的相关数据非常全面，这个Oracle诊断工具是用perl编写的，包含非常丰富的诊断脚本，使用它可以分成便利的采集到oracle数据库服务器系统配置和数据库的详细信息。可以简化我们的日常监控、分析数据库的工作。一般Oracle原厂的巡检也是使用RDA工具，不过一般国内的DBA巡检时很少使用这个工具，一般都是通过AWR或者自己整几个脚本跑一下，分析一下就OK了。
通过RDA可以快速的了解系统的状况，将会大大的缩短问题处理的周期，避免信息的反复采集。RDA不会更改数据库服务器的任何配置，仅仅是采集信息。
想要更详细的了解Oracle RDA工具的使用方法，请进入OTPUB技术课堂：http://www.otpub.com/Course/detail/index/id/210.html

一、前言​
关于mysql主从同步，相信大家都不陌生，随着系统应用访问量逐渐增大，单台数据库读写访问压力也随之增大，当读写访问达到一定瓶颈时，将数据库的读写效率骤然下降，甚至不可用；为了解决此类问题，通常会采用mysql集群，当主库宕机后，集群会自动将一个从库升级为主库，继续对外提供服务；那么主库和从库之间的数据是如何同步的呢？本文针对MySQL 5.7版本进行下面的分析，下面随笔者一起探究一下mysql主从是如何同步的。
 
二、MySQL主从复制原理
为了减轻主库的压力，应该在系统应用层面做读写分离，写操作走主库，读操作走从库，下图为MySQL官网给出的主从复制的原理图，从图中可以简单的了解读写分离及主从同步的过程，分散了数据库的访问压力，提升整个系统的性能和可用性，降低了大访问量引发数据库宕机的故障率。

 
三、binlog简介
MySQL主从同步是基于binlog文件主从复制实现，为了更好的理解主从同步过程，这里简单介绍一下binlog日志文件。

binlog日志用于记录所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改，它是以二进制的形式保存在磁盘中。我们可以通过mysql提供的查看工具mysqlbinlog查看文件中的内容，例如 mysqlbinlog mysql-bin.00001 | more，这里注意一下binlog文件的后缀名00001，binlog文件大小和个数会不断的增加，当MySQL停止或重启时，会产生一个新的binlog文件，后缀名会按序号递增，例如mysql-bin.00002、mysql-bin.00003，并且当binlog文件大小超过 max_binlog_size系统变量配置时也会产生新的binlog文件。
 
（一）binlog日志格式
（1） statement ： 记录每一条更改数据的sql;

• 优点：binlog文件较小，节约I/O，性能较高。
• 缺点：不是所有的数据更改都会写入binlog文件中，尤其是使用MySQL中的一些特殊函数（如LOAD_FILE()、UUID()等）和一些不确定的语句操作，从而导致主从数据无法复制的问题。

 （2） row ： 不记录sql，只记录每行数据的更改细节

• 优点：详细的记录了每一行数据的更改细节，这也意味着不会由于使用一些特殊函数或其他情况导致不能复制的问题。
• 缺点：由于row格式记录了每一行数据的更改细节，会产生大量的binlog日志内容，性能不佳，并且会增大主从同步延迟出现的几率。

 （3） mixed：一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。 （二）binlog日志内容  （三）binlog事件类型MySQL binlog记录的所有操作实际上都有对应的事件类型的，譬如STATEMENT格式中的DML操作对应的是QUERY_EVENT类型，ROW格式下的DML操作对应的是ROWS_EVENT类型，如果想了解更多请参考官方文档，有关binlog日志内容不在这里过多赘述，简单介绍一下是为了更好的理解主从复制的细节，下面我们进入正题。  四、MySQL主从复制原理mysql主从复制需要三个线程，master（binlog dump thread）、slave（I/O thread 、SQL thread）。  Master（1）binlog dump线程：当主库中有数据更新时，那么主库就会根据按照设置的binlog格式，将此次更新的事件类型写入到主库的binlog文件中，此时主库会创建log dump线程通知slave有数据更新，当I/O线程请求日志内容时，会将此时的binlog名称和当前更新的位置同时传给slave的I/O线程。 Slave（2）I/O线程：该线程会连接到master，向log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的relay log中，relay log和binlog日志一样也是记录了数据更新的事件，它也是按照递增后缀名的方式，产生多个relay log（ host_name-relay-bin.000001）文件，slave会使用一个index文件（ host_name-relay-bin.index）来追踪当前正在使用的relay log文件。 （3）SQL线程：该线程检测到relay log有更新后，会读取并在本地做redo操作，将发生在主库的事件在本地重新执行一遍，来保证主从数据同步。此外，如果一个relay log文件中的全部事件都执行完毕，那么SQL线程会自动将该relay log 文件删除掉。 下面是整个复制过程的原理图：  四、主从同步延迟mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的I/O线程到主库取日志，效率也比较高，但是，slave的SQL线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能存在slave上的其他查询产生lock争用的情况，由于SQL也是单线程的，所以一个DDL卡住了，需要执行很长一段事件，后续的DDL线程会等待这个DDL执行完毕之后才执行，这就导致了延时。当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，延时就产生了，除此之外，还有可能与slave的大型query语句产生了锁等待导致。 由于主从同步延迟是客观存在的，我们只能从我们自己的架构上进行设计， 尽量让主库的DDL快速执行。下面列出几种常见的解决方案：[list=1]

业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

服务的基础架构在业务和mysql之间加入memcache或者Redis的cache层。降低mysql的读压力；

使用比主库更好的硬件设备作为slave；

sync_binlog在slave端设置为0；

–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。

禁用slave的binlog

 
五、参考资料
https://dev.mysql.com/doc/refman/5.7/en/replication.html  
http://www.linuxidc.com/Linux/2014-05/101450.htm  
http://blog.csdn.net/xiongping_/article/details/49907095  
http://www.cnblogs.com/martinzhang/p/3454358.html  
“本文转载自 linkedkeeper.com (文／张松然)”地址：http://www.linkedkeeper.com/detail/blog.action?bid=1028  

最近研发的项目对 DB 依赖比较重，梳理了这段时间使用MySQL遇到的8个比较具有代表性的问题，答案也比较偏自己的开发实践，没有 DBA专业和深入，有出入的请使劲拍砖！
 

一、MySQL读写性能是多少，有哪些性能相关的重要参数？​

这里做了几个简单压测实验，结果如下：

机器：8核CPU，8G内存

表结构(尽量模拟业务)：12个字段(1个bigint(20)为自增primary key，5个int(11)，5个varchar(512)，1个timestamp)，InnoDB存储引擎。

实验1(写)：insert => 6000/s

前提：连接数100，每次insert单条记录

分析：CPU跑了50%，这时磁盘为顺序写，故性能较高

实验2(写)：update(where条件命中索引) => 200/s

前提：连接数100，10w条记录，每次update单条记录的4个字段(2个int(11)，2个varchar(512))

分析：CPU跑2%，瓶颈明显在IO的随机写

实验3(读)：select(where条件命中索引) => 5000/s

前提：连接数100，10w条记录，每次select单条记录的4个字段(2个int(11)，2个varchar(512))

分析：CPU跑6%，瓶颈在IO，和db的cache大小相关

实验4(读)：select(where条件没命中索引) => 60/s

前提：连接数100，10w条记录，每次select单条记录的4个字段(2个int(11)，2个varchar(512))

分析：CPU跑到80%，每次select都需遍历所有记录，看来索引的效果非常明显！

几个重要的配置参数，可根据实际的机器和业务特点调整

max_connecttions：最大连接数



table_cache：缓存打开表的数量



key_buffer_size：索引缓存大小



query_cache_size：查询缓存大小



sort_buffer_size：排序缓存大小(会将排序完的数据缓存起来)



read_buffer_size：顺序读缓存大小



read_rnd_buffer_size：某种特定顺序读缓存大小(如order by子句的查询)

PS：查看配置方法：show variables like '%max_connecttions%';
 

二、MySQL负载高时，如何找到是由哪些SQL引起的？

方法：慢查询日志分析(MySQLdumpslow)
 
慢查询日志例子，可看到每个慢查询SQL的耗时：

# User@Host: edu_online[edu_online] @  [10.139.10.167]

# Query_time: 1.958000  Lock_time: 0.000021 Rows_sent: 254786  Rows_examined: 254786

SET timestamp=1410883292;

select * from t_online_group_records;

日志显示该查询用了1.958秒，返回254786行记录，一共遍历了254786行记录。及具体的时间戳和SQL语句。

使用MySQLdumpslow进行慢查询日志分析：

MySQLdumpslow -s t -t 5 slow_log_20140819.txt

输出查询耗时最多的Top5条SQL语句
 
-s：排序方法，t表示按时间 (此外，c为按次数，r为按返回记录数等)
-t：去Top多少条，-t 5表示取前5条
 
执行完分析结果如下：

Count: 1076100  Time=0.09s (99065s)  Lock=0.00s (76s)  Rows=408.9 (440058825), edu_online[edu_online]@28hosts

  select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > N

Count: 1076099  Time=0.05s (52340s)  Lock=0.00s (91s)  Rows=62.6 (67324907), edu_online[edu_online]@28hosts

  select * from t_online_course where UNIX_TIMESTAMP(c_updatetime) > N

Count: 63889  Time=0.78s (49607s)  Lock=0.00s (3s)  Rows=0.0 (18), edu_online[edu_online]@[10x.213.1xx.1xx]

  select f_uin from t_online_student_contact where f_modify_time > N

Count: 1076097  Time=0.02s (16903s)  Lock=0.00s (72s)  Rows=52.2 (56187090), edu_online[edu_online]@28hosts

  select * from t_online_video_info where UNIX_TIMESTAMP(v_update_time) > N

Count: 330046  Time=0.02s (6822s)  Lock=0.00s (45s)  Rows=0.0 (2302), edu_online[edu_online]@4hosts

  select uin,cid,is_canceled,unix_timestamp(end_time) as endtime,unix_timestamp(update_time) as updatetime 

  from t_kick_log where unix_timestamp(update_time) > N

以第1条为例，表示这类SQL(N可以取很多值，这里MySQLdumpslow会归并起来)在8月19号的慢查询日志内出现了1076100次，总耗时99065秒，总返回440058825行记录，有28个客户端IP用到。

通过慢查询日志分析，就可以找到最耗时的SQL，然后进行具体的SQL分析

慢查询相关的配置参数：

log_slow_queries：是否打开慢查询日志，得先确保=ON后面才有得分析



long_query_time：查询时间大于多少秒的SQL被当做是慢查询，一般设为1S



log_queries_not_using_indexes：是否将没有使用索引的记录写入慢查询日志



slow_query_log_file：慢查询日志存放路径

三、如何针对具体的SQL做优化？

使用Explain分析SQL语句执行计划

MySQL> explain select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789;

+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | t_online_group_records | ALL  | NULL          | NULL | NULL    | NULL |   47 | Using where |

+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

如上面例子所示，重点关注下type，rows和Extra：

type：使用类别，有无使用到索引。结果值从好到坏：... > range(使用到索引) > index > ALL(全表扫描)，一般查询应达到range级别

rows：SQL执行检查的记录数

Extra：SQL执行的附加信息，如"Using index"表示查询只用到索引列，不需要去读表等

使用Profiles分析SQL语句执行时间和消耗资源：

MySQL> set profiling=1; (启动profiles，默认是没开启的)

MySQL> select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789; (执行要分析的SQL语句)

MySQL> show profiles;

+----------+------------+----------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                        |

+----------+------------+----------------------------------------------------------------------------------------------+

|        1 | 0.00043250 | select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789 |

+----------+------------+----------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MySQL> show profile cpu,block io for query 1; (可看出SQL在各个环节的耗时和资源消耗)

+----------------------+----------+----------+------------+--------------+---------------+

| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

+----------------------+----------+----------+------------+--------------+---------------+

...

| optimizing           | 0.000016 | 0.000000 |   0.000000 |            0 |             0 |

| statistics           | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |

| preparing            | 0.000017 | 0.000000 |   0.000000 |            0 |             0 |

| executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |

| Sending data         | 0.000076 | 0.000000 |   0.000000 |            0 |             0 |

...

SQL优化的技巧 (只提一些业务常遇到的问题)
 
1、最关键：索引，避免全表扫描
对接触的项目进行慢查询分析，发现TOP10的基本都是忘了加索引或者索引使用不当，如索引字段上加函数导致索引失效等(如where UNIX_TIMESTAMP(gre_updatetime)>123456789)

+----------+------------+---------------------------------------+

| Query_ID | Duration   | Query                                 |

+----------+------------+---------------------------------------+

|        1 | 0.00024700 | select * from mytable where id=100    |

|        2 | 0.27912900 | select * from mytable where id+1=101  |

+----------+------------+---------------------------------------+

另外很多同学在拉取全表数据时，喜欢用select xx from xx limit 5000,1000这种形式批量拉取，其实这个SQL每次都是全表扫描，建议添加1个自增id做索引，将SQL改为 select xx from xx where id>5000 and id<6000;

+----------+------------+-----------------------------------------------------+

| Query_ID | Duration   | Query                                               |

+----------+------------+-----------------------------------------------------+

|        1 | 0.00415400 | select * from mytable where id>=90000 and id<=91000 |

|        2 | 0.10078100 | select * from mytable limit 90000,1000              |

+----------+------------+-----------------------------------------------------+

合理用好索引，应该可解决大部分SQL问题。当然索引也非越多越好，过多的索引会影响写操作性能。
 
1.1 只select出需要的字段，避免select

+----------+------------+-----------------------------------------------------+

| Query_ID | Duration   | Query                                               |

+----------+------------+-----------------------------------------------------+

|        1 | 0.02948800 | select count(1) from ( select id from mytable ) a   |

|        2 | 1.34369100 | select count(1) from ( select * from mytable ) a    |

+----------+------------+-----------------------------------------------------+

1.2 尽量早做过滤，使Join或者Union等后续操作的数据量尽量小
 
1.3 把能在逻辑层算的提到逻辑层来处理，如一些数据排序、时间函数计算等
 
PS：关于SQL优化，已经有足够多文章了，所以就不讲太全面了，只重点说自己1个感受：索引！基本都是因为索引！
 

四、SQL层面已难以优化，请求量继续增大时的应对策略？

下面是我能想到的几个方法，每个方法又都是一篇大文章了，这里就不展开。

• 分库分表
• 使用集群(master-slave)，读写分离
• 增加业务的cache层
• 使用连接池

五、MySQL如何做主从数据同步？

复制机制（Replication）master通过复制机制，将master的写操作通过binlog传到slave生成中继日志(relaylog)，slave再将中继日志redo，使得主库和从库的数据保持同步。 复制相关的3个MySQL线程：[list=1]

slave上的I/O线程：向master请求数据

master上的Binlog Dump线程：读取binlog事件并把数据发送给slave的I/O线程

slave上的SQL线程：读取中继日志并执行，更新数据库

属于slave主动请求拉取的模式 实际使用可能遇到的问题数据非强一致：CDB默认为异步复制，master和slave的数据会有一定延迟(称为主从同步距离，一般 < 1s)主从同步距离变大：可能是DB写入压力大，也可能是slave机器负载高，网络波动等原因，具体问题具体分析 相关监控命令

show processlist：查看MySQL进程信息，包括3个同步线程的当前状态show master status ：查看master配置及当前复制信息show slave status：查看slave配置及当前复制信息

六、如何防止DB误操作和做好容灾？

业务侧应做到的几点：

• 重要DB数据的手工修改操作，操作前需做到2点：1 先在测试环境操作 2 备份数据
• 根据业务重要性做定时备份，考虑系统可承受的恢复时间
• 进行容灾演练，感觉很必要

 MySQL备份和恢复操作 1、备份：使用MySQLdump导出数据

MySQLdump -u 用户名 -p 数据库名 [表名] > 导出的文件名MySQLdump -uxxx -p xxx mytable > mytable.20140921.bak.sql

2、恢复：导入备份数据

MySQL -uxxx -p xxxx < mytable.20140921.bak.sql

3、恢复：导入备份数据之后发送的写操作。先使用MySQLbinlog导出这部分写操作SQL(基于时间点或位置)如导出2014-09-21 09:59:59之后的binlog：

MySQLbinlog --database="test" --start-date="2014-09-21 09:59:59" /var/lib/MySQL/mybinlog.000001 > binlog.data.sql

如导出起始id为123456之后的binlog：

MySQLbinlog --database="test" --start-position="123456" /var/lib/MySQL/mybinlog.000001 > binlog.data.sql

最后把要恢复的binlog导入db

MySQL -uxxxx -p xxxx < binlog.data.sql

七、该选择MySQL哪种存储引擎，Innodb具有什么特性？

存储引擎简介：插件式存储引擎是MySQL的重要特性，MySQL支持多种存储引擎以满足用户的多种应用场景存储引擎解决的问题：如何组织MySQL数据在介质中高效地读取，需考虑存储机制、索引设计、并发读写的锁机制等MySQL5.0支持的存储引擎有MyISAM、InnoDB、Memory、Merge等 MyISAM和InnoDB的区别(只说重点了) 1. InnoDB，MySQL5.5之后及CDB的默认引擎

• 支持行锁：并发性能好
• 支持事务：故InnoDB称为事务性存储引擎，支持ACID，提供了具有提交、回滚和崩溃恢复能力的事务安全
• 支持外键：当前唯一支持外键的引擎

 2. MyISAM，MySQL5.5之前默认引擎

• 支持表锁：插入+查询速度快，更新+删除速度慢
• 不支持事务

 使用show engines可查看当前MySQL支持的存储引擎详情：  

八、MySQL内部结构有哪些层次？

 非专业DBA，这里只简单贴个结构图说明下。MySQL是开源系统，其设计思路和源代码都出自大牛之手，有空可以学习下。 [list=1]

Connectors：连接器。接收不同语言的Client交互

Management Serveices & Utilities：系统管理和控制工具

Connection Pool: 连接池。管理用户连接

SQL Interface: SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果

Parser: 解析器。验证和解析SQL语句成内部数据结构

Optimizer: 查询优化器。为查询语句选择合适的执行路径

Cache和Buffer：查询缓存。缓存查询的结果，有命中即可直接返回

Engine：存储引擎。MySQL数据最后组织并存储成具体文件

 
作者：王昂 
原文地址：https://www.qcloud.com/community/article/233