使用MySQL索引如何快速檢索數據庫的案例

這篇文章將為大家詳細講解有關使用MySQL索引如何快速檢索數據庫的案例，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

一、索引的概念

1、索引是一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數據所在行的物理地址 (類似于C語言的鏈表通過指針指向數據記錄的內存地址) 。

2、使用索引后可以不用掃描全表來定位某行的數據，而是先通過索引表找到該行數據對應的物理地址然后訪問相應的數據，因此能加快數據庫的查詢速度。

3、索引就好比是一本書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

4、索引是表中一列或者若干列值排序的方法。

5、建立索引的目的是加快對表中記錄的查找或排序。

二、索引的作用

1、設置了合適的索引之后，數據庫利用各種快速定位技術，能夠大大加快查詢速度，這是創建索引的最主要的原因。
2、當表很大或查詢涉及到多個表時，使用索引可以成千，上萬倍地提高查詢速度。
3、可以降低數據庫的I/O成本，并且索引還可以降低數據庫的排序成本。
4、通過創建唯一性索引，可以保證數據表中每一行數據的唯一性。
5、可以加快表與表之間的連接。
6、在使用分組和排序時，可大大減少分組和排序的時間。

三、索引的副作用

1、索引需要占用額外的磁盤空間。

• 對于 MyISAM 引擎而言，索引文件和數據文件是分離的，索引文件用于保存數據記錄的地址。

• 而 InnoDB 引擎的表數據文件本身就是索引文件。

2、在插入和修改數據時要花費更多的時間，因為索引也要隨之變動。

四、創建索引的原則依據

索引隨可以提升數據庫查詢的速度，但并不是任何情況下都適合創建索引。因為索引本身會消耗系統資源，在有索引的情況下，數據庫會先進行索引查詢，然后定位到具體的數據行，如果索引使用不當，反而會增加數據庫的負擔。

1、表的主鍵、外鍵必須有索引。因為主鍵具有唯一性，外鍵關聯的是主表的主鍵，查詢時可以快速定位。
2、記錄數超過300行的表應該有索引。如果沒有索引，每次查詢都需要把表遍歷一遍，會嚴重影響數據庫的性能。
3、經常與其他表進行連接的表，在連接字段上應該建立索引。
4、唯一 性太差的字段不適合建立索引。
5、更新太頻繁地字段不適合創建索引。
6、經常出現在 where 子句中的字段，特別是大表的字段，應該建立索引。
7、索引應該建在選擇性高的字段上。
8、索引應該建在小字段.上，對于大的文本字段甚至超長字段，不要建索引。

五、索引的分類和創建

在做之前先用這張表做示范
create table member (id int(10),name varchar(10),Cardid varchar(10),phone int(11),address varchar(50),remark text);

（一）、普通索引

普通索引：最基本的索引類型，沒有唯一性之類的限制。

CREATE INDEX  索引名  ON  表名 （列名[(length)]）；

#(列名(length))：length是可選項，下同。如果忽略 length 的值，則使用整個列的值作為索引。如果指定使用列前的 length 個字符來創建索引，這樣有利于減小索引文件的大小。
#索引名建議以“_index”結尾。

ALTER TABLE 表名 ADD INDEX 索引名 (列名);例：alter table member ADD INDEX phone_index (phone);select phone from member;

CREATE TABLE 表名 ( 字段1 數據類型,字段2 數據類型[,...],INDEX 索引名 (列名));例：create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));show create table test;

（二）、唯一索引

與普通索引類似，但區別是唯一索引列的每個值都唯一。
唯一索引允許有空值（注意和主鍵不同）。如果是用組合索引創建，則列值的組合必須唯一。添加唯一鍵將自動創建唯一索引。

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);例：alter table member add unique name_index (cardid);

為了方便實驗，我先刪除了之前的索引

CREATE TABLE 表名 (字段1 數據類型,字段2 數據類型[,...],UNIQUE 索引名 (列名));

（三）、主鍵索引

是一種特殊的唯一索引，必須指定為“PRIMARY KEY”。
一個表只能有一個主鍵，不允許有空值。 添加主鍵將自動創建主鍵索引。

• ALTER TABLE 表名 ADD PRIMARY KEY (列名);

（四）、組合索引（單列索引與多列索引）

組合索引:可以是單列上創建的索引，也可以是在多列上創建的索引。需要滿足最左原則，因為 select語句的 where 條件是依次從左往右執行的，所以在使用 select 語句查詢時 where 條件使用的字段順序必須和組合索引中的排序一致，否則索引將不會生效。

CREATE TABLE 表名 (列名1 數據類型,列名2 數據類型,列名3 數據類型,INDEX 索引名(列名1,列名2,列名3));select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

（五）、全文索引

適合在進行模糊查詢的時候使用，可用于在一篇文章中檢索文本信息。
在 MySQL5.6 版本以前FULLTEXT 索引僅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 類型的列上創建。每個表只允許有一個全文索引。

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);例：select * from member;create fulltext index name_index on member (name);

• 修改表的方式創建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

• 創建表的時候指定索引

CREATE TABLE 表名 (字段1 數據類型[,...],FULLTEXT 索引名 (列名));

#數據類型可以為 CHAR、VARCHAR 或者 TEXT

• 使用全文索引查詢

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查詢內容');例：select * from member where match(remark) against('this is vip');

六、查看索引

show index from 表名;show index from 表名\G； 豎向顯示表索引信息
show keys from 表名;show keys from 表名\G;

七、刪除索引

1、直接刪除索引
DROP INDEX 索引名 ON 表名;2、修改表方式刪除索引
ALTER TABLE 表名 DROP INDEX 索引名;3、刪除主鍵索引
ALTER TABLE 表名 DROP PRIMARY KEY;

八、實例

案例：為某商場做一個會員卡的系統。這個系統有一個會員表，有下列字段:會員編號  INT
會員姓名  VARCHAR(10)會員身份證號碼  VARCHAR(18)會員電話  INT(11)會員住址  VARCHAR (50)會員備注信息  TEXT 

create table member (id int(10),name varchar(10),cardid varchar(18),phone int(11),address varchar(50),remark text);alter table member add primary key (id);create index name_index on member (name); create unique index cardid_index on member (cardid);alter table member add fulltext remark_index (remark);會員編號，作為主鍵，使用 PRIMARY KEY
會員姓名,如果要建索引的話，那么就是普通的 INDEX
會員身份證號碼,如果要建索引的話，那么可以選擇 UNIQUE (唯一的，不允許重復)會員備注信息，如果需要建索引的話，可以選擇 FULLTEXT，全文搜索。
不過 FULLTEXT 用于搜索很長一篇文章的時候，效果最好。用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以。

關于“使用MySQL索引如何快速檢索數據庫的案例”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。