首页 » linux » 正文

MySQL 开发实践

| linux | | 暂无评论 | 2154 views

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优化的技巧 (只提一些业务常遇到的问题)

  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. 只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    |
+----------+------------+-----------------------------------------------------+
  2. 尽量早做过滤，使Join或者Union等后续操作的数据量尽量小
  3. 把能在逻辑层算的提到逻辑层来处理，如一些数据排序、时间函数计算等
  4. …….

PS：关于SQL优化，已经有足够多文章了，所以就不讲太全面了，只重点说自己1个感受：索引！基本都是因为索引！

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

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

发表评论