mysql的优化

jlthlx

　　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文件,在[mysqld]最后增加如下命令：
　　复制代码 代码如下:
　　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.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);
　　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