《Mysql實(shí)例mysql索引使用與優(yōu)化》要點(diǎn)：
本文介紹了Mysql實(shí)例mysql索引使用與優(yōu)化，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

MYSQL教程本節(jié)內(nèi)容：
mysql數(shù)據(jù)庫的索引使用與優(yōu)化.

MYSQL教程1．索引作用
在索引列上,除了有序查找之外,數(shù)據(jù)庫利用各種各樣的快速定位技術(shù),能夠年夜年夜提高查詢效率.
特別是當(dāng)數(shù)據(jù)量非常年夜,查詢涉及多個(gè)表時(shí),使用索引往往能使查詢速度加快成千上萬倍.

MYSQL教程例如,有3個(gè)未索引的表t1、t2、t3,分別只包括列c1、c2、c3,每個(gè)表分別含有1000行數(shù)據(jù)組成,指為1～1000的數(shù)值,查找對(duì)應(yīng)值相等行的查詢?nèi)缦拢?br>?

MYSQL教程此查詢結(jié)果應(yīng)該為1000行,每行包括3個(gè)相等的值.
在無索引的情況下處理此查詢,必須尋找3個(gè)表所有的組合,以便得出與WHERE子句相配的那些行.
而可能的組合數(shù)目為1000×1000×1000(十億),顯然查詢將會(huì)非常慢.

MYSQL教程如果對(duì)每個(gè)表進(jìn)行索引,就能極大地加速查詢進(jìn)程.利用索引的查詢處理如下.
(1)從表t1中選擇第一行,查看此行所包括的數(shù)據(jù).
(2)使用表t2上的索引,直接定位t2中與t1的值匹配的行.類似,利用表t3上的索引,直接定位t3中與來自t1的值匹配的行.
(3)掃描表t1的下一行并重復(fù)前面的過程,直到遍歷t1中所有的行.
在此情形下,仍然對(duì)表t1執(zhí)行了一個(gè)完全掃描,但能夠在表t2和t3上進(jìn)行索引查找直接取出這些表中的行,比未用索引時(shí)要快一百萬倍.
利用索引,MySQL加速了WHERE子句滿足條件行的搜索,而在多表連接查詢時(shí),在執(zhí)行連接時(shí)加快了與其他表中的行匹配的速度.

MYSQL教程2.? 創(chuàng)立索引
在執(zhí)行CREATE TABLE語句時(shí)可以創(chuàng)立索引,也可以單獨(dú)用CREATE INDEX或ALTER TABLE來為表增加索引.

MYSQL教程1．ALTER TABLE
ALTER TABLE用來創(chuàng)立普通索引、UNIQUE索引或PRIMARY KEY索引.
?

MYSQL教程其中table_name是要增加索引的表名,column_list指出對(duì)哪些列進(jìn)行索引,多列時(shí)各列之間用逗號(hào)分隔.
索引名index_name可選,缺省時(shí),MySQL將根據(jù)第一個(gè)索引列賦一個(gè)名稱.
另外,ALTER TABLE允許在單個(gè)語句中變動(dòng)多個(gè)表,因此可以在同時(shí)創(chuàng)建多個(gè)索引.

MYSQL教程2．CREATE INDEX
CREATE INDEX可對(duì)表增加普通索引或UNIQUE索引.
?

MYSQL教程table_name、index_name和column_list具有與ALTER TABLE語句中相同的含義,索引名弗成選.
另外,不能用CREATE INDEX語句創(chuàng)建PRIMARY KEY索引.

MYSQL教程3．索引類型
在創(chuàng)建索引時(shí),可以規(guī)定索引能否包括重復(fù)值.如果不包括,則索引應(yīng)該創(chuàng)建為PRIMARY KEY或UNIQUE索引.
對(duì)于單列惟一性索引,這保證單列不包括重復(fù)的值.對(duì)于多列惟一性索引,保證多個(gè)值的組合不重復(fù).

