mysql索引.ppt

1、第7章 索引,索引是一種特殊的數(shù)據(jù)庫結(jié)構(gòu)，可以用來快速查詢數(shù)據(jù)庫表中的特定記錄。索引是提高數(shù)據(jù)庫性能的重要方式。MySQL中，所有的數(shù)據(jù)類型都可以被索引。MySQL的索引包括普通索引、惟一性索引、全文索引、單列索引、多列索引和空間索引等。 索引的含義和特點(diǎn) 索引的分類 如何設(shè)計(jì)索引 如何創(chuàng)建索引 如何刪除索引,7.1 索引簡介,索引由數(shù)據(jù)庫表中一列或多列組合而成，其作用是提高對表中數(shù)據(jù)的查詢速度。本節(jié)將詳細(xì)講解索引的含義、作用、分類和設(shè)計(jì)索引的原則。,7.1.1 索引的含義和特點(diǎn),索引是創(chuàng)建在表上的，是對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。索引可以提高查詢的速度。通過索引，查詢數(shù)據(jù)時可

2、以不必讀完記錄的所有信息，而只是查詢索引列。 本小節(jié)將詳細(xì)講解索引的含義、作用、優(yōu)缺點(diǎn)。索引有起明顯的優(yōu)勢，也有其不可避免的缺點(diǎn)。 索引的優(yōu)點(diǎn) 索引的缺點(diǎn),索引的優(yōu)點(diǎn)是可以提高檢索數(shù)據(jù)的速度，這是創(chuàng)建索引的最主要的原因；對于有依賴關(guān)系的子表和父表之間的聯(lián)合查詢時，可以提高查詢速度；使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時，同樣可以顯著節(jié)省查詢中分組和排序的時間。 索引的缺點(diǎn)是創(chuàng)建和維護(hù)索引需要耗費(fèi)時間，耗費(fèi)時間的數(shù)量隨著數(shù)據(jù)量的增加而增加；索引需要占用物理空間，每一個索引要占一定的物理空間；增加、刪除和修改數(shù)據(jù)時，要動態(tài)的維護(hù)索引，造成數(shù)據(jù)的維護(hù)速度降低了。,7.1.2 索引的分類,MySQL的索引包

3、括普通索引、惟一性索引、全文索引、單列索引、多列索引和空間索引等。本小節(jié)將詳細(xì)講解這幾種索引的含義和特點(diǎn)。 1普通索引 2惟一性索引 3全文索引 4單列索引 5多列索引 6空間索引,7.1.3 索引的設(shè)計(jì)原則,為了使索引的使用效率更高，在創(chuàng)建索引的時候必須考慮在哪些字段上創(chuàng)建索引和創(chuàng)建什么類型的索引。本小節(jié)將向讀者介紹一些索引的設(shè)計(jì)原則。 1選擇惟一性索引 2為經(jīng)常需要排序、分組和聯(lián)合操作的字段建立索引 3為常作為查詢條件的字段建立索引 4限制索引的數(shù)目 5盡量使用數(shù)據(jù)量少的索引 6盡量使用前綴來索引 7刪除不再使用或者很少使用的索引,7.2 創(chuàng)建索引,創(chuàng)建索引是指在某個表的一列或多列上建立一

4、個索引，以便提高對表的訪問速度。創(chuàng)建索引有三種方式，這三種方式分別是創(chuàng)建表的時候創(chuàng)建索引、在已經(jīng)存在的表上創(chuàng)建索引和使用ALTER TABLE語句來創(chuàng)建索引。本節(jié)將詳細(xì)講解這三種創(chuàng)建索引的方法。,7.2.1 創(chuàng)建表的時候創(chuàng)建索引,創(chuàng)建表的時候可以直接創(chuàng)建索引，這種方式最簡單、方便。其基本形式如下： CREATE TABLE 表名 ( 屬性名 數(shù)據(jù)類型 完整性約束條件, 屬性名 數(shù)據(jù)類型 完整性約束條件, 屬性名 數(shù)據(jù)類型 UNIQUE | FULLTEXT | SPATIAL INDEX | KEY 別名(屬性名1 (長度) ASC | DESC) );,1 普通索引 Create table

5、 index1( Id int, Name varchar(20), Sex boolean, Index(id) ); Show create table index1 G Explain select * from index1 where id=1 G,2 創(chuàng)建唯一性索引 Create table index2( Id int unique, Name varchar(20), Unique index index2_id(id asc) );,3 創(chuàng)建全文索引 只能創(chuàng)建在char,varchar或text類型的字段上，而且，現(xiàn)在只有MyISAM存儲引擎支持全文索引。 Create ta

