MySQL優(yōu)化

數(shù)據(jù)準(zhǔn)備：https://dev.mysql.com/doc/index-other.html 上的sakila數(shù)據(jù)庫

數(shù)據(jù)庫版本：MySQL 5.5.19

視頻教程

1. SQL語句優(yōu)化

1.1 開啟慢查詢?nèi)罩?/h3>

show variables like 'slow_query_log'; -- 查看是否開啟慢查詢?nèi)罩?
set global log_queries_not_using_indexes = on; -- 設(shè)置慢查詢?nèi)罩景ㄎ丛O(shè)置索引的sql查詢

set global long_query_time = 1; -- 設(shè)置慢查詢?nèi)罩緯r間為1s

set global slow_query_log = on; -- 開啟慢查詢?nèi)罩?
show variables like 'slow%'; -- 查看慢查詢?nèi)罩敬鎯ξ恢?

MySQL慢查詢?nèi)罩靖袷?/div>

慢查詢?nèi)罩痉治龉ぞ撸簃ysqldumpslow茬祷、pt-query-digest

慢查詢?nèi)罩局邪l(fā)現(xiàn)有問題的sql：

1. 查詢次數(shù)多晃琳，且每次查詢占用時間長的sql。通常為pt-query-digest分析的前幾個查詢。
2. IO比較大的查詢剑鞍。注意pt-query-digest分析中的Rows examine項翔冀。
3. 未命中索引的sql摊阀。注意pt-query-digest分析中Rows examine與Rows send的對比人断。

1.2 explain查詢分析SQL的執(zhí)行計劃

explain extended SELECT …：運行SHOW WARNINGS 可得到被MySQL優(yōu)化器優(yōu)化后的查詢語句豆同。

執(zhí)行計劃包含的信息

1. id：表示查詢中執(zhí)行select子句或操作表的順序

   執(zhí)行計劃id

   id相同，可以認為是一組含鳞，從上往下順序執(zhí)行影锈；在所有組中，id值越大蝉绷，優(yōu)先級越高鸭廷，越先執(zhí)行。

2. select_type：表示查詢中每個select子句的類型

   • simple：查詢中不包含子查詢或者UNION
   • primary：包含union操作或者子查詢的select熔吗，最外層查詢則被標(biāo)記為PRIMARY
   • union：若第二個SELECT出現(xiàn)在UNION之后辆床，則被標(biāo)記為UNION
   • derived：from列表中包含的子查詢被標(biāo)記為DERIVED
   • subquery：除了from列表中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是subquery桅狠，SELECT或WHERE列表中包含了子查詢讼载，該子查詢被標(biāo)記為：SUBQUERY

   • union result：包含union的結(jié)果集，因為它不需要參與查詢中跌，所以id字段為null

     
     
     union result

3. table：顯示的查詢表名

   • 如果查詢使用了別名咨堤，那么這里顯示的是別名
   • 如果不涉及對數(shù)據(jù)表的操作，那么這顯示為null
   • 如果顯示為尖括號括起來的<derived N>漩符，就表示這個是臨時表一喘，后邊的N就是執(zhí)行計劃中的id，表示結(jié)果來自于這個查詢產(chǎn)生
   • 如果是尖括號括起來的<union M,N>嗜暴，與<derived N>類似凸克，也是一個臨時表，表示這個結(jié)果來自于union查詢的id為M,N的結(jié)果集

4. partitions：這列是建立在表是分區(qū)表上