MYSQL教程PRIMARY KEY索引和UNIQUE索引非常類似.事實(shí)上,PRIMARY KEY索引僅是一個(gè)具有名稱PRIMARY的UNIQUE索引.
這表示一個(gè)表只能包括一個(gè)PRIMARY KEY,因?yàn)橐粋€(gè)表中不可能具有兩個(gè)同名的索引.

MYSQL教程對(duì)students表在sid上添加PRIMARY KEY索引：
?

MYSQL教程?
4.? 刪除索引
可利用ALTER TABLE或DROP INDEX語句來刪除索引.
類似于CREATE INDEX語句,DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語句處置,語法如下.
?

MYSQL教程此中,前兩條語句是等價(jià)的,刪除掉table_name中的索引index_name.

MYSQL教程第3條語句只在刪除PRIMARY KEY索引時(shí)使用,因?yàn)橐粋€(gè)表只可能有一個(gè)PRIMARY KEY索引,因此不必要指定索引名.如果沒有創(chuàng)建PRIMARY KEY索引,但表具有一個(gè)或多個(gè)UNIQUE索引,則MySQL將刪除第一個(gè)UNIQUE索引.

MYSQL教程如果從表中刪除了某列,則索引會(huì)受到影響.對(duì)于多列組合的索引,如果刪除此中的某列,則該列也會(huì)從索引中刪除.如果刪除組成索引的所有列,則整個(gè)索引將被刪除.

MYSQL教程5．查看索引
?

MYSQL教程說明：
　　· Table
　　表的名稱.
　　· Non_unique
　　如果索引不能包括重復(fù)詞,則為0.如果可以,則為1.
　　· Key_name
　　索引的名稱.
　　· Seq_in_index
　　索引中的列序列號(hào),從1開始.
　　· Column_name
　　列名稱.
　　· Collation
　　列以什么方式存儲(chǔ)在索引中.在MySQL中,有值‘A’(升序)或NULL(無分類).
　　· Cardinality
　　索引中唯一值的數(shù)目的估計(jì)值.通過運(yùn)行ANALYZE TABLE或myisamchk -a可以更新.基數(shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)來計(jì)數(shù),所以即使對(duì)于小型表,該值也沒有必要是精確的.基數(shù)越大,當(dāng)進(jìn)行聯(lián)合時(shí),MySQL使用該索引的機(jī)會(huì)就越大.
　　· Sub_part
　　如果列只是被部分地編入索引,則為被編入索引的字符的數(shù)目.如果整列被編入索引,則為NULL.
　　· Packed
　　指示關(guān)鍵字如何被壓縮.如果沒有被壓縮,則為NULL.
　　· Null
　　如果列含有NULL,則含有YES.如果沒有,則該列含有NO.
　　· Index_type
　　用過的索引辦法(BTREE, FULLTEXT, HASH, RTREE).
　　· Comment

MYSQL教程6．什么情況下使用索引
表的主關(guān)鍵字,自動(dòng)建立唯一索引
如zl_yhjbqk(用戶基本情況)中的hbs_bh(戶標(biāo)識(shí)編號(hào))

MYSQL教程表的字段唯一約束
ORACLE利用索引來保證數(shù)據(jù)的完整性

MYSQL教程如lc_hj(流程環(huán)節(jié))中的lc_bh+hj_sx(流程編號(hào)+環(huán)節(jié)次序)

MYSQL教程直接條件查詢的字段
在SQL中用于條件約束的字段

MYSQL教程如zl_yhjbqk(用戶基本情況)中的qc_bh(區(qū)冊(cè)編號(hào))
?

MYSQL教程查詢中與其它表關(guān)聯(lián)的字段
字段經(jīng)常建立了外鍵關(guān)系
如zl_ydcf(用電成份)中的jldb_bh(計(jì)量點(diǎn)表編號(hào))
?

MYSQL教程查詢中排序的字段
排序的字段如果通過索引去拜訪那將大大提高排序速度
?

MYSQL教程查詢中統(tǒng)計(jì)或分組統(tǒng)計(jì)的字段
?

