資料庫調優分享-mysql

資料庫調優分享------參考一本mysql資料書

日常的困擾

**反映查詢某個資料越來越慢，怎麼辦？

資料庫監控頁面顯示某個操作頻繁執行，每次執行都非常耗時，怎麼辦？

1.sql優化

2.合理使用索引

一、sql優化—常用方法

1）not in 子查詢優化

2）模糊比對 like ‘%abc%’

3）limit分頁優化

4）count(*)統計資料如何加快速度

5）or條件如何優化

6）用where子句代替having子句

1、not in 子查詢優化

select count(*) from t_cps t1 where t1.flag>=2 and sample_md5 not in (select sample_hash from t_sfa_sample t2 where t2.sample_hash=t1.sample_md5);

-1052661行記錄

33.530s

select count(*) from t_cps t1 where t1.flag>=2 and not exists(select sample_hash from t_sfa_sample t2 where t2.sample_hash=t1.sample_md5);

18.617s

select count(*) from t_cps t1 left join t_sfa_sample t2 on t1.sample_md5=t2.sample_hash where t1.flag>=2 and t2.sample_hash is null;

14.544s

生産環境中，盡量避免使用子查詢，可用表連接配接join代替。可避免mysql在記憶體中建立臨時表

in和exists

select count(*) from t_cps t1 where t1.flag>=2 and sample_md5 in (select sample_hash from t_sfa_sample t2 where t2.sample_hash=t1.sample_md5);

1227992行記錄

30.762s

select count(*) from t_cps t1 where t1.flag>=2 and exists (select sample_hash from t_sfa_sample t2 where t2.sample_hash=t1.sample_md5);

15.581s

in和exists執行時:

in是先執行子查詢中的查詢，然後再執行主查詢。

exists是先執行主查詢，即外層表的查詢，然後再執行子查詢

exists 和 in 在執行時效率單從執行時間來說差不多，exists要稍微優于in。在使用時一般應該是用exists而不用in

而在not in 和 not exists比較時，not exists的效率要比not in的效率要高。

但也有少數時候并非如此。

建議是盡量多使用exists和not exists，少用in 和not in

2、模糊比對 like ‘%abc%’

mysql> explain select * from update_log_test where msg like 'e%';

+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-----------------------+

1 row in set (0.00 sec)

mysql> explain select * from update_log_test where msg like '%e%';

+----+-------------+-----------------+------+---------------+------+---------+------+------+-------------+

like ‘abc%’可以用到索引，但like‘%abc%’卻不行。

select * from books where name like "mysql%" ;

但是如果換用下面的查詢，傳回的結果一樣，但速度就要快上很多：

select * from books where name>="mysql"and name<"mysqm" ;

補充：explain列的解釋

table 顯示這一行的資料是關于哪張表的

type 這是重要的列，顯示連接配接使用了何種類型。從最好到最差的連接配接類型為const、eq_reg、ref、range、index和all

possible_keys 顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。

key 實際使用的索引。如果為null，則沒有使用索引。很少的情況下，mysql會選擇優化不足的索引。這種情況下，可以在select語句中使用use index（indexname）來強制使用一個索引或者用ignore index（indexname）來強制mysql忽略索引

key_len 使用的索引的長度。在不損失精确性的情況下，長度越短越好

ref 顯示索引的哪一列被使用了，顯示了哪些字段或者常量被用來和 key配合從表中查詢記錄出來。

rows mysql認為必須檢查的用來傳回請求資料的行數

extra 關于mysql如何解析查詢的額外資訊。這裡可以看到的壞的例子是using temporary和using filesort，意思mysql根本不能使用索引，檢索會很慢

type不同連接配接類型的解釋（按照效率高低的順序排序）

const 索引可以是主鍵或惟一索引，因為隻有一行，這個值實際就是常數，因為mysql先讀這個值然後把它當做常數來對待

mysql> explain select * from update_log_20141111 where id=2;

+----+-------------+---------------------+-------+---------------+---------+---------+-------+------+-------+

eq_ref 從前面的表中，對每一個記錄的聯合都從表中讀取一個記錄，它在查詢使用了索引為主鍵或惟一鍵的全部時使用

mysql> explain select * from t1,t2 where t1.id=t2.id;

+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------+

2 rows in set (0.00 sec)

ref 隻有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊字首）時發生。對于之前的表的每一個行聯合，全部記錄都将從表中讀出。這個類型嚴重依賴于根據索引比對的記錄多少—越少越好

mysql> explain select * from update_log_20141110 t1,update_log_test t2 where t1.action=t2.action;

+----+-------------+-------+------+---------------+----------+---------+----------------+------+-------------+

| 1 | simple | t2 | ref | i_action | i_action | 2 | test.t1.action | 1 | null |

range 傳回一個範圍中的行，比如使用>或<查找東西時