5. type：查詢結(jié)果類型

   type優(yōu)劣順序

   • NULL：MySQL在優(yōu)化過程中分解語句闷沥，執(zhí)行時甚至不用訪問表或索引

     
     
     NULL實例

   • const：使用唯一索引或者主鍵萎战，返回記錄一定是1行記錄的等值where條件時，通常type是const舆逃。system是const類型的特例蚂维，當(dāng)查詢的表只有一行的情況下，使用system

     
     
     const實例

     # 單一主鍵
     SELECT * FROM tbl_name WHERE primary_key=1;
     # 聯(lián)合主鍵
     SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
     

   • eq-ref：出現(xiàn)在要連接表的查詢計劃中颖侄，驅(qū)動表只返回一行數(shù)據(jù)鸟雏，且這行數(shù)據(jù)是第二個表的主鍵或者唯一索引，且必須為not null览祖，唯一索引和主鍵如果是多列時，只有所有的列都用作比較時才會出現(xiàn)eq_ref炊琉。觸發(fā)條件：只匹配到一行的時候展蒂。

     
     
     eq-ref實例

     # 多表關(guān)聯(lián)查詢又活，單行匹配
     SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column=other_table.column;
     
     # 多表關(guān)聯(lián)查詢，聯(lián)合索引锰悼，多行匹配
     SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column_part1=other_table.column
     AND ref_table.key_column_part2=1;
     

   • ref：非唯一性索引掃描柳骄，返回匹配某個單獨值的所有行。常見于唯一索引的非唯一前綴進行的查找箕般。與eq_ref不同的是匹配到了多行耐薯。

     
     
     ref實例

     # 根據(jù)索引（非主鍵，非唯一索引）丝里，匹配到多行
     SELECT * FROM ref_table WHERE key_column=expr;
     
     # 多表關(guān)聯(lián)查詢曲初，單個索引，多行匹配
     SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column=other_table.column;
     
     # 多表關(guān)聯(lián)查詢杯聚，聯(lián)合索引臼婆，多行匹配
     SELECT * FROM ref_table,other_table
     WHERE ref_table.key_column_part1=other_table.column
     AND ref_table.key_column_part2=1;
     

   • ref_or_null：類似于ref，但是可以搜索包含null值的行幌绍，實際用的不多

     
     
     ref_or_null

   • index_merge：出現(xiàn)在使用一張表中的多個索引時颁褂，mysql會將這多個索引合并到一起。官方排序這個在ref_or_null之后傀广，實際上由于要讀取所個索引颁独，性能可能大部分時間都不如range

     
     
     index_merge

   • range：索引范圍掃描，常見于使用>, <, is null, between, in, like等運算符的查詢中

     
     
     range實例

     # 范圍查找
     SELECT * FROM tbl_name
     WHERE key_column BETWEEN 10 and 20;
     
     # 范圍查找
     SELECT * FROM tbl_name
     WHERE key_column IN (10,20,30);
     
     # 多條件加范圍查找
     SELECT * FROM tbl_name
     WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
     

   • index：Full Index Scan伪冰，index與ALL區(qū)別為index類型只遍歷索引樹奖唯。常見于使用索引列就可以處理不需要讀取數(shù)據(jù)文件的查詢

     
     
     index

     index類型和ALL類型一樣，區(qū)別就是index類型是掃描的索引樹糜值。以下兩種情況會觸發(fā)：

     1. 如果索引是查詢的覆蓋索引丰捷，就是說索引查詢的數(shù)據(jù)可以滿足查詢中所需的所有數(shù)據(jù)，則只掃描索引樹寂汇，不需要回表查詢病往。 在這種情況下，explain 的 Extra 列的結(jié)果是 Using index骄瓣。僅索引掃描通常比ALL快停巷，因為索引的大小通常小于表數(shù)據(jù)。
     2. 全表掃描會按索引的順序來查找數(shù)據(jù)行榕栏。使用索引不會出現(xiàn)在Extra列中畔勤。

   • ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行扒磁。這個就是全表掃描數(shù)據(jù)文件庆揪，然后在server層進行過濾返回符合要求的記錄。

     
     
     ALL

6. possible_key：查詢可能使用到的索引都會在這里列出來妨托。

7. key：查詢真正使用到的索引缸榛，select_type為index_merge時吝羞，這里可能出現(xiàn)兩個以上的索引，其他的select_type這里只會出現(xiàn)一個内颗。

8. key_len：用于處理查詢的索引長度钧排，如果是單列索引，那就整個索引長度算進去均澳，如果是多列索引恨溜，那么查詢不一定都能使用到所有的列，具體使用到了多少個列的索引找前，這里就會計算進去糟袁，沒有使用到的列，這里不會計算進去纸厉。key_len只計算where條件用到的索引長度系吭，而排序和分組就算用到了索引，也不會計算到key_len中颗品。

9. ref：指出哪些列或常量被用于查找索引列上的值

   • 如果是使用的常數(shù)等值查詢肯尺，這里會顯示const
   • 如果是連接查詢，被驅(qū)動表的執(zhí)行計劃這里會顯示驅(qū)動表的關(guān)聯(lián)字段
   • 如果是條件使用了表達式或者函數(shù)躯枢，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換则吟，這里可能顯示為func

10. rows：這里是執(zhí)行計劃中估算的掃描行數(shù)，不是精確值

11. filtered：這個字段表示存儲引擎返回的數(shù)據(jù)在server層過濾后锄蹂，剩下多少滿足查詢的記錄數(shù)量的比例