MYSQL教程2,什么情況下應(yīng)不建或少建索引
1)、表記錄太少
如果一個(gè)表只有5條記錄,采用索引去拜訪記錄的話,那首先需拜訪索引表,再通過索引表拜訪數(shù)據(jù)表,一般索引表與數(shù)據(jù)表不在同一個(gè)數(shù)據(jù)塊,這種情況下ORACLE至少要往返讀取數(shù)據(jù)塊兩次.而不用索引的情況下ORACLE會(huì)將所有的數(shù)據(jù)一次讀出,處理速度顯然會(huì)比用索引快.

MYSQL教程如表zl_sybm(使用部門)一般只有幾條記錄,除了主關(guān)鍵字外對(duì)任何一個(gè)字段建索引都不會(huì)產(chǎn)生性能優(yōu)化,實(shí)際上如果對(duì)這個(gè)表進(jìn)行了統(tǒng)計(jì)分析后ORACLE也不會(huì)用你建的索引,而是自動(dòng)執(zhí)行全表拜訪.如：
?

MYSQL教程2)、經(jīng)常插入、刪除、修改的表
對(duì)一些經(jīng)常處理的業(yè)務(wù)表應(yīng)在查詢?cè)试S的情況下盡量減少索引,如zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等業(yè)務(wù)表.

MYSQL教程3)、數(shù)據(jù)重復(fù)且分布平均的表字段
假如一個(gè)表有10萬行記錄,有一個(gè)字段A只有T和F兩種值,且每個(gè)值的分布概率大約為50%,那么對(duì)這種表A字段建索引一般不會(huì)提高數(shù)據(jù)庫的查詢速度.
經(jīng)常和主字段一塊查詢但主字段索引值比擬多的表字段
如gc_dfss(電費(fèi)實(shí)收)表經(jīng)常按收費(fèi)序號(hào)、戶標(biāo)識(shí)編號(hào)、抄表日期、電費(fèi)發(fā)生年月、操作 標(biāo)志來具體查詢某一筆收款的情況,如果將所有的字段都建在一個(gè)索引里那將會(huì)增加數(shù)據(jù)的修改、插入、刪除時(shí)間,從實(shí)際上分析一筆收款如果按收費(fèi)序號(hào)索引就已 經(jīng)將記錄減少到只有幾條,如果再按后面的幾個(gè)字段索引查詢將對(duì)性能不產(chǎn)生太大的影響.

MYSQL教程對(duì)千萬級(jí)MySQL數(shù)據(jù)庫建立索引的事項(xiàng)及提高性能的手段

MYSQL教程一、注意事項(xiàng)：
首先,應(yīng)當(dāng)考慮表空間和磁盤空間是否足夠.我們知道索引也是一種數(shù)據(jù),在建立索引時(shí)勢(shì)必也會(huì)占用年夜量表空間.因此在對(duì)一年夜表建立索引時(shí)首先應(yīng)當(dāng)考慮的是空間容量問題.

MYSQL教程其次,在對(duì)建立索引時(shí)要對(duì)表進(jìn)行加鎖,因此應(yīng)當(dāng)注意操作在業(yè)務(wù)空閑時(shí)進(jìn)行.

MYSQL教程二、性能調(diào)整方面：
首先,考慮因素就是磁盤I/O.
1,物理上,應(yīng)當(dāng)盡量把索引與數(shù)據(jù)分散到不同的磁盤上(不考慮陣列的情況).
2,邏輯上,數(shù)據(jù)表空間與索引表空間分開.這是在建索引時(shí)應(yīng)當(dāng)遵守的基本準(zhǔn)則.

MYSQL教程其次,在建立索引時(shí)要對(duì)表進(jìn)行全表的掃描工作,因此,應(yīng)當(dāng)考慮調(diào)年夜初始化參數(shù)db_file_multiblock_read_count的值.
一般設(shè)置為32或更年夜.

MYSQL教程再次,建立索引除了要進(jìn)行全表掃描外同時(shí)還要對(duì)數(shù)據(jù)進(jìn)行大量的排序操作,因此,應(yīng)當(dāng)調(diào)整排序區(qū)的大小.
9i之前,可以在session級(jí)別上加大sort_area_size的大小,好比設(shè)置為100m或者更大.
9i以后,如果初始化參數(shù)workarea_size_policy的值為TRUE,則排序區(qū)從pga_aggregate_target里自動(dòng)分配獲得.
最后,建立索引時(shí),可以加上nologging選項(xiàng).以減少在建立索引過程中產(chǎn)生的大量redo,從而提高執(zhí)行的速度.