6、ble index3( Id int, Info varchar(20), Fulltext index index3_info(info) )engine=myisam;,4 創(chuàng)建單列索引 Create table index4( Id int, Subject varchar(30), Index index4_st(subject(10) );,5 創(chuàng)建多列索引 Create table index5( Id int, Name varchar(20), Sex char(4), Index index5_ns(name,sex) ); 使用多列索引時一定要特別注意，只有使用了索引中的第

7、一個字段時才會觸發(fā)索引。如果沒有使用索引中的第一個字段，那么這個多列索引就不會起作用。,6 創(chuàng)建空間索引 Create table index6( Id int, Space geometry not null, Spatial index index6_sp(space) )engine=myisam; 創(chuàng)建空間索引時，表的存儲引擎必須是myisam類型，而且索引字段必須有非空約束。空間數(shù)據(jù)類型包括geometry,point,linestring和polygon類型等。平時很少用到。,7.2.2 在已經(jīng)存在的表上創(chuàng)建索引,在已經(jīng)存在的表上，可以直接為表上的一個或幾個字段創(chuàng)建索引?；拘问饺?/p>

8、下： CREATE UNIQUE | FULLTEXT | SPATIAL INDEX 索引名 ON 表名 (屬性名 (長度) ASC | DESC ); 1創(chuàng)建普通索引 2創(chuàng)建惟一性索引 3創(chuàng)建全文索引 4創(chuàng)建單列索引 5創(chuàng)建多列索引 6創(chuàng)建空間索引,7.2.3 用ALTER TABLE語句來創(chuàng)建索引,在已經(jīng)存在的表上，可以通過ALTER TABLE語句直接為表上的一個或幾個字段創(chuàng)建索引。基本形式如下： ALTER TABLE 表名 ADD UNIQUE | FULLTEXT | SPATIAL INDEX 索引名（屬性名 (長度) ASC | DESC）; 其中的參數(shù)與上面的兩種方式的參數(shù)

9、是一樣的。 1創(chuàng)建普通索引 2創(chuàng)建惟一性索引 3創(chuàng)建全文索引 4創(chuàng)建單列索引 5創(chuàng)建多列索引 6創(chuàng)建空間索引,7.3 刪除索引,刪除索引是指將表中已經(jīng)存在的索引刪除掉。一些不再使用的索引會降低表的更新速度，影響數(shù)據(jù)庫的性能。對于這樣的索引，應(yīng)該將其刪除。本節(jié)將詳細(xì)講解刪除索引的方法。 對應(yīng)已經(jīng)存在的索引，可以通過DROP語句來刪除索引?；拘问饺缦拢?DROP INDEX 索引名 ON 表名 ;,7.4 上機(jī)實(shí)踐,題目要求： （1）在數(shù)據(jù)庫job下創(chuàng)建workInfo表。創(chuàng)建表的同時在id字段上創(chuàng)建名為index_id的惟一性索引，而且也降序的格式排列。,2 使用create index語句為

10、name字段創(chuàng)建長度為10的索引index_name 3 使用alter table語句在type和address上創(chuàng)建名為index_t的索引 4 將workinfo表的存儲引擎更改為myisam類型 5 使用alter table語句在extra字段創(chuàng)建名為index_ext的全文索引 6 刪除workinfo表的唯一性索引index_id,7.5 常見問題及解答,1MySQL中索引、主鍵和惟一性的區(qū)別是什么？ 索引建立在一個或幾個字段上，建立了索引后，表中的數(shù)據(jù)就按照索引的一定規(guī)則排列。這樣可以提高查詢速度 主鍵是表中數(shù)據(jù)的唯一標(biāo)識。不同的記錄的主鍵值不同。在建立主鍵時，系統(tǒng)會自動建立一個唯一性索引。 唯一性是建立在表中一個或者幾個字段上。其目的是為了對于不同的記錄，具有唯一性的字段的值是不同的。,2表中建立了索引以后，導(dǎo)入大量數(shù)據(jù)為什么會很慢？ 對已經(jīng)建立了索引的表中插入數(shù)據(jù)時，插入一條數(shù)據(jù)就要對該記錄按索引排序。因此，導(dǎo)入大量數(shù)據(jù)的時候速度會很慢。解決這種情況的辦法是，在沒有任何索引的情況插入數(shù)據(jù)，然后建立索引。,7