12. extra：有幾十種氓仲，常用的有：

    • distinct：在select部分使用了distinc關(guān)鍵字
    • no tables used：不帶from子句的查詢或者from dual查詢
    • using filesort：排序時無法使用到索引時，就會出現(xiàn)這個得糜。常見于order by和group by語句中敬扛。MySQL中無法利用索引完成的排序操作稱為“文件排序”
    • using index：查詢時不需要回表查詢，直接通過索引就可以獲取查詢的數(shù)據(jù)朝抖。即使用了覆蓋索引啥箭。
    • using intersect：表示使用and連接各個索引的條件時，該信息表示從處理結(jié)果獲取交集
    • using union：表示使用or連接各個使用索引的條件時治宣，該信息表示從處理結(jié)果獲取并集
    • using temporary：表示使用了臨時表存儲中間結(jié)果急侥。臨時表可以是內(nèi)存臨時表和磁盤臨時表，執(zhí)行計劃中看不出來侮邀，需要查看status變量坏怪，used_tmp_table，used_tmp_disk_table才能看出來绊茧。常見于排序和分組查詢铝宵。
    • using where：表示存儲引擎返回的記錄并不是所有的都滿足查詢條件，需要在server層進行過濾按傅。

對查詢性能影響最大的幾個列是：

1. select_type：查詢類型
2. type：連接使用了何種類型
3. rows：查詢數(shù)據(jù)需要查詢的行
4. key：查詢真正使用到的索引
5. extra：額外的信息

盡量讓自己的SQL用上索引捉超，避免讓extra里面出現(xiàn)file sort(文件排序),using temporary(使用臨時表)胧卤。

MySQL執(zhí)行計劃的局限：

1. explain不會告訴關(guān)于觸發(fā)器唯绍、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
2. explain不考慮各種Cache
3. explain不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
4. 部分統(tǒng)計信息是估算的拼岳，并非精確值
5. explain只能解釋SELECT操作，其他操作要重寫為SELECT后查看執(zhí)行計劃

1.3 具體優(yōu)化

1. count()况芒、max()優(yōu)化

   max(colA)：在colA上加索引優(yōu)化惜纸。

   count()與count(colA)：count()包含NULL的統(tǒng)計。

   1. 任何情況下select count(*) from table 最優(yōu)選擇
   2. 杜絕select count(colunm) from table

   實踐中發(fā)現(xiàn)：如果數(shù)據(jù)庫沒有主鍵绝骚，count(1) 比count() 快耐版，如果有主鍵，且主鍵作為條件压汪，那么count(1) 比count() 快粪牲。如果表里面只有一個字段那么是count(*)最快。

2. 子查詢優(yōu)化

   通常情況下止剖，把子查詢優(yōu)化成join查詢腺阳。注意一對多時，可能出現(xiàn)數(shù)據(jù)重復(fù)穿香。

3. group by優(yōu)化

   原sql：

   select actor.name, count(*)
   from film_actor inner join actor using(actor_id)
   group by film_actor.actor_id;
   

   優(yōu)化后的sql：連接中使用子查詢

   select actor.name, c.cnt
   from actor inner join
       (select actor_id, count(*) as cnt) from film_actor group by actor_id) as c using(actor_id);
   

4. limit優(yōu)化

   原sql：

   select film_id, description from film order by title limit 50, 5;
   

   優(yōu)化步驟1：使用有索引的列或主鍵進行order by操作

   select film_id, description from film order by film_id limit 50, 5;
   

   上面sql雖然只需要5行數(shù)據(jù)亭引，但會掃描55行。進一步優(yōu)化見下面：

   優(yōu)化步驟2：記錄上次返回的主鍵皮获，在下次查詢時使用主鍵過濾

   select film_id, description from film where film_id > 55 and film_id <= 60
   order by film_id limit 50, 5;
   

   上面sql只掃描5行焙蚓。

5. distinct優(yōu)化

   盡量不要使用distinct，可使用加索引洒宝、group by代替购公，具體情況具體分析。

2. 索引優(yōu)化

1. 建立合適的索引：

   1. 在where雁歌、group by宏浩、order by、on中出現(xiàn)的列加索引将宪，某些時候需要select中的列也加索引绘闷，即使用覆蓋索引。
   2. 索引字段越小越好较坛。以頁為單位存儲印蔗，索引字段小，頁中存儲的數(shù)據(jù)多丑勤，一次IO獲取的數(shù)據(jù)行越大华嘹，效率更高。
   3. 離散度大（可選擇性更高）的列放在聯(lián)合索引的前面。

   索引會加快查詢效率打厘，但是會減小寫入效率瓤湘。

2. 刪除重復(fù)贱鄙、冗余索引：使用工具查找重復(fù)敦姻、冗余索引粒氧。

   pt-duplicate-key-checker \
   -uroot \
   -p '實際的密碼' \
   -h 127.0.0.1
   

3. 刪除不用的索引

