（点击
上方公众号
，可快速关注）
来源：腾云阁 - 王昂
www.qcloud.com/community/article/233
如有好文章投稿，请点击 → 这里了解详情
最近研发的项目对DB依赖比较重，梳理了这段时间使用MySQL遇到的8个比较具有代表性的问题，答案也比较偏自己的开发实践，没有DBA专业和深入，有出入的请使劲拍砖！…
-
MySQL读写性能是多少，有哪些性能相关的配置参数？
-
MySQL负载高时，如何找到是由哪些SQL引起的？
-
如何针对具体的SQL做优化？
-
SQL层面已难以优化，请求量继续增大时的应对策略？
-
MySQL如何做主从数据同步？
-
如何防止DB误操作和做好容灾？
-
该选择MySQL哪种存储引擎，Innodb具有什么特性？
-
MySQL内部结构有哪些层次？
1.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%';
2.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：慢查询日志存放路径
3.如何针对具体的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优化的技巧 (只提一些业务常遇到的问题)
最关键：索引，避免全表扫描。
对接触的项目进行慢查询分析，发现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
+
