sql及索引優(yōu)化

如何發(fā)現(xiàn)有問題的sql？

使用Mysql的慢查詢?nèi)罩緦?duì)有效率問題的SQL進(jìn)行監(jiān)控

    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;

慢查詢?nèi)罩舅畔?/h5>

#執(zhí)行sql的主機(jī)信息
#User@Host：root[root] @ localhost[]
#sql的執(zhí)行信息
#Query_time: 0.00024 Lock_time: 0.00000 Rows_sent:0 Rows_examined:0
#sql執(zhí)行時(shí)間
SET timestamp = 1402389328；
#sql的內(nèi)容
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

輸出到數(shù)據(jù)庫表

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

如何通過慢日志查日志發(fā)現(xiàn)有問題的sql？

1、查詢次數(shù)多且每次查詢占用時(shí)間長(zhǎng)的sql

通常為pt-query-digest分析的前幾個(gè)查詢

2、IO大的sql

注意pt-query-digest分析中的Rows examine項(xiàng)

3、未命中索引的sql？

注意pt-query-digest分析中Rows examine 和Rows Send 的對(duì)比

如何分析SQL查詢

使用explain查詢sql的執(zhí)行計(jì)劃

explain返回各列的含義

table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
type:這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、index和ALL
possible_keys:顯示可能應(yīng)用在這張表中的索引。如果為空，沒有可能的索引
key: 實(shí)際使用的索引。如果為NULL，則表示沒有使用索引
key_len:使用索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好
ref：顯示索引的哪一列被使用了，如果可以的話，是一個(gè)常數(shù)
rows：MYSQL認(rèn)為必須檢查的用來返回請(qǐng)求數(shù)據(jù)的行數(shù)
extra：列需要注意的返回值
Using filesort: 看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。MYSQL需要進(jìn)行額外的步驟來發(fā)現(xiàn)如何對(duì)返回的行排序。它根據(jù)連接類型以及存儲(chǔ)排序鍵值和匹配條件的全部行的行指針來排序全部行
Using temporary: 看到這個(gè)的時(shí)候，查詢就需要優(yōu)化了。這里，MYSQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來存儲(chǔ)結(jié)果，這通常發(fā)生在對(duì)不同的列集進(jìn)行ORDER BY上，而不是GROUP BY上

count()和max()的優(yōu)化方法

查詢最后支付的時(shí)間--優(yōu)化max()函數(shù)

    select max(payment_date) from payment;

添加索引

    create index idx_paydate on payment(payment_date);

在一條sql中同時(shí)查出2006年和2007年電影的數(shù)量——優(yōu)化count()函數(shù)

錯(cuò)誤的方式：

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

無法分開計(jì)算2006和2007年的電影數(shù)量

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

Release_year不可能同時(shí)為2006和2007，因此上有邏輯錯(cuò)誤

正確的方法：

    SELECT count(release_year = '2006' OR NULL) as '2006年電影數(shù)量',count(release_year='2007' OR NULL) as '2007年電影數(shù)量' from film;

子查詢的優(yōu)化

通常情況下，需要把子查詢優(yōu)化為join查詢，但在優(yōu)化的時(shí)候要主義關(guān)聯(lián)是否有一對(duì)多的關(guān)系，要注意重復(fù)數(shù)據(jù)。

（查詢sandra出演的所有影片）優(yōu)化前

    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' ) );

優(yōu)化后

  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';

優(yōu)化group by查詢優(yōu)化前

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;

優(yōu)化后

 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 );

優(yōu)化Limit查詢

limit常用于分頁處理，時(shí)常會(huì)伴隨order by從句使用，因此大多時(shí)候會(huì)使用Filesorts 這樣會(huì)造成大量IO問題。

    select film_id,description from sakila.film order by title limit 50,5;
    #優(yōu)化步驟1：使用有索引的列或主鍵進(jìn)行order by操作
    select film_id,description from sakila.film order by film_id limit 50;
    #優(yōu)化步驟2：記錄上次返回的主鍵，在下次查詢時(shí)使用主鍵過濾
    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 從句中出現(xiàn)的列

2、索引字段越小越好

3、離散度大的列放到聯(lián)合索引的前面

        select * from payment where staff_id =2 and customer_id = 584;
        #是index(staff_id,customer_id)好？還是index(customer_id,staff_id)好？
        #由于customer_id的離散度更大，所以應(yīng)該使用index(customer_id,staff_id)  

索引的維護(hù)及優(yōu)化----重復(fù)及冗余索引

重復(fù)索引是指相同的列以相同的順序建立的同類型的索引，如下表中primary key 和 ID列上的索引就是重復(fù)索引

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

查詢?nèi)哂嗨饕?/h5>

需要在information_schema表下運(yùn)行

    use information_schema；
    select a.TABLE_SCHEMA AS '數(shù)據(jù)名',
            a.table_name AS '表名',
            a.index_name AS '索引1',
            b.INDEX_NAME AS '索引2',
            a.COLUMN_NAME AS '重復(fù)列名'
        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 工具檢查重復(fù)及冗余索引

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

索引的維護(hù)及優(yōu)化 ---刪除不用索引

目前Mysql中還沒有記錄索引的使用情況，但是在PerconMysql和mariaDB中可以通過INDEX_STATISTICS表來查看哪些索引未使用，但在mysql中目前只能通過慢查日志配合pt-index-usage工具來進(jìn)行索引使用情況的分析

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