MySQL数据库的优化.docx-资料库

4a95d7fa-9c68-4d20-99d5-ee72f23ed72f.docx.pdf-第1页.png

第1页 / 共25页

4a95d7fa-9c68-4d20-99d5-ee72f23ed72f.docx.pdf-第2页.png

第2页 / 共25页

4a95d7fa-9c68-4d20-99d5-ee72f23ed72f.docx.pdf-第3页.png

第3页 / 共25页

4a95d7fa-9c68-4d20-99d5-ee72f23ed72f.docx.pdf-第4页.png

第4页 / 共25页

4a95d7fa-9c68-4d20-99d5-ee72f23ed72f.docx.pdf-第5页.png

第5页 / 共25页

4a95d7fa-9c68-4d20-99d5-ee72f23ed72f.docx.pdf-第6页.png

第6页 / 共25页

4a95d7fa-9c68-4d20-99d5-ee72f23ed72f.docx.pdf-第7页.png

第7页 / 共25页

4a95d7fa-9c68-4d20-99d5-ee72f23ed72f.docx.pdf-第8页.png

第8页 / 共25页

MySQL 优化

1. 课程计划 1. MySQL 介绍 2. MySql 流程 3. 查询优化 4. 索引的使用 5. 存储优化 6. 数据库结构优化 7. 硬件优化 8. MySql 缓存 9. 服务器参数 2. MySQL 介绍 MySQL 是由 MySQLAB 公司（目前已经被 SUN 公司收归麾下）自主研发的，目前 IT 行业最流行的开放源代码的数据库管理系统之一，它同时也是一个支持多线程高并发多用户的关系型数据库管理系统。 MySQL 数据库以其简单高效可靠的特点，在最近短短几年的时间就从一个名不见经传的数据库系统，变成一个在 IT 行业几乎是无人不知的开源数据库管理系统。目前 MySQL 已经成为最为流行的开源关系数据库系统，并且一步一步地占领了原有商业数据库的市场。可以看到 Google，Facebook，Twitter，百度，新浪，腾讯，淘宝，网易，久游等绝大多数互联网公司数据库都是用的 MySQL 数据库，甚至将其作为核心应用的数据库系统。而 MySQL 数据库也不再仅仅应用于 Web 项目，其扮演的角色更为丰富。在网络游戏领域中，大部分的后台数据库都采用 MySQL 数据库，如大家比较熟悉的劲舞团、魔兽世界等。很少能看到有哪个网络游戏数据库不是采用 MySQL 数据库的。此外，MySQL 数据库已成功应用于中国外汇交易中心、中国移动、国家电网等许多项目中。一个数据库应用系统（这里的数据库应用系统概指所有使用数据库的系统）的性能瓶颈最容易出现在数据的操作方面，而数据库应用系统的大部分数据操作

都是通过数据库管理软件所提供的相关接口来完成的。所以数据库管理软件也就很自然的成为了数据库应用系统的性能瓶颈所在，这是当前业界比较普遍的一个看法。我们将这里进行一个较为全面的分析，让大家了解到一个数据库应用系统的性能到底与哪些地方有关，让大家寻找出各自应用系统的出现性能问题的根本原因，而尽可能清楚的知道该如何去优化自己的应用系统。 3. MySQL 架构下图是 MySQL 架构的总览图

3.1. 查询执行流程查询执行的流程是怎样的： 1. 连接 1.1 客户端发起一条 Query 请求，监听客户端的‘连接管理模块’接收请求 1.2 将请求转发到‘连接进/线程模块’ 1.3 调用‘用户模块’来进行授权检查 1.4 通过检查后，‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求 2. 处理 2.1 先查询缓存，检查 Query 语句是否完全匹配， 2.2 查询缓存失败则转交给‘命令解析器’ 2.3 再转交给对应的模块处理 2.4 如果是 SELECT 查询还会经由‘查询优化器’做大量的优化，生成执行计划 2.5 模块收到请求后，通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限 2.6 有则调用‘表管理模块’，先是查看 table cache 中是否存在，有则直接对应的表和获取锁，否则重新打开表文件 2.8 根据表的 meta 数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理 2.9 上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中 3. 结果 3.1Query 请求完成后，将结果集返回给‘连接进/线程模块’ 3.2 返回的也可以是相应的状态标识，如成功或失败等 3.3‘连接进/线程模块’进行后续的清理工作，并继续等待请求或断开与客户端的连接 4. 什么是优化  合理安排资源、调整系统参数使 MySQL 运行更快、更节省资源。  优化是多方面的，包括查询、表设计、服务器等。  原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

