MySQL数据库最佳实践案例.doc

MySQL数据库SQL语句最佳实践案例 文档编号 版 本 号 V.0 日 期 2014--29 中国移动通信集团南方基地IT支撑中心 声 明 本文档所有权和解释权归中国移动南方基地所有，未经书面许可，不得复制或向第三方公开。 This document is the property of GMCC and can be neither reproduced nor disclosed to a third party without a written authorization. 修订历史记录 日期 AMD 修订者 说明 1 20014/7/29 A 李季鹏 新建 （A-添加，M-修改，D-删除） MySQL数据库中SQL问题进行分类整理和总结。 批量插入数据 问题案例 INSERT INTO SMAP_HISTORY.SMAP2_SESSION (SESSION_ID,SESSION_TICKET_ID) VALUES（1001,1001）; INSERT INTO SMAP_HISTORY.SMAP2_SESSION (SESSION_ID,SESSION_TICKET_ID) VALUES（1001,1002）; INSERT INTO SMAP_HISTORY.SMAP2_SESSION (SESSION_ID,SESSION_TICKET_ID) VALUES（1001,1003）; 问题分析 当批量插入数据时，MySQL首选多行插入语句对比SQL的binlog和innodb事务日志O量也是后者数倍这些影响使得两者在大批量插入时效率上有较大差异 记录数 单条数据插入 多条数据插入 1百 0.149s 0.011s 1千 1.231s 0.047s 1万 11.678s 0.218s 优化建议 INSERT INTO SMAP_HISTORY.SMAP2_SESSION (SESSION_ID,SESSION_TICKET_ID) VALUES（1001,1001）,（1001,1002）,（1001,1003）…; 避免出现select * 问题案例 SELECT * FROM SMAP2_INTERFACE_INVOKE_LOG S; 问题分析 使用select 取出全部列会让优化器无法完成索引覆盖扫描这类优化更会带来额外的 优化建议 建议评估业务实际避免使用insert…select…语句 问题案例 INSERT INTO SMAP_HISTORY.SMAP2_SESSION (SESSION_ID, SESSION_TICKET_ID, SESSION_TICKET_ISSUER, SESSION_UID, SESSION_SRC_APP_ID, SESSION_DEST_APP_ID, SESSION_SID, SESSION_SID_ISSUE_TIME, SESSION_SID_STATUS, SESSION_ISSUER, SESSION_SID_UPDATE_TIME) SELECT S.SESSION_ID, S.SESSION_TICKET_ID, S.SESSION_TICKET_ISSUER, S.SESSION_UID, S.SESSION_SRC_APP_ID, S.SESSION_DEST_APP_ID, S.SESSION_SID, S.SESSION_SID_ISSUE_TIME, #status#, S.SESSION_ISSUER, #updateTime# FROM SMAP.SMAP2_SESSION S WHERE SESSION_SID = #sessionId# 由于表SMAP.SMAP2_SESSION本身承载着大量的插入操作，该语句造成的表锁严重阻塞了其他对该表进行的insert语句，造成故障。 问题分析 当使用 insert...select...进行记录的插入时，如果 select 的表是 innodb 类型的，不论 insert 的表是什么类型的表，都会对 select 的表的纪录进行锁定。对于那些从 oracle 迁移过来的应用，需要特别的注意，因为 oracle 并不存在类似的问题，所以在 oracle 的应用中 insert...select...操作非常的