1. 普通索引
這是最基本的索引,它沒有任何限制,
–直接創建索引
CREATE INDEX index_name ON table(column(length))
–修改表結構的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
–創建表的時候同時創建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)
–刪除索引
DROP INDEX index_name ON table
2. 唯一索引
與普通索引類似,不同的就是:索引列的值必須唯一,但允許有空值(注意和主鍵不同)。如果是組合索引,則列值的組合必須唯一,創建方法和普通索引類似。
–創建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
–修改表結構
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
–創建表的時候直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (title(length))
);
3. 全文索引(FULLTEXT)
MySQL從3.23.23版開始支持全文索引和全文檢索,FULLTEXT索引僅可用于 MyISAM 表;他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創建,或是隨后使用ALTER TABLE 或CREATE INDEX被添加。對于較大的數據集,將你的資料輸入一個沒有FULLTEXT索引的表中,然后創建索引,其速度比把資料輸入現有FULLTEXT索引的速度更為快。不過切記對于大容量的數據表,生成全文索引是一個非常消耗時間非常消耗硬盤空間的做法。
–創建表的適合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
–修改表結構添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
–直接創建索引
CREATE FULLTEXT INDEX index_content ON article(content)
4. 單列索引、多列索引
多個單列索引與單個多列索引的查詢效果不同,因為執行查詢時,MySQL只能使用一個索引,會從多個索引中選擇一個限制最為嚴格的索引。
5. 組合索引(最左前綴)
平時用的SQL查詢語句一般都有比較多的限制條件,所以為了進一步榨取MySQL的效率,就要考慮建立組合索引。例如上表中針對title和time建立一個組合索引:ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立這樣的組合索引,其實是相當于分別建立了下面兩組組合索引:
–title
–使用到上面的索引
SELECT * FROM article WHREE title='測試' AND time=1234567890;
SELECT * FROM article WHREE utitle='測試';
–不使用上面的索引
SELECT * FROM article WHREE time=1234567890;