數(shù)據(jù)庫索引的作用及實(shí)例

1、2. 在索引列上,除了上面提到的有序查找之外,數(shù)據(jù)庫利用各種各樣的 快速定位技術(shù), 能夠大大提高查詢效率。 特別是當(dāng)數(shù)據(jù)量非常大, 查詢涉 及多個(gè)表時(shí),使用索引往往能使查詢速度加快成千上萬倍。3.4. 例如,有 3個(gè)未索引的表 t1、 t2、 t3,分別只包含列 c1、 c2、 c3,每 個(gè)表分別含有 1000行數(shù)據(jù)組成,指為 11000的數(shù)值,查找對應(yīng)值相等 行的查詢?nèi)缦滤尽?.6. SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c37.8. 此查詢結(jié)果應(yīng)該為 1000行, 每行包含 3個(gè)相等的值。 在無索引的情況 下處理此查詢, 必須尋

2、找 3個(gè)表所有的組合, 以便得出與 WHERE 子句相配 的那些行。而可能的組合數(shù)目為 1000×1000×1000(十億,顯然查詢 將會(huì)非常慢。9.10. 如果對每個(gè)表進(jìn)行索引,就能極大地加速查詢進(jìn)程。利用索引的查詢 處理如下。11.12. (1從表 t1中選擇第一行,查看此行所包含的數(shù)據(jù)。13.14. (2使用表 t2上的索引,直接定位 t2中與 t1的值匹配的行。類似,利 用表 t3上的索引,直接定位 t3中與來自 t1的值匹配的行。15.16. (3 掃描表 t1的下一行并重復(fù)前面的過程, 直到遍歷 t1中所有的行。 17.18. 在此情形下,仍然對表 t1執(zhí)行了一

3、個(gè)完全掃描,但能夠在表 t2和 t3上進(jìn)行索引查找直接取出這些表中的行, 比未用索引時(shí)要快一百萬倍。 19.20. 利用索引, MySQL 加速了 WHERE 子句滿足條件行的搜索,而在多表連 接查詢時(shí),在執(zhí)行連接時(shí)加快了與其他表中的行匹配的速度。21.22.2. 創(chuàng)建索引23. 在執(zhí)行 CREATE TABLE語句時(shí)可以創(chuàng)建索引, 也可以單獨(dú)用 CREATE INDEX或 ALTER TABLE來為表增加索引。24.25.1. ALTER TABLE26.ALTER TABLE用來創(chuàng)建普通索引、 UNIQUE 索引或 PRIMARY KEY索引。 27.28.29.30.ALTER TABL

4、E table_name ADD INDEX index_name (column_list 31.32.ALTER TABLE table_name ADD UNIQUE (column_list34.ALTER TABLE table_name ADD PRIMARY KEY (column_list35.36.37.38. 其 中 table_name是要增加索引的表名, column_list指出對哪些列進(jìn)行 索引,多列時(shí)各列之間用逗號(hào)分隔。索引名 index_name可選, 缺省時(shí), MySQL 將根據(jù)第一個(gè)索引列賦一個(gè)名稱。另外, ALTER TABLE允許在單個(gè) 語句中更改多個(gè)表

5、,因此可以在同時(shí)創(chuàng)建多個(gè)索引。39.40.2. CREATE INDEX41.CREATE INDEX可對表增加普通索引或 UNIQUE 索引。42.43.44.45.CREATE INDEX index_name ON table_name (column_list46.47.CREATE UNIQUE INDEX index_name ON table_name (column_list 48.49.50.51.table_name、 index_name和 column_list具有與 ALTER TABLE語句中相 同的含義,索引名不可選。另外,不能用 CREATE INDEX語句創(chuàng)建

6、PRIMARY KEY索引。52.54. 在創(chuàng)建索引時(shí), 可以規(guī)定索引能否包含重復(fù)值。 如果不包含, 則索引應(yīng)該 創(chuàng)建為 PRIMARY KEY或 UNIQUE 索引。對于單列惟一性索引,這保證單列 不包含重復(fù)的值。對于多列惟一性索引,保證多個(gè)值的組合不重復(fù)。 55.56.PRIMARY KEY索引和 UNIQUE 索引非常類似。 事實(shí)上, PRIMARY KEY索引僅 是一個(gè)具有名稱 PRIMARY 的 UNIQUE 索引。這表示一個(gè)表只能包含一個(gè) PRIMARY KEY,因?yàn)橐粋€(gè)表中不可能具有兩個(gè)同名的索引。57.58. 下面的 SQL 語句對 students 表在 sid 上添加 PR

7、IMARY KEY索引。59.60.61.62.ALTER TABLE students ADD PRIMARY KEY (sid63.64.4. 刪除索引65. 可利用 ALTER TABLE或 DROP INDEX語句來刪除索引。類似于CREATE INDEX語句, DROP INDEX可以在 ALTER TABLE內(nèi)部作為一條語句 處理,語法如下。67.68.69.DROP INDEX index_name ON talbe_name70.71.ALTER TABLE table_name DROP INDEX index_name72.73.ALTER TABLE table_name

8、 DROP PRIMARY KEY74.75.76.77. 其中, 前兩條語句是等價(jià)的, 刪除掉 table_name中的索引 index_name。 78.79. 第 3條語句只在刪除 PRIMARY KEY索引時(shí)使用, 因?yàn)橐粋€(gè)表只可能有一個(gè) PRIMARY KEY索引,因此不需要指定索引名。如果沒有創(chuàng)建 PRIMARY KEY索引,但表具有一個(gè)或多個(gè) UNIQUE 索引,則 MySQL 將刪除第一個(gè) UNIQUE 索引。80.81. 如果從表中刪除了某列, 則索引會(huì)受到影響。 對于多列組合的索引, 如果 刪除其中的某列, 則該列也會(huì)從索引中刪除。 如果刪除組成索引的所有列, 則整個(gè)索引將

9、被刪除。82.83.85.86.mysql> show index from tblname;87.88.mysql> show keys from tblname;89.90. · Table91.92. 表的名稱。93.94. · Non_unique95.96. 如果索引不能包括重復(fù)詞,則為 0。如果可以,則為 1。97.98. · Key_name99.100. 索引的名稱。101.102. · Seq_in_index103.104. 索引中的列序列號(hào),從 1開始。105.106. · Column_name107.108

10、. 列名稱。109.110. · Collation111.112. 列以什么方式存儲(chǔ)在索引中。在 MySQL 中,有值A(chǔ)(升序 或 NULL (無分類。113.114. · Cardinality115.116. 索引中唯一值的數(shù)目的估計(jì)值。通過運(yùn)行 ANALYZE TABLE或 myisamchk -a可以更新?；鶖?shù)根據(jù)被存儲(chǔ)為整數(shù)的統(tǒng)計(jì)數(shù)據(jù)來計(jì)數(shù),所 以即使對于小型表, 該值也沒有必要是精確的。 基數(shù)越大, 當(dāng)進(jìn)行聯(lián)合時(shí), MySQL 使用該索引的機(jī)會(huì)就越大。117.118. · Sub_part119.120. 如果列只是被部分地編入索引, 則為被編入索引的字符的數(shù)目。 如果整列被編入索引,則為 NULL 。121.122. · Packed123.124. 指示關(guān)鍵字