MYSQL教程MySql在建立索引優(yōu)化時(shí)注意的問題有哪些?

MYSQL教程設(shè)計(jì)MySql索引時(shí)有以下幾點(diǎn)注意：

MYSQL教程1,創(chuàng)建索引
對(duì)于查詢占主要的應(yīng)用來說,索引顯得尤為重要.很多時(shí)候性能問題很簡(jiǎn)單的就是因?yàn)槲覀兺颂砑铀饕斐傻?或者說沒有添加更為有效的索引導(dǎo)致.如果不加索引的話,那么查找任何哪怕只是一條特定的數(shù)據(jù)都會(huì)進(jìn)行一次全表掃描,如果一張表的數(shù)據(jù)量很大而符合條件的結(jié)果又很少,那么不加索引會(huì)引起致命的性能下降.但是也不是什么情況都非得建索引不可,好比性別可能就只有兩個(gè)值,建索引不僅沒什么優(yōu)勢(shì),還會(huì)影響到更新速度,這被稱為過度索引.

MYSQL教程2,復(fù)合索引
好比有一條語句是這樣的：select * from users where area=’beijing’ and age=22;
如果我們是在area和age上分別創(chuàng)建單個(gè)索引的話,由于mysql查詢每次只能使用一個(gè)索引,所以雖然這樣已經(jīng)相對(duì)不做索引時(shí)全表掃描提高了很多效率,但是如果在area、age兩列上創(chuàng)建復(fù)合索引的話將帶來更高的效率.如果我們創(chuàng)建了(area, age,salary)的復(fù)合索引,那么其實(shí)相當(dāng)于創(chuàng)建了(area,age,salary)、(area,age)、(area)三個(gè)索引,這被稱為最佳左前綴特性.因此我們?cè)趧?chuàng)建復(fù)合索引時(shí)應(yīng)該將最常用作限制條件的列放在最左邊,依次遞減.

MYSQL教程3,索引不會(huì)包括有NULL值的列
只要列中包括有NULL值都將不會(huì)被包括在索引中,復(fù)合索引中只要有一列含有NULL值,那么這一列對(duì)于此復(fù)合索引就是無效的.所以我們?cè)跀?shù)據(jù)庫設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL.

MYSQL教程4,使用短索引
對(duì)串列進(jìn)行索引,如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度.例如,如果有一個(gè)CHAR(255)的 列,如果在前10 個(gè)或20 個(gè)字符內(nèi),多數(shù)值是惟一的,那么就不要對(duì)整個(gè)列進(jìn)行索引.短索引不僅可以提高查詢速度并且可以節(jié)省磁盤空間和I/O操作.

MYSQL教程5,排序的索引問題
mysql查詢只使用一個(gè)索引,因此如果where子句中已經(jīng)使用了索引的話,那么order by中的列是不會(huì)使用索引的.因此數(shù)據(jù)庫默認(rèn)排序可以符合要求的情況下不要使用排序操作；盡量不要包括多個(gè)列的排序,如果需要最好給這些列創(chuàng)建復(fù)合索引.

MYSQL教程6,like語句操作
一般情況下不鼓勵(lì)使用like操作,如果非使用弗成,如何使用也是一個(gè)問題.like “%aaa%” 不會(huì)使用索引而like “aaa%”可以使用索引.

MYSQL教程7,不要在列上進(jìn)行運(yùn)算
?

MYSQL教程8,不使用NOT IN和操作
NOT IN和操作都不會(huì)使用索引將進(jìn)行全表掃描.NOT IN可以NOT EXISTS取代,id3則可使用id>3 or id

維易PHP培訓(xùn)學(xué)院每天發(fā)布《Mysql實(shí)例mysql索引使用與優(yōu)化》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/13686.html