mysql> explain select * from t1 where id>=9900 order by id limit 10;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

index 對前面的表中的每一個記錄聯合進行完全掃描（比all更好，因為索引一般小于表資料）

all 對每一個記錄聯合進行完全掃描，這一般比較糟糕，應該盡量避免

extra 列傳回的描述的意義

distinct 一旦mysql找到了與行相聯合比對的行，就不再搜尋了

not exists mysql優化了left join，一旦它找到了比對left join标準的行，就不再搜尋

record（index map:#）沒有找到理想的索引，是以對于從前面表中來的每一個行組合，mysql檢查使用哪個索引，并用它來從表中傳回行。這是使用索引的最慢的連接配接之一

using filesort 看到這個的時候，查詢就需要優化了。mysql需要進行額外的步驟來發現如何對傳回的行排序。它根據連接配接類型以及存儲排序鍵值和比對條件的全部行的行指針來排序全部行

using index 列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表傳回的，這發生在對表的全部的請求列都是同一個索引的部分的時候

using temporary 看到這個的時候，查詢需要優化了。這裡，mysql需要建立一個臨時表來存儲結果，這通常發生在對不同的列集進行order by上，而不是group by上

using where 使用了where從句來限制哪些行将與下一張表比對或者是傳回給使用者。如果不想傳回表中的全部行，并且連接配接類型all或index，這就會發生，或者是查詢有問題

3、limit分頁優化

select * from t1 order by id limit 9900,10;

上面的語句，雖然用到了id索引，但是從第一行開始起定位至9900行，然後再掃描後10行，相當于進行了一次全掃描，顯然效率不高。

select * from t1 where id>=9900 order by id limit 10;

利用id索引直接定位到9900行，然後在掃描出後10行，相當于一個range範圍掃描

mysql> explain select * from t1 order by id limit 9900,10;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

4、count(*)統計資料如何加快速度

select count(*) from update_log;

select count(*) from update_log where sid>=0; 利用輔助索引

select count(distinct k) from t;

select count(*) from (select distinct k from t) tmp; 利用索引來做排重操作。

注意：innodb count（*）必須全表掃，而不像myisam那樣有一個計數器，直接從中取出資料。innodb必須要全表掃一次才能得到count，而且會鎖表。

5、or條件如何優化

select * from user where name='d' or age=31;

name和age都建立了索引，但explain發現這個or條件用不到索引。

改成union all結果集合并

select * from user where name='d' union all select * from user where age=31;

6、用where子句代替having子句

select * from user group by id having id>40 limit 3;

select * from user where id>40 group by id limit 3;

having隻會在檢索出所有記錄之後才對結果集進行過濾。這個處理需要排序、總計等操作。

如果能通過where子句限制記錄的數目，那就能減少這方面的開銷。

sql優化的一般步驟

1、通過 show status 指令了解各種sql的執行頻率

show status like 'uptime' 目前mysql運作時間

show status like 'com_select' 目前mysql執行了多少次查詢

show status like 'com_insert' 目前mysql執行了多少次添加

show status like 'com_update' 目前mysql執行了多少次更新

show status like 'com_delete' 目前mysql執行了多少次删除

show status 文法：

show [session|global] status like '';

如果不寫 [session|global] 表示預設是 session 指取出目前視窗的執行情況

如果想看所有(mysql啟動到現在)的情況加上 global

show global status like 'com_insert';

2、定位執行效率較低的sql語句（重點select）

3、通過 explain 分析低效率的sql語句的執行情況

4、确定問題并采取相應的優化措施

二、合理使用索引

使用索引，why?

單列索引和聯合索引

字段使用函數，将不能走索引

當取出的資料量超過表中資料的20%，索引是否有效？

order by 和group by優化

全文索引

1、适當的索引對應用的性能來說至關重要。

2、索引隻對select有加速作用，但對寫入（insert,update、delete）操作會帶來額外的開銷，如果帶有一個或多個索引，那麼資料更新的時候，mysql也要更新各個索引。

3、并不是所有經常查詢的列都适合建立索引，區分度不高的，通常走全表掃浏覽會更快。例如性别，隻有男女兩種，就不适合。

4、一條sql隻能用一個索引，如果有多個，優化器會選擇最優的。

1、單列索引和聯合索引

mysql> create index i_s_time on update_log(server,time);

聯合索引要遵循最左側原則

mysql> explain select * from update_log where server='115.29.138.24' and time='13:00:00';

+----+-------------+------------+------+-----------------+----------+---------+-------------+------+-----------------------+

mysql> explain select * from update_log where server='115.29.138.24';

+----+-------------+------------+------+---------------+----------+---------+-------+---------+-----------------------+

mysql> explain select * from update_log where server='115.29.138.24' and client='14.197.74.21' and time='13:00:00';

