MySQL优化.pdf

《MySQL优化.pdf》由会员分享，可在线阅读，更多相关《MySQL优化.pdf（11页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、MYSQL 优化 1.MYSQL 介绍 2.MySql 流程 3.查询优化 4.索引的使用 5.存储优化 6.数据库结构优化 7.硬件优化 8.MySq 缓存 9.服务器参数 2.MySQL 介绍 MyQL 是由 MySQLAB 公司(目前已经被 SUN 公司收归毫下)自主研发的，目前 T行业最流行的开放源代码的数据库管理系统之一，它同时也是一个支持多线程高并发多用户的关系型数据库管理系统。目前 MySQL 已经成为最为流行的开源关系数据库系统，并且一步一步地占领了原有商业数据库的市场。可以看到 Google，Facebook，Twitter，百度，新浪，腾讯，淘宝，网易，久游等绝大多数互联网

2、公司数据库都是用的 MySQL 数据库甚至将其作为核心应用的数据库系统。而 My SQL 数据库也不再仅仅应用于 Web 项目，其扮演的角色更为丰富。在网络游戏领域中，大部分的后台数据库都采用 MySQL 数据库，如大家比较熟悉的劲舞团、魔兽世界等。很少能看到有哪个网络游戏数据库不是采用 MySQL 数据库的。此外，MySQL 数据库已成功应用于中国外汇交易中心、中国移动、国家电网等许多项目中。个数据库应用系统(这里的数据库应用系统概指所有使用数据库的系统的性能瓶颈最容易出现在数据的操作方面，而数据库应用系统的大部分数据操作都是通过数据库管理软件所提供的相关接口来完成的。所以效据库管理软件也就

3、很自然的成为了数据库应用系统的性能瓶颈是当前业界比较普遍的一个现象。我们将这里进行一个较全面的分析，让大家了解到一个数据库应用系统的性能到底与哪些地方有关，让大家寻找出各自应用系统的出现性能问题的根本原因，而尽可能清楚的知道该如何去优化自己的应用系统。3.MySQL 系统架构 5.MySQL 查询优化 在优化 MySQL 时，通常需要对数据库进行分析。常见的分析手段有慢查询日志，EXPLAINT 分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。5.1 慢查询 5.1.1 慢查询日志开启 在配置文件 my cnf 或 myn 中在 mysqld一行下面加入两个配置参数：log-

4、slow-queries=data/mysqldata/slow-query log long query time=5 注:log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysq 的 运行帐号的可写权限，一般都将这个目录设置为 mysql 的数据存放目求录:long query time=5 中的 5 表示查询超过五秒才记录:;中 还可以在 my cnf 或者 myn 中添加 log-queries-not-usIng-indexes参数，表示记录 下没有使用索引的查询 5.1.2 慢查询分析 我们可以通过打开 log 文件查看得知哪些 SQL 执行效率低下 从

5、日志中，可以发现查询时间超过 5 秒的 SQL，而小于 5 秒的没出现在此日志中。如果慢查询日志中记录内容很多，可以使用 mysqldumpslow 工具（MySQL 客户端安装自带）来对慢查询日志进行分类汇总。Mysqldumpslow 对日志文件进行了 分类汇总，显示汇总后摘要结果。进入 log 的存放目录，运行 rootmysql data#mysqldumpslow slow-query log Reading mysql slow query log from slow-query log Count:2 Time=11.00s(22s)Lock=0.00s(Os)Rows=1.0(

6、2)，rootroot mysql select count(N)from t user;mysqldumpslow 命令 /path/mysqldumpslow-s c-t 10/database/mysql/slow-query log 这会输出记录次数最多的 10 条 SQL 语句，其中;，是表示按照何种方式排序，c、t、|、r 分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙 t，是 topn 的意思，即为返回前面多少条的数据 g，后边可以写一个正则匹配模式，大小写不敏感的:例如:/path/mysqldumpslow-s r-t 10/d

7、atabase/mysql/slow-log 得到返回记录集最多的 10 个查询。/path/mysqldumpslow-st-t 10-g left join/database/mysql/slow-lop 得到按照时间排序的前 10 条里面含有左连接的查询语句 使用 mysqldumpslow 命令可以非常明确的得到各种我们需要的查询语句，对 MySQL 查询语句的监控、分析、优化是 MySQL 优化非常重要的一步。开启慢查 询日志后，由于日志记录操作，在一定程度上会占用 CPU 资源影响 mysql 的性 能，但是可以阶段性开启来定位性能瓶颈。5.2.EXPlAIN 在 MySQL 中可

