从四个方面进行优化
1：硬件 2：系统配置 3：表结构的优化 4：sql语句和索引

数据库优化的目的
1：避免出现页面访问错误
由于数据库连接timeout产生页面5xxx错误
由于慢查询找出页面无法加载
由于堵塞造成数据无法提交
2：增加数据库的稳定性
很多数据库问题都是由于低效的查询引起的
3：优化用户体验
流程页面的访问速度
良好的网站功能体验

注意：
系统配置之打开档数限制：mysql数据库是基于档的，每查询一个表都要打开一些档，档数达到一定限制，档就无法打开，就会进行频繁的IO操作。

数据库的优化主要是sql和索引的优化带来的效果和成本是最大的，其次是数据结构

问题1：如何发现有问题的SQL?
使用MySQL慢查看日志对有效率问题的SQL进行监控

show variables like ‘slow_query_log’ 查看慢查询日志是否打开，off表示关闭

show variables like ‘%log%’ 查看所有的慢查询配置

show variables like ‘slow%’ 查看慢查询的记录位置

set global slow_query_log_file=’/home/mysql/sql_log/mysql-slow.log’ 指定慢查询日志的档位置在哪

set global log_queries_not_using_indexes=on; 是否把没有使用索引的session记录到慢查询日志中

set global slow_query_log=on; 开启慢查询日志

set global long_query_time=1; 把超过1秒的查询记录到慢查询日志中

操作过程：

第一步：查看慢查询日志是否打开
show variables like ‘slow_query_log’ 查看慢查询日志是否打开，off表示关闭
第二步：查看所有的配置
show variables like ‘%log%’ 查看所有的慢查询配置
第三步：根据配置把没有使用索引的session记录到慢查询日志中
set global log_queries_not_using_indexes=on;
第四步：查看慢查询时间设置
show variables like ‘long_query_time’;
第五步：设置慢查询时间
set global long_query_time=1; 把超过1秒的查询记录到慢查询日志中
第五步：接下来就可以把慢查询日志设为开启状态
set global slow_query_log=on; 开启慢查询日志

注意：慢查询日志所包含的内容

#User@Host: root[root] localhost[]  执行SQL的主机信息

#Query_time:0.000024 Lock_time:0.000000 Rows_sent:0
Rows_examined: 0  SQL的执行信息

SET timestamp=1402389328 SQL的执行时间

select CONCAT(‘storage engine: ’,@storage_engine) as INFO:
SQL的内容

MySQL慢查询日志分析工具之mysqldumpslow

查看慢查询日志命令：
mysqldumpslow -t 要查看的条数 日志档的位置 | more
例：mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log |more
表示要查看的耗时最久的前三条命令

MySQL慢查询日志分析工具之pt-query_digest

输出到档：pt-query-digest show-log > slow_log.report
输出到数据库表 pt-query-digest show.log -review \
h=127.0.0.1,D=test,p=root.P=3306,u=root,t=query_review \
–create-reviewtable \
–review-history t=hostname_show

pt-query-digest慢查询日志的输出有三部分
第一部分:
显示了日志的时间范围，以及总的SQL数量和不同的SQL数量
第二部分:
一些表的统计信息
Response time:回应时间占比
Calls : sql执行次数
第三部分:具体的SQL是什么
pt-query-digest分析慢查询日志档比mysqldumpslow更丰富:
1.显示日志的时间范围,以及总的sql数量.
2.表的统计信息sql回应时间和执行次数。
3.具体的sql

解决:
1.查询时间长,查询次数多
2.IO大的sql,分析Rows Examine项,扫描的行数
3.未命中索引的sql,分析Rows Examine与Rows send发送的行数的对比

pt-query-digest分析慢查询日志档比mysqldumpslow更丰富。
1：显示日志的时间范围，以及总的sql数量
2：表的统计信息，sql回应时间和执行次数
3：具体的sql

如何通过慢查询日志发现有问题的SQL？
1：查询次数多且每次查询占用时间长的SQL
通常为pt-query_digest分析的前几个查询
2：IO大的SQL
注意pt-query-digest分析中的Rows examine项
3：未命中索引的SQL
注意pt-query-digest分析中Rows examine和Rows Send的对比

当Rows examine>Row send基本上是使用了索引扫描或者表扫描的方式进行查询，需要进行优化。

通过explain查询和分析SQL的执行计划

explain返回各列的意义
table: 显示这一行的数据是关于哪张表的
type: 这是重要的列,显示连接使用了何种类型,从最好到最差的连接类型为const、eq_reg、ref、range、index和all
possible_keys:显示可能应用在这张表中的索引.如果为空,没有可能的索引.
key: 实际使用的索引.如果为null,则没有使用索引.
key_len: 使用的索引的长度.在不损失精确性的情况下,长度越短越好.
ref: 显示索引的那一列被使用了,如果可能的话,是一个常数
rows: MYSQL认为必须检查的用来返回请求数据的行数

