sql及索引优化

如何发现有问题的sql？

使用Mysql的慢查询日志对有效率问题的SQL进行监控

    show variables like 'slow_query_log';
set global slow_query_log_file = 'home/mysql/sql_log/mysql-slow.log';
set global log_queries_not_using_indexes = on;
set global long_query_time = 1;

慢查询日志所包含信息

#执行sql的主机信息
#User@Host：root[root] @ localhost[]
#sql的执行信息
#Query_time: 0.00024 Lock_time: 0.00000 Rows_sent:0 Rows_examined:0
#sql执行时间
SET timestamp = 1402389328；
#sql的内容
select CONCAT('storage engine:', @@storage_engine) as INFO;

慢日志的分析工具-----mysqldumpslow输出

mysqldumpslow -t 3 /data/mysql/mysql-slow.log | more

慢查询工具pt-query-digest

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm

输出到文件

pt-query-digest slow-log > slow_log.report

输出到数据库表

pt-query-digest slow.log -review \
    h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
    --create-reviewtable \
    --review-history t = hostname_slow

pt-query-digest /data/mysql/mysql-slow.log | more

如何通过慢日志查日志发现有问题的sql？

1、查询次数多且每次查询占用时间长的sql

通常为pt-query-digest分析的前几个查询

2、IO大的sql

注意pt-query-digest分析中的Rows examine项

3、未命中索引的sql？

注意pt-query-digest分析中Rows examine 和Rows Send 的对比

如何分析SQL查询

使用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上

count()和max()的优化方法

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

    select max(payment_date) from payment;

添加索引

    create index idx_paydate on payment(payment_date);

在一条sql中同时查出2006年和2007年电影的数量——优化count()函数

错误的方式：

    SELECT count(release_year = '2006' OR release_year = '2007') from film;

无法分开计算2006和2007年的电影数量

    SELECT count(*) from film where release_year = '2006' and release_year ='2007';

Release_year不可能同时为2006和2007，因此上有逻辑错误

正确的方法：

    SELECT count(release_year = '2006' OR NULL) as '2006年电影数量',count(release_year='2007' OR NULL) as '2007年电影数量' from film;

子查询的优化

通常情况下，需要把子查询优化为join查询，但在优化的时候要主义关联是否有一对多的关系，要注意重复数据。

（查询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' ) );

优化后

  select f.title,f.release_year,f.LENGTH FROMfilm f
    LEFT JOIN film_actor fa ON f.film_id = fa.film_id
    LEFT JOIN actor a ON a.actor_id = fa.actor_id 
    WHERE
        a.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 film_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 );

优化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 limit 50;
    #优化步骤2：记录上次返回的主键，在下次查询时使用主键过滤
    select film_id,description from sakila.film where film_id > 55 and film_id <= 60 ORDER BY film_id limit 1,5;

如何选择合适的列建立索引？

1、在where从句，group by从句，order by从句，on 从句中出现的列

2、索引字段越小越好

3、离散度大的列放到联合索引的前面

        select * from payment where staff_id =2 and customer_id = 584;
        #是index(staff_id,customer_id)好？还是index(customer_id,staff_id)好？
        #由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id)  

索引的维护及优化----重复及冗余索引

重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中primary key 和 ID列上的索引就是重复索引

    create table test(
        id int not null primary key,
        name varchar(10) not null,
        title varchar(50) not null,
        uqique(id)  
        )engine=innodb;

查询冗余索引

需要在information_schema表下运行

    use information_schema；
    select a.TABLE_SCHEMA AS '数据名',
            a.table_name AS '表名',
            a.index_name AS '索引1',
            b.INDEX_NAME AS '索引2',
            a.COLUMN_NAME AS '重复列名'
        from STATISTICS a JOIN STATISTICS b ON
            a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_NAME = b.table_name
            AND a.SEQ_IN_INDEX=b.SEQ_IN_INDEX AND a.COLUMN_NAME = b.COLUMN_NAME
        where a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME<>b.INDEX_NAME;

使用pt-duplicate-key-checker 工具检查重复及冗余索引

     pt-duplicate-key-checker --user=root --host=127.0.0.1 --password=123456 --database=test

索引的维护及优化 ---删除不用索引

目前Mysql中还没有记录索引的使用情况，但是在PerconMysql和mariaDB中可以通过INDEX_STATISTICS表来查看哪些索引未使用，但在mysql中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况的分析

    pt-index-usage \
    -uroot -p'' \
    mysql-slow.log