5. 查询优化在优化 MySQL 时，通常需要对数据库进行分析。常见的分析手段有慢查询日志，EXPLAIN 分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。 5.1. 慢查询 5.1.1. 慢查询日志开启在配置文件 my.cnf 或 my.ini 中在[mysqld]一行下面加入两个配置参数 log-slow-queries=/data/mysqldata/slow-query.log long_query_time=5 注：log-slow-queries 参数为慢查询日志存放的位置，一般这个目录要有 mysql 的运行帐号的可写权限，一般都将这个目录设置为 mysql 的数据存放目录； long_query_time=5 中的 5 表示查询超过五秒才记录；还可以在 my.cnf 或者 my.ini 中添加 log-queries-not-using-indexes 参数，表示记录下没有使用索引的查询。 5.1.2. 慢查询分析我们可以通过打开 log 文件查看得知哪些 SQL 执行效率低下从日志中，可以发现查询时间超过 5 秒的 SQL，而小于 5 秒的没有出现在此日志中。如果慢查询日志中记录内容很多，可以使用 mysqldumpslow 工具（MySQL 客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow 对日志文件进行了分类汇总，显示汇总后摘要结果。进入 log 的存放目录，运行 [root@mysql_data]#mysqldumpslow slow-query.log Reading mysql slow query log from slow-query.log Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql

select count(N) from t_user; mysqldumpslow 命令 /path/mysqldumpslow -s c -t 10 /database/mysql/slow-query.log 这会输出记录次数最多的 10 条 SQL 语句，其中： -s, 是表示按照何种方式排序，c、t、l、r 分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙 -t, 是 top n 的意思，即为返回前面多少条的数据； -g, 后边可以写一个正则匹配模式，大小写不敏感的；例如： /path/mysqldumpslow -s r -t 10 /database/mysql/slow-log 得到返回记录集最多的 10 个查询。 /path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log 得到按照时间排序的前 10 条里面含有左连接的查询语句。使用 mysqldumpslow 命令可以非常明确的得到各种我们需要的查询语句，对 MySQL 查询语句的监控、分析、优化是 MySQL 优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用 CPU 资源影响 mysql 的性能，但是可以阶段性开启来定位性能瓶颈。 5.2. EXPLAIN 在 MySQL 中可以使用 EXPLAIN 查看 SQL 执行计划，用法：EXPLAIN SELECT * FROM products 5.2.1. id SELECT 识别符。这是 SELECT 查询序列号。这个不重要。 5.2.2. select_type 表示 SELECT 语句的类型。例如： 1、 SIMPLE

表示简单查询，其中不包含连接查询和子查询。 2、 PRIMARY 表示主查询，或者是最外面的查询语句。 3、 UNION 表示连接查询的第 2 个或后面的查询语句。 5.2.3. table 表示查询的表。 5.2.4. type 表示表的连接类型。以下的连接类型的顺序是从最佳类型到最差类型： 1、 system 表仅有一行，这是 const 类型的特列，平时不会出现，这个也可以忽略不计。 2、 const 数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于 3、 eq_ref mysql 手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了 const 类型。它用在一个索引的所有部分被联接使用并且索引是 UNIQUE 或 PRIMARY KEY"。eq_ref 可以用于使用= 比较带索引的列。 4、 ref 查询条件索引既不是 UNIQUE 也不是 PRIMARY KEY 的情况。ref 可用于=或<或> 操作符的带索引的列。 5、 ref_or_null 该联接类型如同 ref，但是添加了 MySQL 可以专门搜索包含 NULL 值的行。在解决子查询中经常使用该联接类型的优化。上面这五种情况都是很理想的索引使用情况。 6、 index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key 列包含了使

用的索引的清单，key_len 包含了使用的索引的最长的关键元素。 7、 unique_subquery 该类型替换了下面形式的 IN 子查询的 ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery 是一个索引查找函数,可以完全替换子查询,效率更高。 8、 index_subquery 该联接类型类似于 unique_subquery。可以替换 IN 子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) 9、 range 只检索给定范围的行,使用一个索引来选择行。 10、 index 该联接类型与 ALL 相同,除了只有索引树被扫描。这通常比 ALL 快,因为索引文件通常比数据文件小。 11、 ALL 对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差） 5.2.5. possible_keys 指出 MySQL 能使用哪个索引在该表中找到行。如果该列为 NULL，说明没有使用索引，可以对该列创建索引来提高性能。 5.2.6. key 显示 MySQL 实际决定使用的键(索引)。如果没有选择索引,键是 NULL。 5.2.7. key_len 显示 MySQL 决定使用的键长度。如果键是 NULL,则长度为 NULL。注意：key_len 是确定了 MySQL 将实际使用的索引长度。 5.2.8. ref 显示使用哪个列或常数与 key 一起从表中选择行。

资料库

MySQL数据库的优化.docx

相关推荐

数据库

热门标签

最新资料