SQL優(yōu)化
通過show status命令了解各種sql的執(zhí)行效率
結(jié)果
Com_select:執(zhí)行select操作的次數(shù)裂问，依次查詢之累加1
Com_insert:執(zhí)行insert操作的次數(shù)恩伺，對于批量插入的insert操作，只累加依次
Com_update:執(zhí)行update操作的此時
Com_delete:執(zhí)行delete的次數(shù)
上面的參數(shù)是對所有存儲引擎的表進行累計主之，下面參數(shù)是針對InnoDB存儲引擎的，累加的算法也是略有不同的
Innodb_rows_read:SELECT查詢返回的行數(shù)
Innodb_rows_insered:執(zhí)行inser操作插入的行數(shù)
Innodb_rows_updated:執(zhí)行UPDATE操作更新的行數(shù)
Innodb_rows_deleted執(zhí)行DELETE操作刪除的行數(shù)
通過上述的參數(shù)可以了解當(dāng)前數(shù)據(jù)庫的應(yīng)用是插入更新為主還是查詢操作為主众旗，以及各類的SQL的執(zhí)行比例是多少埠戳。對于更新操作的計算，是對執(zhí)行次數(shù)的計數(shù)瘟判，無論提交還是回滾都會進行累加對于事務(wù)形的應(yīng)用怨绣，通過Com_commit和Com_rollback可以了解事務(wù)提交和回滾的情況，對于回滾操作非常頻繁的數(shù)據(jù)庫拷获，可能意味著應(yīng)用編寫存在的問題
Connections:試圖連接MySql服務(wù)器的次數(shù)
Uptime：服務(wù)器工作時間
Slow_queries:慢查詢的次數(shù)
定位執(zhí)行效率低的SQL語句
通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的sql語句篮撑，用--log-show-queries[=file_name]選項去啟動，mysqlId寫一個包含所有執(zhí)行時間超過long_querty_time秒的sql語句的日志文件
慢查詢?nèi)罩驹诓樵兘Y(jié)束后才記錄刀诬，所以在應(yīng)用反應(yīng)執(zhí)行效率出現(xiàn)問題的時候查詢慢查詢?nèi)罩静⒉荒芏ㄎ粏栴}咽扇，可以使用show processlist命令查看當(dāng)前Mysql在進行的線程邪财，包括線程的狀態(tài)陕壹，是否鎖表等，可以實時查看sql的執(zhí)行情況树埠，同時對一些鎖表進行優(yōu)化
通過explain分析執(zhí)行sql的執(zhí)行計劃
explain或者desc獲取mysql如何執(zhí)行select語句的信息
每個列說明
select_type：表示SELECT的類型糠馆，常見的取值有simple（簡單表，即不用表連接或者子查詢）怎憋，primary（主查詢又碌，即外部查詢），union（union中的第二個或者后面的查詢語句）绊袋，subquery（子查詢中的第一個select）等
table:輸出結(jié)果集
type：表示Mysql在表中找到所需行的方式毕匀，或者叫訪問類型，常見類型如：all,index,range,ref,eq_ref,const,system,null,從做到右癌别，性能由差到好
type=all皂岔，全表掃描，mysql遍歷全表來找到匹配的行
explain?select?*?from?film?where?rating>9
type=index,索引全掃描展姐，MySQL遍歷整個索引來查詢匹配的行
explain?select?title?from?film
type=range,索引范圍掃描躁垛，常見<,<=,>,>=,between
explain?select?*?from?payment?where?customer_id>=300?and?customer_id<=350
type=ref剖毯，使用費索引掃描或唯一索引的前綴掃描
explain?select?*?from?payment?where?customer_id=350
type=eq_ref,類似ref，區(qū)別在于使用的索引是唯一索引教馆，對于每個索引鍵值逊谋，表中有一條記錄匹配；簡單來說就是多表連接使用primary?key或者unique?index作為關(guān)聯(lián)條件
explain?select?*?from?film a,film_text b?where?a.film_id=b.film_id
type=const/system,單表中最多有一個匹配行土铺，查詢起來非常迅速胶滋，索引這個匹配行中的其他列的值可以被優(yōu)化器在當(dāng)前查詢中當(dāng)做常量來處理,例如根據(jù)主鍵primary?key或者唯一一個索引來查詢
type?null,mysql不用訪問數(shù)據(jù)庫直接得到結(jié)果
explain?select?1?from?dual?where?1
mysql 4.1引入了explain extended命令，通過explain extended 加上show warnings可以查看mysql 真正被執(zhí)行之前優(yōu)化器所做的操作
explain?select?*?from?users;
show?warnings;
可以從warning的字段中能夠看到悲敷，會去除一些恒成立的條件镀钓，可以利用explain?extended的結(jié)果來迅速的獲取一個更清晰易讀的sql語句
通過show profile 分析sql
通過profile，我們能夠更清楚的了解sql執(zhí)行的過程镀迂。例如我們知道丁溅，MyISAM表有表元數(shù)據(jù)的緩存（例如行，即COUNT()值),對于MyISAM表的COUNT()是不需要消耗太多資源探遵，而對于Innodb來說窟赏，就沒有這種元數(shù)據(jù)，CONUT（*）執(zhí)行的比較慢
select?count(*)?from?users;
執(zhí)行完畢查看
show?profiles
可以查看之前的queryid
show?profile?for?query?2箱季； 可以查看執(zhí)行過程中線程的每個狀態(tài)和消耗時間
其中 sendingdata 狀態(tài)表示mysql線程開始訪問數(shù)據(jù)行并把結(jié)果返回給客戶端涯穷，而不僅僅是返回給客戶端，由于在sending?data狀態(tài)下藏雏，mysql線程往往需要做大量的磁盤讀取操作拷况；所以經(jīng)常是整個查詢中最耗時的狀態(tài)
mysql 支持進一步選擇all,cpu,block io,context,switch,page faults等明細來查看mysql在使用什么資源上耗費了過高的時間，例如掘殴，選擇查看cpu的耗費時間
show?profile cpu?for?query?6;
對比MyISAM的操作赚瘦，同樣執(zhí)行count()操作，檢查profile奏寨，Innodb表經(jīng)歷了Sending data狀態(tài)起意，而MyISAM的表完全不需要訪問數(shù)據(jù)
*如果對Mysql 源碼感興趣**，可以通過show profile source for query查看sql解析執(zhí)行過程的每個步驟對應(yīng)的源碼文件
show?profile?source?for?query?6
通過trace分析優(yōu)化器如何
MySql 5.6提供對sql的跟蹤trace病瞳，通過trace文件能夠進一步了解為什么優(yōu)化器選擇A執(zhí)行計劃而不選擇B執(zhí)行計劃揽咕，幫助我們更好地了解優(yōu)化器的行為
索引問題
mysql提供四種索引
B-Tree索引:最常見的的索引，大部分引擎支持B樹索引
HASH索引:只有Memory引擎支持套菜，使用場景簡單
R-Tree索引:空間索引是MyISAM的一個特殊索引類型亲善，主要用于地理空間數(shù)據(jù)類型，通常使用較少
Full-text：全文索引也是MyISAM的一個特殊索引逗柴，主要用于全文索引蛹头，InnoDb從MySql5.6開始提供支持全文索引
MySql目前不支持函數(shù)索引，但是能對列的前面某一部分進行索引，例如標(biāo)題title字段掘而，可以只取title的前10個字符索引挟冠，這樣的特性大大縮小了索引文件的大小，但前綴索引也有缺點袍睡，在排序order by和分組group by操作的時候無法使用
create?index?idx_title?on?film(title(10));
常用的索引就是B-tree索引和hash索引知染，資只有memory引擎支持HASH索引，hash索引適用于key-value查詢斑胜，通過hash索引比B-tree索引查詢更加迅速控淡，但是hash索引不支持范圍查找例如<><==,>==等操作，如果使用memory引擎并且where不使用=進行 索引列止潘，就不會用的索引掺炭。Memory只有在"="的條件下才會使用索引
簡單的優(yōu)化方法
本語句可以用于分析和存儲表的關(guān)鍵字分布，分析的結(jié)果可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計信息使得sql凭戴，能夠生成正確的執(zhí)行計劃涧狮。如果用戶感覺實際執(zhí)行計劃并不預(yù)期的執(zhí)行計劃，執(zhí)行一次分析表可能會解決問題
analyze?table?payments;
檢查表：檢查表：檢查表的作用是檢查一個表或多個表是否有錯誤,也可以檢查視圖是否錯誤
check?table?payment;
優(yōu)化表:如果刪除了表的一大部分么夫，或者如果已經(jīng)對可變長度的行表（含varchar者冤、blob、text列）的表進行改動档痪，則使用optimize 進行表優(yōu)化涉枫，這個命令可以使表中的空間碎片進行合并、并且可以消除由于刪除或者更新造成的空間浪費
optimize?table?payment;
對于innodb引擎的表腐螟，可以通過設(shè)置innodb_file_per_taable參數(shù)愿汰，設(shè)置InnoDb為獨立表空間模式，這樣每個數(shù)據(jù)庫的每個表都會生成一個獨立的idb文件乐纸，用于存儲表的數(shù)據(jù)和索引衬廷，可以一定程度減少Innodb表的空間回收問題,另外，在刪除大量數(shù)據(jù)后锯仪，Innodb表可以通過alter table但是不銹鋼引擎方式來回收不用的空間
alter?table?payment enigine=innodb;
ANALYZE,CHECK,OPTIMIZE,ALTER TABLE執(zhí)行期間都是對表進行鎖定泵督，因此要在數(shù)據(jù)庫不頻繁的時候執(zhí)行相關(guān)的操作
常用SQL的優(yōu)化
大批量的插入數(shù)據(jù)
當(dāng)用load導(dǎo)入數(shù)據(jù)，適當(dāng)?shù)脑O(shè)置可以提供導(dǎo)入的速度
對于MyISAM存儲引擎的表庶喜，可以通過以下方式快速導(dǎo)入大量的數(shù)據(jù)
disable keys和enable keys 用來打開或者關(guān)閉MyISAM表非索引的更新。在導(dǎo)入大量的數(shù)據(jù)到一個非空的MyISAM表救鲤，通過設(shè)置這兩個命令久窟，可以提高導(dǎo)入的效率
對于Innodb類型的表不能使用上面的方式提高導(dǎo)入效率
因為Innodb類型的表是按照主鍵的順序保存，所有將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排序本缠，可以有效地提高導(dǎo)入數(shù)據(jù)的效率
在導(dǎo)入數(shù)據(jù)強執(zhí)行SET UNIQUE_CHECKS=0斥扛，關(guān)閉唯一性校驗，在導(dǎo)入結(jié)束后執(zhí)行SET UNIQUE_CHECKS=1.恢復(fù)唯一性校驗，可以提高導(dǎo)入的效率稀颁，如果應(yīng)用使用自動提交的方式芬失，建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0時，關(guān)閉自動提交匾灶，導(dǎo)入結(jié)束后再執(zhí)行SET AUTOCOMMIT=1棱烂，打開自動提交，也可以提高導(dǎo)入的效率
優(yōu)化insert語句
如果同時從一個客戶端插入很多行阶女，應(yīng)盡量使用多個值表的insert語句颊糜，這種方式將大大縮減客戶端與數(shù)據(jù)庫之間的連接、關(guān)閉等消耗秃踩，使得效率比分開執(zhí)行的單個insert語句快（大部分情況下衬鱼，使用多個值表的insert語句那比單個insert語句快上好幾倍）。
insert into test values(1,2),(1,3)...
如果從不同客戶插入很多行憔杨，可以通過使用insert delayed語句提高更高的速度鸟赫，delayed的含義是讓insert語句馬上執(zhí)行，其實數(shù)據(jù)都被放到內(nèi)存的隊列中消别，并沒有真正寫入磁盤惯疙，這比每條語句分別插入要快的多；LOW_PRIORITY剛好相反妖啥，在所有其他用戶對表的讀寫完成后才可以進行
將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放（利用建表中的選項）
如果進行批量插入霉颠，可以通過增加bulk_insert_buffer_size變量值的方法來通過速度，但是荆虱，這只能對MyISAM表使用蒿偎。
當(dāng)從一個文本文件裝載一個表時，使用LOAD DATA INFILE怀读。這通常比使用很多INSERT語句塊快20倍
優(yōu)化ORDER BY語句
MySQL有兩種排序方式
第一種通過有序排序索引順序掃描诉位，這種方式在使用explain分析查詢的時候顯示為Using Index，不需要額外的排序菜枷，操作效率較高
explain?select?customer_id?from?customer?order?by?store_id;
第二張通過返回數(shù)據(jù)進行排序苍糠，也就是通常說的Filesort排序，所有不是通過索引直接返回排序結(jié)果的排序豆角Filesort排序啤誊。Filesort并不代表通過磁盤文件進行排序岳瞭，而只是說明進行了一個排序操作，至于排序操作是否進行了磁盤文件或臨時表等蚊锹，則取決于MySql服務(wù)器對排序參數(shù)的設(shè)置和需要排序數(shù)據(jù)的大小
explain?select?*?from?customer?order?by?store_id;
Filesort是通過相應(yīng)的排序算法瞳筏，將取得的數(shù)據(jù)在sort_buffer_size系統(tǒng)變量設(shè)置的內(nèi)存排序區(qū)進行排序，如果內(nèi)存裝載不下牡昆，它會將磁盤上的數(shù)據(jù)進行分塊姚炕，再對各個數(shù)據(jù)塊進行排序，然后將各個塊合并成有序的結(jié)果集。sort_buffer_size設(shè)置的排序區(qū)是每個線程獨占的柱宦，所有同一個時刻些椒，MySql存在多個sort buffer排序區(qū)
優(yōu)化目標(biāo):盡量減少額外的排序，通過索引直接返回有序數(shù)據(jù).where和ordery by 使用相同的索引掸刊，并且order by的順序和索引順序相同免糕，并且order by的字段都是升序或者都是降序。否則肯定需要額外的排序操作痒给，這樣就會出現(xiàn)filesort
優(yōu)化group by 語句
如果查詢包括group by 但用戶想要避免排序結(jié)果的消耗说墨，可以指定group by null
優(yōu)化嵌套查詢
子查詢可以被更有效率的連接替代
explain?select?*?from?customer?where?customer_id?not?in(select?customer_id?frompayment)
explain?select?*?from?customer a?left?join?payment b?ona.customer_id=b.customer_id?where?b.customer?id?is?null
連接之所用更有效率是因為mysql不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯上需要兩個步驟的查詢工作
優(yōu)化分頁查詢
一般分頁查詢，通過創(chuàng)建覆蓋索引能夠比較好地提高性能苍柏。一個場景是"limit 1000,20",此時Mysql排序出前1020條數(shù)據(jù)后僅僅需要返回第1001到1020條記錄尼斧，前1000條數(shù)據(jù)都被拋棄，查詢和排序代價非常高
優(yōu)化方式：可以增加一個字段last_page_record.記錄上一頁和最后一頁的編號试吁，通過
explain select ...where?last_page_record<... desc limt ..
如果排序字段出現(xiàn)大量重復(fù)字段棺棵，不適用這種方式進行優(yōu)化
MySql常用技巧
正則表達式的使用
使用
select 'abcdefg' regexp '^a';
.....
如果range()提取隨機行
隨機抽取某些行
select?*?from?categrory?order?by?rand()?limit?5;
利用group by的with rollup 子句
使用Group By的with rollup可以檢索更多分組聚合信息
select?date_from(payment_date,'%Y-%M'),staff_id,sum(amount)?from?payment?groupby?date_formate(payment_date,'%Y-%M'),staff_id;
用BIT GROUP FUNCTIONS做統(tǒng)計
使用GROUP BY語句和BIT_AND、BIT_OR函數(shù)完成統(tǒng)計工作熄捍，這兩個函數(shù)的一般用途就是做數(shù)值之間的邏輯
以上先暫時寫到這 后續(xù)抽空再補上 謝謝大家的關(guān)注與支持
