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

优化效比图

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部分):

慢查询日志分析工具

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项)( * <font color=red>数据库中最主要的瓶颈就处在IO</font> *)
• 未命中索引的SQL(注意pt-query-digest分析中ROWS EXAMINE和ROWS SEND的对比)(* <font color=red>SQL扫描行数越多，IO消耗越大</font> *)

优化

分析SQL查询

使用explain查询SQL的执行计划(* <font color=red>数据库中SQL都需先进行执行计划分析，然后再进行具体查询</font> *)
我们使用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;

得到执行计划如图：

表扫描执行计划

各字段意义如下表：

extra(扩展列介绍)

特例优化

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

selece max(isbn) from ssm_book;

SQL的执行计划如图：

max执行计划

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

create index idx_isbn on ssm_book(isbn);

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

max索引优化执行计划

-- 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条数据。

未完待续