搜索

[24136] 2015-07-27_MYSQL调优和使用必读

文档创建者：s7ckTeam
浏览次数：2
最后更新：2025-01-18
2015-07-27_MYSQL调优和使用必读 M Y S Q L   调 优 和 使 用 必 读 L i n u x 中 国   2 0 1 5 - 0 7 - 2 7 M Y S Q L   应 该 是 最 流 行 了   W E B   后 端 数 据 库 。 W E B   开 发 语 言 最 近 发 展 很 快 ， P H P ，   R u b y ,   P y t h o n ,   J a v a   各 有 特 点 ， 虽 然   N O S Q L   最 近 越 來 越 多 的 被 提 到 ， 但 是 相 信 大 部 分 架 构 师 还 是 会 选 择   M Y S Q L   来 做 数 据 存 储 。 M Y S Q L   如 此 方 便 和 稳 定 ， 以 至 于 我 们 在 开 发   W E B   程 序 的 时 候 很 少 想 到 它 。 即 使 想 到 优 化 也 是 程 序 级 别 的 ， 比 如 ， 不 要 写 过 于 消 耗 资 源 的   S Q L   语 句 。 但 是 除 此 之 外 ， 在 整 个 系 统 上 仍 然 有 很 多 可 以 优 化 的 地 方 。 1 .   选 择 合 适 的 存 储 引 擎 :   I n n o D B 除 非 你 的 数 据 表 使 用 来 做 只 读 或 者 全 文 检 索   ( 相 信 现 在 提 到 全 文 检 索 ， 没 人 会 用   M Y S Q L   了 ) ， 你 应 该 默 认 选 择   I n n o D B   。 你 自 己 在 测 试 的 时 候 可 能 会 发 现   M y I S A M   比   I n n o D B   速 度 快 ， 这 是 因 为 ：   M y I S A M   只 缓 存 索 引 ， 而   I n n o D B   缓 存 数 据 和 索 引 ， M y I S A M   不 支 持 事 务 。 但 是   如 果 你 使 用 可 以 获 得 接 近 的 读 取 性 能   ( 相 差 百 倍 )   。 1 . 1   如 何 将 现 有 的   M y I S A M   数 据 库 转 换 为   I n n o D B : 1 . 2   为 每 个 表 分 别 创 建   I n n o D B   F I L E ： 这 样 可 以 保 证   i b d a t a 1   文 件 不 会 过 大 ， 失 去 控 制 。 尤 其 是 在 执 行 的 时 候 。 2 .   保 证 从 内 存 中 读 取 数 据 ， 讲 数 据 保 存 在 内 存 中 2 . 1   足 够 大 的   i n n o d b _ b u f f e r _ p o o l _ s i z e 推 荐 将 数 据 完 全 保 存 在 ， 即 按 存 储 量 规 划 的 容 量 。 这 样 你 可 以 完 全 从 内 存 中 读 取 数 据 ， 最 大 限 度 减 少 磁 盘 操 作 。 2 . 1 . 1   如 何 确 定   i n n o d b _ b u f f e r _ p o o l _ s i z e   足 够 大 ， 数 据 是 从 内 存 读 取 而 不 是 硬 盘 ？ 方 法   1 i n n o d b _ f l u s h _ l o g _ a t _ t r x _ c o m m i t   =   2   1 .   m y s q l   - u   [ U S E R _ N A M E ]   - p   - e   " S H O W   T A B L E S   I N   [ D A T A B A S E _ N A M E ] ; "   |   t a i l   - n   + 2   |   x a r g s   - I   ' { } '   e c h o   " A L T E R   T A B L E   { }   E N G I N E = I n n o D B ; "   >   a l t e r _ t a b l e . s q l 2 .   p e r l   - p   - i   - e   ' s / ( s e a r c h _ [ a - z _ ] +   E N G I N E = ) I n n o D B / 1 M y I S A M / g '   a l t e r _ t a b l e . s q l 3 .   m y s q l   - u   [ U S E R _ N A M E ]   - p   [ D A T A B A S E _ N A M E ]   <   a l t e r _ t a b l e . s q l 1 .   i n n o d b _ f i l e _ p e r _ t a b l e = 1 m y s q l c h e c k   - o   – a l l - d a t a b a s e s i n n o d b _ b u f f e r _ p o o l _ s i z e i n n o d b _ b u f f e r _ p o o l _ s i z e 1 .   m y s q l >   S H O W   G L O B A L   S T A T U S   L I K E   ' i n n o d b _ b u f f e r _ p o o l _ p a g e s _ % ' ; 2 .   + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - + 3 .   |   V a r i a b l e _ n a m e                                         |   V a l u e     | 4 .   + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - + 5 .   |   I n n o d b _ b u f f e r _ p o o l _ p a g e s _ d a t a         |   1 2 9 0 3 7   | 6 .   |   I n n o d b _ b u f f e r _ p o o l _ p a g e s _ d i r t y       |   3 6 2         | 7 .   |   I n n o d b _ b u f f e r _ p o o l _ p a g e s _ f l u s h e d   |   9 9 9 8       |
发 现 为   0 ， 则 说 明   b u f f e r   p o o l   已 经 被 用 光 ， 需 要 增 大 I n n o D B   的 其 他 几 个 参 数 ： 方 法   2 或 者 用 命 令 ， 查 看 硬 盘 的 操 作 。 2 . 1 . 2   服 务 器 上 是 否 有 足 够 内 存 用 来 规 划 执 行 清 除 操 作 系 统 的 文 件 缓 存 ， 可 以 看 到 真 正 的 内 存 使 用 量 。 2 . 2   数 据 预 热 默 认 情 况 ， 只 有 某 条 数 据 被 读 取 一 次 ， 才 会 缓 存 在 。 所 以 ， 数 据 库 刚 刚 启 动 ， 需 要 进 行 数 据 预 热 ， 将 磁 盘 上 的 所 有 数 据 缓 存 到 内 存 中 。 数 据 预 热 可 以 提 高 读 取 速 度 。 对 于   I n n o D B   数 据 库 ， 可 以 用 以 下 方 法 ， 进 行 数 据 预 热 : 1 .   将 以 下 脚 本 保 存 为 7 .   |   I n n o d b _ b u f f e r _ p o o l _ p a g e s _ f l u s h e d   |   9 9 9 8       | 8 .   |   I n n o d b _ b u f f e r _ p o o l _ p a g e s _ f r e e         |   0             |     ! ! ! ! ! ! ! ! 9 .   |   I n n o d b _ b u f f e r _ p o o l _ p a g e s _ m i s c         |   2 0 3 5       | 1 0 .   |   I n n o d b _ b u f f e r _ p o o l _ p a g e s _ t o t a l       |   1 3 1 0 7 2   | 1 1 .   + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - + 1 2 .   6   r o w s   i n   s e t   ( 0 . 0 0   s e c ) I n n o d b _ b u f f e r _ p o o l _ p a g e s _ f r e e   i n n o d b _ b u f f e r _ p o o l _ s i z e 1 .   i n n o d b _ a d d i t i o n a l _ m e m _ p o o l _ s i z e   =   1 / 2 0 0   o f   b u f f e r _ p o o l 2 .   i n n o d b _ m a x _ d i r t y _ p a g e s _ p c t   8 0 % i o s t a t   - d   - x   - k   1 e c h o   1   >   / p r o c / s y s / v m / d r o p _ c a c h e s   i n n o d b _ b u f f e r _ p o o l M a k e S e l e c t Q u e r i e s T o L o a d . s q l 1 .   S E L E C T   D I S T I N C T 2 .           C O N C A T ( ' S E L E C T   ' , n d x c o l l i s t , '   F R O M   ' , d b , ' . ' , t b , 3 .           '   O R D E R   B Y   ' , n d x c o l l i s t , ' ; ' )   S e l e c t Q u e r y T o L o a d C a c h e 4 .           F R O M 5 .           ( 6 .                   S E L E C T 7 .                           e n g i n e , t a b l e _ s c h e m a   d b , t a b l e _ n a m e   t b , 8 .                           i n d e x _ n a m e , G R O U P _ C O N C A T ( c o l u m n _ n a m e   O R D E R   B Y   s e q _ i n _ i n d e x )   n d x c o l l i s t 9 .                   F R O M 1 0 .                   ( 1 1 .                           S E L E C T 1 2 .                                   B . e n g i n e , A . t a b l e _ s c h e m a , A . t a b l e _ n a m e , 1 3 .                                   A . i n d e x _ n a m e , A . c o l u m n _ n a m e , A . s e q _ i n _ i n d e x 1 4 .                           F R O M 1 5 .                                   i n f o r m a t i o n _ s c h e m a . s t a t i s t i c s   A   I N N E R   J O I N 1 6 .                                   ( 1 7 .                                           S E L E C T   e n g i n e , t a b l e _ s c h e m a , t a b l e _ n a m e 1 8 .                                           F R O M   i n f o r m a t i o n _ s c h e m a . t a b l e s   W H E R E 1 9 .                                           e n g i n e = ' I n n o D B ' 2 0 .                                   )   B   U S I N G   ( t a b l e _ s c h e m a , t a b l e _ n a m e ) 2 1 .                           W H E R E   B . t a b l e _ s c h e m a   N O T   I N   ( ' i n f o r m a t i o n _ s c h e m a ' , ' m y s q l ' ) 2 2 .                           O R D E R   B Y   t a b l e _ s c h e m a , t a b l e _ n a m e , i n d e x _ n a m e , s e q _ i n _ i n d e x 2 3 .                   )   A 2 4 .                   G R O U P   B Y   t a b l e _ s c h e m a , t a b l e _ n a m e , i n d e x _ n a m e
2 .   执 行 3 .   每 次 重 启 数 据 库 ， 或 者 整 库 备 份 前 需 要 预 热 的 时 候 执 行 ： 2 . 3   不 要 让 数 据 存 到   S W A P   中 如 果 是 专 用   M Y S Q L   服 务 器 ， 可 以 禁 用   S W A P ， 如 果 是 共 享 服 务 器 ， 确 定 足 够 大 。 或 者 使 用 固 定 的 内 存 空 间 做 缓 存 ， 使 用 指 令 。 3 .   定 期 优 化 重 建 数 据 库 会 让   i b d a t a 1   不 断 增 大 ， 真 正 的 优 化 只 有 重 建 数 据 表 结 构 ： 4 .   减 少 磁 盘 写 入 操 作 4 . 1   使 用 足 够 大 的 写 入 缓 存   i n n o d b _ l o g _ f i l e _ s i z e 但 是 需 要 注 意 如 果 用   1 G   的 ， 假 如 服 务 器 当 机 ， 需 要   1 0   分 钟 来 恢 复 。 推 荐 设 置 为   0 . 2 5   * 4 . 2   i n n o d b _ f l u s h _ l o g _ a t _ t r x _ c o m m i t 这 个 选 项 和 写 磁 盘 操 作 密 切 相 关 ： 则 每 次 修 改 写 入 磁 盘 每 秒 写 入 磁 盘 如 果 你 的 应 用 不 涉 及 很 高 的 安 全 性   ( 金 融 系 统 ) ， 或 者 基 础 架 构 足 够 安 全 ， 或 者   事 务 都 很 小 ， 都 可 以 用   0   或 者   2   来 降 低 磁 盘 操 作 。 4 . 3   避 免 双 写 入 缓 冲 5 .   提 高 磁 盘 读 写 速 度 2 4 .                   G R O U P   B Y   t a b l e _ s c h e m a , t a b l e _ n a m e , i n d e x _ n a m e 2 5 .           )   A A 2 6 .   O R D E R   B Y   d b , t b 2 7 .   ; 1 .   m y s q l   - u r o o t   - A N   <   / r o o t / M a k e S e l e c t Q u e r i e s T o L o a d . s q l   >   / r o o t / S e l e c t Q u e r i e s T o L o a d . s q l 1 .   m y s q l   - u r o o t   <   / r o o t / S e l e c t Q u e r i e s T o L o a d . s q l   >   / d e v / n u l l   2 > & 1 i n n o d b _ b u f f e r _ p o o l _ s i z e m e m l o c k m y s q l c h e c k   - o   – a l l - d a t a b a s e s 1 .   C R E A T E   T A B L E   m y d b . m y t a b l e n e w   L I K E   m y d b . m y t a b l e ; 2 .   I N S E R T   I N T O   m y d b . m y t a b l e n e w   S E L E C T   *   F R O M   m y d b . m y t a b l e ; 3 .   A L T E R   T A B L E   m y d b . m y t a b l e   R E N A M E   m y d b . m y t a b l e z a p ; 4 .   A L T E R   T A B L E   m y d b . m y t a b l e n e w   R E N A M E   m y d b . m y t a b l e ; 5 .   D R O P   T A B L E   m y d b . m y t a b l e z a p ; i n n o d b _ l o g _ f i l e _ s i z e i n n o d b _ l o g _ f i l e _ s i z e i n n o d b _ b u f f e r _ p o o l _ s i z e i n n o d b _ f l u s h _ l o g _ a t _ t r x _ c o m m i t   =   1 i n n o d b _ f l u s h _ l o g _ a t _ t r x _ c o m m i t   =   0 / 2 1 .   i n n o d b _ f l u s h _ m e t h o d = O _ D I R E C T
R A I D 0   尤 其 是 在 使 用   E C 2   这 种 虚 拟 磁 盘   ( E B S )   的 时 候 ， 使 用 软   R A I D 0   非 常 重 要 。 6 .   充 分 使 用 索 引 6 . 1   查 看 现 有 表 结 构 和 索 引 6 . 2   添 加 必 要 的 索 引 索 引 是 提 高 查 询 速 度 的 唯 一 方 法 ， 比 如 搜 索 引 擎 用 的 倒 排 索 引 是 一 样 的 原 理 。 索 引 的 添 加 需 要 根 据 查 询 来 确 定 ， 比 如 通 过 慢 查 询 日 志 或 者 查 询 日 志 , 或 者 通 过   E X P L A I N   命 令 分 析 查 询 。 6 . 2 . 1   比 如 ， 优 化 用 户 验 证 表 ： 添 加 索 引 每 次 重 启 服 务 器 进 行 数 据 预 热 添 加 启 动 脚 本 到   m y . c n f 6 . 2 . 2   使 用 自 动 加 索 引 的 框 架 或 者 自 动 拆 分 表 结 构 的 框 架 比 如 ， R a i l s   这 样 的 框 架 ， 会 自 动 添 加 索 引 ， D r u p a l   这 样 的 框 架 会 自 动 拆 分 表 结 构 。 会 在 你 开 发 的 初 期 指 明 正 确 的 方 向 。 所 以 ， 经 验 不 太 丰 富 的 人 一 开 始 就 追 求 从   0   开 始 构 建 ， 实 际 是 不 好 的 做 法 。 7 .   分 析 查 询 日 志 和 慢 查 询 日 志 记 录 所 有 查 询 ， 这 在 用   O R M   系 统 或 者 生 成 查 询 语 句 的 系 统 很 有 用 。 注 意 不 要 在 生 产 环 境 用 ， 否 则 会 占 满 你 的 磁 盘 空 间 。 记 录 执 行 时 间 超 过   1   秒 的 查 询 ： 8 .   激 进 的 方 法 ， 使 用 内 存 磁 盘 现 在 基 础 设 施 的 可 靠 性 已 经 非 常 高 了 ， 比 如   E C 2   几 乎 不 用 担 心 服 务 器 硬 件 当 机 。 而 且 内 存 实 在 是 便 宜 ， 很 容 易 买 到 几 十 G 内 存 的 服 务 器 ， 可 以 用 内 存 磁 盘 ， 定 期 备 份 到 磁 盘 。 将   M Y S Q L   目 录 迁 移 到   4 G   的 内 存 磁 盘 1 .   S H O W   C R E A T E   T A B L E   d b 1 . t b 1 G 1 .   A D D   U N I Q U E   I N D E X 2 .   A D D   I N D E X 1 .   A L T E R   T A B L E   u s e r s   A D D   U N I Q U E   I N D E X   u s e r n a m e _ n d x   ( u s e r n a m e ) ; 2 .   A L T E R   T A B L E   u s e r s   A D D   U N I Q U E   I N D E X   u s e r n a m e _ p a s s w o r d _ n d x   ( u s e r n a m e , p a s s w o r d ) ; 1 .   e c h o   “ s e l e c t   u s e r n a m e , p a s s w o r d   f r o m   u s e r s ; ”   >   / v a r / l i b / m y s q l / u p c a c h e . s q l 1 .   [ m y s q l d ] 2 .   i n i t - f i l e = / v a r / l i b / m y s q l / u p c a c h e . s q l 1 .   l o g = / v a r / l o g / m y s q l . l o g 1 .   l o n g _ q u e r y _ t i m e = 1 2 .   l o g - s l o w - q u e r i e s = / v a r / l o g / m y s q l / l o g - s l o w - q u e r i e s . l o g 1 .   m k d i r   - p   / m n t / r a m d i s k 2 .   s u d o   m o u n t   - t   t m p f s   - o   s i z e = 4 0 0 0 M   t m p f s   / m n t / r a m d i s k / 3 .   m v   / v a r / l i b / m y s q l   / m n t / r a m d i s k / m y s q l 4 .   l n   - s   / t m p / r a m d i s k / m y s q l   / v a r / l i b / m y s q l 5 .   c h o w n   m y s q l : m y s q l   m y s q l
9 .   用   N O S Q L   的 方 式 使 用   M Y S Q L B - T R E E   仍 然 是 最 高 效 的 索 引 之 一 ， 所 有   M Y S Q L   仍 然 不 会 过 时 。 用   H a n d l e r S o c k e t   跳 过   M Y S Q L   的   S Q L   解 析 层 ， M Y S Q L   就 真 正 变 成 了   N O S Q L 。 1 0 .   其 他 单 条 查 询 最 后 增 加   L I M I T   1 ， 停 止 全 表 扫 描 。 将 非 ” 索 引 ” 数 据 分 离 ， 比 如 将 大 篇 文 章 分 离 存 储 ， 不 影 响 其 他 自 动 查 询 。 不 用   M Y S Q L   内 置 的 函 数 ， 因 为 内 置 函 数 不 会 建 立 查 询 缓 存 。 P H P   的 建 立 连 接 速 度 非 常 快 ， 所 有 可 以 不 用 连 接 池 ， 否 则 可 能 会 造 成 超 过 连 接 数 。 当 然 不 用 连 接 池   P H P   程 序 也 可 能 将 连 接 数 占 满 比 如 用 了 使 用   I P   而 不 是 域 名 做 数 据 库 路 径 ， 避 免   D N S   解 析 问 题 1 1 .   结 束 你 会 发 现 优 化 后 ， 数 据 库 的 性 能 提 高 几 倍 到 几 百 倍 。 所 以   M Y S Q L   基 本 还 是 可 以 适 用 大 部 分 场 景 的 应 用 的 。 优 化 现 有 系 统 的 成 本 比 系 统 重 构 或 者 迁 移 到   N O S Q L   低 很 多 。 阅 读 原 文 @ i g n o r e _ u s e r _ a b o r t ( T R U E ) ;
回复

举报

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

使用Markdown编辑器编辑 使用富文本编辑器编辑

Archiver| 手机版| 小黑屋|
Powered by Discuz! X3.4 Copyright © 2001-2020, Tencent Cloud. 商业源码建议获取授权，如侵犯您的权益，请联系客服处理