8、以使用 EXPLA|N 查看 SQL 执行计划，用法:EXPLAIN SELECT*FROM 5.2.1.id SELECT 识别符。这是 SELECT 查询序列号。这个不重要。5.2.2.select_type 表示 SELECT 语句的类型。例如:1、S|MPLE 表示简单查询，其中不包含连接查询和子查询。2、PR|MARY 表示主查询，或者是最外面的查询语句 3、UNION 表示连接查询的第 2 个或后面的查询语句。5.2.3.tab|e 表示查询的表。5.2.4.type 体中文-小四 AA 实 A 表示表的连接类型。以下的连接类型的顺序是从最佳类型到最差类型:1.system 表仅有

9、一行，这是类型的特列，平时不会出现，这个也可以忽略不计。2.const 数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于 3、eq ref mysql 手册是这样说的:对于每个来自于前面的表的行组合，从该表中读取 行。这可能是最好的联接类型，除了 const 类型。它用在一个素引的所有部分 被联接使用并且索引是 UNIQUE 或 PR|MARY KEY。eq_ref 可以用于使用=比较 带索引的列。4.ref 查询条件索引既不是 UN|QUE 也不是 PRIMARY KEY 的情况。ref 可用于=或 5.ref_or_null 该联接类型如同 ref，但是添加了 MYSQL 可

10、以专门搜索包含 NULL 值的行。在 解决子查询中经常使用该联接类型的优化。上面这五种情况都是很理想的索引使用倩况。上面 5 种情况是最理想的情况。6、index merge 该联接类型表示使用了素引合并优化方法。在这种情况下，key 列包含了使用 的索引的清单，key len 包含了使用的索引的最长的关键元素。7、unique subquery 该类型替换了下面形式的|N 子查询的 ref:value|N(SELECT primary key FROm single_ table WHERE some_ expr)+unique subquery 是一个素引查找函数可以完全替换子查询效率更高

11、 8、index subquery 该联接类型类似于 unique subquery。可以替换|N 子查询但只适合下列形式 的子查询中的非唯一素引:vaue|N(select key column FROM single tab|e WHERE some expr)只检素给定范围的行，使用一个素引来选择行。10、该联接类型与 ALL 相同，除了只有素引树被扫描。这通常比 AL 快，因为索引文 件通常比数据文件小 11、ALL 来自于先前的表的行组合进行完整的表扫描。(性能最差)5.2.5.possible keys 指出 MySQL 能使用哪个素引在该表中找到行。+如果该列为 NULL，说明没

12、有使用索引，可以对该列创建索引来提高性能。5.2.6.key 显示 MySQL 实际决定使用的键(索引)。如果没有选择索引键是 NULL。5.2.7，key len 显示 MySQL 决定使用的键长度。如果键是 NULL 则长度为 NULL。5.2.8.ref 显示使用哪个列或常数与 key 一起从表中选择行。5.2.9，rows.显示 MySQL 认为它执行查询时必须检查的行数。5.2.10.Extra 该列包含 MySQL 解决查询的详细信息 Distinct:MySQL 发现第 1 个匹配行后停止为当前的行组合搜素更多的行。Not exists:MySQL 能够对查询进行 LEFT JO

13、|N 优化，发现 1 个匹配 LEFT JO|N 标 准的行后不再为前面的的行组合在该表内检查更多的行。range checked for each record(index map:#):MySQL 没有发现好的可以使用的 索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。Using filesort:MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检 索表中的列信息 Using temporary:为了解决查询，MySQL 需要创建一个临时表来容纳结果 Using where:WHERE

14、子句用于限制哪一个行匹配下一个表或发送到客户。Using sort union()，Using union(.，Using intersect(.):这些函数说明如何为 index merge 联接类型合并索引扫描。Using index for group-by:类似于访问表的 Using index 方式，Using index for group-by 表示 MySQL 发现了一个素引可以用来查询 GROUP BY 或 DISTINCT 查询的所有列而不要额外搜索硬盘访问实际的表。6.索引使用 6.1.1.MySQL 索引 6.1.1.1.B-Tree 索引 一般来说，My SQL 中的 B-Tree 索引的物理文件大多都是以二叉树的结构来存 储的，也就是所有实际需要的数据都存放于树的叶子节点，而且到任何一个叶子