《Mysql實例mysql索引使用與優化》要點：
本文介紹了Mysql實例mysql索引使用與優化，希望對您有用。如果有疑問，可以聯系我們。
MYSQL教程本節內容：
mysql數據庫的索引使用與優化.
MYSQL教程1．索引作用
在索引列上,除了有序查找之外,數據庫利用各種各樣的快速定位技術,能夠年夜年夜提高查詢效率.
特別是當數據量非常年夜,查詢涉及多個表時,使用索引往往能使查詢速度加快成千上萬倍.
MYSQL教程例如,有3個未索引的表t1、t2、t3,分別只包括列c1、c2、c3,每個表分別含有1000行數據組成,指為1～1000的數值,查找對應值相等行的查詢如下：
?
MYSQL教程此查詢結果應該為1000行,每行包括3個相等的值.
在無索引的情況下處理此查詢,必須尋找3個表所有的組合,以便得出與WHERE子句相配的那些行.
而可能的組合數目為1000×1000×1000(十億),顯然查詢將會非常慢.
MYSQL教程如果對每個表進行索引,就能極大地加速查詢進程.利用索引的查詢處理如下.
(1)從表t1中選擇第一行,查看此行所包括的數據.
(2)使用表t2上的索引,直接定位t2中與t1的值匹配的行.類似,利用表t3上的索引,直接定位t3中與來自t1的值匹配的行.
(3)掃描表t1的下一行并重復前面的過程,直到遍歷t1中所有的行.
在此情形下,仍然對表t1執行了一個完全掃描,但能夠在表t2和t3上進行索引查找直接取出這些表中的行,比未用索引時要快一百萬倍.
利用索引,MySQL加速了WHERE子句滿足條件行的搜索,而在多表連接查詢時,在執行連接時加快了與其他表中的行匹配的速度.
MYSQL教程2.? 創立索引
在執行CREATE TABLE語句時可以創立索引,也可以單獨用CREATE INDEX或ALTER TABLE來為表增加索引.
MYSQL教程1．ALTER TABLE
ALTER TABLE用來創立普通索引、UNIQUE索引或PRIMARY KEY索引.
?
MYSQL教程其中table_name是要增加索引的表名,column_list指出對哪些列進行索引,多列時各列之間用逗號分隔.
索引名index_name可選,缺省時,MySQL將根據第一個索引列賦一個名稱.
另外,ALTER TABLE允許在單個語句中變動多個表,因此可以在同時創建多個索引.
MYSQL教程2．CREATE INDEX
CREATE INDEX可對表增加普通索引或UNIQUE索引.
?
MYSQL教程table_name、index_name和column_list具有與ALTER TABLE語句中相同的含義,索引名弗成選.
另外,不能用CREATE INDEX語句創建PRIMARY KEY索引.
MYSQL教程3．索引類型
在創建索引時,可以規定索引能否包括重復值.如果不包括,則索引應該創建為PRIMARY KEY或UNIQUE索引.
對于單列惟一性索引,這保證單列不包括重復的值.對于多列惟一性索引,保證多個值的組合不重復.
MYSQL教程PRIMARY KEY索引和UNIQUE索引非常類似.事實上,PRIMARY KEY索引僅是一個具有名稱PRIMARY的UNIQUE索引.
這表示一個表只能包括一個PRIMARY KEY,因為一個表中不可能具有兩個同名的索引.
MYSQL教程對students表在sid上添加PRIMARY KEY索引：
?
MYSQL教程?
4.? 刪除索引
可利用ALTER TABLE或DROP INDEX語句來刪除索引.
類似于CREATE INDEX語句,DROP INDEX可以在ALTER TABLE內部作為一條語句處置,語法如下.
?
MYSQL教程此中,前兩條語句是等價的,刪除掉table_name中的索引index_name.
MYSQL教程第3條語句只在刪除PRIMARY KEY索引時使用,因為一個表只可能有一個PRIMARY KEY索引,因此不必要指定索引名.如果沒有創建PRIMARY KEY索引,但表具有一個或多個UNIQUE索引,則MySQL將刪除第一個UNIQUE索引.
MYSQL教程如果從表中刪除了某列,則索引會受到影響.對于多列組合的索引,如果刪除此中的某列,則該列也會從索引中刪除.如果刪除組成索引的所有列,則整個索引將被刪除.
MYSQL教程5．查看索引
?
MYSQL教程說明：
· Table
表的名稱.
· Non_unique
如果索引不能包括重復詞,則為0.如果可以,則為1.
· Key_name
索引的名稱.
· Seq_in_index
索引中的列序列號,從1開始.
· Column_name
列名稱.
· Collation
列以什么方式存儲在索引中.在MySQL中,有值‘A’(升序)或NULL(無分類).
· Cardinality
索引中唯一值的數目的估計值.通過運行ANALYZE TABLE或myisamchk -a可以更新.基數根據被存儲為整數的統計數據來計數,所以即使對于小型表,該值也沒有必要是精確的.基數越大,當進行聯合時,MySQL使用該索引的機會就越大.
· Sub_part
如果列只是被部分地編入索引,則為被編入索引的字符的數目.如果整列被編入索引,則為NULL.
· Packed
指示關鍵字如何被壓縮.如果沒有被壓縮,則為NULL.
· Null
如果列含有NULL,則含有YES.如果沒有,則該列含有NO.
· Index_type
用過的索引辦法(BTREE, FULLTEXT, HASH, RTREE).
· Comment
MYSQL教程6．什么情況下使用索引
表的主關鍵字,自動建立唯一索引
如zl_yhjbqk(用戶基本情況)中的hbs_bh(戶標識編號)
MYSQL教程表的字段唯一約束
ORACLE利用索引來保證數據的完整性
MYSQL教程如lc_hj(流程環節)中的lc_bh+hj_sx(流程編號+環節次序)
MYSQL教程直接條件查詢的字段
在SQL中用于條件約束的字段
MYSQL教程如zl_yhjbqk(用戶基本情況)中的qc_bh(區冊編號)
?
MYSQL教程查詢中與其它表關聯的字段
字段經常建立了外鍵關系
如zl_ydcf(用電成份)中的jldb_bh(計量點表編號)
?
MYSQL教程查詢中排序的字段
排序的字段如果通過索引去拜訪那將大大提高排序速度
?
MYSQL教程查詢中統計或分組統計的字段
?
MYSQL教程2,什么情況下應不建或少建索引
1)、表記錄太少
如果一個表只有5條記錄,采用索引去拜訪記錄的話,那首先需拜訪索引表,再通過索引表拜訪數據表,一般索引表與數據表不在同一個數據塊,這種情況下ORACLE至少要往返讀取數據塊兩次.而不用索引的情況下ORACLE會將所有的數據一次讀出,處理速度顯然會比用索引快.
MYSQL教程如表zl_sybm(使用部門)一般只有幾條記錄,除了主關鍵字外對任何一個字段建索引都不會產生性能優化,實際上如果對這個表進行了統計分析后ORACLE也不會用你建的索引,而是自動執行全表拜訪.如：
?
MYSQL教程2)、經常插入、刪除、修改的表
對一些經常處理的業務表應在查詢允許的情況下盡量減少索引,如zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等業務表.
MYSQL教程3)、數據重復且分布平均的表字段
假如一個表有10萬行記錄,有一個字段A只有T和F兩種值,且每個值的分布概率大約為50%,那么對這種表A字段建索引一般不會提高數據庫的查詢速度.
經常和主字段一塊查詢但主字段索引值比擬多的表字段
如gc_dfss(電費實收)表經常按收費序號、戶標識編號、抄表日期、電費發生年月、操作 標志來具體查詢某一筆收款的情況,如果將所有的字段都建在一個索引里那將會增加數據的修改、插入、刪除時間,從實際上分析一筆收款如果按收費序號索引就已 經將記錄減少到只有幾條,如果再按后面的幾個字段索引查詢將對性能不產生太大的影響.
MYSQL教程對千萬級MySQL數據庫建立索引的事項及提高性能的手段
MYSQL教程一、注意事項：
首先,應當考慮表空間和磁盤空間是否足夠.我們知道索引也是一種數據,在建立索引時勢必也會占用年夜量表空間.因此在對一年夜表建立索引時首先應當考慮的是空間容量問題.
MYSQL教程其次,在對建立索引時要對表進行加鎖,因此應當注意操作在業務空閑時進行.
MYSQL教程二、性能調整方面：
首先,考慮因素就是磁盤I/O.
1,物理上,應當盡量把索引與數據分散到不同的磁盤上(不考慮陣列的情況).
2,邏輯上,數據表空間與索引表空間分開.這是在建索引時應當遵守的基本準則.
MYSQL教程其次,在建立索引時要對表進行全表的掃描工作,因此,應當考慮調年夜初始化參數db_file_multiblock_read_count的值.
一般設置為32或更年夜.
MYSQL教程再次,建立索引除了要進行全表掃描外同時還要對數據進行大量的排序操作,因此,應當調整排序區的大小.
9i之前,可以在session級別上加大sort_area_size的大小,好比設置為100m或者更大.
9i以后,如果初始化參數workarea_size_policy的值為TRUE,則排序區從pga_aggregate_target里自動分配獲得.
最后,建立索引時,可以加上nologging選項.以減少在建立索引過程中產生的大量redo,從而提高執行的速度.
MYSQL教程MySql在建立索引優化時注意的問題有哪些?
MYSQL教程設計MySql索引時有以下幾點注意：
MYSQL教程1,創建索引
對于查詢占主要的應用來說,索引顯得尤為重要.很多時候性能問題很簡單的就是因為我們忘了添加索引而造成的,或者說沒有添加更為有效的索引導致.如果不加索引的話,那么查找任何哪怕只是一條特定的數據都會進行一次全表掃描,如果一張表的數據量很大而符合條件的結果又很少,那么不加索引會引起致命的性能下降.但是也不是什么情況都非得建索引不可,好比性別可能就只有兩個值,建索引不僅沒什么優勢,還會影響到更新速度,這被稱為過度索引.
MYSQL教程2,復合索引
好比有一條語句是這樣的：select * from users where area=’beijing’ and age=22;
如果我們是在area和age上分別創建單個索引的話,由于mysql查詢每次只能使用一個索引,所以雖然這樣已經相對不做索引時全表掃描提高了很多效率,但是如果在area、age兩列上創建復合索引的話將帶來更高的效率.如果我們創建了(area, age,salary)的復合索引,那么其實相當于創建了(area,age,salary)、(area,age)、(area)三個索引,這被稱為最佳左前綴特性.因此我們在創建復合索引時應該將最常用作限制條件的列放在最左邊,依次遞減.
MYSQL教程3,索引不會包括有NULL值的列
只要列中包括有NULL值都將不會被包括在索引中,復合索引中只要有一列含有NULL值,那么這一列對于此復合索引就是無效的.所以我們在數據庫設計時不要讓字段的默認值為NULL.
MYSQL教程4,使用短索引
對串列進行索引,如果可能應該指定一個前綴長度.例如,如果有一個CHAR(255)的 列,如果在前10 個或20 個字符內,多數值是惟一的,那么就不要對整個列進行索引.短索引不僅可以提高查詢速度并且可以節省磁盤空間和I/O操作.
MYSQL教程5,排序的索引問題
mysql查詢只使用一個索引,因此如果where子句中已經使用了索引的話,那么order by中的列是不會使用索引的.因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包括多個列的排序,如果需要最好給這些列創建復合索引.
MYSQL教程6,like語句操作
一般情況下不鼓勵使用like操作,如果非使用弗成,如何使用也是一個問題.like “%aaa%” 不會使用索引而like “aaa%”可以使用索引.
MYSQL教程7,不要在列上進行運算
?
MYSQL教程8,不使用NOT IN和操作
NOT IN和操作都不會使用索引將進行全表掃描.NOT IN可以NOT EXISTS取代,id3則可使用id>3 or id
維易PHP培訓學院每天發布《Mysql實例mysql索引使用與優化》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。
