MySQL 开发实践 8 问 |
您所在的位置：网站首页 › sql如何根据排序算百分比的方法计算 › MySQL 开发实践 8 问 |
|
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+1=101 | +----------+------------+---------------------------------------+复制 另外很多同学在拉取全表数据时，喜欢用select xx from xx limit 5000,1000这种形式批量拉取，其实这个SQL每次都是全表扫描，建议添加1个自增id做索引，将SQL改为select xx from xx where id>5000 and id=90000 and id 导出的文件名 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 7.该选择MySQL哪种存储引擎，Innodb具有什么特性？存储引擎简介 插件式存储引擎是MySQL的重要特性，MySQL支持多种存储引擎以满足用户的多种应用场景 存储引擎解决的问题：如何组织MySQL数据在介质中高效地读取，需考虑存储机制、索引设计、并发读写的锁机制等 MySQL5.0支持的存储引擎有MyISAM、InnoDB、Memory、Merge等 **MyISAM和InnoDB的区别(只说重点了) InnoDB，MySQL5.5之后及CDB的默认引擎。 支持行锁：并发性能好支持事务：故InnoDB称为事务性存储引擎，支持ACID，提供了具有提交、回滚和崩溃恢复能力的事务安全支持外键：当前唯一支持外键的引擎MyISAM，MySQL5.5之前默认引擎 支持表锁：插入+查询速度快，更新+删除速度慢不支持事务使用show engines可查看当前MySQL支持的存储引擎详情 8.MySQL内部结构有哪些层次？非专业DBA，这里只简单贴个结构图说明下。MySQL是开源系统，其设计思路和源代码都出自大牛之手，有空可以学习下。 Connectors：连接器。接收不同语言的Client交互Management Serveices & Utilities：系统管理和控制工具Connection Pool: 连接池。管理用户连接SQL Interface: SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果Parser: 解析器。验证和解析SQL语句成内部数据结构Optimizer: 查询优化器。为查询语句选择合适的执行路径Cache和Buffer：查询缓存。缓存查询的结果，有命中即可直接返回Engine：存储引擎。MySQL数据最后组织并存储成具体文件 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |