mysql的优化
1.使用命令show variables like 'slow_query_log';
查看到当前没有开启慢查询
2.使用命令
show variables like '%log%';
也没有开启log_queries_not_using_indexs
3.set global log_queries_not_using_indexes=on;
4.show variables like 'long_query_time';
查看到long_query_time的值为10，意思是慢查询日志中会记录超过十秒的记录;
默认情况下，MySQL是不会记录超过一定执行时间的SQL语句的。要开启这个功能，我们需要修改MySQL的配置文件，windows下修改my.ini,Linux下修改my.cnf文件,在最后增加如下命令：
复制代码 代码如下:
slow_query_log
long_query_time = 1
5.开启慢查询日志
set global slow_query_log=on;
{
导入mysql官方提供的sakila数据库：
将文件解压，得到三个文件，
打开cmd：使用命令 mysql -uroot -p < "sakila-schema.sql所在的路径"
使用命令 mysql -uroot -p < "sakila-data.sql所在的路径"
}
6.mysql 慢查询日志分析工具
*mysqldumpslow
*pt-query-digest
7.如何通过慢查询日志发现有问题的SQL
* 查询次数多且每次查询所占用的时间长的SQL
通常为pt-query-digest分析的前几个查询
*IO大的SQL
注意pt-query-digest分析中的Rows exammine项
*未命中索引的SQL
注意pt-query-digest分析中的Rowsexamine和Rows Send的对比
8.使用explain查询SQL的执行计划
explain返回各列的含义
table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连续类型为const,eq_reg,ref,range,index和ALL(，没有where从句，表扫描)。
possible_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引
key:实际使用的索引。如果为NULL，则没有使用索引
key_len:使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：mysql认为必须检查的用来返回请求数据的行数
9.Max()的优化方法
*查询最后支付时间----优化MAX()函数
explain select max(payment_date) from payment;
可以查看到：type:ALL是表扫描操作;没有任何索引，rows非常大，IO效率非常低。
如何优化这个SQL：通常情况下建立索引：
create index> 这样在次执行explain select max(payment_date) from payment;
可以看到Extra：select tables optimized away;可以通过索引进行操作，大大减少了IO操作
10.Count()的优化方法
*在一条SQL中同时查出2006年和2007年电影的数量----优化count()函数
错误方式：
SELECT COUNT(release_year='2006' OR> 无法分开计算2006和2007年的电影数量
select count(*) FROM film WHERE> release_year不可能同时为2006和2007，因此有逻辑错误
正确的方式：
SELECT COUNT(release_year='2006' OR NULL) AS '2006年电影数量',COUNT(release_year='2007' OR NULL) AS '2007年电影数量' FROM film;
{
现在说说count(*)和count(id)的区别:
新建一张表：create table t(id int);
插入数据：insert into t values (1),(2),(null);
当我们使用命令：select count(*),count(id) from t;
显示出count(*)为3，而count(id)为2;
说明count(*)包含了null的，count(id)不包含值为null的
}
11.子查询的优化
*通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。
*我们新建一张表t1
create table t1(tid int);
插入一条数据insert into t1 values(1);
进行子查询：select * from t where t.id in (select * from t1.tid from t1);
返回t表id在t1表中的数据
*优化成join的形式：
select t.id from t join t1 on t.id=t1.tid;
这两种形式返回的结果是一样的
*需要注意的是：
如果在t1表中，添加一条数据：insert into t1 values(1);
然后在分别执行这两种形式的查询：
发现使用select * from t where t.id in (select * from t1.tid from t1);查询出来的结果是一条数据。而select t.id from t join t1 on t.id=t1.tid;是两条数据，说明有重复数据，我们可以使用distinct去重
select distinct t.id from t join t1 on t.id=t1.tid;这样就返回一条记录了
12.优化group by查询
explain 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;
{
using可用在join语句相同字段连接，起到和ON相同作用，inner join 和left join中都可以使用
示例：LEFT JOIN 正常写法：
SELECT t1.id,t2.name FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE ....
其实也可以这么写：
SELECT t1.id,t2.name FROM t1 LEFT JOIN t2 USING(id) WHERE ....
}
上面的explain执行结果，可以看到extra为Using temporary，Using filesort用到了临时表,文件排序的方式对表进行了全表扫描.
我们应该避免这种方式。改写如下：
Explain SELECT actor.first_name,actor.last_name,c.cntFROM sakila.actor INNER JOIN(SELECT actor_id,COUNT(*) AS cnt FROM sakila.film_actor GROUP BY actor_id) AS c USING(actor_id);
13.优化limit查询
*limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts，这样会造成大量的IO问题
SELECT film_id,description FROM sakila.film ORDER BY> *这个语句进行explain操作，发现会进行全表扫描，并且有文件排序的方式
*优化方式：
步骤1.使用有索引的列或主键进行Order By操作
SELECT film_id,description FROM sakila.film ORDER BY film_id LIMIT 50,5
使用这种方式可以得到相同的结果，但是explain执行计划却完全不同，type=index,rows=55
这种方式并不是最优的，比如将limit 50,5改为limit 500,5;rows=505，rows会随着limit而改变，如果有上万条数据，那么响应速度回变慢
步骤2.记录上次返回的主键，在下次查询时使用主键过滤
SELECT film_id,description FROM sakila.film WHERE film_id>55 and film_id
页:
[1]