3. 數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

1. 選擇數(shù)據(jù)類型只要遵循“小而簡單”的原則就好馍忽，越小的數(shù)據(jù)類型通常會更快侨嘀，占用更少的磁盤型宝、內(nèi)存八匠，處理時需要的CPU周期也更少。越簡單的數(shù)據(jù)類型在計算時需要更少的CPU周期趴酣，比如梨树，整型就比字符操作代價低，因而會使用整型來存儲ip地址岖寞，使用DATETIME或者INT（更好）來存儲時間抡四，而不是使用字符串：

   1. 計劃在列上創(chuàng)建索引，就應(yīng)該將該列設(shè)置為NOT NULL仗谆。（NULL 其實并不是空值指巡，而是要占用空間，所以mysql在進行比較的時候胸私，NULL 會參與字段比較厌处，所以對效率有一部分影響。B樹索引時不會存儲NULL值岁疼，所以如果索引的字段可以為NULL阔涉，索引的效率會下降很多。）
   2. 對整數(shù)類型指定寬度捷绒，比如INT(11)瑰排，沒有任何卵用。INT使用32位（4個字節(jié)）存儲空間暖侨，那么它的表示范圍已經(jīng)確定椭住，所以INT(1)和INT(20)對于存儲和計算是相同的。
   3. UNSIGNED表示不允許負值字逗，大致可以使正數(shù)的上限提高一倍京郑。比如TINYINT存儲范圍是-128 ~ 127，而UNSIGNED TINYINT存儲的范圍卻是0 - 255葫掉。
   4. 通常來講些举，沒有太大的必要使用DECIMAL數(shù)據(jù)類型。即使是在需要存儲財務(wù)數(shù)據(jù)時俭厚，仍然可以使用BIGINT户魏。比如需要精確到萬分之一，那么可以將數(shù)據(jù)乘以一百萬然后使用BIGINT存儲。這樣可以避免浮點數(shù)計算不準(zhǔn)確和DECIMAL精確計算代價高的問題叼丑。
   5. TIMESTAMP使用4個字節(jié)存儲空間关翎，DATETIME使用8個字節(jié)存儲空間。因而鸠信，TIMESTAMP只能表示1970 - 2038年纵寝，比DATETIME表示的范圍小得多，而且TIMESTAMP的值因時區(qū)不同而不同症副。
   6. 大多數(shù)情況下沒有使用枚舉類型的必要店雅，其中一個缺點是枚舉的字符串列表是固定的政基，添加和刪除字符串（枚舉選項）必須使用ALTER TABLE（如果只是在列表末尾追加元素贞铣，不需要重建表）
   7. schema的列不要太多。原因是存儲引擎的API工作時需要在服務(wù)器層和存儲引擎層之間通過行緩沖格式拷貝數(shù)據(jù)沮明，然后在服務(wù)器層將緩沖內(nèi)容解碼成各個列辕坝，這個轉(zhuǎn)換過程的代價是非常高的。如果列太多而實際使用的列又很少的話荐健，有可能會導(dǎo)致CPU占用過高酱畅。
   8. 大表ALTER TABLE非常耗時，MySQL執(zhí)行大部分修改表結(jié)果操作的方法是用新的結(jié)構(gòu)創(chuàng)建一個張空表江场，從舊表中查出所有的數(shù)據(jù)插入新表纺酸，然后再刪除舊表。尤其當(dāng)內(nèi)存不足而表又很大址否，而且還有很大索引的情況下餐蔬，耗時更久。

   總結(jié)下佑附，可以記住下面幾個：

   1. 使用可以存下數(shù)據(jù)的最小的數(shù)據(jù)類型：int存時間樊诺、bigint存ip（INET_ATON、INET_NTOA）
   2. 使用簡單的數(shù)據(jù)類型音同。int要比varchar在mysql處理上簡單
   3. 盡可能使用not null定義字段
   4. 盡量少用text類型词爬，非用不可時最好考慮分表

2. 范式優(yōu)化

   符合第三范式

3. 反范式優(yōu)化

   增加冗余，以空間換時間

4. 垂直拆分

   把一個字段多的表拆分成幾個表（不常用的字段放在一個表中权均，大字段放在一個表中顿膨，經(jīng)常一起用的字段放在一個表中）

5. 水平拆分

4. 系統(tǒng)配置優(yōu)化

1. 操作系統(tǒng)配置優(yōu)化（增加tcp連接數(shù)、修改最大打開文件數(shù)量）
2. MySQL配置文件優(yōu)化（InnoDB緩沖池大羞瓷蕖）

5. 服務(wù)器硬件優(yōu)化