我們都知道，服務(wù)器數(shù)據(jù)庫(kù)的開(kāi)發(fā)一般都是通過(guò)java或者是PHP語(yǔ)言來(lái)編程實(shí)現(xiàn)的，而為了提高我們數(shù)據(jù)庫(kù)的運(yùn)行速度和效率，數(shù)據(jù)庫(kù)優(yōu)化也成為了我們每日的工作重點(diǎn)，今天，昌平IT培訓(xùn)就一起來(lái)了解一下mysql服務(wù)器數(shù)據(jù)庫(kù)的優(yōu)化方法。
成都創(chuàng)新互聯(lián)公司服務(wù)項(xiàng)目包括瀘縣網(wǎng)站建設(shè)、瀘縣網(wǎng)站制作、瀘縣網(wǎng)頁(yè)制作以及瀘縣網(wǎng)絡(luò)營(yíng)銷策劃等。多年來(lái)，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢(shì)、行業(yè)經(jīng)驗(yàn)、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機(jī)構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，瀘縣網(wǎng)站推廣取得了明顯的社會(huì)效益與經(jīng)濟(jì)效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到瀘縣省份的部分城市，未來(lái)相信會(huì)繼續(xù)擴(kuò)大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
為什么要了解索引真實(shí)案例案例一：大學(xué)有段時(shí)間學(xué)習(xí)爬蟲(chóng)，爬取了知乎300w用戶答題數(shù)據(jù)，存儲(chǔ)到mysql數(shù)據(jù)中。
那時(shí)不了解索引，一條簡(jiǎn)單的“根據(jù)用戶名搜索全部回答的sql“需要執(zhí)行半分鐘左右，完全滿足不了正常的使用。
案例二：近線上應(yīng)用的數(shù)據(jù)庫(kù)頻頻出現(xiàn)多條慢sql風(fēng)險(xiǎn)提示，而工作以來(lái)，對(duì)數(shù)據(jù)庫(kù)優(yōu)化方面所知甚少。
例如一個(gè)用戶數(shù)據(jù)頁(yè)面需要執(zhí)行很多次數(shù)據(jù)庫(kù)查詢，性能很慢，通過(guò)增加超時(shí)時(shí)間勉強(qiáng)可以訪問(wèn)，但是性能上需要優(yōu)化。
索引的優(yōu)點(diǎn)合適的索引，可以大大減小mysql服務(wù)器掃描的數(shù)據(jù)量，避免內(nèi)存排序和臨時(shí)表，提高應(yīng)用程序的查詢性能。
索引的類型mysql數(shù)據(jù)中有多種索引類型，primarykey，unique，normal，但底層存儲(chǔ)的數(shù)據(jù)結(jié)構(gòu)都是BTREE;有些存儲(chǔ)引擎還提供hash索引，全文索引。
BTREE是常見(jiàn)的優(yōu)化要面對(duì)的索引結(jié)構(gòu)，都是基于BTREE的討論。
B-TREE查詢數(shù)據(jù)簡(jiǎn)單暴力的方式是遍歷所有記錄;如果數(shù)據(jù)不重復(fù)，就可以通過(guò)組織成一顆排序二叉樹(shù)，通過(guò)二分查找算法來(lái)查詢，大大提高查詢性能。
而B(niǎo)TREE是一種更強(qiáng)大的排序樹(shù)，支持多個(gè)分支，高度更低，數(shù)據(jù)的插入、刪除、更新更快。
現(xiàn)代數(shù)據(jù)庫(kù)的索引文件和文件系統(tǒng)的文件塊都被組織成BTREE。
btree的每個(gè)節(jié)點(diǎn)都包含有key，data和只想子節(jié)點(diǎn)指針。
btree有度的概念d=1。
假設(shè)btree的度為d，則每個(gè)內(nèi)部節(jié)點(diǎn)可以有n=[d+1，2d+1)個(gè)key，n+1個(gè)子節(jié)點(diǎn)指針。
樹(shù)的大高度為h=Logb[(N+1)/2]。
索引和文件系統(tǒng)中，B-TREE的節(jié)點(diǎn)常設(shè)計(jì)成接近一個(gè)內(nèi)存頁(yè)大小(也是磁盤(pán)扇區(qū)大小)，且樹(shù)的度非常大。
這樣磁盤(pán)I/O的次數(shù)，就等于樹(shù)的高度h。
假設(shè)b=100，一百萬(wàn)個(gè)節(jié)點(diǎn)的樹(shù)，h將只有3層。
即，只有3次磁盤(pán)I/O就可以查找完畢，性能非常高。
索引查詢建立索引后，合適的查詢語(yǔ)句才能大發(fā)揮索引的優(yōu)勢(shì)。
另外，由于查詢優(yōu)化器可以解析客戶端的sql語(yǔ)句，會(huì)調(diào)整sql的查詢語(yǔ)句的條件順序去匹配合適的索引。
在應(yīng)用開(kāi)發(fā)的早期，數(shù)據(jù)量少，開(kāi)發(fā)人員開(kāi)發(fā)功能時(shí)更重視功能上的實(shí)現(xiàn)，隨著生產(chǎn)數(shù)據(jù)的增長(zhǎng)，很多SQL語(yǔ)句開(kāi)始暴露出性能問(wèn)題，對(duì)生產(chǎn)的影響也越來(lái)越大，有時(shí)可能這些有問(wèn)題的SQL就是整個(gè)系統(tǒng)性能的瓶頸。
1、通過(guò)慢查日志等定位那些執(zhí)行效率較低的SQL語(yǔ)句
2、explain 分析SQL的執(zhí)行計(jì)劃
type由上至下，效率越來(lái)越高
Extra
3、show profile 分析
了解SQL執(zhí)行的線程的狀態(tài)及消耗的時(shí)間。默認(rèn)是關(guān)閉的，開(kāi)啟語(yǔ)句“set profiling = 1;”
4、trace
trace分析優(yōu)化器如何選擇執(zhí)行計(jì)劃，通過(guò)trace文件能夠進(jìn)一步了解為什么優(yōu)惠券選擇A執(zhí)行計(jì)劃而不選擇B執(zhí)行計(jì)劃。
5、確定問(wèn)題并采用相應(yīng)的措施
案例1、最左匹配
索引
SQL語(yǔ)句
查詢匹配從左往右匹配，要使用order_no走索引，必須查詢條件攜帶shop_id或者索引( shop_id , order_no )調(diào)換前后順序
案例2、隱式轉(zhuǎn)換
索引
SQL語(yǔ)句
隱式轉(zhuǎn)換相當(dāng)于在索引上做運(yùn)算，會(huì)讓索引失效。mobile是字符類型，使用了數(shù)字，應(yīng)該使用字符串匹配，否則MySQL會(huì)用到隱式替換，導(dǎo)致索引失效。
案例3、大分頁(yè)
索引
SQL語(yǔ)句
對(duì)于大分頁(yè)的場(chǎng)景，可以優(yōu)先讓產(chǎn)品優(yōu)化需求，如果沒(méi)有優(yōu)化的，有如下兩種優(yōu)化方式， 一種是把上一次的最后一條數(shù)據(jù)，也即上面的c傳過(guò)來(lái)，然后做“c xxx”處理，但是這種一般需要改接口協(xié)議，并不一定可行。另一種是采用延遲關(guān)聯(lián)的方式進(jìn)行處理，減少SQL回表，但是要記得索引需要完全覆蓋才有效果，SQL改動(dòng)如下
案例4、in + order by
索引
SQL語(yǔ)句
in查詢?cè)贛ySQL底層是通過(guò)n*m的方式去搜索，類似union，但是效率比union高。in查詢?cè)谶M(jìn)行cost代價(jià)計(jì)算時(shí)（代價(jià) = 元組數(shù) * IO平均值），是通過(guò)將in包含的數(shù)值，一條條去查詢獲取元組數(shù)的，因此這個(gè)計(jì)算過(guò)程會(huì)比較的慢，所以MySQL設(shè)置了個(gè)臨界值(eq_range_index_pe_limit)，5.6之后超過(guò)這個(gè)臨界值后該列的cost就不參與計(jì)算了。因此會(huì)導(dǎo)致執(zhí)行計(jì)劃選擇不準(zhǔn)確。默認(rèn)是200，即in條件超過(guò)了200個(gè)數(shù)據(jù)，會(huì)導(dǎo)致in的代價(jià)計(jì)算存在問(wèn)題，可能會(huì)導(dǎo)致Mysql選擇的索引不準(zhǔn)確。
處理方式，可以( order_status , created_at )互換前后順序，并且調(diào)整SQL為延遲關(guān)聯(lián)。
案例5、范圍查詢阻斷，后續(xù)字段不能走索引
索引
SQL語(yǔ)句
范圍查詢還有“IN、between”
案例6、不等于、不包含不能用到索引的快速搜索。（可以用到ICP）
在索引上，避免使用NOT、!=、、!、NOT EXISTS、NOT IN、NOT LIKE等
案例7、優(yōu)化器選擇不使用索引的情況
如果要求訪問(wèn)的數(shù)據(jù)量很小，則優(yōu)化器還是會(huì)選擇輔助索引，但是當(dāng)訪問(wèn)的數(shù)據(jù)占整個(gè)表中數(shù)據(jù)的蠻大一部分時(shí)（一般是20%左右），優(yōu)化器會(huì)選擇通過(guò)聚集索引來(lái)查找數(shù)據(jù)。
查詢出所有未支付的訂單，一般這種訂單是很少的，即使建了索引，也沒(méi)法使用索引。
案例8、復(fù)雜查詢
如果是統(tǒng)計(jì)某些數(shù)據(jù)，可能改用數(shù)倉(cāng)進(jìn)行解決；如果是業(yè)務(wù)上就有那么復(fù)雜的查詢，可能就不建議繼續(xù)走SQL了，而是采用其他的方式進(jìn)行解決，比如使用ES等進(jìn)行解決。
案例9、asc和desc混用
desc 和asc混用時(shí)會(huì)導(dǎo)致索引失效
案例10、大數(shù)據(jù)
對(duì)于推送業(yè)務(wù)的數(shù)據(jù)存儲(chǔ)，可能數(shù)據(jù)量會(huì)很大，如果在方案的選擇上，最終選擇存儲(chǔ)在MySQL上，并且做7天等有效期的保存。那么需要注意，頻繁的清理數(shù)據(jù)，會(huì)照成數(shù)據(jù)碎片，需要聯(lián)系DBA進(jìn)行數(shù)據(jù)碎片處理。
SQL優(yōu)化一： sql優(yōu)化（一）
上片文章已經(jīng)詳細(xì)介紹了explain各個(gè)字段的含義，以及什么情況應(yīng)該建立索引，什么情況不需要建立索引以及sql語(yǔ)句性能的判斷依據(jù)，接下來(lái)我介紹下如何合理的建立索引。
sql語(yǔ)句：select id，author_id from article where category_id = 1 and comments1 order by views desc limit 1;
分析：首先我們根據(jù)where后面的條件建立符合索引，然后根據(jù)order by后面的字段建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）數(shù)據(jù)列建立復(fù)合索引，但由于comments是一個(gè)范圍，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id則再排序comments，如果遇到相同的comments則再排序views，又因?yàn)閏omments字段在復(fù)合索引里處于中間位置，而comments1是一個(gè)條件（是一個(gè)范圍值），在復(fù)合索引的一個(gè)范圍值的數(shù)據(jù)列后面的索引全部失效，mysql無(wú)法利用索引再對(duì)后面的views部分進(jìn)行檢索，也就是說(shuō)views無(wú)法按照索引排序，所以explain下此sql語(yǔ)句，type為range，extra使用的是Using filesort，這是比較糟糕的。所以我們放棄comments這個(gè)范圍字段，建立索引idx_article_cv，即以（category_id，views）數(shù)據(jù)列建立復(fù)合索引，explain 此sql，type變成了ref，extra的using?filesort也變成了using index，這就變得好多了。
索引：idx_article_cv，即以（category_id，views）數(shù)據(jù)列建立復(fù)合索引
前段時(shí)間做了一個(gè)銷售精細(xì)化項(xiàng)目，是公司crm項(xiàng)目的一個(gè)大模塊，大致就是為銷售人員制定指標(biāo)，實(shí)現(xiàn)銷售目標(biāo)從區(qū)域到團(tuán)到業(yè)務(wù)員到客戶，實(shí)時(shí)跟蹤業(yè)務(wù)員所負(fù)責(zé)客戶的下單量的情況。這就存在許多關(guān)聯(lián)關(guān)系，區(qū)域-團(tuán)，團(tuán)-業(yè)務(wù)員，業(yè)務(wù)員-客戶，這使得sql常常需要關(guān)聯(lián)多張表。
sql語(yǔ)句：SELECT
tu.fuserid,
tu.faccount,
tu.fphone,
tu.fcertificationtype,
tu.fcertificatename,
tu.fkeyarea,
tu.fkeyareatext,
DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,
tag.forggroupid,
tag.forggroupname,
tug.forguserid,
tug.fusername,
tug.fuserphone,
tag.fcitycode
FROM t_finedt_user AS tu
LEFT JOIN t_finedt_customer_relation AS tcr
ON tu.fuserid = tcr.fuserid
LEFT JOIN t_finedt_usergroup AS tug
ON tcr.forguserid = tug.forguserid
and tcr.forggroupid = tug.forggroupid
LEFT JOIN t_finedt_areagroup AS tag
ON tug.forggroupid = tag.forggroupid
where tu.fkeyarea=? and?tu.fuserid=? and tug.forggroupid = ?
分析：上面的sql是左連接，左邊的表一定是全表查詢，所以要建立右邊表對(duì)應(yīng)關(guān)聯(lián)字段的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)字段建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）字段建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)字段建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)字段建立索引。建立索引后，sql查詢速度明顯快了很多
索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid
1、盡可能減少join語(yǔ)句中的NestedLoop的循環(huán)次數(shù)，永遠(yuǎn)用小結(jié)果集驅(qū)動(dòng)大結(jié)果集
2、優(yōu)先優(yōu)化NestedLoop的內(nèi)層循環(huán)
3、保證join語(yǔ)句總被驅(qū)動(dòng)表上的join字段已經(jīng)被索引
4、當(dāng)無(wú)法保證被驅(qū)動(dòng)表join條件字段被索引，且內(nèi)存資源充足的前提下，不要太吝嗇joinBuffer的設(shè)置
1、全值匹配我最愛(ài)
2、最佳左前綴原則——如果索引了多列，要遵守最左前綴原則，指的是查詢從索引的最左前列開(kāi)始并且不跳過(guò)索引中的列
3、并在索引列上做任何操作（計(jì)算、函數(shù)、自動(dòng)or手動(dòng)類型轉(zhuǎn)換），這些會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
4、存儲(chǔ)引擎不能使用索引中范圍條件右邊的列，范圍之后的索引全失效
5、盡量使用覆蓋索引（之訪問(wèn)索引的查詢（索引列和查詢的列一致）），減少select *
6、mysql在使用不等于（！=、、）的時(shí)候無(wú)法使用索引會(huì)導(dǎo)致全表掃描。
7、is null、is not null也無(wú)法使用索引。
8、like以通配符開(kāi)頭（"%abc.."），mysql索引失效也會(huì)變成全表掃描的操作。
9、字符串不加單引號(hào)也會(huì)引起索引失效
10、少用or，用它來(lái)連接時(shí)會(huì)索引失效。
1、對(duì)于單值索引，盡量選擇針對(duì)當(dāng)前query過(guò)濾性更好的索引
2、在選擇組合索引的時(shí)候，當(dāng)前query中過(guò)濾性最好的字段在索引字段順序中，位置越靠前越好
3、在選擇組合索引的時(shí)候，盡量選擇盡可能包含當(dāng)前query中的where字句中更多字段的索引
4、盡可能通過(guò)分析統(tǒng)計(jì)信息和調(diào)整query的寫(xiě)法來(lái)達(dá)到選擇合適索引的目的。
全值匹配我最愛(ài)，最左前綴要遵守
帶頭大哥不能死，中間兄弟不能斷
索引列上少計(jì)算，范圍之后全失效
like百分寫(xiě)最右，覆蓋索引不寫(xiě)里
不等空值還有or，索引失效要少用
var引號(hào)不可丟，sql高級(jí)也不難
重慶分公司
重慶分公司