+----+-------------+------------+------+-----------------+----------+---------+-------------+------+------------------------------------+

mysql> explain select * from update_log where time='13:00:00';

+----+-------------+------------+------+---------------+------+---------+------+----------+-------------+

2、字段使用函數，将不能走索引 --mysql目前還不支援函數索引

mysql> explain select * from update_log where year(date)<2014;

在where後面的查詢條件字段使用了date()函數，是不會用到索引的。

mysql> explain select * from update_log where date<'2014-01-01';

+----+-------------+------------+-------+---------------+------+---------+------+------+-----------------------+

同樣的情形也會發生在對數值型字段進行計算的時候：

select * from t1 where amount/7<24;

select * from t1 where amount<24*7;

3、無引号導緻全表掃描，無法使用索引

mysql> explain select * from update_log_test where msg=123;

mysql> explain select * from update_log_test where msg='yoyo';

+----+-------------+-----------------+------+---------------+-------+---------+-------+------+-----------------------+

由于msg是varchar類型，是以查詢的時候，必須加‘’

數字當字元類型使用時，也一定要加上。

4、當取出的資料量超過表中資料的20%，優化器認為全表掃更快，不會走索引

mysql> explain select * from update_log where time<'14:00:00';

mysql> explain select * from update_log where time<'14:00:00' and time>'13:00:00';

+----+-------------+------------+-------+---------------+--------+---------+------+---------+-----------------------+

5、order by 和group by優化

mysql> explain select * from update_log where server='115.29.138.24' order by time;

+----+-------------+------------+------+---------------+------+---------+------+----------+-----------------------------+

建立聯合索引

query ok, 0 rows affected (3 min 18.94 sec)

records: 0 duplicates: 0 warnings: 0

+----+-------------+------------+------+---------------+----------+---------+-------+---------+------------------------------------+

1 row in set (0.01 sec)

如果order by 後面有多個字段排序，它們的順序要一緻，如果一個是降序，一個是升序，也會出現using filesort排序。

6、全文索引

全文索引：主要是針對檔案，文本的索引，比如文章(5.6開始innodb也支援）

字段類型：char、varchar、text

全文搜尋通過 match() 函數完成.

mysql> create table articles(

-> id int unsigned auto_increment not null primary key,

-> title varchar(200),

-> body text,

-> fulltext(title,body));

query ok, 0 rows affected (0.05 sec)

mysql> insert into articles values

-> (null,'mysql tutorial', 'dbms stands for database ...'),

-> (null,'how to use mysql efficiently', 'after you went through a ...'),

-> (null,'optimising mysql','in this tutorial we will show ...'),

-> (null,'1001 mysql tricks','1. never run mysqld as root. 2. ...'),

-> (null,'mysql vs. yoursql', 'in the following database comparison ...'),

-> (null,'mysql security', 'when configured properly, mysql ...');

query ok, 6 rows affected (0.01 sec)

records: 6 duplicates: 0 warnings: 0

mysql> explain select * from articles where match(title,body) against('database');

+----+-------------+----------+----------+---------------+-------+---------+------+------+-------------+

但如果某個單詞單詞出現在至少全文的50%的行中，它會被列入停止字。對于大型資料集，使用這個操作最合适不過了----一個自然語言問詢不會從一個1gb 的表每隔一行傳回一次。對于小型資料集，它的用處可能比較小。不是大型的資料量，就不要用他，影響插入速度

7、mysql 5.6支援explain update/delete

mysql> explain update update_log_test set msg='gugu' where id=4;

+----+-------------+-----------------+-------+---------------+---------+---------+-------+------+-------------+

mysql> explain delete from update_log_test where id=4;

8、mysql5.6優化了合并索引

mysql> explain select * from update_log where date='2014-11-12' or time='14:00:00';

+----+-------------+------------+-------------+---------------+-------------+---------+------+----------+---------------------------------------+

mysql> explain select * from update_log where date='2014-11-12' union select * from update_log where time='14:00:00';

+----+--------------+------------+------+---------------+--------+---------+-------+----------+-----------------------+

3 rows in set (0.00 sec)

實際測試2條sql，or的執行時間是union執行時間的兩倍。

1、union all 不一定就比 or及in 快，要結合實際情況分析到底使用哪種情況。

2、對于索引列來最好使用union all，因複雜的查詢【包含運算等】将使or、in放棄索引而全表掃描，除非你能确定or、in會使用索引。

3、對于隻有非索引字段來說你就老老實實的用or 或者in，因為非索引字段本來要全表掃描而union all 隻成倍增加表掃描的次數。

union：對兩個結果集進行并集操作，不包括重複行，同時進行預設規則的排序；union all：對兩個結果集進行并集操作，包括重複行，不進行排序；

union因為要進行重複值掃描，是以效率低。

資料庫調優分享-mysql

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述