最近开发的项目严重依赖DB。整理了这段时间在使用MySQL过程中遇到的8个有代表性的问题。答案也比较偏向于我自己的开发实践。我没有专业深入的DBA，有出入。请用力砸砖！...MySQL的读写性能如何，与性能相关的配置参数有哪些？MySQL负载高时，如何找出是哪个SQL引起的？当成交量持续增加时，应采取怎样的应对策略？MySQL是如何进行主从数据同步的？如何防止DB误操作和容灾？MySQL应该选择哪种存储引擎，Innodb有哪些特点？MySQL的内部结构分为哪几层？1.MySQL的读写性能是什么，与性能相关的重要参数有哪些？下面是几台简单的压测机：8核CPU，8G内存表结构（尝试模拟业务）：12个字段（1个bigint(20)是自增主键，5个int(11)，5个varchar(512),1个时间戳),InnoDB存储引擎。实验一（写）：insert=>6000/s前提：连接数100，每次insert单条记录分析：CPU运行50%，此时磁盘是顺序写的，所以性能更高实验二（写）：update(wherecondition***index)=>200/s前提：连接数100，10w条记录，每条更新单条记录的4个字段（2个int（11），2个varchar（512）））分析：CPU运行2%，瓶颈明显在IO随机写实验3（读）：select(wherecondition***index)=>5000/s前提：连接数为100，10w条记录，每次选择4个字段asinglerecord(2int(11),2varchar(512))分析：CPU运行6%，瓶颈是IO，和dbcachesize相关实验四（读）：select(whereconditiondoesnot***index)=>60/s前提：100个连接，10w条记录，每次select单条记录的4个字段（2int(11),2varchar(512)）分析：CPU跑到80%，每次select都需要遍历for所有记录，看来索引的效果很明显啊！几个重要的配置参数可以根据实际机器和业务特点进行调整max_connections:***连接数table_cache:缓存打开的表数key_buffer_size:索引缓存大小query_cache_size:查询缓存大小sort_buffer_size:排序缓存大小（排序后的数据会被缓存）read_buffer_size：顺序读缓存大小read_rnd_buffer_size：具体顺序读缓存大小（比如orderby子句的查询）PS：查看配置方法：showvariableslike'%max_connections%';2、当MySQL负载高时，如何找出是哪条SQL引起的？方法：慢查询日志分析（MySQLdumpslow）慢查询日志示例，可以看到每条慢查询SQL的耗时：#User@Host:edu_online[edu_online]@[10.139.10.167]#Query_time:1.958000Lock_time:0.000021Rows_sent:254786Rows_examined:254786SETtimestamp=1410883292;select*fromt_online_group_records；日志显示查询耗时1.958秒并返回254,786行记录。总共遍历了254,786行记录和特定时间戳以及SQL语句。使用MySQLdumpslow进行慢查询日志分析MySQLdumpslow-st-t5slow_log_20140819.txt输出查询耗时最多的Top5SQL语句-s：排序方式，t表示时间（另外c表示次数，r表示numberofrecordsreturnedetc.)-t：top多少条，-t5表示取前5条，执行分析结果如下：Count:1076100Time=0.09s(99065s)Lock=0.00s(76s)Rows=408.9(440058825),edu_online[edu_online]@28hostsselect*fromt_online_group_recordswhereUNIX_TIMESTAMP(gre_updatetime)>NCount:1076099Time=0.05s(52340s)Lock=0.00s(91s)Rows=62.6(67324907),edu_online[edu_online]@28hostsselect*fromt_online_coursewhereUNIX_TIMESTAMP(c_updatetime)>NCount:63889Time=0.78s(49607s)Lock=0.00s(3s)Rows=0.0(18),edu_online[edu_online]@[10x.213.1xx.1xx]selectf_uinfromt_online_student_contactwheref_modify_time>NCount:1076097Time=0.02s()Lock=0.00s(722s)52.2(56187090),edu_online[edu_online]@28hostsselect*fromt_online_video_infowhereUNIX_TIMESTAMP(v_update_time)>NCount:330046Time=0.02s(6822s)Lock=0.00s(45s)Rows=0.0(2302),edu_online[edu_online]@4hostsselectuin,cid,is_canceled，unix_timestamp(end_time)作为结束时间，unix_timestamp(update_time)asupdatetimefromt_kick_logwhereunix_timestamp(update_time)>N以第1项为例，说明该类SQL（N可以取很多值，MySQLdumpslow会在这里合并）在8月19日的慢查询日志中出现了1,076,100次，并且总共耗时99065秒，一共返回了440058825行记录，使用了28个客户端IP通过慢查询日志分析，可以找到最耗时的SQL，然后分析具体的SQL。慢查询相关的配置参数log_slow_queries：是否开启慢查询日志，必须先保证=ON才能分析:是否将不使用索引的记录写入慢查询日志slow_query_log_file:慢查询日志存储路径3.具体SQL如何优化？使用Explain分析SQL语句执行计划MySQL>explainselect*fromt_online_group_recordswhereUNIX_TIMESTAMP(gre_updatetime)>123456789;+----+------------+-----------------------+------+--------------+------+---------+------+------+------------+|id|select_type|table|type|possible_keys|key|key_len|ref|行|额外|+----+------------+------------------------+------+---------------+------+--------+------+-----+------------+|1|SIMPLE|t_online_group_records|ALL|NULL|NULL|NULL|NULL|47|Usingwhere|+----+-------------+------------------------+------+--------------+------+--------+------+-----+-------------+1rowinset(0.00sec)如上例所示，关注type，rows和Extra：type：使用类别，是否使用索引。结果值从好到坏：...>range（使用索引）>index>ALL（全表扫描），一般查询应该达到范围级别rows：SQL执行检查的记录数extra：附加信息关于SQL执行，如“Usingindex”表示查询只使用索引列，不需要读表等。使用Profiles分析SQL语句执行时间和消耗资源MySQL>setprofiling=1;(启动profiles,默认不开启)MySQL>selectcount(1)fromt_online_group_recordswhereUNIX_TIMESTAMP(gre_updatetime)>123456789;(执行要分析的SQL语句)MySQL>showprofiles;+------------+-----------+-------------------------------------------------------------------------------------------+|Query_ID|Duration|Query|+----------+------------+----------------------------------------------------------------------------------------+|1|0.00043250|selectcount(1)fromt_online_group_recordswhereUNIX_TIMESTAMP(gre_updatetime)>123456789|+----------+-------------+--------------------------------------------------------------------------------------------+1rowinset(0.00sec)MySQL>showprofilecpu,blockioforquery1;（可以看到各个环节SQL的耗时和资源消耗）+----------------------+----------+-----------+------------+------------+---------------+|Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+--------------------+---------+------------+------------+------------+--------------+...|优化|0.000016|0.000000|0.000000|0|0||统计|0.000020|0.000000|0.000000|0|0||准备|0.000017|0.000000|0.000000|0|0||executing|0.000011|0.000000|0.000000|0|0||Sendingdata|0.000076|0.000000|0.000000|0|0|...SQL优化技巧（只提一些业务中经常遇到的问题）最关键：索引，避免全表扫描对自己接触过的项目进行了慢查询分析，发现***0基本是忘记加索引或者索引使用不当，比如给索引字段加函数导致索引失效等（比如whereUNIX_TIMESTAMP(gre_updatetime)>123456789)+--------+------------+------------------------------------------+|查询ID|持续时间|查询|+--------+------------+--------------------------------------+|1|0.00024700|选择*frommytablewhereid=100||2|0.27912900|选择*frommytablewhereid+1=101|+----------+------------+----------------------------------+另外很多同学在拉全的时候喜欢用selectxxfromxxlimit5000,1000的形式分批拉表数据，其实这个SQL每次都是全表扫描。建议加个自增id作为索引，改SQL为selectxxfromxxwhereid>5000andid;+----------+-----------+--------------------------------------------------+|Query_ID|Duration|Query|+----------+------------+---------------------------------------------------+|1|0.00415400|选择*frommytablewhereid>=90000andid91000||2|0.10078100|select*frommytablelimit90000,1000|+---------+------------+----------------------------------------------------+合理使用索引应该可以解决大多数SQL问题。当然索引越多越好，索引太多会影响写操作的性能，只选择需要的字段，避免select+---------+----------+--------------------------------------------------+|Query_ID|Duration|Query|+------------+------------+-----------------------------------------------------+|1|0.02948800|selectcount(1)from(selectidfrommytable)a||2|1.34369100|selectcount(1)from(select*frommytable)a|+----------+------------+----------------------------------------------------+尽早做过滤，让后续Join或Union等操作的数据量尽可能小，逻辑层可以计算的数据交由逻辑层处理，比如一些数据排序，时间函数计算等。PS：关于SQL优化，已经有足够多的文章了，就不讲太全面了。我只会关注我的感受之一：索引！主要是因为索引！4、SQL层面难以优化，请求量持续增加时的响应策略？下面是我能想到的几种方法。每个方法都是一篇大文章。这里不展开分库分表的集群（主从）的使用。读写分离，增加业务的缓存层，使用连接池5.MySQL如何做主从数据同步？复制机制（Replication）master将master的写操作通过binlog传递给slave，通过复制机制生成中继日志（relaylog），slave再重做中继日志，这样master库和slavelibrary数据一直同步复制到slave上I/O线程相关的3个MySQL线程：向master请求数据。master上的BinlogDump线程：读??取binlogevents并将数据发送到slave上的I/O线程。slave上的SQL线程：读取relaylog并执行，更新数据库属于slave主动请求拉取的模式数据会有一定的延迟（称为主从同步距离。一般主从同步距离变大：可能是DB写入压力大，或者从机负载高，网络波动等具体问题用相关监控命令详细分析showprocesslist：查看MySQL进程信息，包括3个同步线程的当前状态showmasterstatus：查看master配置和当前复制信息showslavestatus：查看slave配置和当前复制信息6.如何防止DB误操作和容灾?业务端应该做的几点：手动修改重要的DB数据，操作前需要做两点：1.操作首先在测试环境下。2.根据业务的重要性定期备份数据，并考虑恢复时间系统可以承受。灾备演练，感觉非常有必要的MySQL备份恢复操作1、备份：使用MySQLdump导出数据MySQLdump-u用户名-p数据库名[表名]>导出文件名MySQLdump-uxxx-pxxxmytable>mytable.20140921。bak.sql2。恢复：导入备份数据MySQL-uxxx-pxxxx3。恢复：导入备份数据后发送的写操作先用MySQLbinlog导出这部分写操作SQL（根据时间点或位置），比如导出2014-09-2109:59:59之后的binlog：MySQLbinlog--database="test"--start-date="2014-09-2109: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导入dbMySQL-uxxxx-pxxxx7。MySQL应该选择哪种存储引擎，Innodb有哪些特点？存储引擎介绍插件式存储引擎是MySQL的一个重要特性。MySQL支持多种存储引擎，满足用户各种应用场景。存储引擎解决的问题：如何组织MySQL数据在介质中被高效读取需要考虑存储机制、索引设计、并发读取。MySQL5.0支持的存储引擎如写入的锁机制有MyISAM、InnoDB、Memory、Merge等。**MyISAM和InnoDB的区别（只说重点）1、InnoDB是MySQL5之后的默认引擎。5和国开行。支持行锁：并发性好支持事务：所以InnoDB被称为事务型存储引擎，支持ACID，提供事务安全，具有提交、回滚、崩溃恢复能力支持外键：目前唯一支持外键的引擎2.MyISAM之前MySQL5.5，默认引擎支持表锁：插入+查询速度快，更新+删除速度慢。不支持交易。使用showengines可以查看MySQL目前支持的存储引擎的详细信息。8、MySQL的内部结构有哪几级？非专业DBA，这里贴个结构图说明一下。MySQL是一个开源系统，它的设计思想和源代码都出自大牛之手，有空可以学习一下。连接器：连接器。接收不同语言的Client交互ManagementServices&Utilities：系统管理和控制工具ConnectionPool：连接池。管理用户连接SQLInterface：SQL接口。接受用户的SQL命令，返回用户需要查询的结果。解析器：解析器。验证SQL语句并将其解析为内部数据结构Optimizer：查询优化器。为查询语句Cache和Buffer选择合适的执行路径：查询缓存。缓存查询的结果可以直接返回给Engine：存储引擎。MySQL数据被组织并存储在特定的文件中
