mysql學習筆記之三(索引),mysql學習筆記索引
索引
資料庫物件索引:一種組合資料的方式,通過索引對象,可以快速查詢到資料庫物件表中的特定記錄,是一種提高效能的最常用方式。
一個索引會包含表中的按照一定順序排序的一列或多欄欄位。
索引操作:
建立索引,修改索引,刪除索引。
資料庫物件索引主要為了提高從表中檢索資料的速度。由於資料存放區在資料庫表中,所以索引是建立在資料庫物件上的,由表中的一個欄位或多個欄位產生的鍵組成,這些鍵儲存在資料結構裡(B-樹或雜湊表)中,通過MySql可以快速有效尋找與鍵值相關聯的欄位。根據索引的儲存類型,可以將所以分為B型樹索引和雜湊索引
注意:InnoDB和MyISAM儲存引擎支援Btree類型索引,MEMORY儲存引擎支援Hash類型索引,預設為前者索引。
資料庫物件索引的出現,除了提供資料庫管理系統的尋找速度,而且還可以保證欄位的唯一性。從而實現資料庫表的唯一性。
六種索引:普通索引,唯一索引,全文索引,單列索引,多列索引,空間索引
利弊:
利:提高尋找速度
弊:過多索引佔用磁碟空間
適合建立索引的情況:
1、經常被查詢的欄位,即在where中出現的欄位
2、分組的欄位,即在group by中出現的欄位
3、存在依賴關係的字表和父表之間的聯集查詢,即主鍵或外鍵欄位
4、設定唯一完整性的欄位
不適合建立的情況:
1、在查詢中很少被使用的欄位
2、擁有許多重複值的欄位普通索引
建立:
三種方式:
1、建立表時建立索引
2、在已經存在的表上建立索引
3、通過SQL語句在ALTER TABLE來建立
1、create table tablename(
column1 type,
column2 type,
....
INDEX|KEY [indexname] (columname [(長度)] [ASC|DESC])
)
通過index或key來指定欄位為索引。
在建立索引時,可以指定索引的長度。這是因為不同儲存引擎定義了標的最大索引數和最大索引長度
mysql所支援的儲存引擎對每一個表至少支援16個索引,總索引長度至少為256位元組
create table t_index1(
id int,
name varchar(20),
loc varchar(20),
index index_id(id)
)
之後向表裡插入多條記錄(這是必須的,如果只有一條記錄,出現不了下面的字樣),然後執行
explain select * from t_index1 where id=1\G
之後出現
possible key:index_id
key:index_id
的字樣之後就說明id的索引對象被啟用
2、在已經存在的表上建立普通索引
create index indexname on tablename(column[(長度)][asc|desc])
create index index_name on t_index1(name)
驗證:
explain select * from t_index1 where id=1\G
字樣出現
possible key:index_name
key:index_name
3、通過SQL語句alter table 建立普通索引
alter table tablename add index|key indexname(columnname[(length)][asc|desc])
又見識到create和alter的兩個功能。修改索引會不會用alter呢?刪除索引會不會用drop呢?往後看看
唯一索引
1、建立表時建立唯一索引
create table tablename(
column1 type,
column2 type,
...
unique index|key indexname(column[(length)][asc|desc])
)
所謂的唯一索引就是在普通索引上添加上關鍵字UNIQUE。
2、在已經存在的表上建立一個唯一索引
試著自己寫寫
create unique index indexname on tablename(columname[(長度)][asc|desc])
完全OK
3、通過SQL語句alter table建立索引
alter table tablename add unique index|key indexname(columnname[(length)][asc|desc])
全文索引
全文索引主要關聯在資料類型為char,varchar,text欄位,以便能夠更加快速查詢資料量較大的字串類型的欄位
mysql從3.23.23版本開始支援全文索引,只能在儲存類型為myISAM的資料庫表上建立全文索引。
預設情況下,全文索引的搜尋方式為不區分大小寫,如果全文索引所關聯的欄位為位元據類型,則以區分大小寫搜尋方式執行。
關鍵字:fulltext index
三種建立方式跟上面的類似,將index關鍵字寫成fulltext index就是建立了全文索引
多列索引
在建立索引時,所關聯的欄位不是一個欄位,而是多個欄位
雖然可以通過所關聯的欄位進行查詢,但是只有查詢條件使用了所關聯欄位中的第一個欄位,多列索引才會被使用。
create table t_name(
column1 type,
column2 type,
...
index|key index_columni_columnj(columni[(length)][asc|desc],columnj[(length)][asc|desc])
)
其他兩種建立方式類推一下就可以了。
六種索引:普通索引,唯一索引,全文索引,單列索引,多列索引,空間索引
一共是六種,這裡講到了三種。
網上搜尋到的空間索引
空間索引是對空間資料類型的欄位建立的索引,MYSQL中的空間資料類型有4種,
分別是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL關鍵字進行擴充,使得能夠用於建立正規索引類型的文法建立空間索引。建立空間索引的列,必須
將其聲明為NOT NULL,空間索引只能在儲存引擎為MYISAM的表中建立
create table index6(
id int,
space geometry not null,
spatial index index6_sp(space)
);
以上都是建立索引,有建立就得有刪除
刪除索引
drop index indexname on tablename
文法很簡單。