MySQL优化

2017-07-14 本文已影响0人 FcxD

这是个很大的坑，无法一下子写完，敬请期待 (溜
数据库优化可以从SQL及索引、数据库表结构、系统配置以及硬件这几个方面进行优化，优化成本及其效果见如下金字塔结构图。

优化效比图
下面我们以MySQL为例，介绍如何从这几个方面对数据库进行优化。

MySQL优化

慢查询

我们先使用下列命令查看系统慢查询配置

-- variables：系统变量
show variables  like 'slow_query_log'; -- 查看是否开启慢查询日志记录
show variables like 'log_queries_not_using_indexes'; -- 查看未使用索引的语句是否会记录在日志中
show variables like 'long_query_time'; -- 查看超过多长时间的查询会被记录
show variables like 'slow_query_log_file'; -- 查看日志存储位置

设置变量

-- global 系统全局变量
set global log_queries_not_using_indexes = on; -- 开启未使用索引的SQL记录
set global show_query_log = on; -- 开启慢查询日志记录

慢查询日志存储格式(5部分):

查询执行时间	# Time: 140712 8:33:29
执行SQL的主机信息	# User@Host: root[root]@localhost[]
SQL执行信息	# Query_time: 0.0005 Lock_time: 0.000...
SQL执行时间	SET timestamp=1405125209
SQL内容	show tables;

慢查询日志分析工具

1. mysqldumpshow
2. pt-query-digest
  # pt-query-digest /log/path/file.log | more

优化的问题点

查询次数多且每次查询占用时间长的SQL(通常为pt-query-digest分析的前几个查询)
IO大的SQL(注意pt-query-digest分析中的ROWS EXAMINE项)( * 数据库中最主要的瓶颈就处在IO *)
未命中索引的SQL(注意pt-query-digest分析中ROWS EXAMINE和ROWS SEND的对比)(* SQL扫描行数越多，IO消耗越大 *)

优化

分析SQL查询

使用explain查询SQL的执行计划(* 数据库中SQL都需先进行执行计划分析，然后再进行具体查询 *)
我们使用ssm_book表来做演示,ssm_book表结构如下：

create table ssm_book(
    id int auto_increment primary key, -- ID
    isbn varchar(15) not null, -- 图书ISBN(国际标准书号)编号
    path varchar(150) default '' null, -- 封面路径
    title varchar(200) default '' null, -- 书名
    subtitle varchar(200) default '' null, -- 副标题
    original_title varchar(200) default '' null, -- 原名称
    market_price varchar(10) default '' null, -- 市场价
    intro text null, -- 介绍
    binding varchar(20) default '' null, -- 装订类型
    pages varchar(10) null, -- 页数
    author varchar(200) null, -- 作者
    publisher varchar(100) null, -- 出版商
    catalog text null, -- 目录
    supply varchar(20) null, -- 库存
    status int default '0' null, -- 发布状态
    hot int default '0' null -- 热度
);

数据文件点击下载。

先执行一个简单的SQL，看看其执行计划：

select id, isbn, title from ssm_book;

得到执行计划如图：

表扫描执行计划

各字段意义如下表：

字段名	意义
table	显示这一行的数据是关于哪张表的
type	这是最重要的列，显示链接使用了何种类型。从最好到最差的连接类型为const, eq_reg, ref, range, index和ALL
possible_keys	显示可能应用在这张表中的索引。如果为空，则没有可用的索引
key	实际使用的索引。如果为空，则没有使用索引
key_len	使用的索引的长度。在不损失精度的情况下，长度越短越好
ref	显示索引的哪一列被使用了。如果可能的话，最好为一个常数
rows	MySQL认为必须检查的用来返回请求数据的行数
extra	扩展列。下面表格介绍。

extra(扩展列介绍)

值	意义
using filesort	使用文件排序。看到这个值说明查询需要优化。MySQL需要进行额外的步骤来发现如何对返回行排序。它根据连接类型以及存储排序键值和匹配条件的全部行指针来排序全部行
using temporary	使用临时表。看到这个值说明查询需要优化。这里，MySQL需创建一个临时表来存储结果。这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

特例优化

count()和max()函数的优化方法
eg: 查询ISBN最大的数据 -- 优化max()函数

selece max(isbn) from ssm_book;

SQL的执行计划如图：

max执行计划

可以看到执行这条SQL我们会进行表扫描。
优化的方案通常为在max()函数的键上建立索引。

create index idx_isbn on ssm_book(isbn);

这样，我们的执行计划将变成下图所示：

max索引优化执行计划
这样，我们仅通过索引就能得到结果。 * 索引是顺序排列的，max()函数只需取最后一个就好了 *
eg: 在一条语句中同时查询出中国出版和美国出版的书的数量。 -- count()函数优化

-- ISBN号以978-0 978-1开头的书籍为美国出版，以978-7开头的书籍为中国大陆出版
select count(isbn) from ssm_book where isbn like '9787%' or isbn like '9780%' or isbn like '9781%';

这样的一条SQL并不能达到我们的目的，我们可以使用下面的语句来进行查询：

select count(isbn like "9787%" or NULL) as "中国出版", count(isbn like "9780%" or isbn like "9781%" or NULL) as "美国出版" from ssm_book;

执行计划如下图：

count执行计划

如图，该SQL达到了我们大要求，而且没有使用表扫描。

子查询优化
通常情况下，对子查询的优化为，将子查询优化为join查询，但在优化时需要注意关联键是否存在一对多的关系，需要注意重复数据(用distinct()函数去重)。
group by查询的优化

select actor.first_name, actor.last_name,count(*) 
from sakila.film_actor inner join sakila.actor using(actor_id)
 group by film_actor.actor_id;

该SQL可以使用子查询进行优化：

select actor.first_name, actor.last_name, c.cnt 
from askila.actor inner join (select actor_id, count(*) as cnt 
from sakila.film_actor group by actor_id) as c using(actor_id);

在该SQL优化中，我们使用了子查询先对数据进行过滤，从而达到优化目的。由此可见，SQL的优化并没有统一优化方案，需要具体情况具体分析。

limit查询优化
limit常用语分页处理，时常会伴随着order by从句使用，因此大多时候会使用filesort，这样会造成大量的IO问题。

SELECT id, isbn, title from ssm_book ORDER BY title ASC LIMIT 0, 10;

该SQL将进行表扫描并且使用文件排序，执行计划如图：

limit执行计划

下面介绍优化方案：
步骤1：
使用有索引的列或主键进行order by操作。

-- 这里我们就不为title添加索引了，我们使用ISBN进行模拟测试
select id, isbn, title from ssm_book ORDER BY isbn asc LIMIT 0, 10;

该SQL执行计划如图所示：

limit索引执行计划

比较其执行计划，第一条SQL使用表扫描同事使用文件排序，而第二个语句使用索引查询，而且扫描行数仅为10行。

步骤2：
第二个SQL的扫描行数为10？是不是觉得很奇怪？这里，扫面函数为10行是因为SQL的limit取的是前10行。于是就会发现问题，LIMIT取越后面的行的数剧扫描的行数也就越多，那就是说取越后面的数据SQL执行效率也就越慢。
解决的办法就是：记录上次返回的主键，在下次查询时使用主键过滤。

select id, isbn, title from ssm_book where id >= 1000 and id <=1009 ORDER BY id desc limit 0, 10;

执行计划如图：

limit执行计划

这样的话SQL扫描的行数就永远是10行了，而且连接类型也为比index更优的range，缺点是列必须为顺序且中间无间断，否则将取不到10条数据。

未完待续

MySQL优化

MySQL优化

慢查询

慢查询日志分析工具

优化的问题点

优化

分析SQL查询

特例优化

猜你喜欢

热点阅读