extra列需要注意的返回值
Using filesort：看到这个的时候，查询就需要优化了，MYSQL需要进行额外的步骤来发现如何对返回的行进行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

Using temporary 看到这个的时候，查询需要优化了，这里，MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行Order by上，而不是group by上。

其中type是重要的列，以下从最好到最差：

const：是一个常数查找，一般是主键和唯一索引查找
eq_req：主键和唯一索引的范围查找
ref：连接的查找，一般一个表是基于某一个索引的查找
range：基于索引的范围查找
index：基于索引的扫描
all：基于表扫描

SQL及索引优化
Count()和Max()的优化方法

查询最后支付时间—优化max()函数

select max(payment_date) from payment;

Max()和Count()的优化

1.对max()查询，可以为表创建索引，例如
select max(payment_date) from payment;

如何来进行优化呢？ 可以对payment建立索引 create index idx_paydate on payment(payment_date);
然后在进行查询

2.count()对多个关键字进行查询，比如在一条SQL中同时查出2006年和2007年电影的数量，语句：

select  count(release_year='2006' or null) as '2006年电影数量',
       count(release_year='2007' or null) as '2007年电影数量'
from film;

这里解释一下为什么要加’or null’：
count()函数只有count(NULL)是才不计数，即count(NULL)=0；
而’release_year=’2006”是个比较运算符，结果为1或者0，不管是不是2006 count函数都会计数。
当加上or null以后，当值不为2006，release_year=’2006’的结果为0, ‘0 or null’结果为null，这样就排除了2006以外的结果

3.count(*) 查询的结果中，包含了该列值为null的结果

1.count(*)和count(release_year)的区别：
count(*)统计满足条件的记录数release_year=null的记录也会被统计，count(release_year)则会排除为null的结果再统计。
2.mysql中：

SELECT 4=4, 3=4, 1 or null, 0 or null
1   |   0   |   1   |   NULL

3.count(release_year=’2006’)和count(release_year=’2006’ or null)的区别：
首先count(release_year=’2006’)不能得到目标的结果（统计2006的记录数），因为count()只排除null结果的记录。而count(‘2007’=’2006’)中’2007’=’2006’结果为0任然会被统计。只有加上or null后变为 ‘2007’=’2006’ or null 结果为null 才不会被统计。

问题：
count(release_year = ‘2006’ or NULL) 这部分 为什么要加上or NULL 直接count(release_year=’2006’)有什么问题吗？不就是要找release_year = ‘2006’的数据吗，为什么要计算NULL的数据？？？
count(‘任意内容’)都会统计出所有记录数，因为count只有在遇见null时不计数，即count(null)==0，因此前者单引号内不管输入什么值都会统计出所有记录数，所以这节课例子中count(release_year=’2006’)单引号里你填什么年份都是1000条记录，因为他们都not null！

count()和count(列)的区别在于count()会统计null的行,而count(列)不会;
如果是列是不允许为空的,那么count(列)效率更高

如何分析SQL查询
. explain返回割裂的含义
. extra列需要注意的返回值
Using filesort:看到这个的时候,查询就需要优化了.
Using temporary:看到这个的时候,查询需要优化

子查询的优化：
通常情况下，需要吧1子查询优化为join查询，但在优化时要注意关联键是否有一对多的情况，要注意重复数据。如果有则需要使用distanct关键字去重。
子查询方式：select * from t where t.id in(select t1.tid from t1);
优化成join方式进行查询： select t.id from t join t1 on t.id=t1.tid;

若存在重复则：（若采用子程序仍然是一行，而join查询就会出现重复）
select distinct t.id from t join t1 on t.id=t1.tid;

例：查询sandra出演的所有影片

explain select title,release_year,LENGTH
from film
where film_id in(
      select film_id from film_actor where actor_id in(
        select actor_id from actor where first_name=’sandra’))

group by的优化
优化前：

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

优化后：

explain select actor.first_name,actor.last_name,c.cnt 
from  sakila.actor  inner join (
select actor_id,count(*) AS cnt from  sakila.film_actor 
GROUP BY actor_id) AS c USING(actor_id)；

注意：优化group by查询后，要在外面加上筛选条件则在子查询中增加。

Limit查询的优化
limit常用于分页处理，时常会伴随着order by从句使用，因此大多数时候会使用Filesorts这样会造成大量的IO问题
select film_id,description from sakila.film order by title limit 50,5;

优化步骤：
1：使用有索引的列或进行order by操作

select film_id,description from sakila.film order by film_id limt 50,5;

2：记录上次返回的主键，在下次查询时用主键过滤(避免了数据量大时扫描过多的记录)

select film.id,description from sakila.film where film_id >55 and film_id<=60 order by film_id limit 1,5;

使用这种方法有一个限制，就是主键一定要顺序排序和连续的，如果主键出现了空缺可能会导致最终页面上显示的列表不足5条，解决方法是附加一列，保证这一列是自增的并增加索引就可以了的。